-- 002_dual_ledger_outbox.sql
-- Create outbox table for dual-ledger synchronization
-- Enables transactional outbox pattern for SCB ledger sync

CREATE TABLE IF NOT EXISTS dual_ledger_outbox (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  outbox_id           text NOT NULL UNIQUE,

  internal_entry_id   text NOT NULL,
  internal_settlement_id text NULL,

  sovereign_bank_id   text NOT NULL,
  ledger_id           text NOT NULL,

  reference_id        text NOT NULL,
  payload             jsonb NOT NULL,
  payload_hash        text NOT NULL,

  status              text NOT NULL DEFAULT 'QUEUED', -- QUEUED|SENT|ACKED|FINALIZED|FAILED
  scb_transaction_id  text NULL,
  scb_ledger_hash     text NULL,
  scb_signature       text NULL,

  attempts            int NOT NULL DEFAULT 0,
  last_attempt_at     timestamptz NULL,
  last_error          text NULL,
  acked_at            timestamptz NULL,
  finalized_at        timestamptz NULL,

  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);

-- Idempotency per SCB ledger (prevents duplicate sync attempts)
CREATE UNIQUE INDEX IF NOT EXISTS dual_ledger_outbox_unique_scb_ref
  ON dual_ledger_outbox (sovereign_bank_id, reference_id);

-- Work-queue indexes for efficient job claiming
CREATE INDEX IF NOT EXISTS dual_ledger_outbox_status_idx
  ON dual_ledger_outbox (status);

CREATE INDEX IF NOT EXISTS dual_ledger_outbox_created_idx
  ON dual_ledger_outbox (created_at);

CREATE INDEX IF NOT EXISTS dual_ledger_outbox_payload_hash_idx
  ON dual_ledger_outbox (payload_hash);

-- Auto-update updated_at timestamp
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS dual_ledger_outbox_set_updated_at ON dual_ledger_outbox;
CREATE TRIGGER dual_ledger_outbox_set_updated_at
BEFORE UPDATE ON dual_ledger_outbox
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
