-- SAL extension: positions (asset x chain), fees, reconciliation snapshots.
-- Run after 005_post_ledger_entry.sql.

-- Positions: inventory per account per asset per chain.
CREATE TABLE IF NOT EXISTS sal_positions (
  id           TEXT PRIMARY KEY,
  account_id   TEXT NOT NULL,
  asset        TEXT NOT NULL,
  chain_id     INTEGER NOT NULL,
  balance      NUMERIC(32, 18) NOT NULL DEFAULT 0,
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(account_id, asset, chain_id)
);

CREATE INDEX IF NOT EXISTS idx_sal_positions_account ON sal_positions(account_id);
CREATE INDEX IF NOT EXISTS idx_sal_positions_chain ON sal_positions(chain_id);
CREATE INDEX IF NOT EXISTS idx_sal_positions_asset ON sal_positions(asset);

-- Fees: gas and protocol fees per chain/tx.
CREATE TABLE IF NOT EXISTS sal_fees (
  id            TEXT PRIMARY KEY,
  reference_id  TEXT NOT NULL,
  chain_id      INTEGER NOT NULL,
  tx_hash       TEXT,
  fee_type      TEXT NOT NULL,
  amount        NUMERIC(32, 18) NOT NULL,
  currency_code TEXT NOT NULL DEFAULT 'native',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sal_fees_reference ON sal_fees(reference_id);
CREATE INDEX IF NOT EXISTS idx_sal_fees_chain ON sal_fees(chain_id);
CREATE INDEX IF NOT EXISTS idx_sal_fees_tx ON sal_fees(tx_hash);

-- Reconciliation snapshots: on-chain balance vs SAL.
CREATE TABLE IF NOT EXISTS sal_reconciliation_snapshots (
  id              TEXT PRIMARY KEY,
  account_id      TEXT NOT NULL,
  asset           TEXT NOT NULL,
  chain_id        INTEGER NOT NULL,
  sal_balance     NUMERIC(32, 18) NOT NULL,
  on_chain_balance NUMERIC(32, 18),
  block_number    BIGINT,
  discrepancy     NUMERIC(32, 18),
  status          TEXT NOT NULL DEFAULT 'ok',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sal_recon_account ON sal_reconciliation_snapshots(account_id);
CREATE INDEX IF NOT EXISTS idx_sal_recon_chain ON sal_reconciliation_snapshots(chain_id);
CREATE INDEX IF NOT EXISTS idx_sal_recon_created ON sal_reconciliation_snapshots(created_at);
