path: root/src/backenddb/merchant-0001.sql

--
-- This file is part of TALER
-- Copyright (C) 2014--2020 Taler Systems SA
--
-- TALER is free software; you can redistribute it and/or modify it under the
-- terms of the GNU General Public License as published by the Free Software
-- Foundation; either version 3, or (at your option) any later version.
--
-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
-- A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License along with
-- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
--

-- Everything in one big transaction
BEGIN;

-- Check patch versioning is in place.
SELECT _v.register_patch('merchant-0001', NULL, NULL);

---------------- Exchange information ---------------------------

CREATE TABLE IF NOT EXISTS merchant_exchange_wire_fees
  (wirefee_serial BIGSERIAL PRIMARY KEY
  ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)
  ,h_wire_method BYTEA NOT NULL CHECK (LENGTH(h_wire_method)=64)
  ,start_date INT8 NOT NULL
  ,end_date INT8 NOT NULL
  ,wire_fee_val INT8 NOT NULL
  ,wire_fee_frac INT4 NOT NULL
  ,closing_fee_val INT8 NOT NULL
  ,closing_fee_frac INT4 NOT NULL
  ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
  ,UNIQUE (master_pub,h_wire_method,start_date)
  );
COMMENT ON TABLE merchant_exchange_wire_fees
 IS 'Here we store proofs of the wire fee structure of the various exchanges';
COMMENT ON COLUMN merchant_exchange_wire_fees.master_pub
 IS 'Master public key of the exchange with these wire fees';

CREATE TABLE IF NOT EXISTS merchant_exchange_signing_keys
  (signkey_serial BIGSERIAL PRIMARY KEY
  ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)
  ,exchange_pub BYTEA NOT NULL CHECK (LENGTH(exchange_pub)=32)
  ,start_date INT8 NOT NULL
  ,expire_date INT8 NOT NULL
  ,end_date INT8 NOT NULL
  ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64),
  UNIQUE (exchange_pub, start_date, master_pub)
  );
COMMENT ON TABLE merchant_exchange_signing_keys
 IS 'Here we store proofs of the exchange online signing keys being signed by the exchange master key';
COMMENT ON COLUMN merchant_exchange_signing_keys.master_pub
 IS 'Master public key of the exchange with these online signing keys';


-------------------------- Instances  ---------------------------

CREATE TABLE IF NOT EXISTS merchant_instances
  (merchant_serial BIGSERIAL PRIMARY KEY
  ,merchant_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(merchant_pub)=32)
  ,merchant_id VARCHAR NOT NULL UNIQUE
  ,merchant_name VARCHAR NOT NULL
  ,address BYTEA NOT NULL
  ,jurisdiction BYTEA NOT NULL
  ,default_max_deposit_fee_val INT8 NOT NULL
  ,default_max_deposit_fee_frac INT4 NOT NULL
  ,default_max_wire_fee_val INT8 NOT NULL
  ,default_max_wire_fee_frac INT4 NOT NULL
  ,default_wire_fee_amortization INT4 NOT NULL
  ,default_wire_transfer_delay INT8 NOT NULL
  ,default_pay_delay INT8 NOT NULL
  );
COMMENT ON TABLE merchant_instances
  IS 'all the instances supported by this backend';
COMMENT ON COLUMN merchant_instances.merchant_id
  IS 'identifier of the merchant as used in the base URL (required)';
COMMENT ON COLUMN merchant_instances.merchant_name
  IS 'legal name of the merchant as a simple string (required)';
COMMENT ON COLUMN merchant_instances.address
  IS 'physical address of the merchant as a Location in JSON format (required)';
COMMENT ON COLUMN merchant_instances.jurisdiction
  IS 'jurisdiction of the merchant as a Location in JSON format (required)';

CREATE TABLE IF NOT EXISTS merchant_keys
  (merchant_priv BYTEA NOT NULL UNIQUE CHECK (LENGTH(merchant_priv)=32),
   merchant_serial BIGINT PRIMARY KEY
     REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  );
