salix/db/versions/11042-turquoiseAspidistra/00-firstScript.sql

ALTER TABLE bs.waste ADD buyerFk int(10) unsigned NOT NULL;
USE vn;
CREATE OR REPLACE TEMPORARY TABLE tBuyers
	ENGINE = MEMORY
	WITH tDistinctBuyers AS (
		SELECT DISTINCT buyer
			FROM bs.waste w
	)
	SELECT buyer, u.id
		FROM tDistinctBuyers tdb
			JOIN account.`user` u ON u.name = tdb.buyer COLLATE utf8mb3_unicode_ci;

UPDATE bs.waste w
		JOIN tBuyers tb ON tb.buyer = w.buyer
	SET w.buyerFk = tb.id;

DROP TEMPORARY TABLE tBuyers;

ALTER TABLE bs.waste
    DROP PRIMARY KEY,
    DROP COLUMN family,
    DROP COLUMN rate,
    DROP COLUMN buyer;

ALTER TABLE bs.waste CHANGE buyerFk buyerFk int(10) unsigned NOT NULL AFTER `week`;
ALTER TABLE bs.waste ADD CONSTRAINT waste_user_FK FOREIGN KEY (buyerFk) REFERENCES account.user(id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE bs.waste ADD saleQuantity int(11) DEFAULT NULL NULL;
ALTER TABLE bs.waste MODIFY COLUMN saleTotal decimal(10,2) DEFAULT NULL NULL;
ALTER TABLE bs.waste MODIFY COLUMN saleWaste decimal(10,2) DEFAULT NULL NULL;
ALTER TABLE bs.waste CHANGE saleWaste saleInternalWaste decimal(10,2) DEFAULT NULL NULL;
ALTER TABLE bs.waste ADD saleExternalWaste decimal(10,2) DEFAULT NULL NULL;
ALTER TABLE bs.waste CHANGE saleQuantity saleQuantity decimal(10,2) DEFAULT NULL NULL AFTER itemTypeFk;
ALTER TABLE bs.waste CHANGE itemFk itemFk int(11) DEFAULT 0 NOT NULL AFTER itemTypeFk;

CREATE OR REPLACE TEMPORARY TABLE tWasteUnique
	SELECT *
		FROM bs.waste
		GROUP BY `year`, `week`, buyerFk, itemTypeFk, itemFk
		HAVING COUNT(*) > 1;

DELETE w.* FROM bs.waste w
	JOIN tWasteUnique twu ON twu.`year` = w.`year`
		AND twu.`week` = w.`week`
		AND twu.`buyerFk` = w.`buyerFk`
		AND twu.`itemTypeFk` = w.`itemTypeFk`
		AND twu.`itemFk` = w.`itemFk`;

INSERT INTO bs.waste
	SELECT * FROM tWasteUnique;

ALTER TABLE bs.waste ADD CONSTRAINT waste_pk PRIMARY KEY (`year`, `week`, buyerFk, itemTypeFk, itemFk);

DROP TEMPORARY TABLE tWasteUnique;