<?php

namespace App\Core;

/**
 * BaseModel — PDO Query Wrapper
 *
 * All application models extend this class. It provides parameterized
 * query execution and common CRUD helpers built on top of the Database
 * singleton PDO connection.
 *
 * Architecture rules (ADR-024, DBP v1.0 Ch.4):
 *   - All SQL must be written in the Model. Never in a Service or Controller.
 *   - All queries must use parameterized statements (never string concatenation).
 *   - Return arrays only — never PDO objects or stdClass.
 *   - One connection per request (via Database::getConnection()).
 *
 * Each module model declares its own $table name and extends BaseModel.
 *
 * Example usage in a concrete model:
 *   class GuestModel extends BaseModel {
 *       protected string $table = 'guests';
 *
 *       public function findByWorkspace(int $workspaceId): array {
 *           return $this->query(
 *               'SELECT * FROM guests WHERE workspace_id = ? ORDER BY surname',
 *               [$workspaceId]
 *           )->fetchAll();
 *       }
 *   }
 *
 * @package Inveetaire
 * @ref     DBP v1.0 Ch.4, ADR-024, INIT-008
 */
abstract class BaseModel
{
    /**
     * The primary database table for this model.
     * Must be overridden by each concrete model.
     *
     * @var string
     */
    protected string $table = '';

    /**
     * The PDO connection instance.
     * Resolved from Database::getConnection() on first use.
     * @var \PDO|null
     */
    private ?\PDO $db = null;

    // ─────────────────────────────────────────────────────────────────────────
    // CONNECTION
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Return the PDO connection, resolving from the Database singleton.
     * Lazy — does not connect until the first query is made.
     *
     * @return \PDO
     */
    protected function db(): \PDO
    {
        if ($this->db === null) {
            $this->db = Database::getConnection();
        }

        return $this->db;
    }

    // ─────────────────────────────────────────────────────────────────────────
    // CORE QUERY METHOD
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Prepare, bind, and execute a parameterized SQL statement.
     *
     * This is the single point of SQL execution in the application.
     * All queries MUST go through this method — never use raw PDO directly
     * in module models.
     *
     * @param string  $sql    Parameterized SQL (use ? or :name placeholders).
     * @param array<int|string, mixed> $params Positional or named parameter values.
     *
     * @return \PDOStatement  Ready for ->fetch() or ->fetchAll().
     * @throws \PDOException  On query error (re-thrown for ErrorHandler to catch).
     */
    protected function query(string $sql, array $params = []): \PDOStatement
    {
        $stmt = $this->db()->prepare($sql);
        $stmt->execute($params);

        return $stmt;
    }

