# -*- coding: utf-8 -*-
"""
Deterministic Excel Parser for Rapor Pendidikan SMP Negeri 2 Bojonegoro (20504414).
"""
import re
import os
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.indicator_dictionary import PRIORITY_INDICATORS, is_comparable, is_excluded_for_smp

def clean_number(val):
    """Mengonversi nilai sel (teks atau float) menjadi float standar"""
    if val is None:
        return None
    if isinstance(val, (int, float)):
        return float(val)
    
    val_str = str(val).strip()
    if not val_str or 'tidak tersedia' in val_str.lower():
        return None
    
    # Bersihkan simbol persen dan spasi
    val_str = val_str.replace('%', '').strip()
    # Ganti koma desimal Indonesia menjadi titik
    val_str = val_str.replace(',', '.')
    
    try:
        return float(val_str)
    except ValueError:
        return None

def validate_school_file(file_path):
    """Validasi apakah file merupakan dokumen resmi Rapor Pendidikan / PBD (.xlsx)"""
    try:
        wb = openpyxl.load_workbook(file_path, read_only=True, data_only=True)
        has_laporan = any('laporan rapor' in s.lower() for s in wb.sheetnames)
        if not has_laporan:
            return False, "Format tidak dikenali: Sheet '2. LAPORAN RAPOR' tidak ditemukan dalam file Excel."
        return True, "Validasi struktur file Rapor Pendidikan berhasil."
    except Exception as e:
        return False, f"Gagal membaca file Excel: {str(e)}"

def extract_school_identity(wb, filename, ws_laporan):
    """Mengekstrak Nama Sekolah dan NPSN secara otomatis dari konten sheet atau nama file."""
    school_name = None
    school_npsn = None

    # 1. Coba ekstrak Nama Sekolah dari judul Sheet '2. LAPORAN RAPOR' (Baris 1-3)
    for r in range(1, 5):
        val = ws_laporan.cell(r, 1).value
        if val:
            m = re.search(r'LAPORAN\s+RAPOR\s+PENDIDIKAN\s+(.+?)\s+TAHUN\s+202\d', str(val), re.I)
            if m:
                school_name = m.group(1).strip()
                break

    # 2. Cadangan: Cek judul Sheet Rekomendasi (2.1 / 2.2)
    if not school_name:
        for sname in wb.sheetnames:
            if 'rekom' in sname.lower():
                ws_rek = wb[sname]
                for r in range(1, 4):
                    val = ws_rek.cell(r, 1).value
                    if val:
                        m = re.search(r'REKOMENDASI\s+(?:KESELURUHAN|PRIORITAS)\s+PBD\s+(.+?)\s+TAHUN\s+202\d', str(val), re.I)
                        if m:
                            school_name = m.group(1).strip()
                            break
                if school_name:
                    break

    # 3. Coba ekstrak NPSN (8 digit) dari Sheet RKT/RKJP atau nama file
    for sname in wb.sheetnames:
        if 'rkt' in sname.lower() or 'rkjp' in sname.lower():
            ws_rkt = wb[sname]
            for r in range(5, 11):
                c1 = str(ws_rkt.cell(r, 1).value or '').strip().lower()
                if 'nama satuan' in c1 and not school_name:
                    for c in range(2, 6):
                        v = str(ws_rkt.cell(r, c).value or '').strip()
                        if v:
                            school_name = v
                            break
                if 'npsn' in c1 and not school_npsn:
                    for c in range(2, 6):
                        v = str(ws_rkt.cell(r, c).value or '').strip()
                        m_n = re.search(r'\b(\d{8})\b', v)
                        if m_n:
                            school_npsn = m_n.group(1)
                            break

    # 4. Ekstrak NPSN 8 digit dari nama file (pola: RAPOR-PBD-NAMA-SEKOLAH-12345678-DATA-2025.xlsx)
    if not school_npsn:
        m_npsn = re.search(r'(?:^|[-_\s])(\d{8})(?:[-_\s.]|$)', filename)
        if m_npsn:
            school_npsn = m_npsn.group(1)

    # 5. Ekstrak Nama Sekolah dari nama file jika belum ketemu di konten
    if not school_name:
        m_fn = re.search(r'RAPOR-PBD-(.+?)-(?:\d{8}-)?DATA-202\d', filename, re.I)
        if m_fn:
            school_name = m_fn.group(1).replace('-', ' ').strip().upper()

    if not school_name:
        school_name = "SATUAN PENDIDIKAN (AUTO)"
    if not school_npsn:
        # Jika nama file tidak memuat NPSN, gunakan kode ringkas atau '-'
        school_npsn = Config.NPSN if "SMP NEGERI 2 BOJONEGORO" in school_name.upper() else "-"

    return school_name.upper(), school_npsn