COMMENT ON TABLE merchant_keys
  IS 'private keys of instances that have not been deleted';

CREATE TABLE IF NOT EXISTS merchant_accounts
  (account_serial BIGSERIAL PRIMARY KEY
  ,merchant_serial BIGINT NOT NULL
     REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,h_wire BYTEA NOT NULL CHECK (LENGTH(h_wire)=64)
  ,salt BYTEA NOT NULL CHECK (LENGTH(salt)=64)
  ,payto_uri VARCHAR NOT NULL
  ,active BOOLEAN NOT NULL
  ,UNIQUE (merchant_serial,payto_uri)
  ,UNIQUE (h_wire)
  );
COMMENT ON TABLE merchant_accounts
  IS 'bank accounts of the instances';
COMMENT ON COLUMN merchant_accounts.h_wire
  IS 'salted hash of payto_uri';
COMMENT ON COLUMN merchant_accounts.salt
  IS 'salt used when hashing payto_uri into h_wire';
COMMENT ON COLUMN merchant_accounts.payto_uri
  IS 'payto URI of a merchant bank account';
COMMENT ON COLUMN merchant_accounts.active
  IS 'true if we actively use this bank account, false if it is just kept around for older contracts to refer to';


-------------------------- Inventory  ---------------------------

CREATE TABLE IF NOT EXISTS merchant_inventory
  (product_serial BIGSERIAL PRIMARY KEY
  ,merchant_serial BIGINT NOT NULL
    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,product_id VARCHAR NOT NULL
  ,description VARCHAR NOT NULL
  ,description_i18n BYTEA NOT NULL
  ,unit VARCHAR NOT NULL
  ,image BYTEA NOT NULL
  ,taxes BYTEA NOT NULL
  ,price_val INT8 NOT NULL
  ,price_frac INT4 NOT NULL
  ,total_stock BIGINT NOT NULL
  ,total_sold BIGINT NOT NULL DEFAULT 0
  ,total_lost BIGINT NOT NULL DEFAULT 0
  ,address BYTEA NOT NULL
  ,next_restock INT8 NOT NULL
  ,UNIQUE (merchant_serial, product_id)
  );
COMMENT ON TABLE merchant_inventory
  IS 'products offered by the merchant (may be incomplete, frontend can override)';
COMMENT ON COLUMN merchant_inventory.description
  IS 'Human-readable product description';
COMMENT ON COLUMN merchant_inventory.description_i18n
  IS 'JSON map from IETF BCP 47 language tags to localized descriptions';
COMMENT ON COLUMN merchant_inventory.unit
  IS 'Unit of sale for the product (liters, kilograms, packages)';
COMMENT ON COLUMN merchant_inventory.image
  IS 'NOT NULL, but can be 0 bytes; must contain an ImageDataUrl';
COMMENT ON COLUMN merchant_inventory.taxes
  IS 'JSON array containing taxes the merchant pays, must be JSON, but can be just "[]"';
COMMENT ON COLUMN merchant_inventory.price_val
  IS 'Current price of one unit of the product';
COMMENT ON COLUMN merchant_inventory.total_stock
  IS 'A value of -1 is used for unlimited (electronic good), may never be lowered';
COMMENT ON COLUMN merchant_inventory.total_sold
  IS 'Number of products sold, must be below total_stock, non-negative, may never be lowered';
COMMENT ON COLUMN merchant_inventory.total_lost
  IS 'Number of products that used to be in stock but were lost (spoiled, damaged), may never be lowered; total_stock >= total_sold + total_lost must always hold';
COMMENT ON COLUMN merchant_inventory.address
  IS 'JSON formatted Location of where the product is stocked';
COMMENT ON COLUMN merchant_inventory.next_restock
  IS 'GNUnet absolute time indicating when the next restock is expected. 0 for unknown.';


