-- ============================================
-- CADREE LIFESTYLE E-COMMERCE DATABASE SCHEMA
-- ============================================
-- Database: cadreelifestyle
-- Version: 2.0 (Simplified)
-- ============================================

CREATE DATABASE IF NOT EXISTS cadreelifestyle;
USE cadreelifestyle;

-- ============================================
-- 1. ADMIN & AUTHENTICATION
-- ============================================

CREATE TABLE admin_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(20),
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'admin', 'manager', 'editor') DEFAULT 'admin',
    avatar VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    last_login_at TIMESTAMP NULL,
    remember_token VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE admin_permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description VARCHAR(255),
    module VARCHAR(50)
);

CREATE TABLE admin_role_permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    role VARCHAR(50) NOT NULL,
    permission_id INT NOT NULL,
    UNIQUE KEY unique_role_permission (role, permission_id),
    FOREIGN KEY (permission_id) REFERENCES admin_permissions(id) ON DELETE CASCADE
);

CREATE TABLE admin_activity_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE
);

-- Insert default super admin (password: admin123)
INSERT INTO admin_users (name, email, password_hash, role) VALUES 
('Super Admin', 'admin@cadreelifestyle.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin');

-- Insert default permissions
INSERT INTO admin_permissions (name, slug, module) VALUES
('View Dashboard', 'view_dashboard', 'dashboard'),
('Manage Products', 'manage_products', 'products'),
('Manage Categories', 'manage_categories', 'products'),
('View Orders', 'view_orders', 'orders'),
('Manage Orders', 'manage_orders', 'orders'),
('Manage Customers', 'manage_customers', 'customers'),
('Manage Coupons', 'manage_coupons', 'marketing'),
('Manage Blog', 'manage_blog', 'content'),
('Manage Settings', 'manage_settings', 'settings'),
('Manage Stock', 'manage_stock', 'inventory'),
('View Reports', 'view_reports', 'reports'),
('Manage Admins', 'manage_admins', 'admin');

-- Assign all permissions to super_admin
INSERT INTO admin_role_permissions (role, permission_id) 
SELECT 'super_admin', id FROM admin_permissions;

-- ============================================
-- 2. CATEGORIES & SUBCATEGORIES
-- ============================================

CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(150) UNIQUE NOT NULL,
    description TEXT,
    image VARCHAR(500),
    icon VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE sub_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(150) UNIQUE NOT NULL,
    description TEXT,
    image VARCHAR(500),
    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,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- ============================================
-- 3. SIMPLIFIED PRODUCTS TABLE
-- ============================================

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sub_category_id INT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(300) UNIQUE NOT NULL,
    short_description VARCHAR(500),
    description LONGTEXT,
    product_type ENUM('simple', 'variable') DEFAULT 'simple',
    purchase_price DECIMAL(10,2) DEFAULT 0.00,
    regular_price DECIMAL(10,2) NOT NULL,
    sale_price DECIMAL(10,2),
    sku VARCHAR(100) UNIQUE,
    specifications JSON COMMENT 'Store specifications as JSON: {"material": "Cotton", "color": "Red"}',
    is_featured BOOLEAN DEFAULT FALSE,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sub_category_id) REFERENCES sub_categories(id) ON DELETE SET NULL,
    INDEX idx_product_slug (slug),
    INDEX idx_product_status (status),
    INDEX idx_product_type (product_type),
    INDEX idx_product_featured (is_featured)
);

-- Product Assets (Featured Image, Gallery Images, Videos)
CREATE TABLE product_assets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    asset_type ENUM('featured_image', 'gallery_image', 'video', 'thumbnail') NOT NULL,
    asset_url VARCHAR(500) NOT NULL,
    thumbnail_url VARCHAR(500),
    alt_text VARCHAR(255),
    title VARCHAR(255),
    sort_order INT DEFAULT 0,
    is_primary BOOLEAN DEFAULT FALSE COMMENT 'For featured_image: main display image',
    video_provider ENUM('youtube', 'vimeo', 'self_hosted', 'external') DEFAULT NULL,
    video_id VARCHAR(100) COMMENT 'YouTube/Vimeo video ID',
    file_size BIGINT COMMENT 'File size in bytes',
    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_assets_type (product_id, asset_type)
);

