# -*- coding: utf-8 -*-
"""
Auto-Rapor PBD Analytics Web Application for SMP Negeri 2 Bojonegoro.
Native WSGI Flask Application ready for cPanel & Local Development.
"""
import hmac
import os
import sys
from pathlib import Path
from flask import Flask, render_template, request, redirect, url_for, flash, session, send_file, jsonify
from werkzeug.utils import secure_filename

BASE_DIR = Path(__file__).resolve().parent
if str(BASE_DIR) not in sys.path:
    sys.path.insert(0, str(BASE_DIR))

from config import Config
from core.db import get_db, init_db
from core.analytics import (
    get_available_years, get_kpi_cards, get_red_flags,
    get_pbd_recommendations_with_arkas, get_multi_year_line_chart_data,
    get_active_school
)
from core.ingest import ingest_rapor_file
from core.gemini_narrative import generate_executive_narrative
from core.exporter import export_arkas_excel

app = Flask(__name__)
app.config.from_object(Config)

# Pastikan folder database dan upload ada
Config.DATA_DIR.mkdir(parents=True, exist_ok=True)
Config.UPLOAD_FOLDER.mkdir(parents=True, exist_ok=True)

@app.context_processor
def inject_school_context():
    return dict(
        active_school=get_active_school(),
        logged_in_user=session.get('username')
    )

@app.before_request
def require_login():
    if request.endpoint in ('login', 'logout', 'static') or request.path.startswith('/static/'):
        return None
    if not session.get('logged_in'):
        if request.path.startswith('/api/'):
            return jsonify({'status': 'error', 'message': 'Akses ditolak. Silakan login terlebih dahulu.'}), 401
        return redirect(url_for('login'))

@app.route('/login', methods=['GET', 'POST'])
def login():
    if session.get('logged_in'):
        return redirect(url_for('index'))

    if request.method == 'POST':
        username = request.form.get('username', '').strip()
        password = request.form.get('password', '')

        valid_user = hmac.compare_digest(username, Config.ADMIN_USERNAME)
        valid_pass = hmac.compare_digest(password, Config.ADMIN_PASSWORD)

        if valid_user and valid_pass:
            session['logged_in'] = True
            session['username'] = username
            return redirect(url_for('index'))
        else:
            flash("Username atau kata sandi salah.", "error")

    return render_template('login.html')

@app.route('/logout')
def logout():
    session.clear()
    flash("Anda telah berhasil keluar dari sistem.", "success")
    return redirect(url_for('login'))

@app.route('/')
def index():
    years = get_available_years()
    if not years:
        return redirect(url_for('upload'))
    
    selected_year = request.args.get('year', type=int)
    if not selected_year or selected_year not in years:
        selected_year = years[0]

    kpi_cards = get_kpi_cards(selected_year)
    red_flags = get_red_flags(selected_year)
    line_chart_data = get_multi_year_line_chart_data()

    return render_template(
        'index.html',
        available_years=years,
        selected_year=selected_year,
        kpi_cards=kpi_cards,
        red_flags=red_flags,
        line_chart_data=line_chart_data
    )

@app.route('/indikator')
def indikator():
    years = get_available_years()
    if not years:
        return redirect(url_for('upload'))

    selected_year = request.args.get('year', type=int)
    if not selected_year or selected_year not in years:
        selected_year = years[0]

    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('''
    SELECT code, name, full_text, capaian_label, score, score_prev, delta_yoy,
           is_red_flag, red_flag_reason, rank_kabkota, rank_nasional
    FROM indicator_scores
    WHERE year = ?
    ORDER BY code ASC
    ''', (selected_year,))
    indicators = [dict(r) for r in cursor.fetchall()]
    conn.close()

    return render_template(
        'indikator.html',
        available_years=years,
        selected_year=selected_year,
        indicators=indicators
    )

@app.route('/benahi')
def benahi():
    years = get_available_years()
    if not years:
        return redirect(url_for('upload'))

    selected_year = request.args.get('year', type=int)
    if not selected_year or selected_year not in years:
        selected_year = years[0]

    recommendations = get_pbd_recommendations_with_arkas(selected_year)

    return render_template(
        'benahi.html',
        available_years=years,
        selected_year=selected_year,
        recommendations=recommendations
    )

@app.route('/narasi')
def narasi():
    years = get_available_years()
    if not years:
        return redirect(url_for('upload'))

    selected_year = request.args.get('year', type=int)
    if not selected_year or selected_year not in years:
        selected_year = years[0]

    # Ambil narasi yang tersimpan di database jika sudah pernah di-generate
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT executive_narrative FROM rapor_files WHERE year = ?", (selected_year,))
    row = cursor.fetchone()
    saved_narrative = row['executive_narrative'] if row and row['executive_narrative'] else None
    conn.close()

    kpis = get_kpi_cards(selected_year)
    has_api_key = bool(session.get('gemini_api_key') or Config.GEMINI_API_KEY)

    return render_template(
        'narasi.html',
        available_years=years,
        selected_year=selected_year,
        narrative_text=saved_narrative,
        kpis=kpis,
        has_api_key=has_api_key
    )

