<?php
class Blog extends Model
{
    public function createTableIfNotExists(): void
    {
        $sql = "CREATE TABLE IF NOT EXISTS cms_blog_posts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            excerpt TEXT NULL,
            content TEXT NULL,
            image_path VARCHAR(255) NULL,
            slug VARCHAR(255) NOT NULL UNIQUE,
            published_at DATETIME NULL,
            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);
        $this->addColumnIfNotExists('cms_blog_posts', 'status', "ENUM('draft','published') DEFAULT 'published' AFTER published_at");
    }

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

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

    public function findBySlug(string $slug): ?array
    {
        return $this->fetch('SELECT * FROM cms_blog_posts WHERE slug = :slug LIMIT 1', ['slug' => $slug]);
    }

    public function create(array $data): ?string
    {
        $sql = 'INSERT INTO cms_blog_posts (title, excerpt, content, image_path, slug, published_at, status) 
                VALUES (:title, :excerpt, :content, :image_path, :slug, :published_at, :status)';
        $this->execute($sql, [
            'title' => $data['title'],
            'excerpt' => $data['excerpt'] ?? null,
            'content' => $data['content'] ?? null,
            'image_path' => $data['image_path'] ?? null,
            'slug' => $data['slug'],
            'published_at' => $data['published_at'] ?? null,
            'status' => $data['status'] ?? 'published',
        ]);
        return $this->lastInsertId();
    }

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

        $map = [
            'title' => 'title', 'excerpt' => 'excerpt', 'content' => 'content',
            'image_path' => 'image_path', 'slug' => 'slug', 'published_at' => 'published_at', '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_blog_posts SET ' . implode(', ', $fields) . ' WHERE id = :id';
        return $this->execute($sql, $params);
    }

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

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