import os
from flask import jsonify, render_template, redirect, url_for, current_app, request
from .common import main_bp, get_db_connection, login_required, current_user, upload_progress
from strings import ROUTE_INDEX, METHOD_DELETE, METHOD_POST

@main_bp.route(ROUTE_INDEX)
def index():
    if current_user.is_authenticated: return redirect(url_for('dashboard.dashboard'))
    return redirect(url_for('auth.login'))

@main_bp.route('/upload_progress/<session_id>')
@login_required
def get_upload_progress(session_id):
    status = upload_progress.get(session_id)
    if not status:
        conn = get_db_connection()
        exists = conn.execute('SELECT id FROM sessions WHERE id = ?', (session_id,)).fetchone()
        conn.close()
        if exists: return jsonify({'status': 'completed', 'progress': 100})
        return jsonify({'error': 'Session not found or processing not started'}), 404
    return jsonify(status)

@main_bp.route('/api/session_images/<session_id>')
@login_required
def get_session_images(session_id):
    conn = get_db_connection()
    session_owner = conn.execute('SELECT user_id FROM sessions WHERE id = ?', (session_id,)).fetchone()
    if not session_owner or session_owner['user_id'] != current_user.id:
        conn.close(); return jsonify({'error': 'Unauthorized'}), 403
    originals = conn.execute("SELECT image_index, filename FROM images WHERE session_id = ? AND image_type = 'original' ORDER BY image_index", (session_id,)).fetchall()
    processed = conn.execute("SELECT image_index, processed_filename FROM images WHERE session_id = ? AND image_type = 'color_rm'", (session_id,)).fetchall()
    processed_map = {row['image_index']: row['processed_filename'] for row in processed}
    images_list = []
    for img in originals:
        idx = img['image_index']; p_filename = processed_map.get(idx)
        images_list.append({'index': idx, 'page_number': idx + 1, 'original_url': url_for('main.serve_image', folder='uploads', filename=img['filename']), 'processed_url': url_for('main.serve_processed_file', filename=p_filename) if p_filename else None, 'is_processed': bool(p_filename)})
    conn.close(); return jsonify({'images': images_list})

@main_bp.route('/old/dashboard')
@login_required
def old_dashboard(): return redirect(url_for('dashboard.dashboard'))

@main_bp.route('/delete_session/<session_id>', methods=[METHOD_DELETE])
@login_required
def delete_session(session_id):
    try:
        conn = get_db_connection()
        owner = conn.execute('SELECT user_id FROM sessions WHERE id = ?', (session_id,)).fetchone()
        if not owner or owner['user_id'] != current_user.id: conn.close(); return jsonify({'error': 'Unauthorized'}), 403
        images = conn.execute('SELECT filename, processed_filename FROM images WHERE session_id = ?', (session_id,)).fetchall()
        for img in images:
            if img['filename']:
                try: os.remove(os.path.join(current_app.config['UPLOAD_FOLDER'], img['filename']))
                except OSError: pass
            if img['processed_filename']:
                try: os.remove(os.path.join(current_app.config['PROCESSED_FOLDER'], img['processed_filename']))
                except OSError: pass
        conn.execute('DELETE FROM questions WHERE session_id = ?', (session_id,))
        conn.execute('DELETE FROM images WHERE session_id = ?', (session_id,))
        conn.execute('DELETE FROM sessions WHERE id = ?', (session_id,))
        conn.commit(); conn.close(); return jsonify({'success': True})
    except Exception as e: return jsonify({'error': str(e)}), 500

@main_bp.route('/toggle_persist/<session_id>', methods=[METHOD_POST])
@login_required
def toggle_persist(session_id):
    try:
        conn = get_db_connection()
        owner = conn.execute('SELECT user_id FROM sessions WHERE id = ?', (session_id,)).fetchone()
        if not owner or owner['user_id'] != current_user.id: conn.close(); return jsonify({'error': 'Unauthorized'}), 403
        curr = conn.execute('SELECT persist FROM sessions WHERE id = ?', (session_id,)).fetchone()
        if not curr: conn.close(); return jsonify({'error': 'Not found'}), 404
        new_s = 1 - curr['persist']
        conn.execute('UPDATE sessions SET persist = ? WHERE id = ?', (new_s, session_id))
        pdf = conn.execute('SELECT id FROM generated_pdfs WHERE session_id = ?', (session_id,)).fetchone()
        if pdf: conn.execute('UPDATE generated_pdfs SET persist = ? WHERE id = ?', (new_s, pdf['id']))
        conn.commit(); conn.close(); return jsonify({'success': True, 'status': 'persisted' if new_s == 1 else 'not_persisted'})
    except Exception as e: conn.rollback(); conn.close(); return jsonify({'error': str(e)}), 500