def parse_rapor_file(file_path):
    """
    Mem-parse file Excel Rapor Pendidikan secara deterministik.
    Mengembalikan:
      - metadata: {year, school_name, school_npsn, filename}
      - scores: list of dict indikator
      - rekomendasi_prioritas: list of dict
      - rekomendasi_keseluruhan: list of dict
    """
    valid, msg = validate_school_file(file_path)
    if not valid:
        raise ValueError(msg)

    wb = openpyxl.load_workbook(file_path, data_only=True)
    filename = Path(file_path).name

    # 1. Temukan Sheet Laporan Rapor
    laporan_sheet_name = None
    for name in wb.sheetnames:
        if 'laporan rapor' in name.lower():
            laporan_sheet_name = name
            break
    
    if not laporan_sheet_name:
        raise ValueError("Sheet '2. LAPORAN RAPOR' tidak ditemukan dalam file Excel.")

    ws_laporan = wb[laporan_sheet_name]

    # Ekstrak Tahun dari judul sheet (Baris 1 atau 2)
    report_year = None
    for r in range(1, 6):
        c_val = ws_laporan.cell(r, 1).value
        if c_val and ('TAHUN' in str(c_val).upper() or '202' in str(c_val)):
            m = re.search(r'202\d', str(c_val))
            if m:
                report_year = int(m.group(0))
                break

    if not report_year:
        # Cadangan dari nama file
        m = re.search(r'202\d', filename)
        if m:
            report_year = int(m.group(0))
        else:
            report_year = 2025 # Default fallback

    # Cari Baris Header Tabel
    header_row_idx = None
    col_map = {}
    for r in range(1, 15):
        row_vals = [ws_laporan.cell(r, c).value for c in range(1, 15)]
        str_vals = [str(v).strip().lower() if v else '' for v in row_vals]
        if any('no' in v for v in str_vals) and any('indikator' in v for v in str_vals):
            header_row_idx = r
            for idx, val in enumerate(str_vals, start=1):
                if not val:
                    continue
                if 'no' == val or val.startswith('no') or 'nomor indikator' in val:
                    col_map['no'] = idx
                elif 'indikator' in val:
                    col_map['indikator'] = idx
                elif ('capaian' in val or 'label capaian' in val) and 'definisi' not in val and 'perubahan' not in val and 'nilai' not in val:
                    col_map['capaian'] = idx
                elif (('skor rapor' in val or 'nilai capaian' in val) and str(report_year) in val) or (val == 'nilai sekolah anda'):
                    col_map['skor'] = idx
                elif 'definisi' in val:
                    col_map['definisi'] = idx
                elif 'perubahan' in val:
                    col_map['perubahan'] = idx
                elif ('skor rapor' in val or 'nilai capaian' in val) and (str(report_year - 1) in val or 'tahun lalu' in val):
                    col_map['skor_prev'] = idx
                elif 'peringkat di kab' in val:
                    col_map['rank_kab'] = idx
                elif 'peringkat secara nasional' in val:
                    col_map['rank_nas'] = idx
                elif 'sumber data' in val:
                    col_map['sumber'] = idx
            break

    if not header_row_idx:
        raise ValueError("Header tabel pada sheet 'Laporan Rapor' tidak dapat dideteksi.")

    # Jika skor / skor_prev belum terpetakan spesifik tahun, gunakan fallback posisi
    if 'skor' not in col_map:
        col_map['skor'] = col_map.get('capaian', 3) + 1
    if 'skor_prev' not in col_map:
        col_map['skor_prev'] = col_map.get('perubahan', 6) + 1

    scores = []
    current_parent_code = None

    for r in range(header_row_idx + 1, ws_laporan.max_row + 1):
        raw_no = ws_laporan.cell(r, col_map.get('no', 1)).value
        raw_indikator = ws_laporan.cell(r, col_map.get('indikator', 2)).value
        
        if not raw_indikator and not raw_no:
            continue

        raw_no_str = str(raw_no).strip() if raw_no is not None else ''
        raw_indikator_str = str(raw_indikator).strip() if raw_indikator is not None else ''

        # Tentukan Kode Indikator
        if raw_no_str:
            code = raw_no_str
            if '.' in code and not code.endswith('.skor') and len(code.split('.')) == 2:
                current_parent_code = code
        else:
            # Baris proporsi / sub-distribusi tanpa nomor
            if 'di atas' in raw_indikator_str.lower():
                code = f"{current_parent_code}.prop_di_atas"
            elif 'mencapai' in raw_indikator_str.lower():
                code = f"{current_parent_code}.prop_mencapai"
            elif 'jauh di bawah' in raw_indikator_str.lower():
                code = f"{current_parent_code}.prop_jauh_di_bawah"
            elif 'di bawah' in raw_indikator_str.lower():
                code = f"{current_parent_code}.prop_di_bawah"
            else:
                code = f"{current_parent_code}.sub_{r}"

        # Abaikan indikator SMK yang tidak berlaku untuk SMP
        if is_excluded_for_smp(code):
            continue

        # Ekstrak nama dan deskripsi
        parts = raw_indikator_str.split('\n')
        name = parts[0].strip() if parts else raw_indikator_str
        full_text = raw_indikator_str

        raw_capaian = ws_laporan.cell(r, col_map.get('capaian', 3)).value
        capaian_label = str(raw_capaian).strip() if raw_capaian is not None else ''

        score_val = clean_number(ws_laporan.cell(r, col_map.get('skor', 4)).value)
        score_prev_val = clean_number(ws_laporan.cell(r, col_map.get('skor_prev', 7)).value)

        # Hitung YoY Delta secara deterministik
        comparable = is_comparable(code, report_year, report_year - 1)
        delta_yoy = None
        if comparable and score_val is not None and score_prev_val is not None:
            delta_yoy = round(score_val - score_prev_val, 2)

        # Evaluasi Red Flag v2
        is_red_flag = 0
        red_flag_reason = []

        if 'kurang' in capaian_label.lower():
            is_red_flag = 1
            red_flag_reason.append("Kategori Capaian Resmi: Kurang")

        # Jika indikator prioritas dan turun >= 5 poin
        if code in PRIORITY_INDICATORS and delta_yoy is not None and delta_yoy <= -5.0:
            is_red_flag = 1
            red_flag_reason.append(f"Penurunan Signifikan YoY: {delta_yoy} poin")

        # Anomali lonjakan data tak wajar (> +50 poin)
        if delta_yoy is not None and delta_yoy >= 50.0:
            red_flag_reason.append(f"Lonjakan Ekstrem YoY: +{delta_yoy} poin (Perlu Verifikasi)")

        rank_kab = ws_laporan.cell(r, col_map.get('rank_kab', 8)).value
        rank_nas = ws_laporan.cell(r, col_map.get('rank_nas', 9)).value
        sumber = ws_laporan.cell(r, col_map.get('sumber', 10)).value

        scores.append({
            'year': report_year,
            'code': code,
            'name': name,
            'full_text': full_text,
            'capaian_label': capaian_label,
            'score': score_val,
            'score_prev': score_prev_val,
            'delta_yoy': delta_yoy,
            'is_comparable': 1 if comparable else 0,
            'is_red_flag': is_red_flag,
            'red_flag_reason': "; ".join(red_flag_reason) if red_flag_reason else None,
            'rank_kabkota': str(rank_kab).strip() if rank_kab else None,
            'rank_nasional': str(rank_nas).strip() if rank_nas else None,
            'source_data': str(sumber).strip() if sumber else None
        })

    # 2. Parse Rekomendasi Prioritas & Keseluruhan
    rekom_prioritas = _parse_sheet_rekomendasi(wb, '2.2 REKOM. PRIORITAS', report_year, 'PRIORITAS')
    rekom_keseluruhan = _parse_sheet_rekomendasi(wb, '2.1 REKOM. KESELURUHAN', report_year, 'KESELURUHAN')

    school_name, school_npsn = extract_school_identity(wb, filename, ws_laporan)

    metadata = {
        'filename': filename,
        'year': report_year,
        'school_npsn': school_npsn,
        'school_name': school_name,
        'row_count': len(scores)
    }

    return metadata, scores, rekom_prioritas, rekom_keseluruhan

