<?php
header('Content-Type: application/json');
require '../../config/db.php';

try {
    $from = $_GET['from'] ?? date('Y-m-01');
    $to = $_GET['to'] ?? date('Y-m-t');

    // Stats - Total Shipments
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM tbl_bookings WHERE DATE(created_at) BETWEEN :from AND :to");
    $stmt->execute([':from' => $from, ':to' => $to]);
    $total_shipments = $stmt->fetchColumn();

    // COD Total (Remove date filter for global or keep for range? User said "Value 0" but total is 33, likely date range issue. Let's provide both if needed, but for now range)
    $stmt = $pdo->prepare("SELECT SUM(cod_amount) FROM tbl_bookings WHERE DATE(created_at) BETWEEN :from AND :to");
    $stmt->execute([':from' => $from, ':to' => $to]);
    $cod_total = (float) ($stmt->fetchColumn() ?: 0);

    // Active Counts
    $active_branches = $pdo->query("SELECT COUNT(*) FROM tbl_branch WHERE status='active'")->fetchColumn();
    $active_employees = $pdo->query("SELECT COUNT(*) FROM tbl_employees WHERE status='active'")->fetchColumn();
    $active_companies = $pdo->query("SELECT COUNT(*) FROM tbl_company WHERE status='active'")->fetchColumn();

    // Today's Pickups
    $today = date('Y-m-d');
    $stmt = $pdo->prepare("SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN last_status = 'Created' THEN 1 ELSE 0 END) as pending,
        SUM(CASE WHEN last_status != 'Created' THEN 1 ELSE 0 END) as picked
        FROM tbl_bookings WHERE DATE(created_at) = :today");
    $stmt->execute([':today' => $today]);
    $pickup_data = $stmt->fetch(PDO::FETCH_ASSOC);
    $today_pickups = (int) $pickup_data['total'];
    $today_picked = (int) $pickup_data['picked'];
    $today_pending = (int) $pickup_data['pending'];

    // Upcoming Pickups (Scheduled for tomorrow or later - if applicable, but for now next 3 days)
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM tbl_bookings WHERE DATE(created_at) > :today AND DATE(created_at) <= :upcoming");
    $stmt->execute([
        ':today' => $today,
        ':upcoming' => date('Y-m-d', strtotime('+3 days'))
    ]);
    $upcoming_pickups = $stmt->fetchColumn();

    // NDR Shipments (Non-Delivery Report)
    // We'll define NDR as shipments with specific failed statuses (e.g., 'RTO', 'Failed', 'Returned')
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM tbl_bookings WHERE last_status IN ('RTO', 'Failed', 'Returned', 'Delivery Failed')");
    $stmt->execute();
    $ndr_count = $stmt->fetchColumn();

    // Top 5 Clients
    $stmt = $pdo->prepare("SELECT co.company_name, COUNT(b.id) as count 
                            FROM tbl_bookings b
                            JOIN tbl_pickup_points p ON b.pickup_point_id = p.id
                            JOIN tbl_branch br ON p.branch_id = br.id
                            JOIN tbl_company co ON br.company_id = co.id
                            WHERE DATE(b.created_at) BETWEEN :from AND :to
                            GROUP BY co.id 
                            ORDER BY count DESC 
                            LIMIT 5");
    $stmt->execute([':from' => $from, ':to' => $to]);
    $top_clients = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Stats - By Status (for the selected range)
    $stmt = $pdo->prepare("SELECT last_status, COUNT(*) as count FROM tbl_bookings WHERE DATE(created_at) BETWEEN :from AND :to GROUP BY last_status");
    $stmt->execute([':from' => $from, ':to' => $to]);
    $status_counts = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Stats - By Status (Today's specifically)
    $stmt = $pdo->prepare("SELECT last_status, COUNT(*) as count FROM tbl_bookings WHERE DATE(created_at) = :today GROUP BY last_status");
    $stmt->execute([':today' => $today]);
    $today_status_counts = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Map today status counts for easy frontend access
    $today_mapped = [];
    foreach ($today_status_counts as $sc) {
        $today_mapped[$sc['last_status']] = (int) $sc['count'];
    }

    // Timeline - Daily Shipments for Chart
    $stmt = $pdo->prepare("SELECT DATE(created_at) as date, COUNT(*) as count FROM tbl_bookings WHERE DATE(created_at) BETWEEN :from AND :to GROUP BY DATE(created_at) ORDER BY date ASC");
    $stmt->execute([':from' => $from, ':to' => $to]);
    $daily_stats = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Calendar Events
    $calendar_events = [];
    foreach ($daily_stats as $day) {
        $calendar_events[] = [
            'title' => $day['count'] . ' Shipments',
            'start' => $day['date'],
            'allDay' => true,
            'className' => 'bg-primary'
        ];
    }

    // Recent Bulk Uploads
    $stmt = $pdo->query("SELECT id, filename, total_records, success_count, failure_count, status, created_at FROM tbl_bulkupload_jobs ORDER BY created_at DESC LIMIT 5");
    $recent_bulk_jobs = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Attendance Summary (Today)
    $stmt = $pdo->prepare("SELECT status, COUNT(*) as count FROM tbl_attendance WHERE attendance_date = :today GROUP BY status");
    $stmt->execute([':today' => $today]);
    $attendance_counts = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $attendance_summary = [
        'present' => 0,
        'absent' => 0,
        'leave' => 0,
        'half_day' => 0
    ];
    foreach ($attendance_counts as $ac) {
        $status = strtolower($ac['status']);
        if (isset($attendance_summary[$status])) {
            $attendance_summary[$status] = (int) $ac['count'];
        }
    }

    echo json_encode([
        'status' => 'success',
        'data' => [
            'total_shipments' => (int) $total_shipments,
            'cod_total' => (float) $cod_total,
            'active_branches' => (int) $active_branches,
            'active_employees' => (int) $active_employees,
            'active_companies' => (int) $active_companies,
            'today_pickups' => (int) $today_pickups,
            'today_picked' => (int) $today_picked,
            'today_pending' => (int) $today_pending,
            'upcoming_pickups' => (int) $upcoming_pickups,
            'today_status_counts' => $today_mapped,
            'ndr_count' => (int) $ndr_count,
            'top_clients' => $top_clients,
            'status_counts' => $status_counts,
            'daily_stats' => $daily_stats,
            'calendar_events' => $calendar_events,
            'recent_bulk_jobs' => $recent_bulk_jobs,
            'attendance_summary' => $attendance_summary
        ]
    ]);

} catch (Exception $e) {
    echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
}
?>