<?php
/**
 * DB Class - بديل JSON لـ PDO/MySQL
 * يحاكي نفس واجهة DB الأصلية لكن يعمل مع ملفات JSON
 */

require_once __DIR__ . '/../../json_db.php';

class DB {
    private static $jsonDB = null;
    private static $lastQuery = '';
    private static $bindings = [];
    private static $resultSet = [];
    private static $rowCountVal = 0;
    private static $lastId = 0;

    private static function getDB() {
        if (self::$jsonDB === null) {
            self::$jsonDB = new JsonDB();
        }
        return self::$jsonDB;
    }

    public static function query($sql) {
        self::$lastQuery = $sql;
        self::$bindings = [];
        self::$resultSet = [];
        self::$rowCountVal = 0;
    }

    public static function bind($param, $value, $type = null) {
        $param = ltrim($param, ':');
        self::$bindings[$param] = $value;
    }

    public static function execute() {
        $sql = strtolower(trim(self::$lastQuery));
        $db = self::getDB();

        if (strpos($sql, 'select') === 0) {
            return self::executeSelect($db);
        } elseif (strpos($sql, 'insert') === 0) {
            return self::executeInsert($db);
        } elseif (strpos($sql, 'update') === 0) {
            return self::executeUpdate($db);
        } elseif (strpos($sql, 'delete') === 0) {
            return self::executeDelete($db);
        }
        return false;
    }

    private static function getTable($sql) {
        if (preg_match('/from\s+`?(\w+)`?/i', $sql, $m)) return $m[1];
        if (preg_match('/into\s+`?(\w+)`?/i', $sql, $m)) return $m[1];
        if (preg_match('/update\s+`?(\w+)`?/i', $sql, $m)) return $m[1];
        return '';
    }

    private static function executeSelect($db) {
        $table = self::getTable(self::$lastQuery);
        $data = $db->fetchAll($table);
        $bindings = self::$bindings;
        // Check if query uses OR condition
        $useOr = (stripos(self::$lastQuery, ' OR ') !== false);

        if (!empty($bindings)) {
            $filtered = [];
            foreach ($data as $row) {
                if ($useOr) {
                    // OR logic: match if ANY binding matches
                    $match = false;
                    foreach ($bindings as $key => $value) {
                        if (isset($row[$key]) && $row[$key] == $value) {
                            $match = true;
                            break;
                        }
                    }
                } else {
                    // AND logic: match if ALL bindings match
                    $match = true;
                    foreach ($bindings as $key => $value) {
                        if (isset($row[$key]) && $row[$key] != $value) {
                            $match = false;
                            break;
                        }
                    }
                }
                if ($match) $filtered[] = $row;
            }
            $data = $filtered;
        }

        // Handle ORDER BY
        if (preg_match('/ORDER\s+BY\s+`?(\w+)`?\s*(ASC|DESC)?/i', self::$lastQuery, $orderMatch)) {
            $orderCol = $orderMatch[1];
            $orderDir = isset($orderMatch[2]) ? strtoupper($orderMatch[2]) : 'ASC';
            usort($data, function($a, $b) use ($orderCol, $orderDir) {
                // إذا الحقل غير موجود، نستخدم created_at كبديل
                $va = isset($a[$orderCol]) ? $a[$orderCol] : (isset($a['created_at']) ? $a['created_at'] : '0');
                $vb = isset($b[$orderCol]) ? $b[$orderCol] : (isset($b['created_at']) ? $b['created_at'] : '0');
                if ($orderDir === 'DESC') return strcmp(strval($vb), strval($va));
                return strcmp(strval($va), strval($vb));
            });
        }

        if (preg_match('/limit\s+1/i', self::$lastQuery) && count($data) > 0) {
            $data = [$data[0]];
        }

        self::$resultSet = $data;
        self::$rowCountVal = count($data);
        return true;
    }