-- Product Variants (Only for variable products)
CREATE TABLE product_variants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    name VARCHAR(255) NOT NULL COMMENT 'Variant name e.g., "Red - XL"',
    sku VARCHAR(100) UNIQUE,
    purchase_price DECIMAL(10,2) DEFAULT 0.00,
    regular_price DECIMAL(10,2),
    sale_price DECIMAL(10,2),
    stock_quantity INT DEFAULT 0,
    low_stock_threshold INT DEFAULT 5,
    is_active BOOLEAN DEFAULT TRUE,
    is_default BOOLEAN DEFAULT FALSE,
    sort_order 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
);

-- Variant Attributes
CREATE TABLE variant_attributes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    variant_id INT NOT NULL,
    attribute_name VARCHAR(50) NOT NULL COMMENT 'e.g., color, size',
    attribute_value VARCHAR(100) NOT NULL COMMENT 'e.g., Red, XL',
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE,
    INDEX idx_variant_attribute (attribute_name, attribute_value)
);

-- Variant Assets (Images for specific variants like color swatches)
CREATE TABLE variant_assets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    variant_id INT NOT NULL,
    asset_url VARCHAR(500) NOT NULL,
    alt_text VARCHAR(255),
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE
);

-- Product Recommendations (Addon Products)
CREATE TABLE product_recommendations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL COMMENT 'Main product',
    recommended_product_id INT NOT NULL COMMENT 'Recommended/addon product',
    title VARCHAR(255) COMMENT 'e.g., "Complete the look", "Frequently bought together"',
    description TEXT,
    discount_percentage DECIMAL(5,2) DEFAULT 0.00,
    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,
    UNIQUE KEY unique_recommendation (product_id, recommended_product_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (recommended_product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- ============================================
-- 4. STOCK MANAGEMENT
-- ============================================

CREATE TABLE stock_in (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reference_number VARCHAR(100) UNIQUE NOT NULL,
    supplier_name VARCHAR(255),
    supplier_phone VARCHAR(20),
    supplier_invoice VARCHAR(100),
    notes TEXT,
    total_amount DECIMAL(12,2) DEFAULT 0.00,
    payment_status ENUM('pending', 'paid', 'partial') DEFAULT 'pending',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL
);

CREATE TABLE stock_in_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    stock_in_id INT NOT NULL,
    product_id INT NOT NULL,
    variant_id INT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(12,2) NOT NULL,
    batch_number VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (stock_in_id) REFERENCES stock_in(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL
);

-- Main Stock Ledger (Tracks all stock movements)
CREATE TABLE stock_ledger (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    variant_id INT NULL,
    transaction_type ENUM(
        'stock_in', 
        'order_placed', 
        'order_cancelled', 
        'order_returned', 
        'adjustment_add', 
        'adjustment_remove',
        'damaged'
    ) NOT NULL,
    reference_type VARCHAR(50) COMMENT 'stock_in, order, adjustment',
    reference_id INT COMMENT 'ID from source table',
    reference_number VARCHAR(100),
    quantity_change INT NOT NULL COMMENT 'Positive for addition, Negative for deduction',
    running_stock INT NOT NULL COMMENT 'Available stock after this transaction',
    unit_price DECIMAL(10,2),
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL,
    INDEX idx_stock_ledger_product (product_id, variant_id),
    INDEX idx_stock_ledger_date (created_at),
    INDEX idx_stock_ledger_type (transaction_type)
);

-- Stock Adjustments (Manual corrections)
CREATE TABLE stock_adjustments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    variant_id INT NULL,
    adjustment_type ENUM('damaged', 'lost', 'found', 'correction', 'expired') NOT NULL,
    quantity INT NOT NULL COMMENT 'Use negative for removal, positive for addition',
    reason TEXT NOT NULL,
    adjusted_by INT NOT NULL,
    approved_by INT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    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 (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL,
    FOREIGN KEY (adjusted_by) REFERENCES admin_users(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES admin_users(id) ON DELETE SET NULL
);

-- ============================================
-- 5. CUSTOMERS (Phone-based, No Auth)
-- ============================================

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100),
    phone VARCHAR(20) NOT NULL,
    alternative_phone VARCHAR(20),
    is_guest BOOLEAN DEFAULT TRUE,
    total_orders INT DEFAULT 0,
    total_spent DECIMAL(12,2) DEFAULT 0.00,
    last_order_date TIMESTAMP NULL,
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_phone (phone),
    INDEX idx_customer_phone (phone),
    INDEX idx_customer_name (first_name, last_name)
);

CREATE TABLE customer_addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    address_label VARCHAR(100) COMMENT 'e.g., Home, Office',
    address_type ENUM('billing', 'shipping') NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) NOT NULL DEFAULT 'Bangladesh',
    landmark VARCHAR(255),
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

