schema-terminal.sql

-- InsForge: run from repo root with linked project:
--   npx @insforge/cli db import web3-terminal/schema-terminal.sql

CREATE TABLE IF NOT EXISTS terminal_wallets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  username TEXT NOT NULL UNIQUE,
  balance NUMERIC(20, 8) NOT NULL DEFAULT 100 CHECK (balance >= 0),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  last_active_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS terminal_transfers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  from_wallet_id UUID NOT NULL REFERENCES terminal_wallets(id),
  to_wallet_id UUID NOT NULL REFERENCES terminal_wallets(id),
  amount NUMERIC(20, 8) NOT NULL CHECK (amount > 0),
  from_username TEXT NOT NULL,
  to_username TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION terminal_transfer(
  p_from_id UUID,
  p_to_username TEXT,
  p_amount NUMERIC
) RETURNS JSONB AS $$
DECLARE
  b_from NUMERIC;
  v_to_id UUID;
  v_from_name TEXT;
  v_to_name TEXT;
BEGIN
  IF p_amount IS NULL OR p_amount <= 0 THEN
    RETURN jsonb_build_object('ok', false, 'error', 'amount must be positive');
  END IF;

  SELECT balance, username INTO b_from, v_from_name
  FROM terminal_wallets WHERE id = p_from_id FOR UPDATE;
  IF NOT FOUND THEN
    RETURN jsonb_build_object('ok', false, 'error', 'wallet not found');
  END IF;

  IF b_from < p_amount THEN
    RETURN jsonb_build_object('ok', false, 'error', 'insufficient balance');
  END IF;

  SELECT id, username INTO v_to_id, v_to_name
  FROM terminal_wallets WHERE lower(username) = lower(trim(p_to_username));
  IF NOT FOUND THEN
    RETURN jsonb_build_object('ok', false, 'error', 'recipient not found');
  END IF;

  IF v_to_id = p_from_id THEN
    RETURN jsonb_build_object('ok', false, 'error', 'cannot transfer to yourself');
  END IF;

  UPDATE terminal_wallets
  SET balance = balance - p_amount, last_active_at = NOW()
  WHERE id = p_from_id;

  UPDATE terminal_wallets
  SET balance = balance + p_amount, last_active_at = NOW()
  WHERE id = v_to_id;

  INSERT INTO terminal_transfers (
    from_wallet_id, to_wallet_id, amount, from_username, to_username
  ) VALUES (p_from_id, v_to_id, p_amount, v_from_name, v_to_name);

  RETURN jsonb_build_object('ok', true, 'to_username', v_to_name);
END;
$$ LANGUAGE plpgsql;