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


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


require_once "../salaryCodeCalculate.php";
require_once('../salaryCode.php');


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

// Fetch records from database  
$e_ids = "'" . join("','", $_GET['users']) . "'";
// Fetch records from database  
$firstDayLast = $_GET['from'];
$lastDayLast = $_GET['to'];
$join_month = date("Y-m", strtotime($firstDayLast));
$wsDate = date("Y-m", strtotime($lastDayLast));
$query = "SELECT u.employee_id, u.name, d.department_name, u.joining_date, u.working_status_date, u.account_no, u.consider_as, '$firstDayLast' as firstDayLast, '$lastDayLast' as lastDayLast
        FROM users as u 
        JOIN attendance AS a ON u.employee_id = a.employee_id 
        JOIN departments as d on u.department=d.id
        WHERE u.employee_id in ($e_ids) 
        AND (STR_TO_DATE(a.which_month, '%M %d %Y') BETWEEN '$firstDayLast' AND '$lastDayLast')
        AND (u.joining_date IS NULL OR u.joining_date = '' OR DATE_FORMAT(STR_TO_DATE(u.joining_date, '%Y-%m-%d'),'%Y-%m') <= '$join_month')
        AND (u.working_status_date IS NULL OR u.working_status_date = '' OR DATE_FORMAT(STR_TO_DATE(u.working_status_date, '%Y-%m-%d'),'%Y-%m') >= '$wsDate')
        GROUP BY u.id 
        ORDER BY u.id ASC";
$users = mysqli_query($db, $query);
$newFirstDayLast = $firstDayLast;
$newLastDayLast = $lastDayLast;

$salary_time_period = date("M d Y", strtotime($firstDayLast)) . ' - to - ' . date("M d Y", strtotime($lastDayLast));
$all_salaries = [];
while ($user = mysqli_fetch_assoc($users)) {

    $s_code = salaryCode($user, $firstDayLast, $firstDayLast);

    $leaves = $s_code['leaves'];
    $working_salary = $s_code['working_salary'];
    $deduction = $s_code['deduction'];
    $final_salary = $s_code['final_salary'];
    $fixedMonthlySalary = $s_code['fixedMonthlySalary'];
    $grossWorkingPayable = $s_code['grossWorkingPayable'];

    $all_salaries[$salary_time_period][] = [
        "Employee Details" => [
            "Employee ID" => $user['employee_id'],
            "Name" => $user['name'],
            "Consider As" => $user["consider_as"],
            "Department" => $user['department_name'],
            "Account No" => $user['account_no'],
        ],
        "Days Details" => [
            "Total Working Days" => $leaves['Month Days'],
            "Full Day Leaves" => $leaves['Absent'],
            "Half Day Leaves" => $leaves['Half Day'],
            "Net Paid Days" => $leaves['Total Working Days'],
        ],
        "Monthly Salary Details" => [
            "Fixed Monthly Salary" => $fixedMonthlySalary + ($working_salary['EPF Employer Share'] ?? 0),
        ],
        "Payable Salary Details" => [
            "Basic Salary" => $working_salary['Basic Salary'] ?? '0',
            "House Rent Allowance (HRA)" => $working_salary['House Rent Allowance (HRA)'] ?? '0',
            "Special Allowance" => $working_salary['Special Allowance'] ?? '0',
            "Books & Periodicals" => $working_salary['Books & Periodicals'] ?? '0',
            "Company EPF" => $working_salary['EPF Employer Share'] ?? '0',
            "Employee EPF" => $deduction['EPF Employee Share'] ?? '0',
            "Total Working CTC" => $grossWorkingPayable
        ],
        "Deduction Details" => [
            "TDS" => $deduction['TDS'] ?? '0',
            "Company EPF" => $working_salary['EPF Employer Share'] ?? '0',
            "Employee EPF" => $deduction['EPF Employee Share'] ?? '0',
            "Company ESIC" => $working_salary['ESIC Employer Share @ 3.25% of (A)'] ?? '0',
            "Employee ESIC" => $deduction['ESIC Employee Share @ 0.75% of (A)'] ?? '0',
        ],
        "Final Salary Details" => [
            "Net Payable" => $final_salary['Payable Salary'] ?? '0',
            "Bonus" => $final_salary['Bonus'] ?? '0',
            "Incentive" => $final_salary['Incentive'] ?? '0',
        ],
    ];
}
// echo "<pre>";
// print_r($all_salaries);
// echo "</pre>";



######################################################################### 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 $period => $employees) {
    foreach ($employees 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' => 14],
    '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) . "2", $sectionName)
            ->mergeCells(
                Coordinate::stringFromColumnIndex($startCol) . "2:" .
                Coordinate::stringFromColumnIndex($endCol) . "2"
            )
            ->getStyle(
                Coordinate::stringFromColumnIndex($startCol) . "2:" .
                Coordinate::stringFromColumnIndex($endCol) . "2"
            )
            ->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 = 3;
$colIndex = 1;
foreach ($sections as $sectionName => $keys) {
    foreach ($keys as $colName) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex) . $headerRow, $colName);
        $colIndex++;
    }
}
$sheet->getStyle('A3:'.Coordinate::stringFromColumnIndex($colIndex-1).'3')
    ->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 = 4;
foreach ($all_salaries as $period => $employees) {
    foreach ($employees as $emp) {
        $colIndex = 1;
        foreach ($sections as $sectionName => $keys) {
            foreach ($keys as $colName) {
                // $value = $emp[$sectionName][$colName] ?? '';
                $value = $emp[$sectionName][$colName] ?? '';

                if (is_array($value)) {
                    // Convert nested arrays to readable strings
                    $value = json_encode($value, JSON_UNESCAPED_UNICODE);
                }
                $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");

$tag = "salary_" . str_replace(" ", "-", $firstDayLast) . "_" . str_replace(" ", "-", $lastDayLast);
$q = mysqli_query($db,"INSERT INTO download_files (file_name, tag) VALUES('$fileName', '$tag')");
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');
######################################################################### write in excel and save
exit();
