CREATE OR REPLACE FUNCTION change_payment_status() RETURNS trigger AS '
BEGIN
 IF ((OLD.payment_status is NULL) OR (NOT OLD.payment_status)) AND NEW.payment_status AND (OLD.pay_method_id = 3 OR OLD.pay_method_id =4 OR OLD.pay_method_id = 6) AND OLD.bonus_date IS NULL THEN
 	NEW.bonus_date = now();
 END IF;
 
 RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER change_payment_status
BEFORE UPDATE
ON order_header FOR EACH ROW
EXECUTE PROCEDURE change_payment_status();

CREATE OR REPLACE FUNCTION set_article_by_supplier() RETURNS trigger AS '
BEGIN
	update article set inventory = inventory + NEW.quantity WHERE id = NEW.article_id;
	RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER supplier_sets_article
BEFORE INSERT
ON supplier_order FOR EACH ROW
EXECUTE PROCEDURE set_article_by_supplier();

CREATE OR REPLACE FUNCTION set_article_inventory() RETURNS trigger AS '
DECLARE
	delta_quantity int;
	detail_rec RECORD;
BEGIN
	delta_quantity := 0;

	IF TG_OP = ''DELETE'' THEN
		delta_quantity = OLD.quantity;
		delta_quantity = 0 - delta_quantity;
		UPDATE article SET inventory = inventory - delta_quantity WHERE id = OLD.art_id;
	END IF;

	IF TG_OP = ''INSERT'' THEN
		delta_quantity = NEW.quantity;
		UPDATE article SET inventory = inventory - delta_quantity WHERE id = NEW.art_id;
	END IF;
	
	
	RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER article_inventory
AFTER INSERT OR DELETE
ON order_detail FOR EACH ROW
EXECUTE PROCEDURE set_article_inventory();

CREATE OR REPLACE FUNCTION set_article_inventory_from_status() RETURNS trigger AS '
DECLARE
	delta_quantity int;
	detail_rec RECORD;
BEGIN
	delta_quantity := 0;
	
	IF NEW.order_status_id = 90 THEN
		FOR detail_rec IN select art_id, quantity from order_detail WHERE order_id = NEW.id LOOP
			delta_quantity = detail_rec.quantity;
			delta_quantity = 0 - delta_quantity;
			UPDATE article SET inventory = inventory - delta_quantity WHERE id = detail_rec.art_id;
		END LOOP;
	END IF;	
	
	RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER article_inventory_status
AFTER UPDATE
ON order_header FOR EACH ROW
EXECUTE PROCEDURE set_article_inventory_from_status();


CREATE OR REPLACE FUNCTION change_order_status() RETURNS trigger AS '
DECLARE
	new_bill_id int;
