<?php
require '../class/vendor/autoload.php'; // Adjust path to composer autoload

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

ini_set('memory_limit', '2024M');

include '../class/database.php';
include '../class/config.php';
include '../class/userAuth.php';
include '../class/masterInfo.php';

$config = new config();
$db = new database();
$conn = $db->opendb();

$auth = new userAuth();
$master = new masterInfo();

$auth->sec_session_start();

if ($auth->login_check($conn) == FALSE) {
    header("location: ../login");
    exit();
}

if (!filter_has_var(INPUT_GET, 'month') || filter_input(INPUT_GET, "month", FILTER_VALIDATE_INT) === false) {
    header("Location: ../index.php");
    exit();
}

$user_data = $_SESSION['user_data'];
$unit_cd = $user_data['unit_code'];

$month = filter_input(INPUT_GET, "month", FILTER_VALIDATE_INT);
$year = filter_input(INPUT_GET, "year", FILTER_VALIDATE_INT);
$level = filter_input(INPUT_GET, "level", FILTER_SANITIZE_STRING);

if ($month < 10) {
    $month = '0' . $month;
}
$daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, $year);

// Fetch employees
$sqlEmp = "SELECT EMP_NUMBER as emp_cd, NAME, PHOTO_FILE FROM emp_master WHERE UNIT = ? AND ACTIVE_STATUS = 'Y'";
$params = [$unit_cd];
$types = "i";
if (!empty($level)) {
    $sqlEmp .= " AND LEVEL = ?";
    $params[] = $level;
    $types .= "s";
}
$stmtEmp = $conn->prepare($sqlEmp);
if ($stmtEmp === false) {
    die("Prepare failed for employees: " . $conn->error);
}
$stmtEmp->bind_param($types, ...$params);
$stmtEmp->execute();
$resEmp = $stmtEmp->get_result();

$employees = [];
while ($row = $resEmp->fetch_assoc()) {
    $employees[$row['emp_cd']] = $row;
}
if (empty($employees)) {
    die("No employees found for the given criteria.");
}

// Fetch holidays
$stmtHol = $conn->prepare("SELECT holiday, holiday_type FROM hr_holiday_mast WHERE YEAR(holiday) = ? AND MONTH(holiday) = ?");
$stmtHol->bind_param("ii", $year, $month);
$stmtHol->execute();
$resHol = $stmtHol->get_result();

$holidays = [];
while ($row = $resHol->fetch_assoc()) {
    $holidays[$row['holiday']] = $row['holiday_type'];
}

// Fetch punches
$unit_cd = (string) $unit_cd;
$level = !empty($level) ? (string) $level : null;

$query = "
WITH shift_punches AS (
    SELECT 
        COALESCE(sp.shift, em.SHIFT) AS shift_code,
        t.punchingcode,
        CASE 
            WHEN t.datetime < CONCAT(DATE(t.datetime), ' ', s.punch_start_time)
            THEN DATE_SUB(DATE(t.datetime), INTERVAL 1 DAY)
            ELSE DATE(t.datetime)
        END AS shift_date,
        t.datetime,
        em.EMP_NUMBER AS emp_cd,
        t.unit_cd
    FROM tblt_timesheet t 
    LEFT JOIN emp_master em ON t.punchingcode = em.CARD_NO  
    LEFT JOIN shift_plan sp ON sp.emp_cd = em.EMP_NUMBER AND t.datetime BETWEEN sp.from_dt AND sp.to_dt AND sp.unit_cd = t.unit_cd 
    LEFT JOIN shift_master s ON s.shift_code = COALESCE(sp.shift, em.SHIFT) AND s.unit_cd = t.unit_cd 
    WHERE MONTH(t.date) = ? 
      AND YEAR(t.date) = ? 
      AND t.unit_cd = ?
      " . (!empty($level) ? "AND em.LEVEL = ?" : "") . "
), 
punch_counts AS (
    SELECT 
        sp.emp_cd,
        sp.shift_code,
        sp.shift_date,
        COUNT(sp.punchingcode) AS total_punches,
        MIN(sp.datetime) AS in_datetime,
        CASE WHEN COUNT(sp.punchingcode) % 2 = 0 THEN MAX(sp.datetime) ELSE NULL END AS out_datetime
    FROM shift_punches sp
    GROUP BY sp.emp_cd, sp.shift_code, sp.shift_date
)
SELECT 
    pc.emp_cd,
    pc.shift_date,
    pc.in_datetime,
    pc.out_datetime,
    pc.total_punches,
    sm.start_time,
    sm.end_time,
    sm.type,
    IFNULL(TIMESTAMPDIFF(MINUTE, pc.in_datetime, pc.out_datetime), 0)/60 AS work_hours,
    CASE 
        WHEN sm.type != 'O' THEN
            IF( TIME(sm.start_time) > TIME(sm.end_time), 
                (TIMESTAMPDIFF(MINUTE, CONCAT(pc.shift_date, ' ', sm.start_time), CONCAT(DATE_ADD(pc.shift_date, INTERVAL 1 DAY), ' ', sm.end_time))/60),
                (TIMESTAMPDIFF(MINUTE, CONCAT(pc.shift_date, ' ', sm.start_time), CONCAT(pc.shift_date, ' ', sm.end_time))/60)
            )
        ELSE
            (SELECT ((TIME_TO_SEC(time)/60)/60) FROM open_shift_break os WHERE os.shift = sm.shift_code AND os.unit_cd = sm.unit_cd AND os.pay_per = 1 LIMIT 1)
    END AS shift_hours
