<?php
/**
 * Classe Model de base
 * mySupport - Plateforme de Service Client Intelligent
 */

namespace Core;

abstract class Model
{
    protected Database $db;
    protected string $table;
    protected string $primaryKey = 'id';

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

    public function find(int $id): ?array
    {
        $sql = "SELECT * FROM {$this->table} WHERE {$this->primaryKey} = :id LIMIT 1";
        return $this->db->fetch($sql, ['id' => $id]);
    }

    public function findBy(string $column, $value): ?array
    {
        $sql = "SELECT * FROM {$this->table} WHERE {$column} = :value LIMIT 1";
        return $this->db->fetch($sql, ['value' => $value]);
    }

    public function all(string $orderBy = 'id', string $direction = 'DESC'): array
    {
        $sql = "SELECT * FROM {$this->table} ORDER BY {$orderBy} {$direction}";
        return $this->db->fetchAll($sql);
    }

    public function where(array $conditions, string $orderBy = 'id', string $direction = 'DESC'): array
    {
        $where = [];
        foreach (array_keys($conditions) as $column) {
            $where[] = "{$column} = :{$column}";
        }
        $whereClause = implode(' AND ', $where);
        
        $sql = "SELECT * FROM {$this->table} WHERE {$whereClause} ORDER BY {$orderBy} {$direction}";
        return $this->db->fetchAll($sql, $conditions);
    }

    public function create(array $data): int
    {
        $data['created_at'] = date('Y-m-d H:i:s');
        $data['updated_at'] = date('Y-m-d H:i:s');
        return $this->db->insert($this->table, $data);
    }

    public function update(int $id, array $data): int
    {
        $data['updated_at'] = date('Y-m-d H:i:s');
        return $this->db->update(
            $this->table, 
            $data, 
            "{$this->primaryKey} = :where_id", 
            ['where_id' => $id]
        );
    }

    public function delete(int $id): int
    {
        return $this->db->delete($this->table, "{$this->primaryKey} = :id", ['id' => $id]);
    }

    public function count(array $conditions = []): int
    {
        if (empty($conditions)) {
            $sql = "SELECT COUNT(*) as count FROM {$this->table}";
            $result = $this->db->fetch($sql);
        } else {
            $where = [];
            foreach (array_keys($conditions) as $column) {
                $where[] = "{$column} = :{$column}";
            }
            $whereClause = implode(' AND ', $where);
            $sql = "SELECT COUNT(*) as count FROM {$this->table} WHERE {$whereClause}";
            $result = $this->db->fetch($sql, $conditions);
        }
        return (int) ($result['count'] ?? 0);
    }

    public function paginate(int $page, int $perPage = ITEMS_PER_PAGE, array $conditions = []): array
    {
        $offset = ($page - 1) * $perPage;
        $total = $this->count($conditions);
        
        if (empty($conditions)) {
            $sql = "SELECT * FROM {$this->table} ORDER BY {$this->primaryKey} DESC LIMIT :limit OFFSET :offset";
            $params = ['limit' => $perPage, 'offset' => $offset];
        } else {
            $where = [];
            foreach (array_keys($conditions) as $column) {
                $where[] = "{$column} = :{$column}";
            }
            $whereClause = implode(' AND ', $where);
            $sql = "SELECT * FROM {$this->table} WHERE {$whereClause} ORDER BY {$this->primaryKey} DESC LIMIT :limit OFFSET :offset";
            $params = array_merge($conditions, ['limit' => $perPage, 'offset' => $offset]);
        }
        
        $data = $this->db->fetchAll($sql, $params);
        
        return [
            'data' => $data,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'total_pages' => ceil($total / $perPage)
        ];
    }

    public function exists(string $column, $value, ?int $excludeId = null): bool
    {
        $sql = "SELECT COUNT(*) as count FROM {$this->table} WHERE {$column} = :value";
        $params = ['value' => $value];
        
        if ($excludeId !== null) {
            $sql .= " AND {$this->primaryKey} != :exclude_id";
            $params['exclude_id'] = $excludeId;
        }
        
        $result = $this->db->fetch($sql, $params);
        return ($result['count'] ?? 0) > 0;
    }
}
