-- The Luxe Flame - Database Schema
-- Create database first: CREATE DATABASE luxeflame CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE luxeflame;

-- Admins table
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL DEFAULT 'Admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Categories table (DYNAMIC - add your own!)
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    icon VARCHAR(50) DEFAULT '',
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Products table
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    tagline VARCHAR(300),
    description TEXT,
    long_description TEXT,
    price DECIMAL(10,2) NOT NULL,
    original_price DECIMAL(10,2),
    category VARCHAR(50) NOT NULL DEFAULT 'Spice',
    wax_type VARCHAR(100) NOT NULL DEFAULT 'Premium Soy Wax Blend',
    burn_time VARCHAR(50),
    weight VARCHAR(50),
    shelf_life VARCHAR(50),
    fragrance TEXT,
    main_image VARCHAR(500),
    featured TINYINT(1) DEFAULT 0,
    bestseller TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Product Flavors table
CREATE TABLE IF NOT EXISTS product_flavors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    images JSON,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Home Sections table
CREATE TABLE IF NOT EXISTS home_sections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section_key VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(300),
    subtitle VARCHAR(300),
    body TEXT,
    image VARCHAR(500),
    overlay_image VARCHAR(500),
    cta_text VARCHAR(100),
    cta_link VARCHAR(300),
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- About Content table
CREATE TABLE IF NOT EXISTS about_content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section_key VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(300),
    content TEXT,
    image VARCHAR(500),
    icon VARCHAR(50),
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Contact Settings table
CREATE TABLE IF NOT EXISTS contact_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Site Settings table
CREATE TABLE IF NOT EXISTS site_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default admin (password: password)
INSERT INTO admins (username, password, name) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator');

-- Insert MANY categories (add/edit/delete these from admin panel!)
INSERT INTO categories (name, slug, description, icon, sort_order, is_active) VALUES
('All', 'all', 'View all candles across every category', '', 0, 1),
('Spice', 'spice', 'Warm cinnamon, clove, nutmeg and seasonal spice blends', 'pepper-hot', 1, 1),
('Woody', 'woody', 'Sandalwood, cedar, pine and earthy forest notes', 'tree', 2, 1),
('Floral', 'floral', 'Rose, lavender, jasmine and blooming garden scents', 'flower', 3, 1),
('Citrus', 'citrus', 'Bergamot, orange, lemon and zesty fresh notes', 'lemon', 4, 1),
('Sweet', 'sweet', 'Vanilla, caramel, honey and sugar-inspired aromas', 'candy-cane', 5, 1),
('Fresh', 'fresh', 'Clean cotton, linen breeze and airy ozone scents', 'wind', 6, 1),
('Herbal', 'herbal', 'Sage, eucalyptus, mint and natural botanical blends', 'leaf', 7, 1),
('Fruity', 'fruity', 'Apple, berry, peach and orchard fruit fragrances', 'apple-alt', 8, 1),
('Gourmand', 'gourmand', 'Coffee, chocolate, bakery and dessert-inspired scents', 'cookie', 9, 1),
('Oriental', 'oriental', 'Amber, oud, musk and exotic eastern blends', 'mosque', 10, 1),
('Marine', 'marine', 'Sea salt, ocean breeze and coastal water notes', 'water', 11, 1),
('Green', 'green', 'Grass, bamboo, green tea and nature-fresh aromas', 'seedling', 12, 1),
('Seasonal', 'seasonal', 'Limited edition autumn, winter, spring collections', 'snowflake', 13, 1),
('Festive', 'festive', 'Holiday, Christmas, celebration and gift-ready candles', 'gift', 14, 1),
('Relaxing', 'relaxing', 'Calming lavender, chamomile and sleep-well blends', 'spa', 15, 1),
('Romantic', 'romantic', 'Rose, champagne, love-inspired date night scents', 'heart', 16, 1),
('Masculine', 'masculine', 'Leather, tobacco, bourbon and bold gentleman scents', 'hat-cowboy', 17, 1),
('Feminine', 'feminine', 'Soft florals, peony, blush and elegant lady scents', 'gem', 18, 1),
('Tropical', 'tropical', 'Coconut, pineapple, mango and island escape vibes', 'umbrella-beach', 19, 1),
('Smoky', 'smoky', 'Fireside, campfire, bourbon barrel and toasted notes', 'fire', 20, 1);

-- Insert default contact settings
INSERT INTO contact_settings (setting_key, setting_value) VALUES
('address_line1', '123 Candle Lane, Artisan District'),
('address_line2', 'New York, NY 10001'),
('email', 'hello@theluxeflame.com'),
('order_email', 'orders@theluxeflame.com'),
('phone', '+1 (555) 123-4567'),
('hours', 'Mon-Fri, 9am-5pm EST'),
('instagram', 'https://instagram.com/theluxeflame'),
('facebook', 'https://facebook.com/theluxeflame'),
('footer_tagline', 'Hand-poured luxury candles crafted with premium soy wax and essential oils. Made to transform your space into a sanctuary of warmth and light.');