@main_bp.route('/rename_session/<session_id>', methods=['POST'])
@login_required
def rename_session(session_id):
    data = request.json; new_name = data.get('new_name')
    if not new_name: return jsonify({'error': 'Required'}), 400
    try:
        conn = get_db_connection()
        owner = conn.execute('SELECT user_id FROM sessions WHERE id = ?', (session_id,)).fetchone()
        if not owner or owner['user_id'] != current_user.id: conn.close(); return jsonify({'error': 'Unauthorized'}), 403
        conn.execute('UPDATE sessions SET name = ? WHERE id = ?', (new_name, session_id))
        conn.commit(); conn.close(); return jsonify({'success': True})
    except Exception as e: return jsonify({'error': str(e)}), 500

@main_bp.route('/chart')
@login_required
def chart():
    conn = get_db_connection()
    total_sessions = conn.execute('SELECT COUNT(*) FROM sessions WHERE user_id = ?', (current_user.id,)).fetchone()[0]
    total_pdfs = conn.execute('SELECT COUNT(*) FROM generated_pdfs WHERE user_id = ?', (current_user.id,)).fetchone()[0]
    total_questions = conn.execute("SELECT COUNT(q.id) FROM questions q JOIN sessions s ON q.session_id = s.id WHERE s.user_id = ?", (current_user.id,)).fetchone()[0]
    total_classified = conn.execute("SELECT COUNT(q.id) FROM questions q JOIN sessions s ON q.session_id = s.id WHERE s.user_id = ? AND q.subject IS NOT NULL AND q.chapter IS NOT NULL", (current_user.id,)).fetchone()[0]
    conn.close()
    return render_template('chart.html', total_sessions=total_sessions, total_pdfs=total_pdfs, total_questions=total_questions, total_classified_questions=total_classified)


@main_bp.route('/stacked_chart')
@login_required
def stacked_chart():
    conn = get_db_connection()
    
    # Get all unique group names for this user to populate the group selector
    groups_rows = conn.execute("SELECT DISTINCT group_name FROM sessions WHERE user_id = ? AND group_name IS NOT NULL AND group_name != '' ORDER BY group_name", (current_user.id,)).fetchall()
    groups = [row['group_name'] for row in groups_rows]
    
    conn.close()
    return render_template('stacked_chart.html', groups=groups)


