<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;

final class MenuItemModel
{
    private Database $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    // ── Items ─────────────────────────────────────────────────────────────────

    public function all(int $page = 1, int $perPage = ITEMS_PER_PAGE, array $filters = []): array
    {
        $offset = ($page - 1) * $perPage;
        $where  = ['1=1'];
        $params = [];

        if (!empty($filters['category_id'])) {
            $where[]  = 'mi.category_id = ?';
            $params[] = $filters['category_id'];
        }
        if (!empty($filters['search'])) {
            $where[]  = 'mi.name LIKE ?';
            $params[] = '%' . $filters['search'] . '%';
        }
        if (isset($filters['is_available']) && $filters['is_available'] !== '') {
            $where[]  = 'mi.is_available = ?';
            $params[] = (int) $filters['is_available'];
        }

        $sql = "SELECT mi.*, c.name AS category_name,
                       u.name AS created_by_name,
                       (SELECT COUNT(*) FROM item_choice_groups g WHERE g.menu_item_id = mi.id) AS choice_group_count
                  FROM menu_items mi
                  JOIN categories c ON c.id = mi.category_id
                  LEFT JOIN users u ON u.id = mi.created_by
                 WHERE " . implode(' AND ', $where) . "
                 ORDER BY c.sort_order ASC, mi.sort_order ASC, mi.id DESC
                 LIMIT ? OFFSET ?";

        $params[] = $perPage;
        $params[] = $offset;

        return $this->db->fetchAll($sql, $params);
    }

    public function count(array $filters = []): int
    {
        $where  = ['1=1'];
        $params = [];

        if (!empty($filters['category_id'])) {
            $where[]  = 'category_id = ?';
            $params[] = $filters['category_id'];
        }
        if (!empty($filters['search'])) {
            $where[]  = 'name LIKE ?';
            $params[] = '%' . $filters['search'] . '%';
        }
        if (isset($filters['is_available']) && $filters['is_available'] !== '') {
            $where[]  = 'is_available = ?';
            $params[] = (int) $filters['is_available'];
        }

        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM menu_items WHERE " . implode(' AND ', $where),
            $params
        );
    }

    public function find(int $id): ?array
    {
        return $this->db->fetchOne(
            "SELECT mi.*, c.name AS category_name
               FROM menu_items mi
               JOIN categories c ON c.id = mi.category_id
              WHERE mi.id = ?",
            [$id]
        );
    }

    public function slugExists(string $slug, int $excludeId = 0): bool
    {
        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM menu_items WHERE slug = ? AND id != ?",
            [$slug, $excludeId]
        ) > 0;
    }

    public function create(array $data): int
    {
        return $this->db->insert('menu_items', $data);
    }

    public function update(int $id, array $data): int
    {
        return $this->db->update('menu_items', $data, ['id' => $id]);
    }

    public function delete(int $id): int
    {
        return $this->db->delete('menu_items', ['id' => $id]);
    }

    public function toggle(int $id): bool
    {
        $current = (int) $this->db->fetchValue(
            "SELECT is_available FROM menu_items WHERE id = ?", [$id]
        );
        $this->db->update('menu_items', ['is_available' => $current ? 0 : 1], ['id' => $id]);
        return !$current;
    }

    // ── Choice Groups ─────────────────────────────────────────────────────────

    public function getChoiceGroups(int $itemId): array
    {
        $groups = $this->db->fetchAll(
            "SELECT * FROM item_choice_groups WHERE menu_item_id = ? ORDER BY sort_order ASC",
            [$itemId]
        );

        foreach ($groups as &$group) {
            $group['choices'] = $this->db->fetchAll(
                "SELECT * FROM item_choices WHERE choice_group_id = ? ORDER BY sort_order ASC",
                [$group['id']]
            );
        }

        return $groups;
    }

    public function findChoiceGroup(int $groupId): ?array
    {
        return $this->db->fetchOne(
            "SELECT * FROM item_choice_groups WHERE id = ?", [$groupId]
        );
    }

    public function createChoiceGroup(array $data): int
    {
        return $this->db->insert('item_choice_groups', $data);
    }

    public function updateChoiceGroup(int $id, array $data): void
    {
        $this->db->update('item_choice_groups', $data, ['id' => $id]);
    }

    public function deleteChoiceGroup(int $id): void
    {
        $this->db->delete('item_choice_groups', ['id' => $id]);
    }

    // ── Choices ───────────────────────────────────────────────────────────────

    public function findChoice(int $choiceId): ?array
    {
        return $this->db->fetchOne(
            "SELECT ic.*, icg.menu_item_id
               FROM item_choices ic
               JOIN item_choice_groups icg ON icg.id = ic.choice_group_id
              WHERE ic.id = ?",
            [$choiceId]
        );
    }

    public function createChoice(array $data): int
    {
        return $this->db->insert('item_choices', $data);
    }

    public function updateChoice(int $id, array $data): void
    {
        $this->db->update('item_choices', $data, ['id' => $id]);
    }

    public function deleteChoice(int $id): void
    {
        $this->db->delete('item_choices', ['id' => $id]);
    }

    public function saveChoiceGroups(int $itemId, array $groups): void
    {
        // Delete existing groups (cascades to choices)
        $this->db->query(
            "DELETE FROM item_choice_groups WHERE menu_item_id = ?", [$itemId]
        );

        foreach ($groups as $sortOrder => $group) {
            $groupName = trim($group['name'] ?? '');
            if (!$groupName) continue;

            $groupId = $this->createChoiceGroup([
                'menu_item_id' => $itemId,
                'name'         => $groupName,
                'is_required'  => isset($group['is_required']) ? 1 : 0,
                'min_select'   => max(0, (int)($group['min_select'] ?? 1)),
                'max_select'   => max(1, (int)($group['max_select'] ?? 1)),
                'sort_order'   => $sortOrder,
            ]);

            foreach ($group['choices'] ?? [] as $ci => $choice) {
                $label = trim($choice['label'] ?? '');
                if (!$label) continue;

                $this->createChoice([
                    'choice_group_id' => $groupId,
                    'label'           => $label,
                    'price_modifier'  => (float)($choice['price_modifier'] ?? 0),
                    'is_default'      => isset($choice['is_default']) ? 1 : 0,
                    'sort_order'      => $ci,
                ]);
            }
        }
    }
}
