<?php
/**
 * Reports Page
 * Dozar & Tripper Management System
 */

require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/nepali_calendar.php';

$user = getCurrentUser();
$pdo = getPDO();

// Get current BS month range for default filter
$current_bs = get_current_nepali_date();
$bs_month_start = sprintf('%04d-%02d-%02d', $current_bs['year'], $current_bs['month'], 1);
$bs_days_in_month = get_nepali_days_in_month($current_bs['month'], $current_bs['year']);
$bs_month_end = sprintf('%04d-%02d-%02d', $current_bs['year'], $current_bs['month'], $bs_days_in_month);

// Get filter parameters
$report_type = $_GET['report_type'] ?? 'dozar';
$date_from = $_GET['date_from'] ?? $bs_month_start;
$date_to = $_GET['date_to'] ?? $bs_month_end;
$project_id = $_GET['project_id'] ?? '';
$vehicle_id = $_GET['vehicle_id'] ?? '';
$trip_number = $_GET['trip_number'] ?? '';
$entity_type = $_GET['entity_type'] ?? '';

// Get projects
$projects = [];
$stmt = $pdo->query("SELECT * FROM projects WHERE is_active = 1 ORDER BY project_name");
while ($row = $stmt->fetch()) {
    $projects[$row['id']] = $row['project_name'];
}

// Get vehicles for vehicle filter
$vehicles = [];
$stmt = $pdo->query("SELECT id, vehicle_number FROM vehicles WHERE is_active = 1 ORDER BY vehicle_number");
while ($row = $stmt->fetch()) {
    $vehicles[$row['id']] = $row['vehicle_number'];
}

// Build query based on report type
$records = [];
$vehicle_summary = [];