CREATE TABLE IF NOT EXISTS merchant_inventory_locks
  (product_serial BIGINT NOT NULL
     REFERENCES merchant_inventory (product_serial) -- NO "ON DELETE CASCADE": locks prevent deletion!
  ,lock_uuid BYTEA NOT NULL CHECK (LENGTH(lock_uuid)=16)
  ,total_locked BIGINT NOT NULL
  ,expiration INT8 NOT NULL
  );
CREATE INDEX IF NOT EXISTS merchant_inventory_locks_by_expiration
  ON merchant_inventory_locks
    (expiration);
CREATE INDEX IF NOT EXISTS merchant_inventory_locks_by_uuid
  ON merchant_inventory_locks
    (lock_uuid);
COMMENT ON TABLE merchant_inventory_locks
  IS 'locks on inventory helt by shopping carts; note that locks MAY not be honored if merchants increase total_lost for inventory';
COMMENT ON COLUMN merchant_inventory_locks.total_locked
  IS 'how many units of the product does this lock reserve';
COMMENT ON COLUMN merchant_inventory_locks.expiration
  IS 'when does this lock automatically expire (if no order is created)';


---------------- Orders and contracts ---------------------------

CREATE TABLE IF NOT EXISTS merchant_orders
  (order_serial BIGSERIAL PRIMARY KEY
  ,merchant_serial BIGINT NOT NULL
    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,order_id VARCHAR NOT NULL
  ,claim_token BYTEA NOT NULL CHECK (LENGTH(claim_token)=16)
  ,h_post_data BYTEA NOT NULL CHECK (LENGTH(h_post_data)=64)
  ,pay_deadline INT8 NOT NULL
  ,creation_time INT8 NOT NULL
  ,contract_terms BYTEA NOT NULL
  ,UNIQUE (merchant_serial, order_id)
  );
COMMENT ON TABLE merchant_orders
  IS 'Orders we offered to a customer, but that have not yet been claimed';
COMMENT ON COLUMN merchant_orders.contract_terms
  IS 'Claiming changes the contract_terms, hence we have no hash of the terms in this table';
COMMENT ON COLUMN merchant_orders.h_post_data
  IS 'Hash of the POST request that created this order, for idempotency checks';
COMMENT ON COLUMN merchant_orders.claim_token
  IS 'Token optionally used to authorize the wallet to claim the order. All zeros (not NULL) if not used';
COMMENT ON COLUMN merchant_orders.merchant_serial
  IS 'Identifies the instance offering the contract';
COMMENT ON COLUMN merchant_orders.pay_deadline
  IS 'How long is the offer valid. After this time, the order can be garbage collected';
CREATE INDEX IF NOT EXISTS merchant_orders_by_expiration
  ON merchant_orders
    (pay_deadline);
CREATE INDEX IF NOT EXISTS merchant_orders_by_creation_time
  ON merchant_orders
    (creation_time);

CREATE TABLE IF NOT EXISTS merchant_order_locks
  (product_serial BIGINT NOT NULL
     REFERENCES merchant_inventory (product_serial) -- NO "ON DELETE CASCADE": locks prevent deletion!
  ,total_locked BIGINT NOT NULL
  ,order_serial BIGINT NOT NULL
     REFERENCES merchant_orders (order_serial) ON DELETE CASCADE
  );
CREATE INDEX IF NOT EXISTS merchant_orders_locks_by_order_and_product
  ON merchant_order_locks
    (order_serial, product_serial);
COMMENT ON TABLE merchant_order_locks
  IS 'locks on orders awaiting claim and payment; note that locks MAY not be honored if merchants increase total_lost for inventory';
COMMENT ON COLUMN merchant_order_locks.total_locked
  IS 'how many units of the product does this lock reserve';

