-- Fashion Store Database Schema
-- Run this via setup.php or directly in your MySQL manager (PhpMyAdmin)

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    image_url VARCHAR(2048),
    featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Clothing Store Seed Data
INSERT IGNORE INTO categories (name, slug, description) VALUES 
('Menswear', 'menswear', 'Premium shirts, denims and formal wear'),
('Womenswear', 'womenswear', 'Elegant dresses and daily essentials'),
('Accessories', 'accessories', 'Branded belts, bags and more');

INSERT IGNORE INTO products (category_id, name, slug, description, price, stock, image_url, featured) VALUES 
(1, 'Premium Cotton Linen Shirt', 'linen-shirt', 'Breathable summer essential, available in all sizes.', 45.00, 100, 'https://images.unsplash.com/photo-1596755094514-f87e34085b2c?auto=format&fit=crop&q=80&w=600', 1),
(2, 'Silk Evening Gown', 'silk-gown', 'Hand-stitched elegant evening wear.', 120.00, 15, 'https://images.unsplash.com/photo-1539008835279-43469393e161?auto=format&fit=crop&q=80&w=600', 1),
(1, 'Distressed Blue Denim', 'blue-denim', 'Classic fit durable denim with modern wash.', 65.00, 45, 'https://images.unsplash.com/photo-1542272454315-4c01d7abdf4a?auto=format&fit=crop&q=80&w=600', 1),
(3, 'Genuine Leather Belt', 'leather-belt', 'Italian leather with brushed metal buckle.', 30.00, 80, 'https://images.unsplash.com/photo-1624222247344-550fb8de7582?auto=format&fit=crop&q=80&w=600', 0);