-- Tenant-specific Database Schema

-- Note: This schema is created in each tenant's database

-- Products (Tenant-specific)
CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    short_description VARCHAR(500),
    price DECIMAL(10,2) NOT NULL,
    cost DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    reorder_level INT DEFAULT 10,
    category_id INT,
    brand VARCHAR(100),
    weight DECIMAL(8,2),
    dimensions JSON,
    attributes JSON,
    variants JSON,
    images JSON,
    seo_title VARCHAR(200),
    seo_description VARCHAR(500),
    seo_keywords VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    is_virtual BOOLEAN DEFAULT FALSE,
    has_variants BOOLEAN DEFAULT FALSE,
    times_ordered INT DEFAULT 0,
    units_sold INT DEFAULT 0,
    revenue_generated DECIMAL(12,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sku (sku),
    INDEX idx_category (category_id),
    INDEX idx_active (is_active),
    INDEX idx_featured (is_featured),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Product Variants
CREATE TABLE IF NOT EXISTS product_variants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    variant_id VARCHAR(50) NOT NULL,
    sku VARCHAR(50),
    price DECIMAL(10,2),
    cost DECIMAL(10,2),
    stock_quantity INT DEFAULT 0,
    attributes JSON,
    images JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_product_variant (product_id, variant_id),
    INDEX idx_product (product_id),
    INDEX idx_variant (variant_id)
) ENGINE=InnoDB;

-- Categories
CREATE TABLE IF NOT EXISTS categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    parent_id INT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    image VARCHAR(255),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE,
    INDEX idx_parent (parent_id),
    INDEX idx_slug (slug),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- Product Categories (Many-to-Many)
CREATE TABLE IF NOT EXISTS product_categories (
    product_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (product_id, category_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_category (category_id)
) ENGINE=InnoDB;

-- Tags
CREATE TABLE IF NOT EXISTS tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_slug (slug)
) ENGINE=InnoDB;