CREATE TABLE customer_notes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    admin_id INT NOT NULL,
    note TEXT NOT NULL,
    is_important BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE
);

-- ============================================
-- 6. ORDERS
-- ============================================

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT,
    order_status ENUM(
        'pending', 
        'confirmed', 
        'processing', 
        'shipped', 
        'delivered', 
        'cancelled', 
        'returned'
    ) DEFAULT 'pending',
    payment_status ENUM('pending', 'paid', 'failed', 'refunded', 'cod') DEFAULT 'pending',
    payment_method_id INT,
    shipping_method VARCHAR(100),
    shipping_cost DECIMAL(10,2) DEFAULT 0.00,
    subtotal DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    coupon_code VARCHAR(50),
    coupon_discount DECIMAL(10,2) DEFAULT 0.00,
    tax_amount DECIMAL(10,2) DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL,
    paid_amount DECIMAL(10,2) DEFAULT 0.00,
    due_amount DECIMAL(10,2) DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'BDT',
    notes TEXT,
    admin_notes TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    tracking_number VARCHAR(100),
    tracking_url VARCHAR(500),
    estimated_delivery_date DATE,
    actual_delivery_date TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE SET NULL,
    INDEX idx_order_number (order_number),
    INDEX idx_order_status (order_status),
    INDEX idx_order_date (created_at)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT,
    variant_id INT,
    product_name VARCHAR(255) NOT NULL,
    variant_name VARCHAR(255),
    sku VARCHAR(100),
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    is_recommendation BOOLEAN DEFAULT FALSE COMMENT 'True if this was a recommended/addon product',
    recommended_with_product_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL
);

CREATE TABLE order_shipping_address (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    alternative_phone VARCHAR(20),
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) DEFAULT 'Bangladesh',
    landmark VARCHAR(255),
    delivery_instructions TEXT,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

CREATE TABLE order_billing_address (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) DEFAULT 'Bangladesh',
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

CREATE TABLE order_status_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    status VARCHAR(50) NOT NULL,
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL
);

-- ============================================
-- 7. PAYMENT METHODS & TRANSACTIONS
-- ============================================

CREATE TABLE payment_methods (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) UNIQUE NOT NULL COMMENT 'e.g., cod, bkash, nagad',
    description TEXT,
    instructions TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    is_cod BOOLEAN DEFAULT FALSE,
    configuration JSON,
    logo VARCHAR(500),
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO payment_methods (name, code, description, is_cod, sort_order) VALUES
('Cash on Delivery', 'cod', 'Pay when you receive your order', TRUE, 1),
('bKash', 'bkash', 'Send money to our bKash merchant account', FALSE, 2),
('Nagad', 'nagad', 'Send money to our Nagad merchant account', FALSE, 3),
('Bank Transfer', 'bank_transfer', 'Direct bank transfer to our account', FALSE, 4);

