-- ============================================
-- KPI System Performance Indexes
-- Date: 2025-01-04
-- Description: Adds critical indexes for KPI query performance
-- ============================================

-- deals table indexes
CREATE INDEX IF NOT EXISTS idx_deals_contact_owner ON deals(contact_didar_id, owner_didar_id);
CREATE INDEX IF NOT EXISTS idx_deals_register_time ON deals(register_time);
CREATE INDEX IF NOT EXISTS idx_deals_won_time ON deals(won_time);
CREATE INDEX IF NOT EXISTS idx_deals_lost_time ON deals(lost_time);
CREATE INDEX IF NOT EXISTS idx_deals_status ON deals(status);
CREATE INDEX IF NOT EXISTS idx_deals_contact ON deals(contact_didar_id);

-- persons table indexes
CREATE INDEX IF NOT EXISTS idx_persons_owner_id ON persons(owner_didar_id);
CREATE INDEX IF NOT EXISTS idx_persons_register_time ON persons(register_time);
CREATE INDEX IF NOT EXISTS idx_persons_didar_contact_id ON persons(didar_contact_id);

-- transactions table indexes
CREATE INDEX IF NOT EXISTS idx_trans_deal_first_status ON transactions(deal_id, is_first_payment, status);
CREATE INDEX IF NOT EXISTS idx_trans_payment_date ON transactions(payment_date);

-- deal_products table indexes
CREATE INDEX IF NOT EXISTS idx_deal_products_deal ON deal_products(deal_id);
CREATE INDEX IF NOT EXISTS idx_deal_products_product ON deal_products(product_id);

-- Note: IF NOT EXISTS is MySQL 5.7.4+ syntax
-- For older MySQL versions, remove IF NOT EXISTS and run manually if indexes already exist