    private static function executeInsert($db) {
        $table = self::getTable(self::$lastQuery);
        $sql = self::$lastQuery;
        $record = [];
        
        // Extract column names and placeholder names from SQL
        // Pattern: INSERT INTO table (col1, col2, ...) VALUE (:param1, :param2, ...)
        if (preg_match('/\(([^)]+)\)\s*VALUE?S?\s*\(([^)]+)\)/i', $sql, $matches)) {
            $columns = array_map(function($c) { return trim($c, " `\t\n\r"); }, explode(',', $matches[1]));
            $placeholders = array_map(function($p) { return trim($p, " :\t\n\r"); }, explode(',', $matches[2]));
            
            // Map column names to binding values using placeholder names
            for ($i = 0; $i < count($columns); $i++) {
                $col = $columns[$i];
                $param = isset($placeholders[$i]) ? $placeholders[$i] : $col;
                if (isset(self::$bindings[$param])) {
                    $record[$col] = self::$bindings[$param];
                } elseif (isset(self::$bindings[$col])) {
                    $record[$col] = self::$bindings[$col];
                }
            }
        } else {
            // Fallback: use bindings as-is
            foreach (self::$bindings as $key => $value) {
                $record[$key] = $value;
            }
        }
        
        $record['created_at'] = date('Y-m-d H:i:s');
        $record['updated_at'] = date('Y-m-d H:i:s');
        // last_input_at فقط لجدول users
        if ($table === 'users') {
            $record['last_input_at'] = date('Y-m-d H:i:s');
        }
        $id = $db->insert($table, $record);
        self::$lastId = $id;
        self::$rowCountVal = 1;
        return true;
    }

    private static function executeUpdate($db) {
        $table = self::getTable(self::$lastQuery);
        $sql = self::$lastQuery;
        $bindings = self::$bindings;
        $id = isset($bindings['id']) ? $bindings['id'] : null;

        if ($id !== null) {
            $updates = [];
            
            // Extract column=:param mappings from SET clause
            // Pattern: SET `col1` = :param1, `col2` = :param2 WHERE ...
            if (preg_match('/SET\s+(.+?)\s+WHERE/i', $sql, $setMatch)) {
                $setPart = $setMatch[1];
                // Match each `column` = :param pair
                if (preg_match_all('/`?(\w+)`?\s*=\s*:(\w+)/', $setPart, $pairs, PREG_SET_ORDER)) {
                    foreach ($pairs as $pair) {
                        $colName = $pair[1];   // actual column name
                        $paramName = $pair[2]; // binding parameter name
                        if (isset($bindings[$paramName])) {
                            $updates[$colName] = $bindings[$paramName];
                        }
                    }
                }
            }
            
            // Fallback if no SET parsing worked
            if (empty($updates)) {
                foreach ($bindings as $key => $value) {
                    if ($key !== 'id') {
                        $updates[$key] = $value;
                    }
                }
            }
            
            // تحديث updated_at تلقائياً عند كل تعديل
            $updates['updated_at'] = date('Y-m-d H:i:s');
            $result = $db->update($table, $id, $updates);
            self::$rowCountVal = $result ? 1 : 0;
            return $result;
        }
        return false;
    }

    private static function executeDelete($db) {
        $table = self::getTable(self::$lastQuery);
        $bindings = self::$bindings;

        if (empty($bindings)) {
            $db->deleteAll($table);
            self::$rowCountVal = 1;
            return true;
        }

        $id = isset($bindings['id']) ? $bindings['id'] : null;
        if ($id !== null) {
            $result = $db->delete($table, $id);
            self::$rowCountVal = $result ? 1 : 0;
            return $result;
        }
        return false;
    }

    public static function fetch() {
        if (!empty(self::$resultSet)) {
            return (object) self::$resultSet[0];
        }
        return false;
    }

    public static function fetchAll() {
        $results = [];
        foreach (self::$resultSet as $row) {
            $results[] = (object) $row;
        }
        return $results;
    }

    public static function rowCount() {
        return self::$rowCountVal;
    }

    public static function lastInsertId() {
        return self::$lastId;
    }

    public static function beginTransaction() { return true; }
    public static function endTransaction() { return true; }
    public static function cancelTransaction() { return true; }
    public static function debugDumpParams() { return null; }
}
