from flask import Flask, render_template, request, jsonify, session, redirect, url_for
import pymysql
import re
import os
import uuid
from datetime import datetime
from flask_mail import Mail, Message
from itsdangerous import URLSafeTimedSerializer
import werkzeug.security as security
import threading
from werkzeug.utils import secure_filename
app = Flask(__name__, template_folder='templates', static_folder='static')
app.secret_key = "my_secret_key_123"

def get_db_connection():
    try:
        return pymysql.connect(
            host="localhost",
            user="root",              
            password="admin@123###",  
            database="ushlearn",
            port=3306     
        )
    except pymysql.Error as err:
        print(f"Database Connection Error: {err}")
        return None
app.config['MAIL_SERVER'] = 'smtp.gmail.com'
app.config['MAIL_PORT'] = 465
app.config['MAIL_USERNAME'] = 'shivkishortiwari123123@gmail.com'
app.config['MAIL_PASSWORD'] = 'jsovhymzmtvzhnnj'
app.config['MAIL_USE_TLS'] = False
app.config['MAIL_USE_SSL'] = True
app.config['MAIL_DEBUG'] = True
mail = Mail(app)
mail.init_app(app)
def send_async_email(app_context, msg_payload):
    with app_context.app_context():
        try:
            mail.send(msg_payload)
            print("Background Thread: Email sent successfully via Gmail SMTP!")
        except Exception as mail_err:
            print(f"Background Thread SMTP Error: {mail_err}")
def valid_password(password):
    return (
        len(password) >= 6 and
        re.search(r"[A-Z]", password) and
        re.search(r"[a-z]", password) and
        re.search(r"[0-9]", password) and
        re.search(r"[@]", password)
    )
UPLOAD_FOLDER = 'static/uploads'
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
os.makedirs(UPLOAD_FOLDER, exist_ok=True)
with app.app_context():
    msg = Message(
        subject="Hello from Flask",
        sender=app.config['MAIL_USERNAME'],
        recipients=["recipient@example.com"]
    )
    msg.body = "This is a test email sent from my Flask application!"
    mail.send(msg)
    print( "Email sent successfully!")

@app.route('/login', methods=['GET','POST'])
def login():
    if request.method == 'POST':
        email = request.form.get('email')
        password = request.form.get('password')
        if "@" not in email or "." not in email:
            return render_template("login.html", error="Enter valid Email ID")
        try:
            conn = get_db_connection()
            cursor = conn.cursor(pymysql.cursors.DictCursor)
            cursor.execute(
                "SELECT * FROM students WHERE email=%s AND password=%s",
                (email,password)
            )
            student = cursor.fetchone()
            cursor.close()
            conn.close()
            if student:
                session['student_id'] = student['id']
                return redirect('/dash')
            else:
                return render_template(
                    "login.html",
                    error="Invalid email or password"
                )


        except Exception as e:

            return f"Database Error: {e}"


    return render_template('login.html')
@app.route('/')
def index():
    return render_template('index.html')
@app.route('/reset-password/<token>', methods=['GET', 'POST'])
def reset_password(token):
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)

    cursor.execute("SELECT email FROM password_reset WHERE token=%s", (token,))
    data = cursor.fetchone()

    print("Token data:", data)

    if not data:
        return "Invalid or expired link"

    if request.method == 'POST':
        new_password = request.form.get('password')

        print("New Password:", new_password)
        print("Email repr:", repr(data['email']))
        email = data['email'].strip()
        cursor.execute("""UPDATE students SET password=%s WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s))""",(new_password, email))
        print("Rows updated:", cursor.rowcount)
        conn.commit()

        cursor.execute(
            "DELETE FROM password_reset WHERE token=%s",
            (token,)
        )
        conn.commit()

        cursor.close()
        conn.close()

        return redirect('/login')

    return render_template("reset.html")    
@app.route('/home')
def home():
    return render_template('Home.html')

@app.route('/dash')
def dash():
    student_id = session.get('student_id')
    if not student_id:
         return redirect('/login') 
    conn = get_db_connection()
    if conn is None:
        fallback_student = {"username": "Student (Offline)", "current_streak": 3, "xp": 980}
        return render_template('dash.html', student=fallback_student, courses=[], leaderboard=[])

    try:
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute("SELECT username, current_streak, xp FROM students WHERE id = %s", (student_id,))
        student_data = cursor.fetchone()
        cursor.execute("SELECT title, progress_percent FROM student_courses WHERE student_id = %s", (student_id,))
        my_courses = cursor.fetchall()
        cursor.execute("SELECT username, xp FROM students ORDER BY xp DESC LIMIT 4")
        leaderboard_data = cursor.fetchall()
        
        cursor.close()
        conn.close()
        return render_template('dash.html', student=student_data, leaderboard=leaderboard_data, courses=my_courses)
        
    except Exception as e:
        print(f"Query Execution Error: {e}")
        if 'cursor' in locals(): cursor.close()
        if 'conn' in locals() and conn: conn.close()
        return render_template('dash.html', student={"username": "User"}, courses=[], leaderboard=[])
