<?php
/**
 * Universal Auto-CRUD API
 * فوترة بلس - POS System
 * 
 * Dynamic CRUD operations for any database table
 * No code changes needed when tables/fields are modified
 * 
 * Security: Seller ID data isolation implemented
 */

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

// Get action and table from query string
$action = $_GET['action'] ?? '';
$table = $_GET['table'] ?? '';

// Allowed tables (security whitelist)
$allowedTables = [
    'admins', 'sellers', 'employees', 'customers', 'products', 
    'product_categories', 'invoices', 'invoice_items', 'xml_templates',
    'inventory_movements', 'inventory_alerts', 'expense_categories', 
    'expenses', 'suppliers', 'purchase_orders', 'purchase_order_items',
    'supplier_payments', 'shifts', 'cash_drawer_transactions',
    'loyalty_transactions', 'promotions', 'audit_log', 'employee_performance',
    'invoice_display_settings',
    'stocktake_sessions', 'stocktake_counts', 'variance_reasons', 'stocktake_settlements'
];

// Sensitive tables that REQUIRE seller_id filter
$sensitiveTable = [
    'employees', 'products', 'product_categories',
    'invoices', 'customers',
    'expenses', 'expense_categories', 'shifts',
    'cash_drawer_transactions', 'suppliers',
    'inventory_movements', 'loyalty_transactions', 'promotions',
    'audit_log', 'employee_performance', 'invoice_display_settings',
    'stocktake_sessions', 'stocktake_counts', 'variance_reasons',
    'purchase_orders', 'supplier_payments'
];

// Tables that DON'T have seller_id column (use parent table relationship)
$tablesWithoutSellerId = [
    'purchase_order_items',  // linked via po_id -> purchase_orders.seller_id
    'invoice_items',         // linked via invoice_id -> invoices.seller_id
    'stocktake_settlements'  // linked via session_id -> stocktake_sessions.seller_id
];

// Admin-only tables (don't require seller_id)
$adminOnlyTables = ['admins', 'sellers', 'xml_templates'];

/**
 * Parse filters from multiple sources for compatibility
 * Sources: filter[key]=value, filters=JSON, direct parameters
 */
function parseFilters() {
    $filters = [];
    
    // 1. Read from filter[key]=value (useCrud pattern)
    if (isset($_GET['filter']) && is_array($_GET['filter'])) {
        foreach ($_GET['filter'] as $key => $value) {
            if (preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $key)) {
                $filters[$key] = $value;
            }
        }
    }
    
    // 2. Read from filters=JSON string
    if (isset($_GET['filters']) && !empty($_GET['filters'])) {
        $jsonFilters = json_decode($_GET['filters'], true);
        if (is_array($jsonFilters)) {
            foreach ($jsonFilters as $key => $value) {
                if (preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $key)) {
                    $filters[$key] = $value;
                }
            }
        }
    }
    
    // 3. Read direct seller_id parameter (legacy compatibility)
    if (isset($_GET['seller_id']) && !empty($_GET['seller_id'])) {
        $filters['seller_id'] = $_GET['seller_id'];
    }
    
    return $filters;
}

// Validate table name
function validateTable($table) {
    global $allowedTables;
    if (empty($table)) {
        jsonResponse(false, 'Table name is required', null, 400);
    }
    if (!in_array($table, $allowedTables)) {
        jsonResponse(false, 'Table not allowed: ' . $table, null, 403);
    }
    // Additional sanitization
    if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $table)) {
        jsonResponse(false, 'Invalid table name format', null, 400);
    }
    return $table;
}

// Route to appropriate handler
switch ($action) {
    case 'ping':
        // Health check endpoint - no authentication needed
        jsonResponse(true, 'pong', [
            'timestamp' => date('Y-m-d H:i:s'),
            'version' => 'API v1.0',
            'status' => 'healthy'
        ]);
        break;
    case 'list-tables':
        listTables();
        break;
    case 'columns':
        getColumns();
        break;
    case 'get':
        getAll();
        break;
    case 'get-by-id':
        getById();
        break;
    case 'create':
        create();
        break;
    case 'update':
        update();
        break;
    case 'delete':
        deleteRecord();
        break;
    case 'bulk-delete':
        bulkDelete();
        break;
    case 'query':
        customQuery();
        break;
    default:
        jsonResponse(false, 'Invalid action. Allowed: ping, list-tables, columns, get, get-by-id, create, update, delete, bulk-delete, query', null, 400);
}

/**
 * List all allowed tables
 */
function listTables() {
    global $allowedTables;
    jsonResponse(true, 'Tables retrieved', $allowedTables);
}

