-- Monitored bank accounts (accounts we own and watch for incoming transfers)
CREATE TABLE IF NOT EXISTS accounts (
  id             TEXT PRIMARY KEY,
  bank           TEXT NOT NULL,           -- 'bci', 'santander', 'bancochile', etc.
  account_number TEXT NOT NULL,
  owner_name     TEXT NOT NULL,
  owner_rut      TEXT NOT NULL,           -- normalized: 12345678-9
  check_digit    TEXT,                    -- BCI requires separate check digit
  api_key        TEXT,                    -- BCI subscription key
  webhook_secret TEXT,                    -- key we embed in BCI subscription; BCI echoes it back in webhooks
  bci_subscription_id TEXT,
  active         BOOLEAN NOT NULL DEFAULT true,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (bank, account_number)
);

-- Expected payments registered by downstream services
CREATE TABLE IF NOT EXISTS payment_intents (
  id              TEXT PRIMARY KEY,
  source_app      TEXT NOT NULL,          -- 'muralla', 'rover', etc.
  account_id      TEXT NOT NULL REFERENCES accounts(id),
  nominal_amount  INTEGER NOT NULL,       -- what the customer "should" pay
  exact_amount    INTEGER NOT NULL,       -- jittered: always <= nominal
  jitter          INTEGER NOT NULL DEFAULT 0,
  sender_rut      TEXT,                   -- expected sender RUT (match hint, optional)
  reference_code  TEXT NOT NULL UNIQUE,   -- 6-char code; customer puts in transfer description
  callback_url    TEXT NOT NULL,
  callback_secret TEXT,                   -- HMAC-SHA256 key for signing confirmations
  status          TEXT NOT NULL DEFAULT 'pending',  -- pending | matched | expired | cancelled
  metadata        JSONB,                  -- passed through to callback payload
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at      TIMESTAMPTZ NOT NULL,
  matched_at      TIMESTAMPTZ,
  inbound_id      TEXT
);

-- Unique constraint: no two live intents can share the same exact_amount on the same account
CREATE UNIQUE INDEX IF NOT EXISTS payment_intents_slot
  ON payment_intents (exact_amount, account_id)
  WHERE status = 'pending';

CREATE INDEX IF NOT EXISTS payment_intents_rut
  ON payment_intents (sender_rut)
  WHERE status = 'pending';

CREATE INDEX IF NOT EXISTS payment_intents_status
  ON payment_intents (status, expires_at);

-- Inbound transfers detected from any source
CREATE TABLE IF NOT EXISTS inbound_payments (
  id          TEXT PRIMARY KEY,
  source      TEXT NOT NULL,              -- 'bci_webhook' | 'bank_parser' | 'email'
  account_id  TEXT REFERENCES accounts(id),
  amount      INTEGER NOT NULL,           -- whole pesos (CLP)
  sender_rut  TEXT,
  description TEXT,                       -- transfer description / gloss
  received_at TIMESTAMPTZ NOT NULL,       -- when the bank says the transfer happened
  detected_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  status      TEXT NOT NULL DEFAULT 'unaccounted',  -- unaccounted | matched | ignored
  intent_id   TEXT,
  bank_ref    TEXT,                       -- bank's own transaction ID for deduplication
  raw         JSONB NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS inbound_bank_ref
  ON inbound_payments (bank_ref)
  WHERE bank_ref IS NOT NULL;

CREATE INDEX IF NOT EXISTS inbound_status ON inbound_payments (status);
CREATE INDEX IF NOT EXISTS inbound_detected ON inbound_payments (detected_at DESC);

-- Match audit log
CREATE TABLE IF NOT EXISTS match_log (
  id                TEXT PRIMARY KEY,
  intent_id         TEXT NOT NULL,
  inbound_id        TEXT NOT NULL,
  confidence        TEXT NOT NULL,        -- 'reference_code' | 'exact_amount_rut' | 'amount_only'
  callback_status   INTEGER,
  callback_attempts INTEGER DEFAULT 0,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);