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

--
-- This file is part of TALER
-- Copyright (C) 2014--2023 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/>
--

-- @file merchant-0001.sql
-- @brief database schema for the merchant
-- @author Christian Grothoff
-- @author Priscilla Huang

-- Everything in one big transaction
BEGIN;

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

CREATE SCHEMA merchant;
COMMENT ON SCHEMA merchant IS 'taler-merchant data';

SET search_path TO merchant;

CREATE TYPE taler_amount_currency
  AS
  (val INT8
  ,frac INT4
  ,curr VARCHAR(12)
  );
COMMENT ON TYPE taler_amount_currency
  IS 'Stores an amount, fraction is in units of 1/100000000 of the base value';

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

CREATE TABLE IF NOT EXISTS merchant_exchange_wire_fees
  (wirefee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY 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 taler_amount_currency NOT NULL
  ,closing_fee taler_amount_currency 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 BIGINT GENERATED BY DEFAULT AS IDENTITY 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 BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,merchant_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(merchant_pub)=32)
  ,auth_hash BYTEA CHECK(LENGTH(auth_hash)=64)
  ,auth_salt BYTEA CHECK(LENGTH(auth_salt)=32)
  ,merchant_id TEXT NOT NULL UNIQUE
  ,merchant_name TEXT NOT NULL
  ,website TEXT
  ,email TEXT
  ,logo BYTEA
  ,address BYTEA NOT NULL
  ,jurisdiction BYTEA NOT NULL
  ,use_stefan BOOLEAN NOT NULL DEFAULT TRUE
  ,default_wire_transfer_delay INT8 NOT NULL
  ,default_pay_delay INT8 NOT NULL
  ,user_type INT4
  );
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)';
COMMENT ON COLUMN merchant_instances.website
  IS 'merchant site URL';
COMMENT ON COLUMN merchant_instances.use_stefan
  IS 'use STEFAN curve of exchange to determine acceptable fees (unless given explicitly)';
COMMENT ON COLUMN merchant_instances.email
  IS 'email';
COMMENT ON COLUMN merchant_instances.logo
  IS 'data image url';
COMMENT ON COLUMN merchant_instances.auth_hash
  IS 'hash used for merchant back office authorization, NULL for no check';
COMMENT ON COLUMN merchant_instances.auth_salt
  IS 'salt to use when hashing Authorization header before comparing with auth_hash';
COMMENT ON COLUMN merchant_instances.user_type
  IS 'what type of user is this (individual or business)';


CREATE TABLE IF NOT EXISTS merchant_login_tokens
  (token BYTEA NOT NULL UNIQUE CHECK (LENGTH(token)=32),
   creation_time INT8 NOT NULL,
   expiration_time INT8 NOT NULL,
   validity_scope INT4 NOT NULL,
   merchant_serial BIGINT
     REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  );
COMMENT ON TABLE merchant_login_tokens
  IS 'login tokens that have been created for the given instance';
COMMENT ON COLUMN merchant_login_tokens.token
  IS 'binary value of the login token';
COMMENT ON COLUMN merchant_login_tokens.creation_time
  IS 'time when the token was created; currently not used, potentially useful in the future for a forced logout of all tokens issued before a certain date';
COMMENT ON COLUMN merchant_login_tokens.expiration_time
  IS 'determines when the token expires';
COMMENT ON COLUMN merchant_login_tokens.validity_scope
  IS 'identifies the operations for which the token is valid';
COMMENT ON COLUMN merchant_login_tokens.merchant_serial
  IS 'identifies the instance for which the token is valid';

CREATE INDEX IF NOT EXISTS merchant_login_tokens_by_expiration
  ON merchant_login_tokens
    (expiration_time);


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 BIGINT GENERATED BY DEFAULT AS IDENTITY 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)=16)
  ,credit_facade_url TEXT
  ,credit_facade_credentials TEXT
  ,last_bank_serial INT8 NOT NULL DEFAULT (0)
  ,payto_uri TEXT 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';
