-- Migration: Add pricing to cake features
-- Date: 2025-11-29

-- Add price column to cake types
ALTER TABLE cake_types ADD COLUMN price DECIMAL(10,2) DEFAULT 0 AFTER name_en;

-- Create flavor pricing table (different prices per size)
CREATE TABLE IF NOT EXISTS cake_flavor_pricing (
  id INT PRIMARY KEY AUTO_INCREMENT,
  flavor_id INT NOT NULL,
  size_id INT NOT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (flavor_id) REFERENCES cake_flavors(id) ON DELETE CASCADE,
  FOREIGN KEY (size_id) REFERENCES cake_sizes(id) ON DELETE CASCADE,
  UNIQUE KEY unique_flavor_size (flavor_id, size_id)
);

-- Add price tracking to cake_order_details
ALTER TABLE cake_order_details 
  ADD COLUMN base_price DECIMAL(10,2) DEFAULT 0 AFTER notes,
  ADD COLUMN extras_price DECIMAL(10,2) DEFAULT 0 AFTER base_price,
  ADD COLUMN total_price DECIMAL(10,2) DEFAULT 0 AFTER extras_price;

-- Add price for inscription
ALTER TABLE cake_order_details ADD COLUMN inscription_price DECIMAL(10,2) DEFAULT 0 AFTER has_inscription;

-- Add price for candles
ALTER TABLE cake_order_details ADD COLUMN candles_price DECIMAL(10,2) DEFAULT 0 AFTER has_candles;

-- Add price for number candle
ALTER TABLE cake_order_details ADD COLUMN number_candle_price DECIMAL(10,2) DEFAULT 0 AFTER has_number_candle;

-- Add price for sparkler
ALTER TABLE cake_order_details ADD COLUMN sparkler_price DECIMAL(10,2) DEFAULT 0 AFTER has_sparkler;

-- Add price for wafer photo
ALTER TABLE cake_order_details ADD COLUMN wafer_photo_price DECIMAL(10,2) DEFAULT 0 AFTER has_wafer_photo;

-- Add price for card
ALTER TABLE cake_order_details ADD COLUMN card_price DECIMAL(10,2) DEFAULT 0 AFTER has_card;

-- Add price for figurine
ALTER TABLE cake_order_details ADD COLUMN figurine_price DECIMAL(10,2) DEFAULT 0 AFTER has_figurine;
