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 coordianator (
  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
102-- +migrate StatementBegin
103CREATE FUNCTION set_tx()
104    RETURNS TRIGGER 
105AS 
106$BODY$
107DECLARE token_value NUMERIC := (SELECT usd FROM token WHERE token_id = NEW.token_id);
108BEGIN
109    -- Validate L1/L2 constrains
110    IF NEW.is_l1 AND (( -- L1 mandatory fields
111        NEW.user_origin IS NULL OR
112        NEW.from_eth_addr IS NULL OR
113        NEW.from_bjj IS NULL OR
114        NEW.load_amount IS NULL OR
115        NEW.load_amount_f IS NULL
116    ) OR (NOT NEW.user_origin AND NEW.batch_num IS NULL)) THEN -- If is Coordinator L1, must include batch_num
117        RAISE EXCEPTION 'Invalid L1 tx.';
118    ELSIF NOT NEW.is_l1 THEN
119        IF NEW.fee IS NULL THEN
120            NEW.fee = (SELECT 0);
121        END IF;
122        IF NEW.batch_num IS NULL OR NEW.nonce IS NULL THEN
123            RAISE EXCEPTION 'Invalid L2 tx.';
124        END IF;
125    END IF;
126    -- If is L2, add token_id
127    IF NEW.token_id IS NULL THEN
128        NEW."token_id" = (SELECT token_id FROM account WHERE idx = NEW."from_idx");
129    END IF;
130    -- Set value_usd
131    NEW."amount_usd" = (SELECT token_value * NEW.amount_f);
132    NEW."load_amount_usd" = (SELECT token_value * NEW.load_amount_f);
133    IF NOT NEW.is_l1 THEN
134        NEW."fee_usd" = (SELECT token_value * NEW.amount_f * CASE
135            WHEN NEW.fee = 0 THEN 0	
136            WHEN NEW.fee >= 1 AND NEW.fee <= 32 THEN POWER(10,-24+(NEW.fee::float/2))	
137            WHEN NEW.fee >= 33 AND NEW.fee <= 223 THEN POWER(10,-8+(0.041666666666667*(NEW.fee::float-32)))	
138            WHEN NEW.fee >= 224 AND NEW.fee <= 255 THEN POWER(10,NEW.fee-224) END);
139    END IF;
140    RETURN NEW;
141END;
142$BODY$
143LANGUAGE plpgsql;
144-- +migrate StatementEnd
145CREATE TRIGGER trigger_set_tx BEFORE INSERT ON tx
146FOR EACH ROW EXECUTE PROCEDURE set_tx();
147
148-- +migrate StatementBegin
149CREATE FUNCTION forge_l1_user_txs() 
150    RETURNS TRIGGER 
151AS 
152$BODY$
153BEGIN
154    IF NEW.forge_l1_txs_num IS NOT NULL THEN
155        UPDATE tx 
156        SET batch_num = NEW.batch_num
157        WHERE user_origin AND NEW.forge_l1_txs_num = to_forge_l1_txs_num;
158    END IF;
159    RETURN NEW;
160END;
161$BODY$
162LANGUAGE plpgsql;
163-- +migrate StatementEnd
164CREATE TRIGGER trigger_forge_l1_txs AFTER INSERT ON batch
165FOR EACH ROW EXECUTE PROCEDURE forge_l1_user_txs();
166
167CREATE TABLE account (
168    idx BIGINT PRIMARY KEY,
169    token_id INT NOT NULL REFERENCES token (token_id) ON DELETE CASCADE,
170    batch_num BIGINT NOT NULL REFERENCES batch (batch_num) ON DELETE CASCADE,
171    bjj BYTEA NOT NULL,
172    eth_addr BYTEA NOT NULL
173);
174
175CREATE TABLE rollup_vars (
176    eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
177    forge_l1_timeout BYTEA NOT NULL,
178    fee_l1_user_tx BYTEA NOT NULL,
179    fee_add_token BYTEA NOT NULL,
180    tokens_hez BYTEA NOT NULL,
181    governance BYTEA NOT NULL
182);
183
184CREATE TABLE consensus_vars (
185    eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
186    slot_deadline INT NOT NULL,
187    close_auction_slots INT NOT NULL,
188    open_auction_slots INT NOT NULL,
189    min_bid_slots VARCHAR(200) NOT NULL,
190    outbidding INT NOT NULL,
191    donation_address BYTEA NOT NULL,
192    governance_address BYTEA NOT NULL,
193    allocation_ratio VARCHAR(200)
194);
195
196-- +migrate Down
197DROP TABLE consensus_vars;
198DROP TABLE rollup_vars;
199DROP TABLE account;
200DROP TABLE l2tx;
201DROP TABLE l1tx;
202DROP TABLE token;
203DROP TABLE bid;
204DROP TABLE exit_tree;
205DROP TABLE batch;
206DROP TABLE coordianator;
207DROP TABLE block;