-- Insert site settings (INR - Indian Rupees)
INSERT INTO site_settings (setting_key, setting_value) VALUES
('site_name', 'The Luxe Flame'),
('site_tagline', 'Light that feels like home.'),
('currency', 'Rs'),
('currency_symbol', '₹'),
('free_shipping_threshold', '5000'),
('shipping_cost', '499'),
('tax_rate', '0'),
('country', 'India'),
('phone', '+91 98765 43210'),
('email', 'hello@theluxeflame.in'),
('address', '42, Fragrance Lane, Koramangala, Bangalore, Karnataka 560034');

-- Insert default home sections
INSERT INTO home_sections (section_key, title, subtitle, body, image, cta_text, cta_link, sort_order, is_active) VALUES
('hero', 'THE LUXE\nFLAME', 'CANDLE', 'Hand-poured candles made to slow the moment\u2014warm light, clean ingredients, and a scent that lingers like a memory.', 'hero_candle_dark.jpg', 'Shop the Collection', '#/products', 1, 1),
('meet_candle', 'MEET THE\nCANDLE', 'CANDLE', 'A clean-burning blend of soy and coconut wax, woven with essential oils and finished by hand in small batches.', 'candle_split_right.jpg', 'Read Our Story', '#/about', 2, 1),
('manifesto', 'LIGHT WORTH\nLOVING', 'THE LUXE FLAME', 'We craft candles that turn ordinary evenings into rituals\u2014soft light, honest ingredients, and a fragrance that becomes part of your space.', 'manifesto_candle_scene.jpg', 'See How We Make Them', '#/about', 3, 1),
('perfect_pair', 'THE PERFECT\nPAIR', NULL, 'Some evenings call for one scent. Others call for two. Mix, layer, and make the room yours.', 'collage_left_tall.jpg', 'Shop Bundles', '#/products', 4, 1),
('texture', 'TOUCH THE\nTEXTURE', NULL, 'Soy \u2022 Coconut \u2022 Essential Oils', 'texture_macro.jpg', NULL, NULL, 5, 1),
('crafted', 'CRAFTED BY\nHAND', NULL, 'Poured, finished, and packed with care. Small batches mean better quality\u2014and a candle that burns true.', 'hands_candle.jpg', 'Meet the Maker', '#/about', 6, 1),
('promise', 'MADE TO\nLAST', NULL, 'Clean burn. Even pool. No soot, no drift\u2014just steady light for hours.', 'promise_candle_wood.jpg', 'View Burn Guide', '#/about', 7, 1),
('for_two', 'A CANDLE\nFOR TWO', NULL, 'Share the light. Our gift sets come wrapped and ready\u2014because the best moments are the ones you give.', 'for_two_seated.jpg', 'Shop Gifts', '#/products', 8, 1),
('hours', 'BURNS FOR\nHOURS', 'UP TO 50 HOURS', 'Designed for long, clean sessions\u2014so you can light it and live with it.', 'hours_hand_candle.jpg', 'Shop Bestsellers', '#/products', 9, 1),
('light_in', 'LET THE\nLIGHT IN', NULL, 'Dim the room. Strike a match. Make tonight feel like home.', 'let_light_in_wide.jpg', 'Shop All Candles', '#/products', 10, 1),
('closing', 'FIND YOUR\nFLAME', NULL, 'Browse the collection and choose the scent that fits your evening.', 'closing_dark_candle.jpg', 'Shop Now', '#/products', 11, 1);