COMMENT ON COLUMN merchant_accounts.credit_facade_url
  IS 'Base URL of a facade where the merchant can inquire about incoming bank transactions into this account';
COMMENT ON COLUMN merchant_accounts.credit_facade_credentials
  IS 'JSON with credentials needed to access the credit facade';
COMMENT ON COLUMN merchant_accounts.last_bank_serial
  IS 'Serial number of the bank of the last transaction we successfully imported';


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

CREATE TABLE IF NOT EXISTS merchant_inventory
  (product_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,merchant_serial BIGINT NOT NULL
    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,product_id TEXT NOT NULL
  ,description TEXT NOT NULL
  ,description_i18n BYTEA NOT NULL
  ,unit TEXT NOT NULL
  ,image BYTEA NOT NULL
  ,taxes BYTEA NOT NULL
  ,price taler_amount_currency 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
  ,minimum_age INT4 NOT NULL DEFAULT 0
  ,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
  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 i    ndicating when the next restock is expected. 0 for unknown.';
COMMENT ON COLUMN merchant_inventory.minimum_age
  IS 'Minimum age of the customer in years, to be used if an exchange supports the age restriction extension.';


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 BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,merchant_serial BIGINT NOT NULL
    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,order_id TEXT 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
  ,pos_key TEXT DEFAULT NULL
  ,pos_algorithm INT NOT NULL DEFAULT (0)
  ,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';
COMMENT ON COLUMN merchant_orders.pos_key
  IS 'encoded based key which is used for the verification of payment';
COMMENT ON COLUMN merchant_orders.pos_algorithm
  IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';


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 TEXT NOT NULL
  ,contract_terms BYTEA NOT NULL
  ,wallet_data TEXT DEFAULT 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 TEXT
  ,session_id TEXT DEFAULT '' NOT NULL
  ,pos_key TEXT DEFAULT NULL
  ,pos_algorithm INT NOT NULL DEFAULT (0)
  ,claim_token BYTEA NOT NULL CHECK (LENGTH(claim_token)=16)
  ,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.wallet_data
  IS 'Data provided by the wallet when paying for the contract (subcontract selection, blinded tokens, etc.)';
COMMENT ON COLUMN merchant_contract_terms.h_contract_terms
  IS 'Hash over contract_terms';
COMMENT ON COLUMN merchant_contract_terms.pay_deadline
  IS 'How long is the offer valid. After this time, the order can be garbage collected';
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';
COMMENT  ON COLUMN merchant_contract_terms.pos_key
  IS 'enconded based key which is used for the verification of payment';
COMMENT ON COLUMN merchant_orders.pos_algorithm
  IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';

COMMENT ON COLUMN merchant_contract_terms.claim_token
  IS 'Token optionally used to access the status of the order. All zeros (not NULL) if not used';

CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_expiration
  ON merchant_contract_terms
  (merchant_serial,pay_deadline);
COMMENT ON INDEX merchant_contract_terms_by_merchant_and_expiration
  IS 'for delete_contract_terms';
CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_expiration
  ON merchant_contract_terms
  (paid,pay_deadline);
COMMENT ON INDEX merchant_contract_terms_by_expiration
  IS 'for unlock_contracts';
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_deposit_confirmations
  (deposit_confirmation_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,order_serial BIGINT
     REFERENCES merchant_contract_terms (order_serial) ON DELETE CASCADE
  ,deposit_timestamp INT8 NOT NULL
  ,exchange_url TEXT NOT NULL
  ,total_without_fee taler_amount_currency NOT NULL
  ,wire_fee taler_amount_currency 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, exchange_sig)
  );
COMMENT ON TABLE merchant_deposit_confirmations
  IS 'Table with the deposit confirmations for each coin we deposited at the exchange';
COMMENT ON COLUMN merchant_deposit_confirmations.signkey_serial
  IS 'Online signing key of the exchange on the deposit confirmation';
COMMENT ON COLUMN merchant_deposit_confirmations.deposit_timestamp
  IS 'Time when the exchange generated the deposit confirmation';
