-- Performance Optimization Indexes for Glado POS System
-- These indexes optimize the most frequently used queries

-- Users table indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role_franchise ON users(role, franchise_id);
CREATE INDEX idx_users_active ON users(is_active);

-- Sales table indexes (most critical for performance)
CREATE INDEX idx_sales_date ON sales(sale_date);
CREATE INDEX idx_sales_franchise_date ON sales(franchise_id, sale_date);
CREATE INDEX idx_sales_branch_date ON sales(branch_id, sale_date);
CREATE INDEX idx_sales_customer ON sales(customer_id);
CREATE INDEX idx_sales_user ON sales(user_id);
CREATE INDEX idx_sales_total_amount ON sales(total_amount);

-- Sale items indexes
CREATE INDEX idx_sale_items_sale ON sale_items(sale_id);
CREATE INDEX idx_sale_items_product ON sale_items(product_id);

-- Products table indexes
CREATE INDEX idx_products_franchise ON products(franchise_id);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active ON products(is_active);
CREATE INDEX idx_products_stock ON products(stock_quantity);
CREATE INDEX idx_products_expiration ON products(expiration_date);
CREATE INDEX idx_products_name ON products(name);

-- Customers table indexes
CREATE INDEX idx_customers_franchise ON customers(franchise_id);
CREATE INDEX idx_customers_phone ON customers(phone);
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_customers_referral_code ON customers(referral_code);
CREATE INDEX idx_customers_last_purchase ON customers(last_purchase_date);
CREATE INDEX idx_customers_active ON customers(is_active);

-- Loyalty transactions indexes
CREATE INDEX idx_loyalty_customer ON loyalty_transactions(customer_id);
CREATE INDEX idx_loyalty_sale ON loyalty_transactions(sale_id);
CREATE INDEX idx_loyalty_type_date ON loyalty_transactions(transaction_type, created_at);

-- Promotions table indexes
CREATE INDEX idx_promotions_franchise ON promotions(franchise_id);
CREATE INDEX idx_promotions_code ON promotions(code);
CREATE INDEX idx_promotions_active ON promotions(is_active);
CREATE INDEX idx_promotions_dates ON promotions(start_date, end_date);

-- Activity logs indexes
CREATE INDEX idx_activity_user ON activity_logs(user_id);
CREATE INDEX idx_activity_date ON activity_logs(created_at);
CREATE INDEX idx_activity_action ON activity_logs(action);
CREATE INDEX idx_activity_table ON activity_logs(table_affected);

-- Inventory related indexes
CREATE INDEX idx_inventory_franchise_product ON products(franchise_id, id);
CREATE INDEX idx_inventory_low_stock ON products(stock_quantity, min_stock_level);

-- Composite indexes for complex queries
CREATE INDEX idx_sales_franchise_branch_date ON sales(franchise_id, branch_id, sale_date);
CREATE INDEX idx_products_franchise_active_stock ON products(franchise_id, is_active, stock_quantity);
CREATE INDEX idx_customers_franchise_active_points ON customers(franchise_id, is_active, points_balance);