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

-- Everything in one big transaction
BEGIN;

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

SET search_path TO merchant;

ALTER TABLE merchant_instances
  ADD COLUMN user_type INT;
COMMENT ON COLUMN merchant_instances.user_type
  IS 'what type of user is this (individual or business)';

-- Column makes no sense for multi-account exchanges.  Instead, we should
-- lookup the various accounts of the exchange (by the master_pub) and return
-- all of them (with constraints).
ALTER TABLE merchant_tip_reserve_keys
  DROP COLUMN payto_uri,
  ADD COLUMN master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32);

ALTER TABLE merchant_transfers
  ADD COLUMN ready_time INT8 NOT NULL DEFAULT (0),
  ADD COLUMN failed BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN validation_status INT4 DEFAULT NULL;
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';


ALTER TABLE merchant_accounts
  ADD COLUMN credit_facade_url VARCHAR,
  ADD COLUMN credit_facade_credentials VARCHAR,
  ADD COLUMN last_bank_serial INT8 NOT NULL DEFAULT (0);
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';


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 VARCHAR NOT NULL
  ,conversion_url VARCHAR
  ,debit_restrictions VARCHAR NOT NULL
  ,credit_restrictions VARCHAR 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';


-- Complete transaction
COMMIT;
