import streamlit as st
import pandas as pd
import numpy as np
from cryptography.fernet import Fernet
import os
import io
from dotenv import load_dotenv
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import Flow
from google.auth.transport.requests import Request
from googleapiclient.discovery import build
import json
import logging
from oauthlib.oauth2.rfc6749.errors import InvalidGrantError
import secrets
import time
import mysql.connector  # Add new import

# Load environment variables from .env file
load_dotenv()


# Set up logging
logging.basicConfig(level=logging.DEBUG)

# Set page configuration
st.set_page_config(page_title="Midterm Grade for 2nd Sem 2024-2025", page_icon="📚", layout="centered")
#ADDD
# Custom CSS to improve the app's appearance
st.markdown("""
<style>
    .reportview-container {
        background: #f0f2f6
    }
    .big-font {
        font-size:20px !important;
        font-weight: bold;
    }
    .welcome-message {
        font-size:24px !important;
        font-weight: bold;
        margin-bottom: 20px;
    }
    .stAlert > div {
        padding-top: 15px;
        padding-bottom: 15px;
    }
    .message {
        font-size: 18px;
        font-style: italic;
        margin-top: 20px;
    }
    .warning-message {
        font-size: 18px;
        font-weight: bold;
        color: #ff9800;
        margin-top: 20px;
    }
</style>
""", unsafe_allow_html=True)

# Google OAuth configuration
CLIENT_CONFIG = {
    "web": {
        "client_id": os.getenv("GOOGLE_CLIENT_ID"),
        "client_secret": os.getenv("GOOGLE_CLIENT_SECRET"),
        "auth_uri": "https://accounts.google.com/o/oauth2/auth",
        "token_uri": "https://oauth2.googleapis.com/token",
    }
}

SCOPES = ['https://www.googleapis.com/auth/userinfo.email', 'https://www.googleapis.com/auth/userinfo.profile', 'openid']

# Function to create OAuth flow
def create_flow():
    redirect_uri = "https://meng21-gradeview.hf.space/"  # Update this with your Streamlit app's URL
    # redirect_uri = "http://192.168.1.18:8501"  # local dev
    logging.debug(f"Creating flow with redirect URI: {redirect_uri}")
    flow = Flow.from_client_config(
        client_config=CLIENT_CONFIG,
        scopes=SCOPES,
        redirect_uri=redirect_uri
    )
    return flow

# Function to check if the user is logged in
def is_logged_in():
    return 'credentials' in st.session_state

# Function to get user info
def get_user_info(credentials):
    service = build('oauth2', 'v2', credentials=credentials)
    user_info = service.userinfo().get().execute()
    return user_info

# Remove the load_data function and replace with database connection
def get_db_connection():
    return mysql.connector.connect(
        host=st.secrets.host,
        port=int(st.secrets.port),
        user=st.secrets.user,
        password=st.secrets.password,
        database=st.secrets.database
    )

