-- Source credential registry metadata. Secret material lives in Vault KV v2;
-- this package only stores owner, kind, status, and opaque version coordinates.

CREATE TABLE source_credentials (
    credential_id TEXT PRIMARY KEY,
    owner_id TEXT NOT NULL,
    label TEXT NOT NULL,
    kind TEXT NOT NULL,
    status TEXT NOT NULL,
    active_version_id TEXT,
    secret_mount TEXT NOT NULL,
    secret_path TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL,
    CONSTRAINT source_credentials_kind_check CHECK (
        kind IN (
            'basic_auth',
            'bearer_token',
            'http_headers',
            'object_store',
            'hugging_face'
        )
    ),
    CONSTRAINT source_credentials_status_check CHECK (status IN ('active', 'revoked')),
    CONSTRAINT source_credentials_label_len CHECK (
        char_length(label) BETWEEN 1 AND 200
    ),
    CONSTRAINT source_credentials_active_status CHECK (
        (status = 'active' AND active_version_id IS NOT NULL)
        OR (status = 'revoked')
    )
);

CREATE INDEX source_credentials_owner_created_idx
    ON source_credentials (owner_id, created_at DESC, credential_id DESC);

CREATE TABLE source_credential_versions (
    version_id TEXT PRIMARY KEY,
    credential_id TEXT NOT NULL REFERENCES source_credentials (credential_id),
    vault_kv_version BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    destroyed_at TIMESTAMPTZ,
    CONSTRAINT source_credential_versions_kv_positive CHECK (vault_kv_version >= 1)
);

CREATE INDEX source_credential_versions_credential_idx
    ON source_credential_versions (credential_id, created_at DESC);

ALTER TABLE source_credentials
    ADD CONSTRAINT source_credentials_active_version_fk
    FOREIGN KEY (active_version_id)
    REFERENCES source_credential_versions (version_id)
    DEFERRABLE INITIALLY DEFERRED;