-- Product Tags (Many-to-Many)
CREATE TABLE IF NOT EXISTS product_tags (
    product_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (product_id, tag_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_tag (tag_id)
) ENGINE=InnoDB;

-- Product Images (Separate table for better management)
CREATE TABLE IF NOT EXISTS product_images (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    image_url VARCHAR(500) NOT NULL,
    alt_text VARCHAR(200),
    sort_order INT DEFAULT 0,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_primary (is_primary)
) ENGINE=InnoDB;

-- Product Inventory Log
CREATE TABLE IF NOT EXISTS inventory_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    variant_id VARCHAR(50),
    change_type ENUM('in', 'out', 'adjustment', 'transfer') NOT NULL,
    quantity INT NOT NULL,
    previous_quantity INT NOT NULL,
    new_quantity INT NOT NULL,
    reference_type VARCHAR(50),
    reference_id INT,
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_reference (reference_type, reference_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Product Price History
CREATE TABLE IF NOT EXISTS price_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    old_price DECIMAL(10,2) NOT NULL,
    new_price DECIMAL(10,2) NOT NULL,
    change_reason VARCHAR(200),
    changed_by INT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_changed (changed_at)
) ENGINE=InnoDB;

-- Suppliers
CREATE TABLE IF NOT EXISTS suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    contact_person VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    tax_id VARCHAR(50),
    payment_terms VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- Supplier Products
CREATE TABLE IF NOT EXISTS supplier_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_id INT NOT NULL,
    product_id INT NOT NULL,
    supplier_sku VARCHAR(50),
    cost_price DECIMAL(10,2) NOT NULL,
    min_order_quantity INT DEFAULT 1,
    lead_time_days INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_supplier_product (supplier_id, product_id),
    INDEX idx_supplier (supplier_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB;

-- Purchase Orders
CREATE TABLE IF NOT EXISTS purchase_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    po_number VARCHAR(50) UNIQUE NOT NULL,
    supplier_id INT NOT NULL,
    status ENUM('draft', 'ordered', 'received', 'cancelled', 'partially_received') DEFAULT 'draft',
    total_amount DECIMAL(10,2) NOT NULL,
    order_date DATE NOT NULL,
    expected_delivery_date DATE,
    actual_delivery_date DATE,
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,
    INDEX idx_supplier (supplier_id),
    INDEX idx_status (status),
    INDEX idx_order_date (order_date)
) ENGINE=InnoDB;

-- Purchase Order Items
CREATE TABLE IF NOT EXISTS purchase_order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_order_id INT NOT NULL,
    product_id INT NOT NULL,
    variant_id VARCHAR(50),
    quantity INT NOT NULL,
    unit_cost DECIMAL(10,2) NOT NULL,
    total_cost DECIMAL(10,2) NOT NULL,
    received_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_purchase_order (purchase_order_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB;

-- Warehouses
CREATE TABLE IF NOT EXISTS warehouses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    address TEXT,
    contact_person VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- Warehouse Stock
CREATE TABLE IF NOT EXISTS warehouse_stock (
    id INT PRIMARY KEY AUTO_INCREMENT,
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    variant_id VARCHAR(50),
    quantity INT NOT NULL DEFAULT 0,
    reserved_quantity INT NOT NULL DEFAULT 0,
    available_quantity INT GENERATED ALWAYS AS (quantity - reserved_quantity) STORED,
    reorder_point INT DEFAULT 10,
    max_stock INT,
    last_counted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_warehouse_product (warehouse_id, product_id, variant_id),
    INDEX idx_warehouse (warehouse_id),
    INDEX idx_product (product_id),
    INDEX idx_available (available_quantity)
) ENGINE=InnoDB;

-- Stock Transfers
CREATE TABLE IF NOT EXISTS stock_transfers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transfer_number VARCHAR(50) UNIQUE NOT NULL,
    from_warehouse_id INT NOT NULL,
    to_warehouse_id INT NOT NULL,
    status ENUM('pending', 'in_transit', 'completed', 'cancelled') DEFAULT 'pending',
    total_items INT NOT NULL,
    notes TEXT,
    requested_by INT,
    approved_by INT,
    shipped_at TIMESTAMP NULL,
    received_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (from_warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
    FOREIGN KEY (to_warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
    INDEX idx_from_warehouse (from_warehouse_id),
    INDEX idx_to_warehouse (to_warehouse_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- Stock Transfer Items
CREATE TABLE IF NOT EXISTS stock_transfer_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    stock_transfer_id INT NOT NULL,
    product_id INT NOT NULL,
    variant_id VARCHAR(50),
    quantity INT NOT NULL,
    shipped_quantity INT DEFAULT 0,
    received_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (stock_transfer_id) REFERENCES stock_transfers(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_stock_transfer (stock_transfer_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB;

-- Stock Adjustments
CREATE TABLE IF NOT EXISTS stock_adjustments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    adjustment_number VARCHAR(50) UNIQUE NOT NULL,
    warehouse_id INT NOT NULL,
    adjustment_type ENUM('addition', 'deduction', 'correction') NOT NULL,
    reason ENUM('damaged', 'expired', 'lost', 'found', 'count_error', 'other') NOT NULL,
    total_value DECIMAL(10,2) NOT NULL,
    notes TEXT,
    approved_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
    INDEX idx_warehouse (warehouse_id),
    INDEX idx_adjustment_type (adjustment_type)
) ENGINE=InnoDB;

-- Stock Adjustment Items
CREATE TABLE IF NOT EXISTS stock_adjustment_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    stock_adjustment_id INT NOT NULL,
    product_id INT NOT NULL,
    variant_id VARCHAR(50),
    quantity INT NOT NULL,
    cost_price DECIMAL(10,2) NOT NULL,
    total_value DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (stock_adjustment_id) REFERENCES stock_adjustments(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_stock_adjustment (stock_adjustment_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB;

-- Product Reviews
CREATE TABLE IF NOT EXISTS reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_id INT,
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(200),
    comment TEXT,
    is_approved BOOLEAN DEFAULT FALSE,
    helpful_count INT DEFAULT 0,
    not_helpful_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_customer (customer_id),
    INDEX idx_approved (is_approved),
    INDEX idx_rating (rating)
) ENGINE=InnoDB;

-- Review Helpful Votes
CREATE TABLE IF NOT EXISTS review_votes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    review_id INT NOT NULL,
    customer_id INT NOT NULL,
    is_helpful BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE,
    UNIQUE KEY unique_review_vote (review_id, customer_id),
    INDEX idx_review (review_id),
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB;

-- Product Questions & Answers
CREATE TABLE IF NOT EXISTS product_questions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    question TEXT NOT NULL,
    is_answered BOOLEAN DEFAULT FALSE,
    is_visible BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_customer (customer_id),
    INDEX idx_answered (is_answered)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS question_answers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    question_id INT NOT NULL,
    answered_by ENUM('seller', 'customer', 'admin') NOT NULL,
    answerer_id INT,
    answer TEXT NOT NULL,
    is_official BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES product_questions(id) ON DELETE CASCADE,
    INDEX idx_question (question_id),
    INDEX idx_answered_by (answered_by)
) ENGINE=InnoDB;

-- Product Collections
CREATE TABLE IF NOT EXISTS collections (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    image VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- Collection Products
CREATE TABLE IF NOT EXISTS collection_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    collection_id INT NOT NULL,
    product_id INT NOT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (collection_id) REFERENCES collections(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_collection_product (collection_id, product_id),
    INDEX idx_collection (collection_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB;

-- Product Attributes
CREATE TABLE IF NOT EXISTS attributes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    type ENUM('text', 'number', 'select', 'multiselect', 'boolean', 'date') DEFAULT 'text',
    is_required BOOLEAN DEFAULT FALSE,
    is_filterable BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_filterable (is_filterable)
) ENGINE=InnoDB;

-- Attribute Values
CREATE TABLE IF NOT EXISTS attribute_values (
    id INT PRIMARY KEY AUTO_INCREMENT,
    attribute_id INT NOT NULL,
    value VARCHAR(200) NOT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON DELETE CASCADE,
    UNIQUE KEY unique_attribute_value (attribute_id, value),
    INDEX idx_attribute (attribute_id)
) ENGINE=InnoDB;

-- Product Attribute Values
CREATE TABLE IF NOT EXISTS product_attribute_values (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    attribute_id INT NOT NULL,
    attribute_value_id INT,
    value_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON DELETE CASCADE,
    FOREIGN KEY (attribute_value_id) REFERENCES attribute_values(id) ON DELETE CASCADE,
    UNIQUE KEY unique_product_attribute (product_id, attribute_id),
    INDEX idx_product (product_id),
    INDEX idx_attribute (attribute_id)
) ENGINE=InnoDB;

-- Product Downloads (for digital products)
CREATE TABLE IF NOT EXISTS product_downloads (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT,
    file_type VARCHAR(50),
    download_limit INT,
    expiry_days INT,
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- Download Log
CREATE TABLE IF NOT EXISTS download_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_download_id INT NOT NULL,
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_download_id) REFERENCES product_downloads(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_product_download (product_download_id),
    INDEX idx_order (order_id),
    INDEX idx_customer (customer_id),
    INDEX idx_downloaded (downloaded_at)
) ENGINE=InnoDB;

-- Product Bundles
CREATE TABLE IF NOT EXISTS product_bundles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    description TEXT,
    bundle_price DECIMAL(10,2) NOT NULL,
    regular_price DECIMAL(10,2) NOT NULL,
    savings DECIMAL(10,2) GENERATED ALWAYS AS (regular_price - bundle_price) STORED,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- Bundle Items
CREATE TABLE IF NOT EXISTS bundle_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bundle_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bundle_id) REFERENCES product_bundles(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_bundle_product (bundle_id, product_id),
    INDEX idx_bundle (bundle_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB;

-- Product Related Products
CREATE TABLE IF NOT EXISTS related_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    related_product_id INT NOT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (related_product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_related_product (product_id, related_product_id),
    INDEX idx_product (product_id),
    INDEX idx_related (related_product_id)
) ENGINE=InnoDB;

-- Product Cross-sells
CREATE TABLE IF NOT EXISTS cross_sell_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    cross_sell_product_id INT NOT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (cross_sell_product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_cross_sell (product_id, cross_sell_product_id),
    INDEX idx_product (product_id),
    INDEX idx_cross_sell (cross_sell_product_id)
) ENGINE=InnoDB;

-- Product Up-sells
CREATE TABLE IF NOT EXISTS upsell_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    upsell_product_id INT NOT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (upsell_product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_upsell (product_id, upsell_product_id),
    INDEX idx_product (product_id),
    INDEX idx_upsell (upsell_product_id)
) ENGINE=InnoDB;

-- Product View History
CREATE TABLE IF NOT EXISTS product_views (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT,
    session_id VARCHAR(128),
    ip_address VARCHAR(45),
    user_agent TEXT,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_customer (customer_id),
    INDEX idx_viewed (viewed_at)
) ENGINE=InnoDB;

-- Product Search Log
CREATE TABLE IF NOT EXISTS search_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    search_term VARCHAR(200) NOT NULL,
    customer_id INT,
    session_id VARCHAR(128),
    ip_address VARCHAR(45),
    results_count INT DEFAULT 0,
    searched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_search_term (search_term),
    INDEX idx_customer (customer_id),
    INDEX idx_searched (searched_at)
) ENGINE=InnoDB;

-- Product Wishlist
CREATE TABLE IF NOT EXISTS wishlist (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_customer_product (customer_id, product_id),
    INDEX idx_customer (customer_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB;

-- Product Comparison
CREATE TABLE IF NOT EXISTS product_comparisons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(128) NOT NULL,
    customer_id INT,
    product_ids JSON NOT NULL,
    compared_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session (session_id),
    INDEX idx_customer (customer_id),
    INDEX idx_compared (compared_at)
) ENGINE=InnoDB;

-- Product Email Alerts (for back in stock, price drop)
CREATE TABLE IF NOT EXISTS product_alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    alert_type ENUM('back_in_stock', 'price_drop') NOT NULL,
    email VARCHAR(100) NOT NULL,
    is_sent BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    UNIQUE KEY unique_product_alert (product_id, customer_id, alert_type),
    INDEX idx_product (product_id),
    INDEX idx_customer (customer_id),
    INDEX idx_sent (is_sent)
) ENGINE=InnoDB;

-- Product Rating Summary
CREATE TABLE IF NOT EXISTS product_rating_summary (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    total_reviews INT DEFAULT 0,
    average_rating DECIMAL(3,2) DEFAULT 0.00,
    rating_1 INT DEFAULT 0,
    rating_2 INT DEFAULT 0,
    rating_3 INT DEFAULT 0,
    rating_4 INT DEFAULT 0,
    rating_5 INT DEFAULT 0,
    last_calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_product (product_id),
    INDEX idx_product (product_id),
    INDEX idx_rating (average_rating)
) ENGINE=InnoDB;

-- Trigger to update rating summary
DELIMITER //

CREATE TRIGGER update_rating_summary AFTER INSERT ON reviews
FOR EACH ROW
BEGIN
    INSERT INTO product_rating_summary (product_id)
    VALUES (NEW.product_id)
    ON DUPLICATE KEY UPDATE
        total_reviews = total_reviews + 1,
        average_rating = (
            SELECT AVG(rating) 
            FROM reviews 
            WHERE product_id = NEW.product_id 
            AND is_approved = 1
        ),
        rating_1 = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = NEW.product_id 
            AND rating = 1 
            AND is_approved = 1
        ),
        rating_2 = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = NEW.product_id 
            AND rating = 2 
            AND is_approved = 1
        ),
        rating_3 = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = NEW.product_id 
            AND rating = 3 
            AND is_approved = 1
        ),
        rating_4 = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = NEW.product_id 
            AND rating = 4 
            AND is_approved = 1
        ),
        rating_5 = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = NEW.product_id 
            AND rating = 5 
            AND is_approved = 1
        ),
        last_calculated_at = NOW();
END//

CREATE TRIGGER update_rating_summary_delete AFTER DELETE ON reviews
FOR EACH ROW
BEGIN
    UPDATE product_rating_summary prs
    SET
        total_reviews = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = OLD.product_id 
            AND is_approved = 1
        ),
        average_rating = (
            SELECT AVG(rating) 
            FROM reviews 
            WHERE product_id = OLD.product_id 
            AND is_approved = 1
        ),
        rating_1 = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = OLD.product_id 
            AND rating = 1 
            AND is_approved = 1
        ),
        rating_2 = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = OLD.product_id 
            AND rating = 2 
            AND is_approved = 1
        ),
        rating_3 = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = OLD.product_id 
            AND rating = 3 
            AND is_approved = 1
        ),
        rating_4 = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = OLD.product_id 
            AND rating = 4 
            AND is_approved = 1
        ),
        rating_5 = (
            SELECT COUNT(*) 
            FROM reviews 
            WHERE product_id = OLD.product_id 
            AND rating = 5 
            AND is_approved = 1
        ),
        last_calculated_at = NOW()
    WHERE prs.product_id = OLD.product_id;
