# -*- coding: utf-8 -*-
"""
Analytics Engine: Calculates longitudinal trends, priority KPIs, and Red Flag matrix v2.
"""
import sys
from pathlib import Path

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

from core.db import get_db
from core.indicator_dictionary import PRIORITY_INDICATORS, is_comparable

# Palet warna resmi Velzon Theme untuk 7 Indikator Prioritas
INDICATOR_PALETTE = {
    'A.1': {'color': '#405189', 'cluster': 'akademik', 'name': 'Kemampuan Literasi'},
    'A.2': {'color': '#0ab39c', 'cluster': 'akademik', 'name': 'Kemampuan Numerasi'},
    'A.3': {'color': '#3577f1', 'cluster': 'akademik', 'name': 'Karakter'},
    'D.1': {'color': '#f7b84b', 'cluster': 'lingkungan', 'name': 'Kualitas Pembelajaran'},
    'D.4': {'color': '#299cdb', 'cluster': 'lingkungan', 'name': 'Iklim Keamanan Sekolah'},
    'D.8': {'color': '#6559cc', 'cluster': 'lingkungan', 'name': 'Iklim Kebinekaan'},
    'E.2': {'color': '#f06548', 'cluster': 'anggaran', 'name': 'Proporsi Belanja Mutu'},
}

def get_active_school():
    """Mengambil identitas sekolah aktif (Nama & NPSN) dari database"""
    try:
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute("SELECT school_name, school_npsn FROM rapor_files ORDER BY year DESC LIMIT 1")
        row = cursor.fetchone()
        conn.close()
        if row:
            return {'name': row['school_name'], 'npsn': row['school_npsn']}
    except Exception:
        pass
    return {'name': 'SIMANTAP — BELUM ADA DATA SEKOLAH', 'npsn': '-'}

def get_available_years():
    """Mengambil daftar tahun rapor yang tersedia di database (descending)"""
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT DISTINCT year FROM rapor_files ORDER BY year DESC")
    years = [r[0] for r in cursor.fetchall()]
    conn.close()
    return years

def get_chronological_years():
    """Mengambil daftar tahun rapor secara kronologis (ascending)"""
    years = get_available_years()
    return sorted(years)

def get_kpi_cards(target_year=None):
    """
    Mengambil data 7 Indikator Prioritas untuk Dashboard (A.1, A.2, A.3, D.1, D.4, D.8, E.2)
    Lengkap dengan riwayat deret multi-tahun untuk mini-sparkline di setiap kartu.
    """
    conn = get_db()
    cursor = conn.cursor()

    all_years = get_chronological_years()

    if target_year is None:
        years = get_available_years()
        if not years:
            conn.close()
            return []
        target_year = years[0]

    kpi_results = []
    for code, meta in PRIORITY_INDICATORS.items():
        cursor.execute('''
        SELECT code, name, capaian_label, score, score_prev, delta_yoy, is_red_flag,
               red_flag_reason, rank_kabkota, rank_nasional
        FROM indicator_scores
        WHERE year = ? AND code = ?
        ''', (target_year, code))
        row = cursor.fetchone()

        # Riwayat multi-tahun untuk sparkline
        history_series = []
        for y in all_years:
            cursor.execute("SELECT score FROM indicator_scores WHERE year = ? AND code = ?", (y, code))
            h_row = cursor.fetchone()
            history_series.append(h_row[0] if h_row and h_row[0] is not None else None)

        if row:
            item = dict(row)
        else:
            item = {
                'code': code,
                'name': meta['name'],
                'capaian_label': 'Tidak Tersedia',
                'score': None,
                'score_prev': None,
                'delta_yoy': None,
                'is_red_flag': 0,
                'red_flag_reason': None,
                'rank_kabkota': '-',
                'rank_nasional': '-'
            }

        # Bersihkan label capaian untuk badge ringkas (tanpa teks kurung panjang)
        raw_capaian = item.get('capaian_label') or ''
        short_capaian = raw_capaian.split('(')[0].strip() if raw_capaian else 'Tidak Tersedia'
        if not short_capaian:
            short_capaian = 'Tidak Tersedia'

        cap_lower = short_capaian.lower()
        if 'baik' in cap_lower or 'mencapai' in cap_lower or 'di atas' in cap_lower:
            badge_color = 'success'
        elif 'kurang' in cap_lower or 'di bawah' in cap_lower:
            badge_color = 'danger'
        elif 'sedang' in cap_lower:
            badge_color = 'warning'
        else:
            badge_color = 'secondary'

        item['short_capaian'] = short_capaian
        item['badge_color'] = badge_color
        
        palette = INDICATOR_PALETTE.get(code, {'color': '#405189', 'cluster': 'akademik'})
        item['domain'] = meta['domain']
        item['unit'] = meta['unit']
        item['color'] = palette['color']
        item['cluster'] = palette['cluster']
        item['history'] = history_series
        item['years'] = all_years
        kpi_results.append(item)

    conn.close()
    return kpi_results