# Main function to run the Streamlit app
def main():
    st.title('📚 Midterm Grade for 2nd Sem 2024-2025')
    st.markdown("---")
    
    if not is_logged_in():
        st.write("Please log in with your CSPC Google account to access your grade.")
        
        if st.button("Login with Google"):
            flow = create_flow()
            authorization_url, _ = flow.authorization_url(prompt="consent")
            st.markdown(f'<meta http-equiv="refresh" content="0;url={authorization_url}">', unsafe_allow_html=True)
    else:
        credentials = Credentials.from_authorized_user_info(json.loads(st.session_state['credentials']))
        user_info = get_user_info(credentials)
        st.markdown(f"<p class='welcome-message'>Welcome, {user_info['name']}!</p>", unsafe_allow_html=True)

        try:
            conn = get_db_connection()
            cursor = conn.cursor(dictionary=True)

            # Get student ID from email
            cursor.execute("""
                SELECT s.student_id, s.name 
                FROM students s
                WHERE s.email = %s
            """, (user_info['email'],))
            
            student = cursor.fetchone()
            
            if student:
                # Get student's courses and grades
                cursor.execute("""
                    SELECT c.course_name AS COURSE, e.grade AS GRADE, e.remarks AS REMARKS
                    FROM enrollments e
                    JOIN courses c ON e.course_code = c.course_code
                    WHERE e.student_id = %s
                """, (student['student_id'],))
                
                student_records = cursor.fetchall()

                if not student_records:
                    st.error('Your email is not found in our records. Please contact the administrator.')
                else:
                    st.markdown("---")
                    st.subheader('Your Grade Information:')
                    
                    for record in student_records:
                        st.markdown(f"### Course: {record['COURSE']}")
                        
                        col1, col2, col3 = st.columns(3)
                        
                        with col1:
                            st.markdown("<p class='big-font'>ID</p>", unsafe_allow_html=True)
                            st.write(f"{student['student_id']}")

                        with col2:
                            st.markdown("<p class='big-font'>Grade</p>", unsafe_allow_html=True)
                            grade = record['GRADE']
                            if pd.isna(grade):
                                grade_display = "N/A"
                            else:
                                grade_display = f"{grade:.2f}"
                            st.write(grade_display)
                        
                        with col3:
                            st.markdown("<p class='big-font'>Remarks</p>", unsafe_allow_html=True)
                            remarks = record['REMARKS']
                            if remarks == "Passed":
                                st.success(remarks)
                            elif remarks == "Conditional":
                                st.warning(remarks)
                            else:
                                st.write(remarks)
                        
                        if not pd.isna(grade):
                            if grade >= 1.9 and grade <= 3.0:
                                message = f"Keep up the good work! Your grade of {grade:.2f} shows dedication. Consider seeking additional support to further improve your performance."
                                st.markdown(f"<p class='message'>{message}</p>", unsafe_allow_html=True)
                            
                            elif grade > 3.00:
                                message = f"Your grade of {grade:.2f} indicates that you did not meet the passing requirements. Please consult with your instructor to discuss options for improvement and potential remediation."
                                st.markdown(f"<p class='message'>{message}</p>", unsafe_allow_html=True)
                            else:
                                message = f"Congratulations! Your outstanding grade of {grade:.2f} demonstrates exceptional performance. Keep up the excellent work!"
                                st.markdown(f"<p class='message'>{message}</p>", unsafe_allow_html=True)
                        
                        if remarks == "Conditional":
                            warning_message = "Warning: Your current status is Conditional. You need to pass or achieve a higher grade in the final term to improve your standing."
                            st.markdown(f"<p class='warning-message'>{warning_message}</p>", unsafe_allow_html=True)
                        
                        st.markdown("---")
            else:
                st.error('Your email is not found in our records. Please contact the administrator.')

            conn.close()
        except mysql.connector.Error as err:
            st.error(f"Database error: {err}")

        # Add refresh button above logout
        col1, col2 = st.columns(2)
        with col1:
            if st.button("🔄 Refresh Grades"):
                st.success("Grades refreshed!")
                time.sleep(0.5)
                st.rerun()
        with col2:
            if st.button("🚪 Logout"):
                for key in list(st.session_state.keys()):
                    del st.session_state[key]
                st.rerun()

# Function to handle OAuth callback
def handle_callback():
    flow = create_flow()
    try:
        flow.fetch_token(code=st.query_params["code"])
        credentials = flow.credentials
        st.session_state['credentials'] = credentials.to_json()
        logging.debug("Token fetch successful")
        st.success("Authentication successful!")
        time.sleep(2)  # Give user time to see the success message
        st.rerun()  # Rerun the app to clear the URL parameters
    except Exception as e:
        pass
        # logging.error(f"Error during authentication: {str(e)}")
        # st.error(f"Authentication failed: {str(e)}")

# Database configuration
DB_CONFIG = {
    'host': st.secrets.host,
    'port': int(st.secrets.port),
    'user': st.secrets.user,
    'password': st.secrets.password,
    'database': st.secrets.database
}

if __name__ == '__main__':
    logging.debug("Starting the application")
    if 'code' in st.query_params:
        logging.debug("Authorization code found in query parameters")
        handle_callback()
    main()