CREATE TABLE IF NOT EXISTS merchant_contract_terms
  (order_serial BIGINT PRIMARY KEY
  ,merchant_serial BIGINT NOT NULL
    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,order_id VARCHAR NOT NULL
  ,contract_terms BYTEA NOT NULL
  ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
  ,creation_time INT8 NOT NULL
  ,pay_deadline INT8 NOT NULL
  ,refund_deadline INT8 NOT NULL
  ,paid BOOLEAN DEFAULT FALSE NOT NULL
  ,wired BOOLEAN DEFAULT FALSE NOT NULL
  ,fulfillment_url VARCHAR
  ,session_id VARCHAR DEFAULT '' NOT NULL
  ,UNIQUE (merchant_serial, order_id)
  ,UNIQUE (merchant_serial, h_contract_terms)
  );
COMMENT ON TABLE merchant_contract_terms
  IS 'Contracts are orders that have been claimed by a wallet';
COMMENT ON COLUMN merchant_contract_terms.order_id
  IS 'Not a foreign key into merchant_orders because paid contracts persist after expiration';
COMMENT ON COLUMN merchant_contract_terms.merchant_serial
  IS 'Identifies the instance offering the contract';
COMMENT ON COLUMN merchant_contract_terms.contract_terms
  IS 'These contract terms include the wallet nonce';
COMMENT ON COLUMN merchant_contract_terms.h_contract_terms
  IS 'Hash over contract_terms';
COMMENT ON COLUMN merchant_contract_terms.refund_deadline
  IS 'By what times do refunds have to be approved (useful to reject refund requests)';
COMMENT ON COLUMN merchant_contract_terms.paid
  IS 'true implies the customer paid for this contract; order should be DELETEd from merchant_orders once paid is set to release merchant_order_locks; paid remains true even if the payment was later refunded';
COMMENT ON COLUMN merchant_contract_terms.wired
  IS 'true implies the exchange wired us the full amount for all non-refunded payments under this contract';
COMMENT ON COLUMN merchant_contract_terms.fulfillment_url
  IS 'also included in contract_terms, but we need it here to SELECT on it during repurchase detection; can be NULL if the contract has no fulfillment URL';
COMMENT ON COLUMN merchant_contract_terms.session_id
  IS 'last session_id from we confirmed the paying client to use, empty string for none';
COMMENT ON COLUMN merchant_contract_terms.pay_deadline
  IS 'How long is the offer valid. After this time, the order can be garbage collected';
CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_expiration
  ON merchant_contract_terms
  (merchant_serial,pay_deadline);
CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_payment
  ON merchant_contract_terms
  (merchant_serial,paid);
CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_session_and_fulfillment
  ON merchant_contract_terms
  (merchant_serial,fulfillment_url,session_id);


---------------- Payment and refunds ---------------------------

CREATE TABLE IF NOT EXISTS merchant_deposits
  (deposit_serial BIGSERIAL PRIMARY KEY
  ,order_serial BIGINT
     REFERENCES merchant_contract_terms (order_serial) ON DELETE CASCADE
  ,deposit_timestamp INT8 NOT NULL
  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)
  ,exchange_url VARCHAR NOT NULL
  ,amount_with_fee_val INT8 NOT NULL
  ,amount_with_fee_frac INT4 NOT NULL
  ,deposit_fee_val INT8 NOT NULL
  ,deposit_fee_frac INT4 NOT NULL
  ,refund_fee_val INT8 NOT NULL
  ,refund_fee_frac INT4 NOT NULL
  ,wire_fee_val INT8 NOT NULL
  ,wire_fee_frac INT4 NOT NULL
  ,signkey_serial BIGINT NOT NULL
     REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE
  ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64)
  ,account_serial BIGINT NOT NULL
     REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE
  ,UNIQUE (order_serial, coin_pub)
  );
COMMENT ON TABLE merchant_deposits
  IS 'Table with the deposit confirmations for each coin we deposited at the exchange';
COMMENT ON COLUMN merchant_deposits.signkey_serial
  IS 'Online signing key of the exchange on the deposit confirmation';
