<?php
namespace Core;

class Model {
    protected \PDO $db;
    protected $table = '';
    protected $primaryKey = 'id';
    
    public function __construct() {
        $this->db = \Database::getInstance();
    }
    
    public function find(int $id): ?array {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} WHERE {$this->primaryKey} = ?");
        $stmt->execute([$id]);
        $result = $stmt->fetch();
        return $result ?: null;
    }
    
    public function findAll(array $conditions = [], string $orderBy = 'created_at DESC', int $limit = 0, int $offset = 0): array {
        [$whereSql, $params] = $this->buildWhereClause($conditions);
        $sql = "SELECT * FROM {$this->table} $whereSql";
        if ($orderBy) {
            $sql .= " ORDER BY $orderBy";
        }
        if ($limit > 0) {
            $sql .= " LIMIT " . (int)$limit;
            if ($offset > 0) {
                $sql .= " OFFSET " . (int)$offset;
            }
        }
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    public function findBy(string $column, mixed $value): ?array {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} WHERE $column = ? LIMIT 1");
        $stmt->execute([$value]);
        $result = $stmt->fetch();
        return $result ?: null;
    }
    
    public function findWhere(array $conditions): array {
        return $this->findAll($conditions);
    }
    
    public function create(array $data): int|false {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));
        $sql = "INSERT INTO {$this->table} ($columns) VALUES ($placeholders)";
        $stmt = $this->db->prepare($sql);
        if ($stmt->execute(array_values($data))) {
            return (int)$this->db->lastInsertId();
        }
        return false;
    }
    
    public function update(int $id, array $data): bool {
        if (empty($data)) return true;
        $set = [];
        foreach (array_keys($data) as $key) {
            $set[] = "$key = ?";
        }
        $setStr = implode(', ', $set);
        $sql = "UPDATE {$this->table} SET $setStr WHERE {$this->primaryKey} = ?";
        $params = array_values($data);
        $params[] = $id;
        $stmt = $this->db->prepare($sql);
        return $stmt->execute($params);
    }
    
    public function delete(int $id): bool {
        $stmt = $this->db->prepare("DELETE FROM {$this->table} WHERE {$this->primaryKey} = ?");
        return $stmt->execute([$id]);
    }
    
    public function count(array $conditions = []): int {
        [$whereSql, $params] = $this->buildWhereClause($conditions);
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM {$this->table} $whereSql");
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }
    
    public function query(string $sql, array $params = []): array {
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    public function queryOne(string $sql, array $params = []): ?array {
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch();
        return $result ?: null;
    }
    
    public function execute(string $sql, array $params = []): bool {
        $stmt = $this->db->prepare($sql);
        return $stmt->execute($params);
    }
    
    public function paginate(array $conditions = [], int $page = 1, int $perPage = 20, string $orderBy = 'created_at DESC'): array {
        $total = $this->count($conditions);
        $pages = (int)ceil($total / $perPage);
        $offset = ($page - 1) * $perPage;
        
        $data = $this->findAll($conditions, $orderBy, $perPage, $offset);
        
        return [
            'data' => $data,
            'total' => $total,
            'pages' => $pages,
            'current' => $page,
            'perPage' => $perPage
        ];
    }
    
    public function beginTransaction(): void {
        $this->db->beginTransaction();
    }
    
    public function commit(): void {
        $this->db->commit();
    }
    
    public function rollback(): void {
        $this->db->rollBack();
    }
    
    protected function buildWhereClause(array $conditions): array {
        if (empty($conditions)) {
            return ['', []];
        }
        $clauses = [];
        $params = [];
        foreach ($conditions as $column => $value) {
            if (is_array($value)) {
                $in = str_repeat('?,', count($value) - 1) . '?';
                $clauses[] = "$column IN ($in)";
                $params = array_merge($params, $value);
            } else {
                $clauses[] = "$column = ?";
                $params[] = $value;
            }
        }
        return ['WHERE ' . implode(' AND ', $clauses), $params];
    }
}
