<?php
session_start();
include '../db.php';
include '../auth.php';
require_right('Items');

// DataTables server-side processing parameters
$draw = intval($_GET['draw'] ?? 1);
$start = intval($_GET['start'] ?? 0);
$length = intval($_GET['length'] ?? 25);
if ($length < 1) {
    $length = 25;
}
$search_value = $_GET['search']['value'] ?? '';
$order_column = intval($_GET['order'][0]['column'] ?? 0);
$order_dir = $_GET['order'][0]['dir'] ?? 'desc';

// Filter parameters
$filter_roles = !empty($_GET['filter_roles']) ? explode(',', $_GET['filter_roles']) : [];
$filter_categories = !empty($_GET['filter_categories']) ? explode(',', $_GET['filter_categories']) : [];
$filter_serial = !empty($_GET['filter_serial']) ? explode(',', $_GET['filter_serial']) : [];
$filter_third_schedule = !empty($_GET['filter_third_schedule']) ? explode(',', $_GET['filter_third_schedule']) : [];

// Column mapping (matches table columns in order)
$columns = [
    'items.id',
    'items.item_code',
    'items.sku_number',
    'items.item_role',
    'items.name',
    'categories.name',
    'sub_categories.name',
    'items.retail_price',
    'items.cost_price',
    'items.tax_type',
    'tax_rates.rate'
];

// Build WHERE clause for search and filters
$where_conditions = [];
$params = [];
$param_types = "";

// Search filter
if (!empty($search_value)) {
    $search_pattern = '%' . $search_value . '%';
    $where_conditions[] = "(items.name LIKE ? OR items.item_code LIKE ? OR items.sku_number LIKE ? OR categories.name LIKE ? OR sub_categories.name LIKE ?)";
    $params[] = $search_pattern;
    $params[] = $search_pattern;
    $params[] = $search_pattern;
    $params[] = $search_pattern;
    $params[] = $search_pattern;
    $param_types .= "sssss";
}

// Role filter
if (!empty($filter_roles)) {
    $role_placeholders = implode(',', array_fill(0, count($filter_roles), '?'));
    $where_conditions[] = "items.item_role IN ($role_placeholders)";
    foreach ($filter_roles as $role) {
        $params[] = trim($role);
        $param_types .= "s";
    }
}

// Category filter
if (!empty($filter_categories)) {
    $category_placeholders = implode(',', array_fill(0, count($filter_categories), '?'));
    $where_conditions[] = "categories.name IN ($category_placeholders)";
    foreach ($filter_categories as $category) {
        $params[] = trim($category);
        $param_types .= "s";
    }
}

// Serial filter
if (!empty($filter_serial)) {
    $serial_conditions = [];
    foreach ($filter_serial as $serial_val) {
        $serial_val = trim($serial_val);
        if ($serial_val === 'serial_yes') {
            $serial_conditions[] = "items.is_serialized = 1";
        } elseif ($serial_val === 'serial_no') {
            $serial_conditions[] = "items.is_serialized = 0";
        }
    }
    if (!empty($serial_conditions)) {
        $where_conditions[] = "(" . implode(" OR ", $serial_conditions) . ")";
    }
}

// 3rd Schedule filter
if (!empty($filter_third_schedule)) {
    $third_schedule_conditions = [];
    foreach ($filter_third_schedule as $third_schedule_val) {
        $third_schedule_val = trim($third_schedule_val);
        if ($third_schedule_val === 'third_schedule_yes') {
            $third_schedule_conditions[] = "items.is_third_schedule = 1";
        } elseif ($third_schedule_val === 'third_schedule_no') {
            $third_schedule_conditions[] = "(items.is_third_schedule = 0 OR items.is_third_schedule IS NULL)";
        }
    }
    if (!empty($third_schedule_conditions)) {
        $where_conditions[] = "(" . implode(" OR ", $third_schedule_conditions) . ")";
    }
}

$where_clause = !empty($where_conditions) ? "WHERE " . implode(" AND ", $where_conditions) : "";

// Get total records count (before filtering)
$total_records_query = "SELECT COUNT(*) as total FROM items";
$total_result = $conn->query($total_records_query);
$total_records = $total_result->fetch_assoc()['total'];

