<?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.code AS awb_id,
					c.name as customer_name,
					m.code AS trip_id,
					d.name as driver_name,
					CASE 
						WHEN JSON_UNQUOTE(JSON_EXTRACT(s.addons, '$.pod_active')) = '1' 
						THEN 'Perlu' 
						ELSE 'Tidak Perlu' 
					END AS pod_fisik,
					CASE 
						WHEN JSON_UNQUOTE(JSON_EXTRACT(s.addons, '$.grn')) = '1' 
						THEN 'Perlu' 
						ELSE 'Tidak Perlu' 
					END AS grn_fisik,
					'' AS tukar_faktur,
					'' AS pod_kode_alamat,
					'' AS pod_nama_alamat,
					pod_name AS pod_nama_kontak,
					v.asset_type AS vendor_internal,
					ROW_NUMBER() OVER (
						PARTITION BY m.code   -- Reset the drop_point for each trip_id
						ORDER BY MIN(sl16.created_at)  -- Order by earliest 'ast' for each shipment
					) AS drop_point,
					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,
					


					
					s.sender_lat AS entry_from_lat,
					s.sender_lng AS entry_from_lng,
					s.reciver_lat AS entry_to_lat,
					s.reciver_lng AS entry_to_lng,
					CONCAT(sl6.lat, ', ', sl6.lng) AS actual_from_lat_lng,
					CONCAT(sl7.lat, ', ', sl7.lng) AS actual_to_lat_lng,
					s.sender_lat AS actual_from_lat,   -- Pickup location
					s.sender_lng AS actual_from_lng,
					sl16.lat AS actual_to_lat,   -- Delivered location
					sl16.lng AS actual_to_lng,

					s.distance,
					s.duration AS aed_in_minute,
					MIN(sl6.created_at) AS ast,
					MAX(sl7.created_at) AS aat,
					MAX(sl16.created_at) AS alt,
					MAX(sl16.created_at) AS aet,
					v.location as pool,
					v.type as fleet_type
				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 
				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
					AND s.client_id != 27
				GROUP BY
					s.id, s.code, m.code, c.code, s.sender_lat, s.sender_lng, s.reciver_lat, s.reciver_lng, s.distance
			), 
			distance_data AS (
				SELECT 
					*,
					CASE
						-- First drop point: Calculate distance from warehouse
						WHEN drop_point = 1 THEN 
							ROUND(ST_Distance_Sphere(
								POINT(entry_from_lng, entry_from_lat),  -- Warehouse location
								POINT(entry_to_lng, entry_to_lat)       -- First drop location
							) / 1000, 2)  
						-- Subsequent drop points: Calculate distance from previous drop
						ELSE
							ROUND(ST_Distance_Sphere(
								POINT(
									COALESCE(LAG(entry_to_lng) OVER (PARTITION BY trip_id ORDER BY ast), entry_from_lng), 
									COALESCE(LAG(entry_to_lat) OVER (PARTITION BY trip_id ORDER BY ast), entry_from_lat)
								),
								POINT(entry_to_lng, entry_to_lat)
							) / 1000, 2)
					END AS distance_from_previous_drop_point_km,
					
					-- Ensure `wh_distance_km` follows the same format
					ROUND(ST_Distance_Sphere(
						POINT(entry_from_lng, entry_from_lat),  -- Warehouse location
						POINT(entry_to_lng, entry_to_lat)       -- First drop location
					) / 1000, 2) AS wh_distance_km,
					
					-- Actual Distance Calculation (for actual_from_* and actual_to_*)
					CASE
						-- First drop point: Calculate distance from warehouse
						WHEN drop_point = 1 THEN 
							ROUND(ST_Distance_Sphere(
								POINT(actual_from_lng, actual_from_lat),  -- Warehouse actual location
								POINT(actual_to_lng, actual_to_lat)       -- First drop actual location
							) / 1000, 2)  
						-- Subsequent drop points: Calculate distance from previous actual drop
						ELSE
							ROUND(ST_Distance_Sphere(
								POINT(
									COALESCE(LAG(actual_to_lng) OVER (PARTITION BY trip_id ORDER BY ast), actual_from_lng), 
									COALESCE(LAG(actual_to_lat) OVER (PARTITION BY trip_id ORDER BY ast), actual_from_lat)
								),
								POINT(actual_to_lng, actual_to_lat)
							) / 1000, 2)
					END AS actual_distance_from_previous_drop_point_km,
					
					-- Ensure `actual_wh_distance_km` follows the same format
					ROUND(ST_Distance_Sphere(
						POINT(
							COALESCE(actual_from_lng, 0),  -- Default to 0 if NULL
							COALESCE(actual_from_lat, 0)
						),  
						POINT(
							COALESCE(actual_to_lng, actual_from_lng),  -- Use from_lng if to_lng is NULL
							COALESCE(actual_to_lat, actual_from_lat)
						)
					) / 1000, 2) AS actual_wh_distance_kmxx,
					
					ROUND(ST_Distance_Sphere(
				POINT(COALESCE(actual_from_lng, 0), COALESCE(actual_from_lat, 0)),  
				POINT(COALESCE(actual_to_lng, 0), COALESCE(actual_to_lat, 0))
			) / 1000, 2) AS actual_wh_distance_km

					
						FROM ranked_shipments
					)
					
					SELECT 
						awb_id, 
						project_id,
						customer_name as project_name,     
						trip_id, 
						drop_point as actual_sequence, 
						driver_name,
						pool,
						fleet_type, 
						pod_fisik, 
						grn_fisik, 
						tukar_faktur, 
						pod_kode_alamat, 
						pod_nama_alamat, 
						pod_nama_kontak, 
						vendor_internal,     
						nama_penerima, 
						entry_from_lat, 
						entry_from_lng,
						entry_to_lat, 
						entry_to_lng,
						wh_distance_km wh_km_line,
						distance_from_previous_drop_point_km as drop_point_km_line,
						actual_wh_distance_km,
						actual_distance_from_previous_drop_point_km,
						
						actual_from_lat,
						actual_from_lng,
						actual_to_lat,
						actual_to_lng,
						DATE_FORMAT(ast, '%Y-%m-%d %H:%i:%s') AS ast,
						DATE_FORMAT(aat, '%Y-%m-%d %H:%i:%s') AS aat,
						DATE_FORMAT(alt, '%Y-%m-%d %H:%i:%s') AS alt,
						DATE_FORMAT(aet, '%Y-%m-%d %H:%i:%s') AS aet,
						SEC_TO_TIME(TIMESTAMPDIFF(SECOND, ast, aet)) AS aed,
						SEC_TO_TIME(TIMESTAMPDIFF(SECOND, ast, aat)) AS atd, 
						SEC_TO_TIME(
							TIMESTAMPDIFF(SECOND, ast, aet) - TIMESTAMPDIFF(SECOND, ast, aat)
						) AS ald,
						CASE
							WHEN drop_point = 1 THEN '00:00:00'  
							ELSE SEC_TO_TIME(
								TIMESTAMPDIFF(MINUTE, 
									LAG(ast) OVER (PARTITION BY trip_id ORDER BY ast),
									ast
								) * 60  
							)
						END AS aibd,
						
						CASE
							WHEN TIMESTAMPDIFF(SECOND, ast, aet) > 0 
							THEN ROUND(distance_from_previous_drop_point_km / (NULLIF(TIMESTAMPDIFF(SECOND, ast, aet), 0) / 3600), 2)
							ELSE NULL
						END AS avg_speed_kmh,
						'' AS parking_fee,
						'' AS calculated_travel_expense,
						'' AS entry_travel_expense,
						'' AS entry_driver_expense,
						'' AS entry_fleet_expense,
						'' AS entry_sequence,
						'' AS entry_km_route
						
						   
			-- 		    SEC_TO_TIME(TIMESTAMPDIFF(SECOND, aat, aet)) AS parking_incurred
					FROM distance_data
					ORDER BY trip_id, drop_point 
				",
        'Trip Productivity' => "
			WITH ranked_shipments AS (
				SELECT
					s.id AS shipment_id,
					s.code AS awb_id,
					v.location as base_pool,
					m.code AS trip_id,
					c.code AS project_id,
					v.license_plate AS fleet_id,
					m.fleet_type,
					d.name as driver_name,
					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:%s') target_start,
					DATE_FORMAT(m.actual_start, '%Y-%m-%d %H:%i:%s') 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:%s') target_rtb,
					DATE_FORMAT(m.actual_rtb, '%Y-%m-%d %H:%i:%s') 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 
					drivers d ON s.captain_id = d.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
				AND s.client_id != 27
				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
				base_pool,
				trip_id,
				ANY_VALUE(fleet_id) AS fleet_id,
				ANY_VALUE(fleet_type) AS fleet_type,
				driver_name,
				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,
				DATE_FORMAT(MIN(ast), '%Y-%m-%d %H:%i:%s') AS tst,  -- Include seconds
				DATE_FORMAT(MAX(alt), '%Y-%m-%d %H:%i:%s') AS tet,  -- Include seconds
				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
					AND s.client_id != 27
				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.actual_rtb), '%Y-%m-%d %H:%i:%s') AS return_to_base_time
				FROM
					missions m
				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,
				COALESCE(ANY_VALUE(return_to_base_time.return_to_base_time), 'No App Activity') 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.name 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
					WHERE s.client_id != 27
					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,
					m.actual_start,
					m.actual_rtb,
					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
					AND s.client_id != 27
				GROUP BY 
					d.name
			)

			SELECT
			 	license_plate,
				vendor_internal,
				driver_name,  -- Display driver name
				CASE 
					WHEN ast IS NULL OR actual_start IS NULL OR actual_rtb 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)
				AND m.vehicle_id != 1
				AND s.client_id != 27
				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',
            'project_id' => 'Project ID',
            'project_name' => 'Project Name', 
			'trip_id' => 'Trip ID', 
			'actual_sequence' => 'Actual Sequence',
            'driver_name' => 'Driver Name',
            'pool' => 'Pool',
            'fleet_type' => 'Fleet Type',
            'pod_fisik' => 'PoD Fisik',
            'grn_fisik' => 'GRN Fisik',
            'tukar_faktur' => 'Tukar Faktur',
            'pod_kode_alamat' => 'Kode Alamat',
            'pod_nama_alamat' => 'Nama Alamat',
            'pod_nama_kontak' => 'Nama Kontak',
            'vendor_internal' => 'Vendor/Internal',
            'sequences' => 'Sequences',
            'project_id' => 'Project ID',
            'nama_penerima' => 'Nama Penerima',
            'entry_from_lat' => 'Entry From (Lat)',
            'entry_from_lng' => 'Entry From (Lng)',
            'entry_to_lat' => 'Entry To (Lat)',
            'entry_to_lng' => 'Entry To (Lng)',
            'wh_km_line' => 'Entry KM Line (From)',
            'drop_point_km_line' => 'Entry KM Line (To-1)',
			'actual_from_lat' => 'Actual From (Lat)',
			'actual_from_lng' => 'Actual From (Lng)',
			'actual_to_lat' => 'Actual To (Lat)',
			'actual_to_lng' => 'Actual To (Lng)',
			'actual_wh_distance_km' => 'Actual KM Line (From)',
			'actual_distance_from_previous_drop_point_km' => 'Actual KM Line (To-1)',
			'ast' => 'AST (Actual Start Time)',
			'aat' => 'AAT (Actual Arrival Time)',
			'alt' => 'ALT (Actual Unload Time)',
			'aet' => 'AET (Actual End Time)',
			'aed' => 'AWB-E2E Duration',
			'atd' => 'ATD Duration',
			'ald' => 'ALD Duration',
			'aibd' => 'AIBD (AWB inBetween Duration)',
			'avg_speed_kmh' => 'Calc.Avg Speed Km/h',
			'parking_fee' => 'Parking Fee',
			'calculated_travel_expense' => 'Travel Exp. (Calc)',
			'entry_travel_expense' => 'Travel Exp. (Entry)',
			'entry_driver_expense' => 'Driver Exp. (Entry)',
			'entry_fleet_expense' => 'Fleet Exp. (Entry)',
			'entry_sequence' => 'Sequence (Entry)',
			'entry_km_route' => 'KM Route (Entry)'
		],
        'Trip Productivity' => [
            'base_pool' => 'Base Pool',
            'trip_id' => 'Trip ID',
            'fleet_id' => 'Fleet ID',
            'fleet_type' => 'Fleet Type',
            'driver_name' => 'Driver Name',
			'start' => 'Start (Lat, Lng)',
			'end' => 'End (Lat, Lng)',
			'distance' => 'Distance (KM)',
			'tac' => 'TAC (Total AWB Count)',
			'tdp' => 'TDP (Total Drop-Point Count)',
			'tst' => 'TST (Trip Start Time)',
			'tet' => 'TET (Trip End Time)',
			'actual_start' => 'Actual Trip Start (1st AWB)',
			'tet' => 'Actual Trip End (End AWB)',
			'ted' => 'TED (Trip Elapsed Duration)',
			'target_start' => 'Target Trip Start',
			'target_rtb' => 'Target Return to Base',
			'status_start' => 'Trip Activity Status',
			'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 (%)',
            'return_to_base' => 'Actual Return to Base'
        ],
        '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, ['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('shabrina.nisa@freshfactory.id', 'Shabrina');
		$mail->addAddress('danny.ramintang@freshfactory.id', 'Danny');
		$mail->addAddress('ineke.rainy@freshfactory.id', 'Ineke Rainy');
		$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->addAddress('Naufal.nasution@frex.co.id', 'Naufal Nasution');
		$mail->addAddress('yayan.fitriyana@frex.co.id', 'Yayan Fitriyana');
        $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();
}