-- Performance Optimization Indexes for Large-Scale Deployment
-- Optimized for 200 tenants with 10,000+ products each over 10 years

-- ============================================================
-- PRODUCTS TABLE OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + active status + category filtering
ALTER TABLE products ADD INDEX idx_tenant_active_category (tenant_id, is_active, category_id);

-- Index for tenant + SKU lookup (already exists as unique, but ensure it's there)
-- UNIQUE KEY tenant_sku (tenant_id, sku) - already exists

-- Index for tenant + brand filtering
ALTER TABLE products ADD INDEX idx_tenant_brand (tenant_id, brand_id);

-- Index for tenant + name search (for autocomplete/search)
ALTER TABLE products ADD INDEX idx_tenant_name (tenant_id, name(50));

-- ============================================================
-- STOCK TABLE OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + location filtering
ALTER TABLE stock ADD INDEX idx_tenant_location (tenant_id, location_id);

-- Composite index for tenant + product filtering (for stock queries)
ALTER TABLE stock ADD INDEX idx_tenant_product (tenant_id, product_id);

-- Index for quantity filtering (low stock queries)
ALTER TABLE stock ADD INDEX idx_quantity (quantity);

-- ============================================================
-- STOCK MOVEMENTS OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + product + location queries
ALTER TABLE stock_movements ADD INDEX idx_tenant_product_location (tenant_id, product_id, location_id);

-- Index for time-based queries (created_at)
ALTER TABLE stock_movements ADD INDEX idx_created_at (created_at);

-- Composite index for tenant + created_at (for recent movements)
ALTER TABLE stock_movements ADD INDEX idx_tenant_created (tenant_id, created_at);

-- Index for movement type filtering
ALTER TABLE stock_movements ADD INDEX idx_type (type);

-- ============================================================
-- SALES TABLE OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + location filtering
ALTER TABLE sales ADD INDEX idx_tenant_location (tenant_id, location_id);

-- Index for time-based queries
ALTER TABLE sales ADD INDEX idx_created_at (created_at);

-- Composite index for tenant + created_at (for recent sales)
ALTER TABLE sales ADD INDEX idx_tenant_created (tenant_id, created_at);

-- Index for customer filtering
ALTER TABLE sales ADD INDEX idx_customer (customer_id);

-- Index for status filtering
ALTER TABLE sales ADD INDEX idx_status (status);

-- ============================================================
-- SALE ITEMS OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + product (for product sales reports)
ALTER TABLE sale_items ADD INDEX idx_tenant_product (tenant_id, product_id);

-- Index for sale_id joins
ALTER TABLE sale_items ADD INDEX idx_sale_id (sale_id);

-- ============================================================
-- PURCHASES TABLE OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + location filtering
ALTER TABLE purchases ADD INDEX idx_tenant_location (tenant_id, location_id);

-- Index for time-based queries
ALTER TABLE purchases ADD INDEX idx_created_at (created_at);

-- Composite index for tenant + created_at (for recent purchases)
ALTER TABLE purchases ADD INDEX idx_tenant_created (tenant_id, created_at);

-- Index for supplier filtering
ALTER TABLE purchases ADD INDEX idx_supplier (supplier_id);

-- Index for status filtering
ALTER TABLE purchases ADD INDEX idx_status (status);

-- ============================================================
-- PURCHASE ITEMS OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + product (for purchase analysis)
ALTER TABLE purchase_items ADD INDEX idx_tenant_product (tenant_id, product_id);

-- Index for purchase_id joins
ALTER TABLE purchase_items ADD INDEX idx_purchase_id (purchase_id);

-- Index for expiry date filtering (for expiry reports)
ALTER TABLE purchase_items ADD INDEX idx_expiry (expiry_date);

-- ============================================================
-- BATCHES OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + product
ALTER TABLE batches ADD INDEX idx_tenant_product (tenant_id, product_id);

-- Index for expiry date (for expiry tracking)
ALTER TABLE batches ADD INDEX idx_expiry (expiry_date);

-- ============================================================
-- CUSTOMERS OPTIMIZATIONS
-- ============================================================

-- Index for name search
ALTER TABLE customers ADD INDEX idx_name (name(50));

-- ============================================================
-- SUPPLIERS OPTIMIZATIONS
-- ============================================================

-- Index for name search
ALTER TABLE suppliers ADD INDEX idx_name (name(50));

-- ============================================================
-- NOTIFICATIONS OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + user + read status
ALTER TABLE notifications ADD INDEX idx_tenant_user_read (tenant_id, user_id, is_read);

-- Index for time-based queries
ALTER TABLE notifications ADD INDEX idx_created_at (created_at);

-- Composite index for tenant + created_at (for recent notifications)
ALTER TABLE notifications ADD INDEX idx_tenant_created (tenant_id, created_at);

-- ============================================================
-- AUDIT LOG OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + created_at (for audit trail queries)
ALTER TABLE audit_logs ADD INDEX idx_tenant_created (tenant_id, created_at);

-- Index for user filtering
ALTER TABLE audit_logs ADD INDEX idx_user (user_id);

-- ============================================================
-- USERS OPTIMIZATIONS
-- ============================================================

-- Index for email search within tenant
ALTER TABLE users ADD INDEX idx_tenant_email (tenant_id, email(50));

-- ============================================================
-- LOCATIONS OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + active status
ALTER TABLE locations ADD INDEX idx_tenant_active (tenant_id, is_active);

-- ============================================================
-- CATEGORIES OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + parent (for category hierarchy)
ALTER TABLE categories ADD INDEX idx_tenant_parent (tenant_id, parent_id);

-- ============================================================
-- STOCK ADJUSTMENTS OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + product + location
ALTER TABLE stock_adjustments ADD INDEX idx_tenant_product_location (tenant_id, product_id, location_id);

-- Index for time-based queries
ALTER TABLE stock_adjustments ADD INDEX idx_created_at (created_at);

-- ============================================================
-- STOCK TRANSFERS OPTIMIZATIONS
-- ============================================================

-- Composite index for tenant + from_location
ALTER TABLE stock_transfers ADD INDEX idx_tenant_from (tenant_id, from_location_id);

-- Composite index for tenant + to_location
ALTER TABLE stock_transfers ADD INDEX idx_tenant_to (tenant_id, to_location_id);

-- Index for status filtering
ALTER TABLE stock_transfers ADD INDEX idx_status (status);

-- Index for time-based queries
ALTER TABLE stock_transfers ADD INDEX idx_created_at (created_at);
