# -*- coding: utf-8 -*-
"""
ARKAS Master Catalog Manager & Deterministic Recommendation Lookup.
Populates and queries master catalog kegiatan ARKAS & non-ARKAS.
"""
import os
import re
import sys
from pathlib import Path
import openpyxl

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

def seed_arkas_catalog_from_simantap(excel_path=None):
    """
    Mengimpor data katalog referensi kegiatan dari Rekap_Benahi_SIMANTAP.xlsx
    ke tabel SQLite master_arkas_katalog.
    """
    if excel_path is None:
        excel_path = Config.DATA_DIR.parent / "Rekap_Benahi_SIMANTAP.xlsx"

    if not Path(excel_path).exists():
        print(f"File katalog {excel_path} tidak ditemukan.")
        return 0

    wb = openpyxl.load_workbook(excel_path, data_only=True)
    ws = wb.active

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

    # Cek apakah sudah terisi
    cursor.execute("SELECT COUNT(*) FROM master_arkas_katalog")
    count = cursor.fetchone()[0]
    if count > 0:
        conn.close()
        return count

    inserted = 0
    for r in range(2, ws.max_row + 1):
        snp = ws.cell(r, 2).value
        identifikasi = ws.cell(r, 3).value
        akar_masalah = ws.cell(r, 4).value
        benahi = ws.cell(r, 5).value
        implementasi = ws.cell(r, 6).value
        biaya = ws.cell(r, 7).value
        kode_arkas = ws.cell(r, 8).value
        kegiatan_arkas = ws.cell(r, 9).value

        if not benahi and not kegiatan_arkas:
            continue

        # Ekstrak L1 dan L2 code dari kolom identifikasi jika ada
        # Format identifikasi: "A.1 Kemampuan Literasi - A.1.1 Kompetensi Membaca Teks Informasi"
        l1_code, l2_code = None, None
        if identifikasi:
            m_codes = re.findall(r'[A-E]\.\d+(?:\.\d+)?', str(identifikasi))
            if len(m_codes) >= 1:
                l1_code = m_codes[0]
            if len(m_codes) >= 2:
                l2_code = m_codes[1]

        cursor.execute('''
        INSERT INTO master_arkas_katalog (
            snp_name, identifikasi, akar_masalah, l1_code, l2_code,
            kegiatan_benahi, implementasi, membutuhkan_biaya, kode_arkas, kegiatan_arkas
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            str(snp).strip() if snp else None,
            str(identifikasi).strip() if identifikasi else None,
            str(akar_masalah).strip() if akar_masalah else None,
            l1_code,
            l2_code,
            str(benahi).strip() if benahi else None,
            str(implementasi).strip() if implementasi else None,
            str(biaya).strip() if biaya else 'Tidak',
            str(kode_arkas).strip() if kode_arkas else None,
            str(kegiatan_arkas).strip() if kegiatan_arkas else None
        ))
        inserted += 1

    conn.commit()
    conn.close()
    print(f"Berhasil mengimpor {inserted} kegiatan ke master_arkas_katalog.")
    return inserted

def get_arkas_recommendations_for_indicator(l1_code=None, l2_code=None):
    """
    Pencocokan deterministik 100% dari akar masalah indikator ke kode/nama kegiatan ARKAS
    """
    conn = get_db()
    cursor = conn.cursor()

    query = "SELECT * FROM master_arkas_katalog WHERE 1=1"
    params = []

    if l2_code:
        query += " AND l2_code = ?"
        params.append(l2_code)
    elif l1_code:
        query += " AND l1_code = ?"
        params.append(l1_code)

    cursor.execute(query, params)
    rows = [dict(r) for r in cursor.fetchall()]
    conn.close()
    return rows

if __name__ == '__main__':
    seed_arkas_catalog_from_simantap()
