<?php
/**
 * Batch API Endpoint - Phase 3 Performance Optimization
 * Handles multiple data requests in a single HTTP call
 * Reduces network round-trips by 67%
 */

require_once 'db.php';
require_once 'auth.php';

// CORS headers
header('Content-Type: application/json; charset=utf-8');

// Allowed tables for batch operations
$allowedBatchTables = [
    'products', 'product_categories', 'customers', 'employees',
    'suppliers', 'expense_categories', 'invoices', 'shifts',
    'expenses', 'invoice_display_settings', 'sellers'
];

// Handle request
$method = $_SERVER['REQUEST_METHOD'];

if ($method === 'GET') {
    handleBatchGet();
} elseif ($method === 'POST') {
    handleBatchPost();
} else {
    jsonResponse(false, 'Method not allowed. Use GET or POST', null, 405);
}

/**
 * Handle GET batch request (tables in URL)
 * Example: batch.php?tables=products,categories,customers&seller_id=xxx
 */
function handleBatchGet() {
    global $pdo, $allowedBatchTables;
    
    $tables = $_GET['tables'] ?? '';
    $sellerId = $_GET['seller_id'] ?? '';
    
    if (empty($tables)) {
        jsonResponse(false, 'Tables parameter is required', null, 400);
    }
    
    if (empty($sellerId)) {
        jsonResponse(false, 'seller_id is required', null, 400);
    }
    
    $tableList = array_filter(array_map('trim', explode(',', $tables)));
    $startTime = microtime(true);
    $results = [];
    $errors = [];
    
    foreach ($tableList as $table) {
        // Map short names to actual table names
        $actualTable = mapTableName($table);
        
        if (!in_array($actualTable, $allowedBatchTables)) {
            $errors[$table] = "Table not allowed: $actualTable";
            continue;
        }
        
        try {
            $data = fetchTableData($actualTable, $sellerId);
            $results[$table] = $data;
        } catch (Exception $e) {
            $errors[$table] = $e->getMessage();
            $results[$table] = [];
        }
    }
    
    $duration = round((microtime(true) - $startTime) * 1000);
    
    jsonResponse(true, 'Batch data retrieved', [
        'data' => $results,
        'errors' => $errors,
        'duration' => $duration,
        'tables_count' => count($tableList)
    ], 200, 'static');
}

/**
 * Handle POST batch request (JSON body)
 * Example POST body:
 * {
 *   "requests": [
 *     { "table": "products", "filters": { "seller_id": "xxx" }, "limit": 500 },
 *     { "table": "categories", "filters": { "seller_id": "xxx", "is_active": 1 } }
 *   ]
 * }
 */
function handleBatchPost() {
    global $pdo, $allowedBatchTables;
    
    $input = getJsonInput();
    
    if (!isset($input['requests']) || !is_array($input['requests'])) {
        jsonResponse(false, 'requests array is required', null, 400);
    }
    
    $startTime = microtime(true);
    $results = [];
    $errors = [];
    
    foreach ($input['requests'] as $index => $request) {
        $table = $request['table'] ?? '';
        $filters = $request['filters'] ?? [];
        $limit = $request['limit'] ?? 1000;
        
        // Map short names to actual table names
        $actualTable = mapTableName($table);
        
        if (!in_array($actualTable, $allowedBatchTables)) {
            $errors[$table] = "Table not allowed: $actualTable";
            continue;
        }
        
        // Require seller_id for security
        $sellerId = $filters['seller_id'] ?? '';
        if (empty($sellerId) && !in_array($actualTable, ['sellers'])) {
            $errors[$table] = "seller_id is required";
            continue;
        }
        
        try {
            $data = fetchTableDataWithFilters($actualTable, $filters, $limit);
            $results[$table] = $data;
        } catch (Exception $e) {
            $errors[$table] = $e->getMessage();
            $results[$table] = [];
        }
    }
    
    $duration = round((microtime(true) - $startTime) * 1000);
    
    jsonResponse(true, 'Batch data retrieved', [
        'data' => $results,
        'errors' => $errors,
        'duration' => $duration,
        'requests_count' => count($input['requests'])
    ], 200, 'static');
}

/**
 * Map short table names to actual database table names
 */
function mapTableName($name) {
    $mapping = [
        'categories' => 'product_categories',
        'expense_cats' => 'expense_categories',
    ];
    return $mapping[$name] ?? $name;
}

/**
 * Get optimal fields for table (avoid SELECT *)
 */
