-- Cake Features/Options Tables
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),
  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
);

-- 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
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), -- comma-separated IDs
  cake_type_custom VARCHAR(100),
  
  -- Inscription
  has_inscription BOOLEAN DEFAULT 0,
  inscription_text TEXT,
  
  -- Candles
  has_candles BOOLEAN DEFAULT 0,
  candles_count INT DEFAULT 0,
  has_number_candle BOOLEAN DEFAULT 0,
  number_candle_value INT,
  has_sparkler BOOLEAN DEFAULT 0,
  
  -- Wafer/Edible image
  has_wafer_photo BOOLEAN DEFAULT 0,
  wafer_file VARCHAR(255),
  
  -- Card/Note
  has_card BOOLEAN DEFAULT 0,
  card_text TEXT,
  card_pattern VARCHAR(100),
  
  -- Figurine
  has_figurine BOOLEAN DEFAULT 0,
  figurine_description TEXT,
  
  -- Reference
  reference_photo VARCHAR(255),
  notes TEXT,
  
  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
INSERT INTO cake_flavors (name, name_en, sort_order) VALUES
('Z mascarpone i owocami', 'Mascarpone with fruits', 1),
('Czarny luwr', 'Black Louvre', 2),
('Oreo', 'Oreo', 3),
('Ciasteczkowy', 'Cookie', 4),
('Orzechowy na kremie', 'Nutty with cream', 5),
('Kajmakowy na kremie', 'Caramel with cream', 6),
('Czekoladowy na kremie', 'Chocolate with cream', 7),
('Czekoladowo-Truskawkowy na kremie', 'Chocolate-Strawberry with cream', 8),
('Truskawkowy na kremie', 'Strawberry with cream', 9),
('Truskawkowy na śmietanie', 'Strawberry with whipped cream', 10),
('Malinowy na kremie', 'Raspberry with cream', 11),
('Malinowy na śmietanie', 'Raspberry with whipped cream', 12),
('Malinowa rozkosz', 'Raspberry delight', 13),
('Trio Czekoladowe', 'Chocolate Trio', 14),
('Super trio czekoladowe', 'Super Chocolate Trio', 15),
('Hiszpański', 'Spanish', 16),
('Bananowy', 'Banana', 17),
('Owocowa Poezja', 'Fruit Poetry', 18),
('Owoce Leśne', 'Forest Fruits', 19),
('Pistacjowy', 'Pistachio', 20),
('Inny', 'Other', 99);

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

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

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