-- BizMatch Phase 2 — module 6a, second cut: the NDA inbox reads from the DB
--
-- Proxying the Dropbox Sign list API on every view mount meant 7-8 paged calls
-- and ~74s for a 90-day window, rate-limit errors, and the whole list lost on
-- a tab switch. The requests are now mirrored here by a background refresh and
-- the inbox only ever reads this table.

CREATE TABLE ds_request (
  signature_request_id text PRIMARY KEY,
  title                text,
  created_at           timestamptz,
  status               text NOT NULL CHECK (status IN ('pending', 'signed', 'declined')),
  signer_name          text,
  signer_email         text,
  signed_at            timestamptz,
  -- When we last saw this row on the Dropbox side.
  fetched_at           timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ds_request_created_idx ON ds_request (created_at DESC);
-- The inbox resolves the known buyer over this, same normalisation as contact.
CREATE INDEX ds_request_email_idx ON ds_request (lower(btrim(signer_email)))
  WHERE signer_email IS NOT NULL;

-- Small key/value store for state that has no natural home. Currently
-- ds_last_refresh_at and ds_refresh_state (idle | running | error:<msg>).
CREATE TABLE app_meta (
  key        text PRIMARY KEY,
  value      text,
  updated_at timestamptz NOT NULL DEFAULT now()
);