-- NEO-135: append-only reputation delta audit log.
CREATE TABLE IF NOT EXISTS reputation_delta_audit (
    id TEXT NOT NULL PRIMARY KEY,
    player_id TEXT NOT NULL REFERENCES player_position(player_id) ON DELETE CASCADE,
    faction_id TEXT NOT NULL,
    delta_amount INTEGER NOT NULL,
    resulting_standing INTEGER NOT NULL,
    source_kind TEXT NOT NULL,
    source_id TEXT NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_reputation_delta_audit_player_recorded
    ON reputation_delta_audit (player_id, recorded_at, id);

COMMENT ON TABLE reputation_delta_audit IS 'Append-only faction reputation delta audit (NEO-135).';