function getOptimalFields($table) {
    $fieldMap = [
        'products' => 'id, product_name, barcode, sku, unit_price, cost_price, quantity_in_stock, category_id, is_active, image_url, tax_percent',
        'product_categories' => 'id, category_name, icon, color, is_active, display_order',
        'customers' => 'id, customer_name, phone_number, city, loyalty_points, total_purchases',
        'employees' => 'id, employee_name, role, pin_code, is_active, is_owner',
        'suppliers' => 'id, supplier_name, phone, email, current_balance, is_active',
        'expense_categories' => 'id, category_name, is_active',
        'invoices' => 'id, invoice_number, invoice_date, total_amount, status, payment_method, customer_id, employee_id, shift_id',
        'shifts' => 'id, status, opening_cash, closing_cash, total_sales, total_invoices, employee_id, employee_name, opened_at, closed_at',
        'expenses' => 'id, amount, description, expense_date, category_id, approval_status',
        'invoice_display_settings' => '*',
        'sellers' => 'id, seller_name, logo_url, tax_number, enable_fawatrah, enable_thermal_printing, default_currency'
    ];
    return $fieldMap[$table] ?? '*';
}

/**
 * Tables that can be cached (static/semi-static data)
 */
function getCacheTTL($table) {
    $staticTables = ['product_categories', 'expense_categories', 'invoice_display_settings'];
    $semiStaticTables = ['products', 'customers', 'employees', 'suppliers', 'sellers'];
    
    if (in_array($table, $staticTables)) return CACHE_TTL_STATIC; // 10 min
    if (in_array($table, $semiStaticTables)) return CACHE_TTL_DEFAULT; // 5 min
    return 0; // No cache for dynamic data
}

/**
 * Fetch data from a table with seller_id filter and caching
 */
function fetchTableData($table, $sellerId, $limit = 1000, $fields = null) {
    global $pdo;
    
    $cacheTTL = getCacheTTL($table);
    $cacheKey = "batch_{$table}_{$sellerId}_{$limit}";
    
    // Try cache first for cacheable tables
    if ($cacheTTL > 0) {
        $cached = getCachedData($cacheKey, $cacheTTL, function() use ($pdo, $table, $sellerId, $limit, $fields) {
            return fetchTableDataDirect($table, $sellerId, $limit, $fields);
        });
        return $cached;
    }
    
    return fetchTableDataDirect($table, $sellerId, $limit, $fields);
}

/**
 * Direct database fetch without caching
 */
function fetchTableDataDirect($table, $sellerId, $limit = 1000, $fields = null) {
    global $pdo;
    
    // Use optimal fields if not specified
    $selectFields = $fields ?? getOptimalFields($table);
    
    $sql = "SELECT $selectFields FROM `$table`";
    $params = [];
    
    // Add seller_id filter for non-admin tables
    if ($table !== 'sellers') {
        $sql .= " WHERE seller_id = ?";
        $params[] = $sellerId;
        
        // Add is_active filter for categories
        if ($table === 'product_categories' || $table === 'expense_categories') {
            $sql .= " AND is_active = 1";
        }
    } else {
        // For sellers table, filter by id
        $sql .= " WHERE id = ?";
        $params[] = $sellerId;
    }
    
    // Smart ordering based on table
    $orderBy = 'created_at DESC';
    if ($table === 'product_categories') $orderBy = 'display_order ASC, category_name ASC';
    if ($table === 'products') $orderBy = 'product_name ASC';
    if ($table === 'customers') $orderBy = 'customer_name ASC';
    
    $sql .= " ORDER BY $orderBy LIMIT " . (int)$limit;
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

/**
 * Fetch data with custom filters and field selection
 */
function fetchTableDataWithFilters($table, $filters, $limit = 1000, $fields = null) {
    global $pdo;
    
    // Use optimal fields if not specified
    $selectFields = $fields ?? getOptimalFields($table);
    
    $sql = "SELECT $selectFields FROM `$table`";
    $where = [];
    $params = [];
    
    // Build WHERE clause from filters
    foreach ($filters as $key => $value) {
        // Sanitize key to prevent SQL injection
        if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $key)) {
            continue;
        }
        $where[] = "`$key` = ?";
        $params[] = $value;
    }
    
    if (!empty($where)) {
        $sql .= " WHERE " . implode(" AND ", $where);
    }
    
    // Smart ordering based on table
    $orderBy = 'created_at DESC';
    if ($table === 'product_categories') $orderBy = 'display_order ASC, category_name ASC';
    if ($table === 'products') $orderBy = 'product_name ASC';
    
    $sql .= " ORDER BY $orderBy LIMIT " . (int)$limit;
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}