FROM punch_counts pc
LEFT JOIN shift_master sm ON pc.shift_code = sm.shift_code AND sm.unit_cd = ?
ORDER BY pc.emp_cd, pc.shift_date
";

$stmtPunches = $conn->prepare($query);
if (!empty($level)) {
    $stmtPunches->bind_param("issis", $month, $year, $unit_cd, $level, $unit_cd);
} else {
    $stmtPunches->bind_param("iiss", $month, $year, $unit_cd, $unit_cd);
}
$stmtPunches->execute();
$resPunches = $stmtPunches->get_result();

$punches = [];
while ($row = $resPunches->fetch_assoc()) {
    $punches[$row['emp_cd']][$row['shift_date']] = $row;
}

// Prepare data structure
$firstDayObj = new DateTime("$year-$month-01");
$finalData = [];

foreach ($employees as $emp_cd => $empInfo) {
    $finalData[$emp_cd] = [
        'info' => $empInfo,
        'days' => [],
        'summary' => ['P'=>0, 'A'=>0, 'S'=>0, 'H'=>0, 'OT'=>0],
    ];
    $dateObj = clone $firstDayObj;
    for ($d = 1; $d <= $daysInMonth; $d++) {
        $dateStr = $dateObj->format('Y-m-d');
        $dow = (int)$dateObj->format('w');
        $holidayType = $holidays[$dateStr] ?? '';

        if ($holidayType) {
            $status = substr($holidayType,0,1);
            $finalData[$emp_cd]['summary']['H']++;
            $in_time = $out_time = '';
            $ot = 0;
        } else {
            $punch = $punches[$emp_cd][$dateStr] ?? null;
            if ($punch) {
                $status = 'P';
                $finalData[$emp_cd]['summary']['P']++;
                $in_time = date('h:i A', strtotime($punch['in_datetime']));
                $out_time = !empty($punch['out_datetime']) ? date('h:i A', strtotime($punch['out_datetime'])) : '';
                $otCalc = $punch['work_hours'] - $punch['shift_hours'];
                $ot = $otCalc > 0 ? round($otCalc,1) : 0;
                $finalData[$emp_cd]['summary']['OT'] += $ot;
            } else {
                if ($dow === 0) {
                    $status = 'S';
                    $finalData[$emp_cd]['summary']['S']++;
                    $in_time = $out_time = '';
                    $ot = 0;
                } else {
                    $status = 'A';
                    $finalData[$emp_cd]['summary']['A']++;
                    $in_time = $out_time = '';
                    $ot = 0;
                }
            }
        }
        $finalData[$emp_cd]['days'][$dateStr] = compact('status','in_time','out_time','ot','holidayType');
        $dateObj->modify('+1 day');
    }
}

