# -*- coding: utf-8 -*-
"""
Excel Exporter for Rujukan Kegiatan ARKAS & Rekomendasi PBD.
"""
import io
import sys
from pathlib import Path
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

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.analytics import get_pbd_recommendations_with_arkas, get_active_school

def export_arkas_excel(year):
    """
    Menghasilkan file Excel (.xlsx) lembar kerja ARKAS untuk tahun terpilih.
    """
    rekom_list = get_pbd_recommendations_with_arkas(year)
    school = get_active_school()

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = f"Rujukan ARKAS {year}"

    # Header Dokumen
    ws.merge_cells('A1:J1')
    ws['A1'] = f"LEMBAR KERJA RUJUKAN KEGIATAN ARKAS - {school['name']}"
    ws['A1'].font = Font(name='Calibri', size=14, bold=True, color='1E3A8A')
    ws['A1'].alignment = Alignment(horizontal='center', vertical='center')

    ws.merge_cells('A2:J2')
    ws['A2'] = f"Tahun Anggaran: {year} | NPSN: {school['npsn']} | Basis Data: Rapor Pendidikan & SIMANTAP"
    ws['A2'].font = Font(name='Calibri', size=10, italic=True)
    ws['A2'].alignment = Alignment(horizontal='center', vertical='center')

    # Header Tabel Sesuai Rapor PBD Resmi & Katalog Teknis
    headers = [
        "Ranking Prioritas",
        "Standar Pendidikan (SNP)",
        "Identifikasi (Indikator Prioritas)",
        "Akar Masalah (Level 2)",
        "Kegiatan Benahi",
        "Kegiatan ARKAS (Rujukan Rapor PBD)",
        "Kode Rekening ARKAS",
        "Kegiatan Belanja ARKAS",
        "Membutuhkan Biaya?",
        "Tautan Referensi Benahi"
    ]

    fill_header = PatternFill(start_color="1E3A8A", end_color="1E3A8A", fill_type="solid")
    font_header = Font(name='Calibri', size=10, bold=True, color="FFFFFF")
    thin_border = Border(
        left=Side(style='thin', color='CCCCCC'),
        right=Side(style='thin', color='CCCCCC'),
        top=Side(style='thin', color='CCCCCC'),
        bottom=Side(style='thin', color='CCCCCC')
    )

    for col_idx, h in enumerate(headers, 1):
        cell = ws.cell(row=4, column=col_idx, value=h)
        cell.fill = fill_header
        cell.font = font_header
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

    row_num = 5
    for item in rekom_list:
        arkas_items = item.get('kegiatan_arkas_list', [])
        snp_text = ", ".join(item.get('snp_list', [])) if item.get('snp_list') else 'Standar Terkait'
        rank_label = f"Rank #{item.get('no_urut')}"
        arkas_rapor_text = item.get('kegiatan_arkas_rapor') or '-'

        if arkas_items:
            for arkas in arkas_items:
                ws.cell(row=row_num, column=1, value=rank_label).alignment = Alignment(horizontal='center')
                ws.cell(row=row_num, column=2, value=arkas.get('snp_name') or snp_text)
                ws.cell(row=row_num, column=3, value=item.get('identifikasi'))
                ws.cell(row=row_num, column=4, value=f"{item.get('l2_code', '')} {item.get('l2_name', '')}")
                ws.cell(row=row_num, column=5, value=item.get('kegiatan_benahi'))
                ws.cell(row=row_num, column=6, value=arkas_rapor_text)
                ws.cell(row=row_num, column=7, value=arkas.get('kode_arkas') or '-').alignment = Alignment(horizontal='center')
                ws.cell(row=row_num, column=8, value=arkas.get('kegiatan_arkas'))
                ws.cell(row=row_num, column=9, value=arkas.get('membutuhkan_biaya') or 'Ya').alignment = Alignment(horizontal='center')
                ws.cell(row=row_num, column=10, value=item.get('tautan_referensi') or '-')

                for c in range(1, 11):
                    ws.cell(row=row_num, column=c).border = thin_border
                    ws.cell(row=row_num, column=c).font = Font(name='Calibri', size=9)
                row_num += 1
        else:
            ws.cell(row=row_num, column=1, value=rank_label).alignment = Alignment(horizontal='center')
            ws.cell(row=row_num, column=2, value=snp_text)
            ws.cell(row=row_num, column=3, value=item.get('identifikasi'))
            ws.cell(row=row_num, column=4, value=f"{item.get('l2_code', '')} {item.get('l2_name', '')}")
            ws.cell(row=row_num, column=5, value=item.get('kegiatan_benahi'))
            ws.cell(row=row_num, column=6, value=arkas_rapor_text)
            ws.cell(row=row_num, column=7, value='-').alignment = Alignment(horizontal='center')
            ws.cell(row=row_num, column=8, value='Non-Biaya (Dukungan Manajemen Mandiri)')
            ws.cell(row=row_num, column=9, value='Tidak').alignment = Alignment(horizontal='center')
            ws.cell(row=row_num, column=10, value=item.get('tautan_referensi') or '-')

            for c in range(1, 11):
                ws.cell(row=row_num, column=c).border = thin_border
                ws.cell(row=row_num, column=c).font = Font(name='Calibri', size=9)
            row_num += 1

    # Lebar kolom otomatis
    col_widths = {1: 14, 2: 24, 3: 25, 4: 25, 5: 32, 6: 35, 7: 15, 8: 35, 9: 14, 10: 30}
    for col_idx, width in col_widths.items():
        ws.column_dimensions[get_column_letter(col_idx)].width = width

    ws.row_dimensions[4].height = 28

    # Sheet 2: Inspirasi Kegiatan Tidak Berbiaya (Non-ARKAS)
    ws_non = wb.create_sheet(title=f"Non-ARKAS {year}")
    ws_non.merge_cells('A1:G1')
    ws_non['A1'] = f"INSPIRASI KEGIATAN TIDAK BERBIAYA (NON-ARKAS) - {school['name']}"
    ws_non['A1'].font = Font(name='Calibri', size=14, bold=True, color="1E3A8A")
    ws_non['A1'].alignment = Alignment(horizontal='center', vertical='center')

    ws_non.merge_cells('A2:G2')
    ws_non['A2'] = f"Tahun: {year} | Fokus: Tata Kelola Internal, Kombel & Pembelajaran Tanpa Pos Belanja Fisik"
    ws_non['A2'].font = Font(name='Calibri', size=10, italic=True)
    ws_non['A2'].alignment = Alignment(horizontal='center', vertical='center')

    headers_non = [
        "Ranking Prioritas",
        "Standar Pendidikan (SNP)",
        "Identifikasi (Indikator Prioritas)",
        "Akar Masalah (Level 2)",
        "Inspirasi Kegiatan Tidak Berbiaya (Non-ARKAS)",
        "Program / Bentuk Benahi",
        "Membutuhkan Biaya?"
    ]

    fill_non = PatternFill(start_color="B45309", end_color="B45309", fill_type="solid")
    for col_idx, h in enumerate(headers_non, 1):
        cell = ws_non.cell(row=4, column=col_idx, value=h)
        cell.fill = fill_non
        cell.font = font_header
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

    r_non = 5
    for item in rekom_list:
        non_items = item.get('kegiatan_non_arkas_list', [])
        snp_text = ", ".join(item.get('snp_list', [])) if item.get('snp_list') else 'Standar Terkait'
        rank_label = f"Rank #{item.get('no_urut')}"
        for non in non_items:
            ws_non.cell(row=r_non, column=1, value=rank_label).alignment = Alignment(horizontal='center')
            ws_non.cell(row=r_non, column=2, value=non.get('snp_name') or snp_text)
            ws_non.cell(row=r_non, column=3, value=item.get('identifikasi'))
            ws_non.cell(row=r_non, column=4, value=f"{item.get('l2_code', '')} {item.get('l2_name', '')}")
            ws_non.cell(row=r_non, column=5, value=non.get('kegiatan_arkas'))
            ws_non.cell(row=r_non, column=6, value=non.get('kegiatan_benahi') or '-')
            ws_non.cell(row=r_non, column=7, value='Tidak').alignment = Alignment(horizontal='center')
            for c in range(1, 8):
                ws_non.cell(row=r_non, column=c).border = thin_border
                ws_non.cell(row=r_non, column=c).font = Font(name='Calibri', size=9)
            r_non += 1

    col_widths_non = {1: 14, 2: 24, 3: 25, 4: 25, 5: 42, 6: 35, 7: 16}
    for col_idx, width in col_widths_non.items():
        ws_non.column_dimensions[get_column_letter(col_idx)].width = width
    ws_non.row_dimensions[4].height = 28

    stream = io.BytesIO()
    wb.save(stream)
    stream.seek(0)
    return stream
