CREATE TABLE kuknos_reconciliation_issues (
    settlement_id uuid PRIMARY KEY REFERENCES kuknos_settlements(id),
    issue_type text NOT NULL CHECK (issue_type IN ('MISSING_LEDGER_CONFIRMATION','TRANSACTION_HASH_MISMATCH')),
    expected_hash text NOT NULL DEFAULT '',
    actual_hash text NOT NULL DEFAULT '',
    status text NOT NULL DEFAULT 'OPEN' CHECK (status IN ('OPEN','RESOLVED')),
    first_detected_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    last_detected_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    resolved_at timestamptz
);

CREATE INDEX kuknos_reconciliation_issues_open_idx
    ON kuknos_reconciliation_issues (status, last_detected_at DESC);