-- ============================================================
-- Menswear E-Commerce Database Setup
-- Run this SQL in your MySQL/phpMyAdmin to create the database
-- ============================================================

CREATE DATABASE IF NOT EXISTS menswear_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE menswear_db;

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

-- Products Table
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,
    sale_price DECIMAL(10,2) DEFAULT NULL,
    stock INT DEFAULT 0,
    image VARCHAR(255),
    badge VARCHAR(50) DEFAULT NULL,  -- 'New', 'Sale', 'Hot'
    is_featured TINYINT(1) DEFAULT 0,
    is_bestseller TINYINT(1) DEFAULT 0,
    rating DECIMAL(2,1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    country VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders Table
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    session_id VARCHAR(100),
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_name VARCHAR(150),
    shipping_email VARCHAR(150),
    shipping_phone VARCHAR(20),
    shipping_address TEXT,
    shipping_city VARCHAR(100),
    shipping_country VARCHAR(100),
    payment_method VARCHAR(50) DEFAULT 'cod',
    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 Table
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    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 CASCADE
);

-- Blog Posts Table
CREATE TABLE IF NOT EXISTS blog_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    excerpt TEXT,
    content LONGTEXT,
    image VARCHAR(255),
    published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- Sample Data
-- ============================================================

INSERT INTO categories (name, slug, description) VALUES
('Clothing', 'clothing', 'Men''s clothing collection'),
('Shoes', 'shoes', 'Premium footwear'),
('Accessories', 'accessories', 'Bags, caps and more');

INSERT INTO products (category_id, name, slug, description, price, sale_price, stock, image, badge, is_featured, is_bestseller, rating) VALUES
(1, 'Piqué Biker Jacket', 'pique-biker-jacket', 'Premium quality biker jacket with a modern twist. Made from finest materials.', 67.24, NULL, 25, 'product-1.jpg', 'New', 1, 1, 4.5),
(1, 'Slim Fit Blazer', 'slim-fit-blazer', 'Classic slim fit blazer perfect for formal and casual occasions.', 89.99, NULL, 18, 'product-2.jpg', NULL, 1, 1, 4.0),
(2, 'Ankle Boots', 'ankle-boots', 'Stylish ankle boots crafted from genuine leather.', 98.49, 79.99, 12, 'product-6.jpg', 'Sale', 1, 0, 4.5),
(3, 'Multi-pocket Chest Bag', 'multi-pocket-chest-bag', 'Versatile chest bag with multiple compartments.', 43.48, 29.99, 30, 'product-3.jpg', 'Sale', 1, 1, 4.0),
(3, 'Diagonal Textured Cap', 'diagonal-textured-cap', 'Trendy cap with diagonal texture pattern.', 60.90, NULL, 50, 'product-4.jpg', NULL, 0, 1, 3.5),
(3, 'Leather Backpack', 'leather-backpack', 'Durable leather backpack with spacious compartments.', 31.37, NULL, 20, 'product-5.jpg', NULL, 0, 0, 3.0),
(1, 'T-shirt Contrast Pocket', 'tshirt-contrast-pocket', 'Modern T-shirt with contrast pocket detail.', 49.66, NULL, 40, 'product-7.jpg', NULL, 0, 1, 4.0),
(3, 'Basic Flowing Scarf', 'basic-flowing-scarf', 'Lightweight flowing scarf for all seasons.', 26.28, NULL, 35, 'product-8.jpg', NULL, 0, 0, 3.5);

INSERT INTO blog_posts (title, slug, excerpt, content, image, published_at) VALUES
('What Curling Irons Are The Best Ones', 'what-curling-irons-are-best', 'Discover the top styling tools for modern men...', 'Full blog content here...', 'blog-1.jpg', '2024-02-16 00:00:00'),
('Eternity Bands Do Last Forever', 'eternity-bands-do-last-forever', 'How to style accessories that stand the test of time...', 'Full blog content here...', 'blog-2.jpg', '2024-02-21 00:00:00'),
('The Health Benefits Of Sunglasses', 'health-benefits-of-sunglasses', 'Why sunglasses are more than just a fashion statement...', 'Full blog content here...', 'blog-3.jpg', '2024-02-28 00:00:00');