-- Initial schema for as4-411 directory (data-model.md)
-- Run with psql or migration runner; uses snake_case for columns.

CREATE TABLE IF NOT EXISTS tenants (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS participants (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_participants_tenant_id ON participants(tenant_id);

CREATE TABLE IF NOT EXISTS identifiers (
  id TEXT PRIMARY KEY,
  participant_id TEXT NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
  identifier_type TEXT NOT NULL,
  value TEXT NOT NULL,
  scope TEXT,
  priority INTEGER NOT NULL DEFAULT 0,
  verified_at TIMESTAMPTZ
);

CREATE INDEX idx_identifiers_lookup ON identifiers(identifier_type, value);
CREATE INDEX idx_identifiers_participant_id ON identifiers(participant_id);

CREATE TABLE IF NOT EXISTS endpoints (
  id TEXT PRIMARY KEY,
  participant_id TEXT NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
  protocol TEXT NOT NULL,
  address TEXT NOT NULL,
  profile TEXT,
  priority INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'draining'))
);

CREATE INDEX idx_endpoints_participant_id ON endpoints(participant_id);

CREATE TABLE IF NOT EXISTS capabilities (
  id TEXT PRIMARY KEY,
  participant_id TEXT NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
  service TEXT,
  action TEXT,
  process TEXT,
  document_type TEXT,
  constraints_json JSONB
);

CREATE INDEX idx_capabilities_participant_id ON capabilities(participant_id);

CREATE TABLE IF NOT EXISTS credentials (
  id TEXT PRIMARY KEY,
  participant_id TEXT NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
  credential_type TEXT NOT NULL CHECK (credential_type IN ('tls', 'sign', 'encrypt')),
  vault_ref TEXT NOT NULL,
  fingerprint TEXT,
  valid_from TIMESTAMPTZ,
  valid_to TIMESTAMPTZ
);

CREATE INDEX idx_credentials_participant_id ON credentials(participant_id);

CREATE TABLE IF NOT EXISTS policies (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  rule_json JSONB NOT NULL DEFAULT '{}',
  effect TEXT NOT NULL CHECK (effect IN ('allow', 'deny')),
  priority INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX idx_policies_tenant_id ON policies(tenant_id);

CREATE TABLE IF NOT EXISTS audit_log (
  id TEXT PRIMARY KEY,
  at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  actor TEXT,
  action TEXT NOT NULL,
  resource TEXT NOT NULL,
  resource_id TEXT NOT NULL,
  payload JSONB,
  hash_prev TEXT
);

CREATE INDEX idx_audit_log_resource ON audit_log(resource, resource_id);
