<?php
require_once('../db.php');
require_once('../libs/PHPExcel/Classes/PHPExcel.php');

// 1. ดึงข้อมูลสำหรับ Dropdowns
$schools_res = mysqli_query($conn, "SELECT school_name FROM schools ORDER BY school_name");
$types_res = mysqli_query($conn, "SELECT type_name FROM budget_types ORDER BY type_name");
$plans_res = mysqli_query($conn, "SELECT plan_name FROM plans ORDER BY plan_name");

$schools = []; $types = []; $plans = [];
while($row = mysqli_fetch_assoc($schools_res)) $schools[] = $row['school_name'];
while($row = mysqli_fetch_assoc($types_res)) $types[] = $row['type_name'];
while($row = mysqli_fetch_assoc($plans_res)) $plans[] = $row['plan_name'];

// 2. สร้างไฟล์ Excel
$objPHPExcel = new PHPExcel();

// == สร้าง Sheet หลักสำหรับกรอกข้อมูล ==
$objPHPExcel->setActiveSheetIndex(0);
$mainSheet = $objPHPExcel->getActiveSheet();
$mainSheet->setTitle('Allocations');

// ตั้งค่าหัวคอลัมน์และตัวอย่าง
$mainSheet->getCell('A1')->setValue("School Name\n(เลือกจาก Dropdown)");
$mainSheet->getCell('B1')->setValue("Budget Type\n(เลือกจาก Dropdown)");
$mainSheet->getCell('C1')->setValue("Plan\n(เลือกจาก Dropdown)");
$mainSheet->getCell('D1')->setValue("Item Name\n(เช่น 'ค่าวัสดุสำนักงาน')");
$mainSheet->getCell('E1')->setValue("Amount (Baht)\n(เช่น 15000.50)");
$mainSheet->getCell('F1')->setValue("Allocation Date\n(เช่น 2025-10-06)");

// จัดรูปแบบหัวคอลัมน์
$headerStyle = [
    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
    'fill' => ['type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => ['rgb' => '4F81BD']],
    'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'wrap' => true]
];
$mainSheet->getStyle('A1:F1')->applyFromArray($headerStyle);
$mainSheet->getRowDimension(1)->setRowHeight(40);

// กำหนดความกว้างคอลัมน์
$mainSheet->getColumnDimension('A')->setWidth(30);
$mainSheet->getColumnDimension('B')->setWidth(20);
$mainSheet->getColumnDimension('C')->setWidth(25);
$mainSheet->getColumnDimension('D')->setWidth(35);
$mainSheet->getColumnDimension('E')->setWidth(15);
$mainSheet->getColumnDimension('F')->setWidth(20);

// == สร้าง Sheet สำหรับเก็บข้อมูล Dropdown (ซ่อนไว้) ==
$dataSheet = $objPHPExcel->createSheet();
$dataSheet->setTitle('DataLists');

// ใส่ข้อมูลลงใน Sheet นี้
foreach ($schools as $index => $name) $dataSheet->getCell('A'.($index+1))->setValue($name);
foreach ($types as $index => $name) $dataSheet->getCell('B'.($index+1))->setValue($name);
foreach ($plans as $index => $name) $dataSheet->getCell('C'.($index+1))->setValue($name);

// ซ่อน Sheet
$objPHPExcel->getSheetByName('DataLists')->setSheetState(PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN);

// 3. กำหนด Data Validation (Dropdowns) ใน Sheet หลัก
$max_rows = 1000; // รองรับการกรอกข้อมูล 1000 แถว
for ($i = 2; $i <= $max_rows; $i++) {
    // School Name
    $validation = $mainSheet->getCell('A'.$i)->getDataValidation();
    $validation->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
    $validation->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
    $validation->setAllowBlank(false);
    $validation->setShowInputMessage(true);
    $validation->setShowErrorMessage(true);
    $validation->setShowDropDown(true);
    $validation->setFormula1('=DataLists!$A$1:$A$'.count($schools));

    // Budget Type
    $validation = $mainSheet->getCell('B'.$i)->getDataValidation();
    $validation->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
    $validation->setAllowBlank(false);
    $validation->setShowDropDown(true);
    $validation->setFormula1('=DataLists!$B$1:$B$'.count($types));

    // Plan
    $validation = $mainSheet->getCell('C'.$i)->getDataValidation();
    $validation->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
    $validation->setAllowBlank(false);
    $validation->setShowDropDown(true);
    $validation->setFormula1('=DataLists!$C$1:$C$'.count($plans));
}

$objPHPExcel->setActiveSheetIndex(0);

// 4. ส่งไฟล์ให้ผู้ใช้ดาวน์โหลด
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="template_allocations.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
?>