BEGIN
	IF (OLD.order_status_id=40 AND NEW.order_status_id=50 AND NEW.bill_id is NULL) THEN
		-- when provisionsminderung, then no bill will be created
		IF NOT NEW.pay_method_id=5 THEN
			select into NEW.bill_id nextval(\'seq_bill_id\');
			NEW.billing_date:=now();
		END IF;
	ELSEIF (OLD.order_status_id=10) AND  (NEW.order_status_id>10) AND NEW.order_status_id<90 THEN
		IF NEW.pay_method_id= 4 THEN
			NEW.order_status_id:=30;
		ELSEIF NEW.pay_method_id= 3 THEN
			NEW.order_status_id:=40;
		ELSEIF NEW.pay_method_id=5 THEN
			NEW.order_status_id:=50;
		ELSEIF NEW.pay_method_id=6 THEN
			NEW.order_status_id:=40;
		END IF;
	ELSEIF OLD.order_status_id=30 AND NEW.order_status_id=40 AND NOT OLD.payment_status THEN
		NEW.payment_status:=true;
		IF (OLD.pay_method_id = 3 OR OLD.pay_method_id =4 OR OLD.pay_method_id = 6) AND OLD.bonus_date is NULL THEN
		 NEW.bonus_date:= now();
		END IF;
		
	END IF;
	
	RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER change_order_status
BEFORE UPDATE
ON order_header FOR EACH ROW
EXECUTE PROCEDURE change_order_status();

CREATE OR REPLACE FUNCTION set_bonus_date_at_payment_slip() RETURNS TRIGGER AS '
BEGIN
	IF NEW.PAY_METHOD_ID=6 THEN
		NEW.bonus_date:=now();
	END IF;
	
	RETURN NEW;
END;
' LANGUAGE plpgsql;

-- CREATE TRIGGER set_bonus_date
-- BEFORE INSERT
-- ON order_header FOR EACH ROW
-- EXECUTE PROCEDURE set_bonus_date_at_payment_slip();


CREATE OR REPLACE FUNCTION change_activation() RETURNS trigger AS '

BEGIN
 IF TG_OP=''UPDATE'' THEN
  IF NOT OLD.deleted AND NEW.deleted OR OLD.active AND NOT NEW.active THEN
    NEW.active:=false;
    NEW.deactivation_date:=now();
  ELSEIF NOT OLD.active AND NEW.active THEN
    NEW.deactivation_date:=NULL;
  END IF;
 ELSEIF NEW.deleted OR NOT NEW.active THEN
     NEW.active:=false;
     NEW.deactivation_date:=now();
  END IF;
 
  return NEW;
END;
' LANGUAGE  plpgsql;

CREATE TRIGGER change_activation
BEFORE INSERT OR UPDATE
ON distributor FOR EACH ROW
EXECUTE PROCEDURE change_activation();

CREATE OR REPLACE FUNCTION change_payment_method() RETURNS trigger AS '
BEGIN
 IF OLD.pay_method_id <> NEW.pay_method_id AND NEW.pay_method_id in (3,4,5) AND NEW.order_status_id=20 THEN
 	NEW.order_status_id:=10;
 END IF;
 
 return NEW;
END;
' LANGUAGE plpgsql;
 
CREATE TRIGGER change_payment_method
BEFORE UPDATE
ON order_header FOR EACH ROW
EXECUTE PROCEDURE change_payment_method();

CREATE OR REPLACE FUNCTION save_distributor_changes() RETURNS trigger AS $save_changes$
BEGIN
 IF (lower(TG_RELNAME) = 'distributor') THEN
    IF OLD.level_id <> NEW.level_id THEN
    	insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'level_id', OLD.level_id, NEW.level_id);  
	END IF;
    IF (OLD.vat_id <> NEW.vat_id OR (OLD.vat_id IS NULL AND NEW.vat_id is NOT NULL) OR (OLD.vat_id IS NOT NULL AND NEW.vat_id IS NULL)) THEN
        	insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'vat_id', OLD.vat_id, NEW.vat_id);  
    END IF;
    IF OLD.partner_type_id <> NEW.partner_type_id THEN
    		insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'partner_type_id', OLD.partner_type_id, NEW.partner_type_id);  
    END IF;
 	
 	IF OLD.sponsor <> NEW.sponsor THEN
 		insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'sponsor', OLD.sponsor, NEW.sponsor);  	
 	END IF;
 	
 ELSEIF (lower(TG_RELNAME) = 'bank_account') THEN
   IF (OLD.bank_code <> NEW.bank_code OR (OLD.bank_code IS NULL AND NEW.bank_code is NOT NULL) OR (OLD.bank_code IS NOT NULL AND NEW.bank_code IS NULL)) THEN
       insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.distr_id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'bank_code', OLD.bank_code, NEW.bank_code);  
   END IF;
   
   IF (OLD.iban <> NEW.iban OR (OLD.iban IS NULL AND NEW.iban is NOT NULL) OR (OLD.iban IS NOT NULL AND NEW.iban IS NULL)) THEN
      insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.distr_id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'iban', OLD.iban, NEW.iban);  
   END IF;
   
   IF (OLD.account_number <> NEW.account_number OR (OLD.account_number IS NULL AND NEW.account_number is NOT NULL) OR (OLD.account_number IS NOT NULL AND NEW.account_number IS NULL)) THEN
   	  insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.distr_id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'account_number', OLD.account_number, NEW.account_number);  
   END IF;
   
   IF (OLD.bank <> NEW.bank OR (OLD.bank IS NULL AND NEW.bank is NOT NULL) OR (OLD.bank IS NOT NULL AND NEW.bank IS NULL)) THEN
   	    insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.distr_id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'bank', OLD.bank, NEW.bank);  
   END IF;
   
   IF (OLD.bic <> NEW.bic OR (OLD.bic IS NULL AND NEW.bic is NOT NULL) OR (OLD.bic IS NOT NULL AND NEW.bic IS NULL)) THEN
	   	insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.distr_id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'bic', OLD.bic, NEW.bic);  
   END IF;
 
 
 ELSEIF (lower(TG_RELNAME) = 'creditcard') THEN
  	IF (OLD.cc_org_id <> NEW.cc_org_id OR (OLD.cc_org_id IS NULL AND NEW.cc_org_id is NOT NULL) OR (OLD.cc_org_id IS NOT NULL AND NEW.cc_org_id IS NULL)) THEN
     	insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.distr_id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'cc_org_id', OLD.cc_org_id, NEW.cc_org_id);  
    END IF;
    
    IF (OLD.cc_number <> NEW.cc_number OR (OLD.cc_number IS NULL AND NEW.cc_number is NOT NULL) OR (OLD.cc_number IS NOT NULL AND NEW.cc_number IS NULL)) THEN
     	insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.distr_id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'cc_number', OLD.cc_number, NEW.cc_number);  
    END IF;
    
     IF (OLD.valid_to <> NEW.valid_to OR (OLD.valid_to IS NULL AND NEW.valid_to is NOT NULL) OR (OLD.valid_to IS NOT NULL AND NEW.valid_to IS NULL)) THEN
     	insert into hist_distributor(distr_id, modified_by_id, modification_time, modification_ip, table_name, field, old_value, new_value) values (OLD.distr_id, NEW.modified_by_id, now(), NEW.modification_ip, TG_RELNAME, 'valid_to', OLD.valid_to, NEW.valid_to);  
    END IF;
     
  END IF;
  
  return NEW;
 END;
 $save_changes$ LANGUAGE plpgsql;


CREATE TRIGGER save_distributor_changes
AFTER UPDATE
ON distributor FOR EACH ROW
EXECUTE PROCEDURE save_distributor_changes();
 	
CREATE TRIGGER save_bank_account_changes
AFTER UPDATE
ON bank_account FOR EACH ROW
EXECUTE PROCEDURE save_distributor_changes();
	
CREATE TRIGGER save_creditcard_changes
AFTER UPDATE
ON creditcard FOR EACH ROW
EXECUTE PROCEDURE save_distributor_changes();
			