template-saas-database-v2/ddl/schemas/sales/05-indexes.sql

-- ============================================
-- TEMPLATE-SAAS: Sales Indexes
-- Version: 1.0.0
-- Module: SAAS-018
-- ============================================

-- ============================================
-- Pipeline Stages Indexes
-- ============================================
CREATE INDEX idx_pipeline_stages_tenant ON sales.pipeline_stages(tenant_id);
CREATE INDEX idx_pipeline_stages_position ON sales.pipeline_stages(tenant_id, position);
CREATE INDEX idx_pipeline_stages_active ON sales.pipeline_stages(tenant_id, is_active) WHERE is_active = TRUE;

-- ============================================
-- Leads Indexes
-- ============================================
-- Primary indexes
CREATE INDEX idx_leads_tenant ON sales.leads(tenant_id);
CREATE INDEX idx_leads_status ON sales.leads(tenant_id, status);
CREATE INDEX idx_leads_source ON sales.leads(tenant_id, source);
CREATE INDEX idx_leads_assigned ON sales.leads(assigned_to) WHERE assigned_to IS NOT NULL;

-- Search indexes
CREATE INDEX idx_leads_email ON sales.leads(tenant_id, email) WHERE email IS NOT NULL;
CREATE INDEX idx_leads_company ON sales.leads(tenant_id, company) WHERE company IS NOT NULL;

-- Full-text search on name
CREATE INDEX idx_leads_name_search ON sales.leads
    USING gin(to_tsvector('simple', first_name || ' ' || last_name));

-- Score for prioritization
CREATE INDEX idx_leads_score ON sales.leads(tenant_id, score DESC) WHERE deleted_at IS NULL;

-- Soft delete filter
CREATE INDEX idx_leads_active ON sales.leads(tenant_id)
    WHERE deleted_at IS NULL;

-- Conversion tracking
CREATE INDEX idx_leads_converted ON sales.leads(tenant_id, converted_at)
    WHERE converted_at IS NOT NULL;

-- ============================================
-- Opportunities Indexes
-- ============================================
-- Primary indexes
CREATE INDEX idx_opportunities_tenant ON sales.opportunities(tenant_id);
CREATE INDEX idx_opportunities_stage ON sales.opportunities(tenant_id, stage);
CREATE INDEX idx_opportunities_stage_id ON sales.opportunities(tenant_id, stage_id) WHERE stage_id IS NOT NULL;
CREATE INDEX idx_opportunities_assigned ON sales.opportunities(assigned_to) WHERE assigned_to IS NOT NULL;

-- Value-based queries
CREATE INDEX idx_opportunities_amount ON sales.opportunities(tenant_id, amount DESC);
CREATE INDEX idx_opportunities_probability ON sales.opportunities(tenant_id, probability DESC);

-- Date-based queries
CREATE INDEX idx_opportunities_expected_close ON sales.opportunities(tenant_id, expected_close_date);
CREATE INDEX idx_opportunities_won ON sales.opportunities(tenant_id, won_at) WHERE won_at IS NOT NULL;
CREATE INDEX idx_opportunities_lost ON sales.opportunities(tenant_id, lost_at) WHERE lost_at IS NOT NULL;

-- Lead reference
CREATE INDEX idx_opportunities_lead ON sales.opportunities(lead_id) WHERE lead_id IS NOT NULL;

-- Soft delete filter
CREATE INDEX idx_opportunities_active ON sales.opportunities(tenant_id)
    WHERE deleted_at IS NULL;

-- Pipeline view (common query pattern)
CREATE INDEX idx_opportunities_pipeline ON sales.opportunities(tenant_id, stage, amount DESC)
    WHERE deleted_at IS NULL AND won_at IS NULL AND lost_at IS NULL;

-- ============================================
-- Activities Indexes
-- ============================================
-- Primary indexes
CREATE INDEX idx_activities_tenant ON sales.activities(tenant_id);
CREATE INDEX idx_activities_type ON sales.activities(tenant_id, type);
CREATE INDEX idx_activities_status ON sales.activities(tenant_id, status);

-- Related records
CREATE INDEX idx_activities_lead ON sales.activities(lead_id) WHERE lead_id IS NOT NULL;
CREATE INDEX idx_activities_opportunity ON sales.activities(opportunity_id) WHERE opportunity_id IS NOT NULL;

-- Assignment
CREATE INDEX idx_activities_assigned ON sales.activities(assigned_to) WHERE assigned_to IS NOT NULL;

-- Scheduling
CREATE INDEX idx_activities_due_date ON sales.activities(tenant_id, due_date)
    WHERE status = 'pending' AND deleted_at IS NULL;

-- Upcoming activities (common query)
CREATE INDEX idx_activities_upcoming ON sales.activities(tenant_id, due_date, type)
    WHERE status = 'pending' AND deleted_at IS NULL AND due_date IS NOT NULL;

-- Reminders
CREATE INDEX idx_activities_reminder ON sales.activities(reminder_at)
    WHERE reminder_sent = FALSE AND reminder_at IS NOT NULL AND status = 'pending';

-- Soft delete filter
CREATE INDEX idx_activities_active ON sales.activities(tenant_id)
    WHERE deleted_at IS NULL;

-- ============================================
-- Composite indexes for common queries
-- ============================================

-- Dashboard: leads by status with score
CREATE INDEX idx_leads_dashboard ON sales.leads(tenant_id, status, score DESC)
    WHERE deleted_at IS NULL;

-- Dashboard: opportunities pipeline
CREATE INDEX idx_opportunities_dashboard ON sales.opportunities(tenant_id, stage, expected_close_date)
    WHERE deleted_at IS NULL AND won_at IS NULL AND lost_at IS NULL;

-- Activity feed for a lead
CREATE INDEX idx_activities_lead_feed ON sales.activities(lead_id, created_at DESC)
    WHERE deleted_at IS NULL;

-- Activity feed for an opportunity
CREATE INDEX idx_activities_opportunity_feed ON sales.activities(opportunity_id, created_at DESC)
    WHERE deleted_at IS NULL;