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

DROP TABLE IF EXISTS delivery_orders;
DROP TABLE IF EXISTS store_setup_steps;
DROP TABLE IF EXISTS payouts;
DROP TABLE IF EXISTS coupons;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS restaurants;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(160) NOT NULL UNIQUE,
    password VARCHAR(120) NOT NULL,
    role ENUM('customer', 'vendor', 'admin', 'delivery_boy') NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE restaurants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    cuisine VARCHAR(160) NOT NULL,
    specialty VARCHAR(255) NOT NULL,
    delivery_time VARCHAR(80) NOT NULL,
    rating DECIMAL(3,1) NOT NULL,
    delivery_fee VARCHAR(80) NOT NULL,
    distance_km DECIMAL(4,1) NOT NULL,
    address VARCHAR(255) NOT NULL,
    latitude DECIMAL(10,6) NOT NULL,
    longitude DECIMAL(10,6) NOT NULL,
    opening_hours VARCHAR(120) NOT NULL,
    min_order VARCHAR(80) NOT NULL,
    price_level VARCHAR(12) NOT NULL,
    partner_type VARCHAR(120) NOT NULL,
    color_key VARCHAR(40) NOT NULL,
    banner_icon_key VARCHAR(60) NOT NULL,
    features_json JSON NOT NULL,
    tags_json JSON NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE menu_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    restaurant_id INT NOT NULL,
    title VARCHAR(160) NOT NULL,
    description TEXT NOT NULL,
    price_label VARCHAR(40) NOT NULL,
    price_value DECIMAL(10,2) NOT NULL,
    image_url VARCHAR(255) NOT NULL DEFAULT '',
    spicy_level VARCHAR(60) NOT NULL,
    category VARCHAR(120) NOT NULL,
    prep_time VARCHAR(40) NOT NULL,
    calories VARCHAR(40) NOT NULL,
    badge VARCHAR(80) NOT NULL,
    add_ons_json JSON NOT NULL,
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(40) NOT NULL,
    customer_name VARCHAR(120) NOT NULL,
    role_scope ENUM('customer', 'vendor') NOT NULL DEFAULT 'vendor',
    status VARCHAR(40) NOT NULL,
    date_time VARCHAR(80) NOT NULL,
    amount VARCHAR(40) NOT NULL,
    payment_type VARCHAR(40) NOT NULL,
    delivery_mode VARCHAR(60) NOT NULL,
    note VARCHAR(255) NOT NULL DEFAULT '',
    rating DECIMAL(2,1) DEFAULT NULL
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    item_title VARCHAR(160) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

CREATE TABLE coupons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(40) NOT NULL,
    title VARCHAR(160) NOT NULL,
    description VARCHAR(255) NOT NULL,
    usage_text VARCHAR(255) NOT NULL,
    status VARCHAR(40) NOT NULL
);

CREATE TABLE payouts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(160) NOT NULL,
    amount VARCHAR(40) NOT NULL,
    payout_date VARCHAR(80) NOT NULL,
    status VARCHAR(40) NOT NULL
);

CREATE TABLE store_setup_steps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    step_title VARCHAR(160) NOT NULL,
    step_description VARCHAR(255) NOT NULL,
    status VARCHAR(40) NOT NULL,
    sort_order INT NOT NULL
);

CREATE TABLE delivery_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(40) NOT NULL,
    status VARCHAR(40) NOT NULL,
    amount VARCHAR(40) NOT NULL
);

INSERT INTO users (name, email, password, role) VALUES
('Paul Miah', 'customer@feasto.com', '123456', 'customer'),
('Saffron Plate Admin', 'vendor@feasto.com', '123456', 'vendor'),
('Platform Admin', 'admin@feasto.com', '123456', 'admin'),
('Rider Hasan', 'rider@feasto.com', '123456', 'delivery_boy');

INSERT INTO restaurants (
    name, cuisine, specialty, delivery_time, rating, delivery_fee, distance_km,
    address, latitude, longitude, opening_hours, min_order, price_level,
    partner_type, color_key, banner_icon_key, features_json, tags_json
) VALUES
(
    'Saffron Plate',
    'Premium Bangla & Grill',
    'Kala Bhuna, mezbani beef, family platters',
    '20-25 min',
    4.9,
    'Free',
    1.8,
    'House 12, Road 8, Banani, Dhaka',
    23.793700,
    90.406600,
    '10:00 AM - 11:30 PM',
    '$8',
    '$$',
    'Individual Restaurant',
    'primary',
    'outdoor_grill',
    JSON_ARRAY('GPS tracked', 'Halal', 'Cashback', 'Party order'),
    JSON_ARRAY('Best Seller', 'Family Meals', 'Individual')
),
(
    'Urban Spice Hub',
    'Biriyani, Kebab, Dessert',
    'Dhakai biriyani, smoky kebab, premium desserts',
    '30-35 min',
    4.8,
    '$2.50',
    3.6,
    'Plot 7, Gulshan Avenue, Dhaka',
    23.792500,
    90.414400,
    '11:00 AM - 1:00 AM',
    '$10',
    '$$$',
    'Multi Vendor Kitchen',
    'secondary',
    'ramen_dining',
    JSON_ARRAY('GPS tracked', 'Late night', 'Combo meal', 'Top rated'),
    JSON_ARRAY('Multi Vendor', 'Festival Offer', 'Late Night')
),
(
    'Coastal Bowl',
    'Seafood & Healthy Bowls',
    'Prawn bowls, grilled fish, clean eating',
    '18-22 min',
    4.7,
    '$1.99',
    2.4,
    'Lake View Plaza, Baridhara DOHS, Dhaka',
    23.810100,
    90.425400,
    '9:00 AM - 10:30 PM',
    '$7',
    '$$',
    'Cloud Kitchen',
    'soft_gold',
    'set_meal',
    JSON_ARRAY('GPS tracked', 'Healthy', 'Diet menu', 'Office pack'),
    JSON_ARRAY('Fresh', 'Office Lunch', 'Popular')
);

