-- Additional indexes for credential lifecycle management
-- Migration: 004_add_credential_indexes.sql

-- Credential templates
CREATE INDEX IF NOT EXISTS idx_credential_templates_name ON credential_templates(name);
CREATE INDEX IF NOT EXISTS idx_credential_templates_active ON credential_templates(is_active) WHERE is_active = TRUE;
CREATE INDEX IF NOT EXISTS idx_credential_templates_name_version ON credential_templates(name, version);

-- Credential issuance requests
CREATE INDEX IF NOT EXISTS idx_credential_issuance_requests_subject ON credential_issuance_requests(subject_did);
CREATE INDEX IF NOT EXISTS idx_credential_issuance_requests_status ON credential_issuance_requests(status);
CREATE INDEX IF NOT EXISTS idx_credential_issuance_requests_created ON credential_issuance_requests(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_credential_issuance_requests_template ON credential_issuance_requests(template_id) WHERE template_id IS NOT NULL;

-- Credential revocation events
CREATE INDEX IF NOT EXISTS idx_credential_revocation_events_credential ON credential_revocation_events(credential_id);
CREATE INDEX IF NOT EXISTS idx_credential_revocation_events_revoked_at ON credential_revocation_events(revoked_at DESC);
CREATE INDEX IF NOT EXISTS idx_credential_revocation_events_reason ON credential_revocation_events(revocation_reason);

-- Credential issuance audit
CREATE INDEX IF NOT EXISTS idx_credential_issuance_audit_credential ON credential_issuance_audit(credential_id);
CREATE INDEX IF NOT EXISTS idx_credential_issuance_audit_action ON credential_issuance_audit(action);
CREATE INDEX IF NOT EXISTS idx_credential_issuance_audit_timestamp ON credential_issuance_audit(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_credential_issuance_audit_issuer ON credential_issuance_audit(issuer_did);
CREATE INDEX IF NOT EXISTS idx_credential_issuance_audit_subject ON credential_issuance_audit(subject_did);
CREATE INDEX IF NOT EXISTS idx_credential_issuance_audit_type ON credential_issuance_audit USING GIN(credential_type);

-- Composite indexes for common query patterns
CREATE INDEX IF NOT EXISTS idx_vc_issuer_subject_type ON verifiable_credentials(issuer_did, subject_did, credential_type);
CREATE INDEX IF NOT EXISTS idx_vc_expiration_revoked ON verifiable_credentials(expiration_date, revoked) WHERE expiration_date IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_credential_audit_action_timestamp ON credential_issuance_audit(action, timestamp DESC);

