START TRANSACTION;

CREATE TABLE `dir_item_billing` (
  `itb_id` INT NOT NULL AUTO_INCREMENT,
  `itb_token` VARCHAR(40) NULL,
  `itb_phone` VARCHAR(20) NULL,
  `itb_ischecked` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`itb_id`));

delete from dir_prices 	WHERE prc_id in (
SELECT * FROM (SELECT prc_id FROM
dir_prices p2
	JOIN (SELECT min(prc_id), max(prc_id) m, prc_good g, prc_type t, count(*) FROM dir_prices p1 group by p1.prc_good, p1.prc_type HAVING count(*) > 1) tmp1 ON p2.prc_good = g AND p2.prc_type = t
WHERE p2.prc_id <> m) tmp2)


ALTER TABLE `dir_prices` 
ADD UNIQUE INDEX `prc_good_type_idx` (`prc_good` ASC, `prc_type` ASC);

ALTER TABLE `dir_prices` 
ADD COLUMN `prc_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP() AFTER `prc_deleted`;

ALTER TABLE `dir_prices` 
ADD INDEX `prc_last_update_idx` (`prc_last_update` ASC);



ALTER TABLE `dir_prices_types` 
ADD COLUMN `prct_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `prct_max_discount`;

ALTER TABLE `dir_prices_types` 
ADD INDEX `prct_last_update` (`prct_last_update` ASC);


ALTER TABLE `dir_goods` 
ADD COLUMN `gd_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `gd_vat`,
ADD INDEX `gd_last_update_idx` (`gd_last_update` ASC);

ALTER TABLE `dir_scans` 
ADD COLUMN `scn_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `scn_value`,
ADD INDEX `scn_last_update_idx` (`scn_last_update` ASC);

ALTER TABLE `doc_sales` 
ADD COLUMN `sls_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `sls_deleted_mark`,
ADD INDEX `sls_last_update_idx` (`sls_last_update` ASC);

ALTER TABLE `doc_payments` 
ADD COLUMN `pmt_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `pmt_note`,
ADD INDEX `pmt_last_update_idx` (`pmt_last_update` ASC);

ALTER TABLE `operations` 
ADD COLUMN `opr_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `opr_positive`,
ADD INDEX `opr_last_update_idx` (`opr_last_update` ASC);


ALTER TABLE `operations_additional_prop` 
ADD COLUMN `oap_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `oap_price2`,
ADD INDEX `oap_last_update_idx` (`oap_last_update` ASC);



DROP TRIGGER IF EXISTS `operations_additional_prop_AFTER_UPDATE`;

DELIMITER $$

CREATE %%%DEFINER%%% TRIGGER `operations_additional_prop_AFTER_UPDATE` AFTER UPDATE ON `operations_additional_prop` FOR EACH ROW
BEGIN
	update operations SET opr_last_update = new.oap_last_update WHERE opr_id = new.oap_operation;
END$$
DELIMITER ;

DROP TRIGGER IF EXISTS `operations_AFTER_UPDATE`;

DELIMITER $$

CREATE %%%DEFINER%%% TRIGGER `operations_AFTER_UPDATE` AFTER UPDATE ON `operations` FOR EACH ROW
BEGIN
	if new.opr_last_update <> old.opr_last_update then
		begin
			if new.opr_type in (2,6) then
				begin
					update doc_sales set sls_last_update = new.opr_last_update WHERE sls_id = new.opr_document;
                end;
			end if;
        end;
	end if;
END$$
DELIMITER ;


CREATE TABLE `sys_web_sync` (
  `ws_table_name` VARCHAR(50) NOT NULL,
  `ws_last_update` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`ws_table_name`));

INSERT INTO `dir_extensions` (`ext_id`, `ext_name`, `ext_enable`) VALUES ('10003', 'EasyTrade Web', '0');

