<?php

require_once BASE_PATH . '/config/database.php';

class ServiceCategory
{
    private PDO $db;

    public function __construct()
    {
        $this->db = (new Database())->connect();
    }

    public function getActive(): array
    {
        return $this->db->query(
            'SELECT id, name, slug, description, sort_order
             FROM service_categories
             WHERE is_active = 1
             ORDER BY sort_order ASC, name ASC'
        )->fetchAll();
    }

    public function getForOwner(): array
    {
        return $this->db->query(
            'SELECT sc.*, COUNT(s.id) AS service_count
             FROM service_categories sc
             LEFT JOIN services s ON s.category_id = sc.id
             GROUP BY sc.id, sc.name, sc.slug, sc.description,
                      sc.sort_order, sc.is_active, sc.created_at, sc.updated_at
             ORDER BY sc.sort_order ASC, sc.name ASC'
        )->fetchAll();
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT * FROM service_categories WHERE id = :id LIMIT 1'
        );
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();

        return $row ?: null;
    }

    public function slugExists(string $slug, ?int $excludeId = null): bool
    {
        $sql = 'SELECT 1 FROM service_categories WHERE slug = :slug';
        $params = ['slug' => $slug];
        if ($excludeId !== null) {
            $sql .= ' AND id != :id';
            $params['id'] = $excludeId;
        }
        $stmt = $this->db->prepare($sql . ' LIMIT 1');
        $stmt->execute($params);
        return (bool) $stmt->fetchColumn();
    }

    public function create(array $data): int
    {
        $stmt = $this->db->prepare(
            'INSERT INTO service_categories (
                name, slug, description, sort_order, is_active
             ) VALUES (
                :name, :slug, :description, :sort_order, :is_active
             )'
        );
        $stmt->execute([
            'name' => $data['name'],
            'slug' => $data['slug'],
            'description' => $data['description'],
            'sort_order' => $data['sort_order'],
            'is_active' => $data['is_active']
        ]);

        return (int) $this->db->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $stmt = $this->db->prepare(
            'UPDATE service_categories
             SET name = :name, slug = :slug, description = :description,
                 sort_order = :sort_order, is_active = :is_active
             WHERE id = :id'
        );
        $stmt->execute([
            'id' => $id,
            'name' => $data['name'],
            'slug' => $data['slug'],
            'description' => $data['description'],
            'sort_order' => $data['sort_order'],
            'is_active' => $data['is_active']
        ]);
    }

    public function toggle(int $id): void
    {
        $stmt = $this->db->prepare(
            'UPDATE service_categories
             SET is_active = IF(is_active = 1, 0, 1)
             WHERE id = :id'
        );
        $stmt->execute(['id' => $id]);
    }
}
