<?php

namespace App\Core;

/**
 * ITSWEBER CMS – Base Model
 *
 * Simple Active-Record-style base class for database models.
 * Each model subclass defines its table name.
 */
abstract class Model
{
    protected static string $table = '';
    protected static string $primaryKey = 'id';

    protected static function db(): Database
    {
        return App::getInstance()->db();
    }

    /**
     * Find a record by primary key.
     */
    public static function find(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT * FROM " . static::$table . " WHERE " . static::$primaryKey . " = :id LIMIT 1",
            ['id' => $id]
        );
    }

    /**
     * Sanitise an ORDER BY column name to prevent SQL injection.
     * Only allows alphanumeric characters and underscores.
     */
    protected static function sanitizeOrderBy(string $column): string
    {
        if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $column)) {
            return static::$primaryKey;
        }
        return $column;
    }

    /**
     * Get all records, optionally ordered.
     */
    public static function all(string $orderBy = 'id', string $direction = 'ASC'): array
    {
        $orderBy   = static::sanitizeOrderBy($orderBy);
        $direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';
        return static::db()->fetchAll(
            "SELECT * FROM " . static::$table . " ORDER BY {$orderBy} {$direction}"
        );
    }

    /**
     * Find records matching conditions.
     */
    public static function where(array $conditions, string $orderBy = 'id', string $direction = 'ASC'): array
    {
        $orderBy   = static::sanitizeOrderBy($orderBy);
        $direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';
        $whereParts = array_map(fn($k) => "{$k} = :{$k}", array_keys($conditions));
        $sql = "SELECT * FROM " . static::$table
             . " WHERE " . implode(' AND ', $whereParts)
             . " ORDER BY {$orderBy} {$direction}";
        return static::db()->fetchAll($sql, $conditions);
    }

    /**
     * Find a single record matching conditions.
     */
    public static function findWhere(array $conditions): ?array
    {
        $whereParts = array_map(fn($k) => "{$k} = :{$k}", array_keys($conditions));
        $sql = "SELECT * FROM " . static::$table
             . " WHERE " . implode(' AND ', $whereParts)
             . " LIMIT 1";
        return static::db()->fetch($sql, $conditions);
    }

    /**
     * Create a new record.
     */
    public static function create(array $data): int
    {
        return static::db()->insert(static::$table, $data);
    }

    /**
     * Update a record by primary key.
     */
    public static function updateById(int $id, array $data): int
    {
        return static::db()->update(static::$table, $data, [static::$primaryKey => $id]);
    }

    /**
     * Delete a record by primary key.
     */
    public static function deleteById(int $id): int
    {
        return static::db()->delete(static::$table, [static::$primaryKey => $id]);
    }

    /**
     * Count all records, optionally with conditions.
     */
    public static function count(array $conditions = []): int
    {
        if (empty($conditions)) {
            return (int) static::db()->fetchColumn("SELECT COUNT(*) FROM " . static::$table);
        }
        $whereParts = array_map(fn($k) => "{$k} = :{$k}", array_keys($conditions));
        $sql = "SELECT COUNT(*) FROM " . static::$table . " WHERE " . implode(' AND ', $whereParts);
        return (int) static::db()->fetchColumn($sql, $conditions);
    }

    /**
     * Find by slug.
     */
    public static function findBySlug(string $slug): ?array
    {
        return static::db()->fetch(
            "SELECT * FROM " . static::$table . " WHERE slug = :slug LIMIT 1",
            ['slug' => $slug]
        );
    }
}
