DELIMITER //
DROP PROCEDURE IF EXISTS autoCreateLots;
CREATE PROCEDURE autoCreateLots()
BEGIN
    DECLARE v_id VARCHAR(50);
	DECLARE v_ward CHAR(1);
	DECLARE v_medical INT(11);
    DECLARE v_prep_date, v_due_date DATETIME DEFAULT CURDATE();
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT MDSRWRD_WRD_ID_A, MDSRWRD_MDSR_ID
							FROM MEDICALDSRWARD;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_ward, v_medical;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET v_id = CONCAT('AUTO_',v_ward,'_',v_medical); 
        INSERT INTO MEDICALDSRLOT (LT_ID_A, LT_MDSR_ID, LT_PREP_DATE, LT_DUE_DATE) VALUES (v_id, v_medical, v_prep_date, v_due_date);
        UPDATE MEDICALDSRWARD SET MDSRWRD_LT_ID_A = v_id WHERE MDSRWRD_WRD_ID_A = v_ward AND MDSRWRD_MDSR_ID = v_medical;
        
    END LOOP;
  	CLOSE cur;
END; 

DROP PROCEDURE IF EXISTS updateWardToWardReference;
CREATE PROCEDURE updateWardToWardReference()
BEGIN
    DECLARE v_lot VARCHAR(50);
	DECLARE v_ward CHAR(1);
	DECLARE v_medical INT(11);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT MMVN_MDSR_ID, MMVN_WRD_ID_A_FROM, MMVN_LT_ID 
							FROM MEDICALDSRSTOCKMOVWARD
							WHERE MMVN_WRD_ID_A_FROM IS NOT NULL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_medical, v_ward, v_lot;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        UPDATE MEDICALDSRSTOCKMOVWARD SET MMVN_LT_ID = v_lot WHERE MMVN_MDSR_ID = v_medical AND MMVN_WRD_ID_A_TO = v_ward;
        
    END LOOP;
  	CLOSE cur;
END; //

DELIMITER ;

-- adding medical id (LT_MDSR_ID) to MEDICALDSRLOT
ALTER TABLE MEDICALDSRLOT ADD COLUMN LT_MDSR_ID INT(11) NULL AFTER LT_ID_A;

-- populating new field (LT_MDSR_ID)
UPDATE MEDICALDSRLOT 
       JOIN MEDICALDSRSTOCKMOV 
       ON MEDICALDSRLOT.LT_ID_A = MEDICALDSRSTOCKMOV.MMV_LT_ID_A
SET MEDICALDSRLOT.LT_MDSR_ID = MEDICALDSRSTOCKMOV.MMV_MDSR_ID;

-- adding lot id (MDSRWRD_LT_ID_A) to MEDICALDSRWARD
ALTER TABLE MEDICALDSRWARD ADD COLUMN MDSRWRD_LT_ID_A VARCHAR(50) NULL AFTER MDSRWRD_OUT_QTI;
ALTER TABLE MEDICALDSRSTOCKMOVWARD ADD COLUMN MMVN_LT_ID VARCHAR(50) NULL AFTER MMVN_WRD_ID_A_TO;

-- porting previous data
ALTER TABLE MEDICALDSRWARD 
DROP FOREIGN KEY FK_MEDICALDSRWARD_MEDICALDSR,
DROP FOREIGN KEY FK_MEDICALDSRWARD_WARD,
DROP PRIMARY KEY;

CALL autoCreateLots();

ALTER TABLE MEDICALDSRWARD CHANGE COLUMN MDSRWRD_LT_ID_A MDSRWRD_LT_ID_A VARCHAR(50) NOT NULL;

-- adding foreign keys and indexes
ALTER TABLE MEDICALDSRLOT CHANGE COLUMN LT_MDSR_ID LT_MDSR_ID INT(11) NOT NULL;

ALTER TABLE MEDICALDSRLOT 
ADD INDEX FK_MEDICALDSRLOT_MEDICALDSR_idx (LT_MDSR_ID ASC),
ADD CONSTRAINT FK_MEDICALDSRLOT_MEDICALDSR
  FOREIGN KEY (LT_MDSR_ID)
  REFERENCES MEDICALDSR (MDSR_ID)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE MEDICALDSRWARD
ADD INDEX FK_MEDICALDSRWARD_WARD_idx (MDSRWRD_WRD_ID_A ASC),
ADD CONSTRAINT FK_MEDICALDSRWARD_WARD
  FOREIGN KEY (MDSRWRD_WRD_ID_A)
  REFERENCES WARD (WRD_ID_A)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD INDEX FK_MEDICALDSRWARD_MEDICALDSR_idx (MDSRWRD_MDSR_ID ASC),
ADD CONSTRAINT FK_MEDICALDSRWARD_MEDICALDSR
  FOREIGN KEY (MDSRWRD_MDSR_ID)
  REFERENCES MEDICALDSR (MDSR_ID)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD INDEX FK_MEDICALDSRWARD_MEDICALDSRLOT_idx (MDSRWRD_LT_ID_A ASC),
ADD CONSTRAINT FK_MEDICALDSRWARD_MEDICALDSRLOT
  FOREIGN KEY (MDSRWRD_LT_ID_A)
  REFERENCES MEDICALDSRLOT (LT_ID_A)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE MEDICALDSRSTOCKMOVWARD 
DROP INDEX FK_MEDICALDSRSTOCKMOVWARD_WARD,
ADD INDEX FK_MEDICALDSRSTOCKMOVWARD_WARD_idx (MMVN_WRD_ID_A ASC),
DROP INDEX FK_MEDICALDSRSTOCKMOVWARD_PATIENT,
ADD INDEX FK_MEDICALDSRSTOCKMOVWARD_PATIENT_idx (MMVN_PAT_ID ASC),
ADD INDEX FK_MEDICALDSRSTOCKMOVWARD_LOT_idx (MMVN_LT_ID ASC),
ADD CONSTRAINT FK_MEDICALDSRSTOCKMOVWARD_LOT
  FOREIGN KEY (MMVN_LT_ID)
  REFERENCES MEDICALDSRLOT (LT_ID_A)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

-- changing compound primary key
ALTER TABLE MEDICALDSRWARD ADD PRIMARY KEY (MDSRWRD_WRD_ID_A, MDSRWRD_MDSR_ID, MDSRWRD_LT_ID_A);

CALL updateWardToWardReference();