<?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(['error' => 'Unauthorized access']);
    exit();
}

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

try {
    // DataTables parameters
    $draw = intval($_POST['draw'] ?? 1);
    $start = intval($_POST['start'] ?? 0);
    $length = intval($_POST['length'] ?? 25);
    $search = $_POST['search']['value'] ?? '';
    $orderColumn = intval($_POST['order'][0]['column'] ?? 1);
    $orderDir = $_POST['order'][0]['dir'] ?? 'asc';
    
    // Filter parameters
    $customerType = $_POST['customer_type'] ?? '';
    $status = $_POST['status'] ?? '';
    $credit = $_POST['credit'] ?? '';
    $country = $_POST['country'] ?? '';
    $period = $_POST['period'] ?? '';
    
    // Column mapping for ordering (excluding checkbox and actions columns)
    $columns = [
        0 => null, // Checkbox column - not sortable
        1 => 'customer_name', // Customer name (special handling needed)
        2 => 'customer_code',
        3 => 'customer_type',
        4 => 'phone', // Contact column
        5 => 'city', // Location column
        6 => 'status',
        7 => 'credit_limit', // Credit info column
        8 => null // Actions column - not sortable
    ];
    
    $orderBy = $columns[$orderColumn] ?? 'created_at';
    
    // Base query with all necessary fields
    $baseQuery = "FROM customers c 
                  LEFT JOIN users creator ON c.created_by = creator.user_id
                  WHERE 1=1";
    
    // Build WHERE conditions
    $whereConditions = [];
    $params = [];
    $paramTypes = '';
    
    // Search functionality
    if (!empty($search)) {
        $whereConditions[] = "(
            c.customer_code LIKE ? OR 
            c.company_name LIKE ? OR 
            c.first_name LIKE ? OR 
            c.last_name LIKE ? OR 
            c.email LIKE ? OR 
            c.phone LIKE ? OR 
            c.alternate_phone LIKE ? OR
            c.city LIKE ? OR 
            c.country LIKE ? OR
            c.address_line1 LIKE ?
        )";
        
        $searchTerm = "%$search%";
        for ($i = 0; $i < 10; $i++) {
            $params[] = $searchTerm;
            $paramTypes .= 's';
        }
    }
    
    // Customer type filter
    if (!empty($customerType)) {
        $whereConditions[] = "c.customer_type = ?";
        $params[] = $customerType;
        $paramTypes .= 's';
    }
    
    // Status filter
    if (!empty($status)) {
        $whereConditions[] = "c.status = ?";
        $params[] = $status;
        $paramTypes .= 's';
    }
    
    // Credit customer filter
    if ($credit !== '') {
        $whereConditions[] = "c.is_credit_customer = ?";
        $params[] = intval($credit);
        $paramTypes .= 'i';
    }
    
    // Country filter
    if (!empty($country)) {
        $whereConditions[] = "c.country = ?";
        $params[] = $country;
        $paramTypes .= 's';
    }
    
    // Period filter
    if (!empty($period)) {
        switch ($period) {
            case 'today':
                $whereConditions[] = "DATE(c.created_at) = CURDATE()";
                break;
            case 'week':
                $whereConditions[] = "c.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)";
                break;
            case 'month':
                $whereConditions[] = "c.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)";
                break;
            case 'quarter':
                $whereConditions[] = "c.created_at >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)";
                break;
            case 'year':
                $whereConditions[] = "c.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)";
                break;
        }
    }
    
    // Combine WHERE conditions
    if (!empty($whereConditions)) {
        $baseQuery .= " AND " . implode(" AND ", $whereConditions);
    }
    
    // Get total records without filters
    $totalRecordsQuery = "SELECT COUNT(*) as total FROM customers";
    $totalRecordsResult = $conn->query($totalRecordsQuery);
    $totalRecords = $totalRecordsResult->fetch_assoc()['total'];
    
    // Get filtered count
    $filteredCountQuery = "SELECT COUNT(*) as total " . $baseQuery;
    if (!empty($params)) {
        $stmt = $conn->prepare($filteredCountQuery);
        $stmt->bind_param($paramTypes, ...$params);
        $stmt->execute();
        $filteredRecords = $stmt->get_result()->fetch_assoc()['total'];
    } else {
        $filteredRecords = $conn->query($filteredCountQuery)->fetch_assoc()['total'];
    }
    
    // Main data query
    $selectFields = "SELECT 
        c.customer_id,
        c.customer_code,
        c.customer_type,
        c.company_name,
        c.first_name,
        c.last_name,
        c.email,
        c.phone,
        c.alternate_phone,
        c.address_line1,
        c.address_line2,
        c.city,
        c.state,
        c.postal_code,
        c.country,
        c.tax_id,
        c.is_credit_customer,
        c.credit_limit,
        c.payment_terms,
        c.status,
        c.created_at,
        c.updated_at,
        creator.full_name as created_by_name,
        CASE 
            WHEN c.customer_type = 'CORPORATE' THEN c.company_name
            ELSE CONCAT(c.first_name, ' ', c.last_name)
        END as customer_name";
    
    $dataQuery = $selectFields . " " . $baseQuery;
    
    // Add ordering
    if ($orderBy && $orderBy !== 'customer_name') {
        $dataQuery .= " ORDER BY c.$orderBy $orderDir";
    } else if ($orderBy === 'customer_name') {
        $dataQuery .= " ORDER BY customer_name $orderDir";
    } else {
        $dataQuery .= " ORDER BY c.created_at DESC";
    }
    
    // Add pagination
    $dataQuery .= " LIMIT $start, $length";
    
    // Execute main query
    if (!empty($params)) {
        $stmt = $conn->prepare($dataQuery);
        $stmt->bind_param($paramTypes, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();
    } else {
        $result = $conn->query($dataQuery);
    }
    
    // Process results
    $data = [];
    while ($row = $result->fetch_assoc()) {
        // Sanitize and format data
        $customerData = [
            'customer_id' => (int)$row['customer_id'],
            'customer_code' => htmlspecialchars($row['customer_code']),
            'customer_type' => $row['customer_type'],
            'company_name' => htmlspecialchars($row['company_name'] ?? ''),
            'first_name' => htmlspecialchars($row['first_name'] ?? ''),
            'last_name' => htmlspecialchars($row['last_name'] ?? ''),
            'customer_name' => htmlspecialchars($row['customer_name'] ?? ''),
            'email' => htmlspecialchars($row['email']),
            'phone' => htmlspecialchars($row['phone']),
            'alternate_phone' => htmlspecialchars($row['alternate_phone'] ?? ''),
            'address_line1' => htmlspecialchars($row['address_line1']),
            'address_line2' => htmlspecialchars($row['address_line2'] ?? ''),
            'city' => htmlspecialchars($row['city']),
            'state' => htmlspecialchars($row['state'] ?? ''),
            'postal_code' => htmlspecialchars($row['postal_code'] ?? ''),
            'country' => htmlspecialchars($row['country']),
            'tax_id' => htmlspecialchars($row['tax_id'] ?? ''),
            'is_credit_customer' => (int)$row['is_credit_customer'],
            'credit_limit' => (float)$row['credit_limit'],
            'payment_terms' => (int)$row['payment_terms'],
            'status' => $row['status'],
            'created_at' => $row['created_at'],
            'updated_at' => $row['updated_at'],
            'created_by_name' => htmlspecialchars($row['created_by_name'] ?? 'System')
        ];
        
        $data[] = $customerData;
    }
    
    // Prepare response
    $response = [
        'draw' => $draw,
        'recordsTotal' => (int)$totalRecords,
        'recordsFiltered' => (int)$filteredRecords,
        'data' => $data,
        'sql_debug' => [
            'total_query' => $totalRecordsQuery,
            'filtered_query' => $filteredCountQuery,
            'data_query' => $dataQuery,
            'parameters' => $params
        ]
    ];
    
    // Remove debug info in production
    if (!defined('DEBUG_MODE') || !DEBUG_MODE) {
        unset($response['sql_debug']);
    }
    
    echo json_encode($response);
    
} catch (Exception $e) {
    // Log the error for debugging
    error_log("Customers DataTables Error: " . $e->getMessage());
    error_log("Stack trace: " . $e->getTraceAsString());
    
    // Return error response
    http_response_code(500);
    echo json_encode([
        'draw' => intval($_POST['draw'] ?? 1),
        'recordsTotal' => 0,
        'recordsFiltered' => 0,
        'data' => [],
        'error' => 'An error occurred while loading customer data',
        'message' => $e->getMessage(),
        'debug_info' => [
            'file' => $e->getFile(),
            'line' => $e->getLine(),
            'request_data' => $_POST
        ]
    ]);
} finally {
    // Close database connection
    if (isset($conn)) {
        $conn->close();
    }
}
?>