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

-- ============================================
-- TEMPLATE-SAAS: Commissions Indexes
-- Version: 1.0.0
-- Module: SAAS-020
-- ============================================

-- ============================================
-- Schemes Indexes
-- ============================================
-- Primary tenant isolation
CREATE INDEX idx_schemes_tenant ON commissions.schemes(tenant_id);

-- Active schemes lookup
CREATE INDEX idx_schemes_active ON commissions.schemes(tenant_id, is_active)
    WHERE is_active = TRUE AND deleted_at IS NULL;

-- Scheme type filtering
CREATE INDEX idx_schemes_type ON commissions.schemes(tenant_id, type);

-- Soft delete filter
CREATE INDEX idx_schemes_not_deleted ON commissions.schemes(tenant_id)
    WHERE deleted_at IS NULL;

-- ============================================
-- Assignments Indexes
-- ============================================
-- Primary tenant isolation
CREATE INDEX idx_assignments_tenant ON commissions.assignments(tenant_id);

-- User assignments lookup (common query)
CREATE INDEX idx_assignments_user ON commissions.assignments(tenant_id, user_id)
    WHERE is_active = TRUE;

-- Scheme assignments lookup
CREATE INDEX idx_assignments_scheme ON commissions.assignments(tenant_id, scheme_id)
    WHERE is_active = TRUE;

-- Active assignments with date range
CREATE INDEX idx_assignments_active_period ON commissions.assignments(tenant_id, user_id, starts_at, ends_at)
    WHERE is_active = TRUE;

-- Find current active assignment for user
CREATE INDEX idx_assignments_user_current ON commissions.assignments(user_id, scheme_id, starts_at DESC)
    WHERE is_active = TRUE AND (ends_at IS NULL OR ends_at > NOW());

-- ============================================
-- Entries Indexes
-- ============================================
-- Primary tenant isolation
CREATE INDEX idx_entries_tenant ON commissions.entries(tenant_id);

-- User entries (for my-earnings queries)
CREATE INDEX idx_entries_user ON commissions.entries(tenant_id, user_id);
CREATE INDEX idx_entries_user_status ON commissions.entries(tenant_id, user_id, status);

-- Status filtering (pending approvals queue)
CREATE INDEX idx_entries_status ON commissions.entries(tenant_id, status);
CREATE INDEX idx_entries_pending ON commissions.entries(tenant_id, created_at)
    WHERE status = 'pending';

-- Period association
CREATE INDEX idx_entries_period ON commissions.entries(period_id)
    WHERE period_id IS NOT NULL;

-- Reference lookups (find commission for a sale)
CREATE INDEX idx_entries_reference ON commissions.entries(tenant_id, reference_type, reference_id);

-- Date range queries (reporting)
CREATE INDEX idx_entries_created ON commissions.entries(tenant_id, created_at DESC);
CREATE INDEX idx_entries_paid ON commissions.entries(tenant_id, paid_at)
    WHERE paid_at IS NOT NULL;

-- Scheme attribution
CREATE INDEX idx_entries_scheme ON commissions.entries(scheme_id)
    WHERE scheme_id IS NOT NULL;

-- Dashboard: pending commissions by user
CREATE INDEX idx_entries_user_pending ON commissions.entries(user_id, commission_amount DESC)
    WHERE status = 'pending';

-- Dashboard: approved commissions awaiting payment
CREATE INDEX idx_entries_approved_unpaid ON commissions.entries(tenant_id, approved_at)
    WHERE status = 'approved' AND paid_at IS NULL;

-- ============================================
-- Periods Indexes
-- ============================================
-- Primary tenant isolation
CREATE INDEX idx_periods_tenant ON commissions.periods(tenant_id);

-- Status filtering
CREATE INDEX idx_periods_status ON commissions.periods(tenant_id, status);

-- Open period lookup (common query)
CREATE INDEX idx_periods_open ON commissions.periods(tenant_id, starts_at)
    WHERE status = 'open';

-- Date range queries
CREATE INDEX idx_periods_dates ON commissions.periods(tenant_id, starts_at, ends_at);

-- Period history
CREATE INDEX idx_periods_closed ON commissions.periods(tenant_id, closed_at DESC)
    WHERE status IN ('closed', 'processing', 'paid');

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

-- Dashboard: earnings by user in date range
CREATE INDEX idx_entries_earnings_dashboard ON commissions.entries(tenant_id, user_id, status, created_at)
    WHERE status NOT IN ('rejected', 'cancelled');

-- Period summary calculation
CREATE INDEX idx_entries_period_summary ON commissions.entries(period_id, status, commission_amount)
    WHERE status IN ('pending', 'approved');

-- Top earners query
CREATE INDEX idx_entries_top_earners ON commissions.entries(tenant_id, user_id, commission_amount DESC, created_at)
    WHERE status NOT IN ('rejected', 'cancelled');

-- User's active scheme with rate
CREATE INDEX idx_assignments_user_scheme ON commissions.assignments(user_id, scheme_id, custom_rate)
    WHERE is_active = TRUE;