<?php
class CmsSlide extends Model
{
    public function createTableIfNotExists(): void
    {
        $sql = "CREATE TABLE IF NOT EXISTS cms_slides (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(150) NOT NULL,
            subtitle VARCHAR(255) NULL,
            image_path VARCHAR(255) NOT NULL,
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            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_slides', 'sort_order', 'INT DEFAULT 0 AFTER image_path');
        $this->addColumnIfNotExists('cms_slides', 'active', 'BOOLEAN DEFAULT TRUE AFTER sort_order');
    }

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

    public function activeAll(): array
    {
        return $this->fetchAll('SELECT * FROM cms_slides WHERE active = 1 ORDER BY sort_order ASC, id ASC');
    }

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

    public function create(array $data): ?string
    {
        $sql = 'INSERT INTO cms_slides (title, subtitle, image_path, sort_order, active, created_at) VALUES (:title, :subtitle, :image_path, :sort_order, :active, NOW())';
        $this->execute($sql, [
            'title' => $data['title'],
            'subtitle' => $data['subtitle'] ?? null,
            'image_path' => $data['image_path'],
            'sort_order' => (int)($data['sort_order'] ?? 0),
            'active' => !empty($data['active']) ? 1 : 0,
        ]);
        return $this->lastInsertId();
    }

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

        $map = [
            'title' => 'title', 'subtitle' => 'subtitle', 'image_path' => 'image_path',
            'sort_order' => 'sort_order', 'active' => 'active',
        ];

        foreach ($map as $field => $column) {
            if (array_key_exists($field, $data)) {
                if ($field === 'active') {
                    $fields[] = "$column = :$field";
                    $params[$field] = !empty($data[$field]) ? 1 : 0;
                } else {
                    $fields[] = "$column = :$field";
                    $params[$field] = $data[$field];
                }
            }
        }

        if (empty($fields)) return false;

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

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