ALTER TABLE `dir_customers` 
ADD COLUMN `cstm_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `cstm_hash`,
ADD INDEX `cstm_last_update_idx` (`cstm_last_update` ASC);

ALTER TABLE dir_groups
ADD COLUMN grp_last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER grp_depth,
ADD INDEX grp_last_update_idx (grp_last_update ASC);

ALTER TABLE dir_objects
ADD COLUMN obj_last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ADD INDEX obj_last_update_idx (obj_last_update ASC);


CREATE TABLE `dir_good_remainders` (
  `objbl_good` INT NOT NULL COMMENT 'номенклатура',
  `objbl_object` INT NOT NULL COMMENT 'место хранения',
  `objbl_quantity` DECIMAL (15, 4) NOT NULL DEFAULT 0 COMMENT 'текущее количество',
  `objbl_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'время последнего изменения',
  PRIMARY KEY (`objbl_good`, `objbl_object`),
  INDEX `objbl_update_idx` (`objbl_last_update` ASC),
  FOREIGN KEY `objbl_good_fk` (`objbl_good`) REFERENCES `dir_goods` (`gd_id`) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY `objbl_object_fk` (`objbl_object`) REFERENCES `dir_objects` (`obj_id`) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = 'таблица хранящая текущий остатки в разрезе номенклаутры и мест хранения';

DELIMITER $$
DROP FUNCTION IF EXISTS `fn_calc_objects_balance`$$
CREATE %%%DEFINER%%% FUNCTION `fn_calc_objects_balance`() RETURNS INT(11)
NO SQL
BEGIN
 
 DECLARE _good INT; -- номенклатура
 DECLARE _object INT; -- место хранения
 DECLARE _amount DOUBLE; -- количество
 DECLARE _time TIMESTAMP; -- время выполнения  
 DECLARE boni INT;
 
 DECLARE curr CURSOR FOR SELECT opr_good, opr_object, IFNULL(SUM(CASE WHEN opr_positive = 1 THEN opr_quantity ELSE -opr_quantity END),0) AS amount
 FROM operations WHERE opr_performed = 1 GROUP BY opr_object, opr_good;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET boni = 1; 
 
 SET _time = CURRENT_TIMESTAMP;

 DELETE FROM dir_good_remainders;
 
 -- открываю курсор
 OPEN curr;
      SET boni = 0;
      bon_loop: LOOP 
	FETCH curr INTO _good, _object, _amount; 
	IF boni = 1 THEN
         /* курсор закончился - выходим */	
	    CLOSE curr;
	    SET boni = 0;
            LEAVE bon_loop;
	END IF;
	/* вставляю данные в таблицу юаланса */
	INSERT INTO dir_good_remainders VALUES (_good, _object, _amount, _time) ON DUPLICATE KEY UPDATE objbl_quantity = objbl_quantity + _amount, objbl_last_update = _time; 
	END LOOP bon_loop;  /* конец цикла по курсору */
 RETURN 0;
    END$$

DELIMITER ;




DELIMITER $$

DROP TRIGGER IF EXISTS `operations_AFTER_UPDATE`$$
CREATE
    TRIGGER `operations_AFTER_UPDATE` AFTER UPDATE ON `operations` 
    FOR EACH ROW 
BEGIN
	if new.opr_last_update <> old.opr_last_update then
		begin
			if new.opr_type in (2,6) then
				begin
					update doc_sales set sls_last_update = new.opr_last_update WHERE sls_id = new.opr_document;
				end;
			end if;
		end;
	end if;
	
	if (NEW.opr_performed = 1 ) AND (OLD.opr_performed = 0) THEN
         -- операция перешла в статус проведена
         IF NEW.opr_positive = 1 THEN
          -- приход - добавляю количество
          INSERT INTO dir_good_remainders (objbl_good, objbl_object, objbl_quantity) 
          VALUES (NEW.opr_good, NEW.opr_object, NEW.opr_quantity) ON DUPLICATE KEY UPDATE objbl_quantity = objbl_quantity + NEW.opr_quantity;	
         ELSE
          -- расход - убираю количество
          INSERT INTO dir_good_remainders (objbl_good, objbl_object, objbl_quantity) 
          VALUES (NEW.opr_good, NEW.opr_object, -NEW.opr_quantity) ON DUPLICATE KEY UPDATE objbl_quantity = objbl_quantity - NEW.opr_quantity;	
         END if;
        END IF;
	
	IF (NEW.opr_performed = 0 ) AND (OLD.opr_performed = 1) THEN
         -- операция перешла в статус не проведена
         IF NEW.opr_positive = 1 THEN
          -- был приход - нужно убрать количество
          INSERT INTO dir_good_remainders (objbl_good, objbl_object, objbl_quantity) 
          VALUES (NEW.opr_good, NEW.opr_object, -NEW.opr_quantity) ON DUPLICATE KEY UPDATE objbl_quantity = objbl_quantity - NEW.opr_quantity;	
         ELSE
          -- был расход - нужно добавить количество
          INSERT INTO dir_good_remainders (objbl_good, objbl_object, objbl_quantity) 
          VALUES (NEW.opr_good, NEW.opr_object, NEW.opr_quantity) ON DUPLICATE KEY UPDATE objbl_quantity = objbl_quantity + NEW.opr_quantity;	
         END IF;
        END IF;