COMMENT ON COLUMN merchant_deposit_confirmations.exchange_sig
  IS 'Signature of the exchange over the deposit confirmation';
COMMENT ON COLUMN merchant_deposit_confirmations.wire_fee
  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_deposits
  (deposit_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,coin_offset INT4 NOT NULL
  ,deposit_confirmation_serial BIGINT NOT NULL
   REFERENCES merchant_deposit_confirmations (deposit_confirmation_serial) ON DELETE CASCADE
  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)
  ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)
  ,amount_with_fee taler_amount_currency NOT NULL
  ,deposit_fee taler_amount_currency NOT NULL
  ,refund_fee taler_amount_currency NOT NULL
  ,UNIQUE (deposit_confirmation_serial, coin_pub)
  );
COMMENT ON TABLE merchant_deposits
  IS 'Table with the deposit details for each coin we deposited at the exchange';
COMMENT ON COLUMN merchant_deposits.coin_offset
  IS 'Offset of this coin in the batch';
COMMENT ON COLUMN merchant_deposits.deposit_confirmation_serial
  IS 'Reference to the deposit confirmation of the exchange';
COMMENT ON COLUMN merchant_deposits.coin_pub
  IS 'Public key of the coin that was deposited';
COMMENT ON COLUMN merchant_deposits.amount_with_fee
  IS 'Total amount (incl. fee) of the coin that was deposited';
COMMENT ON COLUMN merchant_deposits.deposit_fee
  IS 'Deposit fee (for this coin) that was paid';
COMMENT ON COLUMN merchant_deposits.refund_fee
  IS 'How high would the refund fee be (for this coin)';


CREATE TABLE IF NOT EXISTS merchant_refunds
  (refund_serial BIGINT GENERATED BY DEFAULT AS IDENTITY 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 TEXT NOT NULL
  ,refund_amount taler_amount_currency 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 INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,exchange_url TEXT NOT NULL
  ,wtid BYTEA CHECK (LENGTH(wtid)=32)
  ,credit_amount taler_amount_currency NOT NULL
  ,account_serial INT8 NOT NULL
   REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE
  ,ready_time INT8 NOT NULL DEFAULT (0)
  ,validation_status INT4 DEFAULT NULL
  ,failed BOOLEAN NOT NULL DEFAULT FALSE
  ,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
  IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the merchant';
COMMENT ON COLUMN merchant_transfers.failed
  IS 'set to true on permanent verification failures';
COMMENT ON COLUMN merchant_transfers.validation_status
  IS 'Taler error code describing the state of the validation';

CREATE INDEX merchant_transfers_by_open
  ON merchant_transfers
  (ready_time ASC)
  WHERE confirmed AND NOT (failed OR verified);
COMMENT ON INDEX merchant_transfers_by_open
  IS 'For select_open_transfers';


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 taler_amount_currency NOT NULL
  ,credit_amount taler_amount_currency 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';
COMMENT ON COLUMN merchant_transfer_signatures.credit_amount
  IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the exchange';


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 taler_amount_currency NOT NULL
  ,exchange_deposit_fee taler_amount_currency 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
  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
  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 taler_amount_currency 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';


-------------------------- Rewards ---------------------------

CREATE TABLE IF NOT EXISTS merchant_reward_reserves
  (reserve_serial BIGINT GENERATED BY DEFAULT AS IDENTITY 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 taler_amount_currency NOT NULL
  ,exchange_initial_balance taler_amount_currency NOT NULL
  ,rewards_committed taler_amount_currency NOT NULL
  ,rewards_picked_up taler_amount_currency NOT NULL
  );
COMMENT ON TABLE merchant_reward_reserves
  IS 'balances of the reserves available for rewards';
COMMENT ON COLUMN merchant_reward_reserves.expiration
  IS 'FIXME: EXCHANGE API needs to tell us when reserves close if we are to compute this';
COMMENT ON COLUMN merchant_reward_reserves.merchant_initial_balance
  IS 'Set to the initial balance the merchant told us when creating the reserve';
COMMENT ON COLUMN merchant_reward_reserves.exchange_initial_balance
  IS 'Set to the initial balance the exchange told us when we queried the reserve status';
COMMENT ON COLUMN merchant_reward_reserves.rewards_committed
  IS 'Amount of outstanding approved rewards that have not been picked up';
COMMENT ON COLUMN merchant_reward_reserves.rewards_picked_up
  IS 'Total amount rewards that have been picked up from this reserve';

CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_reserve_pub_and_merchant_serial
  ON merchant_reward_reserves
    (reserve_pub,merchant_serial,creation_time);
CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_merchant_serial_and_creation_time
  ON merchant_reward_reserves
    (merchant_serial,creation_time);
CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_exchange_balance
  ON merchant_reward_reserves
    (exchange_initial_balance);



CREATE TABLE IF NOT EXISTS merchant_reward_reserve_keys
  (reserve_serial BIGINT NOT NULL UNIQUE
     REFERENCES merchant_reward_reserves (reserve_serial) ON DELETE CASCADE
  ,reserve_priv BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_priv)=32)
  ,exchange_url TEXT NOT NULL
  ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)
  );