END//

DELIMITER ;

-- Product Sales Analytics View
CREATE OR REPLACE VIEW product_sales_analytics AS
SELECT 
    p.id,
    p.sku,
    p.name,
    p.price,
    p.cost,
    p.stock_quantity,
    COALESCE(SUM(oi.quantity), 0) as total_units_sold,
    COALESCE(SUM(oi.total_price), 0) as total_revenue,
    COALESCE(SUM(oi.total_price) - COALESCE(SUM(oi.quantity * p.cost), 0), 0) as total_profit,
    COUNT(DISTINCT o.id) as total_orders,
    COUNT(DISTINCT o.customer_id) as unique_customers,
    COALESCE(AVG(oi.quantity), 0) as avg_quantity_per_order,
    COALESCE(MAX(o.created_at), p.created_at) as last_sale_date
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
GROUP BY p.id;

-- Low Stock Alert View
CREATE OR REPLACE VIEW low_stock_alert AS
SELECT 
    p.id,
    p.sku,
    p.name,
    p.stock_quantity,
    p.reorder_level,
    ws.available_quantity,
    ws.warehouse_id,
    w.name as warehouse_name,
    CASE 
        WHEN p.stock_quantity <= 0 THEN 'Out of Stock'
        WHEN p.stock_quantity <= p.reorder_level THEN 'Low Stock'
        ELSE 'In Stock'
    END as stock_status