CREATE TABLE payment_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    payment_method_id INT,
    transaction_id VARCHAR(255),
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed', 'refunded', 'cancelled') DEFAULT 'pending',
    payment_details JSON,
    refund_amount DECIMAL(10,2) DEFAULT 0.00,
    refund_reason TEXT,
    paid_at TIMESTAMP NULL,
    refunded_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE SET NULL
);

-- ============================================
-- 8. SHIPPING
-- ============================================

CREATE TABLE shipping_zones (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE shipping_zone_locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    zone_id INT NOT NULL,
    country VARCHAR(100) DEFAULT 'Bangladesh',
    division VARCHAR(100),
    district VARCHAR(100),
    city VARCHAR(100),
    area VARCHAR(255),
    postal_code VARCHAR(20),
    FOREIGN KEY (zone_id) REFERENCES shipping_zones(id) ON DELETE CASCADE
);

CREATE TABLE shipping_rates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    zone_id INT NOT NULL,
    method_name VARCHAR(100) NOT NULL COMMENT 'e.g., Standard Delivery, Express Delivery',
    description TEXT,
    base_rate DECIMAL(10,2) NOT NULL,
    per_kg_rate DECIMAL(10,2) DEFAULT 0.00,
    free_shipping_above DECIMAL(10,2),
    max_delivery_days INT,
    min_delivery_days INT,
    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,
    FOREIGN KEY (zone_id) REFERENCES shipping_zones(id) ON DELETE CASCADE
);

INSERT INTO shipping_zones (name, description) VALUES
('Inside Dhaka', 'Delivery within Dhaka metropolitan area'),
('Outside Dhaka', 'Delivery to all other districts');

-- ============================================
-- 9. COUPONS & DISCOUNTS
-- ============================================

CREATE TABLE coupons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    description VARCHAR(255),
    discount_type ENUM('percentage', 'fixed_amount', 'free_shipping') NOT NULL,
    discount_value DECIMAL(10,2) NOT NULL,
    minimum_order_amount DECIMAL(10,2) DEFAULT 0.00,
    maximum_discount_amount DECIMAL(10,2),
    usage_limit INT,
    per_customer_limit INT DEFAULT 1,
    usage_count INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    is_public BOOLEAN DEFAULT FALSE,
    start_date TIMESTAMP NULL,
    end_date TIMESTAMP NULL,
    terms_conditions TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL
);

CREATE TABLE coupon_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    coupon_id INT NOT NULL,
    product_id INT NOT NULL,
    FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_coupon_product (coupon_id, product_id)
);

CREATE TABLE coupon_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    coupon_id INT NOT NULL,
    category_id INT NOT NULL,
    FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    UNIQUE KEY unique_coupon_category (coupon_id, category_id)
);

CREATE TABLE coupon_usage_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    coupon_id INT NOT NULL,
    order_id INT NOT NULL,
    customer_id INT,
    discount_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

-- ============================================
-- 10. BLOG
-- ============================================

CREATE TABLE blog_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(150) UNIQUE NOT NULL,
    description TEXT,
    image VARCHAR(500),
    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
);

CREATE TABLE blog_posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(300) UNIQUE NOT NULL,
    content LONGTEXT,
    excerpt TEXT,
    featured_image VARCHAR(500),
    author_id INT,
    category_id INT,
    status ENUM('draft', 'published', 'scheduled', 'archived') DEFAULT 'draft',
    is_featured BOOLEAN DEFAULT FALSE,
    views_count INT DEFAULT 0,
    reading_time INT,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES admin_users(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES blog_categories(id) ON DELETE SET NULL,
    INDEX idx_blog_slug (slug),
    INDEX idx_blog_status (status)
);

CREATE TABLE blog_tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(150) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE blog_post_tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    UNIQUE KEY unique_post_tag (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES blog_tags(id) ON DELETE CASCADE
);