COMMENT ON COLUMN merchant_deposits.deposit_timestamp
  IS 'Time when the exchange generated the deposit confirmation';
COMMENT ON COLUMN merchant_deposits.exchange_sig
  IS 'Signature of the exchange over the deposit confirmation';
COMMENT ON COLUMN merchant_deposits.wire_fee_val
  IS 'We MAY want to see if we should try to get this via merchant_exchange_wire_fees (not sure, may be too complicated with the date range, etc.)';

CREATE TABLE IF NOT EXISTS merchant_refunds
  (refund_serial BIGSERIAL PRIMARY KEY
  ,order_serial BIGINT NOT NULL
     REFERENCES merchant_contract_terms (order_serial) ON DELETE CASCADE
  ,rtransaction_id BIGINT NOT NULL
  ,refund_timestamp INT8 NOT NULL
  ,coin_pub BYTEA NOT NULL
  ,reason VARCHAR NOT NULL
  ,refund_amount_val INT8 NOT NULL
  ,refund_amount_frac INT4 NOT NULL
  ,UNIQUE (order_serial, coin_pub, rtransaction_id)
  );
COMMENT ON TABLE merchant_deposits
  IS 'Refunds approved by the merchant (backoffice) logic, excludes abort refunds';
COMMENT ON COLUMN merchant_refunds.rtransaction_id
  IS 'Needed for uniqueness in case a refund is increased for the same order';
COMMENT ON COLUMN merchant_refunds.refund_timestamp
  IS 'Needed for grouping of refunds in the wallet UI; has no semantics in the protocol (only for UX), but should be from the time when the merchant internally approved the refund';
CREATE INDEX IF NOT EXISTS merchant_refunds_by_coin_and_order
  ON merchant_refunds
  (coin_pub,order_serial);

CREATE TABLE IF NOT EXISTS merchant_refund_proofs
  (refund_serial BIGINT PRIMARY KEY
     REFERENCES merchant_refunds (refund_serial) ON DELETE CASCADE
  ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64)
  ,signkey_serial BIGINT NOT NULL
     REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE
);
COMMENT ON TABLE merchant_refund_proofs
  IS 'Refunds confirmed by the exchange (not all approved refunds are grabbed by the wallet)';

-------------------- Wire transfers ---------------------------

CREATE TABLE IF NOT EXISTS merchant_transfers
  (credit_serial BIGSERIAL PRIMARY KEY
  ,exchange_url VARCHAR NOT NULL
  ,wtid BYTEA CHECK (LENGTH(wtid)=32)
  ,credit_amount_val INT8 NOT NULL
  ,credit_amount_frac INT4 NOT NULL
  ,account_serial BIGINT NOT NULL
     REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE
  ,verified BOOLEAN NOT NULL DEFAULT FALSE
  ,confirmed BOOLEAN NOT NULL DEFAULT FALSE
  ,UNIQUE (wtid, exchange_url, account_serial)
  );
COMMENT ON TABLE merchant_transfers
  IS 'table represents the information provided by the (trusted) merchant about incoming wire transfers';
COMMENT ON COLUMN merchant_transfers.verified
  IS 'true once we got an acceptable response from the exchange for this transfer';
COMMENT ON COLUMN merchant_transfers.confirmed
  IS 'true once the merchant confirmed that this transfer was received';
COMMENT ON COLUMN merchant_transfers.credit_amount_val
  IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the merchant';

CREATE TABLE IF NOT EXISTS merchant_transfer_signatures
  (credit_serial BIGINT PRIMARY KEY
     REFERENCES merchant_transfers (credit_serial) ON DELETE CASCADE
  ,signkey_serial BIGINT NOT NULL
     REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE
  ,wire_fee_val INT8 NOT NULL
  ,wire_fee_frac INT4 NOT NULL
  ,execution_time INT8 NOT NULL
  ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64)
  );
