-- Database bootstrap
CREATE DATABASE IF NOT EXISTS novashop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE novashop;

-- Category catalog
CREATE TABLE IF NOT EXISTS categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    description TEXT,
    image_url VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Backfill the image_url column for older databases
SET @needs_category_image := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'categories'
      AND COLUMN_NAME = 'image_url'
);

SET @category_image_ddl := IF(
    @needs_category_image = 0,
    'ALTER TABLE categories ADD COLUMN image_url VARCHAR(255) NULL AFTER description',
    'SELECT 1'
);

PREPARE category_stmt FROM @category_image_ddl;
EXECUTE category_stmt;
DEALLOCATE PREPARE category_stmt;

-- Product catalog
CREATE TABLE IF NOT EXISTS products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    name VARCHAR(160) NOT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    brand VARCHAR(120) NULL,
    price DECIMAL(10,2) NOT NULL,
    short_description VARCHAR(255),
    description TEXT,
    image_url VARCHAR(255) NOT NULL,
    is_featured TINYINT(1) DEFAULT 0,
    stock INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB;

-- Orders
CREATE TABLE IF NOT EXISTS orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(160) NOT NULL,
    email VARCHAR(160) NOT NULL,
    phone VARCHAR(60) NOT NULL,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(160) NOT NULL,
    postal_code VARCHAR(30) NOT NULL,
    payment_method ENUM('card','cod') DEFAULT 'card',
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending','paid','fulfilled','cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NULL,
    product_name VARCHAR(160) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;

-- Seed categories
INSERT INTO categories (name, slug, description, image_url) VALUES
    ('Electronics', 'electronics', 'Tech gadgets and accessories', 'https://images.unsplash.com/photo-1518770660439-4636190af475'),
    ('Home & Living', 'home-living', 'Furniture and decor', 'https://images.unsplash.com/photo-1505693416388-ac5ce068fe85'),
    ('Apparel', 'apparel', 'Mens and womens fashion', 'https://images.unsplash.com/photo-1445205170230-053b83016050'),
    ('Outdoors', 'outdoors', 'Gear for adventures', 'https://images.unsplash.com/photo-1441974231531-c6227db76b6e')
ON DUPLICATE KEY UPDATE name = VALUES(name), image_url = VALUES(image_url), description = VALUES(description);

-- Seed products
INSERT INTO products (category_id, name, slug, brand, price, short_description, description, image_url, is_featured, stock) VALUES
    (1, 'Noise Cancelling Headphones', 'nc-headphones', 'SonicPulse', 199.00, '40h battery life, USB-C charging.', 'Experience immersive sound with adaptive ANC and plush comfort.', 'https://images.unsplash.com/photo-1517048676732-d65bc937f952', 1, 30),
    (1, 'Smart Fitness Watch', 'smart-watch', 'PulseOne', 149.00, 'AMOLED display, 7-day battery.', 'Track workouts, sleep, and notifications with waterproof protection.', 'https://images.unsplash.com/photo-1511707171634-5f897ff02aa9', 1, 50),
    (2, 'Minimalist Lounge Chair', 'lounge-chair', 'NordicHome', 279.00, 'Vegan leather, oak frame.', 'Comfort-first seating with clean modern lines for any living room.', 'https://images.unsplash.com/photo-1481277542470-605612bd2d61', 1, 10),
    (2, 'Ceramic Serveware Set', 'serveware-set', 'Atelier Clay', 89.00, 'Set of 6 hand-glazed pieces.', 'Heat-safe ceramics crafted for daily meals and hosting.', 'https://images.unsplash.com/photo-1504754524776-8f4f37790ca0', 0, 40),
    (3, 'Organic Cotton Hoodie', 'cotton-hoodie', 'Fieldfare', 69.00, 'Unisex relaxed fit.', 'Sustainably sourced hoodie with reinforced seams and cozy interior.', 'https://images.unsplash.com/photo-1521572267360-ee0c2909d518', 1, 60),
    (4, 'Trail Backpack 28L', 'trail-backpack', 'SummitWorks', 129.00, 'Water-resistant, 28L.', 'All-day comfort with ventilated straps, fits 15" laptop sleeve.', 'https://images.unsplash.com/photo-1500530855697-b586d89ba3ee', 0, 25)
ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price), stock = VALUES(stock);

