<?php
namespace App\Models;

use PDO;
use PDOException;

/**
 * Class Model
 * Enterprise Active Record / Data Mapper Base Model (PDO Wrapper with Prepared Statements)
 *
 * @package App\Models
 */
abstract class Model
{
    protected static ?PDO $db = null;
    protected string $table;
    protected string $primaryKey = 'id';

    /**
     * Get Singleton PDO Database Connection
     */
    public static function getDB(): PDO
    {
        if (self::$db === null) {
            $connectionFile = __DIR__ . '/../../config/connection.php';
            if (file_exists($connectionFile)) {
                $pdo = require $connectionFile;
                if ($pdo instanceof PDO) {
                    self::$db = $pdo;
                    return self::$db;
                }
            }
            throw new PDOException("ไม่พบไฟล์เชื่อมต่อฐานข้อมูล config/connection.php หรือไฟล์ไม่ได้ส่งคืน Object PDO");
        }
        return self::$db;
    }

    /**
     * Find single record by Primary Key
     */
    public function find(int $id): ?array
    {
        $sql = "SELECT * FROM `{$this->table}` WHERE `{$this->primaryKey}` = :id LIMIT 1";
        $stmt = self::getDB()->prepare($sql);
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    /**
     * Get all records with optional limit and order
     */
    public function all(int $limit = 100, string $orderBy = 'id DESC'): array
    {
        $sql = "SELECT * FROM `{$this->table}` ORDER BY {$orderBy} LIMIT :limit";
        $stmt = self::getDB()->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * Find records by condition (e.g. ['branch_id' => 1, 'status' => 'Waiting'])
     */
    public function where(array $conditions, string $orderBy = 'id DESC', int $limit = 50): array
    {
        $clauses = [];
        $params = [];
        foreach ($conditions as $col => $val) {
            $clauses[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }
        $whereSql = implode(' AND ', $clauses);
        $sql = "SELECT * FROM `{$this->table}` WHERE {$whereSql} ORDER BY {$orderBy} LIMIT {$limit}";
        $stmt = self::getDB()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * Find single record by condition
     */
    public function firstWhere(array $conditions): ?array
    {
        $rows = $this->where($conditions, 'id ASC', 1);
        return $rows[0] ?? null;
    }

    /**
     * Insert new record and return Insert ID
     */
    public function create(array $data): int
    {
        $cols = array_keys($data);
        $fields = implode(', ', array_map(fn($c) => "`{$c}`", $cols));
        $placeholders = implode(', ', array_map(fn($c) => ":{$c}", $cols));

        $sql = "INSERT INTO `{$this->table}` ({$fields}) VALUES ({$placeholders})";
        $stmt = self::getDB()->prepare($sql);
        $stmt->execute($data);

        return (int)self::getDB()->lastInsertId();
    }

    /**
     * Update record by Primary Key
     */
    public function update(int $id, array $data): bool
    {
        $sets = [];
        foreach (array_keys($data) as $col) {
            $sets[] = "`{$col}` = :{$col}";
        }
        $setSql = implode(', ', $sets);
        $data['id'] = $id;

        $sql = "UPDATE `{$this->table}` SET {$setSql} WHERE `{$this->primaryKey}` = :id";
        $stmt = self::getDB()->prepare($sql);
        return $stmt->execute($data);
    }

    /**
     * Delete record by Primary Key
     */
    public function delete(int $id): bool
    {
        $sql = "DELETE FROM `{$this->table}` WHERE `{$this->primaryKey}` = :id";
        $stmt = self::getDB()->prepare($sql);
        return $stmt->execute(['id' => $id]);
    }
}