-- Database Schema for XSular Parfum

CREATE DATABASE IF NOT EXISTS xsular_parfum;
USE xsular_parfum;

-- Categories (Collections)
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    image_url VARCHAR(255),
    is_featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Orders
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    customer_name VARCHAR(100) NOT NULL,
    customer_email VARCHAR(100) NOT NULL,
    customer_phone VARCHAR(20),
    shipping_address TEXT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Order Items
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT,
    product_name VARCHAR(100) NOT NULL, -- Stored in case product is deleted
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

-- Contact Messages
CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    subject VARCHAR(200),
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert Categories
INSERT INTO categories (name, slug, description, image_url) VALUES
('Signature', 'signature', 'Timeless compositions defining modern elegance.', 'https://images.unsplash.com/photo-1615634260167-c8cdede054de'),
('Private Blend', 'private-blend', 'Rare ingredients meeting artisanal mastery.', 'https://images.unsplash.com/photo-1594035910387-fea4779426e9'),
('Home Fragrance', 'home-fragrance', 'Atmospheric scents to transform your space.', 'https://images.unsplash.com/photo-1608528577891-9e859e9a38f9');

-- Insert Sample Products
INSERT INTO products (category_id, name, slug, description, price, stock_quantity, image_url, is_featured) VALUES
(1, 'Rose Noire', 'rose-noire', 'A dark and mysterious rose scent with notes of oud and amber.', 185.00, 50, 'https://images.unsplash.com/photo-1594035910387-fea4779426e9', TRUE),
(2, 'Santal 33', 'santal-33', 'A woody aromatic fragrance for men and women.', 220.00, 30, 'https://images.unsplash.com/photo-1615634260167-c8cdede054de', TRUE),
(1, 'Velvet Amber', 'velvet-amber', 'Rich amber accord with touches of vanilla and musk.', 290.00, 15, 'https://images.unsplash.com/photo-1541643600914-78b084683601', TRUE);
