-- Add database indexes for performance optimization
-- Migration: 002_add_indexes.sql

-- User lookups
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_did ON users(did) WHERE did IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at DESC);

-- Document queries
CREATE INDEX IF NOT EXISTS idx_documents_user_id ON documents(user_id) WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_documents_status ON documents(status);
CREATE INDEX IF NOT EXISTS idx_documents_type ON documents(type);
CREATE INDEX IF NOT EXISTS idx_documents_created_at ON documents(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_documents_classification ON documents(classification) WHERE classification IS NOT NULL;

-- Deal queries
CREATE INDEX IF NOT EXISTS idx_deals_created_by ON deals(created_by) WHERE created_by IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_deals_status ON deals(status);
CREATE INDEX IF NOT EXISTS idx_deals_created_at ON deals(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_deals_dataroom_id ON deals(dataroom_id) WHERE dataroom_id IS NOT NULL;

-- Deal documents
CREATE INDEX IF NOT EXISTS idx_deal_documents_deal_id ON deal_documents(deal_id);
CREATE INDEX IF NOT EXISTS idx_deal_documents_document_id ON deal_documents(document_id);
CREATE INDEX IF NOT EXISTS idx_deal_documents_access_level ON deal_documents(access_level);

-- Verifiable Credentials
CREATE INDEX IF NOT EXISTS idx_vc_subject_did ON verifiable_credentials(subject_did);
CREATE INDEX IF NOT EXISTS idx_vc_issuer_did ON verifiable_credentials(issuer_did);
CREATE INDEX IF NOT EXISTS idx_vc_revoked ON verifiable_credentials(revoked) WHERE revoked = false;
CREATE INDEX IF NOT EXISTS idx_vc_credential_id ON verifiable_credentials(credential_id);
CREATE INDEX IF NOT EXISTS idx_vc_credential_type ON verifiable_credentials USING GIN(credential_type);
CREATE INDEX IF NOT EXISTS idx_vc_issuance_date ON verifiable_credentials(issuance_date DESC);
CREATE INDEX IF NOT EXISTS idx_vc_expiration_date ON verifiable_credentials(expiration_date) WHERE expiration_date IS NOT NULL;

-- Signatures
CREATE INDEX IF NOT EXISTS idx_signatures_document_id ON signatures(document_id) WHERE document_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_signatures_signer_did ON signatures(signer_did);
CREATE INDEX IF NOT EXISTS idx_signatures_signature_timestamp ON signatures(signature_timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_signatures_signature_type ON signatures(signature_type);

-- Ledger entries
CREATE INDEX IF NOT EXISTS idx_ledger_account_id ON ledger_entries(account_id);
CREATE INDEX IF NOT EXISTS idx_ledger_created_at ON ledger_entries(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_ledger_type ON ledger_entries(type);
CREATE INDEX IF NOT EXISTS idx_ledger_currency ON ledger_entries(currency);
CREATE INDEX IF NOT EXISTS idx_ledger_reference ON ledger_entries(reference) WHERE reference IS NOT NULL;

-- Payments
CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status);
CREATE INDEX IF NOT EXISTS idx_payments_transaction_id ON payments(transaction_id) WHERE transaction_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_payments_created_at ON payments(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_payments_currency ON payments(currency);
CREATE INDEX IF NOT EXISTS idx_payments_payment_method ON payments(payment_method);

-- Composite indexes for common query patterns
CREATE INDEX IF NOT EXISTS idx_documents_user_status ON documents(user_id, status) WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_deals_created_by_status ON deals(created_by, status) WHERE created_by IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_ledger_account_type ON ledger_entries(account_id, type);
CREATE INDEX IF NOT EXISTS idx_vc_subject_revoked ON verifiable_credentials(subject_did, revoked);

