# -*- coding: utf-8 -*-
"""
Ingestion Service: Saves parsed Rapor Pendidikan files into SQLite Database.
"""
import os
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 config import Config
from core.db import get_db, init_db
from core.parser import parse_rapor_file
from core.arkas_catalog import seed_arkas_catalog_from_simantap

def ingest_rapor_file(file_path):
    """
    Mem-parse file dan menyimpan seluruh skor serta rekomendasi ke SQLite.
    Jika tahun sudah ada, data tahun tersebut diperbarui (replace).
    """
    metadata, scores, rekom_prioritas, rekom_keseluruhan = parse_rapor_file(file_path)
    year = metadata['year']

    conn = get_db()
    cursor = conn.cursor()

    # Cek apakah file yang diupload milik sekolah yang berbeda dengan yang ada di DB
    cursor.execute("SELECT school_npsn, school_name FROM rapor_files LIMIT 1")
    existing = cursor.fetchone()
    if existing:
        old_npsn = (existing['school_npsn'] or '').strip()
        old_name = (existing['school_name'] or '').strip().upper()
        new_npsn = (metadata['school_npsn'] or '').strip()
        new_name = (metadata['school_name'] or '').strip().upper()

        is_diff_npsn = (len(old_npsn) == 8 and len(new_npsn) == 8 and old_npsn != new_npsn)
        is_diff_name = (old_name != new_name and not (old_npsn == new_npsn and len(old_npsn) == 8))

        if is_diff_npsn or is_diff_name:
            print(f"[AUTO-SWITCH] Sekolah berbeda terdeteksi ({old_name} -> {new_name}). Mengosongkan tabel database lama untuk sekolah baru...")
            cursor.execute("DELETE FROM indicator_scores")
            cursor.execute("DELETE FROM rekomendasi_pbd")
            cursor.execute("DELETE FROM rapor_files")
            conn.commit()
            # Bersihkan file Excel lama di folder uploads kecuali file yang sedang diimpor
            try:
                current_abs = str(Path(file_path).resolve())
                for f in Config.UPLOAD_FOLDER.glob("*"):
                    if f.is_file() and str(f.resolve()) != current_abs:
                        f.unlink()
            except Exception:
                pass

    # 1. Masukkan atau ganti catatan rapor_files
    cursor.execute('''
    INSERT INTO rapor_files (filename, year, school_npsn, school_name, row_count)
    VALUES (?, ?, ?, ?, ?)
    ON CONFLICT(year) DO UPDATE SET
        filename = excluded.filename,
        school_npsn = excluded.school_npsn,
        school_name = excluded.school_name,
        imported_at = CURRENT_TIMESTAMP,
        row_count = excluded.row_count
    ''', (
        metadata['filename'],
        year,
        metadata['school_npsn'],
        metadata['school_name'],
        metadata['row_count']
    ))

    cursor.execute("SELECT id FROM rapor_files WHERE year = ?", (year,))
    file_id = cursor.fetchone()[0]

    # 2. Hapus skor lama jika ada untuk tahun ini
    cursor.execute("DELETE FROM indicator_scores WHERE year = ?", (year,))

    # Masukkan skor baru
    for s in scores:
        cursor.execute('''
        INSERT INTO indicator_scores (
            file_id, year, code, name, full_text, capaian_label,
            score, score_prev, delta_yoy, is_comparable, is_red_flag,
            red_flag_reason, rank_kabkota, rank_nasional, source_data
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            file_id, year, s['code'], s['name'], s['full_text'], s['capaian_label'],
            s['score'], s['score_prev'], s['delta_yoy'], s['is_comparable'],
            s['is_red_flag'], s['red_flag_reason'], s['rank_kabkota'],
            s['rank_nasional'], s['source_data']
        ))

    # 3. Hapus dan masukkan rekomendasi PBD
    cursor.execute("DELETE FROM rekomendasi_pbd WHERE year = ?", (year,))

    for r in rekom_prioritas + rekom_keseluruhan:
        cursor.execute('''
        INSERT INTO rekomendasi_pbd (
            file_id, year, tipe, no_urut, identifikasi, capaian,
            skor, l1_code, l1_name, l2_code, l2_name, kegiatan_benahi,
            inspirasi_benahi, tautan_referensi, kegiatan_arkas_rapor
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            file_id, year, r['tipe'], r['no_urut'], r['identifikasi'],
            r['capaian'], r['skor'], r['l1_code'], r['l1_name'],
            r['l2_code'], r['l2_name'], r['kegiatan_benahi'],
            r['inspirasi_benahi'], r['tautan_referensi'], r.get('kegiatan_arkas_rapor')
        ))

    conn.commit()
    conn.close()

    print(f"Ingesti Sukses: {metadata['filename']} (Tahun {year}) - {len(scores)} skor, {len(rekom_prioritas)} prioritas.")
    return file_id, year

def ingest_all_local_rapor():
    """Mengimpor seluruh file Rapor di data/rapor_raw"""
    init_db()
    seed_arkas_catalog_from_simantap()
    
    raw_files = sorted(list(Config.RAW_DIR.glob("*.xlsx")))
    results = []
    for f in raw_files:
        try:
            res = ingest_rapor_file(f)
            results.append(res)
        except Exception as e:
            print(f"Error mengimpor {f.name}: {str(e)}")
    return results

if __name__ == '__main__':
    ingest_all_local_rapor()
