# app.py
from flask import Flask, request, jsonify, render_template
import requests
import json
import logging
from flask_cors import CORS
import mysql.connector
from mysql.connector import errorcode

# Configure logging
logging.basicConfig(level=logging.DEBUG)
app = Flask(__name__)
CORS(app)

# --- Moodle Configuration ---
MOODLE_LMS_URL = "https://lms.collnetwork.net/webservice/rest/server.php"
MOODLE_LMS_TOKEN = "33e292b8342c48d69351d9cad2e4408c"
COURSE_ID = 8

# --- MariaDB Configuration ---
# IMPORTANT: Replace with your actual credentials
DB_CONFIG = {
    'user': 'collnetw',
    'password': '@CNT2025',
    'host': 'localhost',  # Or the specific host if it's not on the same server
    'database': 'collnetw_attendance'
}

def get_db_connection():
    """Establishes and returns a new MariaDB database connection."""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        return conn
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            app.logger.error("Something is wrong with your user name or password.")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            app.logger.error("Database does not exist.")
        else:
            app.logger.error(f"Database connection error: {err}")
        return None

# --- API Endpoints ---
@app.route('/moodle/participants')
def get_moodle_participants():
    """
    Fetches Moodle participants, upserts them into the MariaDB 'students' table,
    and returns the list of students.
    """
    params = {
        'wstoken': MOODLE_LMS_TOKEN,
        'wsfunction': 'core_enrol_get_enrolled_users',
        'moodlewsrestformat': 'json',
        'courseid': COURSE_ID,
    }
    
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Failed to connect to the database'}), 500

    try:
        response = requests.get(MOODLE_LMS_URL, params=params)
        response.raise_for_status()
        participants_data = response.json()

        if isinstance(participants_data, dict) and 'error' in participants_data:
            return jsonify({'error': participants_data.get('error')}), 500
            
        students_only = [user for user in participants_data if user.get('id') != 3]

        cursor = conn.cursor()
        # Upsert students into the MariaDB 'students' table
        for student in students_only:
            sql = "INSERT INTO students (id, fullname) VALUES (%s, %s) ON DUPLICATE KEY UPDATE fullname=%s"
            cursor.execute(sql, (student['id'], student['fullname'], student['fullname']))
        
        conn.commit()
        app.logger.info("Successfully fetched and upserted participants.")
        
        return jsonify(students_only)
    except requests.exceptions.RequestException as e:
        app.logger.error(f"Error fetching Moodle participants: {e}")
        return jsonify({'error': f"Failed to fetch participants: {e}"}), 500
    except json.JSONDecodeError:
        app.logger.error("Failed to decode Moodle API response as JSON.")
        return jsonify({'error': 'Invalid JSON response from Moodle'}), 500
    except mysql.connector.Error as err:
        app.logger.error(f"Database error during upsert: {err}")
        return jsonify({'error': 'Database error while saving students.'}), 500
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

@app.route('/save-attendance', methods=['POST'])
def save_attendance():
    """
    Receives attendance data from the frontend and saves it to the MariaDB.
    A score of 1 is given for present, 0 for absent.
    """
    data = request.json
    selected_date = data.get('date')
    attendance_records = data.get('records')

    if not selected_date or not attendance_records:
        return jsonify({'error': 'Invalid data provided.'}), 400

    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Failed to connect to the database'}), 500

    try:
        cursor = conn.cursor()
        for record in attendance_records:
            student_id = record['userId']
            is_present = record['present']
            
            # The score is 1 if present, 0 if absent.
            score = 1 if is_present else 0
            
            # Use INSERT ... ON DUPLICATE KEY UPDATE to handle re-submissions for the same day.
            sql = """
                INSERT INTO attendance (student_id, attendance_date, present)
                VALUES (%s, %s, %s)
                ON DUPLICATE KEY UPDATE present = %s
            """
            cursor.execute(sql, (student_id, selected_date, score, score))
        
        conn.commit()
        app.logger.info(f"Attendance for {selected_date} saved successfully.")
        return jsonify({'message': 'Attendance saved successfully!'}), 200
    except mysql.connector.Error as err:
        app.logger.error(f"Database error during attendance save: {err}")
        conn.rollback()  # Rollback changes if an error occurs
        return jsonify({'error': 'Database error while saving attendance.'}), 500
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

@app.route('/view-report', methods=['GET'])
def view_report():
    """
    Fetches the attendance report with total points for all students.
    """
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Failed to connect to the database'}), 500
        
    try:
        cursor = conn.cursor(dictionary=True)
        sql = """
            SELECT
                s.id,
                s.fullname,
                SUM(a.present) AS total_points
            FROM students s
            LEFT JOIN attendance a ON s.id = a.student_id
            GROUP BY s.id, s.fullname
            ORDER BY s.fullname ASC
        """
        cursor.execute(sql)
        report_data = cursor.fetchall()
        
        return render_template('report.html', students=report_data)
    except mysql.connector.Error as err:
        app.logger.error(f"Database error while fetching report: {err}")
        return jsonify({'error': 'Database error while fetching report.'}), 500
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

# --- Web Server Routes ---
@app.route('/')
def index():
    """Renders the main attendance HTML page."""
    return render_template('index.html')