def get_red_flags(target_year=None):
    """Mengambil seluruh indikator yang berstatus Red Flag pada tahun target"""
    conn = get_db()
    cursor = conn.cursor()

    if target_year is None:
        years = get_available_years()
        if not years:
            conn.close()
            return []
        target_year = years[0]

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

def get_multi_year_line_chart_data():
    """
    Menyusun payload data ApexCharts multi-tahun untuk 7 Indikator Prioritas.
    Returns:
      - years: [2023, 2024, 2025, 2026]
      - datasets: list of dict (name, code, data, color, cluster, unit)
      - benchmarks: garis batas standar resmi (70=Baik, 40=Kurang)
    """
    years = get_chronological_years()
    conn = get_db()
    cursor = conn.cursor()

    datasets = []
    for code, meta in PRIORITY_INDICATORS.items():
        palette = INDICATOR_PALETTE.get(code, {'color': '#405189', 'cluster': 'akademik'})
        scores_series = []
        
        for y in years:
            cursor.execute('''
            SELECT score FROM indicator_scores
            WHERE year = ? AND code = ?
            ''', (y, code))
            row = cursor.fetchone()
            scores_series.append(row[0] if row and row[0] is not None else None)

        datasets.append({
            'code': code,
            'name': f"{code} - {meta['name']}",
            'short_name': meta['name'],
            'data': scores_series,
            'color': palette['color'],
            'cluster': palette['cluster'],
            'unit': meta['unit'],
            'domain': meta['domain']
        })

    conn.close()
    return {
        'years': years,
        'datasets': datasets,
        'benchmarks': {
            'baik': 70.0,
            'kurang': 40.0
        }
    }

def get_pbd_recommendations_with_arkas(target_year=None):
    """
    Menggabungkan Rekomendasi PBD Prioritas dengan Master Katalog ARKAS secara deterministik
    Lengkap dengan pemetaan 8 Standar Nasional Pendidikan (SNP) yang bertanggung jawab.
    """
    conn = get_db()
    cursor = conn.cursor()

    if target_year is None:
        years = get_available_years()
        if not years:
            conn.close()
            return []
        target_year = years[0]

    cursor.execute('''
    SELECT id, no_urut, identifikasi, capaian, skor, l1_code, l1_name, l2_code, l2_name,
           kegiatan_benahi, inspirasi_benahi, tautan_referensi, kegiatan_arkas_rapor
    FROM rekomendasi_pbd
    WHERE year = ? AND tipe = 'PRIORITAS'
    ORDER BY CAST(no_urut AS INTEGER) ASC
    ''', (target_year,))
    rekom_list = [dict(r) for r in cursor.fetchall()]

    for item in rekom_list:
        l2 = item.get('l2_code')
        l1 = item.get('l1_code')

        # Ambil seluruh SNP yang terkait dengan indikator ini
        cursor.execute('''
        SELECT DISTINCT snp_name
        FROM master_arkas_katalog
        WHERE (l2_code = ? OR l1_code = ?) AND snp_name IS NOT NULL
        ''', (l2, l1))
        item['snp_list'] = [r[0] for r in cursor.fetchall() if r[0]]

        # Ambil daftar kegiatan ARKAS resmi lengkap dengan Nama SNP (Kegiatan Berbiaya)
        arkas_query = '''
        SELECT snp_name, kode_arkas, kegiatan_arkas, kegiatan_benahi, implementasi, membutuhkan_biaya
        FROM master_arkas_katalog
        WHERE (l2_code = ? OR l1_code = ?) AND membutuhkan_biaya = 'Ya'
        ORDER BY CASE WHEN l2_code = ? THEN 0 ELSE 1 END, id ASC
        LIMIT 6
        '''
        cursor.execute(arkas_query, (l2, l1, l2))
        item['kegiatan_arkas_list'] = [dict(r) for r in cursor.fetchall()]

        # Ambil daftar inspirasi kegiatan tidak berbiaya (Non-ARKAS)
        non_arkas_query = '''
        SELECT snp_name, kegiatan_arkas, kegiatan_benahi, implementasi, membutuhkan_biaya
        FROM master_arkas_katalog
        WHERE (l2_code = ? OR l1_code = ?) AND membutuhkan_biaya = 'Tidak'
        ORDER BY CASE WHEN l2_code = ? THEN 0 ELSE 1 END, id ASC
        LIMIT 6
        '''
        cursor.execute(non_arkas_query, (l2, l1, l2))
        item['kegiatan_non_arkas_list'] = [dict(r) for r in cursor.fetchall()]

    conn.close()
    return rekom_list