FROM products p
LEFT JOIN warehouse_stock ws ON p.id = ws.product_id
LEFT JOIN warehouses w ON ws.warehouse_id = w.id
WHERE p.stock_quantity <= p.reorder_level
ORDER BY p.stock_quantity ASC;

-- Product Performance View
CREATE OR REPLACE VIEW product_performance AS
SELECT 
    p.id,
    p.name,
    p.sku,
    pv.total_views,
    wl.total_wishlists,
    rv.total_reviews,
    rv.average_rating,
    sa.total_units_sold,
    sa.total_revenue,
    sa.total_profit,
    ROUND(
        CASE 
            WHEN pv.total_views > 0 THEN (sa.total_units_sold / pv.total_views) * 100
            ELSE 0
        END, 2
    ) as conversion_rate
FROM products p
LEFT JOIN (
    SELECT product_id, COUNT(*) as total_views
    FROM product_views
    GROUP BY product_id
) pv ON p.id = pv.product_id
LEFT JOIN (
    SELECT product_id, COUNT(*) as total_wishlists
    FROM wishlist
    GROUP BY product_id
) wl ON p.id = wl.product_id
LEFT JOIN (
    SELECT product_id, COUNT(*) as total_reviews, AVG(rating) as average_rating
    FROM reviews
    WHERE is_approved = 1
    GROUP BY product_id
) rv ON p.id = rv.product_id
LEFT JOIN product_sales_analytics sa ON p.id = sa.id;