@app.route('/profile')
def profile():
    student_id = session.get('student_id')
    if not student_id:
        return redirect('/login')        
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)   
    cursor.execute("SELECT * FROM students WHERE id = %s", (student_id,))
    profile_data = cursor.fetchone() 
    if not profile_data:
        return "Profile data not found", 404
    cursor.execute("""SELECT 
        c.id,
        c.course_name,
        c.description,
        c.image,
        c.duration,
        sc.progress_percent
    FROM student_courses sc
    JOIN courses c ON sc.course_id = c.id
    WHERE sc.student_id = %s
""", (student_id,))
    courses = cursor.fetchall()
    cursor.close()
    conn.close()
    return render_template('profile.html', student=profile_data,username=profile_data['username'],
        phoneno=profile_data['phoneno'], courses=courses)
@app.route('/edit_profile', methods=['GET', 'POST'])
def edit_profile():
    if 'student_id' not in session:
        return redirect(url_for('login'))

    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)

    cursor.execute(
        "SELECT * FROM students WHERE id=%s",
        (session['student_id'],)
    )
    student = cursor.fetchone()

    if request.method == "POST":

        email = request.form["email"]
        phone = request.form["phone"]

        filename = student.get("profile_image")

        file = request.files.get("profile_image")

        if file and file.filename:
            filename = secure_filename(file.filename)
            file.save(os.path.join(app.config["UPLOAD_FOLDER"], filename))

        cursor.execute("""
            UPDATE students
            SET email=%s,
                phoneno=%s,
                profile_image=%s
            WHERE id=%s
        """, (
            email,
            phone,
            filename,
            session["student_id"]
        ))

        conn.commit()

        cursor.close()
        conn.close()

        return redirect(url_for("profile"))

    cursor.close()
    conn.close()

    return render_template(
        "edit_profile.html",
        student=student
    )
@app.route('/register', methods=['GET', 'POST'])
def register():

    if request.method == 'POST':
        course_id = request.form.get('course_id')
        manual_course_name = request.form.get('manual_course_name')
        username = request.form.get('username')
        email = request.form.get('email')
        password = request.form.get('password')
        phoneno = request.form.get('phoneno')
        gender = request.form.get('gender')
        dob = request.form.get('dob')
        university = request.form.get('university')
        college = request.form.get('college')
        main_branch = request.form.get('branch')
        spec = request.form.get('specialisation') or request.form.get('mtech_specialisation') or ""
        final_branch = f"{main_branch} ({spec})" if spec else main_branch
        city = request.form.get('city')
        state = request.form.get('state')
        pincode = request.form.get('pincode')
        if "@" not in email or "." not in email:
            return render_template("login.html", error="Enter valid Email ID")
        if not phoneno.isdigit() or len(phoneno) != 10:
            return render_template("register.html",error="Phone number must contain exactly 10 digits."
        )
        try:
            conn = get_db_connection()
            cursor = conn.cursor(pymysql.cursors.DictCursor)
            cursor.execute(
                "SELECT id FROM students WHERE email=%s",
                (email,)
            )

            if cursor.fetchone():
                cursor.close()
                conn.close()
                return render_template(
                    'register.html',
                    error="Email already exists!"
                )
            insert_sql = """
            INSERT INTO students (
                username,
                email,
                password,
                phoneno,
                branch,
                university,
                college,
                dob,
                city,
                gender,
                state,
                pincode
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            cursor.execute(insert_sql, (
                username,
                email,
                password,
                phoneno,       
                final_branch,  
                university,
                college,
                dob,
                city,
                gender,
                state,
                pincode
            ))

            conn.commit()
            new_student_id = cursor.lastrowid
            session['student_id'] = new_student_id
            cursor.close()
            conn.close()

            return redirect('/login')

        except Exception as e:
            if 'conn' in locals():
                conn.rollback()

            return f"Database Error: {e}"

    return render_template('register.html')
@app.route('/enroll-course', methods=['GET'])
def enroll_course():
    student_id = session.get('student_id')
    course_id = request.args.get('course_id')
    manual_course_name = request.args.get('manual_course_name')
    if not student_id:
        if course_id:
            return redirect(url_for('register', course_id=course_id))
        elif manual_course_name:
            return redirect(url_for('register', manual_course_name=manual_course_name))
        return redirect('/register')
    target_title = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)

        if course_id:
            cursor.execute("SELECT course_name FROM courses WHERE id = %s", (course_id,))
            c_data = cursor.fetchone()
            if c_data: target_title = c_data['course_name']
        elif manual_course_name:
            target_title = manual_course_name

        if target_title:
            cursor.execute("SELECT id FROM student_courses WHERE student_id = %s AND title = %s", (student_id, target_title))
            if not cursor.fetchone():
                cursor.execute("INSERT INTO student_courses (student_id, course_id, title, progress_percent) VALUES (%s, %s, %s, %s)", (student_id, course_id, target_title,0))
                conn.commit()
        
        cursor.close()
        conn.close()
        return redirect('/dash') 
        
    except Exception as e:
        if 'conn' in locals() and conn: conn.rollback()
        return f"Enrollment Processing Failure Database Error: {e}"
        
@app.route('/courses')
def courses():
    student_id = session.get('student_id')
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute("SELECT * FROM courses")
    all_courses = cursor.fetchall()
    owned_course_titles = []
    if student_id:
        cursor.execute("SELECT title FROM student_courses WHERE student_id = %s", (student_id,))
        owned_records = cursor.fetchall()
        owned_course_titles = [record['title'] for record in owned_records]
        
    cursor.close()
    conn.close()
    
    return render_template('courses.html', courses=all_courses, owned_courses=owned_course_titles)
    '''if not student_id:
        return redirect('/login')
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute("SELECT * FROM courses")
    all_courses = cursor.fetchall()
    cursor.close()
    conn.close()
    return render_template('courses.html', courses=all_courses)'''
@app.route('/mentors')
def mentors():
    if not session.get('student_id'):
        return redirect('/login')
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute("SELECT mentor_name, email, expertise FROM mentors")
    mentor_list = cursor.fetchall()
    cursor.close()
    conn.close()
    return render_template('mentors.html', mentors=mentor_list)
@app.route('/goals', methods=['GET', 'POST'])
def goals():
    student_id = session.get('student_id')
    if not student_id:
        return redirect('/login')
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    if request.method == 'POST':
        goal_title = request.form.get('goal_title')
        if goal_title:
            cursor.execute("""
                INSERT INTO goals (student_id, goal_title)
                VALUES (%s, %s)
            """, (student_id, goal_title))
            conn.commit()
    cursor.execute("""
        SELECT *
        FROM goals
        WHERE student_id=%s
        ORDER BY id DESC
    """, (student_id,))
    goals = cursor.fetchall()
    cursor.close()
    conn.close()
    return render_template("goals.html", goals=goals)
@app.route('/complete-goal/<int:id>')
def complete_goal(id):
    if 'student_id' not in session:
        return redirect('/login')
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        UPDATE goals
        SET status='Completed'
        WHERE id=%s
        AND student_id=%s
    """, (id, session['student_id']))
    conn.commit()
    cursor.close()
    conn.close()
    return redirect('/goals')