CREATE TABLE blog_seo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    blog_post_id INT NOT NULL UNIQUE,
    meta_title VARCHAR(255),
    meta_description TEXT,
    meta_keywords VARCHAR(500),
    og_title VARCHAR(255),
    og_description TEXT,
    og_image VARCHAR(500),
    og_type VARCHAR(50) DEFAULT 'article',
    twitter_card VARCHAR(50) DEFAULT 'summary_large_image',
    twitter_title VARCHAR(255),
    twitter_description TEXT,
    twitter_image VARCHAR(500),
    canonical_url VARCHAR(500),
    schema_markup JSON,
    focus_keyword VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (blog_post_id) REFERENCES blog_posts(id) ON DELETE CASCADE
);

CREATE TABLE blog_assets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    blog_post_id INT NOT NULL,
    asset_type ENUM('image', 'video', 'document') NOT NULL,
    asset_url VARCHAR(500) NOT NULL,
    alt_text VARCHAR(255),
    title VARCHAR(255),
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blog_post_id) REFERENCES blog_posts(id) ON DELETE CASCADE
);

-- ============================================
-- 11. ANALYTICS & TRACKING
-- ============================================

CREATE TABLE tracking_codes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    code_type ENUM('google_analytics', 'google_ads', 'facebook_pixel', 'google_tag_manager', 'tiktok_pixel', 'hotjar', 'custom') NOT NULL,
    tracking_id VARCHAR(255),
    script_code TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    position ENUM('head', 'body_start', 'body_end') DEFAULT 'head',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE page_views (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    page_url VARCHAR(500) NOT NULL,
    page_title VARCHAR(255),
    product_id INT,
    category_id INT,
    customer_id INT,
    session_id VARCHAR(100),
    ip_address VARCHAR(45),
    user_agent TEXT,
    referrer_url VARCHAR(500),
    utm_source VARCHAR(255),
    utm_medium VARCHAR(255),
    utm_campaign VARCHAR(255),
    device_type ENUM('desktop', 'tablet', 'mobile', 'unknown') DEFAULT 'unknown',
    browser VARCHAR(100),
    operating_system VARCHAR(100),
    country VARCHAR(100),
    city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_page_views_url (page_url),
    INDEX idx_page_views_date (created_at),
    INDEX idx_page_views_product (product_id)
);

CREATE TABLE search_queries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    search_term VARCHAR(255) NOT NULL,
    results_count INT DEFAULT 0,
    customer_id INT,
    session_id VARCHAR(100),
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_search_term (search_term)
);

-- ============================================
-- 12. BUSINESS SETTINGS
-- ============================================

CREATE TABLE business_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value LONGTEXT,
    setting_type ENUM('text', 'number', 'boolean', 'json', 'image', 'textarea', 'color', 'url', 'email') DEFAULT 'text',
    group_name VARCHAR(50) COMMENT 'e.g., general, email, social, seo, shipping, payment',
    label VARCHAR(255),
    description TEXT,
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO business_settings (setting_key, setting_value, setting_type, group_name, label) VALUES
('store_name', 'Cadree Lifestyle', 'text', 'general', 'Store Name'),
('store_tagline', 'Your Premium Lifestyle Store', 'text', 'general', 'Store Tagline'),
('store_email', 'info@cadreelifestyle.com', 'email', 'general', 'Store Email'),
('store_phone', '+8801700000000', 'text', 'general', 'Store Phone'),
('store_address', 'Dhaka, Bangladesh', 'textarea', 'general', 'Store Address'),
('store_logo', '', 'image', 'general', 'Store Logo'),
('store_favicon', '', 'image', 'general', 'Favicon'),
('currency_code', 'BDT', 'text', 'general', 'Currency Code'),
('currency_symbol', '৳', 'text', 'general', 'Currency Symbol'),
('timezone', 'Asia/Dhaka', 'text', 'general', 'Timezone'),
('date_format', 'd-m-Y', 'text', 'general', 'Date Format'),
('meta_title', 'Cadree Lifestyle - Premium Lifestyle Products', 'text', 'seo', 'Default Meta Title'),
('meta_description', 'Discover premium lifestyle products at Cadree Lifestyle.', 'textarea', 'seo', 'Default Meta Description'),
('facebook_url', 'https://facebook.com/cadreelifestyle', 'url', 'social', 'Facebook URL'),
('instagram_url', 'https://instagram.com/cadreelifestyle', 'url', 'social', 'Instagram URL'),
('order_confirmation_email', 'true', 'boolean', 'email', 'Send Order Confirmation Email'),
('shipping_email', 'true', 'boolean', 'email', 'Send Shipping Update Email'),
('invoice_prefix', 'INV-', 'text', 'order', 'Invoice Number Prefix'),
('order_prefix', 'CL-', 'text', 'order', 'Order Number Prefix'),
('minimum_order_amount', '0', 'number', 'order', 'Minimum Order Amount'),
('free_shipping_enabled', 'true', 'boolean', 'shipping', 'Enable Free Shipping'),
('free_shipping_minimum', '5000', 'number', 'shipping', 'Free Shipping Minimum Amount'),
('cod_enabled', 'true', 'boolean', 'payment', 'Cash on Delivery Enabled'),
('tax_enabled', 'true', 'boolean', 'tax', 'Enable Tax'),
('default_tax_rate', '0', 'number', 'tax', 'Default Tax Rate (%)');

