#!/usr/bin/env python3
"""
Skrip Demonstrasi Automasi Agentic AI - Google Antigravity
Tugasan: Mengekstrak teks arahan pentadbir dan mengisikannya ke dalam Fail Excel Agihan Tugas Rasmi Sekolah
Lengkap dengan formula pengiraan mata beban guru secara automatik.
"""

import os
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

def build_excel_agihan_tugas():
    wb = Workbook()
    ws = wb.active
    ws.title = "Agihan_Tugas_2026"
    ws.views.sheetView[0].showGridLines = True

    # Palet Warna Korporat Sekolah KPM
    header_fill = PatternFill(start_color="1E3A8A", end_color="1E3A8A", fill_type="solid") # Dark Navy
    sub_fill = PatternFill(start_color="3B82F6", end_color="3B82F6", fill_type="solid")    # Royal Blue
    koko_fill = PatternFill(start_color="10B981", end_color="10B981", fill_type="solid")   # Emerald
    hem_fill = PatternFill(start_color="F59E0B", end_color="F59E0B", fill_type="solid")    # Amber
    total_fill = PatternFill(start_color="FEF3C7", end_color="FEF3C7", fill_type="solid")  # Light Yellow
    zebra_fill = PatternFill(start_color="F8FAFC", end_color="F8FAFC", fill_type="solid")

    font_title = Font(name="Arial", size=14, bold=True, color="1E3A8A")
    font_subtitle = Font(name="Arial", size=10, italic=True, color="475569")
    font_header = Font(name="Arial", size=11, bold=True, color="FFFFFF")
    font_bold = Font(name="Arial", size=10, bold=True)
    font_data = Font(name="Arial", size=10)

    thin_border = Border(
        left=Side(style='thin', color='CBD5E1'),
        right=Side(style='thin', color='CBD5E1'),
        top=Side(style='thin', color='CBD5E1'),
        bottom=Side(style='thin', color='CBD5E1')
    )

    # 1. Header Tajuk Sekolah
    ws.merge_cells("A1:G1")
    ws["A1"] = "SEKOLAH KEBANGSAAN SEKSYEN 9, SHAH ALAM"
    ws["A1"].font = font_title
    ws["A1"].alignment = Alignment(horizontal="center", vertical="center")

    ws.merge_cells("A2:G2")
    ws["A2"] = "SISTEM PENGIRAAN PEMBERAT BEBAN TUGAS GURU TAHUN 2026 (DIJANA OLEH GOOGLE ANTIGRAVITY AGENT)"
    ws["A2"].font = font_subtitle
    ws["A2"].alignment = Alignment(horizontal="center", vertical="center")

    # 2. Header Jadual Utama
    headers = [
        "Bil", "Nama Guru", "Jawatan Kurikulum", "Mata Kuri", 
        "Jawatan HEM", "Mata HEM", "Jawatan Kokurikulum", "Mata Koko", 
        "Jumlah Mata Beban", "Status Beban"
    ]
    
    row_h = 4
    for col_idx, h in enumerate(headers, 1):
        cell = ws.cell(row=row_h, column=col_idx, value=h)
        cell.font = font_header
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = thin_border
    ws.row_dimensions[row_h].height = 28

    # 3. Data Guru yang Diekstrak oleh Ejen daripada Arahan Teks Guru Besar
    teachers_data = [
        (1, "CIKGU SYAHEEDULLAH BIN YAHYA", "Ketua Panitia BM", 4, "-", 0, "Guru Penasihat Pengakap", 2),
        (2, "CIKGU FARIZ AKMAL", "Ketua Panitia Sains", 4, "Penyelaras Disiplin", 3, "Penyelaras Robotik", 2),
        (3, "CIKGU NURUL HUDA BINTI KAMAL", "Penyelaras PSS", 3, "Penyelaras Kebajikan", 2, "-", 0),
        (4, "CIKGU MOHD HAFIZ BIN OSMAN", "Guru Data & ICT", 3, "-", 0, "Guru Penasihat Bola Sepak", 2),
        (5, "CIKGU SITI AISYAH BINTI ZULKIFLI", "-", 0, "Penyelaras SPBT", 3, "Penasihat Badminton", 2),
    ]

    start_row = 5
    for i, t in enumerate(teachers_data):
        curr_row = start_row + i
        ws.row_dimensions[curr_row].height = 22

        ws.cell(row=curr_row, column=1, value=t[0]).alignment = Alignment(horizontal="center")
        ws.cell(row=curr_row, column=2, value=t[1])
        ws.cell(row=curr_row, column=3, value=t[2])
        ws.cell(row=curr_row, column=4, value=t[3]).alignment = Alignment(horizontal="center")
        ws.cell(row=curr_row, column=5, value=t[4])
        ws.cell(row=curr_row, column=6, value=t[5]).alignment = Alignment(horizontal="center")
        ws.cell(row=curr_row, column=7, value=t[6])
        ws.cell(row=curr_row, column=8, value=t[7]).alignment = Alignment(horizontal="center")

        # Formula Excel Dinamik untuk Jumlah Mata: =D5+F5+H5
        sum_formula = f"=D{curr_row}+F{curr_row}+H{curr_row}"
        cell_sum = ws.cell(row=curr_row, column=9, value=sum_formula)
        cell_sum.alignment = Alignment(horizontal="center")
        cell_sum.font = font_bold
        cell_sum.fill = total_fill

        # Formula Status Beban: IF(I5>8, "PADAT", "SEIMBANG")
        status_formula = f'=IF(I{curr_row}>8, "BEBAN TINGGI", "SEIMBANG")'
        cell_status = ws.cell(row=curr_row, column=10, value=status_formula)
        cell_status.alignment = Alignment(horizontal="center")
        cell_status.font = font_bold

        # Garisan & Warna Baris Berselang
        for col_idx in range(1, 11):
            c = ws.cell(row=curr_row, column=col_idx)
            c.border = thin_border
            if not c.font.bold:
                c.font = font_data
            if i % 2 == 1 and col_idx not in (9, 10):
                c.fill = zebra_fill

    # Laraskan Lebar Kolum
    col_widths = {1: 6, 2: 34, 3: 24, 4: 12, 5: 24, 6: 12, 7: 26, 8: 12, 9: 18, 10: 16}
    for col_idx, width in col_widths.items():
        ws.column_dimensions[get_column_letter(col_idx)].width = width

    output_path = os.path.join(os.path.dirname(__file__), "agihan_tugas_guru_SELESAI.xlsx")
    wb.save(output_path)
    print(f"[BERJAYA] Fail Excel dijana secara automatik di: {output_path}")

if __name__ == "__main__":
    build_excel_agihan_tugas()