-- Durable intent for Vault secret writes. Secret material is never stored here.
-- Intents make create/rotate recoverable across ambiguous PostgreSQL commits
-- without best-effort Vault destruction that could erase a committed version.

CREATE TABLE secret_write_intents (
    intent_id TEXT PRIMARY KEY,
    operation TEXT NOT NULL,
    credential_id TEXT NOT NULL,
    version_id TEXT NOT NULL,
    owner_id TEXT,
    label TEXT,
    credential_kind TEXT,
    secret_mount TEXT NOT NULL,
    secret_path TEXT NOT NULL,
    vault_kv_version BIGINT,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL,
    CONSTRAINT secret_write_intents_operation_check CHECK (
        operation IN ('create', 'rotate')
    ),
    CONSTRAINT secret_write_intents_status_check CHECK (
        status IN ('prepared', 'vault_written', 'durable_committed', 'abandoned')
    ),
    CONSTRAINT secret_write_intents_kv_check CHECK (
        vault_kv_version IS NULL OR vault_kv_version >= 1
    ),
    CONSTRAINT secret_write_intents_create_fields_check CHECK (
        (operation = 'create'
            AND owner_id IS NOT NULL
            AND label IS NOT NULL
            AND credential_kind IS NOT NULL)
        OR (operation = 'rotate'
            AND owner_id IS NULL
            AND label IS NULL
            AND credential_kind IS NULL)
    ),
    CONSTRAINT secret_write_intents_written_has_version CHECK (
        (status = 'prepared' AND vault_kv_version IS NULL)
        OR (status IN ('vault_written', 'durable_committed')
            AND vault_kv_version IS NOT NULL)
        OR status = 'abandoned'
    )
);

CREATE INDEX secret_write_intents_status_idx
    ON secret_write_intents (status, created_at);