-- Complete Cake Orders System with Pricing
-- Date: 2025-11-29
-- This migration includes all tables and pricing columns

-- Drop existing tables to recreate with correct structure
DROP TABLE IF EXISTS cake_order_details;
DROP TABLE IF EXISTS cake_flavor_pricing;

-- Cake Features/Options Tables (recreate to ensure image_url and price columns)
CREATE TABLE IF NOT EXISTS cake_flavors (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  name_en VARCHAR(100),
  image_url VARCHAR(255),
  sort_order INT DEFAULT 0,
  active BOOLEAN DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS cake_sizes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  name_en VARCHAR(50),
  image_url VARCHAR(255),
  sort_order INT DEFAULT 0,
  active BOOLEAN DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS cake_types (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  name_en VARCHAR(50),
  price DECIMAL(10,2) DEFAULT 0,
  sort_order INT DEFAULT 0,
  active BOOLEAN DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS pickup_locations (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  address TEXT,
  sort_order INT DEFAULT 0,
  active BOOLEAN DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 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)
);

-- Extend orders table
ALTER TABLE orders 
  ADD COLUMN IF NOT EXISTS is_cake_order BOOLEAN DEFAULT 0,
  ADD COLUMN IF NOT EXISTS pickup_date DATE,
  ADD COLUMN IF NOT EXISTS pickup_time TIME,
  ADD COLUMN IF NOT EXISTS pickup_location_id INT,
  ADD COLUMN IF NOT EXISTS delivery_address TEXT,
  ADD COLUMN IF NOT EXISTS payment_method ENUM('deposit','on_pickup','paid') DEFAULT 'on_pickup',
  ADD COLUMN IF NOT EXISTS deposit_amount DECIMAL(10,2);

-- Cake order details with all pricing columns
CREATE TABLE IF NOT EXISTS cake_order_details (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  flavor_id INT,
  flavor_custom VARCHAR(100),
  size_id INT,
  size_custom TEXT,
  cake_type_ids VARCHAR(100),
  cake_type_custom VARCHAR(100),
  
  -- Inscription
  has_inscription BOOLEAN DEFAULT 0,
  inscription_text TEXT,
  inscription_price DECIMAL(10,2) DEFAULT 0,
  
  -- Candles
  has_candles BOOLEAN DEFAULT 0,
  candles_price DECIMAL(10,2) DEFAULT 0,
  candles_count INT DEFAULT 0,
  has_number_candle BOOLEAN DEFAULT 0,
  number_candle_price DECIMAL(10,2) DEFAULT 0,
  number_candle_value INT,
  has_sparkler BOOLEAN DEFAULT 0,
  sparkler_price DECIMAL(10,2) DEFAULT 0,
  
  -- Wafer/Edible image
  has_wafer_photo BOOLEAN DEFAULT 0,
  wafer_photo_price DECIMAL(10,2) DEFAULT 0,
  wafer_file VARCHAR(255),
  
  -- Card/Note
  has_card BOOLEAN DEFAULT 0,
  card_price DECIMAL(10,2) DEFAULT 0,
  card_text TEXT,
  card_pattern VARCHAR(100),
  
  -- Figurine
  has_figurine BOOLEAN DEFAULT 0,
  figurine_price DECIMAL(10,2) DEFAULT 0,
  figurine_description TEXT,
  
  -- Reference
  reference_photo VARCHAR(255),
  notes TEXT,
  
  -- Pricing summary
  base_price DECIMAL(10,2) DEFAULT 0,
  extras_price DECIMAL(10,2) DEFAULT 0,
  total_price DECIMAL(10,2) DEFAULT 0,
  
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (flavor_id) REFERENCES cake_flavors(id) ON DELETE SET NULL,
  FOREIGN KEY (size_id) REFERENCES cake_sizes(id) ON DELETE SET NULL
);

-- Insert default data only if tables are empty
INSERT IGNORE INTO cake_flavors (id, name, name_en, sort_order) VALUES
(1, 'Z mascarpone i owocami', 'Mascarpone with fruits', 1),
(2, 'Czarny luwr', 'Black Louvre', 2),
(3, 'Oreo', 'Oreo', 3),
(4, 'Ciasteczkowy', 'Cookie', 4),
(5, 'Orzechowy na kremie', 'Nutty with cream', 5),
(6, 'Kajmakowy na kremie', 'Caramel with cream', 6),
(7, 'Czekoladowy na kremie', 'Chocolate with cream', 7),
(8, 'Czekoladowo-Truskawkowy na kremie', 'Chocolate-Strawberry with cream', 8),
(9, 'Truskawkowy na kremie', 'Strawberry with cream', 9),
(10, 'Truskawkowy na śmietanie', 'Strawberry with whipped cream', 10),
(11, 'Malinowy na kremie', 'Raspberry with cream', 11),
(12, 'Malinowy na śmietanie', 'Raspberry with whipped cream', 12),
(13, 'Malinowa rozkosz', 'Raspberry delight', 13),
(14, 'Trio Czekoladowe', 'Chocolate Trio', 14),
(15, 'Super trio czekoladowe', 'Super Chocolate Trio', 15),
(16, 'Hiszpański', 'Spanish', 16),
(17, 'Bananowy', 'Banana', 17),
(18, 'Owocowa Poezja', 'Fruit Poetry', 18),
(19, 'Owoce Leśne', 'Forest Fruits', 19),
(20, 'Pistacjowy', 'Pistachio', 20),
(99, 'Inny', 'Other', 99);

INSERT IGNORE INTO cake_sizes (id, name, name_en, sort_order) VALUES
(1, 'Mały', 'Small', 1),
(2, 'Średni', 'Medium', 2),
(3, 'Duży', 'Large', 3),
(4, 'Inny', 'Other', 4);

INSERT IGNORE INTO cake_types (id, name, name_en, price, sort_order) VALUES
(1, 'Standardowy', 'Standard', 0, 1),
(2, 'Okazjonalny', 'Occasional', 0, 2),
(3, 'Artystyczny', 'Artistic', 0, 3),
(4, 'Weselny', 'Wedding', 0, 4),
(5, 'Inny', 'Other', 0, 5);

INSERT IGNORE INTO pickup_locations (id, name, address, sort_order) VALUES
(1, 'Słoneczna 1', 'Słoneczna 1', 1),
(2, 'Witkiewicza 2', 'Witkiewicza 2', 2),
(3, 'Krupówki 4a', 'Krupówki 4a', 3),
(4, 'S. Nędzy Kubińca 181, Kościelisko', 'S. Nędzy Kubińca 181, Kościelisko', 4),
(5, 'Pardałówka', 'Pardałówka', 5),
(6, 'Kasprusie 34', 'Kasprusie 34', 6),
(7, 'Chramcówki 4', 'Chramcówki 4', 7),
(8, 'Magda', 'Magda', 8),
(9, 'Dostawa', 'Delivery', 99);
