-- Cert inventory: every CA + server cert issued by orca, with metadata
-- sufficient to drive rotation history, fingerprint pinning, and
-- `orca doctor cert` health reports. This is migration 0004; v0.2 P01.
--
-- `kind` is one of: 'ca', 'server'. CA rows have node_id = '' (the
-- CA is per-cluster, not per-node). Server rows have node_id set.
-- `serial_hex` is the cert serial as a hex string; used to detect
-- duplicate issuances.
-- `fingerprint` is SHA-256 hex (lowercase) of the cert's DER bytes;
-- matches the value returned by `Fingerprint(certPath)` in
-- internal/security.
CREATE TABLE IF NOT EXISTS certs (
    id          TEXT PRIMARY KEY,
    kind        TEXT NOT NULL,
    node_id     TEXT NOT NULL DEFAULT '',
    serial_hex  TEXT NOT NULL,
    subject_cn  TEXT NOT NULL,
    issuer_cn   TEXT NOT NULL,
    not_before  DATETIME NOT NULL,
    not_after   DATETIME NOT NULL,
    fingerprint TEXT NOT NULL,
    source_path TEXT NOT NULL DEFAULT '',
    created_at  DATETIME NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_certs_kind      ON certs(kind);
CREATE INDEX IF NOT EXISTS idx_certs_node      ON certs(node_id);
CREATE INDEX IF NOT EXISTS idx_certs_node_kind ON certs(node_id, kind);
CREATE INDEX IF NOT EXISTS idx_certs_created   ON certs(created_at);
CREATE INDEX IF NOT EXISTS idx_certs_fp        ON certs(fingerprint);
