-- 004_create_orders_table.sql

-- ====================================
-- 9. ORDERS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id VARCHAR(100) UNIQUE NOT NULL,
    user_id VARCHAR(100) NOT NULL,
    user_email VARCHAR(255),
    vendor_id VARCHAR(100) NOT NULL,
    
    -- Amounts
    total_amount DECIMAL(10,2) NOT NULL,
    item_count INT DEFAULT 1,
    
    -- Status
    order_status VARCHAR(50) DEFAULT 'pending',
    payment_method VARCHAR(50) DEFAULT 'COD',
    payment_status VARCHAR(50) DEFAULT 'PENDING',
    
    -- Location/Address
    shipping_address TEXT,
    
    -- First Item Preview (For lists efficiently)
    first_product_name VARCHAR(255),
    first_product_image VARCHAR(500),
    
    -- Dates
    order_date VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user (user_id),
    INDEX idx_vendor (vendor_id),
    INDEX idx_status (order_status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;