    // ─────────────────────────────────────────────────────────────────────────
    // COMMON CRUD HELPERS
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Find a single row by its primary key (id).
     *
     * Returns null if no row is found.
     *
     * @param int $id  Primary key value.
     * @return array<string, mixed>|null  Associative row array, or null.
     */
    protected function find(int $id): ?array
    {
        if ($this->table === '') {
            throw new \LogicException(
                static::class . '::$table must be set before calling find().'
            );
        }

        $stmt   = $this->query(
            'SELECT * FROM `' . $this->table . '` WHERE id = ? LIMIT 1',
            [$id]
        );
        $result = $stmt->fetch();

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

    /**
     * Find all rows in the model's table.
     *
     * Optional WHERE clause via an associative array:
     *   ['workspace_id' => 3, 'status' => 'active']
     *   → WHERE workspace_id = ? AND status = ?
     *
     * Optional ORDER BY:
     *   'surname ASC, first_name ASC'
     *
     * All column names in $where are internally sanitized (only a–z, 0–9, _).
     * All values are parameterized — no SQL injection risk.
     *
     * @param array<string, mixed> $where   Column => value equality conditions.
     * @param string               $orderBy ORDER BY expression (not parameterized — do not
     *                                       pass user input here; use column names only).
     * @return array<int, array<string, mixed>>  Array of associative row arrays.
     */
    protected function findAll(array $where = [], string $orderBy = ''): array
    {
        if ($this->table === '') {
            throw new \LogicException(
                static::class . '::$table must be set before calling findAll().'
            );
        }

        $sql    = 'SELECT * FROM `' . $this->table . '`';
        $params = [];

        if (!empty($where)) {
            $clauses = [];
            foreach ($where as $column => $value) {
                // Sanitize column name to prevent injection through array key.
                $safeColumn = preg_replace('/[^a-zA-Z0-9_]/', '', $column);
                $clauses[]  = '`' . $safeColumn . '` = ?';
                $params[]   = $value;
            }
            $sql .= ' WHERE ' . implode(' AND ', $clauses);
        }

        if ($orderBy !== '') {
            // ORDER BY value is NOT parameterized (can't be in PDO).
            // Only pass static column names here — never user input.
            $sql .= ' ORDER BY ' . $orderBy;
        }

        return $this->query($sql, $params)->fetchAll();
    }

    /**
     * Insert a row into the model's table.
     *
     * @param array<string, mixed> $data  Column => value pairs to insert.
     *                                    Columns are sanitized; values parameterized.
     * @return int|null  The new row's auto-increment ID, or null on failure.
     */
    protected function insert(array $data): ?int
    {
        if ($this->table === '') {
            throw new \LogicException(
                static::class . '::$table must be set before calling insert().'
            );
        }

        if (empty($data)) {
            return null;
        }

        $columns = [];
        $params  = [];

        foreach ($data as $column => $value) {
            $safeColumn = preg_replace('/[^a-zA-Z0-9_]/', '', $column);
            $columns[]  = '`' . $safeColumn . '`';
            $params[]   = $value;
        }

        $placeholders = implode(', ', array_fill(0, count($columns), '?'));

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

        $this->query($sql, $params);

        $lastId = $this->db()->lastInsertId();

        return $lastId !== false ? (int) $lastId : null;
    }

    /**
     * Update rows in the model's table matching the given WHERE conditions.
     *
     * @param array<string, mixed> $data   Column => new value pairs.
     * @param array<string, mixed> $where  Column => value equality conditions for WHERE clause.
     *
     * @return int  Number of rows affected.
     */
    protected function update(array $data, array $where): int
    {
        if ($this->table === '') {
            throw new \LogicException(
                static::class . '::$table must be set before calling update().'
            );
        }

        if (empty($data) || empty($where)) {
            return 0;
        }

        $setClauses   = [];
        $whereClauses = [];
        $params       = [];

        foreach ($data as $column => $value) {
            $safeColumn  = preg_replace('/[^a-zA-Z0-9_]/', '', $column);
            $setClauses[] = '`' . $safeColumn . '` = ?';
            $params[]     = $value;
        }

        foreach ($where as $column => $value) {
            $safeColumn     = preg_replace('/[^a-zA-Z0-9_]/', '', $column);
            $whereClauses[] = '`' . $safeColumn . '` = ?';
            $params[]       = $value;
        }

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

        return (int) $this->query($sql, $params)->rowCount();
    }

    /**
     * Count rows in the model's table.
     * Optional WHERE conditions follow the same format as findAll().
     *
     * @param array<string, mixed> $where  Column => value conditions.
     * @return int  Row count.
     */
    protected function count(array $where = []): int
    {
        if ($this->table === '') {
            throw new \LogicException(
                static::class . '::$table must be set before calling count().'
            );
        }

        $sql    = 'SELECT COUNT(*) FROM `' . $this->table . '`';
        $params = [];

        if (!empty($where)) {
            $clauses = [];
            foreach ($where as $column => $value) {
                $safeColumn = preg_replace('/[^a-zA-Z0-9_]/', '', $column);
                $clauses[]  = '`' . $safeColumn . '` = ?';
                $params[]   = $value;
            }
            $sql .= ' WHERE ' . implode(' AND ', $clauses);
        }

        $result = $this->query($sql, $params)->fetchColumn();

        return (int) $result;
    }
}
