<?php
// config file with database connection
include 'config.php';
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Set SQL mode to remove ONLY_FULL_GROUP_BY for current session
$conn->query("SET SESSION sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')");

// Calculate first date of last month and last date of current month
$startDate = date('Y-m-01', strtotime('first day of -1 month'));
$endDate = date('Y-m-t');


// TRUNCATE old tables
$sql1 = "TRUNCATE TABLE broriserin_ic_attendance.ams_report";
$sql2 = "TRUNCATE TABLE broriserin_ic_attendance.ams_merge";
$sql3 = "TRUNCATE TABLE broriserin_ic_attendance.ams_final";

foreach ([$sql1, $sql2, $sql3] as $index => $sql) {
    if (!$conn->query($sql)) {
        echo "❌ Error in sql" . ($index + 1) . ": " . $conn->error . "<br>";
    } else {
        echo "✅ sql" . ($index + 1) . " executed successfully.<br>";
    }
}

// Insert Leave Data
$sql4 = "INSERT INTO broriserin_ic_attendance.ams_report(empid, OfficeDate, location, status, Approval_Status)
         SELECT empid, DateFrom, LocationAddress, LeaveType, Status 
         FROM broriserin_ic_attendance.emp_leave 
         WHERE DateFrom BETWEEN '$startDate' AND '$endDate'";

$sql5 = "INSERT INTO broriserin_ic_attendance.ams_report(empid, OfficeDate, location, InTime, out_location, OutTime, status)
         SELECT empid, OfficeDate, location, OfficeIn, out_location, OfficeOut, status 
         FROM broriserin_ic_attendance.tracker 
         WHERE OfficeDate BETWEEN '$startDate' AND '$endDate'";

$sql6 = "INSERT INTO broriserin_ic_attendance.ams_report(empid, OfficeDate, status)
         SELECT empid, DateWoff, status 
         FROM broriserin_ic_attendance.woff 
         WHERE DateWoff BETWEEN '$startDate' AND '$endDate'";

foreach ([$sql4, $sql5, $sql6] as $index => $sql) {
    if (!$conn->query($sql)) {
        echo "❌ Error in sql" . ($index + 4) . ": " . $conn->error . "<br>";
    } else {
        echo "✅ sql" . ($index + 4) . " executed successfully.<br>";
    }
}

// Insert into ams_merge
$sql7 = "INSERT INTO broriserin_ic_attendance.ams_merge (
    empid, OfficeDate, location, InTime, out_location, OutTime, status, Approval_Status
)
SELECT 
    empid,
    OfficeDate,
    MAX(location),
    MAX(InTime),
    MAX(out_location),
    MAX(OutTime),
    GROUP_CONCAT(status ORDER BY status SEPARATOR ','),
    MAX(Approval_Status)
FROM broriserin_ic_attendance.ams_report
WHERE TRIM(empid) != '' AND OfficeDate IS NOT NULL
GROUP BY empid, OfficeDate";

if (!$conn->query($sql7)) {
    echo "❌ Error in sql7: " . $conn->error . "<br>";
} else {
    echo "✅ sql7 executed successfully.<br>";
}