-- Insert default about content
INSERT INTO about_content (section_key, title, content, image, icon, sort_order, is_active) VALUES
('hero', 'Light Worth\nLoving', NULL, 'manifesto_candle_scene.jpg', NULL, 1, 1),
('story_title', 'The Luxe Flame Story', NULL, NULL, NULL, 2, 1),
('story_p1', NULL, 'The Luxe Flame was born from a simple belief: that the everyday ritual of lighting a candle should feel special. Founded in 2020, we set out to create candles that combine luxury craftsmanship with clean, honest ingredients.', NULL, NULL, 3, 1),
('story_p2', NULL, 'Each candle in our collection is hand-poured in small batches using a premium blend of soy and coconut wax. We source our fragrance oils from trusted suppliers who share our commitment to quality and sustainability.', NULL, NULL, 4, 1),
('story_p3', NULL, 'Our mission is to transform ordinary moments into something memorable. Whether you are unwinding after a long day, setting the mood for a dinner party, or simply enjoying a quiet morning, our candles are designed to elevate the experience.', NULL, NULL, 5, 1),
('values_title', 'What We Stand For', NULL, NULL, NULL, 6, 1),
('value_1', 'Handcrafted with Love', 'Every candle is poured, finished, and packed by hand in our small studio. We believe the care we put into each piece shines through in the final product.', NULL, 'Heart', 7, 1),
('value_2', 'Clean Ingredients', 'We use only premium soy and coconut wax blends, lead-free cotton wicks, and phthalate-free fragrance oils. Clean burning means cleaner air in your home.', NULL, 'Leaf', 8, 1),
('value_3', 'Small Batch Quality', 'We produce in small batches to ensure consistent quality and attention to detail. Each batch is tested for scent throw, burn time, and appearance.', NULL, 'Award', 9, 1),
('value_4', 'Sustainable Packaging', 'Our packaging is fully recyclable and made from recycled materials whenever possible. We are committed to reducing our environmental footprint.', NULL, 'Truck', 10, 1),
('process_title', 'Our Process', NULL, 'hands_candle.jpg', NULL, 11, 1),
('process_1', 'Sourcing', 'We carefully select our raw materials, from premium soy wax to the finest essential oils and fragrance blends. Every ingredient is tested for quality and safety.', NULL, NULL, 12, 1),
('process_2', 'Blending', 'Our signature scents are created by blending fragrance oils at precise ratios. Each formula is refined through multiple iterations until we achieve the perfect balance.', NULL, NULL, 13, 1),
('process_3', 'Pouring & Curing', 'Each candle is hand-poured at the optimal temperature and allowed to cure for 48 hours. This ensures a smooth surface and optimal scent throw when burned.', NULL, NULL, 14, 1),
('stat_1', '15+', 'Unique Scents', NULL, NULL, 15, 1),
('stat_2', '50h', 'Max Burn Time', NULL, NULL, 16, 1),
('stat_3', '100%', 'Handcrafted', NULL, NULL, 17, 1),
('stat_4', '10k+', 'Happy Customers', NULL, NULL, 18, 1);


-- ===== NEW TABLES FOR 100% COMPLETE BACKEND =====

-- Orders table (CUSTOMER ORDERS)
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    customer_name VARCHAR(200) NOT NULL,
    customer_email VARCHAR(200) NOT NULL,
    customer_phone VARCHAR(20),
    shipping_address TEXT NOT NULL,
    city VARCHAR(100),
    state VARCHAR(100),
    pin_code VARCHAR(10),
    items_json LONGTEXT NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    shipping_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL,
    status ENUM('pending','confirmed','shipped','delivered','cancelled') DEFAULT 'pending',
    payment_method VARCHAR(50) DEFAULT 'cod',
    payment_status ENUM('pending','paid','failed','refunded') DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Contact Form Submissions table
CREATE TABLE IF NOT EXISTS contact_submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    email VARCHAR(200) NOT NULL,
    phone VARCHAR(20),
    message TEXT NOT NULL,
    ip_address VARCHAR(45),
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Newsletter Subscribers table
CREATE TABLE IF NOT EXISTS newsletter_subscribers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(200) NOT NULL UNIQUE,
    ip_address VARCHAR(45),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Testimonials table
CREATE TABLE IF NOT EXISTS testimonials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    location VARCHAR(200),
    quote TEXT NOT NULL,
    image VARCHAR(500),
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_active (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Refund Policy table
CREATE TABLE IF NOT EXISTS refund_policy (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    content TEXT NOT NULL,
    icon VARCHAR(50) DEFAULT '',
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default testimonial
INSERT INTO testimonials (name, location, quote, sort_order, is_active) VALUES
('Priya Sharma', 'Mumbai', 'The Sandalwood Amber candle transformed my living room into a sanctuary. The scent is sophisticated, long-lasting, and unmistakably premium. This is now my go-to gift for every special occasion.', 1, 1);

-- Insert default refund policy sections
INSERT INTO refund_policy (title, content, icon, sort_order, is_active) VALUES
('30-Day Returns', 'We accept returns within 30 days of delivery. Items must be unused, in original packaging, and in resalable condition.', 'shield-alt', 1, 1),
('Easy Exchanges', 'Not happy with your scent? Exchange it for a different one within 30 days. We will cover the return shipping for exchanges.', 'sync', 2, 1),
('Damaged Items', 'If your candle arrives damaged, contact us within 7 days with photos. We will send a replacement or issue a full refund immediately.', 'heart', 3, 1),
('Refund Timeline', 'Refunds are processed within 5-7 business days after we receive your return. Funds appear in your account within 3-5 additional days.', 'clock', 4, 1);