-- SILK SAGAR Database Schema
-- SQLite compatible

-- Categories table
CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    display_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Subcategories table
CREATE TABLE IF NOT EXISTS subcategories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER NOT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    display_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- Products table
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    subcategory_id INTEGER NOT NULL,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    price_current DECIMAL(10,2) NOT NULL,
    price_original DECIMAL(10,2),
    badge VARCHAR(50),
    rating_stars INTEGER DEFAULT 5,
    rating_count INTEGER DEFAULT 0,
    display_order INTEGER DEFAULT 0,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subcategory_id) REFERENCES subcategories(id) ON DELETE CASCADE
);

-- Product images table
CREATE TABLE IF NOT EXISTS product_images (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    is_primary INTEGER DEFAULT 0,
    display_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Admin users table
CREATE TABLE IF NOT EXISTS admin_users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME
);

-- Insert default categories
INSERT OR IGNORE INTO categories (id, name, slug, display_order) VALUES
(1, 'Scarves', 'scarves', 1),
(2, 'Fabrics', 'fabrics', 2),
(3, 'Yarns', 'yarns', 3),
(4, 'Fibers', 'fibers', 4);

-- Insert default subcategories for Yarns
INSERT OR IGNORE INTO subcategories (category_id, name, slug, description, display_order) VALUES
(3, 'Cotton Yarns', 'cotton-yarn', 'Premium cotton yarns for all your textile needs', 1),
(3, 'Recycled Linen Yarns', 'recycled-linen-yarn', 'Eco-friendly recycled linen yarns with sustainable quality', 2),
(3, 'Recycled Linen mixed with Recycled Silk', 'recycled-linen-silk-mixed', 'Premium blend of recycled linen and silk for luxurious eco-friendly yarns', 3),
(3, 'Recycled Silk Yarn', 'recycled-silk-yarn', 'Sustainable recycled silk yarns', 4),
(3, 'Recycled Banana Silk Yarn', 'recycled-banana-silk-yarn', 'Unique banana silk blend yarns', 5),
(3, 'Linen Yarn', 'linen-yarn', 'Natural linen yarns', 6),
(3, 'Silk Yarn', 'silk-yarn', 'Premium silk yarns', 7);

-- Create default admin user (username: admin, password: admin123)
-- Password will be set by initialization script
-- INSERT OR IGNORE INTO admin_users (username, password_hash, email) VALUES
-- ('admin', 'HASH_WILL_BE_SET_BY_SCRIPT', 'admin@silksagar.com');


-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_subcategories_category ON subcategories(category_id);
CREATE INDEX IF NOT EXISTS idx_products_subcategory ON products(subcategory_id);
CREATE INDEX IF NOT EXISTS idx_products_active ON products(is_active);
CREATE INDEX IF NOT EXISTS idx_product_images_product ON product_images(product_id);
CREATE INDEX IF NOT EXISTS idx_product_images_primary ON product_images(is_primary);