COMMENT ON TABLE merchant_transfer_signatures
  IS 'table represents the main information returned from the /transfer request to the exchange.';
COMMENT ON COLUMN merchant_transfer_signatures.execution_time
  IS 'Execution time as claimed by the exchange, roughly matches time seen by merchant';

CREATE TABLE IF NOT EXISTS merchant_transfer_to_coin
  (deposit_serial BIGINT UNIQUE NOT NULL
     REFERENCES merchant_deposits (deposit_serial) ON DELETE CASCADE
  ,credit_serial BIGINT NOT NULL
     REFERENCES merchant_transfers (credit_serial) ON DELETE CASCADE
  ,offset_in_exchange_list INT8 NOT NULL
  ,exchange_deposit_value_val INT8 NOT NULL
  ,exchange_deposit_value_frac INT4 NOT NULL
  ,exchange_deposit_fee_val INT8 NOT NULL
  ,exchange_deposit_fee_frac INT4 NOT NULL
  );
CREATE INDEX IF NOT EXISTS merchant_transfers_by_credit
  ON merchant_transfer_to_coin
  (credit_serial);
COMMENT ON TABLE merchant_transfer_to_coin
  IS 'Mapping of (credit) transfers to (deposited) coins';
COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_value_val
  IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits minus refunds';
COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_fee_val
  IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits';

CREATE TABLE IF NOT EXISTS merchant_deposit_to_transfer
  (deposit_serial BIGINT NOT NULL
     REFERENCES merchant_deposits (deposit_serial) ON DELETE CASCADE
  ,coin_contribution_value_val INT8 NOT NULL
  ,coin_contribution_value_frac INT4 NOT NULL
  ,credit_serial BIGINT NOT NULL
     REFERENCES merchant_transfers (credit_serial)
  ,execution_time INT8 NOT NULL
  ,signkey_serial BIGINT NOT NULL
     REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE
  ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64)
  ,UNIQUE(deposit_serial,credit_serial)
);
COMMENT ON TABLE merchant_deposit_to_transfer
  IS 'Mapping of deposits to (possibly unconfirmed) wire transfers; NOTE: not used yet';
COMMENT ON COLUMN merchant_deposit_to_transfer.execution_time
  IS 'Execution time as claimed by the exchange, roughly matches time seen by merchant';


-------------------------- Tipping ---------------------------

CREATE TABLE IF NOT EXISTS merchant_tip_reserves
  (reserve_serial BIGSERIAL PRIMARY KEY
  ,reserve_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_pub)=32)
  ,merchant_serial BIGINT NOT NULL
    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,creation_time INT8 NOT NULL
  ,expiration INT8 NOT NULL
  ,merchant_initial_balance_val INT8 NOT NULL
  ,merchant_initial_balance_frac INT4 NOT NULL
  ,exchange_initial_balance_val INT8 NOT NULL DEFAULT 0
  ,exchange_initial_balance_frac INT4 NOT NULL DEFAULT 0
  ,tips_committed_val INT8 NOT NULL DEFAULT 0
  ,tips_committed_frac INT4 NOT NULL DEFAULT 0
  ,tips_picked_up_val INT8 NOT NULL DEFAULT 0
  ,tips_picked_up_frac INT4 NOT NULL DEFAULT 0
  );
COMMENT ON TABLE merchant_tip_reserves
  IS 'balances of the reserves available for tips';
COMMENT ON COLUMN merchant_tip_reserves.expiration
  IS 'FIXME: EXCHANGE API needs to tell us when reserves close if we are to compute this';
COMMENT ON COLUMN merchant_tip_reserves.merchant_initial_balance_val
  IS 'Set to the initial balance the merchant told us when creating the reserve';
COMMENT ON COLUMN merchant_tip_reserves.exchange_initial_balance_val
  IS 'Set to the initial balance the exchange told us when we queried the reserve status';
COMMENT ON COLUMN merchant_tip_reserves.tips_committed_val
  IS 'Amount of outstanding approved tips that have not been picked up';
