<?php

declare(strict_types=1);

namespace App\Core;

use PDO;
use RuntimeException;

/**
 * Abstract Base Model
 *
 * Provides a simple active-record-like interface for database operations.
 * All queries use prepared statements to prevent SQL injection.
 */
abstract class Model
{
    /**
     * Database connection instance
     */
    protected PDO $db;

    /**
     * The database table associated with the model
     */
    protected string $table;

    /**
     * The primary key column for the table
     */
    protected string $primaryKey = 'id';

    /**
     * Constructor — receives the PDO connection.
     *
     * @param PDO $db Active PDO connection
     * @throws RuntimeException If the $table property is not set
     */
    public function __construct(PDO $db)
    {
        $this->db = $db;

        if (empty($this->table)) {
            throw new RuntimeException(
                static::class . ' must define a $table property.'
            );
        }
    }

    /**
     * Find a single record by its primary key.
     *
     * @param int $id The primary key value
     * @return array<string, mixed>|null The record as an associative array, or null if not found
     */
    public function find(int $id): ?array
    {
        $sql = sprintf(
            'SELECT * FROM `%s` WHERE `%s` = :id LIMIT 1',
            $this->table,
            $this->primaryKey
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':id' => $id]);

        $result = $stmt->fetch();

        return $result !== false ? $result : null;
    }

    /**
     * Retrieve all records from the table.
     *
     * @return array<int, array<string, mixed>> Array of all records
     */
    public function all(): array
    {
        $sql = sprintf('SELECT * FROM `%s`', $this->table);

        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /**
     * Find all records where a column matches a value.
     *
     * @param string $column The column name to filter on
     * @param mixed $value The value to match
     * @return array<int, array<string, mixed>> Array of matching records
     */
    public function where(string $column, mixed $value): array
    {
        $sql = sprintf(
            'SELECT * FROM `%s` WHERE `%s` = :value',
            $this->table,
            $column
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':value' => $value]);

        return $stmt->fetchAll();
    }

    /**
     * Find the first record where a column matches a value.
     *
     * @param string $column The column name to filter on
     * @param mixed $value The value to match
     * @return array<string, mixed>|null The first matching record, or null
     */
    public function first(string $column, mixed $value): ?array
    {
        $sql = sprintf(
            'SELECT * FROM `%s` WHERE `%s` = :value LIMIT 1',
            $this->table,
            $column
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':value' => $value]);

        $result = $stmt->fetch();

        return $result !== false ? $result : null;
    }

    /**
     * Insert a new record and return the last insert ID.
     *
     * Builds an INSERT statement from the provided data array,
     * where keys are column names and values are the data to insert.
     *
     * @param array<string, mixed> $data Column-value pairs to insert
     * @return int The last inserted ID
     */
    public function create(array $data): int
    {
        $columns = array_keys($data);
        $placeholders = array_map(fn(string $col) => ':' . $col, $columns);

        $sql = sprintf(
            'INSERT INTO `%s` (`%s`) VALUES (%s)',
            $this->table,
            implode('`, `', $columns),
            implode(', ', $placeholders)
        );

        $params = [];
        foreach ($data as $key => $value) {
            $params[':' . $key] = $value;
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);

        return (int) $this->db->lastInsertId();
    }

    /**
     * Update an existing record by primary key.
     *
     * Builds an UPDATE statement from the provided data array.
     *
     * @param int $id The primary key of the record to update
     * @param array<string, mixed> $data Column-value pairs to update
     * @return bool True if the update affected at least one row
     */
    public function update(int $id, array $data): bool
    {
        $setClauses = [];
        $params = [];

        foreach ($data as $key => $value) {
            $setClauses[] = sprintf('`%s` = :set_%s', $key, $key);
            $params[':set_' . $key] = $value;
        }

        $params[':id'] = $id;

        $sql = sprintf(
            'UPDATE `%s` SET %s WHERE `%s` = :id',
            $this->table,
            implode(', ', $setClauses),
            $this->primaryKey
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);

        return $stmt->rowCount() > 0;
    }

    /**
     * Delete a record by its primary key.
     *
     * @param int $id The primary key of the record to delete
     * @return bool True if the delete affected at least one row
     */
    public function delete(int $id): bool
    {
        $sql = sprintf(
            'DELETE FROM `%s` WHERE `%s` = :id',
            $this->table,
            $this->primaryKey
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':id' => $id]);

        return $stmt->rowCount() > 0;
    }

    /**
     * Count the total number of records in the table.
     *
     * @return int The total count of records
     */
    public function count(): int
    {
        $sql = sprintf('SELECT COUNT(*) AS total FROM `%s`', $this->table);

        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        $result = $stmt->fetch();

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