1-- +migrate Up
  2CREATE TABLE block (
  3    eth_block_num BIGINT PRIMARY KEY,
  4    timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  5    hash BYTEA NOT NULL
  6);
  7
  8CREATE TABLE coordinator (
  9    forger_addr BYTEA NOT NULL,
 10    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 11    withdraw_addr BYTEA NOT NULL,
 12    url VARCHAR(200) NOT NULL,
 13    PRIMARY KEY (forger_addr, eth_block_num)
 14);
 15
 16CREATE TABLE batch (
 17    batch_num BIGINT PRIMARY KEY,
 18    eth_block_num BIGINT REFERENCES block (eth_block_num) ON DELETE CASCADE,
 19    forger_addr BYTEA NOT NULL, -- fake foreign key for coordinator
 20    fees_collected BYTEA NOT NULL,
 21    state_root BYTEA NOT NULL,
 22    num_accounts BIGINT NOT NULL,
 23    exit_root BYTEA NOT NULL,
 24    forge_l1_txs_num BIGINT,
 25    slot_num BIGINT NOT NULL
 26);
 27
 28CREATE TABLE exit_tree (
 29    batch_num BIGINT REFERENCES batch (batch_num) ON DELETE CASCADE,
 30    withdrawn BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL,
 31    account_idx BIGINT,
 32    merkle_proof BYTEA NOT NULL,
 33    balance NUMERIC NOT NULL,
 34    nullifier BYTEA NOT NULL,
 35    PRIMARY KEY (batch_num, account_idx)
 36);
 37
 38CREATE TABLE bid (
 39    slot_num BIGINT NOT NULL,
 40    bid_value BYTEA NOT NULL,
 41    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 42    forger_addr BYTEA NOT NULL, -- fake foreign key for coordinator
 43    PRIMARY KEY (slot_num, bid_value)
 44);
 45
 46CREATE TABLE token (
 47    token_id INT PRIMARY KEY,
 48    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 49    eth_addr BYTEA UNIQUE NOT NULL,
 50    name VARCHAR(20) NOT NULL,
 51    symbol VARCHAR(10) NOT NULL,
 52    decimals INT NOT NULL,
 53    usd NUMERIC,
 54    usd_update TIMESTAMP
 55);
 56
 57-- +migrate StatementBegin
 58CREATE FUNCTION set_token_usd_update() 
 59    RETURNS TRIGGER 
 60AS 
 61$BODY$
 62BEGIN
 63    IF NEW."usd" IS NOT NULL AND NEW."usd_update" IS NULL THEN
 64        NEW."usd_update" = timezone('utc', now());
 65    END IF;
 66    RETURN NEW;
 67END;
 68$BODY$
 69LANGUAGE plpgsql;
 70-- +migrate StatementEnd
 71CREATE TRIGGER trigger_token_usd_update BEFORE UPDATE OR INSERT ON token
 72FOR EACH ROW EXECUTE PROCEDURE set_token_usd_update();
 73
 74CREATE TABLE tx (
 75    -- Generic TX
 76    is_l1 BOOLEAN NOT NULL,
 77    id BYTEA PRIMARY KEY,
 78    type VARCHAR(40) NOT NULL,
 79    position INT NOT NULL,
 80    from_idx BIGINT NOT NULL,
 81    to_idx BIGINT NOT NULL,
 82    amount BYTEA NOT NULL,
 83    amount_f NUMERIC NOT NULL,
 84    token_id INT NOT NULL REFERENCES token (token_id),
 85    amount_usd NUMERIC, -- Value of the amount in USD at the moment the tx was inserted in the DB
 86    batch_num BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL, -- Can be NULL in the case of L1 txs that are on the queue but not forged yet.
 87    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 88    -- L1
 89    to_forge_l1_txs_num BIGINT,
 90    user_origin BOOLEAN,
 91    from_eth_addr BYTEA,
 92    from_bjj BYTEA,
 93    load_amount BYTEA,
 94    load_amount_f NUMERIC,
 95    load_amount_usd NUMERIC,
 96    -- L2
 97    fee INT,
 98    fee_usd NUMERIC,
 99    nonce BIGINT
100);
101
102CREATE INDEX tx_order ON tx (batch_num, position);
103
104-- +migrate StatementBegin
105CREATE FUNCTION set_tx()
106    RETURNS TRIGGER 
107AS 
108$BODY$
109DECLARE token_value NUMERIC := (SELECT usd FROM token WHERE token_id = NEW.token_id);
110BEGIN
111    -- Validate L1/L2 constrains
112    IF NEW.is_l1 AND (( -- L1 mandatory fields
113        NEW.user_origin IS NULL OR
114        NEW.from_eth_addr IS NULL OR
115        NEW.from_bjj IS NULL OR
116        NEW.load_amount IS NULL OR
117        NEW.load_amount_f IS NULL
118    ) OR (NOT NEW.user_origin AND NEW.batch_num IS NULL)) THEN -- If is Coordinator L1, must include batch_num
119        RAISE EXCEPTION 'Invalid L1 tx.';
120    ELSIF NOT NEW.is_l1 THEN
121        IF NEW.fee IS NULL THEN
122            NEW.fee = (SELECT 0);
123        END IF;
124        IF NEW.batch_num IS NULL OR NEW.nonce IS NULL THEN
125            RAISE EXCEPTION 'Invalid L2 tx.';
126        END IF;
127    END IF;
128    -- If is L2, add token_id
129    IF NEW.token_id IS NULL THEN
130        NEW."token_id" = (SELECT token_id FROM account WHERE idx = NEW."from_idx");
131    END IF;
132    -- Set value_usd
133    NEW."amount_usd" = (SELECT token_value * NEW.amount_f);
134    NEW."load_amount_usd" = (SELECT token_value * NEW.load_amount_f);
135    IF NOT NEW.is_l1 THEN
136        NEW."fee_usd" = (SELECT token_value * NEW.amount_f * CASE
137            WHEN NEW.fee = 0 THEN 0	
138            WHEN NEW.fee >= 1 AND NEW.fee <= 32 THEN POWER(10,-24+(NEW.fee::float/2))	
139            WHEN NEW.fee >= 33 AND NEW.fee <= 223 THEN POWER(10,-8+(0.041666666666667*(NEW.fee::float-32)))	
140            WHEN NEW.fee >= 224 AND NEW.fee <= 255 THEN POWER(10,NEW.fee-224) END);
141    END IF;
142    RETURN NEW;
143END;
144$BODY$
145LANGUAGE plpgsql;
146-- +migrate StatementEnd
147CREATE TRIGGER trigger_set_tx BEFORE INSERT ON tx
148FOR EACH ROW EXECUTE PROCEDURE set_tx();
149
150-- +migrate StatementBegin
151CREATE FUNCTION forge_l1_user_txs() 
152    RETURNS TRIGGER 
153AS 
154$BODY$
155BEGIN
156    IF NEW.forge_l1_txs_num IS NOT NULL THEN
157        UPDATE tx 
158        SET batch_num = NEW.batch_num
159        WHERE user_origin AND NEW.forge_l1_txs_num = to_forge_l1_txs_num;
160    END IF;
161    RETURN NEW;
162END;
163$BODY$
164LANGUAGE plpgsql;
165-- +migrate StatementEnd
166CREATE TRIGGER trigger_forge_l1_txs AFTER INSERT ON batch
167FOR EACH ROW EXECUTE PROCEDURE forge_l1_user_txs();
168
169CREATE TABLE account (
170    idx BIGINT PRIMARY KEY,
171    token_id INT NOT NULL REFERENCES token (token_id) ON DELETE CASCADE,
172    batch_num BIGINT NOT NULL REFERENCES batch (batch_num) ON DELETE CASCADE,
173    bjj BYTEA NOT NULL,
174    eth_addr BYTEA NOT NULL
175);
176
177CREATE TABLE rollup_vars (
178    eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
179    forge_l1_timeout BYTEA NOT NULL,
180    fee_l1_user_tx BYTEA NOT NULL,
181    fee_add_token BYTEA NOT NULL,
182    tokens_hez BYTEA NOT NULL,
183    governance BYTEA NOT NULL
184);
185
186CREATE TABLE consensus_vars (
187    eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
188    slot_deadline INT NOT NULL,
189    close_auction_slots INT NOT NULL,
190    open_auction_slots INT NOT NULL,
191    min_bid_slots VARCHAR(200) NOT NULL,
192    outbidding INT NOT NULL,
193    donation_address BYTEA NOT NULL,
194    governance_address BYTEA NOT NULL,
195    allocation_ratio VARCHAR(200)
196);
197
198-- +migrate Down
199DROP TABLE consensus_vars;
200DROP TABLE rollup_vars;
201DROP TABLE account;
202DROP TABLE tx;
203DROP TABLE token;
204DROP TABLE bid;
205DROP TABLE exit_tree;
206DROP TABLE batch;
207DROP TABLE coordinator;
208DROP TABLE block;