<?php

declare(strict_types=1);

namespace App\Core;

use App\Helpers\Paginator;
use PDO;

/**
 * BaseModel
 *
 * Every future model (LeadModel, CustomerModel, CompanyModel, ...)
 * extends this. It is intentionally NOT a full ORM — no lazy-loaded
 * relationships, no query-builder magic — because a CRM this data-heavy
 * benefits far more from explicit, readable SQL in each model's own
 * methods than from an abstraction that hides what's actually being
 * queried. What BaseModel DOES give every model for free:
 *
 *   - PDO access via Database::connection()
 *   - find/all/paginate/insert/update/delete on the model's own table
 *   - automatic created_at/updated_at handling
 *   - automatic soft-delete handling (deleted_at) when the table has it
 *   - automatic created_by/updated_by stamping from the current user
 *   - mass-assignment protection via $fillable
 *
 * Concrete models only need to declare $table, $fillable, and whether
 * $softDeletes / $timestamps apply — then add their own domain-specific
 * query methods (e.g. LeadModel::findByStage()).
 */
abstract class BaseModel
{
    protected string $table;

    /** Columns that insert()/update() are allowed to write. Never trust raw request input beyond this list. */
    protected array $fillable = [];

    protected bool $timestamps = true;
    protected bool $softDeletes = false;
    protected bool $auditStamps = true;

    protected PDO $db;

    public function __construct()
    {
        $this->db = Database::connection();
    }

    public function find(int|string $id): ?array
    {
        $sql = "SELECT * FROM `{$this->table}` WHERE id = :id";
        $sql .= $this->softDeletes ? ' AND deleted_at IS NULL' : '';

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

        $row = $stmt->fetch();

        return $row === false ? null : $row;
    }

    public function findOrFail(int|string $id): array
    {
        $row = $this->find($id);

        if ($row === null) {
            throw new \RuntimeException("Record not found in `{$this->table}` for id {$id}.");
        }

        return $row;
    }

    /** @param array<string,mixed> $where Simple column => value equality filters (AND-ed together). */
    public function all(array $where = [], string $orderBy = 'id DESC', ?int $limit = null): array
    {
        [$sql, $bindings] = $this->buildSelect($where, $orderBy, $limit);

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

        return $stmt->fetchAll();
    }

    public function paginate(array $where, Paginator $paginator, string $orderBy = 'id DESC'): array
    {
        $countSql = "SELECT COUNT(*) FROM `{$this->table}`" . $this->whereClause($where)['sql'];
        $countStmt = $this->db->prepare($countSql);
        $countStmt->execute($this->whereClause($where)['bindings']);
        $paginator->setTotal((int) $countStmt->fetchColumn());

        [$sql, $bindings] = $this->buildSelect($where, $orderBy);
        $sql .= ' LIMIT :limit OFFSET :offset';

        $stmt = $this->db->prepare($sql);
        foreach ($bindings as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->bindValue('limit', $paginator->perPage(), PDO::PARAM_INT);
        $stmt->bindValue('offset', $paginator->offset(), PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function insert(array $data, ?int $actingUserId = null): int
    {
        $data = $this->onlyFillable($data);

        if ($this->timestamps) {
            $data['created_at'] = now();
            $data['updated_at'] = now();
        }

        if ($this->auditStamps && $actingUserId !== null) {
            $data['created_by'] = $actingUserId;
            $data['updated_by'] = $actingUserId;
        }

        $columns = array_keys($data);
        $placeholders = array_map(fn ($c) => ":{$c}", $columns);

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

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

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

    public function update(int|string $id, array $data, ?int $actingUserId = null): bool
    {
        $data = $this->onlyFillable($data);

        if ($data === []) {
            return true; // nothing to update is not an error
        }

        if ($this->timestamps) {
            $data['updated_at'] = now();
        }

        if ($this->auditStamps && $actingUserId !== null) {
            $data['updated_by'] = $actingUserId;
        }

        $assignments = implode(', ', array_map(fn ($c) => "`{$c}` = :{$c}", array_keys($data)));
        $sql = "UPDATE `{$this->table}` SET {$assignments} WHERE id = :id";
        $data['id'] = $id;

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

        return $stmt->execute($data);
    }

    /** Soft-deletes when the table supports it, otherwise a real DELETE. */
    public function delete(int|string $id, ?int $actingUserId = null): bool
    {
        if ($this->softDeletes) {
            $sql = "UPDATE `{$this->table}` SET deleted_at = :now" .
                ($this->auditStamps && $actingUserId !== null ? ', updated_by = :uid' : '') .
                ' WHERE id = :id';

            $bindings = ['now' => now(), 'id' => $id];
            if ($this->auditStamps && $actingUserId !== null) {
                $bindings['uid'] = $actingUserId;
            }

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

            return $stmt->execute($bindings);
        }

        $stmt = $this->db->prepare("DELETE FROM `{$this->table}` WHERE id = :id");

        return $stmt->execute(['id' => $id]);
    }

    public function restore(int|string $id): bool
    {
        if (!$this->softDeletes) {
            return false;
        }

        $stmt = $this->db->prepare("UPDATE `{$this->table}` SET deleted_at = NULL WHERE id = :id");

        return $stmt->execute(['id' => $id]);
    }

    public function count(array $where = []): int
    {
        $clause = $this->whereClause($where);
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM `{$this->table}`" . $clause['sql']);
        $stmt->execute($clause['bindings']);

        return (int) $stmt->fetchColumn();
    }

    protected function onlyFillable(array $data): array
    {
        if ($this->fillable === []) {
            return $data;
        }

        return array_intersect_key($data, array_flip($this->fillable));
    }

    private function buildSelect(array $where, string $orderBy, ?int $limit = null): array
    {
        $clause = $this->whereClause($where);
        $sql = "SELECT * FROM `{$this->table}`" . $clause['sql'] . " ORDER BY {$orderBy}";

        if ($limit !== null) {
            $sql .= " LIMIT {$limit}";
        }

        return [$sql, $clause['bindings']];
    }

    /** @return array{sql:string, bindings:array} */
    private function whereClause(array $where): array
    {
        $conditions = [];
        $bindings = [];

        foreach ($where as $column => $value) {
            $param = 'w_' . preg_replace('/[^a-zA-Z0-9_]/', '', $column);
            $conditions[] = "`{$column}` = :{$param}";
            $bindings[$param] = $value;
        }

        if ($this->softDeletes) {
            $conditions[] = 'deleted_at IS NULL';
        }

        return [
            'sql' => $conditions === [] ? '' : ' WHERE ' . implode(' AND ', $conditions),
            'bindings' => $bindings,
        ];
    }
}