// Setup Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Attendance Register');

$colSN = 1; $colPic=2; $colEmpInfo=3; $colFirstDay=4;
$lastCol = $colFirstDay + 31 -1;
$summaryStartCol = $lastCol +1;
$summaryCols = 5;
$lastSummaryCol = $summaryStartCol + $summaryCols -1;

$styleP = ['fill'=>['fillType'=>Fill::FILL_SOLID, 'startColor'=>['rgb'=>'94FF66']]];
$styleA = ['fill'=>['fillType'=>Fill::FILL_SOLID, 'startColor'=>['rgb'=>'FF7066']]];
$styleS = ['fill'=>['fillType'=>Fill::FILL_SOLID, 'startColor'=>['rgb'=>'FFFF66']]];
$styleH = ['fill'=>['fillType'=>Fill::FILL_SOLID, 'startColor'=>['rgb'=>'66F5FF']]];

$allBorders = [
    'borders'=>[
        'allBorders'=>[
            'borderStyle'=>Border::BORDER_THIN,
            'color'=>['argb'=>'FF000000'],
        ],
    ],
];

// Header 1 (title)
$rowNum = 1;
$monthName = date('M', strtotime("$year-$month-01"));
$sheet->mergeCellsByColumnAndRow(1,$rowNum, $lastSummaryCol, $rowNum);
$sheet->setCellValueByColumnAndRow(1,$rowNum, "Attendance Register For $monthName - $year");
$sheet->getStyleByColumnAndRow(1,$rowNum)->getFont()->setBold(true)->setSize(14);
$sheet->getStyleByColumnAndRow(1,$rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getRowDimension($rowNum)->setRowHeight(25);
$rowNum++;

// Header 2 (labels)
$sheet->setCellValueByColumnAndRow($colSN,$rowNum,'SrNo');
$sheet->setCellValueByColumnAndRow($colPic,$rowNum,'Pic.');
$sheet->setCellValueByColumnAndRow($colEmpInfo,$rowNum,'Employee Info');
for($d=1;$d<=31;$d++){
    $sheet->setCellValueByColumnAndRow($colFirstDay+$d-1,$rowNum,$d);
}
$sLabels = ['S','OT','P','TOTAL','A'];
for($i=0;$i<$summaryCols;$i++){
    $sheet->setCellValueByColumnAndRow($summaryStartCol+$i,$rowNum,$sLabels[$i]);
}
$sheet->getStyle("A{$rowNum}:" . Coordinate::stringFromColumnIndex($lastSummaryCol) . "{$rowNum}")->getFont()->setBold(true);
$sheet->getStyle("A{$rowNum}:" . Coordinate::stringFromColumnIndex($lastSummaryCol) . "{$rowNum}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getRowDimension($rowNum)->setRowHeight(20);
$sheet->getStyle("A{$rowNum}:" . Coordinate::stringFromColumnIndex($lastSummaryCol) . "{$rowNum}")->applyFromArray($allBorders);
$rowNum++;

// Header 3 (weekday)
$sheet->setCellValueByColumnAndRow($colSN,$rowNum,'');
$sheet->setCellValueByColumnAndRow($colPic,$rowNum,'');
$sheet->setCellValueByColumnAndRow($colEmpInfo,$rowNum,'');
$dateObj = new DateTime("$year-$month-01");
for($d=1;$d<=31;$d++){
    $col = $colFirstDay+$d-1;
    if($d<=$daysInMonth){
        $sheet->setCellValueByColumnAndRow($col,$rowNum,$dateObj->format('D'));
        $sheet->getStyleByColumnAndRow($col,$rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $dateObj->modify('+1 day');
    } else {
        $sheet->setCellValueByColumnAndRow($col,$rowNum,'');
    }
}
for($c=$summaryStartCol; $c<=$lastSummaryCol; $c++){
    $sheet->setCellValueByColumnAndRow($c,$rowNum,'');
}
$sheet->getStyle("A{$rowNum}:" . Coordinate::stringFromColumnIndex($lastSummaryCol) . "{$rowNum}")->applyFromArray($allBorders);
$rowNum++;

$sn =1;
foreach($finalData as $emp_cd => $empData){
    $info = $empData['info'];
    $days = $empData['days'];
    $summary = $empData['summary'];

    // ROW 1 Status
    $sheet->setCellValueByColumnAndRow($colSN,$rowNum,$sn);
    
    // Pic. cell content for Status row (empty or placeholder)
    $sheet->setCellValueByColumnAndRow($colPic,$rowNum,''); 
    
    // Merge Pic column cells vertically over 4 rows
    $picColLetter = Coordinate::stringFromColumnIndex($colPic);
    $sheet->mergeCells($picColLetter . $rowNum . ':' . $picColLetter . ($rowNum + 3));
    
    // Employee Info cell
    $sheet->setCellValueByColumnAndRow($colEmpInfo,$rowNum, "$emp_cd - " . $info['NAME']);

    $dateObj = new DateTime("$year-$month-01");
    for($d=1;$d<=31;$d++){
        $col = $colFirstDay+$d-1;
        if($d<=$daysInMonth){
            $dateStr = $dateObj->format('Y-m-d');
            $dayData = $days[$dateStr] ?? ['status'=>'','holidayType'=>''];
            $status = $dayData['status'];
            $sheet->setCellValueByColumnAndRow($col,$rowNum,$status);
            switch($status){
                case 'P': $sheet->getStyleByColumnAndRow($col,$rowNum)->applyFromArray($styleP); break;
                case 'A': $sheet->getStyleByColumnAndRow($col,$rowNum)->applyFromArray($styleA); break;
                case 'S': $sheet->getStyleByColumnAndRow($col,$rowNum)->applyFromArray($styleS); break;
                default: if(!empty($dayData['holidayType'])) $sheet->getStyleByColumnAndRow($col,$rowNum)->applyFromArray($styleH);
            }
            $sheet->getStyleByColumnAndRow($col,$rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $dateObj->modify('+1 day');
        } else {
            $sheet->setCellValueByColumnAndRow($col,$rowNum,'');
        }
    }
    for($c = $summaryStartCol; $c <= $lastSummaryCol; $c++){
        $sheet->setCellValueByColumnAndRow($c,$rowNum,'');
    }
    $sheet->getStyle("A{$rowNum}:" . Coordinate::stringFromColumnIndex($lastSummaryCol) . "{$rowNum}")->applyFromArray($allBorders);
    $startMergeRow = $rowNum;
    $rowNum++;

    // ROW 2 OT
    $sheet->setCellValueByColumnAndRow($colSN,$rowNum,'');
    $sheet->setCellValueByColumnAndRow($colPic,$rowNum,'');
    $sheet->setCellValueByColumnAndRow($colEmpInfo,$rowNum,'OT');

    $dateObj = new DateTime("$year-$month-01");
    for($d=1;$d<=31;$d++){
        $col = $colFirstDay + $d -1;
        if($d <= $daysInMonth){
            $dateStr = $dateObj->format('Y-m-d');
            $ot = $days[$dateStr]['ot'] ?? 0;
            $sheet->setCellValueByColumnAndRow($col,$rowNum, $ot ? $ot : '');
            $sheet->getStyleByColumnAndRow($col,$rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $dateObj->modify('+1 day');
        } else {
            $sheet->setCellValueByColumnAndRow($col,$rowNum,'');
        }
    }
    for($c=$summaryStartCol; $c <= $lastSummaryCol; $c++){
        $colLetter = Coordinate::stringFromColumnIndex($c);
        $mergeRange = $colLetter . $startMergeRow . ':' . $colLetter . ($startMergeRow + 3);
        $sheet->mergeCells($mergeRange);

        switch ($c - $summaryStartCol){
            case 0: $val = $summary['S']; break;
            case 1: $val = number_format($summary['OT'], 1); break;
            case 2: $val = $summary['P']; break;
            case 3: $val = $summary['P'] + $summary['S']; break;
            case 4: $val = $summary['A']; break;
        }
        $sheet->setCellValue($colLetter . $startMergeRow, $val);
        $sheet->getStyle($mergeRange)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER)->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle($mergeRange)->applyFromArray($allBorders);
    }
    $sheet->getStyle("A{$rowNum}:" . Coordinate::stringFromColumnIndex($lastSummaryCol) . $rowNum)->applyFromArray($allBorders);
    $rowNum++;

    // ROW 3 In Time
    $sheet->setCellValueByColumnAndRow($colSN,$rowNum,'');
    $sheet->setCellValueByColumnAndRow($colPic,$rowNum,'');
    $sheet->setCellValueByColumnAndRow($colEmpInfo,$rowNum,'In Time');

    $dateObj = new DateTime("$year-$month-01");
    for($d=1;$d<=31;$d++){
        $col = $colFirstDay + $d - 1;
        if($d <= $daysInMonth){
            $dateStr = $dateObj->format('Y-m-d');
            $val = $days[$dateStr]['in_time'] ?? '';
            $sheet->setCellValueByColumnAndRow($col,$rowNum, $val);
            $sheet->getStyleByColumnAndRow($col,$rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $dateObj->modify('+1 day');
        } else {
            $sheet->setCellValueByColumnAndRow($col,$rowNum,'');
        }
    }
    for($c = $summaryStartCol; $c <= $lastSummaryCol; $c++){
        $colLetter = Coordinate::stringFromColumnIndex($c);
        $mergeRange = $colLetter . $startMergeRow . ':' . $colLetter . ($startMergeRow + 3);
        $sheet->getStyle($mergeRange)->applyFromArray($allBorders);
    }
    $sheet->getStyle("A{$rowNum}:" . Coordinate::stringFromColumnIndex($lastSummaryCol) . $rowNum)->applyFromArray($allBorders);
    $rowNum++;

    // ROW 4 Out Time
    $sheet->setCellValueByColumnAndRow($colSN,$rowNum,'');
    $sheet->setCellValueByColumnAndRow($colPic,$rowNum,'');
    $sheet->setCellValueByColumnAndRow($colEmpInfo,$rowNum,'Out Time');

    $dateObj = new DateTime("$year-$month-01");
    for($d=1;$d<=31;$d++){
        $col = $colFirstDay + $d -1;
        if($d <= $daysInMonth){
            $dateStr = $dateObj->format('Y-m-d');
            $val = $days[$dateStr]['out_time'] ?? '';
            $sheet->setCellValueByColumnAndRow($col,$rowNum,$val);
            $sheet->getStyleByColumnAndRow($col,$rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $dateObj->modify('+1 day');
        } else {
            $sheet->setCellValueByColumnAndRow($col,$rowNum,'');
        }
    }
    for($c = $summaryStartCol; $c <= $lastSummaryCol; $c++){
        $colLetter = Coordinate::stringFromColumnIndex($c);
        $mergeRange = $colLetter . $startMergeRow . ':' . $colLetter . ($startMergeRow + 3);
        $sheet->getStyle($mergeRange)->applyFromArray($allBorders);
    }
    $sheet->getStyle("A{$rowNum}:" . Coordinate::stringFromColumnIndex($lastSummaryCol) . $rowNum)->applyFromArray($allBorders);
    $rowNum++;
    $sn++;
}

// Auto size columns
$lastColLetter = Coordinate::stringFromColumnIndex($lastSummaryCol);
foreach(range('A', $lastColLetter) as $colLetter){
    $sheet->getColumnDimension($colLetter)->setAutoSize(true);
}

// Note row
$sheet->setCellValue('A'.$rowNum, "Note:-");
$sheet->getStyle('A'.$rowNum)->getFont()->setBold(true);
$sheet->mergeCells("A{$rowNum}:C{$rowNum}");
$sheet->setCellValue('D'.$rowNum,"A-Absent | P-Present | S-Sunday | H-Holiday | F-Festival");
$sheet->mergeCells("D{$rowNum}:$lastColLetter{$rowNum}");
$sheet->getStyle("D{$rowNum}:$lastColLetter{$rowNum}")->getFont()->setBold(true);
$sheet->getStyle("D{$rowNum}:$lastColLetter{$rowNum}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Output Excel
$filename = "Attendance_Register_{$year}-{$month}.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=\"$filename\"");
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;

consider sunday working time as over time