@main_bp.route('/api/stacked_chart_data')
@login_required
def api_stacked_chart_data():
    conn = get_db_connection()
    group_filter = request.args.get('group')
    
    # Base WHERE clause components
    where_clause = "WHERE s.user_id = ? AND q.subject IS NOT NULL AND q.chapter IS NOT NULL"
    params = [current_user.id]
    
    if group_filter:
        where_clause += " AND s.group_name = ?"
        params.append(group_filter)
    else:
        # Default: show sessions with NO group
        where_clause += " AND (s.group_name IS NULL OR s.group_name = '')"
    
    # Filter for only specific subjects (P, C, B, Z, M)
    where_clause += """ AND UPPER(SUBSTR(TRIM(
               CASE 
                 WHEN q.subject = 'CHEMISTRY' THEN 'Chemistry'
                 WHEN q.subject = 'PHYSICS' THEN 'Physics'
                 WHEN q.subject = 'BIOLOGY' THEN 'Biology'
                 WHEN q.subject = 'MATHEMATICS' THEN 'Mathematics'
                 WHEN q.subject = 'ZOOLOGY' THEN 'Zoology'
                 WHEN q.subject = 'BOTANY' THEN 'Botany'
                 ELSE q.subject
               END
          ), 1, 1)) IN ('P', 'C', 'B', 'Z', 'M')"""

    # Get classified questions grouped by session_id, subject, and topic (chapter)
    classified_data = conn.execute(f"""
        SELECT s.id as session_id, s.name as session_name, s.original_filename, s.created_at,
               CASE 
                 WHEN q.subject = 'CHEMISTRY' THEN 'Chemistry'
                 WHEN q.subject = 'PHYSICS' THEN 'Physics'
                 WHEN q.subject = 'BIOLOGY' THEN 'Biology'
                 WHEN q.subject = 'MATHEMATICS' THEN 'Mathematics'
                 WHEN q.subject = 'ZOOLOGY' THEN 'Zoology'
                 WHEN q.subject = 'BOTANY' THEN 'Botany'
                 ELSE q.subject
               END as subject,
               q.chapter, COUNT(*) as question_count
        FROM questions q
        JOIN sessions s ON q.session_id = s.id
        {where_clause}
        GROUP BY s.id, s.name, s.original_filename, s.created_at,
               CASE 
                 WHEN q.subject = 'CHEMISTRY' THEN 'Chemistry'
                 WHEN q.subject = 'PHYSICS' THEN 'Physics'
                 WHEN q.subject = 'BIOLOGY' THEN 'Biology'
                 WHEN q.subject = 'MATHEMATICS' THEN 'Mathematics'
                 WHEN q.subject = 'ZOOLOGY' THEN 'Zoology'
                 WHEN q.subject = 'BOTANY' THEN 'Botany'
                 ELSE q.subject
               END,
               q.chapter
        ORDER BY s.created_at DESC, s.id, 
               CASE 
                 WHEN q.subject = 'CHEMISTRY' THEN 'Chemistry'
                 WHEN q.subject = 'PHYSICS' THEN 'Physics'
                 WHEN q.subject = 'BIOLOGY' THEN 'Biology'
                 WHEN q.subject = 'MATHEMATICS' THEN 'Mathematics'
                 WHEN q.subject = 'ZOOLOGY' THEN 'Zoology'
                 WHEN q.subject = 'BOTANY' THEN 'Botany'
                 ELSE q.subject
               END,
               q.chapter
    """, params).fetchall()
    
    # Get topic occurrence statistics
    topic_stats = conn.execute(f"""
        SELECT 
               CASE 
                 WHEN q.subject = 'CHEMISTRY' THEN 'Chemistry'
                 WHEN q.subject = 'PHYSICS' THEN 'Physics'
                 WHEN q.subject = 'BIOLOGY' THEN 'Biology'
                 WHEN q.subject = 'MATHEMATICS' THEN 'Mathematics'
                 WHEN q.subject = 'ZOOLOGY' THEN 'Zoology'
                 WHEN q.subject = 'BOTANY' THEN 'Botany'
                 ELSE q.subject
               END as subject,
               q.chapter,
               COUNT(*) as total_occurrences,
               COUNT(DISTINCT q.session_id) as total_sessions_with_topic
        FROM questions q
        JOIN sessions s ON q.session_id = s.id
        {where_clause}
        GROUP BY 
               CASE 
                 WHEN q.subject = 'CHEMISTRY' THEN 'Chemistry'
                 WHEN q.subject = 'PHYSICS' THEN 'Physics'
                 WHEN q.subject = 'BIOLOGY' THEN 'Biology'
                 WHEN q.subject = 'MATHEMATICS' THEN 'Mathematics'
                 WHEN q.subject = 'ZOOLOGY' THEN 'Zoology'
                 WHEN q.subject = 'BOTANY' THEN 'Botany'
                 ELSE q.subject
               END,
               q.chapter
    """, params).fetchall()
    
    # Group the data by session_id for the chart
    sessions_data = {}
    for row in classified_data:
        session_id = row['session_id']
        session_name = row['session_name'] or session_id
        
        if session_name == session_id and row['original_filename']:
            session_name = row['original_filename']
        
        subject = row['subject']
        topic = row['chapter']
        count = row['question_count']
        
        if session_id not in sessions_data:
            sessions_data[session_id] = {
                'session_name': session_name,
                'created_at': row['created_at'],
                'subjects': {}
            }
        
        if subject not in sessions_data[session_id]['subjects']:
            sessions_data[session_id]['subjects'][subject] = {}
        
        sessions_data[session_id]['subjects'][subject][topic] = count
    
    # Create mapping for topic stats
    topic_stats_map = {}
    for row in topic_stats:
        subject = row['subject']
        topic = row['chapter']
        topic_stats_map[f"{subject}|{topic}"] = {
            'total_occurrences': row['total_occurrences'],
            'total_sessions_with_topic': row['total_sessions_with_topic']
        }
    
    # Add topic statistics to sessions_data
    for session_id in sessions_data:
        sessions_data[session_id]['wrong_ratios'] = {}
        for subject in sessions_data[session_id]['subjects']:
            sessions_data[session_id]['wrong_ratios'][subject] = {}
            for topic in sessions_data[session_id]['subjects'][subject]:
                key = f"{subject}|{topic}"
                topic_stat = topic_stats_map.get(key, {'total_occurrences': 0, 'total_sessions_with_topic': 0})
                sessions_data[session_id]['wrong_ratios'][subject][topic] = topic_stat
    
    conn.close()
    return jsonify(sessions_data)