<?php
session_start();
include '../layouts/dbconn.php';

// Set timezone to East Africa Time
date_default_timezone_set('Africa/Nairobi');

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    http_response_code(401);
    echo json_encode(['success' => false, 'message' => 'Unauthorized access']);
    exit();
}

// Set headers for JSON response
header('Content-Type: application/json');

try {
    // Get fresh customer statistics
    $sql_stats = "SELECT 
        (SELECT COUNT(*) FROM customers WHERE status = 'ACTIVE') as active_customers,
        (SELECT COUNT(*) FROM customers WHERE status = 'INACTIVE') as inactive_customers,
        (SELECT COUNT(*) FROM customers WHERE status = 'SUSPENDED') as suspended_customers,
        (SELECT COUNT(*) FROM customers WHERE customer_type = 'CORPORATE') as corporate_customers,
        (SELECT COUNT(*) FROM customers WHERE customer_type = 'INDIVIDUAL') as individual_customers,
        (SELECT COUNT(*) FROM customers WHERE is_credit_customer = 1) as credit_customers,
        (SELECT COALESCE(SUM(credit_limit), 0) FROM customers WHERE is_credit_customer = 1) as total_credit_limit,
        (SELECT COUNT(*) FROM customers WHERE DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)) as new_customers_month,
        (SELECT COUNT(DISTINCT sender_customer_id) FROM shipments WHERE DATE(booking_date) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)) as active_shippers_month,
        (SELECT COUNT(*) FROM customers) as total_customers";

    $result_stats = $conn->query($sql_stats);
    
    if (!$result_stats) {
        throw new Exception('Failed to fetch customer statistics: ' . $conn->error);
    }
    
    $stats = $result_stats->fetch_assoc();
    
    // Calculate additional metrics
    $stats['inactive_rate'] = $stats['total_customers'] > 0 ? round(($stats['inactive_customers'] / $stats['total_customers']) * 100, 2) : 0;
    $stats['suspended_rate'] = $stats['total_customers'] > 0 ? round(($stats['suspended_customers'] / $stats['total_customers']) * 100, 2) : 0;
    $stats['corporate_rate'] = $stats['total_customers'] > 0 ? round(($stats['corporate_customers'] / $stats['total_customers']) * 100, 2) : 0;
    $stats['credit_utilization'] = $stats['credit_customers'] > 0 ? round(($stats['credit_customers'] / $stats['total_customers']) * 100, 2) : 0;
    
    // Get top performing locations (cities with most customers)
    $sql_locations = "SELECT 
        country,
        city,
        COUNT(*) as customer_count,
        COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END) as active_count
        FROM customers 
        WHERE status != 'SUSPENDED'
        GROUP BY country, city 
        ORDER BY customer_count DESC 
        LIMIT 5";
    
    $result_locations = $conn->query($sql_locations);
    $top_locations = [];
    
    if ($result_locations) {
        while ($row = $result_locations->fetch_assoc()) {
            $top_locations[] = [
                'location' => $row['city'] . ', ' . $row['country'],
                'total_customers' => (int)$row['customer_count'],
                'active_customers' => (int)$row['active_count']
            ];
        }
    }
    
    // Get customer growth trend (last 6 months)
    $sql_growth = "SELECT 
        DATE_FORMAT(created_at, '%Y-%m') as month,
        COUNT(*) as new_customers,
        COUNT(CASE WHEN customer_type = 'CORPORATE' THEN 1 END) as new_corporate,
        COUNT(CASE WHEN customer_type = 'INDIVIDUAL' THEN 1 END) as new_individual
        FROM customers 
        WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
        GROUP BY DATE_FORMAT(created_at, '%Y-%m')
        ORDER BY month ASC";
    
    $result_growth = $conn->query($sql_growth);
    $growth_data = [];
    
    if ($result_growth) {
        while ($row = $result_growth->fetch_assoc()) {
            $growth_data[] = [
                'month' => $row['month'],
                'new_customers' => (int)$row['new_customers'],
                'new_corporate' => (int)$row['new_corporate'],
                'new_individual' => (int)$row['new_individual']
            ];
        }
    }
    
    // Get recent customer activity
    $sql_recent = "SELECT 
        COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as customers_today,
        COUNT(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 1 END) as customers_week,
        COUNT(CASE WHEN DATE(updated_at) >= DATE_SUB(CURDATE(), INTERVAL 24 HOUR) THEN 1 END) as updated_today
        FROM customers";
    
    $result_recent = $conn->query($sql_recent);
    $recent_activity = $result_recent ? $result_recent->fetch_assoc() : [
        'customers_today' => 0,
        'customers_week' => 0,
        'updated_today' => 0
    ];
    
    // Combine all statistics
    $response_data = [
        'success' => true,
        'timestamp' => date('Y-m-d H:i:s'),
        'stats' => [
            'total_customers' => (int)$stats['total_customers'],
            'active_customers' => (int)$stats['active_customers'],
            'inactive_customers' => (int)$stats['inactive_customers'],
            'suspended_customers' => (int)$stats['suspended_customers'],
            'corporate_customers' => (int)$stats['corporate_customers'],
            'individual_customers' => (int)$stats['individual_customers'],
            'credit_customers' => (int)$stats['credit_customers'],
            'total_credit_limit' => (float)$stats['total_credit_limit'],
            'new_customers_month' => (int)$stats['new_customers_month'],
            'active_shippers_month' => (int)$stats['active_shippers_month']
        ],
        'rates' => [
            'inactive_rate' => $stats['inactive_rate'],
            'suspended_rate' => $stats['suspended_rate'],
            'corporate_rate' => $stats['corporate_rate'],
            'credit_utilization' => $stats['credit_utilization']
        ],
        'top_locations' => $top_locations,
        'growth_trend' => $growth_data,
        'recent_activity' => [
            'customers_today' => (int)$recent_activity['customers_today'],
            'customers_week' => (int)$recent_activity['customers_week'],
            'updated_today' => (int)$recent_activity['updated_today']
        ]
    ];
    
    echo json_encode($response_data);
    
} catch (Exception $e) {
    // Log error for debugging
    error_log("Customer Statistics Error: " . $e->getMessage());
    error_log("Stack trace: " . $e->getTraceAsString());
    
    // Return error response
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => 'Failed to fetch customer statistics',
        'error_details' => $e->getMessage(),
        'timestamp' => date('Y-m-d H:i:s')
    ]);
} finally {
    // Close database connection
    if (isset($conn)) {
        $conn->close();
    }
}
?>