<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require('../config.php');

if(!isset($_GET['users'])){
    exit();
}

// Fetch records from database  
$e_ids = "'" . join("','", $_GET['users']) . "'";


$all_salaries = [];
$salary = mysqli_query($db, "SELECT t1.*, t2.name, t2.consider_as FROM `employee_salary` t1 INNER JOIN users t2 ON t1.employee_id = t2.employee_id WHERE t1.employee_id IN ($e_ids) ");
while ($row = mysqli_fetch_assoc($salary)) {

    $fixed_pay = [];
    $fixed = json_decode($row['fixed'], true);
    foreach($fixed as $v){
        $fixed_pay[$v['title']] = $v['yearly_amount'];
    }

    $addOn_pay = [];
    $add_on = json_decode($row['add_on'], true);
    foreach($add_on as $v){
        $addOn_pay[$v['title']] = $v['yearly_amount'];
    }

    $deduction_pay = [];
    $deduction = json_decode($row['deduction'], true);
    foreach($deduction as $v){
        $deduction_pay[$v['title']] = $v['yearly_amount'];
    }

    $all_salaries[] =[
        "Employee Details" => [
            "Employee ID" => $row['employee_id'],
            "Name" => $row['name'],
            "Consider As" => $row['consider_as'],
        ],
        "Fixed Pay" => $fixed_pay,
        "Add On" => $addOn_pay,
        "Deduction" => $deduction_pay,
        "Final Salary" => [
            "Yearly CTC" => $row['ctc'],
            "Net Payable" => $row['net_payable'],
        ],
        "Applicability" => [
            "Revision Start Date" => $row['revision_start_date'],
            "Revision End Date" => $row['revision_end_date'],
        ],
    ];
}
// echo "<pre>";
// print_r($all_salaries);
// echo "</pre>";
// exit();



######################################################################### write in excel and save
require '../../../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();


// ----------------------------------------------------
// Step 1: Collect all unique sections + their headers
// ----------------------------------------------------
$sections = []; // no hardcoding
foreach ($all_salaries as $emp) {
    foreach ($emp as $sectionName => $sectionData) {
        if (!isset($sections[$sectionName])) {
            $sections[$sectionName] = [];
        }
        if (is_array($sectionData)) {
            $sections[$sectionName] = array_unique(
                array_merge($sections[$sectionName], array_keys($sectionData))
            );
        }
    }
}

// Flatten total column count
$total_column = array_sum(array_map('count', $sections));


// // ----------------------------------------------------
// // Step 2: Salary Period Title (merged full width)
// // ----------------------------------------------------
// $sheetTitleArray = [
//     'font' => ['bold' => true, 'size' => 18],
//     'alignment' => [
//         'horizontal' => Alignment::HORIZONTAL_CENTER,
//         'vertical'   => Alignment::VERTICAL_BOTTOM,
//     ],
// ];
// $lastColChar = Coordinate::stringFromColumnIndex($total_column);
// $sheet->setCellValue('A1', $salary_time_period)
//     ->mergeCells("A1:{$lastColChar}1")
//     ->getStyle("A1:{$lastColChar}1")
//     ->applyFromArray($sheetTitleArray);



// ----------------------------------------------------
// Step 3: Section Headers (loop through sections)
// ----------------------------------------------------
$styleArray1 = [
    'font' => ['bold' => true, 'size' => 13],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical'   => Alignment::VERTICAL_BOTTOM,
    ],
];
$startCol = 1;
foreach ($sections as $sectionName => $keys) {
    $count = count($keys);
    if ($count > 0) {
        $endCol = $startCol + $count - 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startCol) . "1", $sectionName)
            ->mergeCells(
                Coordinate::stringFromColumnIndex($startCol) . "1:" .
                Coordinate::stringFromColumnIndex($endCol) . "1"
            )
            ->getStyle(
                Coordinate::stringFromColumnIndex($startCol) . "1:" .
                Coordinate::stringFromColumnIndex($endCol) . "1"
            )
            ->applyFromArray($styleArray1);
        $startCol = $endCol + 1;
    }
}


// ----------------------------------------------------
// Step 4: Column Names (headers for each section)
// ----------------------------------------------------
$styleArray2 = [
    'font' => ['bold' => true, 'size' => 12],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical'   => Alignment::VERTICAL_BOTTOM,
    ],
];
$headerRow = 2;
$colIndex = 1;
foreach ($sections as $sectionName => $keys) {
    foreach ($keys as $colName) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex) . $headerRow, $colName);
        $colIndex++;
    }
}
$sheet->getStyle('A2:'.Coordinate::stringFromColumnIndex($colIndex-1).'2')
    ->applyFromArray($styleArray2);
// Autosize all used columns
foreach (range('A', Coordinate::stringFromColumnIndex($colIndex-1)) as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}



// ----------------------------------------------------
// Step 5: Write Data Rows (loop through sections)
// ----------------------------------------------------
$styleArray3 = [
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical'   => Alignment::VERTICAL_BOTTOM,
    ],
];
$rowIndex = 3;
foreach ($all_salaries as $emp) {
    $colIndex = 1;
    foreach ($sections as $sectionName => $keys) {
        foreach ($keys as $colName) {
            $value = $emp[$sectionName][$colName] ?? '';
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex) . $rowIndex, $value);
            $colIndex++;
        }
    }
    $sheet->getStyle('A' . $rowIndex . ':' . Coordinate::stringFromColumnIndex($colIndex-1) . $rowIndex)
        ->applyFromArray($styleArray3);
    $rowIndex++;
}


// ----------------------------------------------------
// Step 6: Save and record in DB
// ----------------------------------------------------
$writer = new Xlsx($spreadsheet);
$fileName = uniqid()."_".time().".xlsx";

// save in the folder
$writer->save("../files_download/$fileName");

$q = mysqli_query($db,"INSERT INTO download_files (file_name, tag) VALUES('$fileName', 'Employees Salary')");
if($q){
    echo json_encode(["status"=>200, 'message'=>'File Downloaded Successfully!']);
}else{
    unlink("../files_download/$fileName");
    echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
}

// // Headers for download 
// header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// header("Content-Disposition: attachment; filename=\"$fileName\"");
// $writer->save('php://output');

exit();