if ($report_type === 'dozar') {
    $where = "WHERE d.created_date BETWEEN ? AND ?";
    $params = [$date_from, $date_to];
    if ($project_id) {
        $where .= " AND d.project_id = ?";
        $params[] = $project_id;
    }

    $sql = "SELECT d.*, p.project_name, v.vehicle_number, dr.full_name as driver_name
            FROM dozar_records d
            LEFT JOIN projects p ON d.project_id = p.id
            LEFT JOIN vehicles v ON d.vehicle_id = v.id
            LEFT JOIN drivers dr ON d.driver_id = dr.id
            $where ORDER BY d.created_date DESC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $records = $stmt->fetchAll();

} elseif ($report_type === 'tripper_trip') {
    // Trip-wise report: each trip with trip_number per vehicle per day
    $where = "WHERE t.created_date BETWEEN ? AND ?";
    $params = [$date_from, $date_to];
    if ($project_id) {
        $where .= " AND t.project_id = ?";
        $params[] = $project_id;
    }
    if ($vehicle_id) {
        $where .= " AND t.vehicle_id = ?";
        $params[] = $vehicle_id;
    }

    if ($entity_type) {
        $where .= " AND df.entity_type = ?";
        $params[] = $entity_type;
    }

    // Use ROW_NUMBER to assign trip_number per vehicle per day
    $sql = "SELECT sub.* FROM (
                SELECT t.*, p.project_name, v.vehicle_number, v.vehicle_type, v.capacity,
                       dr.full_name as driver_name, dr.phone_number as driver_phone,
                       df.entity_name as deployed_from, df.entity_type,
                       lc.full_name as loading_collector_name,
                       uc.full_name as unloading_collector_name,
                       ROW_NUMBER() OVER (PARTITION BY t.vehicle_id, t.created_date ORDER BY t.id ASC) as trip_number
                FROM tripper_records t
                LEFT JOIN projects p ON t.project_id = p.id
                LEFT JOIN vehicles v ON t.vehicle_id = v.id
                LEFT JOIN drivers dr ON t.driver_id = dr.id
                LEFT JOIN deployed_from df ON t.deployed_from_id = df.id
                LEFT JOIN users lc ON t.loading_collector_id = lc.id
                LEFT JOIN users uc ON t.unloading_collector_id = uc.id
                $where
            ) sub";

    // Filter by trip number if selected
    if ($trip_number !== '' && $trip_number !== '0') {
        $sql .= " WHERE sub.trip_number = ?";
        $params[] = intval($trip_number);
    }

    $sql .= " ORDER BY sub.created_date DESC, sub.vehicle_number, sub.trip_number";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $records = $stmt->fetchAll();

    // Find max trip number for the filter dropdown
    $where2 = "WHERE t.created_date BETWEEN ? AND ?";
    $params2 = [$date_from, $date_to];
    if ($project_id) { $where2 .= " AND t.project_id = ?"; $params2[] = $project_id; }
    if ($vehicle_id) { $where2 .= " AND t.vehicle_id = ?"; $params2[] = $vehicle_id; }
    if ($entity_type) { $where2 .= " AND df.entity_type = ?"; $params2[] = $entity_type; }
    $max_sql = "SELECT MAX(trip_num) as max_trip FROM (
                    SELECT ROW_NUMBER() OVER (PARTITION BY t.vehicle_id, t.created_date ORDER BY t.id ASC) as trip_num
                    FROM tripper_records t
                    LEFT JOIN deployed_from df ON t.deployed_from_id = df.id
                    $where2
                ) tmp";
    $stmt2 = $pdo->prepare($max_sql);
    $stmt2->execute($params2);
    $max_trip = intval($stmt2->fetchColumn() ?: 0);

} elseif ($report_type === 'tripper_vehicle') {
    // Vehicle No-wise report: grouped by vehicle
    $where = "WHERE t.created_date BETWEEN ? AND ?";
    $params = [$date_from, $date_to];
    if ($project_id) {
        $where .= " AND t.project_id = ?";
        $params[] = $project_id;
    }
    if ($vehicle_id) {
        $where .= " AND t.vehicle_id = ?";
        $params[] = $vehicle_id;
    }

    if ($entity_type) {
        $where .= " AND df.entity_type = ?";
        $params[] = $entity_type;
    }

    // Get summary grouped by vehicle
    $sql = "SELECT v.vehicle_number, v.vehicle_type, v.capacity,
                   COUNT(t.id) as total_trips,
                   SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_trips,
                   SUM(CASE WHEN t.status = 'pending_unloading' THEN 1 ELSE 0 END) as pending_trips,
                   SUM(CASE WHEN t.status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_trips,
                   SUM(CASE WHEN t.unloading_end_odometer > t.loading_start_odometer THEN t.unloading_end_odometer - t.loading_start_odometer ELSE 0 END) as total_km,
                   MIN(t.created_date) as first_trip_date,
                   MAX(t.created_date) as last_trip_date,
                   GROUP_CONCAT(DISTINCT dr.full_name SEPARATOR ', ') as drivers_used,
                   GROUP_CONCAT(DISTINCT p.project_name SEPARATOR ', ') as projects_used
            FROM tripper_records t
            LEFT JOIN vehicles v ON t.vehicle_id = v.id
            LEFT JOIN drivers dr ON t.driver_id = dr.id
            LEFT JOIN projects p ON t.project_id = p.id
            LEFT JOIN deployed_from df ON t.deployed_from_id = df.id
            $where
            GROUP BY t.vehicle_id, v.vehicle_number, v.vehicle_type, v.capacity
            ORDER BY total_trips DESC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $vehicle_summary = $stmt->fetchAll();

    // Also get individual trip records for the expanded detail view
    $sql2 = "SELECT t.*, p.project_name, v.vehicle_number, dr.full_name as driver_name,
                    df.entity_name as deployed_from, df.entity_type
             FROM tripper_records t
             LEFT JOIN projects p ON t.project_id = p.id
             LEFT JOIN vehicles v ON t.vehicle_id = v.id
             LEFT JOIN drivers dr ON t.driver_id = dr.id
             LEFT JOIN deployed_from df ON t.deployed_from_id = df.id
             $where ORDER BY v.vehicle_number, t.created_date DESC";
    $stmt2 = $pdo->prepare($sql2);
    $stmt2->execute($params);
    $all_records = $stmt2->fetchAll();

    // Group records by vehicle number
    $records_by_vehicle = [];
    foreach ($all_records as $r) {
        $vn = $r['vehicle_number'] ?? 'Unknown';
        $records_by_vehicle[$vn][] = $r;
    }

} else {
    // Default tripper list (original)
    $where = "WHERE t.created_date BETWEEN ? AND ?";
    $params = [$date_from, $date_to];
    if ($project_id) {
        $where .= " AND t.project_id = ?";
        $params[] = $project_id;
    }
    if ($entity_type) {
        $where .= " AND df.entity_type = ?";
        $params[] = $entity_type;
    }

    $sql = "SELECT t.*, p.project_name, v.vehicle_number, dr.full_name as driver_name,
                   df.entity_name as deployed_from, df.entity_type
            FROM tripper_records t
            LEFT JOIN projects p ON t.project_id = p.id
            LEFT JOIN vehicles v ON t.vehicle_id = v.id
            LEFT JOIN drivers dr ON t.driver_id = dr.id
            LEFT JOIN deployed_from df ON t.deployed_from_id = df.id
            $where ORDER BY t.created_date DESC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $records = $stmt->fetchAll();
}

// Calculate totals
$total_records = count($records);
$total_hours = 0;
$total_km = 0;
$total_completed = 0;
$total_pending = 0;

if ($report_type === 'dozar') {
    foreach ($records as $r) {
        $total_hours += floatval($r['work_length_hours'] ?? 0);
    }
} elseif ($report_type === 'tripper_vehicle') {
    $total_records = 0;
    foreach ($vehicle_summary as $vs) {
        $total_records += intval($vs['total_trips']);
        $total_km += floatval($vs['total_km']);
        $total_completed += intval($vs['completed_trips']);
        $total_pending += intval($vs['pending_trips']);
    }
} else {
    foreach ($records as $r) {
        $start = floatval($r['loading_start_odometer'] ?? 0);
        $end = floatval($r['unloading_end_odometer'] ?? 0);
        $total_km += ($end > $start) ? ($end - $start) : 0;
        if (($r['status'] ?? '') === 'completed') $total_completed++;
        if (($r['status'] ?? '') === 'pending_unloading') $total_pending++;
    }
}
?>
<?php include __DIR__ . '/../includes/header.php'; ?>

<style>
/* Premium Print & Report Styles */
:root {
    --report-primary: #1a237e;
    --report-secondary: #455a64;
    --report-light: #f8f9fa;
    --report-border: #dee2e6;
}

@media print {
    @page {
        size: A4 landscape;
        margin: 1cm;
    }
    body {
        font-family: 'Segoe UI', Roboto, Helvetica, Arial, sans-serif;
        font-size: 10pt;
        background: white !important;
        color: #000 !important;
    }
    .no-print { display: none !important; }
    .card { border: none !important; box-shadow: none !important; margin-bottom: 0 !important; }
    .table { border-collapse: collapse !important; width: 100% !important; }
    .table th, .table td { border: 1px solid #ddd !important; padding: 4px 6px !important; }
    .table thead th { background-color: #f2f2f2 !important; color: black !important; -webkit-print-color-adjust: exact; }
    .badge { border: 1px solid #ccc !important; color: black !important; background: transparent !important; }
    .table-dark { background-color: #eee !important; color: black !important; }
    
    .report-header {
        border-bottom: 2px solid var(--report-primary);
        padding-bottom: 10px;
        margin-bottom: 20px;
    }
    .report-title {
        font-size: 18pt;
        font-weight: bold;
        text-transform: uppercase;
        color: var(--report-primary) !important;
    }
    .report-subtitle {
        font-size: 14pt;
        font-weight: 600;
        color: var(--report-secondary) !important;
    }
    .signature-box {
        margin-top: 50px;
        page-break-inside: avoid;
    }
    .signature-line {
        border-top: 1px solid #000;
        width: 150px;
        margin: 0 auto;
        padding-top: 5px;
    }
}

/* On-screen report styling */
.report-summary-card {
    transition: transform 0.2s;
    border-left: 4px solid var(--report-primary);
}
.report-summary-card:hover {
    transform: translateY(-2px);
}
.trip-group-header {
    background-color: #e9ecef !important;
    font-weight: bold;
}
.gps-coord {
    font-family: monospace;
    font-size: 0.85em;
    color: #666;
}
</style>

<div class="row no-print">
    <div class="col-12">
        <h2><i class="fas fa-chart-bar"></i> <?php echo t('report_title', 'Reports'); ?></h2>
    </div>
</div>

<!-- Filters -->
<div class="card mb-4 no-print">
    <div class="card-body">
        <form method="GET" action="" class="row g-3" id="reportForm">
            <div class="col-md-2">
                <label class="form-label"><?php echo t('report_title', 'Report Type'); ?></label>
                <select name="report_type" class="form-select" onchange="toggleVehicleFilter(this.value); this.form.submit()">
                    <option value="dozar" <?php echo $report_type === 'dozar' ? 'selected' : ''; ?>><?php echo t('nav_dozar', 'Dozar'); ?></option>
                    <option value="tripper" <?php echo $report_type === 'tripper' ? 'selected' : ''; ?>>Tripper (All)</option>
                    <option value="tripper_trip" <?php echo $report_type === 'tripper_trip' ? 'selected' : ''; ?>>Tripper (Trip-wise)</option>
                    <option value="tripper_vehicle" <?php echo $report_type === 'tripper_vehicle' ? 'selected' : ''; ?>>Tripper (Vehicle-wise)</option>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label"><?php echo t('report_from_date', 'Date From'); ?> (नेपाली)</label>
                <?php echo nepali_date_picker('date_from', $date_from, '', 'form-control', false); ?>
            </div>
            <div class="col-md-2">
                <label class="form-label"><?php echo t('report_to_date', 'Date To'); ?> (नेपाली)</label>
                <?php echo nepali_date_picker('date_to', $date_to, '', 'form-control', false); ?>
            </div>
            <div class="col-md-2">
                <label class="form-label"><?php echo t('label_project', 'Project'); ?></label>
                <select name="project_id" class="form-select">
                    <option value="">All Projects</option>
                    <?php foreach ($projects as $id => $name): ?>
                    <option value="<?php echo $id; ?>" <?php echo $project_id == $id ? 'selected' : ''; ?>><?php echo htmlspecialchars($name); ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2" id="entityFilterCol" style="<?php echo $report_type !== 'dozar' ? '' : 'display:none'; ?>">
                <label class="form-label">Entity</label>
                <select name="entity_type" class="form-select">
                    <option value="">All Entities</option>
                    <option value="kmc"     <?php echo $entity_type === 'kmc'     ? 'selected' : ''; ?>>KMC</option>
                    <option value="private" <?php echo $entity_type === 'private' ? 'selected' : ''; ?>>Private</option>
                    <option value="other"   <?php echo $entity_type === 'other'   ? 'selected' : ''; ?>>Other</option>
                </select>
            </div>
            <div class="col-md-2" id="vehicleFilterCol" style="<?php echo in_array($report_type, ['tripper_trip', 'tripper_vehicle']) ? '' : 'display:none'; ?>">
                <label class="form-label">Vehicle</label>
                <select name="vehicle_id" class="form-select">
                    <option value="">All Vehicles</option>
                    <?php foreach ($vehicles as $vid => $vnum): ?>
                    <option value="<?php echo $vid; ?>" <?php echo $vehicle_id == $vid ? 'selected' : ''; ?>><?php echo htmlspecialchars($vnum); ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-1" id="tripFilterCol" style="<?php echo $report_type === 'tripper_trip' ? '' : 'display:none'; ?>">
                <label class="form-label">Trip No.</label>
                <select name="trip_number" class="form-select">
                    <option value="">All</option>
                    <?php if (isset($max_trip) && $max_trip > 0): ?>
                        <?php for ($tn = 1; $tn <= $max_trip; $tn++): ?>
                        <option value="<?php echo $tn; ?>" <?php echo $trip_number == $tn ? 'selected' : ''; ?>>Trip <?php echo $tn; ?></option>
                        <?php endfor; ?>
                    <?php else: ?>
                        <?php for ($tn = 1; $tn <= 10; $tn++): ?>
                        <option value="<?php echo $tn; ?>" <?php echo $trip_number == $tn ? 'selected' : ''; ?>>Trip <?php echo $tn; ?></option>
                        <?php endfor; ?>
                    <?php endif; ?>
                </select>
            </div>
            <div class="col-md-1">
                <label class="form-label">&nbsp;</label>
                <button type="submit" class="btn btn-primary w-100">
                    <i class="fas fa-search"></i>
                </button>
            </div>
            <div class="col-md-1">
                <label class="form-label">&nbsp;</label>
                <button type="button" class="btn btn-secondary w-100" onclick="window.print()">
                    <i class="fas fa-print"></i>
                </button>
            </div>
        </form>
        <?php if (!empty($records) || !empty($vehicle_summary)): ?>
        <div class="row mt-2">
            <div class="col-12 text-end">
                <a href="export.php?report_type=<?php echo $report_type; ?>&date_from=<?php echo $date_from; ?>&date_to=<?php echo $date_to; ?>&project_id=<?php echo $project_id; ?>&vehicle_id=<?php echo $vehicle_id; ?>&entity_type=<?php echo urlencode($entity_type); ?>" class="btn btn-success btn-sm">
                    <i class="fas fa-file-excel"></i> <?php echo t('action_export', 'Export CSV'); ?>
                </a>
            </div>
        </div>
        <?php endif; ?>
    </div>
</div>

<!-- Print Header (only visible when printing) -->
<div class="d-none d-print-block report-header">
    <div class="row align-items-center">
        <div class="col-12 text-center">
            <h1 class="report-title">Kathmandu Metropolitan City</h1>
            <h2 class="report-subtitle">Environment Management Department</h2>
            <hr class="my-2">
            <h3 class="mb-1"><?php
                if ($report_type === 'dozar') echo 'Dozar Operations Report';
                elseif ($report_type === 'tripper_trip') echo 'Daily Trip Detailed Report';
                elseif ($report_type === 'tripper_vehicle') echo 'Vehicle-wise Performance Report';
                else echo 'Tripper Operations Report';
            ?></h3>
            <div class="d-flex justify-content-center gap-4 mt-2">
                <span><strong>Period:</strong> <?php echo $date_from; ?> to <?php echo $date_to; ?></span>
                <?php if ($project_id && isset($projects[$project_id])): ?>
                    <span><strong>Project:</strong> <?php echo htmlspecialchars($projects[$project_id]); ?></span>
                <?php endif; ?>
                <?php if ($vehicle_id && isset($vehicles[$vehicle_id])): ?>
                    <span><strong>Vehicle:</strong> <?php echo htmlspecialchars($vehicles[$vehicle_id]); ?></span>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<!-- Summary Cards -->
<div class="row mb-4 g-3 no-print">
    <?php if ($report_type === 'dozar'): ?>
    <div class="col-md-4">
        <div class="card report-summary-card shadow-sm h-100">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Total Operations</h6>
                <h2 class="mb-0 font-weight-bold"><?php echo $total_records; ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-4">
        <div class="card report-summary-card shadow-sm h-100" style="border-left-color: #2e7d32;">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Total Work Hours</h6>
                <h2 class="mb-0 font-weight-bold text-success"><?php echo number_format($total_hours, 2); ?> <small class="h6">hrs</small></h2>
            </div>
        </div>
    </div>
    <div class="col-md-4">
        <div class="card report-summary-card shadow-sm h-100" style="border-left-color: #0277bd;">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Report Period</h6>
                <p class="mb-0"><strong><?php echo $date_from; ?></strong> to <strong><?php echo $date_to; ?></strong></p>
            </div>
        </div>
    </div>
    <?php elseif ($report_type === 'tripper_vehicle'): ?>
    <div class="col-md-3">
        <div class="card report-summary-card shadow-sm h-100">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Active Vehicles</h6>
                <h2 class="mb-0 font-weight-bold"><?php echo count($vehicle_summary); ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card report-summary-card shadow-sm h-100" style="border-left-color: #2e7d32;">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Completed Trips</h6>
                <h2 class="mb-0 font-weight-bold text-success"><?php echo $total_records; ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card report-summary-card shadow-sm h-100" style="border-left-color: #f57c00;">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Total Distance</h6>
                <h2 class="mb-0 font-weight-bold text-warning"><?php echo number_format($total_km, 2); ?> <small class="h6">KM</small></h2>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card report-summary-card shadow-sm h-100" style="border-left-color: #0277bd;">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Completion Rate</h6>
                <?php $rate = $total_records > 0 ? ($total_completed / $total_records) * 100 : 0; ?>
                <h2 class="mb-0 font-weight-bold text-info"><?php echo number_format($rate, 1); ?>%</h2>
                <small class="text-muted"><?php echo $total_completed; ?> of <?php echo $total_records; ?> trips</small>
            </div>
        </div>
    </div>
    <?php else: ?>
    <!-- Tripper Summary -->
    <div class="col-md-3">
        <div class="card report-summary-card shadow-sm h-100">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Total Trips</h6>
                <h2 class="mb-0 font-weight-bold"><?php echo $total_records; ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card report-summary-card shadow-sm h-100" style="border-left-color: #f57c00;">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Total Distance</h6>
                <h2 class="mb-0 font-weight-bold text-warning"><?php echo number_format($total_km, 2); ?> <small class="h6">KM</small></h2>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card report-summary-card shadow-sm h-100" style="border-left-color: #2e7d32;">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Completed</h6>
                <h2 class="mb-0 font-weight-bold text-success"><?php echo $total_completed; ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card report-summary-card shadow-sm h-100" style="border-left-color: #c62828;">
            <div class="card-body">
                <h6 class="text-muted text-uppercase small font-weight-bold">Pending Delivery</h6>
                <h2 class="mb-0 font-weight-bold text-danger"><?php echo $total_pending; ?></h2>
            </div>
        </div>
    </div>
    <?php endif; ?>
</div>

<!-- ============================================ -->
<!-- DOZAR REPORT TABLE -->
<!-- ============================================ -->
<?php if ($report_type === 'dozar'): ?>
<div class="card shadow-sm">
    <div class="card-header bg-dark text-white d-flex justify-content-between align-items-center">
        <h5 class="mb-0"><i class="fas fa-truck-monster"></i> Dozar Operations Report</h5>
    </div>
    <div class="card-body p-0">
        <div class="table-responsive">
            <table class="table table-bordered table-sm mb-0">
                <thead>
                    <tr class="bg-light">
                        <th width="40" class="text-center">#</th>
                        <th>Date</th>
                        <th>Project / Vehicle</th>
                        <th>Driver</th>
                        <th>Work Location</th>
                        <th class="text-center">Hours</th>
                        <th class="text-center">Odometer (Start/End)</th>
                        <th class="no-print text-center" width="100">Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($records)): ?>
                    <tr><td colspan="8" class="text-center py-4 text-muted">No dozar records found</td></tr>
                    <?php else: ?>
                    <?php $sn = 1; foreach ($records as $r): ?>
                    <tr>
                        <td class="text-center"><?php echo $sn++; ?></td>
                        <td><?php echo htmlspecialchars($r['created_date']); ?></td>
                        <td>
                            <div class="font-weight-bold text-primary"><?php echo htmlspecialchars($r['project_name'] ?? '-'); ?></div>
                            <div class="small text-muted font-weight-bold"><?php echo htmlspecialchars($r['vehicle_number'] ?? '-'); ?></div>
                        </td>
                        <td><?php echo htmlspecialchars($r['driver_name'] ?? '-'); ?></td>
                        <td><?php echo htmlspecialchars($r['work_location']); ?></td>
                        <td class="text-center font-weight-bold"><?php echo number_format($r['work_length_hours'], 2); ?></td>
                        <td class="text-center small">
                            <span class="badge bg-light text-dark border"><?php echo $r['start_odometer']; ?></span> 
                            <i class="fas fa-arrow-right mx-1 text-muted small"></i> 
                            <span class="badge bg-light text-dark border"><?php echo $r['end_odometer'] ?? '...'; ?></span>
                        </td>
                        <td class="no-print text-center">
                            <div class="btn-group">
                                <a href="dozar_view.php?id=<?php echo $r['id']; ?>" class="btn btn-sm btn-outline-info" title="View"><i class="fas fa-eye"></i></a>
                                <a href="dozar_edit.php?id=<?php echo $r['id']; ?>" class="btn btn-sm btn-outline-warning" title="Edit"><i class="fas fa-edit"></i></a>
                                <button type="button" class="btn btn-sm btn-outline-danger" onclick="confirmDelete('dozar', <?php echo $r['id']; ?>)" title="Delete"><i class="fas fa-trash"></i></button>
                            </div>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                    <tr class="table-dark text-white fw-bold">
                        <td colspan="5" class="text-end py-2">TOTAL WORK HOURS</td>
                        <td class="text-center py-2 h5 mb-0"><?php echo number_format($total_hours, 2); ?></td>
                        <td colspan="2"></td>
                    </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- ============================================ -->
<!-- TRIPPER TRIP-WISE REPORT -->
<!-- ============================================ -->
<?php elseif ($report_type === 'tripper_trip'): ?>
<div class="card shadow-sm">
    <div class="card-header bg-dark text-white d-flex justify-content-between align-items-center">
        <h5 class="mb-0"><i class="fas fa-route"></i> Daily Trip Detailed Report</h5>
    </div>
    <div class="card-body p-0">
        <div class="table-responsive">
            <table class="table table-bordered table-sm mb-0">
                <thead>
                    <tr class="bg-light">
                        <th width="40">S.N.</th>
                        <th>Vehicle / Driver / Project</th>
                        <th>Trip Details</th>
                        <th>Loading Information</th>
                        <th>Unloading Information</th>
                        <th>Distance / Material</th>
                        <th>Status</th>
                        <th class="no-print" width="60">Action</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($records)): ?>
                    <tr><td colspan="8" class="text-center py-4 text-muted">No trip records found for the selected criteria</td></tr>
                    <?php else: ?>
                    <?php 
                        $sn = 1; 
                        $grand_km = 0; 
                        $current_date = '';
                        $daily_km = 0;
                        $daily_trips = 0;
                        
                        foreach ($records as $index => $r):
                            $start_km = floatval($r['loading_start_odometer'] ?? 0);
                            $end_km = floatval($r['unloading_end_odometer'] ?? 0);
                            $distance = ($end_km > $start_km) ? ($end_km - $start_km) : 0;
                            $grand_km += $distance;
                            
                            // Date grouping logic
                            if ($current_date !== $r['created_date']):
                                if ($current_date !== ''): ?>
                                    <tr class="table-secondary fw-bold">
                                        <td colspan="5" class="text-end small">Daily Sub-total for <?php echo $current_date; ?>:</td>
                                        <td colspan="3"><?php echo $daily_trips; ?> Trips | <?php echo number_format($daily_km, 2); ?> KM</td>
                                    </tr>
                                <?php endif;
                                $current_date = $r['created_date'];
                                $daily_km = 0;
                                $daily_trips = 0;
                                ?>
                                <tr class="trip-group-header">
                                    <td colspan="8"><i class="fas fa-calendar-day me-2"></i> <?php echo $current_date; ?></td>
                                </tr>
                            <?php endif;
                            
                            $daily_km += $distance;
                            $daily_trips++;
                    ?>
                    <tr>
                        <td class="text-center font-weight-bold"><?php echo $sn++; ?></td>
                        <td>
                            <div><strong><?php echo htmlspecialchars($r['vehicle_number'] ?? '-'); ?></strong></div>
                            <div class="small text-muted"><i class="fas fa-user-tie me-1"></i> <?php echo htmlspecialchars($r['driver_name'] ?? '-'); ?></div>
                            <div class="small text-primary font-weight-bold mt-1"><?php echo htmlspecialchars($r['project_name'] ?? '-'); ?></div>
                        </td>
                        <td class="text-center">
                            <span class="badge bg-primary px-2 mb-1">Trip <?php echo $r['trip_number']; ?></span>
                            <?php if ($r['quantity_weight']): ?>
                                <div class="small font-weight-bold"><?php echo number_format($r['quantity_weight'], 2); ?> Ton</div>
                            <?php endif; ?>
                        </td>
                        <td>
                            <div class="small"><strong>Loc:</strong> <?php echo htmlspecialchars($r['loading_location'] ?? '-'); ?></div>
                            <div class="small"><strong>Time:</strong> <?php echo $r['loading_time'] ? date('H:i', strtotime($r['loading_time'])) : '-'; ?> | <strong>Odo:</strong> <?php echo $r['loading_start_odometer']; ?></div>
                            <div class="gps-coord mt-1">
                                <i class="fas fa-map-marker-alt text-danger me-1"></i>
                                <?php echo $r['loading_gps_latitude'] ? $r['loading_gps_latitude'] . ', ' . $r['loading_gps_longitude'] : 'No GPS'; ?>
                                <div class="small text-muted">Coll: <?php echo htmlspecialchars($r['loading_collector_name'] ?? '-'); ?></div>
                            </div>
                        </td>
                        <td>
                            <div class="small"><strong>Place:</strong> <?php echo htmlspecialchars($r['unloading_place'] ?? $r['unloading_address'] ?? '-'); ?></div>
                            <div class="small"><strong>Time:</strong> <?php echo $r['unloading_time'] ? date('H:i', strtotime($r['unloading_time'])) : '-'; ?> | <strong>Odo:</strong> <?php echo $r['unloading_end_odometer'] ?? '-'; ?></div>
                            <div class="gps-coord mt-1">
                                <i class="fas fa-map-marker-alt text-info me-1"></i>
                                <?php echo $r['unloading_gps_latitude'] ? $r['unloading_gps_latitude'] . ', ' . $r['unloading_gps_longitude'] : 'No GPS'; ?>
                                <div class="small text-muted">Coll: <?php echo htmlspecialchars($r['unloading_collector_name'] ?? '-'); ?></div>
                            </div>
                        </td>
                        <td>
                            <div class="h6 mb-0"><strong><?php echo $distance > 0 ? number_format($distance, 2) : '0.00'; ?></strong> <small>KM</small></div>
                            <div class="small text-muted mt-1 italic"><?php echo htmlspecialchars($r['material_type'] ?? 'N/A'); ?></div>
                        </td>
                        <td>
                            <div class="badge bg-<?php echo $r['status'] === 'completed' ? 'success' : ($r['status'] === 'cancelled' ? 'danger' : 'warning'); ?> d-block py-2">
                                <?php echo $r['status'] === 'completed' ? 'Completed' : ($r['status'] === 'cancelled' ? 'Cancelled' : 'In Progress'); ?>
                            </div>
                        </td>
                        <td class="no-print text-center">
                            <a href="tripper_view.php?id=<?php echo $r['id']; ?>" class="btn btn-sm btn-outline-info" title="View Details"><i class="fas fa-eye"></i></a>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                    <!-- Last daily total -->
                    <tr class="table-secondary fw-bold">
                        <td colspan="5" class="text-end small">Daily Sub-total for <?php echo $current_date; ?>:</td>
                        <td colspan="3"><?php echo $daily_trips; ?> Trips | <?php echo number_format($daily_km, 2); ?> KM</td>
                    </tr>
                    <!-- Grand Total -->
                    <tr class="table-dark text-white fw-bold">
                        <td colspan="5" class="text-end py-2">GRAND TOTAL (<?php echo count($records); ?> TRIPS)</td>
                        <td colspan="3" class="py-2 h5 mb-0 font-weight-bold text-warning"><?php echo number_format($grand_km, 2); ?> KM</td>
                    </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- ============================================ -->
<!-- TRIPPER VEHICLE-WISE REPORT -->
<!-- ============================================ -->
<?php elseif ($report_type === 'tripper_vehicle'): ?>
<!-- Vehicle Summary Table -->
<div class="card mb-4">
    <div class="card-header bg-secondary text-white">
        <h5 class="mb-0"><i class="fas fa-truck"></i> Tripper Report - Vehicle No-wise Summary</h5>
    </div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="table table-bordered table-sm table-striped">
                <thead>
                    <tr>
                        <th>S.N.</th>
                        <th>Vehicle No.</th>
                        <th>Vehicle Type</th>
                        <th>Capacity</th>
                        <th>Total Trips</th>
                        <th>Completed</th>
                        <th>Pending</th>
                        <th>Cancelled</th>
                        <th>Total KM</th>
                        <th>Avg KM/Trip</th>
                        <th>Drivers</th>
                        <th>Projects</th>
                        <th>First Trip</th>
                        <th>Last Trip</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($vehicle_summary)): ?>
                    <tr><td colspan="14" class="text-center text-muted">No records found</td></tr>
                    <?php else: ?>
                    <?php $sn = 1; $g_trips = 0; $g_km = 0; $g_completed = 0; $g_pending = 0; $g_cancelled = 0;
                    foreach ($vehicle_summary as $vs):
                        $avg_km = $vs['total_trips'] > 0 ? $vs['total_km'] / $vs['total_trips'] : 0;
                        $g_trips += $vs['total_trips'];
                        $g_km += $vs['total_km'];
                        $g_completed += $vs['completed_trips'];
                        $g_pending += $vs['pending_trips'];
                        $g_cancelled += $vs['cancelled_trips'];
                    ?>
                    <tr>
                        <td><?php echo $sn++; ?></td>
                        <td><strong><?php echo htmlspecialchars($vs['vehicle_number']); ?></strong></td>
                        <td><?php echo htmlspecialchars($vs['vehicle_type'] ?? '-'); ?></td>
                        <td><?php echo htmlspecialchars($vs['capacity'] ?? '-'); ?></td>
                        <td class="text-center"><strong><?php echo $vs['total_trips']; ?></strong></td>
                        <td class="text-center"><span class="badge bg-success"><?php echo $vs['completed_trips']; ?></span></td>
                        <td class="text-center"><span class="badge bg-warning"><?php echo $vs['pending_trips']; ?></span></td>
                        <td class="text-center"><span class="badge bg-danger"><?php echo $vs['cancelled_trips']; ?></span></td>
                        <td class="text-end"><strong><?php echo number_format($vs['total_km'], 2); ?></strong></td>
                        <td class="text-end"><?php echo number_format($avg_km, 2); ?></td>
                        <td><small><?php echo htmlspecialchars($vs['drivers_used'] ?? '-'); ?></small></td>
                        <td><small><?php echo htmlspecialchars($vs['projects_used'] ?? '-'); ?></small></td>
                        <td nowrap><?php echo htmlspecialchars($vs['first_trip_date']); ?></td>
                        <td nowrap><?php echo htmlspecialchars($vs['last_trip_date']); ?></td>
                    </tr>
                    <?php endforeach; ?>
                    <tr class="fw-bold table-dark">
                        <td colspan="4" class="text-end">Grand Total</td>
                        <td class="text-center"><?php echo $g_trips; ?></td>
                        <td class="text-center"><?php echo $g_completed; ?></td>
                        <td class="text-center"><?php echo $g_pending; ?></td>
                        <td class="text-center"><?php echo $g_cancelled; ?></td>
                        <td class="text-end"><?php echo number_format($g_km, 2); ?></td>
                        <td class="text-end"><?php echo $g_trips > 0 ? number_format($g_km / $g_trips, 2) : '0.00'; ?></td>
                        <td colspan="4"></td>
                    </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- Vehicle-wise Trip Details (expandable) -->
<?php if (!empty($records_by_vehicle)): ?>
<div class="card">
    <div class="card-header bg-dark text-white">
        <h5 class="mb-0"><i class="fas fa-list-alt"></i> Trip Details by Vehicle</h5>
    </div>
    <div class="card-body">
        <div class="accordion" id="vehicleAccordion">
            <?php $idx = 0; foreach ($records_by_vehicle as $vnum => $trips): $idx++; ?>
            <div class="accordion-item">
                <h2 class="accordion-header" id="heading<?php echo $idx; ?>">
                    <button class="accordion-button collapsed" type="button" data-bs-toggle="collapse" data-bs-target="#collapse<?php echo $idx; ?>">
                        <strong><i class="fas fa-truck me-2"></i> <?php echo htmlspecialchars($vnum); ?></strong>
                        <span class="badge bg-primary ms-2"><?php echo count($trips); ?> trips</span>
                    </button>
                </h2>
                <div id="collapse<?php echo $idx; ?>" class="accordion-collapse collapse" data-bs-parent="#vehicleAccordion">
                    <div class="accordion-body p-0">
                        <div class="table-responsive">
                            <table class="table table-bordered table-sm mb-0">
                                <thead class="table-light">
                                    <tr>
                                        <th>S.N.</th>
                                        <th>Date</th>
                                        <th>Driver</th>
                                        <th>Project</th>
                                        <th>Loading Location</th>
                                        <th>Unloading Place</th>
                                        <th>Start KM</th>
                                        <th>End KM</th>
                                        <th>Distance</th>
                                        <th>Material</th>
                                        <th>Status</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php $tsn = 1; $veh_km = 0; foreach ($trips as $tr):
                                        $s = floatval($tr['loading_start_odometer'] ?? 0);
                                        $e = floatval($tr['unloading_end_odometer'] ?? 0);
                                        $d = ($e > $s) ? ($e - $s) : 0;
                                        $veh_km += $d;
                                    ?>
                                    <tr>
                                        <td><?php echo $tsn++; ?></td>
                                        <td nowrap><?php echo htmlspecialchars($tr['created_date']); ?></td>
                                        <td><?php echo htmlspecialchars($tr['driver_name'] ?? '-'); ?></td>
                                        <td><?php echo htmlspecialchars($tr['project_name'] ?? '-'); ?></td>
                                        <td><?php echo htmlspecialchars($tr['loading_location'] ?? '-'); ?></td>
                                        <td><?php echo htmlspecialchars($tr['unloading_place'] ?? $tr['unloading_address'] ?? '-'); ?></td>
                                        <td><?php echo $tr['loading_start_odometer']; ?></td>
                                        <td><?php echo $tr['unloading_end_odometer'] ?? '-'; ?></td>
                                        <td><strong><?php echo $d > 0 ? number_format($d, 2) : '-'; ?></strong></td>
                                        <td><?php echo htmlspecialchars($tr['material_type'] ?? '-'); ?></td>
                                        <td>
                                            <span class="badge bg-<?php echo $tr['status'] === 'completed' ? 'success' : ($tr['status'] === 'cancelled' ? 'danger' : 'warning'); ?>">
                                                <?php echo $tr['status'] === 'completed' ? 'Completed' : ($tr['status'] === 'cancelled' ? 'Cancelled' : 'Pending'); ?>
                                            </span>
                                        </td>
                                    </tr>
                                    <?php endforeach; ?>
                                    <tr class="fw-bold table-secondary">
                                        <td colspan="8" class="text-end">Vehicle Total KM</td>
                                        <td><?php echo number_format($veh_km, 2); ?></td>
                                        <td colspan="2"></td>
                                    </tr>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
            <?php endforeach; ?>
        </div>
    </div>
</div>
<?php endif; ?>

<!-- ============================================ -->
<!-- TRIPPER DEFAULT (ALL) REPORT -->
<!-- ============================================ -->
<?php else: ?>
<div class="card">
    <div class="card-header bg-secondary text-white">
        <h5 class="mb-0"><i class="fas fa-truck-loading"></i> Tripper Report</h5>
    </div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="table table-bordered table-sm table-striped">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Date</th>
                        <th>Project</th>
                        <th>Vehicle</th>
                        <th>Driver</th>
                        <th>Loading</th>
                        <th>Unloading</th>
                        <th>KM</th>
                        <th>Status</th>
                        <th class="no-print">Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($records)): ?>
                    <tr><td colspan="10" class="text-center text-muted">No records found</td></tr>
                    <?php else: ?>
                    <?php $sn = 1; foreach ($records as $r): ?>
                    <tr>
                        <td><?php echo $sn++; ?></td>
                        <td><?php echo htmlspecialchars($r['created_date']); ?></td>
                        <td><?php echo htmlspecialchars($r['project_name'] ?? '-'); ?></td>
                        <td><?php echo htmlspecialchars($r['vehicle_number'] ?? '-'); ?></td>
                        <td><?php echo htmlspecialchars($r['driver_name'] ?? '-'); ?></td>
                        <td><?php echo htmlspecialchars($r['loading_location'] ?? '-'); ?></td>
                        <td><?php echo htmlspecialchars($r['unloading_address'] ?? '-'); ?></td>
                        <td><?php echo $r['unloading_end_odometer'] ? number_format($r['unloading_end_odometer'] - $r['loading_start_odometer'], 2) : '-'; ?></td>
                        <td><span class="badge bg-<?php echo $r['status'] === 'completed' ? 'success' : ($r['status'] === 'cancelled' ? 'danger' : 'warning'); ?>">
                            <?php echo $r['status'] === 'completed' ? 'Completed' : ($r['status'] === 'cancelled' ? 'Cancelled' : 'Pending'); ?>
                        </span></td>
                        <td class="no-print">
                            <a href="tripper_view.php?id=<?php echo $r['id']; ?>" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a>
                            <a href="tripper_edit.php?id=<?php echo $r['id']; ?>" class="btn btn-sm btn-warning"><i class="fas fa-edit"></i></a>
                            <button type="button" class="btn btn-sm btn-danger" onclick="confirmDelete('tripper', <?php echo $r['id']; ?>)"><i class="fas fa-trash"></i></button>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
<?php endif; ?>

<!-- Signature Section -->
<div class="signature-box mt-5 d-print-block">
    <div class="container-fluid">
        <div class="row text-center">
            <div class="col-4">
                <div class="signature-line"></div>
                <p class="mt-2 mb-0 font-weight-bold">Prepared By</p>
                <p class="small text-muted">(Operator / Staff)</p>
            </div>
            <div class="col-4">
                <div class="signature-line"></div>
                <p class="mt-2 mb-0 font-weight-bold">Verified By</p>
                <p class="small text-muted">(Site Supervisor)</p>
            </div>
            <div class="col-4">
                <div class="signature-line"></div>
                <p class="mt-2 mb-0 font-weight-bold">Approved By</p>
                <p class="small text-muted">(Department Head)</p>
            </div>
        </div>
        <div class="row mt-4">
            <div class="col-12 text-end">
                <p class="small text-muted italic">Report Generated On: <?php echo date('Y-m-d H:i:s'); ?> | <?php echo htmlspecialchars($user['full_name']); ?></p>
            </div>
        </div>
    </div>
</div>

<!-- Delete Confirmation Modal -->
<div class="modal fade" id="deleteModal" tabindex="-1">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header bg-danger text-white">
                <h5 class="modal-title">Delete Record</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <div class="modal-body">
                <p>Are you sure you want to delete this record? This action cannot be undone.</p>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                <a href="#" id="deleteLink" class="btn btn-danger">Delete</a>
            </div>
        </div>
    </div>
</div>

<script>
function confirmDelete(type, id) {
    document.getElementById('deleteLink').href = type + '_delete.php?id=' + id;
    var myModal = new bootstrap.Modal(document.getElementById('deleteModal'), {
        keyboard: false
    });
    myModal.show();
}

function toggleVehicleFilter(reportType) {
    var col = document.getElementById('vehicleFilterCol');
    var tripCol = document.getElementById('tripFilterCol');
    var entityCol = document.getElementById('entityFilterCol');
    if (reportType === 'tripper_trip' || reportType === 'tripper_vehicle') {
        col.style.display = '';
    } else {
        col.style.display = 'none';
    }
    if (reportType === 'tripper_trip') {
        tripCol.style.display = '';
    } else {
        tripCol.style.display = 'none';
    }
    entityCol.style.display = (reportType === 'dozar') ? 'none' : '';
}
</script>

<?php include __DIR__ . '/../includes/footer.php'; ?>