/**
 * Get table columns/schema
 */
function getColumns() {
    global $pdo, $table;
    $table = validateTable($table);
    
    try {
        $stmt = $pdo->prepare("SHOW COLUMNS FROM `$table`");
        $stmt->execute();
        $columns = $stmt->fetchAll();
        
        jsonResponse(true, 'Columns retrieved', $columns);
    } catch (PDOException $e) {
        jsonResponse(false, 'Failed to get columns: ' . $e->getMessage(), null, 500);
    }
}

/**
 * Get all records from table
 * With seller_id security enforcement
 */
function getAll() {
    global $pdo, $table, $sensitiveTable, $adminOnlyTables, $tablesWithoutSellerId;
    $table = validateTable($table);
    
    try {
        // Parse filters from all sources
        $parsedFilters = parseFilters();
        $sellerId = $parsedFilters['seller_id'] ?? null;
        
        // Skip seller_id check for tables that don't have this column
        $skipSellerIdFilter = in_array($table, $tablesWithoutSellerId);
        
        // Phase 2: Enforce seller_id for sensitive tables (except those without seller_id)
        if (in_array($table, $sensitiveTable) && !$skipSellerIdFilter) {
            if (empty($sellerId)) {
                // Log warning for monitoring
                error_log("SECURITY WARNING: Request to sensitive table '$table' without seller_id from " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown'));
                // Return empty array instead of error (safe behavior)
                jsonResponse(true, 'No seller_id provided for sensitive table', []);
                return;
            }
        }
        
        // Build query with optional filters
        $where = [];
        $params = [];
        
        // Apply seller_id filter from parsed filters (skip for tables without this column)
        if (!empty($sellerId) && !$skipSellerIdFilter) {
            $where[] = "seller_id = ?";
            $params[] = $sellerId;
        }
        
        // Handle po_id filter for purchase_order_items
        if (isset($parsedFilters['po_id']) && !empty($parsedFilters['po_id'])) {
            $where[] = "po_id = ?";
            $params[] = $parsedFilters['po_id'];
        } elseif (isset($_GET['po_id']) && !empty($_GET['po_id'])) {
            $where[] = "po_id = ?";
            $params[] = $_GET['po_id'];
        }
        
        // Handle session_id filter for stocktake tables
        if (isset($parsedFilters['session_id']) && !empty($parsedFilters['session_id'])) {
            $where[] = "session_id = ?";
            $params[] = $parsedFilters['session_id'];
        } elseif (isset($_GET['session_id']) && !empty($_GET['session_id'])) {
            $where[] = "session_id = ?";
            $params[] = $_GET['session_id'];
        }
        
        // Handle status filter
        if (isset($parsedFilters['status']) && $parsedFilters['status'] !== '') {
            $where[] = "status = ?";
            $params[] = $parsedFilters['status'];
        } elseif (isset($_GET['status']) && !empty($_GET['status'])) {
            $where[] = "status = ?";
            $params[] = $_GET['status'];
        }
        
        // Handle is_active filter
        if (isset($parsedFilters['is_active'])) {
            $where[] = "is_active = ?";
            $params[] = $parsedFilters['is_active'];
        } elseif (isset($_GET['is_active'])) {
            $where[] = "is_active = ?";
            $params[] = $_GET['is_active'];
        }
        
        // Handle category_id filter
        if (isset($parsedFilters['category_id']) && !empty($parsedFilters['category_id'])) {
            $where[] = "category_id = ?";
            $params[] = $parsedFilters['category_id'];
        } elseif (isset($_GET['category_id']) && !empty($_GET['category_id'])) {
            $where[] = "category_id = ?";
            $params[] = $_GET['category_id'];
        }
        
        // Handle invoice_id filter for invoice_items table
        if (isset($parsedFilters['invoice_id']) && !empty($parsedFilters['invoice_id'])) {
            $where[] = "invoice_id = ?";
            $params[] = $parsedFilters['invoice_id'];
        } elseif (isset($_GET['invoice_id']) && !empty($_GET['invoice_id'])) {
            $where[] = "invoice_id = ?";
            $params[] = $_GET['invoice_id'];
        }
        
        // Handle customer_id filter
        if (isset($parsedFilters['customer_id']) && !empty($parsedFilters['customer_id'])) {
            $where[] = "customer_id = ?";
            $params[] = $parsedFilters['customer_id'];
        } elseif (isset($_GET['customer_id']) && !empty($_GET['customer_id'])) {
            $where[] = "customer_id = ?";
            $params[] = $_GET['customer_id'];
        }
        
        // Handle product_id filter
        if (isset($parsedFilters['product_id']) && !empty($parsedFilters['product_id'])) {
            $where[] = "product_id = ?";
            $params[] = $parsedFilters['product_id'];
        } elseif (isset($_GET['product_id']) && !empty($_GET['product_id'])) {
            $where[] = "product_id = ?";
            $params[] = $_GET['product_id'];
        }
        
        // Handle is_hidden filter
        if (isset($parsedFilters['is_hidden'])) {
            $where[] = "is_hidden = ?";
            $params[] = $parsedFilters['is_hidden'];
        } elseif (isset($_GET['is_hidden'])) {
            $where[] = "is_hidden = ?";
            $params[] = $_GET['is_hidden'];
        }
        
        // Handle employee_id filter
        if (isset($parsedFilters['employee_id']) && !empty($parsedFilters['employee_id'])) {
            $where[] = "employee_id = ?";
            $params[] = $parsedFilters['employee_id'];
        } elseif (isset($_GET['employee_id']) && !empty($_GET['employee_id'])) {
            $where[] = "employee_id = ?";
            $params[] = $_GET['employee_id'];
        }
        
        // Handle date range filters
        if (isset($_GET['date_from']) && !empty($_GET['date_from'])) {
            $where[] = "created_at >= ?";
            $params[] = $_GET['date_from'];
        }
        if (isset($_GET['date_to']) && !empty($_GET['date_to'])) {
            $where[] = "created_at <= ?";
            $params[] = $_GET['date_to'];
        }
        
        // Handle field selection (avoid SELECT * when possible)
        $selectFields = '*';
        if (isset($_GET['fields']) && !empty($_GET['fields'])) {
            $requestedFields = array_map('trim', explode(',', $_GET['fields']));
            $validFields = [];
            foreach ($requestedFields as $field) {
                // Sanitize field names
                if (preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $field)) {
                    $validFields[] = "`$field`";
                }
            }
            if (!empty($validFields)) {
                $selectFields = implode(', ', $validFields);
            }
        }
        
        // Build SQL with optimized field selection
        $sql = "SELECT $selectFields FROM `$table`";
        if (!empty($where)) {
            $sql .= " WHERE " . implode(" AND ", $where);
        }
        
        // Handle ordering with smart defaults per table
        $orderBy = $_GET['order_by'] ?? null;
        $orderDir = strtoupper($_GET['order_dir'] ?? 'DESC');
        if (!in_array($orderDir, ['ASC', 'DESC'])) $orderDir = 'DESC';
        
        // Smart default ordering based on table type
        if (!$orderBy) {
            switch ($table) {
                case 'products':
                    $orderBy = 'product_name';
                    $orderDir = 'ASC';
                    break;
                case 'customers':
                    $orderBy = 'customer_name';
                    $orderDir = 'ASC';
                    break;
                case 'product_categories':
                    $orderBy = 'display_order';
                    $orderDir = 'ASC';
                    break;
                case 'expense_categories':
                    $orderBy = 'category_name';
                    $orderDir = 'ASC';
                    break;
                default:
                    $orderBy = 'created_at';
            }
        }
        
        // Sanitize order_by to prevent SQL injection
        if (preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $orderBy)) {
            $sql .= " ORDER BY `$orderBy` $orderDir";
        }
        
        // Handle pagination
        $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 1000;
        $offset = isset($_GET['offset']) ? (int)$_GET['offset'] : 0;
        $sql .= " LIMIT $limit OFFSET $offset";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $data = $stmt->fetchAll();
        
        // Phase 4: Data leak detection (logging only - safe)
        if (in_array($table, $sensitiveTable) && count($data) > 0) {
            $sellerIds = array_unique(array_filter(array_column($data, 'seller_id')));
            if (count($sellerIds) > 1) {
                error_log("DATA LEAK DETECTED in $table: Multiple seller_ids found: " . implode(', ', $sellerIds));
                // For safety, we log but don't block - to avoid breaking the app
            }
        }
        
        // ✅ RADICAL FIX: Disable ALL HTTP caching to ensure instant data updates
        // This prevents browser from using stale cached responses
        $cacheType = false;
        
        jsonResponse(true, 'Data retrieved', $data, 200, $cacheType);
    } catch (PDOException $e) {
        jsonResponse(false, 'Failed to get data: ' . $e->getMessage(), null, 500);
    }
}