@app.route('/delete-goal/<int:id>')
def delete_goal(id):
    if 'student_id' not in session:
        return redirect('/login')
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        DELETE FROM goals
        WHERE id=%s
        AND student_id=%s
    """, (id, session['student_id']))
    conn.commit()
    cursor.close()
    conn.close()
    return redirect('/goals')
@app.route('/assignments')
def assignments():
    student_id = session.get('student_id')
    if not student_id:
        return redirect('/login')    
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute("""
        SELECT 
            c.course_name AS course_name,
            a.title AS title, 
            a.due_date AS due_date, 
            sa.submission_status AS status,
            sa.assignment_id AS assignment_id
        FROM student_assignments sa
        JOIN assignments a ON sa.assignment_id = a.id
        LEFT JOIN courses c ON a.course_id = c.id
        WHERE sa.student_id = %s
        ORDER BY a.due_date ASC
    """, (student_id,))
    assignments_list = cursor.fetchall()
    cursor.execute("SELECT username FROM students WHERE id = %s", (student_id,))
    student_data = cursor.fetchone()
    cursor.close()
    conn.close()
    return render_template('assignments.html', student=student_data, assignments=assignments_list)
@app.route('/submit-assignment', methods=['POST'])
def submit_assignment():
    student_id = session.get('student_id')
    if not student_id:
        return redirect('/login')
        
    assignment_id = request.form.get('assignment_id')
    file = request.files.get('submission_file')
    if file and file.filename != '':
        filename = f"{student_id}_{assignment_id}_{file.filename}"
        file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
    conn = get_db_connection()
    if conn:
      try:
           cursor = conn.cursor()
           cursor.execute("""
              UPDATE student_assignments 
              SET submission_status = 'Submitted' 
              WHERE student_id = %s AND assignment_id = %s
           """, (student_id, assignment_id))
           conn.commit()
           cursor.close()
      except Exception as e:
        print(f"Submission error: {e}")
      finally:
         conn.close()
        
    return redirect('/assignments')
@app.route('/activity')
def activity():
    student_id = session.get('student_id')
    if not student_id:
        return redirect('/login')
    try:
       conn = get_db_connection()
       cursor = conn.cursor(pymysql.cursors.DictCursor)
       cursor.execute("""
          SELECT activity, activity_date
          FROM activity_log
          WHERE student_id=%s
          ORDER BY activity_date DESC
          LIMIT 100
        """, (student_id,))
       logs = cursor.fetchall()
       cursor.execute("""
           SELECT
              COUNT(*) AS total,
              SUM(DATE(activity_date)=CURDATE()) AS today
           FROM activity_log
           WHERE student_id=%s
        """, (student_id,))
       stats = cursor.fetchone()
       cursor.execute("""
            SELECT
              DATE(activity_date) AS study_date,
              COUNT(*)*10 AS total_minutes
           FROM activity_log
           WHERE student_id=%s
           GROUP BY study_date
           ORDER BY study_date
        """, (student_id,))
       study_data = cursor.fetchall()
       cursor.execute("""
            SELECT 
              c.course_name,
              COUNT(*) AS views
           FROM activity_log a
           JOIN courses c ON a.course_id = c.id
           WHERE a.student_id = %s
           GROUP BY c.course_name
        """, (student_id,))
       course_engagement = cursor.fetchall()
       cursor.close()
       conn.close()
       return render_template(
          "activity.html",
           logs=logs,
           total=stats["total"],
           today=stats["today"],
           study_data=study_data,
           course_engagement=course_engagement
       )
    except Exception as e:
        return f"Activity Page Error: {e}"
@app.route('/community')
def community():
    return render_template('community.html')
@app.route('/settings', methods=['GET', 'POST'])
def settings():
    student_id = session.get('student_id')
    if not student_id:
        return redirect('/login')
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    success_msg = None
    if request.method == 'POST':
        username = request.form.get('username')
        email = request.form.get('email')
        phoneno = request.form.get('phoneno')
        branch = request.form.get('branch')
        theme = request.form.get('siteTheme')
        email_alerts = 1 if request.form.get('email_alerts') else 0
        course_updates = 1 if request.form.get('course_updates') else 0
        try:
           cursor.execute("""
                UPDATE students
                SET username=%s,
                  email=%s,
                  phoneno=%s,
                  theme=%s,
                  email_alerts=%s,
                  course_updates=%s,
                  branch=%s      
                WHERE id=%s
           """, (username, email, phoneno, theme, email_alerts, course_updates, branch, student_id))
           conn.commit()
           success_msg = "Changes saved successfully!"
        except Exception as e:
            print(f"Database Update Error: {e}")
    cursor.execute(
        "SELECT * FROM students WHERE id=%s",
        (student_id,)
    )
    student = cursor.fetchone()
    cursor.close()
    conn.close()
    return render_template('settings.html',student=student, success=success_msg)
@app.route('/contact')
def contact():
    return render_template('contact.html')
@app.route('/forgot-password', methods=['GET', 'POST'])
def forgot_password():
    if request.method == 'POST':
        email = request.form.get('email','').strip().lower()
        token = str(uuid.uuid4())
        conn = None
        cursor = None
        try:
          conn = get_db_connection()
          cursor = conn.cursor(pymysql.cursors.DictCursor)
          cursor.execute("SELECT id FROM students WHERE LOWER(TRIM(email))=%s", (email,))
          user = cursor.fetchone()
          if user:
              cursor.execute(
                 "INSERT INTO password_reset (email, token) VALUES (%s, %s)",
                 (email, token)
                )
              conn.commit()
              reset_link = f"http://192.168.29.45:5500/reset-password/{token}"
              print("RESET LINK GENERATED:", reset_link)
              try:
                  msg = Message(subject="Password Reset Link - UshLearn",sender=app.config['MAIL_USERNAME'],recipients=[email])
                  msg.body = f"Hello,\n\nPlease click the link below to safely reset your account password:\n{reset_link}"
                  mail.send(msg)
                  print(" Email sent successfully via Gmail SMTP!")
              except Exception as mail_err:
                   print(f"MAIL SYSTEM ERROR: {mail_err}")
                   return f"Database updated but Mail system failed: {mail_err}"
          else: 
             print("Notice: Entered email was not found in the database system.")
             return "Email address not found in our records."
        except Exception as db_err:
            print(f" DATABASE ERROR: {db_err}")
            return f"Database Operation Failed: {db_err}"
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
        return "If the account email exists, a password reset link has been dispatched."       
    return render_template("forgot.html")
@app.route('/logout')
def logout():
    session.clear()
    return render_template('logout.html')
@app.route('/api/logout', methods=['POST'])
def api_logout():
    try:
        return jsonify({"status": "success", "message": "Successfully logged out"}), 200
    except Exception as e:
        return jsonify({"status": "error", "message": str(e)}), 500
if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5500, debug=True, use_reloader=True)