<?php
namespace Core;
use Core\Database\Connection;
use Core\Session;
use Exception;

abstract class Model extends Connection
{
    protected static string $table;
    protected static string $primaryKey = 'id';
    protected static bool $auditable = true;

    // A placeholder for fetching the current user's ID
    // In a real application, this would come from a session or auth service
    private static function getUserId(): ?int
    {
        // Replace this with your actual logic to get the logged-in user's ID
        // For example:
        // return $_SESSION['user_id'] ?? null;
        Session::start();
        
        return Session::get('user_id') ?? null; // For testing purposes, we'll use a static ID
    }

    private static function logChange(string $tableName, int $recordId, string $actionType, ?array $oldData = null, ?array $newData = null): void
    {
        if (!static::$auditable) {
            return;
        }
        
        $sql = "INSERT INTO `audit_logs` (`table_name`, `record_id`, `action_type`, `old_data`, `new_data`, `changed_by`) VALUES (?, ?, ?, ?, ?, ?)";
        
        $oldJson = $oldData ? json_encode($oldData) : null;
        $newJson = $newData ? json_encode($newData) : null;

        $params = [
            $tableName,
            $recordId,
            $actionType,
            $oldJson,
            $newJson,
            self::getUserId()
        ];
        
        self::execute($sql, $params);
    }

    public static function find(mixed $id){
        $sql = "SELECT * FROM `" . static::$table . "` WHERE `" . static::$primaryKey . "` = ?";
        return self::fetch($sql, [$id]);
    }

    public static function all(): array
    {
        $sql = "SELECT * FROM `" . static::$table . "`";
        return self::fetchAll($sql);
    }

    public static function where(string $column, mixed $value, string $operator = "=")
    {
        $sql = "SELECT * FROM `" . static::$table . "` WHERE `{$column}` {$operator} ?";
        return self::fetch($sql, [$value]);
    }


    public static function whereAll(string $column, mixed $value): array
    {
        $sql = "SELECT * FROM `" . static::$table . "` WHERE `{$column}` = ?";
        return self::fetchAll($sql, [$value]);
    }

    public static function create(array $data): array|false
    {
        $columns = implode("`, `", array_keys($data));
        $placeholders = implode(", ", array_fill(0, count($data), '?'));
        $sql = "INSERT INTO `" . static::$table . "` (`{$columns}`) VALUES ({$placeholders})";

        $success = self::execute($sql, array_values($data));

        if ($success) {
            // Get the last inserted ID
            $lastId = self::lastInsertId();

            // Fetch the full record from the database using your find() method
            $record = self::find($lastId);

            // Log the creation
            self::logChange(static::$table, $lastId, 'INSERT', null, $record);

            return $record ?: $data; // Fallback to input data if fetch fails
        }

        return false;
    }

    /**
     * Executes a raw INSERT query and returns the last inserted ID.
     * Useful for controllers that need the ID immediately without fetching the full record.
     * * @param string $sql The raw SQL query
     * @param array $params The parameters to bind
     * @return int|false The last inserted ID or false on failure
     */
    public static function insert(string $sql, array $params): int|false
    {
        if (self::execute($sql, $params)) {
            return self::lastInsertId();
        }
        return false;
    }


    public static function update(mixed $id, array $data): int
    {
        // 1. Fetch the old data before the update
        $oldRecord = self::find($id);
        
        // 2. Perform the update
        $setClauses = implode(" = ?, ", array_keys($data)) . " = ?";
        $sql = "UPDATE `" . static::$table . "` SET {$setClauses} WHERE `" . static::$primaryKey . "` = ?";
        
        $params = array_values($data);
        $params[] = $id;
        
        $rowsAffected = self::execute($sql, $params);

        // 3. Log the change
        if ($rowsAffected > 0 && $oldRecord) {
            // Merge old and new data to get a full picture of the record
            $newData = array_merge($oldRecord, $data);
            self::logChange(static::$table, $id, 'UPDATE', $oldRecord, $newData);
        }
        
        return $rowsAffected;
    }

    public static function delete(mixed $id): int
    {
        // 1. Fetch the data before deleting it
        $oldRecord = self::find($id);

        // 2. Perform the deletion
        $sql = "DELETE FROM `" . static::$table . "` WHERE `" . static::$primaryKey . "` = ?";
        $rowsAffected = self::execute($sql, [$id]);
        
        // 3. Log the deletion
        if ($rowsAffected > 0 && $oldRecord) {
            self::logChange(static::$table, $id, 'DELETE', $oldRecord, null);
        }

        return $rowsAffected;
    }

    public static function lastInsertId(): int
    {
        $pdoInstance = self::getInstance();
        if (!$pdoInstance) {
            throw new Exception("Database connection not initialized. Call Core\Database\Connection::init() first.");
        }
        return (int)$pdoInstance->lastInsertId();
    }

    public static function count(string $column = '*', mixed $value = null): int
    {
        if ($value !== null && $column !== '*') {
            $sql = "SELECT COUNT(*) as total FROM `" . static::$table . "` WHERE `{$column}` = ?";
            $result = self::fetch($sql, [$value]);
        } else {
            $sql = "SELECT COUNT(*) as total FROM `" . static::$table . "`";
            $result = self::fetch($sql);
        }

        return (int)($result['total'] ?? 0);
    }

    public static function sum(string $column, string $whereColumn = null, mixed $value = null): float
    {
        $sql = "SELECT SUM(`{$column}`) as total FROM `" . static::$table . "`";

        $params = [];
        if ($whereColumn !== null && $value !== null) {
            $sql .= " WHERE `{$whereColumn}` = ?";
            $params[] = $value;
        }

        $result = self::fetch($sql, $params);
        return (float)($result['total'] ?? 0);
    }

    public static function countWhere(string ...$args): int
    {
        // Must be even number of arguments (column, value pairs)
        if (count($args) % 2 !== 0) {
            throw new \InvalidArgumentException("Arguments must be in pairs: column, value");
        }

        // If exactly 1 pair, use old behavior
        if (count($args) === 2) {
            $sql = "SELECT COUNT(*) as total FROM `" . static::$table . "` WHERE `{$args[0]}` = ?";
            $result = self::fetch($sql, [$args[1]]);
            return (int)($result['total'] ?? 0);
        }

        // If multiple pairs, build dynamic WHERE
        $conditions = [];
        $values = [];
        for ($i = 0; $i < count($args); $i += 2) {
            $conditions[] = "`{$args[$i]}` = ?";
            $values[] = $args[$i + 1];
        }

        $sql = "SELECT COUNT(*) as total FROM `" . static::$table . "` WHERE " . implode(" AND ", $conditions);
        $result = self::fetch($sql, $values);

        return (int)($result['total'] ?? 0);
    }


    public static function getById(int $id, ?string $column = null): object|string|array|null
    {
        // Use the find() method to get the record
        $record = self::find($id);

        if (!$record) {
            return null;
        }

        // If a specific column is requested
        if ($column !== null) {
            return array_key_exists($column, $record) ? $record[$column] : null;
        }

        // Otherwise return the full record (array)
        return $record;
    }

}