-- Create Database
CREATE DATABASE IF NOT EXISTS xsular_db;
USE xsular_db;

-- Products Table
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    tagline VARCHAR(255),
    category VARCHAR(100),
    price DECIMAL(10, 2) NOT NULL,
    rating DECIMAL(3, 1) DEFAULT 0,
    reviews INT DEFAULT 0,
    image VARCHAR(500),
    badge VARCHAR(50),
    active_ingredient VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders Table
-- storing id as VARCHAR to support the "#ORD-..." format or we can use auto-inc and format in code.
-- Let's stick to VARCHAR for the main ID to match current frontend logic, or use a separate display_id.
-- Currently frontend generates string IDs.
CREATE TABLE IF NOT EXISTS orders (
    id VARCHAR(50) PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255),
    date VARCHAR(50), -- Keeping as string to match current format "Jan 19, 2024", or ideally DATE type
    total DECIMAL(10, 2),
    status VARCHAR(50) DEFAULT 'Processing',
    payment_method VARCHAR(50),
    shipping_address TEXT,
    items_count INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Order Items Table
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(50),
    product_name VARCHAR(255),
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Messages Table
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    subject VARCHAR(255),
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Seed Data for Products
INSERT INTO products (name, tagline, category, price, rating, reviews, image, badge, active_ingredient) VALUES
('Clarifying Salicylic Cleanser', 'Pore-Deep Purification', 'Acne Control', 28.00, 4.9, 128, 'https://images.unsplash.com/photo-1556228720-1988d8b449b2?q=80&w=400', 'Best Seller', '2% Salicylic Acid'),
('Growth Activator Serum', 'Follicle Stimulation', 'Hair Care', 45.00, 4.8, 85, 'https://images.unsplash.com/photo-1620916566398-39f1143ab7be?q=80&w=400', 'Trending', 'Biotin + Caffeine'),
('Hydra-Repair Night Cream', 'Barrier Restoration', 'Moisturizer', 34.00, 5.0, 42, 'https://images.unsplash.com/photo-1608248596669-f35d25679d99?q=80&w=400', NULL, 'Ceramides + HA'),
('Bio-Active Balancing Toner', 'PH Restoration', 'Treatment', 24.00, 4.7, 63, 'https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=400', NULL, 'Niacinamide'),
('Revitalizing Scalp Treatment', 'Deep Follicle Nourishment', 'Hair Care', 38.00, 4.9, 76, 'https://images.unsplash.com/photo-1629198688000-71f23e745b6e?q=80&w=400', 'New', 'Peptides + Keratin');