def _parse_sheet_rekomendasi(wb, sheet_name, report_year, tipe):
    """Helper untuk mem-parse sheet rekomendasi PBD 2.1 dan 2.2"""
    target_sheet = None
    for s in wb.sheetnames:
        if sheet_name.lower() in s.lower() or tipe.lower() in s.lower():
            target_sheet = s
            break

    if not target_sheet or target_sheet not in wb.sheetnames:
        return []

    ws = wb[target_sheet]
    items = []

    # Cari baris header (biasanya baris 6 atau 7)
    start_row = 8
    for r in range(1, 10):
        val = str(ws.cell(r, 1).value).strip() if ws.cell(r, 1).value else ''
        val2 = str(ws.cell(r, 2).value).strip() if ws.cell(r, 2).value else ''
        if '1' in val and len(val) <= 2:
            start_row = r
            break
        elif 'identifikasi' in val2.lower():
            start_row = r + 2
            break

    for r in range(start_row, ws.max_row + 1):
        no_urut = ws.cell(r, 1).value
        identifikasi = ws.cell(r, 2).value
        capaian = ws.cell(r, 3).value
        skor = ws.cell(r, 4).value
        l1 = ws.cell(r, 5).value
        l2 = ws.cell(r, 6).value
        kegiatan_benahi = ws.cell(r, 7).value
        inspirasi = ws.cell(r, 8).value
        tautan = ws.cell(r, 9).value
        kegiatan_arkas = ws.cell(r, 10).value

        if not identifikasi and not l1 and not l2:
            continue

        # Ekstrak Kode L1 dan Nama L1
        l1_str = str(l1).strip() if l1 else ''
        l1_code, l1_name = None, l1_str
        m_l1 = re.match(r'^([A-E]\.\d+)\s*(.*)', l1_str)
        if m_l1:
            l1_code = m_l1.group(1)
            l1_name = m_l1.group(2).strip()

        # Ekstrak Kode L2 dan Nama L2
        l2_str = str(l2).strip() if l2 else ''
        l2_code, l2_name = None, l2_str
        m_l2 = re.match(r'^([A-E]\.\d+\.\d+)\s*(.*)', l2_str)
        if m_l2:
            l2_code = m_l2.group(1)
            l2_name = m_l2.group(2).strip()

        items.append({
            'year': report_year,
            'tipe': tipe,
            'no_urut': str(no_urut).strip() if no_urut else None,
            'identifikasi': str(identifikasi).strip() if identifikasi else None,
            'capaian': str(capaian).strip() if capaian else None,
            'skor': clean_number(skor),
            'l1_code': l1_code,
            'l1_name': l1_name,
            'l2_code': l2_code,
            'l2_name': l2_name,
            'kegiatan_benahi': str(kegiatan_benahi).strip() if kegiatan_benahi else None,
            'inspirasi_benahi': str(inspirasi).strip() if inspirasi else None,
            'tautan_referensi': str(tautan).strip() if tautan else None,
            'kegiatan_arkas_rapor': str(kegiatan_arkas).strip() if kegiatan_arkas else None
        })

    return items
