-- ============================================
-- SAAS-022: Goals Indexes
-- Performance optimization
-- ============================================

-- ─────────────────────────────────────────────
-- Definitions Indexes
-- ─────────────────────────────────────────────

-- Tenant + status (most common query)
CREATE INDEX idx_definitions_tenant_status
    ON goals.definitions (tenant_id, status);

-- Period queries
CREATE INDEX idx_definitions_tenant_period
    ON goals.definitions (tenant_id, period, starts_at, ends_at);

-- Date range queries
CREATE INDEX idx_definitions_dates
    ON goals.definitions (starts_at, ends_at);

-- Category filtering
CREATE INDEX idx_definitions_category
    ON goals.definitions (tenant_id, category)
    WHERE category IS NOT NULL;

-- Active goals (partial index)
CREATE INDEX idx_definitions_active
    ON goals.definitions (tenant_id, starts_at, ends_at)
    WHERE status = 'active';

-- Created by user
CREATE INDEX idx_definitions_created_by
    ON goals.definitions (created_by)
    WHERE created_by IS NOT NULL;

-- Tags (GIN for JSONB array)
CREATE INDEX idx_definitions_tags
    ON goals.definitions USING GIN (tags);

-- ─────────────────────────────────────────────
-- Assignments Indexes
-- ─────────────────────────────────────────────

-- Tenant + status (most common query)
CREATE INDEX idx_assignments_tenant_status
    ON goals.assignments (tenant_id, status);

-- Definition lookup
CREATE INDEX idx_assignments_definition
    ON goals.assignments (definition_id);

-- User assignments (most common filter)
CREATE INDEX idx_assignments_user
    ON goals.assignments (user_id, status)
    WHERE user_id IS NOT NULL;

-- Team assignments
CREATE INDEX idx_assignments_team
    ON goals.assignments (team_id, status)
    WHERE team_id IS NOT NULL;

-- Active assignments (partial index)
CREATE INDEX idx_assignments_active
    ON goals.assignments (tenant_id, user_id)
    WHERE status = 'active';

-- Progress percentage (for reports)
CREATE INDEX idx_assignments_progress
    ON goals.assignments (tenant_id, progress_percentage);

-- Achieved goals
CREATE INDEX idx_assignments_achieved
    ON goals.assignments (achieved_at)
    WHERE status = 'achieved';

-- ─────────────────────────────────────────────
-- Progress Log Indexes
-- ─────────────────────────────────────────────

-- Assignment history
CREATE INDEX idx_progress_log_assignment
    ON goals.progress_log (assignment_id, logged_at DESC);

-- Tenant + date range
CREATE INDEX idx_progress_log_tenant_date
    ON goals.progress_log (tenant_id, logged_at);

-- Source reference (for deduplication)
CREATE INDEX idx_progress_log_source_ref
    ON goals.progress_log (source_reference)
    WHERE source_reference IS NOT NULL;

-- By user who logged
CREATE INDEX idx_progress_log_logged_by
    ON goals.progress_log (logged_by)
    WHERE logged_by IS NOT NULL;

-- ─────────────────────────────────────────────
-- Milestone Notifications Indexes
-- ─────────────────────────────────────────────

-- Assignment milestones
CREATE INDEX idx_milestone_notifications_assignment
    ON goals.milestone_notifications (assignment_id);

-- Tenant notifications
CREATE INDEX idx_milestone_notifications_tenant
    ON goals.milestone_notifications (tenant_id, notified_at);