// Get filtered records count (before adding limit/offset params)
$filtered_query = "SELECT COUNT(*) as total 
                   FROM items 
                   LEFT JOIN categories ON items.category_id = categories.id
                   LEFT JOIN sub_categories ON items.sub_category_id = sub_categories.id
                   $where_clause";

// Use the same params for count query (limit/offset haven't been added yet)
$count_params = $params;
$count_param_types = $param_types;

if (!empty($count_param_types)) {
    $filtered_stmt = $conn->prepare($filtered_query);
    $filtered_stmt->bind_param($count_param_types, ...$count_params);
    $filtered_stmt->execute();
    $filtered_result = $filtered_stmt->get_result();
    $filtered_records = $filtered_result->fetch_assoc()['total'];
    $filtered_stmt->close();
} else {
    $filtered_result = $conn->query($filtered_query);
    $filtered_records = $filtered_result->fetch_assoc()['total'];
}

// Build ORDER BY clause
$order_by_column = $columns[$order_column] ?? 'items.id';
$order_by_dir = strtoupper($order_dir) === 'ASC' ? 'ASC' : 'DESC';
$order_clause = "ORDER BY $order_by_column $order_by_dir";

// Build main query
// Select only columns required by the DataTable to reduce payload and CPU.
$query = "SELECT
            items.id,
            items.item_code,
            items.sku_number,
            items.item_role,
            items.name,
            items.retail_price,
            items.cost_price,
            items.tax_type,
            items.is_serialized,
            items.is_third_schedule,
            tax_rates.rate AS tax_rate,
            categories.name AS category_name,
            sub_categories.name AS sub_category_name
          FROM items
          LEFT JOIN tax_rates ON items.tax_rate_id = tax_rates.id
          LEFT JOIN categories ON items.category_id = categories.id
          LEFT JOIN sub_categories ON items.sub_category_id = sub_categories.id
          $where_clause
          $order_clause
          LIMIT ? OFFSET ?";

// Add limit and offset to params (always needed)
if (empty($params)) {
    $params = [$length, $start];
    $param_types = "ii";
} else {
    $params[] = $length;
    $params[] = $start;
    $param_types .= "ii";
}

// Execute query
$stmt = $conn->prepare($query);
if (!empty($param_types)) {
    $stmt->bind_param($param_types, ...$params);
} else {
    // No search params, but we still have limit and offset
    $stmt->bind_param("ii", $length, $start);
}
$stmt->execute();
$result = $stmt->get_result();

// Build response data
$data = [];
while ($row = $result->fetch_assoc()) {
    $serialFlag = $row['is_serialized'] == 1 ? 'serial_yes' : 'serial_no';
    
    // Build item role badges
    $role_badges = htmlspecialchars($row['item_role']);
    if ($row['is_serialized'] == 1) {
        $role_badges .= ' <span class="badge bg-info ms-1" title="Serial Tracking Enabled"><i class="fa fa-barcode"></i> Serial</span>';
    }
    if (!empty($row['is_third_schedule'])) {
        $role_badges .= ' <span class="badge bg-warning text-dark ms-1" title="Marked as 3rd Schedule for FBR">3rd Schedule</span>';
    }
    
    $data[] = [
        $row['id'],
        htmlspecialchars($row['item_code']),
        htmlspecialchars($row['sku_number']),
        '<span data-serialized="' . $serialFlag . '" data-role="' . htmlspecialchars($row['item_role']) . '">' . $role_badges . '</span>',
        htmlspecialchars($row['name']),
        htmlspecialchars($row['category_name'] ?? ''),
        htmlspecialchars($row['sub_category_name'] ?? ''),
        number_format($row['retail_price'], 2),
        number_format($row['cost_price'], 2),
        htmlspecialchars($row['tax_type']),
        $row['tax_rate'] ? number_format($row['tax_rate'], 2) . '%' : 'N/A'
    ];
}

$stmt->close();

// Return JSON response
header('Content-Type: application/json');
echo json_encode([
    'draw' => $draw,
    'recordsTotal' => $total_records,
    'recordsFiltered' => $filtered_records,
    'data' => $data
]);