-- Insert default warehouse
INSERT INTO warehouses (name, code, is_default, is_active) VALUES
('Main Warehouse', 'WH001', TRUE, TRUE);

-- Create indexes for performance
CREATE INDEX idx_products_composite ON products (is_active, is_featured, created_at);
CREATE INDEX idx_inventory_log_composite ON inventory_log (product_id, created_at);
CREATE INDEX idx_reviews_composite ON reviews (product_id, is_approved, rating);
CREATE INDEX idx_wishlist_composite ON wishlist (customer_id, product_id);
CREATE INDEX idx_product_views_composite ON product_views (product_id, viewed_at);
CREATE INDEX idx_search_log_composite ON search_log (search_term, searched_at);

-- Create events for data maintenance
DELIMITER //

CREATE EVENT IF NOT EXISTS update_product_stats_daily
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '02:00:00')
DO
BEGIN
    -- Update product statistics
    UPDATE products p
    SET 
        times_ordered = (
            SELECT COUNT(DISTINCT oi.order_id)
            FROM order_items oi
            WHERE oi.product_id = p.id
        ),
        units_sold = (
            SELECT COALESCE(SUM(oi.quantity), 0)
            FROM order_items oi
            WHERE oi.product_id = p.id
        ),
        revenue_generated = (
            SELECT COALESCE(SUM(oi.total_price), 0)
            FROM order_items oi
            WHERE oi.product_id = p.id
        ),
        updated_at = NOW();
END//

CREATE EVENT IF NOT EXISTS cleanup_old_data
ON SCHEDULE EVERY 1 WEEK
STARTS TIMESTAMP(CURRENT_DATE, '03:00:00')
DO
BEGIN
    -- Cleanup old product views (older than 90 days)
    DELETE FROM product_views WHERE viewed_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
    
    -- Cleanup old search logs (older than 180 days)
    DELETE FROM search_log WHERE searched_at < DATE_SUB(NOW(), INTERVAL 180 DAY);
    
    -- Cleanup old product comparisons (older than 30 days)
    DELETE FROM product_comparisons WHERE compared_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END//

DELIMITER ;

-- Enable event scheduler
SET GLOBAL event_scheduler = ON;