INSERT INTO menu_items (
    restaurant_id, title, description, price_label, price_value, image_url, spicy_level,
    category, prep_time, calories, badge, add_ons_json, is_featured
) VALUES
(
    1,
    'Smoked Beef Kala Bhuna',
    'Slow-cooked beef with charred onion, aromatic spice oil, and saffron rice.',
    '$12.99',
    12.99,
    'https://images.unsplash.com/photo-1544025162-d76694265947?auto=format&fit=crop&w=900&q=80',
    'Medium',
    'Signature Main Course',
    '18 min',
    '740 kcal',
    'Chef Special',
    JSON_ARRAY('Extra beef +$3', 'Butter naan +$1.5', 'Cold drinks +$1'),
    1
),
(
    1,
    'Royal Chicken Platter',
    'Juicy grilled chicken, butter naan, fresh salad, and mint yogurt.',
    '$10.80',
    10.80,
    'https://images.unsplash.com/photo-1512058564366-18510be2db19?auto=format&fit=crop&w=900&q=80',
    'Mild',
    'Family Combo',
    '16 min',
    '620 kcal',
    'Most Popular',
    JSON_ARRAY('Extra dip +$0.8', 'Fries +$1.2', 'Soup +$1.7'),
    1
),
(
    3,
    'Ocean Lime Bowl',
    'Garlic prawns, coconut rice, roasted vegetables, and lime dressing.',
    '$13.40',
    13.40,
    'https://images.unsplash.com/photo-1559847844-5315695dadae?auto=format&fit=crop&w=900&q=80',
    'Light',
    'Healthy Bowl',
    '14 min',
    '510 kcal',
    'Fresh Catch',
    JSON_ARRAY('Avocado +$1.8', 'Poached egg +$1', 'Lemon soda +$1.3'),
    1
);

INSERT INTO orders (
    order_id, customer_name, role_scope, status, date_time, amount, payment_type,
    delivery_mode, note, rating
) VALUES
('V-1021', 'Rahim Uddin', 'vendor', 'preparing', '14 May 2026, 1:10 PM', '$24.80', 'Card', 'GPS delivery', 'Extra napkins please', NULL),
('V-1020', 'Nusrat Jahan', 'vendor', 'ready', '14 May 2026, 12:42 PM', '$18.40', 'Cash', 'Pickup', 'Call on arrival', NULL),
('V-1019', 'Tanim Noor', 'vendor', 'pending', '14 May 2026, 12:05 PM', '$31.90', 'Card', 'Express', 'No onion', NULL),
('V-1015', 'Mitu Akter', 'vendor', 'completed', '13 May 2026, 9:12 PM', '$29.90', 'Card', 'GPS delivery', '', 4.9),
('V-1014', 'Imran Hossain', 'vendor', 'completed', '13 May 2026, 7:48 PM', '$17.20', 'Cash', 'Pickup', '', 4.7);

INSERT INTO order_items (order_id, item_title) VALUES
(1, 'Smoked Beef Kala Bhuna'),
(1, 'Butter naan'),
(2, 'Royal Chicken Platter'),
(2, 'Soup'),
(3, 'Ocean Lime Bowl'),
(3, 'Lemon soda'),
(4, 'Family Grill Pack'),
(4, 'Cold drinks'),
(5, 'Chicken Wrap Combo');

INSERT INTO coupons (code, title, description, usage_text, status) VALUES
('WEEKEND20', 'Weekend Push', 'Orders above $20 get 20% off. Valid Friday evening to Sunday night.', 'Used 82 times - Revenue assisted $1,480', 'running'),
('LUNCH10', 'Office Lunch Boost', '10% off for weekday lunch orders over $12.', 'Used 41 times - Revenue assisted $620', 'scheduled');

INSERT INTO payouts (title, amount, payout_date, status) VALUES
('Weekly settlement', '$2,480', '13 May 2026', 'processing'),
('Previous transfer', '$2,140', '06 May 2026', 'transferred');

INSERT INTO store_setup_steps (step_title, step_description, status, sort_order) VALUES
('Business profile', 'Update business name, cuisine, logo, and public about section.', 'completed', 1),
('Delivery zone', 'Choose service area, estimated delivery time, and GPS coverage.', 'completed', 2),
('Operating hours', 'Define open hours, off days, and holiday rules.', 'needs_review', 3),
('Bank settlement', 'Add bank account details for payout transfer.', 'pending_verification', 4),
('Brand media', 'Upload storefront banner, packaging badge, and promo cover.', 'pending', 5);

INSERT INTO delivery_orders (order_id, status, amount) VALUES
('D-9001', 'assigned', '$12.80'),
('D-9002', 'picked_up', '$18.40'),
('D-8991', 'delivered', '$10.50'),
('D-8990', 'delivered', '$14.20'),
('D-8989', 'delivered', '$8.90');