COMMENT ON COLUMN merchant_tip_reserves.tips_picked_up_val
  IS 'Total amount tips that have been picked up from this reserve';
CREATE INDEX IF NOT EXISTS merchant_tip_reserves_by_reserve_pub_and_merchant_serial
  ON merchant_tip_reserves
    (reserve_pub,merchant_serial,creation_time);
CREATE INDEX IF NOT EXISTS merchant_tip_reserves_by_merchant_serial_and_creation_time
  ON merchant_tip_reserves
    (merchant_serial,creation_time);
CREATE INDEX IF NOT EXISTS merchant_tip_reserves_by_exchange_balance
  ON merchant_tip_reserves
    (exchange_initial_balance_val,exchange_initial_balance_frac);



CREATE TABLE IF NOT EXISTS merchant_tip_reserve_keys
  (reserve_serial BIGINT NOT NULL UNIQUE
     REFERENCES merchant_tip_reserves (reserve_serial) ON DELETE CASCADE
  ,reserve_priv BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_priv)=32)
  ,exchange_url VARCHAR NOT NULL
  );
COMMENT ON TABLE merchant_tip_reserves
  IS 'private keys of reserves that have not been deleted';

CREATE TABLE IF NOT EXISTS merchant_tips
  (tip_serial BIGSERIAL PRIMARY KEY
  ,reserve_serial BIGINT NOT NULL
     REFERENCES merchant_tip_reserves (reserve_serial) ON DELETE CASCADE
  ,tip_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(tip_id)=64)
  ,justification VARCHAR NOT NULL
  ,next_url VARCHAR NOT NULL
  ,expiration INT8 NOT NULL
  ,amount_val INT8 NOT NULL
  ,amount_frac INT4 NOT NULL
  ,picked_up_val INT8 NOT NULL DEFAULT 0
  ,picked_up_frac INT4 NOT NULL DEFAULT 0
  ,was_picked_up BOOLEAN NOT NULL DEFAULT FALSE
  );
CREATE INDEX IF NOT EXISTS merchant_tips_by_pickup_and_expiration
  ON merchant_tips
    (was_picked_up,expiration);
COMMENT ON TABLE merchant_tips
  IS 'tips that have been authorized';
COMMENT ON COLUMN merchant_tips.amount_val
  IS 'Overall tip amount';
COMMENT ON COLUMN merchant_tips.picked_up_val
  IS 'Tip amount left to be picked up';
COMMENT ON COLUMN merchant_tips.reserve_serial
  IS 'Reserve from which this tip is funded';
COMMENT ON COLUMN merchant_tips.expiration
  IS 'by when does the client have to pick up the tip';

CREATE TABLE IF NOT EXISTS merchant_tip_pickups
  (pickup_serial BIGSERIAL PRIMARY KEY NOT NULL
  ,tip_serial BIGINT NOT NULL
      REFERENCES merchant_tips (tip_serial) ON DELETE CASCADE
  ,pickup_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(pickup_id)=64)
  ,amount_val INT8 NOT NULL
  ,amount_frac INT4 NOT NULL
  );
COMMENT ON TABLE merchant_tip_pickups
  IS 'tips that have been picked up';
COMMENT ON COLUMN merchant_tips.amount_val
  IS 'total transaction cost for all coins including withdraw fees';

CREATE TABLE IF NOT EXISTS merchant_tip_pickup_signatures
  (pickup_serial INT8 NOT NULL
     REFERENCES merchant_tip_pickups (pickup_serial) ON DELETE CASCADE
  ,coin_offset INT4 NOT NULL
  ,blind_sig BYTEA NOT NULL
  ,PRIMARY KEY (pickup_serial, coin_offset)
  );
COMMENT ON TABLE merchant_tip_pickup_signatures
  IS 'blind signatures we got from the exchange during the tip pickup';


-- Complete transaction
COMMIT;