COMMENT ON TABLE merchant_reward_reserves
  IS 'private keys of reserves that have not been deleted';
COMMENT ON COLUMN merchant_reward_reserve_keys.master_pub
  IS 'Master public key of the exchange to which the reserve belongs';


CREATE TABLE IF NOT EXISTS merchant_rewards
  (reward_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,reserve_serial BIGINT NOT NULL
     REFERENCES merchant_reward_reserves (reserve_serial) ON DELETE CASCADE
  ,reward_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(reward_id)=64)
  ,justification TEXT NOT NULL
  ,next_url TEXT NOT NULL
  ,expiration INT8 NOT NULL
  ,amount taler_amount_currency NOT NULL
  ,picked_up taler_amount_currency NOT NULL
  ,was_picked_up BOOLEAN NOT NULL DEFAULT FALSE
  );
CREATE INDEX IF NOT EXISTS merchant_rewards_by_pickup_and_expiration
  ON merchant_rewards
    (was_picked_up,expiration);
COMMENT ON TABLE merchant_rewards
  IS 'rewards that have been authorized';
COMMENT ON COLUMN merchant_rewards.amount
  IS 'Overall reward amount';
COMMENT ON COLUMN merchant_rewards.picked_up
  IS 'Reward amount left to be picked up';
COMMENT ON COLUMN merchant_rewards.reserve_serial
  IS 'Reserve from which this reward is funded';
COMMENT ON COLUMN merchant_rewards.expiration
  IS 'by when does the client have to pick up the reward';

CREATE TABLE IF NOT EXISTS merchant_reward_pickups
  (pickup_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY NOT NULL
  ,reward_serial BIGINT NOT NULL
      REFERENCES merchant_rewards (reward_serial) ON DELETE CASCADE
  ,pickup_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(pickup_id)=64)
  ,amount taler_amount_currency NOT NULL
  );
COMMENT ON TABLE merchant_reward_pickups
  IS 'rewards that have been picked up';
COMMENT ON COLUMN merchant_rewards.amount
  IS 'total transaction cost for all coins including withdraw fees';

CREATE TABLE IF NOT EXISTS merchant_reward_pickup_signatures
  (pickup_serial INT8 NOT NULL
     REFERENCES merchant_reward_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_reward_pickup_signatures
  IS 'blind signatures we got from the exchange during the reward pickup';




CREATE TABLE IF NOT EXISTS merchant_kyc
(kyc_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,kyc_timestamp INT8 NOT NULL
,kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)
,aml_decision INT4 NOT NULL DEFAULT (0)
,exchange_sig BYTEA CHECK(LENGTH(exchange_sig)=64)
,exchange_pub BYTEA CHECK(LENGTH(exchange_pub)=32)
,exchange_kyc_serial INT8 NOT NULL DEFAULT(0)
,account_serial INT8 NOT NULL
  REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE
,exchange_url TEXT NOT NULL
,PRIMARY KEY (account_serial,exchange_url)
);
COMMENT ON TABLE merchant_kyc
  IS 'Status of the KYC process of a merchant account at an exchange';
