-- Credential lifecycle management schema
-- Migration: 003_credential_lifecycle.sql

-- Credential templates table
CREATE TABLE IF NOT EXISTS credential_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  credential_type TEXT[] NOT NULL,
  template_data JSONB NOT NULL,
  version INTEGER DEFAULT 1,
  is_active BOOLEAN DEFAULT TRUE,
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(name, version)
);

-- Credential status history table
CREATE TABLE IF NOT EXISTS credential_status_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  credential_id UUID NOT NULL REFERENCES verifiable_credentials(id) ON DELETE CASCADE,
  status VARCHAR(50) NOT NULL,
  reason TEXT,
  changed_by UUID REFERENCES users(id),
  changed_at TIMESTAMP DEFAULT NOW(),
  metadata JSONB
);

-- Credential revocation registry
CREATE TABLE IF NOT EXISTS credential_revocation_registry (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  credential_id VARCHAR(500) NOT NULL,
  issuer_did VARCHAR(500) NOT NULL,
  revocation_reason TEXT,
  revoked_by UUID REFERENCES users(id),
  revoked_at TIMESTAMP DEFAULT NOW(),
  revocation_list_index INTEGER,
  UNIQUE(credential_id)
);

-- Credential issuance audit log
CREATE TABLE IF NOT EXISTS credential_issuance_audit (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  credential_id VARCHAR(500) NOT NULL,
  issuer_did VARCHAR(500) NOT NULL,
  subject_did VARCHAR(500) NOT NULL,
  credential_type TEXT[] NOT NULL,
  action VARCHAR(50) NOT NULL, -- 'issued', 'revoked', 'verified', 'renewed'
  performed_by UUID REFERENCES users(id),
  performed_at TIMESTAMP DEFAULT NOW(),
  metadata JSONB,
  ip_address INET,
  user_agent TEXT
);

-- Credential expiration tracking (indexed for fast queries)
CREATE INDEX IF NOT EXISTS idx_verifiable_credentials_expiration 
  ON verifiable_credentials(expiration_date) 
  WHERE expiration_date IS NOT NULL AND revoked = FALSE;

CREATE INDEX IF NOT EXISTS idx_verifiable_credentials_expiring_soon
  ON verifiable_credentials(expiration_date)
  WHERE expiration_date IS NOT NULL 
    AND expiration_date > NOW() 
    AND expiration_date < NOW() + INTERVAL '90 days'
    AND revoked = FALSE;

-- Credential status history indexes
CREATE INDEX IF NOT EXISTS idx_credential_status_history_credential_id
  ON credential_status_history(credential_id);

CREATE INDEX IF NOT EXISTS idx_credential_status_history_changed_at
  ON credential_status_history(changed_at DESC);

-- Credential revocation registry indexes
CREATE INDEX IF NOT EXISTS idx_credential_revocation_registry_credential_id
  ON credential_revocation_registry(credential_id);

CREATE INDEX IF NOT EXISTS idx_credential_revocation_registry_issuer_did
  ON credential_revocation_registry(issuer_did);

-- Credential issuance audit indexes
CREATE INDEX IF NOT EXISTS idx_credential_issuance_audit_credential_id
  ON credential_issuance_audit(credential_id);

CREATE INDEX IF NOT EXISTS idx_credential_issuance_audit_subject_did
  ON credential_issuance_audit(subject_did);

CREATE INDEX IF NOT EXISTS idx_credential_issuance_audit_performed_at
  ON credential_issuance_audit(performed_at DESC);

CREATE INDEX IF NOT EXISTS idx_credential_issuance_audit_action
  ON credential_issuance_audit(action);

-- Credential templates indexes
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;

