SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS wishlist;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS product_sizes;
DROP TABLE IF EXISTS product_colors;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS sizes;
DROP TABLE IF EXISTS colors;
DROP TABLE IF EXISTS users;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- CATEGORIES
-- ============================================================
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    image VARCHAR(255),
    slug VARCHAR(255) UNIQUE NOT NULL
);

-- ============================================================
-- PRODUCTS
-- ============================================================
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    category_id INT,
    image VARCHAR(255),
    stock INT DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- ============================================================
-- USERS
-- ============================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    address TEXT,
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- ORDERS
-- ============================================================
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    status ENUM('pending','processing','shipped','delivered','cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- ============================================================
-- ORDER ITEMS
-- ============================================================
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    color VARCHAR(255) DEFAULT NULL,
    size VARCHAR(50) DEFAULT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

-- ============================================================
-- WISHLIST
-- ============================================================
CREATE TABLE wishlist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY user_product (user_id, product_id)
);

-- ============================================================
-- COLORS
-- ============================================================
CREATE TABLE colors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    hex VARCHAR(50) NOT NULL,
    border VARCHAR(50) DEFAULT '#FFFFFF'
);

-- ============================================================
-- SIZES
-- ============================================================
CREATE TABLE sizes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- ============================================================
-- PRODUCT_COLORS (Many-to-Many)
-- ============================================================
CREATE TABLE product_colors (
    product_id INT NOT NULL,
    color_id INT NOT NULL,
    PRIMARY KEY (product_id, color_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (color_id) REFERENCES colors(id) ON DELETE CASCADE
);

-- ============================================================
-- PRODUCT_SIZES (Many-to-Many)
-- ============================================================
CREATE TABLE product_sizes (
    product_id INT NOT NULL,
    size_id INT NOT NULL,
    PRIMARY KEY (product_id, size_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (size_id) REFERENCES sizes(id) ON DELETE CASCADE
);

-- ============================================================
-- DATA: Categories
-- ============================================================
INSERT INTO categories (name, image, slug) VALUES
('Shirts',          'shirts.png',      'shirts'),
('T-Shirts',        'tshirts.png',     't-shirts'),
('Underwear',       'underwear.png',   'underwear'),
('Underskirts',     'underskirts.png', 'underskirts'),
('Mattress Covers', 'mattress.png',    'mattress-covers');

-- ============================================================
-- DATA: Products
-- ============================================================
INSERT INTO products (name, description, price, category_id, image, stock, is_featured) VALUES
('Classic Oxford Shirt',           'Premium cotton Oxford shirt with a tailored fit, mother-of-pearl buttons, and reinforced collar.',                3950.00, 1, 'shirts.png',      0,  TRUE),
('Premium Crew Neck T-Shirt',      'Ultra-soft combed cotton crew neck tee with a relaxed modern fit and durable stitching.',                            2450.00, 2, 'tshirts.png',     0,  TRUE),
('Comfort Cotton Briefs Pack',     'Pack of 3 breathable cotton briefs with elastic waistband and premium comfort lining.',                               1850.00, 3, 'underwear.png',   0,  TRUE),
('Silk Slip Underskirt',           'Elegant silk-blend slip underskirt with lace trim detailing and adjustable straps.',                                   2950.00, 4, 'underskirts.png', 0,  TRUE),
('Waterproof Mattress Protector',  'Premium waterproof mattress cover with hypoallergenic cotton terry top and deep pocket elastic fit.',                  4500.00, 5, 'mattress.png',    0,  TRUE),
('Women''s Shirt',                 'Stylish and comfortable women''s shirt, perfect for casual and formal wear.',                                         3500.00, 1, 'shirts.png',      25, FALSE),
('Women''s T-Shirt',               'Soft cotton t-shirt designed for everyday comfort and style.',                                                        2500.00, 2, 'tshirts.png',     30, FALSE),
('Women''s Underskirt',            'Elegant underskirt that adds volume and grace to your traditional outfits.',                                          4500.00, 4, 'underskirts.png', 20, FALSE),
('Women''s Underwear',             'Premium quality underwear crafted for maximum comfort and breathability.',                                            1800.00, 3, 'underwear.png',   50, FALSE),
('Mattress',                       'High-quality mattress for a restful and rejuvenating sleep experience.',                                             15000.00,5, 'mattress.png',    10, FALSE);

-- ============================================================
-- DATA: Colors
-- ============================================================
INSERT INTO colors (name, hex, border) VALUES
('Midnight Black', '#050505', '#2A2A2A'),
('Royal Gold',     '#DCC7A3', '#FFFFFF'),
('Ivory White',    '#F4F4F4', '#E0E0E0'),
('Espresso Brown', '#4E3629', '#6A4E3D');

-- ============================================================
-- DATA: Sizes
-- ============================================================
INSERT INTO sizes (name) VALUES
('XS'), ('S'), ('M'), ('L'), ('XL');

-- ============================================================
-- DATA: Product-Color assignments (all products get all colors)
-- ============================================================
INSERT INTO product_colors (product_id, color_id)
SELECT p.id, c.id FROM products p, colors c;

-- ============================================================
-- DATA: Product-Size assignments (all products get all sizes)
-- ============================================================
INSERT INTO product_sizes (product_id, size_id)
SELECT p.id, s.id FROM products p, sizes s;

-- ============================================================
-- DATA: Admin User
-- ============================================================
-- Default password for both users: admin123
INSERT INTO users (full_name, email, password, is_admin, address) VALUES
('Lady Seraphina',   'seraphina@luxury.com', '$2y$10$FN0zDfXPfpAuZviudH085OCte0E4JD6UL1klZTl0/ZQMQXFL39zVi', FALSE, '123 Luxury Lane, Colombo'),
('Admin MN Direct',  'admin@mndirect.com',   '$2y$10$ZKl.5DPPMhwIvfqqM.bDuOpngyARIQGhrhi/dPBSQ2jbdYoStwAIa', TRUE,  'MN DIRECT Head Office, Colombo');
