<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

// Include Composer's autoloader
require 'vendor/autoload.php';

// Database connection details
$host = '34.101.158.124';
$dbname = 'frex_app';
$username = 'frex_tms';
$password = 'D8NHJQHsK0Br5VnwEUwE';

try {
    // Create a PDO instance
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Queries
    $queries = [
        'AWB Productivity' => "
			WITH ranked_shipments AS (
				SELECT
					s.id AS shipment_id,
					s.code AS awb_id,
					m.code AS trip_id,
					v.asset_type AS vendor_internal,
					c.code AS project_id,
					s.reciver_name AS nama_penerima,
					CONCAT(s.sender_lat, ', ', s.sender_lng) AS entry_from_lat_lng,
					CONCAT(s.reciver_lat, ', ', s.reciver_lng) AS entry_to_lat_lng,
					CONCAT(sl16.lat, ', ', sl16.lng) AS actual_from_lat_lng,
					CONCAT(sl16.lat, ', ', sl16.lng) AS actual_to_lat_lng,
					s.distance,
					s.duration as aed_in_minute,
					-- Select the earliest 'ast' timestamp for each shipment
					MIN(sl6.created_at) AS ast,
					MAX(sl16.created_at) AS aet,
					MAX(sl7.created_at) AS aat,
					MAX(sl16.created_at) AS alt,
					ROW_NUMBER() OVER (
						PARTITION BY m.code   -- Reset the drop_point for each trip_id
						ORDER BY MIN(sl6.created_at)  -- Order by earliest 'ast' for each shipment
					) AS drop_point
				FROM 
					shipments s
				JOIN 
					shipment_mission sm ON s.id = sm.shipment_id
				JOIN 
					missions m ON sm.mission_id = m.id
				JOIN 
					clients c ON s.client_id = c.id
				JOIN 
					vehicles v ON m.vehicle_id  = v.id
				LEFT JOIN 
					shipment_log sl6 ON s.id = sl6.shipment_id AND sl6.`to` = 6
				LEFT JOIN 
					shipment_log sl16 ON s.id = sl16.shipment_id AND sl16.`to` = 16
				LEFT JOIN 
					shipment_log sl7 ON s.id = sl7.shipment_id AND sl7.`to` = 7
				WHERE 
					DATE(s.shipping_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
				AND m.vehicle_id != 1
				GROUP BY
					s.id, s.code, m.code, c.code, s.sender_lat, s.sender_lng, s.reciver_lat, s.reciver_lng, s.distance
			)

			SELECT
				awb_id,
				trip_id,
				vendor_internal,
				drop_point AS sequences,
				project_id,
				nama_penerima,
				entry_from_lat_lng,
				entry_to_lat_lng,
				actual_from_lat_lng,
				actual_to_lat_lng,
				-- Calculate distance between drop points (longitude first, latitude second)
-- 				CASE
-- 					WHEN drop_point = 1 THEN
-- 						-- For the first drop point, calculate the distance from 'from_lat_lng' to 'to_lat_lng'
-- 						ROUND(ST_Distance_Sphere(
-- 							POINT(SUBSTRING_INDEX(entry_from_lat_lng, ',', -1), SUBSTRING_INDEX(entry_from_lat_lng, ',', 1)),
-- 							POINT(SUBSTRING_INDEX(entry_to_lat_lng, ',', -1), SUBSTRING_INDEX(entry_to_lat_lng, ',', 1))
-- 						) / 1000, 2)  -- Distance in kilometers, rounded to 2 decimal places
-- 					ELSE
-- 						-- For subsequent drop points, calculate distance from the previous drop point to the current one
-- 						ROUND(ST_Distance_Sphere(
-- 							POINT(SUBSTRING_INDEX(LAG(entry_to_lat_lng) OVER (PARTITION BY trip_id ORDER BY ast), ',', -1), 
-- 								  SUBSTRING_INDEX(LAG(entry_to_lat_lng) OVER (PARTITION BY trip_id ORDER BY ast), ',', 1)),
-- 							POINT(SUBSTRING_INDEX(entry_to_lat_lng, ',', -1), SUBSTRING_INDEX(entry_to_lat_lng, ',', 1))
-- 						) / 1000, 2)  -- Distance in kilometers, rounded to 2 decimal places
-- 				END AS distance_from_previous_drop_point_km,
				ast,
				aet,
				aat,
				alt,
				TIMESTAMPDIFF(MINUTE, ast, aet) AS aed, 
				TIMESTAMPDIFF(MINUTE, ast, aet) AS atd,  -- Calculate the duration
				
				-- Calculate the duration from the previous drop point to the current one
				CASE
					WHEN drop_point = 1 THEN
						-- For the first drop point, set the duration to 0 or NULL since there is no previous drop point
						0
					ELSE
						-- Calculate the difference in minutes between the 'ast' timestamps of the current and previous drop points
						TIMESTAMPDIFF(MINUTE, 
							LAG(ast) OVER (PARTITION BY trip_id ORDER BY ast),  -- 'ast' of the previous drop point
							ast  -- 'ast' of the current drop point
						)
				END AS aibd,

				-- Check if driver used the app and updated it
				CASE 
					WHEN ast IS NULL THEN 'Driver tidak menggunakan app'
					WHEN (actual_from_lat_lng IS NULL OR actual_from_lat_lng = '-, -') AND ast IS NOT NULL THEN 'Driver tidak update aplikasi'
					ELSE 'OK'
				END AS status_penggunaan_aplikasi

			FROM 
				ranked_shipments

			ORDER BY 
				trip_id, ast   
				",
        'Trip Productivity' => "
			WITH ranked_shipments AS (
				SELECT
					s.id AS shipment_id,
					s.code AS awb_id,
					m.code AS trip_id,
					c.code AS project_id,
					v.license_plate AS fleet_id,
					m.fleet_type,
					s.client_id,
					CONCAT(s.sender_lat, ', ', s.sender_lng) AS from_lat_lng,
					CONCAT(s.reciver_lat, ', ', s.reciver_lng) AS to_lat_lng,
					s.distance,
					MIN(sl6.created_at) AS ast,
					MAX(sl16.created_at) AS aet,
					MAX(sl7.created_at) AS aat,
					MAX(sl16.created_at) AS alt,
					ROW_NUMBER() OVER (
						PARTITION BY m.code
						ORDER BY MIN(sl6.created_at)
					) AS drop_point,
					s.sender_lat,
					s.sender_lng,
					s.reciver_lat,
					s.reciver_lng,
					DATE_FORMAT(m.target_start, '%Y-%m-%d %H:%i') target_start,
					DATE_FORMAT(m.actual_start, '%Y-%m-%d %H:%i') actual_start,
				    
				    CONCAT(
					    IF(TIME_TO_SEC(TIMEDIFF(m.actual_start, m.target_start)) < 0, '-', ''),
					    FLOOR(ABS(TIME_TO_SEC(TIMEDIFF(m.actual_start, m.target_start))) / 3600), 
					    '.',
					    LPAD(ROUND((ABS(TIME_TO_SEC(TIMEDIFF(m.actual_start, m.target_start))) % 3600) / 60), 2, '0')
					) AS duration_start,

				    
					CASE 
					    WHEN TIME_TO_SEC(TIMEDIFF(m.actual_start, m.target_start)) > 0 THEN 'Late'
					    WHEN m.actual_start IS NULL OR m.target_start IS NULL THEN 'No App Activity'
					    ELSE 'On-Time'
					END AS status_start,
					
					DATE_FORMAT(m.due_date, '%Y-%m-%d %H:%i') target_rtb,
					DATE_FORMAT(m.actual_rtb, '%Y-%m-%d %H:%i') actual_rtb,
					
					CONCAT(
					    IF(TIME_TO_SEC(TIMEDIFF(m.actual_rtb, m.due_date)) < 0, '-', ''),
					    FLOOR(ABS(TIME_TO_SEC(TIMEDIFF(m.actual_rtb, m.due_date))) / 3600), 
					    '.',
					    LPAD(ROUND((ABS(TIME_TO_SEC(TIMEDIFF(m.actual_rtb, m.due_date))) % 3600) / 60), 2, '0')
					) AS duration_rtb,

				    
					CASE 
						WHEN TIME_TO_SEC(TIMEDIFF(m.actual_rtb, m.due_date)) > 0 THEN 'Late'
						WHEN m.actual_rtb IS NULL OR m.due_date IS NULL THEN 'No App Activity'
						ELSE 'On-Time'
					END AS status_rtb

				FROM 
					shipments s
				JOIN 
					shipment_mission sm ON s.id = sm.shipment_id
				JOIN 
					missions m ON sm.mission_id = m.id
				JOIN 
					clients c ON s.client_id = c.id
				LEFT JOIN 
					vehicles v ON m.vehicle_id = v.id -- Join vehicles for license_plate
				LEFT JOIN 
					shipment_log sl6 ON s.id = sl6.shipment_id AND sl6.`to` = 6
				LEFT JOIN 
					shipment_log sl16 ON s.id = sl16.shipment_id AND sl16.`to` = 16
				LEFT JOIN 
					shipment_log sl7 ON s.id = sl7.shipment_id AND sl7.`to` = 7
				WHERE 
					DATE(s.shipping_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
				AND m.vehicle_id != 1
				GROUP BY
					s.id, s.code, m.code, c.code, s.sender_lat, s.sender_lng, s.reciver_lat, s.reciver_lng, s.distance, m.vehicle_id, m.fleet_type, s.client_id
			),

			distances AS (
				SELECT
					trip_id,
					ROW_NUMBER() OVER (PARTITION BY trip_id ORDER BY ast) AS drop_point,
					CASE 
						WHEN ROW_NUMBER() OVER (PARTITION BY trip_id ORDER BY ast) = 1 THEN 
							ROUND(ST_Distance_Sphere(
								POINT(sender_lng, sender_lat),
								POINT(reciver_lng, reciver_lat)
							) / 1000,2)
						ELSE 
							ROUND(ST_Distance_Sphere(
								POINT(
									LAG(reciver_lng) OVER (PARTITION BY trip_id ORDER BY ast),
									LAG(reciver_lat) OVER (PARTITION BY trip_id ORDER BY ast)
								),
								POINT(sender_lng, sender_lat)
							) / 1000,2)
					END AS distance_to_previous_point_km
				FROM
					ranked_shipments
			),

			trip_projects AS (
				SELECT
					trip_id,
					GROUP_CONCAT(DISTINCT project_id ORDER BY project_id) AS unique_project_ids
				FROM
					ranked_shipments
				GROUP BY
					trip_id
			)

			SELECT
				trip_id,
				ANY_VALUE(fleet_id) AS fleet_id,
				ANY_VALUE(fleet_type) AS fleet_type,
				
				CONCAT(MIN(sender_lat), ', ', MIN(sender_lng)) AS start,
				CONCAT(MAX(reciver_lat), ', ', MAX(reciver_lng)) AS end,
				SUM(distance_to_previous_point_km) AS distance,  -- Sum the distance_to_previous_point_km here
				COUNT(awb_id) AS tac,
				COUNT(DISTINCT CONCAT(sender_lat, ', ', sender_lng, ' -> ', reciver_lat, ', ', reciver_lng)) AS tdp,
				MIN(ast) AS tst,
				MAX(alt) AS tet,
-- 				ROUND(AVG(TIMESTAMPDIFF(SECOND, ast, alt)) / 3600, 2) AS aad, -- Convert to hours
				ROUND(TIMESTAMPDIFF(SECOND, MIN(ast), MAX(alt)) / 3600, 2) AS ted, -- Convert to hours
				target_start,
				actual_start,
				status_start,
				target_rtb,
				actual_rtb,
				status_rtb
			FROM
				distances
			JOIN
				ranked_shipments USING (trip_id, drop_point)
			GROUP BY
				trip_id
        ",
        'Fleet Productivity' => "
			WITH ranked_shipments AS (
				SELECT
					s.id AS shipment_id,
					s.code AS awb_id,
					m.code AS trip_id,
					c.code AS project_id,
					v.id AS vehicle_id,  -- Ensure vehicle ID is captured
					v.asset_type,
					v.license_plate AS fleet_id,
					v.location,
					v.type AS fleet_type,
					CASE WHEN v.is_archived = 1 THEN 'NON ACTIVE' ELSE 'ACTIVE' END AS fleet_status,
					s.client_id,
					CONCAT(s.sender_lat, ', ', s.sender_lng) AS from_lat_lng,
					CONCAT(s.reciver_lat, ', ', s.reciver_lng) AS to_lat_lng,
					s.distance,
					MIN(sl6.created_at) AS ast,
					MAX(sl16.created_at) AS aet,
					MAX(sl7.created_at) AS aat,
					MAX(sl16.created_at) AS alt,
					ROW_NUMBER() OVER (
						PARTITION BY m.code
						ORDER BY MIN(sl6.created_at)
					) AS drop_point,  -- Drop point per shipment
					s.sender_lat,
					s.sender_lng,
					s.reciver_lat,
					s.reciver_lng
				FROM 
					shipments s
				JOIN 
					shipment_mission sm ON s.id = sm.shipment_id
				JOIN 
					missions m ON sm.mission_id = m.id
				JOIN 
					clients c ON s.client_id = c.id
				LEFT JOIN 
					vehicles v ON m.vehicle_id = v.id
				LEFT JOIN 
					shipment_log sl6 ON s.id = sl6.shipment_id AND sl6.`to` = 6
				LEFT JOIN 
					shipment_log sl16 ON s.id = sl16.shipment_id AND sl16.`to` = 16
				LEFT JOIN 
					shipment_log sl7 ON s.id = sl7.shipment_id AND sl7.`to` = 7
				WHERE 
					DATE(s.shipping_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
					AND m.vehicle_id != 1
				GROUP BY
					s.id, s.code, m.code, c.code, s.sender_lat, s.sender_lng, 
					s.reciver_lat, s.reciver_lng, s.distance, m.vehicle_id, 
					m.fleet_type, s.client_id, v.id
			),

			trip_drop_points AS (
				SELECT
					trip_id,
					MAX(drop_point) AS total_drop_points_per_trip
				FROM
					ranked_shipments
				GROUP BY
					trip_id
			),

			distances AS (
				SELECT
					trip_id,
					ST_Distance_Sphere(
						POINT(
							LAG(reciver_lng) OVER (PARTITION BY trip_id ORDER BY ast),
							LAG(reciver_lat) OVER (PARTITION BY trip_id ORDER BY ast)
						),
						POINT(sender_lng, sender_lat)
					) / 1000 AS distance_km
				FROM
					ranked_shipments
			),

			trip_projects AS (
				SELECT
					trip_id,
					GROUP_CONCAT(DISTINCT project_id ORDER BY project_id) AS unique_project_ids
				FROM
					ranked_shipments
				GROUP BY
					trip_id
			),

			fleet_driver_count AS (
				SELECT
					m.code AS trip_id,
					COUNT(DISTINCT d.id) AS unique_driver_count,
					SUM(COUNT(DISTINCT d.id)) OVER (PARTITION BY m.vehicle_id ORDER BY m.code) AS accumulated_driver_count
				FROM
					missions m
				JOIN 
					drivers d ON m.captain_id = d.id
				GROUP BY
					m.code, m.vehicle_id
			),

			return_to_base_time AS (
				SELECT
					m.code AS trip_id,
					DATE_FORMAT(MAX(m.updated_at), '%Y-%m-%d %H:%i:%s') AS return_to_base_time
				FROM
					missions m
				WHERE
					m.status_id = 3
				GROUP BY
					m.code
			)

			SELECT
				v.license_plate AS fleet_id,			    
				COALESCE(ANY_VALUE(fleet_driver_count.unique_driver_count), 0) AS total_unique_drivers,
				COALESCE(ANY_VALUE(v.asset_type), 'N/A') AS vendor_internal,
				COALESCE(ANY_VALUE(v.location), 'N/A') AS pool,
				v.type AS fleet_type,
				CASE WHEN v.is_archived = 1 THEN 'NON ACTIVE' ELSE 'ACTIVE' END AS fleet_status,
				COUNT(DISTINCT ranked_shipments.trip_id) AS total_trip_count,
				COUNT(DISTINCT CONCAT(ranked_shipments.sender_lat, ', ', ranked_shipments.sender_lng, ' -> ', ranked_shipments.reciver_lat, ', ', ranked_shipments.reciver_lng)) AS total_drop_point_count,  -- Include NULL handling
				COUNT(DISTINCT ranked_shipments.awb_id) AS total_awb_count,
				ROUND(TIMESTAMPDIFF(SECOND, MIN(ast), MAX(alt)) / 3600, 2) AS accumulated_ted,
				'16' AS capacity_ted,
				CONCAT(ROUND((TIMESTAMPDIFF(SECOND, MIN(ast), MAX(alt)) / 3600) / 16 * 100, 2), '%') AS percentage_daily_fleet_utilization,
				ANY_VALUE(return_to_base_time.return_to_base_time) AS return_to_base
			FROM
				vehicles v  -- Use vehicles as the base table
			LEFT JOIN
				ranked_shipments ON v.id = ranked_shipments.vehicle_id  -- Left join to include all vehicles
			LEFT JOIN
				fleet_driver_count ON ranked_shipments.trip_id = fleet_driver_count.trip_id
			LEFT JOIN
				distances ON ranked_shipments.trip_id = distances.trip_id
			LEFT JOIN
				return_to_base_time ON ranked_shipments.trip_id = return_to_base_time.trip_id
			LEFT JOIN
				trip_drop_points ON ranked_shipments.trip_id = trip_drop_points.trip_id  -- Include drop points
			GROUP BY
				v.license_plate, v.asset_type, v.location, v.type, v.is_archived
			ORDER BY total_awb_count DESC

        ",
        'Project Productivity' => "
			SELECT
				project_id,
				SUM(COUNT(DISTINCT trip_id)) OVER (PARTITION BY project_id) AS acc_trip_id, -- Accumulated trip count per project_id
				SUM(COUNT(DISTINCT awb_id)) OVER (PARTITION BY project_id) AS acc_awb, -- Accumulated AWB count per project_id
				SUM(COUNT(DISTINCT fleet_id)) OVER (PARTITION BY project_id) AS acc_fleet, -- Accumulated fleet count per project_id
				SUM(awb_end_to_end_duration) AS acc_aed
				
			FROM (
				SELECT
					trip_id,
					shipment_id,
					awb_id,
					project_id,
					fleet_id,
					distance,
					ast,
					aet,
					LAG(ast) OVER (PARTITION BY trip_id ORDER BY ast) AS prev_ast,
					TIMESTAMPDIFF(SECOND, LAG(ast) OVER (PARTITION BY trip_id ORDER BY ast), ast) AS duration_seconds,
					-- Calculate the AWB end-to-end duration (difference between the first and last timestamps for each AWB)
					ROUND(TIMESTAMPDIFF(SECOND, MIN(ast) OVER (PARTITION BY awb_id), MAX(aet) OVER (PARTITION BY awb_id)) / 60, 2) AS awb_end_to_end_duration

				FROM (
					SELECT
						s.id AS shipment_id,
						s.code AS awb_id,
						m.code AS trip_id,
						c.code AS project_id,
						m.vehicle_id AS fleet_id,
						s.distance,
						MIN(sl6.created_at) AS ast,
						MAX(sl16.created_at) AS aet,
						ROW_NUMBER() OVER (
							PARTITION BY m.code
							ORDER BY MIN(sl6.created_at)
						) AS drop_point
					FROM 
						shipments s
					JOIN 
						shipment_mission sm ON s.id = sm.shipment_id
					JOIN 
						missions m ON sm.mission_id = m.id
					JOIN 
						clients c ON s.client_id = c.id
					LEFT JOIN 
						shipment_log sl6 ON s.id = sl6.shipment_id AND sl6.`to` = 6
					LEFT JOIN 
						shipment_log sl16 ON s.id = sl16.shipment_id AND sl16.`to` = 16
					GROUP BY
						s.id, s.code, m.code, c.code, s.sender_lat, s.sender_lng, s.reciver_lat, s.reciver_lng, s.distance, m.vehicle_id
				) AS ranked_shipments
			) AS lagged_shipments
			GROUP BY 
				project_id
			ORDER BY 
				acc_trip_id DESC, project_id

        ",
		'Driver_App_Usage' => "
			WITH ranked_shipments AS (
				SELECT
					v.license_plate,
					v.asset_type AS vendor_internal,
					d.name AS driver_name,  -- Fetch driver name
					CONCAT(s.sender_lat, ', ', s.sender_lng) AS entry_from_lat_lng,
					CONCAT(s.reciver_lat, ', ', s.reciver_lng) AS entry_to_lat_lng,
					CONCAT(sl16.lat, ', ', sl16.lng) AS actual_from_lat_lng,
					CONCAT(sl16.lat, ', ', sl16.lng) AS actual_to_lat_lng,
					s.distance,
					s.duration AS aed_in_minute,
					MIN(sl6.created_at) AS ast,  -- Earliest timestamp
					MAX(sl16.created_at) AS aet, -- Latest timestamp for 'to' = 16
					MAX(sl7.created_at) AS aat,  -- Latest timestamp for 'to' = 7
					ROW_NUMBER() OVER (
						PARTITION BY m.code  -- Reset for each trip
						ORDER BY MIN(sl6.created_at)  -- Order by earliest 'ast'
					) AS drop_point
				FROM 
					shipments s
				JOIN shipment_mission sm ON s.id = sm.shipment_id
				JOIN missions m ON sm.mission_id = m.id
				JOIN clients c ON s.client_id = c.id
				JOIN vehicles v ON m.vehicle_id = v.id
				LEFT JOIN drivers d ON s.captain_id = d.id  -- Fetch driver name
				LEFT JOIN shipment_log sl6 ON s.id = sl6.shipment_id AND sl6.`to` = 6
				LEFT JOIN shipment_log sl16 ON s.id = sl16.shipment_id AND sl16.`to` = 16
				LEFT JOIN shipment_log sl7 ON s.id = sl7.shipment_id AND sl7.`to` = 7
				WHERE 
					s.shipping_date = CURDATE() - INTERVAL 1 DAY
					AND m.vehicle_id != 1
				GROUP BY 
					d.name
			)

			SELECT
			 	license_plate,
				vendor_internal,
				driver_name,  -- Display driver name
				CASE 
					WHEN ast IS NULL THEN 'Driver tidak menggunakan app'
					WHEN (actual_from_lat_lng IS NULL OR actual_from_lat_lng = '-, -') AND ast IS NOT NULL THEN 'Driver tidak update aplikasi'
					ELSE 'OK'
				END AS status_penggunaan_aplikasi
			FROM 
				ranked_shipments
			ORDER BY 
				driver_name",

		'Late_Summary' => "
			WITH trip_projects AS (
				SELECT
					m.id AS mission_id,  
					m.code AS trip_id,
					GROUP_CONCAT(DISTINCT c.code ORDER BY c.code) AS unique_project_ids,
					
					-- Status for trip start
					CASE 
						WHEN TIME_TO_SEC(TIMEDIFF(m.actual_start, m.target_start)) > 0 THEN 'Late'
						WHEN m.actual_start IS NULL OR m.target_start IS NULL THEN 'No App Activity'
						ELSE 'On-Time'
					END AS status_start,
					
					-- Status for return to base (RTB)
					CASE 
						WHEN TIME_TO_SEC(TIMEDIFF(m.actual_rtb, m.due_date)) > 0 THEN 'Late'
						WHEN m.actual_rtb IS NULL OR m.due_date IS NULL THEN 'No App Activity'
						ELSE 'On-Time'
					END AS status_rtb
				FROM
					shipments s
				JOIN shipment_mission sm ON s.id = sm.shipment_id
				JOIN missions m ON sm.mission_id = m.id
				JOIN clients c ON s.client_id = c.id
				WHERE DATE(s.shipping_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
				GROUP BY m.id, m.code
			)

			SELECT 
				COUNT(DISTINCT mission_id) AS total_trips,  -- Total unique trips
				COUNT(CASE WHEN status_start = 'Late' THEN 1 END) AS late_start,
				COUNT(CASE WHEN status_start = 'On-Time' THEN 1 END) AS on_time_start,
				COUNT(CASE WHEN status_start = 'No App Activity' THEN 1 END) AS no_app_activity_start,

				COUNT(CASE WHEN status_rtb = 'Late' THEN 1 END) AS late_rtb,
				COUNT(CASE WHEN status_rtb = 'On-Time' THEN 1 END) AS on_time_rtb,
				COUNT(CASE WHEN status_rtb = 'No App Activity' THEN 1 END) AS no_app_activity_rtb
			FROM trip_projects"

    ];

    // Header mappings for each query
    $headerMappings = [
        'AWB Productivity' => [
            'awb_id' => 'AWB ID',
            'trip_id' => 'Trip ID',
            'vendor_internal' => 'vendor/Internal',
            'sequences' => 'Sequences',
            'project_id' => 'Project ID',
            'nama_penerima' => 'Nama Penerima',
            'entry_from_lat_lng' => 'Entry From (Lat, Lng)',
            'entry_to_lat_lng' => 'Entry To (Lat, Lng)',
			'actual_from_lat_lng' => 'Actual From (Lat, Lng)',
            'actual_to_lat_lng' => 'Actual To (Lat, Lng)',
            'distance' => 'Distance (KM)',
            'ast' => 'AST (Actual Start Time)',
            'aet' => 'AET (Actual End Time)',
            'aat' => 'AAT (Actual Arrival Time)',
            'alt' => 'ALT (Actual Load Time)',
            'aibd' => 'AIBD (AWB inBetween Duration)',
            'atd' => 'ATD (AWB Travel Duration)',
            'aed' => 'AED'
        ],
        'Trip Productivity' => [
            'trip_id' => 'Trip ID',
            'fleet_id' => 'Fleet ID',
            'fleet_type' => 'Fleet Type',
            'total_drop_point' => 'Total Drop Points',
            'start' => 'Start (Lat, Lng)',
            'end' => 'End (Lat, Lng)',
            'distance' => 'Distance (KM)',
            'tdp' => 'TDP (Total Drop-Point Count)',
            'tac' => 'TAC (Total AWB Count)',
            'tst' => 'TST (Trip Start Time)',
            'tet' => 'TET (Trip End Time)',
            'aad' => 'AAD (Average AWB Duration)',
            'ted' => 'TED (Trip Elapsed Duration)',
            'target_start' => 'Target Trip Strart',
            'actual_start' => 'Actual Trip Start',
            'status_start' => 'Trip Activity Status',
            'target_rtb' => 'Target Return to Base',
            'actual_rtb' => 'Actual Return to Base',
            'status_rtb' => 'Return to Base Activity Status'
        ],
        'Fleet Productivity' => [
            'fleet_id' => 'Fleet ID',
            'total_unique_drivers' => 'Unique Driver',
            'vendor_internal' => 'Vendor/Internal',
            'pool' => 'Pool',
            'fleet_type' => 'Fleet Type',
            'fleet_status' => 'Fleet Status',
            'total_trip_count' => 'Total TRIP (#)',
            'total_drop_point_count' => 'Total Drop Point (#)',
            'total_awb_count' => 'Total AWB (#)',
            'accumulated_ted' => 'Accumulated TED (#)', //in Hours
            'capacity_ted' => 'Capacity TED',
            'percentage_daily_fleet_utilization' => 'Daily Fleet Utilization (%)'
        ],
        'Project Productivity' => [
            'project_id' => 'Project ID',
            'aac_trip_id' => 'Accumulated Trip (#)',
            'acc_awb' => 'Accumulated AWB (#)',
            'acc_fleet' => 'Accumulated Fleet ID (#)',
            'acc_aed' => 'Accumulated AED'
        ],
		'Trip Summary' => [
            'total_trips' => 'Total Trip',
            'on_time_start' => 'On-Time Start Trip',
            'late_start' => 'Late Start Trip',
            'no_app_activity_start' => 'No App Activity Start/End Trip',
            'on_time_rtb' => 'On-Time Return to Base',
            'late_rtb' => 'Late Return to Base',
            'no_app_activity_rtb' => 'No App Activity Return to Base',
        ],		
		'Driver App Usage Report' => [
            'license_plate' => 'Plate Number',
            'vendor_internal' => 'Vendor/Internal',
            'driver_name' => 'Driver Name',
            'status_penggunaan_aplikasi' => 'Mobile App Usage'
        ]
    ];


    // Initialize PhpSpreadsheet
$spreadsheet = new Spreadsheet();
$sheetIndex = 0;

foreach ($queries as $sheetName => $query) {
    // Skip specific sheets
    if (in_array($sheetName, ['Driver_App_Usage', 'Late_Summary'])) {
        continue; // Skip this iteration and move to the next query
    }

    // Fetch data
    $stmt = $pdo->query($query);

    if ($stmt->rowCount() > 0) {
        if ($sheetIndex > 0) {
            $spreadsheet->createSheet();
        }
        $spreadsheet->setActiveSheetIndex($sheetIndex);
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle($sheetName);

        // Fetch column names
        $columnHeaders = array_keys($stmt->fetch(PDO::FETCH_ASSOC));
        $stmt->execute();

        // Rename headers based on the mapping
        $mappedHeaders = array_map(function ($header) use ($headerMappings, $sheetName) {
            return $headerMappings[$sheetName][$header] ?? $header;
        }, $columnHeaders);

        // Add "Row Number" column to the headers
        array_unshift($mappedHeaders, 'No.');

        // Set renamed headers in the first row
        $sheet->fromArray($mappedHeaders, null, 'A1');

        // Apply formatting to the headers
        $lastColumnLetter = Coordinate::stringFromColumnIndex(count($mappedHeaders)); // Get last column letter
        $headerRange = "A1:{$lastColumnLetter}1"; // Example: A1:D1

        $sheet->getStyle($headerRange)->applyFromArray([
            'font' => [
                'bold' => true,
                'color' => ['rgb' => 'FFFFFF'], // White text color
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => '4F81BD'], // Blue background color
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ]);

        // Populate data rows with row number
        $row = 2;
        $rowNumber = 1;
        while ($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $dataWithRowNumber = array_merge([$rowNumber], array_values($data));
            $sheet->fromArray($dataWithRowNumber, null, "A$row");
            $row++;
            $rowNumber++;
        }

        // Auto-size columns
        for ($col = 1; $col <= count($mappedHeaders); $col++) {
            $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($col))->setAutoSize(true);
        }

        $sheetIndex++;
    }
}



    // Save spreadsheet
    $filename = 'TMS_Productivity_' . date('Y-m-d') . '.xlsx';
    $directory = 'files/tms_kpi/';
    if (!file_exists($directory)) {
        mkdir($directory, 0755, true);
    }
    $filePath = $directory . $filename;
    $writer = new Xlsx($spreadsheet);
    $writer->save($filePath);

$driverAppUsageStmt = $pdo->query($queries['Driver_App_Usage']);
$driverAppUsageResults = $driverAppUsageStmt->fetchAll(PDO::FETCH_ASSOC);

$lateSummaryStmt = $pdo->query($queries['Late_Summary']);
$lateSummaryStmtResults = $lateSummaryStmt->fetchAll(PDO::FETCH_ASSOC);

// Initialize the email body variable
$emailBody = "";
// Initialize $tripSummary with default values
$tripSummary = [
    'total_trips' => 0,
    'late_start' => 0,
    'on_time_start' => 0,
    'no_app_activity_start' => 0,
    'late_rtb' => 0,
    'on_time_rtb' => 0,
    'no_app_activity_rtb' => 0
];

// Check if $lateSummaryStmtResults contains data
if (!empty($lateSummaryStmtResults)) {
    foreach ($lateSummaryStmtResults as $row) {
        // Assign values from $lateSummaryStmtResults to $tripSummary
        $tripSummary['total_trips'] += $row['total_trips'];
        $tripSummary['late_start'] += $row['late_start'];
        $tripSummary['on_time_start'] += $row['on_time_start'];
        $tripSummary['no_app_activity_start'] += $row['no_app_activity_start'];
        $tripSummary['late_rtb'] += $row['late_rtb'];
        $tripSummary['on_time_rtb'] += $row['on_time_rtb'];
        $tripSummary['no_app_activity_rtb'] += $row['no_app_activity_rtb'];
    }
}

if (empty($driverAppUsageResults)) {
    $emailBody .= "<tr><td colspan='5' style='padding: 8px;'>No data available for Driver App Usage.</td></tr>";
} else {
    foreach ($driverAppUsageResults as $row) {
        $emailBody .= "<tr>
            <td style='padding: 8px;'>{$row['license_plate']}</td>
            <td style='padding: 8px;'>{$row['vendor_internal']}</td>
            <td style='padding: 8px;'>{$row['driver_name']}</td>
            <td style='padding: 8px;'>{$row['status_penggunaan_aplikasi']}</td>
        </tr>";
    }
}



    // Send email with attachment
    $mail = new PHPMailer(true);
    try {
        $mail->isSMTP();
        $mail->Host = 'smtp.larksuite.com';
        $mail->SMTPAuth = true;
        $mail->Username = 'no-reply@freshfactory.id';
        $mail->Password = 'T8L23W3yndDVc8iJ';
        $mail->SMTPSecure = 'tls';
        $mail->Port = 587;

        $mail->setFrom('report@freshfactory.id', 'Fresh Factory');
        $mail->addReplyTo('no-reply@freshfactory.id', 'Report Fresh Factory');
        $mail->addAddress('zulfikar@freshfactory.id', 'Zulfikar');
		// $mail->addAddress('larry.ridwan@freshfactory.id', 'Larry Ridwan');
		// $mail->addAddress('andre.septiano@freshfactory.id', 'Andre Septiano');
		// $mail->addAddress('danny.ramintang@freshfactory.id', 'Danny');
		// $mail->addAddress('andhy@frex.co.id', 'Yusuf');
		// $mail->addAddress('m.rafli@frex.co.id', 'M. Rafli');
		// $mail->addAddress('farah.dini@frex.co.id', 'Farah Dini');
        $mail->addAttachment($filePath);

        $mail->isHTML(true);
        $mail->Subject = 'TMS Productivity Report';
        $mail->Body = 'Please find the attached productivity report.';
		
// Define the email body with inline CSS
$emailBody = "
    <h3>Trip Summary Report</h3>
    <table border='1' cellspacing='0' cellpadding='5' style='border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; font-size: 14px; text-align: center;'>
        <tr style='background-color: #f2f2f2;'>
            <th rowspan='2' style='padding: 8px; text-align: left;'>Total Trips</th>
            <th colspan='3' style='padding: 8px; text-align: center;'>Trip Activity</th>
            <th colspan='3' style='padding: 8px; text-align: center;'>Return to Base Activity</th>
        </tr>
        <tr style='background-color: #f2f2f2;'>
            <th style='padding: 8px;'>Late Start</th>
            <th style='padding: 8px;'>On-Time Start</th>
            <th style='padding: 8px;'>No App Activity Start</th>
            <th style='padding: 8px;'>Late RTB</th>
            <th style='padding: 8px;'>On-Time RTB</th>
            <th style='padding: 8px;'>No App Activity RTB</th>
        </tr>";

$emailBody .= "<tr>
    <td style='padding: 8px;'><strong>{$tripSummary['total_trips']}</strong></td>
    <td style='padding: 8px; color: darkred;'><strong>{$tripSummary['late_start']}</strong></td>
    <td style='padding: 8px;'><strong>{$tripSummary['on_time_start']}</strong></td>
    <td style='padding: 8px;'><strong>{$tripSummary['no_app_activity_start']}</strong></td>
    <td style='padding: 8px; color: darkred;'><strong>{$tripSummary['late_rtb']}</strong></td>
    <td style='padding: 8px;'><strong>{$tripSummary['on_time_rtb']}</strong></td>
    <td style='padding: 8px;'><strong>{$tripSummary['no_app_activity_rtb']}</strong></td>
</tr>";

$emailBody .= "</table><br><br>"; // Separate sections with a line break


			// Add Driver App Usage Report
			$emailBody .= "
			<h3>Driver App Usage Report</h3>
			<table border='1' cellspacing='0' cellpadding='5' style='border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; font-size: 14px;'>
				<tr style='background-color: #f2f2f2;'>
					<th style='padding: 8px; text-align: left;'>Plate Number</th>
					<th style='padding: 8px; text-align: left;'>Vendor/Internal</th>
					<th style='padding: 8px; text-align: left;'>Driver Name</th>
					<th style='padding: 8px; text-align: left;'>Status Penggunaan Aplikasi</th>
				</tr>";

			foreach ($driverAppUsageResults as $row) {
				$emailBody .= "<tr>
					<td style='padding: 8px;'>{$row['license_plate']}</td>
					<td style='padding: 8px;'>{$row['vendor_internal']}</td>
					<td style='padding: 8px;'>{$row['driver_name']}</td>
					<td style='padding: 8px;'>{$row['status_penggunaan_aplikasi']}</td>
				</tr>";
			}

			$emailBody .= "</table>"; // Close the driver report table


		// Set email body
		$mail->Body = $emailBody;

        $mail->send();
        echo 'Email has been sent successfully.';
    } catch (Exception $e) {
        echo "Email could not be sent. Mailer Error: {$mail->ErrorInfo}";
    }
} catch (PDOException $e) {
    echo 'Database connection failed: ' . $e->getMessage();
}