@app.route('/api/generate-narasi', methods=['POST'])
def api_generate_narasi():
    """Endpoint AJAX untuk menghasilkan narasi AI secara asinkron tanpa memblokir halaman"""
    data = request.get_json() or {}
    year_raw = data.get('year')
    try:
        year = int(year_raw) if year_raw is not None else None
    except (ValueError, TypeError):
        year = None

    if not year:
        return jsonify({'status': 'error', 'message': 'Parameter tahun tidak valid'}), 400

    api_key = session.get('gemini_api_key') or Config.GEMINI_API_KEY or os.environ.get('GEMINI_API_KEY')
    if not api_key:
        return jsonify({
            'status': 'error',
            'message': 'Gemini API Key belum dimasukkan. Silakan masukkan API Key di panel sebelah kanan.'
        }), 400

    try:
        narrative = generate_executive_narrative(year, api_key=api_key)
        
        # Simpan ke database agar permanen
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute("UPDATE rapor_files SET executive_narrative = ? WHERE year = ?", (narrative, year))
        conn.commit()
        conn.close()

        return jsonify({
            'status': 'success',
            'narrative': narrative
        })
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': str(e)
        }), 500

@app.route('/narasi/update-key', methods=['POST'])
def update_key():
    key = request.form.get('api_key', '').strip()
    if key:
        session['gemini_api_key'] = key
        flash("Gemini API Key berhasil disimpan untuk sesi ini.", "success")
    else:
        session.pop('gemini_api_key', None)
        flash("Menggunakan konfigurasi API Key standar.", "info")
    return redirect(url_for('narasi'))

@app.route('/export/arkas')
def export_arkas():
    years = get_available_years()
    if not years:
        flash("Belum ada data untuk diekspor.", "error")
        return redirect(url_for('upload'))

    selected_year = request.args.get('year', type=int)
    if not selected_year or selected_year not in years:
        selected_year = years[0]

    stream = export_arkas_excel(selected_year)
    return send_file(
        stream,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        as_attachment=True,
        download_name=f"Rujukan_ARKAS_SMPN2_Bojonegoro_{selected_year}.xlsx"
    )

@app.route('/upload', methods=['GET', 'POST'])
def upload():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT id, filename, year, school_npsn, row_count, imported_at FROM rapor_files ORDER BY year DESC")
    uploaded_files = [dict(r) for r in cursor.fetchall()]
    conn.close()

    if request.method == 'POST':
        if 'rapor_file' not in request.files:
            flash("Tidak ada file yang dipilih.", "error")
            return redirect(request.url)

        file = request.files['rapor_file']
        if file.filename == '':
            flash("Silakan pilih file Rapor .xlsx terlebih dahulu.", "error")
            return redirect(request.url)

        if not file.filename.endswith('.xlsx'):
            flash("Format file harus berupa Excel (.xlsx).", "error")
            return redirect(request.url)

        filename = secure_filename(file.filename)
        save_path = Config.UPLOAD_FOLDER / filename
        file.save(save_path)

        try:
            file_id, year = ingest_rapor_file(save_path)
            school = get_active_school()
            flash(f"Berhasil mengimpor Rapor Pendidikan Tahun {year} untuk {school['name']} (NPSN: {school['npsn']})!", "success")
            return redirect(url_for('index', year=year))
        except Exception as e:
            flash(f"Gagal memproses file: {str(e)}", "error")
            return redirect(request.url)

    return render_template('upload.html', uploaded_files=uploaded_files)

@app.route('/delete/<int:year>', methods=['POST'])
def delete_year(year):
    """Menghapus data rapor, hasil analisa, dan rekomendasi untuk tahun tertentu"""
    conn = get_db()
    cursor = conn.cursor()

    # Cari nama file untuk dihapus dari uploads jika ada
    cursor.execute("SELECT filename FROM rapor_files WHERE year = ?", (year,))
    row = cursor.fetchone()
    if row and row['filename']:
        uploaded_file_path = Config.UPLOAD_FOLDER / row['filename']
        if uploaded_file_path.exists():
            try:
                uploaded_file_path.unlink()
            except Exception:
                pass

    # Hapus seluruh data terkait tahun tersebut
    cursor.execute("DELETE FROM indicator_scores WHERE year = ?", (year,))
    cursor.execute("DELETE FROM rekomendasi_pbd WHERE year = ?", (year,))
    cursor.execute("DELETE FROM rapor_files WHERE year = ?", (year,))

    conn.commit()
    conn.close()

    flash(f"Data Rapor Pendidikan Tahun {year} beserta seluruh hasil analisanya berhasil dihapus.", "success")
    return redirect(url_for('upload'))

# Error handlers
@app.errorhandler(404)
def not_found(e):
    return "Halaman tidak ditemukan", 404

@app.errorhandler(500)
def server_error(e):
    return "Terjadi kesalahan internal server", 500

if __name__ == '__main__':
    # Local development server
    app.run(host='127.0.0.1', port=5000, debug=True)
