CREATE DATABASE IF NOT EXISTS cafe_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cafe_app;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(160) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    membership_label VARCHAR(120) NOT NULL DEFAULT 'New Member',
    address VARCHAR(255) NOT NULL DEFAULT 'Dhaka, Bangladesh',
    reward_progress DECIMAL(4,2) NOT NULL DEFAULT 0.12,
    cafe_type VARCHAR(120) NOT NULL DEFAULT 'Fusion Cafe',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(120) NOT NULL,
    subtitle VARCHAR(180) NOT NULL,
    icon_key VARCHAR(80) NOT NULL,
    gradient_start VARCHAR(20) NOT NULL,
    gradient_end VARCHAR(20) NOT NULL,
    image_url TEXT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS menu_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    short_description VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    badge VARCHAR(80) NOT NULL,
    prep_time VARCHAR(40) NOT NULL,
    calories VARCHAR(40) NOT NULL,
    image_url TEXT NOT NULL,
    accent_start VARCHAR(20) NOT NULL,
    accent_end VARCHAR(20) NOT NULL,
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_menu_category FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE IF NOT EXISTS item_addons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    name VARCHAR(140) NOT NULL,
    note VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_addon_item FOREIGN KEY (item_id) REFERENCES menu_items(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS offers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(160) NOT NULL,
    subtitle VARCHAR(255) NOT NULL,
    cta VARCHAR(80) NOT NULL,
    image_url TEXT NOT NULL,
    color_start VARCHAR(20) NOT NULL,
    color_end VARCHAR(20) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(40) NOT NULL,
    delivery_mode VARCHAR(80) NOT NULL,
    payment_mode VARCHAR(80) NOT NULL,
    address VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    item_id INT NOT NULL,
    item_name VARCHAR(160) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    addon_name VARCHAR(160) NOT NULL DEFAULT '',
    CONSTRAINT fk_order_item_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

INSERT INTO users (name, email, password_hash, membership_label, address, reward_progress, cafe_type)
VALUES
('Ariana Paul', 'demo@brewflame.com', '$2y$10$2Y9F3aTkmvM9Thn9rpRik.O597KmJhildafOH5JVxPwvXf0FOHGOC', 'Loyalty Gold Member', '22 Lake View Road, Dhaka', 0.72, 'Fusion Cafe')
ON DUPLICATE KEY UPDATE email = email;

INSERT INTO categories (title, subtitle, icon_key, gradient_start, gradient_end, image_url, sort_order)
VALUES
('Tea Cafe', 'Masala chai, snacks', 'emoji_food_beverage', '#E8B56A', '#C86B3C', 'https://images.unsplash.com/photo-1515823064-d6e0c04616a7?auto=format&fit=crop&w=900&q=80', 1),
('Chinese Cafe', 'Wok bowls, momos', 'ramen_dining', '#DE7C5A', '#B5472F', 'https://images.unsplash.com/photo-1544025162-d76694265947?auto=format&fit=crop&w=900&q=80', 2),
('Tandoori Cafe', 'Grill and spice', 'outdoor_grill', '#876445', '#402218', 'https://images.unsplash.com/photo-1603894584373-5ac82b2ae398?auto=format&fit=crop&w=900&q=80', 3)
ON DUPLICATE KEY UPDATE title = VALUES(title);

INSERT INTO menu_items (category_id, slug, name, short_description, description, price, badge, prep_time, calories, image_url, accent_start, accent_end, is_featured)
VALUES
(1, 'saffron-chai-latte', 'Saffron Chai Latte', 'Silky tea with saffron foam and almond crunch', 'A rich cafe signature made for tea lounges and fusion spots, blending warm spices, delicate saffron, creamy foam, and a soft almond finish.', 6.90, 'Best seller', '8 min', '220 kcal', 'https://images.unsplash.com/photo-1517701550927-30cf4ba1f72d?auto=format&fit=crop&w=1200&q=80', '#F0C078', '#C86B3C', 1),
(2, 'dragon-chili-noodles', 'Dragon Chili Noodles', 'Street-style wok noodles with smoky chili glaze', 'Built for chinese cafe menus with bold wok fragrance, glossy chili sauce, sauteed vegetables, and a restaurant-style finish.', 11.40, 'Chef pick', '15 min', '540 kcal', 'https://images.unsplash.com/photo-1617622141675-d3005b9067c5?auto=format&fit=crop&w=1200&q=80', '#E59A74', '#B5472F', 1),
(3, 'paneer-tandoori-wrap', 'Paneer Tandoori Wrap', 'Charred paneer, onion slaw, mint mayo', 'A tandoori-cafe friendly wrap layered with smoky paneer, bright herbs, crunchy onion slaw, and creamy mint sauce in toasted flatbread.', 8.90, 'New', '12 min', '410 kcal', 'https://images.unsplash.com/photo-1550547660-d9450f859349?auto=format&fit=crop&w=1200&q=80', '#9A7055', '#402218', 1),
(1, 'chocolate-lava-cookie', 'Chocolate Lava Cookie', 'Warm dessert with vanilla drizzle', 'A soft-centered cookie for tea or dessert cafes, baked for a gooey middle and topped with smooth vanilla sauce.', 4.30, 'Sweet', '7 min', '280 kcal', 'https://images.unsplash.com/photo-1499636136210-6f4ee915583e?auto=format&fit=crop&w=1200&q=80', '#E7BA8A', '#9A4E2D', 0)
ON DUPLICATE KEY UPDATE slug = VALUES(slug);

INSERT INTO item_addons (item_id, name, note, price)
SELECT id, 'Classic recipe', 'No extra add-on', 0.00 FROM menu_items WHERE slug = 'saffron-chai-latte'
UNION ALL
SELECT id, 'Oat milk', 'Lighter and dairy-free', 0.80 FROM menu_items WHERE slug = 'saffron-chai-latte'
UNION ALL
SELECT id, 'Extra saffron foam', 'More aroma and texture', 1.20 FROM menu_items WHERE slug = 'saffron-chai-latte'
UNION ALL
SELECT id, 'Regular spice', 'Balanced cafe heat', 0.00 FROM menu_items WHERE slug = 'dragon-chili-noodles'
UNION ALL
SELECT id, 'Extra chili oil', 'For bold spice lovers', 0.90 FROM menu_items WHERE slug = 'dragon-chili-noodles'
UNION ALL
SELECT id, 'Add chicken', 'More protein and depth', 2.00 FROM menu_items WHERE slug = 'dragon-chili-noodles'
UNION ALL
SELECT id, 'Original', 'House balance of sauce and spice', 0.00 FROM menu_items WHERE slug = 'paneer-tandoori-wrap'
UNION ALL
SELECT id, 'Cheese melt', 'Extra creamy layer', 1.00 FROM menu_items WHERE slug = 'paneer-tandoori-wrap'
UNION ALL
SELECT id, 'Double paneer', 'More grilled filling', 2.20 FROM menu_items WHERE slug = 'paneer-tandoori-wrap'
UNION ALL
SELECT id, 'Vanilla drizzle', 'Included in base price', 0.00 FROM menu_items WHERE slug = 'chocolate-lava-cookie'
UNION ALL
SELECT id, 'Ice cream scoop', 'Cold contrast on top', 1.40 FROM menu_items WHERE slug = 'chocolate-lava-cookie'
UNION ALL
SELECT id, 'Caramel dust', 'Sweet golden crunch', 0.70 FROM menu_items WHERE slug = 'chocolate-lava-cookie';

INSERT INTO offers (title, subtitle, cta, image_url, color_start, color_end, is_active, sort_order)
VALUES
('Buy 1 Get 1 Tea Hour', 'Weekdays, 4 PM to 6 PM on all handcrafted chai.', 'Claim deal', 'https://images.unsplash.com/photo-1509042239860-f550ce710b93?auto=format&fit=crop&w=1200&q=80', '#402218', '#87563F', 1, 1),
('Family Tandoori Combo', 'Flat 20% off on platters, wraps, and mocktails.', 'View combo', 'https://images.unsplash.com/photo-1555939594-58d7cb561ad1?auto=format&fit=crop&w=1200&q=80', '#9A4E2D', '#C86B3C', 1, 2),
('Live Music Friday', 'Reserve seats for acoustic night with dessert sampler.', 'Reserve now', 'https://images.unsplash.com/photo-1517248135467-4c7edcad34c4?auto=format&fit=crop&w=1200&q=80', '#2E6F57', '#5BA788', 1, 3)
ON DUPLICATE KEY UPDATE title = VALUES(title);