<?php

class MenuItem extends Model
{
    protected string $table = 'menu_items';

    // Returns categories => items => variants, for the POS item picker
    public function getMenuForPos(): array
    {
        $items = $this->db->query(
            "SELECT mi.*, mc.category_name
             FROM menu_items mi
             JOIN menu_categories mc ON mc.id = mi.category_id
             WHERE mi.is_available = 1 AND mc.is_active = 1
             ORDER BY mc.display_order ASC, mi.item_name ASC"
        )->fetchAll();

        $variantStmt = $this->db->prepare(
            "SELECT * FROM menu_item_variants WHERE menu_item_id = :id ORDER BY price_adjustment ASC"
        );

        $grouped = [];
        foreach ($items as $item) {
            $variantStmt->execute(['id' => $item['id']]);
            $item['variants'] = $variantStmt->fetchAll();
            $grouped[$item['category_name']][] = $item;
        }
        return $grouped;
    }

    public function todaysOrderCounts(): array
    {
        $stmt = $this->db->query(
            "SELECT oi.menu_item_id, SUM(oi.quantity) as qty
             FROM order_items oi
             JOIN orders o ON o.id = oi.order_id
             WHERE DATE(o.created_at) = CURDATE() AND o.status != 'CANCELLED'
             GROUP BY oi.menu_item_id"
        );
        $counts = [];
        foreach ($stmt->fetchAll() as $row) {
            $counts[(int) $row['menu_item_id']] = (int) $row['qty'];
        }
        return $counts;
    }

    public function findWithVariant(int $menuItemId, ?int $variantId): array
    {
        $item = $this->find($menuItemId);
        if (!$item) {
            throw new RuntimeException("Menu item #{$menuItemId} not found");
        }
        $price = (float) $item['base_price'];
        $variantName = null;
        if ($variantId) {
            $stmt = $this->db->prepare("SELECT * FROM menu_item_variants WHERE id = :id AND menu_item_id = :mid");
            $stmt->execute(['id' => $variantId, 'mid' => $menuItemId]);
            $variant = $stmt->fetch();
            if ($variant) {
                $price += (float) $variant['price_adjustment'];
                $variantName = $variant['variant_name'];
            }
        }
        return [
            'item'         => $item,
            'unit_price'   => $price,
            'variant_name' => $variantName,
        ];
    }

    // Raw materials + qty required for 1 unit of this menu item
    public function getRecipe(int $menuItemId): array
    {
        $stmt = $this->db->prepare(
            "SELECT r.*, rm.material_name, rm.unit, rm.current_stock
             FROM recipes r JOIN raw_materials rm ON rm.id = r.raw_material_id
             WHERE r.menu_item_id = :id"
        );
        $stmt->execute(['id' => $menuItemId]);
        return $stmt->fetchAll();
    }

    public function addRecipeLine(int $menuItemId, int $rawMaterialId, float $quantityRequired): void
    {
        // Avoid duplicate rows for the same material — update instead if it already exists
        $existing = $this->db->prepare("SELECT id FROM recipes WHERE menu_item_id = :mi AND raw_material_id = :rm");
        $existing->execute(['mi' => $menuItemId, 'rm' => $rawMaterialId]);
        $row = $existing->fetch();

        if ($row) {
            $this->db->prepare("UPDATE recipes SET quantity_required = :qty WHERE id = :id")
                ->execute(['qty' => $quantityRequired, 'id' => $row['id']]);
        } else {
            $this->db->prepare(
                "INSERT INTO recipes (menu_item_id, raw_material_id, quantity_required) VALUES (:mi, :rm, :qty)"
            )->execute(['mi' => $menuItemId, 'rm' => $rawMaterialId, 'qty' => $quantityRequired]);
        }
    }

    public function deleteRecipeLine(int $recipeId): void
    {
        $this->db->prepare("DELETE FROM recipes WHERE id = :id")->execute(['id' => $recipeId]);
    }

    // --- Admin management ---

    public function allWithCategory(): array
    {
        return $this->db->query(
            "SELECT mi.*, mc.category_name
             FROM menu_items mi JOIN menu_categories mc ON mc.id = mi.category_id
             ORDER BY mc.display_order ASC, mi.item_name ASC"
        )->fetchAll();
    }

    public function getVariants(int $menuItemId): array
    {
        $stmt = $this->db->prepare(
            "SELECT * FROM menu_item_variants WHERE menu_item_id = :id ORDER BY price_adjustment ASC"
        );
        $stmt->execute(['id' => $menuItemId]);
        return $stmt->fetchAll();
    }

    public function addVariant(int $menuItemId, string $name, float $priceAdjustment): int
    {
        $stmt = $this->db->prepare(
            "INSERT INTO menu_item_variants (menu_item_id, variant_name, price_adjustment) VALUES (:id, :name, :adj)"
        );
        $stmt->execute(['id' => $menuItemId, 'name' => $name, 'adj' => $priceAdjustment]);
        return (int) $this->db->lastInsertId();
    }

    public function deleteVariant(int $variantId): void
    {
        $this->db->prepare("DELETE FROM menu_item_variants WHERE id = :id")->execute(['id' => $variantId]);
    }
}
