merchant-0028.sql (9587B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 16 -- @file merchant-0028.sql 17 -- @brief Add tables for notifications, product groups and money pots 18 -- @author Christian Grothoff 19 20 BEGIN; 21 22 -- Check patch versioning is in place. 23 SELECT _v.register_patch('merchant-0028', NULL, NULL); 24 25 SET search_path TO merchant; 26 27 CREATE TABLE merchant_reports 28 (report_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 29 ,merchant_serial BIGINT NOT NULL 30 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 31 ,report_program_section TEXT NOT NULL 32 ,report_description TEXT NOT NULL 33 ,mime_type TEXT NOT NULL 34 ,report_token BYTEA NOT NULL CHECK (LENGTH(report_token)=32) 35 ,data_source TEXT NOT NULL 36 ,target_address TEXT NOT NULL 37 ,frequency INT8 NOT NULL 38 ,frequency_shift INT8 NOT NULL 39 ,next_transmission INT8 NOT NULL 40 ,last_error_code INT4 DEFAULT NULL 41 ,last_error_detail TEXT DEFAULT NULL 42 ); 43 COMMENT ON TABLE merchant_reports 44 IS 'Specifies where we should send periodic reports about instance activities'; 45 COMMENT ON COLUMN merchant_reports.report_serial 46 IS 'Unique identifier for the report'; 47 COMMENT ON COLUMN merchant_reports.merchant_serial 48 IS 'Which instance the report is about'; 49 COMMENT ON COLUMN merchant_reports.report_program_section 50 IS 'Which helper program (configuration section) to use to transmit the report'; 51 COMMENT ON COLUMN merchant_reports.mime_type 52 IS 'Mime-type to request from the backend for the transmission'; 53 COMMENT ON COLUMN merchant_reports.report_token 54 IS 'Token clients requesting the report must include in the /report request'; 55 COMMENT ON COLUMN merchant_reports.data_source 56 IS 'Relative URL of the instance for a GET request to request data to send'; 57 COMMENT ON COLUMN merchant_reports.target_address 58 IS 'Address to which the report should be sent'; 59 COMMENT ON COLUMN merchant_reports.frequency 60 IS 'Relative time with the desired report frequency'; 61 COMMENT ON COLUMN merchant_reports.frequency_shift 62 IS 'Relative time by which to offset the actual transmission from the frequency multiple'; 63 COMMENT ON COLUMN merchant_reports.next_transmission 64 IS 'Absolute time at which we should do the next transmission'; 65 COMMENT ON COLUMN merchant_reports.last_error_code 66 IS 'ErrorCode of the last attempted transmission, NULL on success'; 67 COMMENT ON COLUMN merchant_reports.last_error_detail 68 IS 'Additional human-readable text explaining errors from the last transmission attempt (for diagnostics), NULL on success'; 69 70 71 CREATE TABLE merchant_product_groups 72 (product_group_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 73 ,merchant_serial BIGINT NOT NULL 74 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 75 ,product_group_name TEXT NOT NULL 76 ,product_group_description TEXT NOT NULL 77 ,UNIQUE (merchant_serial,product_group_name) 78 ); 79 COMMENT ON TABLE merchant_product_groups 80 IS 'Specifies a product group'; 81 COMMENT ON COLUMN merchant_product_groups.product_group_serial 82 IS 'Unique identifier for the group'; 83 COMMENT ON COLUMN merchant_product_groups.merchant_serial 84 IS 'Merchant instance using the group'; 85 COMMENT ON COLUMN merchant_product_groups.product_group_name 86 IS 'Name for the group'; 87 COMMENT ON COLUMN merchant_product_groups.product_group_description 88 IS 'Human-readable description for the group'; 89 90 91 CREATE TABLE merchant_money_pots 92 (money_pot_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 93 ,merchant_serial BIGINT NOT NULL 94 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 95 ,money_pot_name TEXT NOT NULL 96 ,money_pot_description TEXT NOT NULL 97 ,pot_totals taler_amount_currency[] NOT NULL 98 DEFAULT ARRAY[]::taler_amount_currency[] 99 ,UNIQUE (merchant_serial,money_pot_name) 100 ); 101 COMMENT ON TABLE merchant_money_pots 102 IS 'Accounting construct for tracking finances by groups such as net income, taxes, tips to be paid to staff, etc.'; 103 COMMENT ON COLUMN merchant_money_pots.money_pot_serial 104 IS 'Unique identifier for the money pot'; 105 COMMENT ON COLUMN merchant_money_pots.merchant_serial 106 IS 'Merchant instance using the group'; 107 COMMENT ON COLUMN merchant_money_pots.money_pot_name 108 IS 'Name for the money pot'; 109 COMMENT ON COLUMN merchant_money_pots.money_pot_description 110 IS 'Human-readable description for the money pot'; 111 COMMENT ON COLUMN merchant_money_pots.pot_totals 112 IS 'Total amounts in the pot'; 113 114 115 ALTER TABLE merchant_inventory 116 ADD COLUMN product_group_serial INT8 DEFAULT (NULL) 117 REFERENCES merchant_product_groups (product_group_serial) 118 ON DELETE SET NULL, 119 ADD COLUMN money_pot_serial INT8 DEFAULT (NULL) 120 REFERENCES merchant_money_pots (money_pot_serial) 121 ON DELETE SET NULL, 122 DROP COLUMN price, -- forgotten to drop in v27 123 ADD COLUMN price_is_net BOOL DEFAULT (FALSE); 124 125 COMMENT ON COLUMN merchant_inventory.product_group_serial 126 IS 'Specifies the product group a given product is a member of. If NULL, the product is in the __default__ group'; 127 COMMENT ON COLUMN merchant_inventory.money_pot_serial 128 IS 'Specifies that sales amounts of this product (excluding taxes on the product that have their own pot specified) should by default be added to the given money pot. If NULL, the money pot rules of the overall order apply instead.'; 129 COMMENT ON COLUMN merchant_inventory.price_is_net 130 IS 'If true, the price given is the net price; if false, it is the gross price.'; 131 132 133 CREATE FUNCTION merchant_deposits_insert_statistics_trigger() 134 RETURNS trigger 135 LANGUAGE plpgsql 136 AS $$ 137 DECLARE 138 my_instance INT8; 139 BEGIN 140 SELECT mct.merchant_serial 141 INTO my_instance 142 FROM merchant_contract_terms mct 143 JOIN merchant_deposit_confirmations mdc 144 USING (order_serial) 145 WHERE mdc.deposit_confirmation_serial = NEW.deposit_confirmation_serial; 146 CALL merchant_do_bump_amount_stat 147 ('deposits-received' 148 ,my_instance 149 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 150 ,NEW.amount_with_fee); 151 CALL merchant_do_bump_amount_stat 152 ('deposits-fees-paid' 153 ,my_instance 154 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 155 ,NEW.deposit_fee); 156 RETURN NEW; 157 END $$; 158 159 160 -- Whenever a contract is updated, call our trigger to bump statistics 161 CREATE TRIGGER merchant_deposits_on_insert_statistic 162 AFTER INSERT 163 ON merchant_deposits 164 FOR EACH ROW EXECUTE FUNCTION merchant_deposits_insert_statistics_trigger(); 165 166 167 CREATE FUNCTION merchant_expected_transfers_insert_statistics_trigger() 168 RETURNS trigger 169 LANGUAGE plpgsql 170 AS $$ 171 DECLARE 172 my_instance INT8; 173 BEGIN 174 IF NEW.wire_fee IS NOT NULL 175 THEN 176 SELECT ma.merchant_serial 177 INTO my_instance 178 FROM merchant_expected_transfers met 179 JOIN merchant_accounts ma 180 USING (account_serial) 181 WHERE met.expected_credit_serial = NEW.expected_credit_serial; 182 CALL merchant_do_bump_amount_stat 183 ('wire-fees-paid' 184 ,my_instance 185 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 186 ,NEW.wire_fee); 187 END IF; 188 RETURN NEW; 189 END $$; 190 191 192 CREATE FUNCTION merchant_expected_transfers_update_statistics_trigger() 193 RETURNS trigger 194 LANGUAGE plpgsql 195 AS $$ 196 DECLARE 197 my_instance INT8; 198 BEGIN 199 IF NEW.wire_fee IS NOT NULL AND OLD.wire_fee IS NULL 200 THEN 201 SELECT ma.merchant_serial 202 INTO my_instance 203 FROM merchant_expected_transfers met 204 JOIN merchant_accounts ma 205 USING (account_serial) 206 WHERE met.expected_credit_serial = NEW.expected_credit_serial; 207 CALL merchant_do_bump_amount_stat 208 ('wire-fees-paid' 209 ,my_instance 210 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 211 ,NEW.wire_fee); 212 END IF; 213 RETURN NEW; 214 END $$; 215 216 217 -- Whenever a contract is updated, call our trigger to bump statistics 218 CREATE TRIGGER merchant_expected_transfers_on_insert_statistic 219 AFTER INSERT 220 ON merchant_expected_transfers 221 FOR EACH ROW EXECUTE FUNCTION merchant_expected_transfers_insert_statistics_trigger(); 222 CREATE TRIGGER merchant_expected_transfers_on_update_statistic 223 AFTER INSERT 224 ON merchant_expected_transfers 225 FOR EACH ROW EXECUTE FUNCTION merchant_expected_transfers_update_statistics_trigger(); 226 227 228 229 -- Enable additional bucket statistics 230 INSERT INTO merchant_statistic_bucket_meta 231 (slug 232 ,description 233 ,stype 234 ,ranges 235 ,ages) 236 VALUES 237 ('deposits-received' 238 ,'total amount customers deposited to us (including deposit fees)' 239 ,'amount' 240 ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 241 ,ARRAY[72, 14, 12, 24, 12, 10] 242 ), 243 ('deposits-fees-paid' 244 ,'total amount in deposit fees paid by us or our customers' 245 ,'amount' 246 ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 247 ,ARRAY[72, 14, 12, 24, 12, 10] 248 ), 249 ('wire-fees-paid' 250 ,'amount in wire fees paid by' 251 ,'amount' 252 ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 253 ,ARRAY[72, 14, 12, 24, 12, 10] 254 ), 255 ('orders-paid' 256 ,'number of orders paid (but not necessarily settled by the exchange)' 257 ,'number' 258 ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 259 ,ARRAY[72, 14, 12, 24, 12, 10] 260 ); 261 262 263 264 COMMIT;