<?php
class Testimonial extends Model
{
    public function createTableIfNotExists(): void
    {
        $sql = "CREATE TABLE IF NOT EXISTS cms_testimonials (
            id INT AUTO_INCREMENT PRIMARY KEY,
            client_name VARCHAR(120) NOT NULL,
            position VARCHAR(100) NULL,
            company VARCHAR(150) NULL,
            message TEXT NOT NULL,
            photo_path VARCHAR(255) NULL,
            rating INT DEFAULT 5,
            status ENUM('draft','published') DEFAULT 'published',
            sort_order INT DEFAULT 0,
            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_testimonials', 'rating', 'INT DEFAULT 5 AFTER photo_path');
        $this->addColumnIfNotExists('cms_testimonials', 'status', "ENUM('draft','published') DEFAULT 'published' AFTER rating");
        $this->addColumnIfNotExists('cms_testimonials', 'sort_order', 'INT DEFAULT 0 AFTER status');
    }

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

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

    public function create(array $data): bool
    {
        $sql = 'INSERT INTO cms_testimonials (client_name, position, company, message, photo_path, rating, status, sort_order) 
                VALUES (:client_name, :position, :company, :message, :photo_path, :rating, :status, :sort_order)';
        return $this->execute($sql, [
            'client_name' => $data['client_name'],
            'position' => $data['position'] ?? null,
            'company' => $data['company'] ?? null,
            'message' => $data['message'],
            'photo_path' => $data['photo_path'] ?? null,
            'rating' => $data['rating'] ?? 5,
            'status' => $data['status'] ?? 'published',
            'sort_order' => $data['sort_order'] ?? 0,
        ]);
    }

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

        $map = [
            'client_name' => 'client_name', 'position' => 'position', 'company' => 'company',
            'message' => 'message', 'photo_path' => 'photo_path', 'rating' => 'rating',
            'status' => 'status', 'sort_order' => 'sort_order',
        ];

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

        if (empty($fields)) return false;

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

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

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