// Insert into ams_final
$sql8 = "INSERT INTO broriserin_ic_attendance.ams_final (
    empid, Functional_Reporting, OfficeDate, location, InTime, out_location, OutTime, Approval_Status, Status, FinalStatus
)
SELECT 
    empid,
    MAX(Functional_Reporting),
    OfficeDate,
    MAX(location),
    MAX(InTime),
    MAX(out_location),
    MAX(OutTime),
    MAX(Approval_Status),
    GROUP_CONCAT(status ORDER BY status SEPARATOR ','),
    CASE 
        WHEN MAX(status) = '1.0-COFF' THEN 'COFF'
        WHEN MAX(status) = 'ABSENT' THEN 'A'
        WHEN MAX(status) = 'PRESENT' THEN 'P'
        WHEN MAX(status) = '1-OD' THEN 'OD'
        WHEN MAX(status) = '1-CL' THEN 'CL'
        WHEN MAX(status) = '1-PL' THEN 'PL'
        WHEN MAX(status) = '1-SL' THEN 'SL'
        WHEN MAX(status) = '1-COFF' THEN 'COFF'
        WHEN MAX(status) = '1-LWP' THEN 'LWP'
        WHEN MAX(status) = '1-ML' THEN 'ML'
        WHEN MAX(status) = '1-BDY' THEN 'BDY'
        WHEN MAX(status) = '1-BL' THEN 'BL'
        WHEN MAX(status) = '1-MRG' THEN 'MRG'
        WHEN MAX(status) = '1-PTL' THEN 'PTL'
        WHEN MAX(status) = '0.5-OD' THEN 'HALF-OD'
        WHEN MAX(status) = '0.5-CL' THEN 'HALF-CL'
        WHEN MAX(status) = '0.5-PL' THEN 'HALF-PL'
        WHEN MAX(status) = '0.5-SL' THEN 'HALF-SL'
        WHEN MAX(status) = '0.5-COFF' THEN 'HALF-COFF'
        WHEN MAX(status) = '0.5-LWP' THEN 'HALF-LWP'
        WHEN MAX(status) = '0.5-ML' THEN 'HALF-ML'
        WHEN MAX(status) = '0.5-BDY' THEN 'HALF-BDY'
        WHEN MAX(status) = '0.5-BL' THEN 'HALF-BL'
        WHEN MAX(status) = '0.5-MRG' THEN 'HALF-MRG'
        WHEN MAX(status) = '0.5-MIS-P' AND Approval_Status = 'Approved' THEN 'P'
        WHEN MAX(status) = 'Holiday' THEN 'HOLIDAY'
         
        
        WHEN MAX(status) = '0.5-MIS-P,MIS' AND Approval_Status = 'Approved' THEN 'P'
        WHEN MAX(status) = '0.5-MIS-P,MIS' AND Approval_Status = 'Pending' THEN 'MIS'
        
        WHEN MAX(status) = '0.5-OD,ABSENT' AND Approval_Status = 'Approved' THEN 'HALF-OD'
        WHEN MAX(status) = '0.5-OD,ABSENT' AND Approval_Status = 'Pending' THEN 'A'
        WHEN MAX(status) = '0.5-OD,ABSENT'                                       THEN 'A'
        
        WHEN MAX(status) = '0.5-OD,HALF' AND Approval_Status = 'Approved' THEN 'P'
        WHEN MAX(status) = '0.5-OD,HALF' AND Approval_Status = 'Pending' THEN 'HALF'
        
        WHEN MAX(status) = '1-OD,1-OD' AND Approval_Status = 'Approved' THEN 'OD'
        WHEN MAX(status) = '1-OD,1-OD' AND Approval_Status = 'Pending' THEN 'A'
        
        
    
    
    
    WHEN MAX(status) = '0.5-OD,MIS'    AND Approval_Status = 'Approved' THEN 'P'
    WHEN MAX(status) = '0.5-OD,MIS'                                        THEN 'MIS'
    WHEN MAX(status) = '0.5-OD,MIS'    AND Approval_Status = 'Pending' THEN 'P'
    
    
    WHEN MAX(status) = '0.5-OD,SRT'    AND Approval_Status = 'Approved' THEN 'HALF-OD'
    WHEN MAX(status) = '0.5-OD,SRT'                                        THEN 'SRT'
    WHEN MAX(status) = '0.5-OD,WOFF'   AND Approval_Status = 'Approved' THEN 'POW'
    WHEN MAX(status) = '0.5-OD,WOFF'                                       THEN 'WOFF'
    WHEN MAX(status) = '1-OD,ABSENT'   AND Approval_Status = 'Approved' THEN 'OD'
    WHEN MAX(status) = '1-OD,ABSENT'                                      THEN 'ABSENT'
    WHEN MAX(status) = '1-OD,MIS'     AND Approval_Status = 'Approved' THEN 'OD'
    WHEN MAX(status) = '1-OD,MIS'                                         THEN 'MIS'
    WHEN MAX(status) = '1-OD,SRT'     AND Approval_Status = 'Approved' THEN 'OD'
    WHEN MAX(status) = '1-OD,SRT'                                         THEN 'SRT'
    WHEN MAX(status) = '1-OD,WOFF'    AND Approval_Status = 'Approved' THEN 'POW'
    WHEN MAX(status) = '1-OD,WOFF'                                        THEN 'WOFF'
        WHEN MAX(status) = 'ABSENT,MIS' THEN 'MIS'
        WHEN MAX(status) = 'LATE,MIS,MIS,MIS,MIS' THEN 'LATE'
        WHEN MAX(status) = 'PRESENT,PRESENT' THEN 'P'
        WHEN MAX(status) = 'MIS,PRESENT' THEN 'P'
        
        












        ELSE MAX(status)
    END AS FinalStatus
FROM broriserin_ic_attendance.ams_merge
WHERE TRIM(empid) != '' AND OfficeDate IS NOT NULL
GROUP BY empid, OfficeDate";

if (!$conn->query($sql8)) {
    echo "❌ Error in sql8: " . $conn->error . "<br>";
} else {
    echo "✅ sql8 executed successfully.<br>";
}
?>
