<?php

declare(strict_types=1);

namespace BotGuard\Core;

/**
 * Minimal active-record-style base model.
 *
 * Provides common CRUD helpers built on the Database wrapper (prepared
 * statements throughout). Subclasses declare their table name and, optionally,
 * a list of fillable columns. This is intentionally lightweight — complex
 * queries are written explicitly in the model that needs them.
 */
abstract class Model
{
    protected string $table = '';

    protected string $primaryKey = 'id';

    public function __construct(protected Database $db)
    {
    }

    public function table(): string
    {
        return $this->table;
    }

    /**
     * @return array<string, mixed>|null
     */
    public function find(int|string $id): ?array
    {
        return $this->db->selectOne(
            "SELECT * FROM `{$this->table}` WHERE `{$this->primaryKey}` = ? LIMIT 1",
            [$id]
        );
    }

    /**
     * Fetch the first row matching an equality map.
     *
     * @param array<string, mixed> $where
     * @return array<string, mixed>|null
     */
    public function firstWhere(array $where): ?array
    {
        [$sql, $bindings] = $this->buildWhere($where);

        return $this->db->selectOne(
            "SELECT * FROM `{$this->table}` {$sql} LIMIT 1",
            $bindings
        );
    }

    /**
     * @param array<string, mixed> $where
     * @return array<int, array<string, mixed>>
     */
    public function where(array $where, string $orderBy = '', int $limit = 0): array
    {
        [$sql, $bindings] = $this->buildWhere($where);
        $order = $orderBy !== '' ? " ORDER BY {$orderBy}" : '';
        $lim = $limit > 0 ? " LIMIT {$limit}" : '';

        return $this->db->select("SELECT * FROM `{$this->table}` {$sql}{$order}{$lim}", $bindings);
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function all(string $orderBy = ''): array
    {
        $order = $orderBy !== '' ? " ORDER BY {$orderBy}" : '';

        return $this->db->select("SELECT * FROM `{$this->table}`{$order}");
    }

    /**
     * @param array<string, mixed> $data
     */
    public function create(array $data): int
    {
        return $this->db->insert($this->table, $data);
    }

    /**
     * @param array<string, mixed> $data
     */
    public function updateById(int|string $id, array $data): int
    {
        return $this->db->update($this->table, $data, [$this->primaryKey => $id]);
    }

    public function deleteById(int|string $id): int
    {
        return $this->db->affectingStatement(
            "DELETE FROM `{$this->table}` WHERE `{$this->primaryKey}` = ?",
            [$id]
        );
    }

    /**
     * @param array<string, mixed> $where
     */
    public function count(array $where = []): int
    {
        [$sql, $bindings] = $this->buildWhere($where);

        return (int) $this->db->scalar("SELECT COUNT(*) FROM `{$this->table}` {$sql}", $bindings);
    }

    /**
     * Build a parameterised WHERE clause from an equality map.
     *
     * @param array<string, mixed> $where
     * @return array{0:string, 1:array<int, mixed>}
     */
    protected function buildWhere(array $where): array
    {
        if ($where === []) {
            return ['', []];
        }

        $clauses = [];
        $bindings = [];

        foreach ($where as $column => $value) {
            $clauses[] = "`{$column}` = ?";
            $bindings[] = $value;
        }

        return ['WHERE ' . implode(' AND ', $clauses), $bindings];
    }
}