COMMENT ON COLUMN merchant_kyc.kyc_timestamp
  IS 'Last time we checked our KYC status at the exchange. Useful to re-check if the status is very stale. Also the timestamp used for the exchange signature (if present).';
COMMENT ON COLUMN merchant_kyc.exchange_kyc_serial
  IS 'Number to use in the KYC-endpoints of the exchange to check the KYC status or begin the KYC process. 0 if we do not know it yet.';
COMMENT ON COLUMN merchant_kyc.kyc_ok
  IS 'true if the KYC check was passed successfully';
COMMENT ON COLUMN merchant_kyc.exchange_sig
  IS 'signature of the exchange affirming the KYC passed (or NULL if exchange does not require KYC or not kyc_ok)';
COMMENT ON COLUMN merchant_kyc.exchange_pub
  IS 'public key used with exchange_sig (or NULL if exchange_sig is NULL)';
COMMENT ON COLUMN merchant_kyc.aml_decision
  IS 'current AML decision for our account at the exchange';
COMMENT ON COLUMN merchant_kyc.account_serial
  IS 'Which bank account of the merchant is the KYC status for';
COMMENT ON COLUMN merchant_kyc.exchange_url
  IS 'Which exchange base URL is this KYC status valid for';


CREATE TABLE IF NOT EXISTS merchant_otp_devices
  (otp_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,merchant_serial BIGINT NOT NULL
    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,otp_id TEXT NOT NULL
  ,otp_description TEXT NOT NULL
  ,otp_key TEXT DEFAULT NULL
  ,otp_algorithm INT NOT NULL DEFAULT (0)
  ,otp_ctr INT8 NOT NULL DEFAULT (0)
  ,UNIQUE (merchant_serial, otp_id)
  );
COMMENT ON TABLE merchant_otp_devices
  IS 'OTP device owned by a merchant';
COMMENT ON COLUMN merchant_otp_devices.otp_description
  IS 'Human-readable OTP device description';
COMMENT ON COLUMN merchant_otp_devices.otp_key
  IS 'A base64-encoded key of the point-of-sale. It will be use by the OTP device';
COMMENT ON COLUMN merchant_otp_devices.otp_algorithm
  IS 'algorithm to used to generate the confirmation code. It is linked with the otp_key and otp_ctr';
COMMENT ON COLUMN merchant_otp_devices.otp_ctr
  IS 'counter for counter-based OTP generators';


CREATE TABLE IF NOT EXISTS merchant_template
  (template_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,merchant_serial BIGINT NOT NULL
    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,template_id TEXT NOT NULL
  ,template_description TEXT NOT NULL
  ,otp_device_id BIGINT
    REFERENCES merchant_otp_devices (otp_serial) ON DELETE SET NULL
  ,template_contract TEXT NOT NULL -- in JSON format
  ,UNIQUE (merchant_serial, template_id)
  );
COMMENT ON TABLE merchant_template
  IS 'template used by the merchant (may be incomplete, frontend can override)';
COMMENT ON COLUMN merchant_template.template_description
  IS 'Human-readable template description';
COMMENT ON COLUMN merchant_template.template_contract
  IS 'The template contract will contains some additional information.';



CREATE TABLE IF NOT EXISTS merchant_webhook
  (webhook_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,merchant_serial BIGINT NOT NULL
    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,webhook_id TEXT NOT NULL
  ,event_type TEXT NOT NULL
  ,url TEXT NOT NULL
  ,http_method TEXT NOT NULL
  ,header_template TEXT
  ,body_template TEXT
  ,UNIQUE (merchant_serial, webhook_id)
  );
COMMENT ON TABLE merchant_webhook
  IS 'webhook used by the merchant (may be incomplete, frontend can override)';