/**
 * Get single record by ID
 */
function getById() {
    global $pdo, $table;
    $table = validateTable($table);
    $id = $_GET['id'] ?? '';
    
    if (empty($id)) {
        jsonResponse(false, 'ID is required', null, 400);
    }
    
    try {
        $stmt = $pdo->prepare("SELECT * FROM `$table` WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $data = $stmt->fetch();
        
        if ($data) {
            jsonResponse(true, 'Record found', $data);
        } else {
            jsonResponse(false, 'Record not found', null, 404);
        }
    } catch (PDOException $e) {
        jsonResponse(false, 'Failed to get record: ' . $e->getMessage(), null, 500);
    }
}

/**
 * Create new record
 */
function create() {
    global $pdo, $table;
    
    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        jsonResponse(false, 'Method not allowed', null, 405);
    }
    
    $table = validateTable($table);
    $input = getJsonInput();
    
    if (empty($input)) {
        jsonResponse(false, 'Request body is required', null, 400);
    }
    
    try {
        // Get table columns
        $stmt = $pdo->prepare("SHOW COLUMNS FROM `$table`");
        $stmt->execute();
        $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        // Filter input to only include valid columns
        $validData = [];
        foreach ($input as $key => $value) {
            if (in_array($key, $columns)) {
                $validData[$key] = $value;
            }
        }
        
        // Check for duplicate category name (product_categories table)
        if ($table === 'product_categories' && isset($validData['category_name']) && isset($validData['seller_id'])) {
            $categoryName = trim($validData['category_name']);
            $sellerId = $validData['seller_id'];
            
            $checkStmt = $pdo->prepare("SELECT id FROM `product_categories` WHERE seller_id = ? AND LOWER(TRIM(category_name)) = LOWER(?) AND is_active = 1 LIMIT 1");
            $checkStmt->execute([$sellerId, $categoryName]);
            $existingCategory = $checkStmt->fetch();
            
            if ($existingCategory) {
                jsonResponse(false, 'فئة بهذا الاسم موجودة بالفعل', ['existing_id' => $existingCategory['id']], 409);
            }
        }
        
        // Check for duplicate expense category name (expense_categories table)
        if ($table === 'expense_categories' && isset($validData['category_name']) && isset($validData['seller_id'])) {
            $categoryName = trim($validData['category_name']);
            $sellerId = $validData['seller_id'];
            
            $checkStmt = $pdo->prepare("SELECT id FROM `expense_categories` WHERE seller_id = ? AND LOWER(TRIM(category_name)) = LOWER(?) AND is_active = 1 LIMIT 1");
            $checkStmt->execute([$sellerId, $categoryName]);
            $existingCategory = $checkStmt->fetch();
            
            if ($existingCategory) {
                jsonResponse(false, 'فئة مصروفات بهذا الاسم موجودة بالفعل', ['existing_id' => $existingCategory['id']], 409);
            }
        }
        
        // Auto-generate UUID if id is not provided and table has id column
        if (in_array('id', $columns) && !isset($validData['id'])) {
            $validData['id'] = generateUUID();
        }
        
        // Auto-set timestamps
        if (in_array('created_at', $columns) && !isset($validData['created_at'])) {
            $validData['created_at'] = getCurrentTimestamp();
        }
        if (in_array('updated_at', $columns) && !isset($validData['updated_at'])) {
            $validData['updated_at'] = getCurrentTimestamp();
        }
        
        if (empty($validData)) {
            jsonResponse(false, 'No valid fields provided', null, 400);
        }
        
        // Build INSERT query
        $fields = array_keys($validData);
        $placeholders = array_fill(0, count($fields), '?');
        
        $sql = "INSERT INTO `$table` (`" . implode("`, `", $fields) . "`) VALUES (" . implode(", ", $placeholders) . ")";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array_values($validData));
        
        // Return created record
        $stmt = $pdo->prepare("SELECT * FROM `$table` WHERE id = ? LIMIT 1");
        $stmt->execute([$validData['id']]);
        $created = $stmt->fetch();
        
        jsonResponse(true, 'Record created successfully', $created, 201);
    } catch (PDOException $e) {
        jsonResponse(false, 'Failed to create record: ' . $e->getMessage(), null, 500);
    }
}

