<?php
namespace App\Core;

use App\Config\Database;
use PDO;

abstract class Model {
    protected static string $table = '';

    protected static function db(): PDO {
        return Database::getConnection();
    }

    public static function all(string $orderBy = 'id DESC'): array {
        $stmt = self::db()->query("SELECT * FROM " . static::$table . " ORDER BY {$orderBy}");
        return $stmt->fetchAll();
    }

    public static function find(int $id): ?array {
        $stmt = self::db()->prepare("SELECT * FROM " . static::$table . " WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $res = $stmt->fetch();
        return $res ?: null;
    }

    public static function where(string $column, mixed $value, string $orderBy = 'id DESC'): array {
        $stmt = self::db()->prepare("SELECT * FROM " . static::$table . " WHERE `{$column}` = ? ORDER BY {$orderBy}");
        $stmt->execute([$value]);
        return $stmt->fetchAll();
    }

    public static function firstWhere(string $column, mixed $value): ?array {
        $stmt = self::db()->prepare("SELECT * FROM " . static::$table . " WHERE `{$column}` = ? LIMIT 1");
        $stmt->execute([$value]);
        $res = $stmt->fetch();
        return $res ?: null;
    }

    public static function create(array $data): int {
        $columns = array_keys($data);
        $fields = implode('`, `', $columns);
        $placeholders = implode(', ', array_fill(0, count($columns), '?'));

        $sql = "INSERT INTO " . static::$table . " (`{$fields}`) VALUES ({$placeholders})";
        $stmt = self::db()->prepare($sql);
        $stmt->execute(array_values($data));
        return (int)self::db()->lastInsertId();
    }

    public static function update(int $id, array $data): bool {
        $setClauses = [];
        $values = [];
        foreach ($data as $col => $val) {
            $setClauses[] = "`{$col}` = ?";
            $values[] = $val;
        }
        $values[] = $id;

        $sql = "UPDATE " . static::$table . " SET " . implode(', ', $setClauses) . " WHERE id = ?";
        $stmt = self::db()->prepare($sql);
        return $stmt->execute($values);
    }

    public static function delete(int $id): bool {
        $stmt = self::db()->prepare("DELETE FROM " . static::$table . " WHERE id = ?");
        return $stmt->execute([$id]);
    }

    public static function count(string $whereClause = '', array $params = []): int {
        $sql = "SELECT COUNT(*) as total FROM " . static::$table;
        if (!empty($whereClause)) {
            $sql .= " WHERE " . $whereClause;
        }
        $stmt = self::db()->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch();
        return (int)($row['total'] ?? 0);
    }
}
