<?php

class CakeFlavorPricing {
    private $pdo;

    public function __construct() {
        global $pdo;
        $this->pdo = $pdo;
    }

    public function getPrice($flavorId, $sizeId) {
        $stmt = $this->pdo->prepare("SELECT price FROM cake_flavor_pricing WHERE flavor_id = ? AND size_id = ?");
        $stmt->execute([$flavorId, $sizeId]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result ? (float)$result['price'] : 0;
    }

    public function getPricingForFlavor($flavorId) {
        $stmt = $this->pdo->prepare("
            SELECT cfp.*, cs.name as size_name, cs.name_en as size_name_en
            FROM cake_flavor_pricing cfp
            JOIN cake_sizes cs ON cfp.size_id = cs.id
            WHERE cfp.flavor_id = ?
            ORDER BY cs.sort_order
        ");
        $stmt->execute([$flavorId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function setPrice($flavorId, $sizeId, $price) {
        $stmt = $this->pdo->prepare("
            INSERT INTO cake_flavor_pricing (flavor_id, size_id, price) 
            VALUES (?, ?, ?)
            ON DUPLICATE KEY UPDATE price = VALUES(price)
        ");
        return $stmt->execute([$flavorId, $sizeId, $price]);
    }

    public function deleteForFlavor($flavorId) {
        $stmt = $this->pdo->prepare("DELETE FROM cake_flavor_pricing WHERE flavor_id = ?");
        return $stmt->execute([$flavorId]);
    }

    public function all() {
        $stmt = $this->pdo->query("
            SELECT cfp.*, 
                   cf.name as flavor_name, cf.name_en as flavor_name_en,
                   cs.name as size_name, cs.name_en as size_name_en
            FROM cake_flavor_pricing cfp
            JOIN cake_flavors cf ON cfp.flavor_id = cf.id
            JOIN cake_sizes cs ON cfp.size_id = cs.id
            ORDER BY cf.sort_order, cs.sort_order
        ");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