/**
 * Update existing record
 */
function update() {
    global $pdo, $table;
    
    if ($_SERVER['REQUEST_METHOD'] !== 'POST' && $_SERVER['REQUEST_METHOD'] !== 'PUT') {
        jsonResponse(false, 'Method not allowed', null, 405);
    }
    
    $table = validateTable($table);
    $id = $_GET['id'] ?? '';
    $input = getJsonInput();
    
    if (empty($id)) {
        jsonResponse(false, 'ID is required', null, 400);
    }
    
    if (empty($input)) {
        jsonResponse(false, 'Request body is required', null, 400);
    }
    
    try {
        // Get table columns
        $stmt = $pdo->prepare("SHOW COLUMNS FROM `$table`");
        $stmt->execute();
        $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        // Filter input to only include valid columns (exclude id)
        $validData = [];
        foreach ($input as $key => $value) {
            if (in_array($key, $columns) && $key !== 'id') {
                $validData[$key] = $value;
            }
        }
        
        // Auto-set updated_at
        if (in_array('updated_at', $columns)) {
            $validData['updated_at'] = getCurrentTimestamp();
        }
        
        if (empty($validData)) {
            jsonResponse(false, 'No valid fields provided', null, 400);
        }
        
        // Build UPDATE query
        $setClause = [];
        $params = [];
        foreach ($validData as $key => $value) {
            $setClause[] = "`$key` = ?";
            $params[] = $value;
        }
        $params[] = $id;
        
        $sql = "UPDATE `$table` SET " . implode(", ", $setClause) . " WHERE id = ?";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        
        // Return updated record
        $stmt = $pdo->prepare("SELECT * FROM `$table` WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $updated = $stmt->fetch();
        
        if ($updated) {
            jsonResponse(true, 'Record updated successfully', $updated);
        } else {
            jsonResponse(false, 'Record not found', null, 404);
        }
    } catch (PDOException $e) {
        jsonResponse(false, 'Failed to update record: ' . $e->getMessage(), null, 500);
    }
}

/**
 * Delete record
 */
function deleteRecord() {
    global $pdo, $table;
    
    if ($_SERVER['REQUEST_METHOD'] !== 'POST' && $_SERVER['REQUEST_METHOD'] !== 'DELETE') {
        jsonResponse(false, 'Method not allowed', null, 405);
    }
    
    $table = validateTable($table);
    $id = $_GET['id'] ?? '';
    
    if (empty($id)) {
        jsonResponse(false, 'ID is required', null, 400);
    }
    
    try {
        // Check if record exists
        $stmt = $pdo->prepare("SELECT * FROM `$table` WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $existing = $stmt->fetch();
        
        if (!$existing) {
            jsonResponse(false, 'Record not found', null, 404);
        }
        
        // Delete record
        $stmt = $pdo->prepare("DELETE FROM `$table` WHERE id = ?");
        $stmt->execute([$id]);
        
        jsonResponse(true, 'Record deleted successfully', $existing);
    } catch (PDOException $e) {
        jsonResponse(false, 'Failed to delete record: ' . $e->getMessage(), null, 500);
    }
}

/**
 * Bulk delete records
 */
function bulkDelete() {
    global $pdo, $table;
    
    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        jsonResponse(false, 'Method not allowed', null, 405);
    }
    
    $table = validateTable($table);
    $input = getJsonInput();
    $ids = $input['ids'] ?? [];
    
    if (empty($ids) || !is_array($ids)) {
        jsonResponse(false, 'Array of IDs is required', null, 400);
    }
    
    try {
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $stmt = $pdo->prepare("DELETE FROM `$table` WHERE id IN ($placeholders)");
        $stmt->execute($ids);
        
        $deletedCount = $stmt->rowCount();
        
        jsonResponse(true, "Deleted $deletedCount records", ['deleted_count' => $deletedCount]);
    } catch (PDOException $e) {
        jsonResponse(false, 'Failed to delete records: ' . $e->getMessage(), null, 500);
    }
}

/**
 * Execute custom query (for advanced operations)
 */
function customQuery() {
    global $pdo;
    
    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        jsonResponse(false, 'Method not allowed', null, 405);
    }
    
    // Require authentication for custom queries
    requireAuth();
    
    $input = getJsonInput();
    $query = $input['query'] ?? '';
    $params = $input['params'] ?? [];
    
    if (empty($query)) {
        jsonResponse(false, 'Query is required', null, 400);
    }
    
    // Security: Only allow SELECT queries for safety
    $queryLower = strtolower(trim($query));
    if (strpos($queryLower, 'select') !== 0) {
        jsonResponse(false, 'Only SELECT queries are allowed via this endpoint', null, 403);
    }
    
    try {
        $stmt = $pdo->prepare($query);
        $stmt->execute($params);
        $data = $stmt->fetchAll();
        
        jsonResponse(true, 'Query executed', $data);
    } catch (PDOException $e) {
        jsonResponse(false, 'Query failed: ' . $e->getMessage(), null, 500);
    }
}