-- ============================================
-- 13. PRODUCT REVIEWS
-- ============================================

CREATE TABLE product_reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT,
    order_item_id INT,
    rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(255),
    review_text TEXT,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    is_approved BOOLEAN DEFAULT FALSE,
    is_featured BOOLEAN DEFAULT FALSE,
    likes_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,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE SET NULL
);

CREATE TABLE review_images (
    id INT PRIMARY KEY AUTO_INCREMENT,
    review_id INT NOT NULL,
    image_url VARCHAR(500) NOT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (review_id) REFERENCES product_reviews(id) ON DELETE CASCADE
);

-- ============================================
-- 14. STORED PROCEDURES FOR STOCK MANAGEMENT
-- ============================================

DELIMITER $$

-- Process Stock In
CREATE PROCEDURE ProcessStockIn(IN p_stock_in_id INT, IN p_created_by INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_product_id INT;
    DECLARE v_variant_id INT;
    DECLARE v_quantity INT;
    DECLARE v_unit_price DECIMAL(10,2);
    DECLARE v_current_stock INT;
    DECLARE stock_cursor CURSOR FOR 
        SELECT product_id, variant_id, quantity, unit_price 
        FROM stock_in_items WHERE stock_in_id = p_stock_in_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    START TRANSACTION;
    
    OPEN stock_cursor;
    
    read_loop: LOOP
        FETCH stock_cursor INTO v_product_id, v_variant_id, v_quantity, v_unit_price;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Get current stock
        IF v_variant_id IS NULL THEN
            SELECT COALESCE(
                (SELECT running_stock FROM stock_ledger 
                 WHERE product_id = v_product_id AND variant_id IS NULL 
                 ORDER BY id DESC LIMIT 1), 0
            ) INTO v_current_stock;
        ELSE
            SELECT COALESCE(
                (SELECT running_stock FROM stock_ledger 
                 WHERE product_id = v_product_id AND variant_id = v_variant_id 
                 ORDER BY id DESC LIMIT 1), 0
            ) INTO v_current_stock;
        END IF;
        
        -- Insert into stock ledger
        INSERT INTO stock_ledger (
            product_id, variant_id, transaction_type,
            reference_type, reference_id, reference_number,
            quantity_change, running_stock, unit_price, 
            notes, created_by
        ) VALUES (
            v_product_id, v_variant_id, 'stock_in',
            'stock_in', p_stock_in_id, 
            (SELECT reference_number FROM stock_in WHERE id = p_stock_in_id),
            v_quantity, v_current_stock + v_quantity, v_unit_price,
            CONCAT('Stock in from purchase order #', 
                   (SELECT reference_number FROM stock_in WHERE id = p_stock_in_id)),
            p_created_by
        );
        
        -- Update stock quantity in product/variant table
        IF v_variant_id IS NULL THEN
            UPDATE products SET updated_at = NOW() WHERE id = v_product_id;
        ELSE
            UPDATE product_variants 
            SET stock_quantity = stock_quantity + v_quantity 
            WHERE id = v_variant_id;
        END IF;
        
    END LOOP;
    
    CLOSE stock_cursor;
    COMMIT;
END$$

-- Deduct Stock for Order
CREATE PROCEDURE DeductStockForOrder(IN p_order_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_product_id INT;
    DECLARE v_variant_id INT;
    DECLARE v_quantity INT;
    DECLARE v_current_stock INT;
    DECLARE order_cursor CURSOR FOR 
        SELECT product_id, variant_id, quantity 
        FROM order_items WHERE order_id = p_order_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    START TRANSACTION;
    
    OPEN order_cursor;
    
    read_loop: LOOP
        FETCH order_cursor INTO v_product_id, v_variant_id, v_quantity;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Get current running stock
        IF v_variant_id IS NULL THEN
            SELECT COALESCE(
                (SELECT running_stock FROM stock_ledger 
                 WHERE product_id = v_product_id AND variant_id IS NULL 
                 ORDER BY id DESC LIMIT 1), 0
            ) INTO v_current_stock;
        ELSE
            SELECT COALESCE(
                (SELECT running_stock FROM stock_ledger 
                 WHERE product_id = v_product_id AND variant_id = v_variant_id 
                 ORDER BY id DESC LIMIT 1), 0
            ) INTO v_current_stock;
        END IF;
        
        -- Insert into stock ledger
        INSERT INTO stock_ledger (
            product_id, variant_id, transaction_type,
            reference_type, reference_id, reference_number,
            quantity_change, running_stock,
            notes
        ) VALUES (
            v_product_id, v_variant_id, 'order_placed',
            'order', p_order_id,
            (SELECT order_number FROM orders WHERE id = p_order_id),
            -v_quantity, v_current_stock - v_quantity,
            CONCAT('Order placed #', (SELECT order_number FROM orders WHERE id = p_order_id))
        );
        
        -- Update variant stock quantity
        IF v_variant_id IS NOT NULL THEN
            UPDATE product_variants 
            SET stock_quantity = stock_quantity - v_quantity 
            WHERE id = v_variant_id;
        END IF;
        
    END LOOP;
    
    CLOSE order_cursor;
    COMMIT;
END$$

-- Return Stock on Order Cancellation
CREATE PROCEDURE ReturnStockOnCancel(IN p_order_id INT, IN p_notes TEXT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_product_id INT;
    DECLARE v_variant_id INT;
    DECLARE v_quantity INT;
    DECLARE v_current_stock INT;
    DECLARE order_cursor CURSOR FOR 
        SELECT product_id, variant_id, quantity 
        FROM order_items WHERE order_id = p_order_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    START TRANSACTION;
    
    OPEN order_cursor;
    
    read_loop: LOOP
        FETCH order_cursor INTO v_product_id, v_variant_id, v_quantity;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Get current running stock
        IF v_variant_id IS NULL THEN
            SELECT COALESCE(
                (SELECT running_stock FROM stock_ledger 
                 WHERE product_id = v_product_id AND variant_id IS NULL 
                 ORDER BY id DESC LIMIT 1), 0
            ) INTO v_current_stock;
        ELSE
            SELECT COALESCE(
                (SELECT running_stock FROM stock_ledger 
                 WHERE product_id = v_product_id AND variant_id = v_variant_id 
                 ORDER BY id DESC LIMIT 1), 0
            ) INTO v_current_stock;
        END IF;
        
        -- Insert into stock ledger
        INSERT INTO stock_ledger (
            product_id, variant_id, transaction_type,
            reference_type, reference_id, reference_number,
            quantity_change, running_stock,
            notes
        ) VALUES (
            v_product_id, v_variant_id, 'order_cancelled',
            'order', p_order_id,
            (SELECT order_number FROM orders WHERE id = p_order_id),
            v_quantity, v_current_stock + v_quantity,
            CONCAT('Order cancelled #', (SELECT order_number FROM orders WHERE id = p_order_id), ' - ', p_notes)
        );
        
        -- Update variant stock quantity
        IF v_variant_id IS NOT NULL THEN
            UPDATE product_variants 
            SET stock_quantity = stock_quantity + v_quantity 
            WHERE id = v_variant_id;
        END IF;
        
    END LOOP;
    
    CLOSE order_cursor;
    COMMIT;
END$$

DELIMITER ;

-- ============================================
-- 15. VIEWS FOR REPORTING
-- ============================================

-- Daily Sales View
CREATE VIEW v_daily_sales AS
SELECT 
    DATE(created_at) AS order_date,
    COUNT(*) AS total_orders,
    SUM(subtotal) AS total_subtotal,
    SUM(discount_amount) AS total_discount,
    SUM(shipping_cost) AS total_shipping,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS average_order_value
FROM orders
WHERE order_status NOT IN ('cancelled', 'returned')
GROUP BY DATE(created_at)
ORDER BY order_date DESC;

-- Low Stock Products View
CREATE VIEW v_low_stock_products AS
SELECT 
    p.id,
    p.name AS product_name,
    p.sku,
    p.product_type,
    pv.id AS variant_id,
    pv.name AS variant_name,
    pv.sku AS variant_sku,
    COALESCE(
        (SELECT running_stock FROM stock_ledger 
         WHERE product_id = p.id AND (variant_id = pv.id OR (variant_id IS NULL AND pv.id IS NULL))
         ORDER BY id DESC LIMIT 1), 0
    ) AS current_stock,
    COALESCE(pv.low_stock_threshold, 5) AS low_stock_threshold
FROM products p
LEFT JOIN product_variants pv ON p.id = pv.product_id
WHERE p.status = 'published'
HAVING current_stock <= low_stock_threshold
ORDER BY current_stock ASC;

-- Product Performance View
CREATE VIEW v_product_performance AS
SELECT 
    p.id,
    p.name,
    p.sku,
    p.regular_price,
    p.sale_price,
    COUNT(DISTINCT oi.order_id) AS total_orders,
    SUM(oi.quantity) AS total_quantity_sold,
    SUM(oi.total_price) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id AND o.order_status NOT IN ('cancelled', 'returned')
GROUP BY p.id
ORDER BY total_quantity_sold DESC;

-- Customer Summary View
CREATE VIEW v_customer_summary AS
SELECT 
    c.id,
    CONCAT(c.first_name, ' ', COALESCE(c.last_name, '')) AS full_name,
    c.phone,
    COUNT(o.id) AS total_orders,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS avg_order_value,
    MAX(o.created_at) AS last_order_date,
    DATEDIFF(NOW(), MAX(o.created_at)) AS days_since_last_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.order_status NOT IN ('cancelled', 'returned')
GROUP BY c.id;

-- Stock Movement View
CREATE VIEW v_stock_movement AS
SELECT 
    sl.id,
    sl.product_id,
    p.name AS product_name,
    p.sku,
    sl.variant_id,
    pv.name AS variant_name,
    sl.transaction_type,
    sl.reference_type,
    sl.reference_number,
    sl.quantity_change,
    sl.running_stock,
    sl.notes,
    au.name AS created_by_name,
    sl.created_at
FROM stock_ledger sl
LEFT JOIN products p ON sl.product_id = p.id
LEFT JOIN product_variants pv ON sl.variant_id = pv.id
LEFT JOIN admin_users au ON sl.created_by = au.id
ORDER BY sl.created_at DESC;

-- ============================================
-- 16. ADDITIONAL INDEXES
-- ============================================

CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_payment_transactions_order ON payment_transactions(order_id);
CREATE INDEX idx_customer_orders ON orders(customer_id, order_status);
CREATE INDEX idx_product_category ON products(sub_category_id);
CREATE INDEX idx_product_price ON products(regular_price, sale_price);
CREATE INDEX idx_stock_ledger_reference ON stock_ledger(reference_type, reference_id);

-- ============================================
-- END OF DATABASE SCHEMA
-- ============================================