COMMENT ON COLUMN merchant_webhook.event_type
  IS 'Event of the webhook';
COMMENT ON COLUMN merchant_webhook.url
  IS 'URL to make the request to';
COMMENT ON COLUMN merchant_webhook.http_method
  IS 'http method use by the merchant';
COMMENT ON COLUMN merchant_webhook.header_template
  IS 'Template for the header of the webhook, to be modified based on trigger data';
COMMENT ON COLUMN merchant_webhook.body_template
  IS 'Template for the body of the webhook, to be modified based on trigger data';


CREATE TABLE IF NOT EXISTS merchant_pending_webhooks
  (webhook_pending_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,merchant_serial BIGINT NOT NULL
    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
  ,webhook_serial BIGINT NOT NULL
    REFERENCES merchant_webhook (webhook_serial) ON DELETE CASCADE
  ,next_attempt INT8 NOT NULL DEFAULT(0)
  ,retries INT4 NOT NULL DEFAULT(0)
  ,url TEXT NOT NULL
  ,http_method TEXT NOT NULL
  ,header TEXT
  ,body TEXT
  ,UNIQUE (merchant_serial, webhook_pending_serial)
  );
COMMENT ON TABLE merchant_pending_webhooks
  IS 'webhooks that still need to be executed by the merchant';
COMMENT ON COLUMN merchant_pending_webhooks.url
  IS 'URL to make the request to';
COMMENT ON COLUMN merchant_pending_webhooks.webhook_serial
  IS 'Reference to the configured webhook template';
COMMENT ON COLUMN merchant_pending_webhooks.retries
  IS 'How often have we tried this request so far';
COMMENT ON COLUMN merchant_pending_webhooks.next_attempt
  IS 'Time when we should make the next request to the webhook';
COMMENT ON COLUMN merchant_pending_webhooks.http_method
  IS 'http method use for the webhook';
COMMENT ON COLUMN merchant_pending_webhooks.header
  IS 'Header of the webhook';
COMMENT ON COLUMN merchant_pending_webhooks.body
  IS 'Body of the webhook';


CREATE TABLE IF NOT EXISTS merchant_exchange_accounts
  (mea_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)
  ,payto_uri TEXT NOT NULL
  ,conversion_url TEXT
  ,debit_restrictions TEXT NOT NULL
  ,credit_restrictions TEXT NOT NULL
  ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
  );
COMMENT ON TABLE merchant_exchange_accounts
 IS 'Here we store which bank accounts the exchange uses and with which constraints';
COMMENT ON COLUMN merchant_exchange_accounts.master_pub
 IS 'Master public key of the exchange with these accounts';
COMMENT ON COLUMN merchant_exchange_accounts.payto_uri
 IS 'RFC 8905 URI of the exchange bank account';
COMMENT ON COLUMN merchant_exchange_accounts.conversion_url
 IS 'NULL if this account does not require currency conversion';
COMMENT ON COLUMN merchant_exchange_accounts.debit_restrictions
 IS 'JSON array with account restrictions';
COMMENT ON COLUMN merchant_exchange_accounts.credit_restrictions
 IS 'JSON array with account restrictions';


CREATE TABLE IF NOT EXISTS merchant_exchange_keys
  (mek_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
  ,exchange_url TEXT PRIMARY KEY
  ,keys_json TEXT NOT NULL
  ,expiration_time INT8 NOT NULL
  );
COMMENT ON TABLE merchant_exchange_keys
 IS 'Here we store the cached /keys response from an exchange in JSON format';
COMMENT ON COLUMN merchant_exchange_keys.exchange_url
 IS 'Base URL of the exchange with these keys';
COMMENT ON COLUMN merchant_exchange_keys.keys_json
 IS 'JSON string of the /keys as generated by libtalerexchange';
COMMENT ON COLUMN merchant_exchange_keys.expiration_time
 IS 'When should this /keys object be deleted';


-- Complete transaction
COMMIT;