<?php
include 'config.php';

// Calculate date range
$firstDayPrevMonth = date('Y-m-01', strtotime('first day of previous month'));
$firstDayCurrMonth = date('Y-m-01');
$interval31Day = date('Y-m-d', strtotime('-31 days'));
$startDate = ($firstDayPrevMonth > $interval31Day) ? $firstDayPrevMonth : $interval31Day;
$endDate = date('Y-m-d');

// Truncate existing POW data
$sql1 = "TRUNCATE TABLE broriserin_ic_attendance.pow";

// Create temp_pow_new table with combined data
$sql2 = "
CREATE TEMPORARY TABLE temp_pow_new AS
SELECT a.empid, a.OfficeDate, a.FinalStatus,
       CASE 
           WHEN a.FinalStatus = 'POW' THEN 1
           WHEN a.FinalStatus = 'HALF-POW' THEN 0.5
           ELSE 0
       END AS LQTY
FROM broriserin_ic_attendance.ams_final a
WHERE a.FinalStatus IN ('POW', 'HALF-POW')
AND (a.Approval_Status IS NULL OR a.Approval_Status = 'Approved')
AND a.OfficeDate BETWEEN '$startDate' AND '$endDate';
";

// Insert into pow
$sql3 = "INSERT INTO broriserin_ic_attendance.pow (empid, OfficeDate, Status, LQTY)
SELECT empid, OfficeDate, FinalStatus, LQTY FROM temp_pow_new";

// Update HALF-POW to POW
$sql4 = "UPDATE broriserin_ic_attendance.pow SET Status = 'POW' WHERE Status = 'HALF-POW'";

// Create temp table for deletion with proper collation handling
$sql5 = "
CREATE TEMPORARY TABLE temp_delete_new AS
SELECT p.empid, p.OfficeDate
FROM broriserin_ic_attendance.pow p
JOIN broriserin_ic_attendance.emp_leave el
  ON CONVERT(p.empid USING utf8mb4) COLLATE utf8mb4_general_ci = CONVERT(el.empid USING utf8mb4) COLLATE utf8mb4_general_ci
  AND p.OfficeDate = el.DateCoff
WHERE el.LeaveCategory = 'COFF';
";

// Delete overlapping POW rows (except Rejected)
$sql6 = "
DELETE p.* 
FROM broriserin_ic_attendance.pow p
JOIN temp_delete_new d
  ON p.empid = d.empid AND p.OfficeDate = d.OfficeDate
WHERE p.Status != 'Rejected';
";

// Drop temporary tables
$sql7 = "DROP TEMPORARY TABLE IF EXISTS temp_pow_new";
$sql8 = "DROP TEMPORARY TABLE IF EXISTS temp_delete_new";

// Execute queries sequentially
if ($conn->query($sql1) === TRUE) { echo "✅ Successfully S1 (Truncate POW)<br>"; } else { echo "❌ Error S1: " . $conn->error . "<br>"; }

if ($conn->multi_query($sql2)) {
    do {
        if ($result = $conn->store_result()) { $result->free(); }
    } while ($conn->next_result());
    echo "✅ Successfully S2 (Create temp_pow_new)<br>";
} else {
    echo "❌ Error S2: " . $conn->error . "<br>";
}

if ($conn->query($sql3) === TRUE) { echo "✅ Successfully S3 (Insert into POW)<br>"; } else { echo "❌ Error S3: " . $conn->error . "<br>"; }

if ($conn->query($sql4) === TRUE) { echo "✅ Successfully S4 (Update HALF-POW to POW)<br>"; } else { echo "❌ Error S4: " . $conn->error . "<br>"; }

if ($conn->query($sql5) === TRUE) { echo "✅ Successfully S5 (Create temp_delete_new)<br>"; } else { echo "❌ Error S5: " . $conn->error . "<br>"; }

if ($conn->query($sql6) === TRUE) { echo "✅ Successfully S6 (Delete overlapping POW)<br>"; } else { echo "❌ Error S6: " . $conn->error . "<br>"; }

if ($conn->query($sql7) === TRUE) { echo "✅ Successfully S7 (Drop temp_pow_new)<br>"; } else { echo "❌ Error S7: " . $conn->error . "<br>"; }

if ($conn->query($sql8) === TRUE) { echo "✅ Successfully S8 (Drop temp_delete_new)<br>"; } else { echo "❌ Error S8: " . $conn->error . "<br>"; }

$conn->close();
?>
