# -*- coding: utf-8 -*-
"""
Database Manager for SIMANTAP Lokal (SQLite)
"""
import sqlite3
import os
import sys
from pathlib import Path

# Add project root to sys.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

def get_db():
    Config.DATA_DIR.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(Config.DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    Config.DATA_DIR.mkdir(parents=True, exist_ok=True)
    conn = get_db()
    cursor = conn.cursor()

    # 1. Tabel File Rapor yang diimpor
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS rapor_files (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        filename TEXT NOT NULL,
        year INTEGER NOT NULL UNIQUE,
        school_npsn TEXT NOT NULL,
        school_name TEXT NOT NULL,
        imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        row_count INTEGER DEFAULT 0,
        executive_narrative TEXT
    )
    ''')

    # 2. Tabel Skor Indikator Rapor Pendidikan
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS indicator_scores (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        file_id INTEGER NOT NULL,
        year INTEGER NOT NULL,
        code TEXT NOT NULL,
        name TEXT NOT NULL,
        full_text TEXT,
        capaian_label TEXT,
        score REAL,
        score_prev REAL,
        delta_yoy REAL,
        is_comparable INTEGER DEFAULT 1,
        is_red_flag INTEGER DEFAULT 0,
        red_flag_reason TEXT,
        rank_kabkota TEXT,
        rank_nasional TEXT,
        source_data TEXT,
        FOREIGN KEY (file_id) REFERENCES rapor_files (id),
        UNIQUE(year, code)
    )
    ''')

    # 3. Tabel Rekomendasi PBD (dari Sheet 2.1 & 2.2)
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS rekomendasi_pbd (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        file_id INTEGER NOT NULL,
        year INTEGER NOT NULL,
        tipe TEXT NOT NULL, -- 'PRIORITAS' atau 'KESELURUHAN'
        no_urut TEXT,
        identifikasi TEXT,
        capaian TEXT,
        skor REAL,
        l1_code TEXT,
        l1_name TEXT,
        l2_code TEXT,
        l2_name TEXT,
        kegiatan_benahi TEXT,
        inspirasi_benahi TEXT,
        tautan_referensi TEXT,
        kegiatan_arkas_rapor TEXT,
        FOREIGN KEY (file_id) REFERENCES rapor_files (id)
    )
    ''')

    # 4. Tabel Master Katalog Kegiatan ARKAS & Non-ARKAS
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS master_arkas_katalog (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        snp_name TEXT,
        identifikasi TEXT,
        akar_masalah TEXT,
        l1_code TEXT,
        l2_code TEXT,
        kegiatan_benahi TEXT,
        implementasi TEXT,
        membutuhkan_biaya TEXT, -- 'Ya' / 'Tidak'
        kode_arkas TEXT,        -- Contoh '02.02.01' atau NULL
        kegiatan_arkas TEXT,    -- Nama kegiatan ARKAS atau kegiatan non-ARKAS
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')

    # Migrasi aman untuk kolom tambahan jika DB lama sudah ada
    try:
        cursor.execute("ALTER TABLE rapor_files ADD COLUMN executive_narrative TEXT")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE rekomendasi_pbd ADD COLUMN kegiatan_arkas_rapor TEXT")
    except Exception:
        pass

    conn.commit()
    conn.close()
    print("Database SQLite initialized successfully at", Config.DB_PATH)

if __name__ == '__main__':
    init_db()
