<?php
class Gallery extends Model
{
    public function createTableIfNotExists(): void
    {
        $sql = "CREATE TABLE IF NOT EXISTS cms_gallery (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(150) NOT NULL,
            category VARCHAR(100) NULL,
            description TEXT NULL,
            image_path VARCHAR(255) NOT NULL,
            sort_order INT DEFAULT 0,
            status ENUM('draft','published') DEFAULT 'published',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        $this->execute($sql);
    }

    public function all(): array
    {
        return $this->fetchAll('SELECT * FROM cms_gallery ORDER BY sort_order ASC, created_at DESC');
    }

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

    public function create(array $data): ?string
    {
        $sql = 'INSERT INTO cms_gallery (title, category, description, image_path, sort_order, status) 
                VALUES (:title, :category, :description, :image_path, :sort_order, :status)';
        $this->execute($sql, [
            'title' => $data['title'],
            'category' => $data['category'] ?? null,
            'description' => $data['description'] ?? null,
            'image_path' => $data['image_path'],
            'sort_order' => $data['sort_order'] ?? 0,
            'status' => $data['status'] ?? 'published',
        ]);
        return $this->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        $fields = [];
        $params = ['id' => $id];

        $map = [
            'title' => 'title', 'category' => 'category', 'description' => 'description',
            'image_path' => 'image_path', 'sort_order' => 'sort_order', 'status' => 'status',
        ];

        foreach ($map as $field => $column) {
            if (isset($data[$field])) {
                $fields[] = "$column = :$field";
                $params[$field] = $data[$field];
            }
        }

        if (empty($fields)) return false;

        $sql = 'UPDATE cms_gallery SET ' . implode(', ', $fields) . ' WHERE id = :id';
        return $this->execute($sql, $params);
    }

    public function delete(int $id): bool
    {
        return $this->execute('DELETE FROM cms_gallery WHERE id = :id', ['id' => $id]);
    }

    public function published(): array
    {
        return $this->fetchAll('SELECT * FROM cms_gallery WHERE status = "published" ORDER BY sort_order ASC, created_at DESC');
    }

    public function categories(): array
    {
        $rows = $this->fetchAll('SELECT DISTINCT category FROM cms_gallery WHERE category IS NOT NULL AND category != "" ORDER BY category ASC');
        return array_column($rows, 'category');
    }
}