END;
$$

DELIMITER ;


DELIMITER $$

DROP TRIGGER IF EXISTS `operations_AFTER_DELETE`$$
CREATE %%%DEFINER%%% 
    TRIGGER `operations_AFTER_DELETE` AFTER DELETE ON `operations` 
    FOR EACH ROW 
BEGIN
	IF (OLD.opr_performed = 1 ) THEN
         -- операция была проведена
         IF OLD.opr_positive = 1 THEN
          -- был приход - нужно убрать количество
          INSERT INTO dir_good_remainders (objbl_good, objbl_object, objbl_quantity) 
          VALUES (OLD.opr_good, OLD.opr_object, -OLD.opr_quantity) ON DUPLICATE KEY UPDATE objbl_quantity = objbl_quantity - OLD.opr_quantity;	
         ELSE
          -- был расход - нужно добавить количество
          INSERT INTO dir_good_remainders (objbl_good, objbl_object, objbl_quantity) 
          VALUES (OLD.opr_good, OLD.opr_object, OLD.opr_quantity) ON DUPLICATE KEY UPDATE objbl_quantity = objbl_quantity + OLD.opr_quantity;	
         END IF;
        END IF;
END;
$$

DELIMITER ;


DELIMITER $$
DROP TRIGGER IF EXISTS `operations_AFTER_INSERT`$$

CREATE %%%DEFINER%%% 
    TRIGGER `operations_AFTER_INSERT` AFTER INSERT ON `operations` 
    FOR EACH ROW 
BEGIN
	if (NEW.opr_performed = 1 ) THEN
         -- операция была проведена
         IF NEW.opr_positive = 1 THEN
          -- был приход - нужно добавить
          INSERT INTO dir_good_remainders (objbl_good, objbl_object, objbl_quantity) 
          VALUES (NEW.opr_good, NEW.opr_object, NEW.opr_quantity) ON DUPLICATE KEY UPDATE objbl_quantity = objbl_quantity + NEW.opr_quantity;	
         ELSE
          -- был расход - нужно убавить
          INSERT INTO dir_good_remainders (objbl_good, objbl_object, objbl_quantity) 
          VALUES (NEW.opr_good, NEW.opr_object, NEW.opr_quantity) ON DUPLICATE KEY UPDATE objbl_quantity = objbl_quantity - NEW.opr_quantity;	
         END IF;
        END IF;
END;
$$

DELIMITER ;




select fn_calc_objects_balance();

INSERT IGNORE INTO `dir_extensions_settings` (`extss_id`, `extss_extension`, `extss_value`) VALUES ('10004', '10003', ' ');

ALTER TABLE `dir_avg_cost`
  ADD COLUMN `avgc_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `avgc_value_cur`;
  
ALTER TABLE dir_extensions_settings CHANGE extss_value extss_value VARCHAR (255) CHARSET utf8 COLLATE utf8_general_ci DEFAULT '0' NOT NULL;
ALTER TABLE dir_extensions_settings DROP PRIMARY KEY, ADD PRIMARY KEY (extss_id, extss_extension);
INSERT IGNORE INTO dir_extensions_settings VALUES (10005, 10003, '30');
INSERT IGNORE INTO dir_extensions_settings VALUES (10006, 10003, 'https://api.regos.uz/v1/WebEasyTrade/');
INSERT IGNORE INTO dir_extensions_settings VALUES (10007, 10003, '15');


UPDATE sys_info SET si_version = "1.556" WHERE sys_info.si_id = 1;
COMMIT; 

