-- Enterprise-Scale Indexes for High Concurrency
-- These indexes optimize queries for 50+ concurrent users

-- Sales table indexes
ALTER TABLE sales ADD INDEX idx_tenant_created (tenant_id, created_at);
ALTER TABLE sales ADD INDEX idx_tenant_status (tenant_id, status);
ALTER TABLE sales ADD INDEX idx_tenant_customer (tenant_id, customer_id);
ALTER TABLE sales ADD INDEX idx_tenant_location (tenant_id, location_id);
ALTER TABLE sales ADD INDEX idx_sale_no (sale_no);
ALTER TABLE sales ADD INDEX idx_payment_status (payment_status);

-- Sale items indexes
ALTER TABLE sale_items ADD INDEX idx_sale_product (sale_id, product_id);
ALTER TABLE sale_items ADD INDEX idx_tenant_product (tenant_id, product_id);

-- Stock table indexes (critical for POS operations)
ALTER TABLE stock ADD INDEX idx_tenant_product_location (tenant_id, product_id, location_id);
ALTER TABLE stock ADD INDEX idx_tenant_batch (tenant_id, batch_id);
ALTER TABLE stock ADD INDEX idx_quantity (quantity);

-- Products table indexes
ALTER TABLE products ADD INDEX idx_tenant_category (tenant_id, category_id);
ALTER TABLE products ADD INDEX idx_tenant_sku (tenant_id, sku);
ALTER TABLE products ADD INDEX idx_tenant_active (tenant_id, is_active);
ALTER TABLE products ADD INDEX idx_name (name);

-- Purchases table indexes
ALTER TABLE purchases ADD INDEX idx_tenant_created (tenant_id, created_at);
ALTER TABLE purchases ADD INDEX idx_tenant_supplier (tenant_id, supplier_id);
ALTER TABLE purchases ADD INDEX idx_payment_status (payment_status);
ALTER TABLE purchases ADD INDEX idx_status (status);

-- Purchase items indexes
ALTER TABLE purchase_items ADD INDEX idx_purchase_product (purchase_id, product_id);

-- Customers table indexes
ALTER TABLE customers ADD INDEX idx_tenant_name (tenant_id, name);
ALTER TABLE customers ADD INDEX idx_tenant_phone (tenant_id, phone);

-- Suppliers table indexes
ALTER TABLE suppliers ADD INDEX idx_tenant_name (tenant_id, name);

-- Categories table indexes
ALTER TABLE categories ADD INDEX idx_tenant_name (tenant_id, name);

-- Locations table indexes
ALTER TABLE locations ADD INDEX idx_tenant_active (tenant_id, is_active);

-- Users table indexes
ALTER TABLE users ADD INDEX idx_tenant_email (tenant_id, email);
ALTER TABLE users ADD INDEX idx_tenant_role (tenant_id, role_id);

-- Settings table indexes
ALTER TABLE settings ADD INDEX idx_tenant_key (tenant_id, setting_key);

-- Composite indexes for common query patterns
ALTER TABLE sales ADD INDEX idx_tenant_status_created (tenant_id, status, created_at);
ALTER TABLE stock ADD INDEX idx_tenant_product_loc_qty (tenant_id, product_id, location_id, quantity);
