USE vn;

ALTER TABLE `postgresql`.`business` 
ADD COLUMN `workerFk` INT(10) UNSIGNED NOT NULL AFTER `occupationCodeFk`;

UPDATE postgresql.business b
        JOIN postgresql.profile p ON p.profile_id = b.client_id
    SET  b.workerFk = p.workerFk
    WHERE  p.workerFk IS NOT NULL;

-- 5 líneas afectadas contratos 2014 (info en A3)
DELETE FROM postgresql.business 
    WHERE NOT workerFk;

ALTER TABLE `postgresql`.`business` 
    ADD INDEX `business_workerFk_idx` (`workerFk` ASC) ;

ALTER TABLE `postgresql`.`business` 
    ADD CONSTRAINT `business_workerFk`
    FOREIGN KEY (`workerFk`)
    REFERENCES `vn`.`worker` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

CREATE TABLE `business` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `companyCodeFk` char(3) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `started` date DEFAULT NULL,
  `ended` date DEFAULT NULL,
  `workerBusiness` longtext DEFAULT NULL,
  `reasonEndFk` int(11) DEFAULT NULL,
  `payedHolidays` decimal(5,2) NOT NULL DEFAULT 0.00,
  `occupationCodeFk` varchar(1) DEFAULT NULL,
  `workerFk` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `business_occupationCodeFk` (`occupationCodeFk`),
  KEY `business_companyCodeFk` (`companyCodeFk`),
  KEY `business_workerFk_idx` (`workerFk`),
  CONSTRAINT `business_companyCodeFk` FOREIGN KEY (`companyCodeFk`) REFERENCES `vn`.`company` (`code`) ON UPDATE CASCADE,
  CONSTRAINT `business_occupationCodeFk` FOREIGN KEY (`occupationCodeFk`) REFERENCES `vn`.`occupationCode` (`code`) ON UPDATE CASCADE,
  CONSTRAINT `business_workerFk` FOREIGN KEY (`workerFk`) REFERENCES `vn`.`worker` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO vn.business (id, 
        companyCodeFk, 
        started, 
        ended, 
        workerBusiness, 
        reasonEndFk, 
        payedHolidays, 
        occupationCodeFk, 
        workerFk )
    SELECT business_id, 
            companyCodeFk, 
            date_start, 
            date_end, 
            workerBusiness, 
            reasonEndFk, 
            IFNULL(payedHolidays, 0), 
            occupationCodeFk, 
            workerFk 
		FROM postgresql.business;

ALTER TABLE `vn`.`worker` 
	DROP FOREIGN KEY `worker_business`;

ALTER TABLE `vn`.`worker` 
ADD CONSTRAINT `worker_businessFk`
  FOREIGN KEY (`businessFk`)
  REFERENCES `vn`.`business` (`id`)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

ALTER TABLE `postgresql`.`business_labour_payroll` 
	DROP FOREIGN KEY `business_labour_payroll_fk1`;

ALTER TABLE `postgresql`.`business_labour_payroll` 
ADD CONSTRAINT `business_labour_payroll_business_id` 
  FOREIGN KEY (`business_id`)
  REFERENCES `vn`.`business` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `postgresql`.`business_labour` 
DROP FOREIGN KEY `bus_restriction`;

ALTER TABLE `postgresql`.`business_labour` 
ADD CONSTRAINT `business_labour_business_id`
  FOREIGN KEY (`business_id`)
  REFERENCES `vn`.`business` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

-- Es necesario hacerlo aquí porque falla en otras vistas donde se utiliza esta
CREATE 
     OR REPLACE ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `postgresql`.`calendar_employee` AS
    SELECT 
        `ce`.`id` AS `id`,
        `ce`.`businessFk` AS `businessFk`,
        `ce`.`dayOffTypeFk` AS `calendar_state_id`,
        `ce`.`dated` AS `date`
    FROM
        `vn`.`calendar` `ce`;

DROP TABLE `vn`.`profile`;

ALTER TABLE `vn`.`business` 
ADD COLUMN `notes` LONGTEXT NULL DEFAULT NULL AFTER `workerFk`,
ADD COLUMN `departmentFk` INT(11) NULL DEFAULT NULL AFTER `notes`,
ADD COLUMN `workerBusinessProfessionalCategoryFk` INT(11) NULL DEFAULT NULL AFTER `departmentFk`,
ADD COLUMN `calendarTypeFk` INT(11) NULL DEFAULT 1 AFTER `workerBusinessProfessionalCategoryFk`,
ADD COLUMN `isHourlyLabor` tinyint(1) NOT NULL DEFAULT FALSE COMMENT 'Contrato por horas' AFTER `calendarTypeFk`,
ADD COLUMN `workcenterFk` INT(11) NOT NULL AFTER `isHourlyLabor`,
ADD COLUMN `rate` INT(11) NULL DEFAULT NULL AFTER `workcenterFk`,
ADD COLUMN `workerBusinessCategoryFk` INT(11) NULL DEFAULT NULL AFTER `rate`,
ADD COLUMN `workerBusinessTypeFk` INT(11) NULL DEFAULT NULL AFTER `workerBusinessCategoryFk`,
ADD COLUMN `amount` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Importe pactado' AFTER `workerBusinessTypeFk`,
ADD COLUMN `workerBusinessAgreementFk` INT(11) NULL DEFAULT NULL AFTER `amount`;

UPDATE vn.business b
    LEFT JOIN postgresql.business_labour bl ON bl.business_id = b.id
    LEFT JOIN postgresql.business_labour_payroll blp ON blp.business_id = b.id
  SET b.notes = bl.notes,
    b.departmentFk = bl.department_id,
    b.workerBusinessProfessionalCategoryFk = bl.professional_category_id,
    b.calendarTypeFk = bl.calendar_labour_type_id,
    b.isHourlyLabor = bl.porhoras,
    b.workcenterFk = bl.workcenter_id,
    b.rate = blp.cod_tarifa,
    b.workerBusinessCategoryFk = blp.cod_categoria,
    b.workerBusinessTypeFk = blp.cod_contrato,
    b.amount = blp.importepactado,
    b.workerBusinessAgreementFk = bl.labour_agreement_id;

  ALTER TABLE `vn`.`business` 
    ADD INDEX `business_departmentFk_idx` (`departmentFk` ASC);

  ALTER TABLE `vn`.`business` 
    ADD CONSTRAINT `business_departmentFk`
      FOREIGN KEY (`departmentFk`)
      REFERENCES `vn`.`department` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE;

  ALTER TABLE `vn`.`business` 
	  ADD INDEX `business_workerBusinessProfessionalCategoryFk_idx` (`workerBusinessProfessionalCategoryFk` ASC) ;  

  ALTER TABLE `vn`.`business` 
    ADD CONSTRAINT `business_workerBusinessProfessionalCategoryFk`
      FOREIGN KEY (`workerBusinessProfessionalCategoryFk`)
      REFERENCES `postgresql`.`professional_category` (`professional_category_id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE;

  ALTER TABLE `vn`.`business` 
    ADD INDEX `business_calendarTypeFk_idx` (`calendarTypeFk` ASC);

  ALTER TABLE `vn`.`business` 
    ADD CONSTRAINT `business_calendarTypeFk`
      FOREIGN KEY (`calendarTypeFk`)
      REFERENCES `postgresql`.`calendar_labour_type` (`calendar_labour_type_id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE;

  ALTER TABLE `vn`.`business` 
    ADD CONSTRAINT `business_workerBusinessCategoryFk`
      FOREIGN KEY (`workerBusinessCategoryFk`)
      REFERENCES `vn2008`.`payroll_categorias` (`codcategoria`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE;

  ALTER TABLE `vn`.`business` 
    ADD INDEX `business_workerBusinessTypeFk_idx` (`workerBusinessTypeFk` ASC);
    
  ALTER TABLE `vn`.`business` 
    ADD CONSTRAINT `business_workerBusinessTypeFk`
      FOREIGN KEY (`workerBusinessTypeFk`)
      REFERENCES `vn`.`workerBusinessType` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE;

  ALTER TABLE `vn`.`business` 
    ADD INDEX `business_workerBusinessAgreementFk_idx` (`workerBusinessAgreementFk` ASC);

  ALTER TABLE `vn`.`business` 
    ADD CONSTRAINT `business_workerBusinessAgreementFk`
      FOREIGN KEY (`workerBusinessAgreementFk`)
      REFERENCES `postgresql`.`labour_agreement` (`labour_agreement_id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE;  

ALTER TABLE `postgresql`.`profile` 
  RENAME TO  `postgresql`.`profile__` ;

ALTER TABLE `postgresql`.`business` 
  RENAME TO  `postgresql`.`business__` ;

ALTER TABLE `postgresql`.`business_labour` 
  RENAME TO  `postgresql`.`business_labour__` ;

ALTER TABLE `postgresql`.`business_labour_payroll` 
  RENAME TO  `postgresql`.`business_labour_payroll__` ;

ALTER TABLE `postgresql`.`journey` 
	DROP FOREIGN KEY `business_journey`;
ALTER TABLE `postgresql`.`journey` 
	ADD INDEX `journey_business_id_idx` (`business_id` ASC) ,
	DROP INDEX `fki_business_journey` ;

ALTER TABLE `postgresql`.`journey` 
	ADD CONSTRAINT `journey_business_id`
	  FOREIGN KEY (`business_id`)
	  REFERENCES `vn`.`business` (`id`)
	  ON DELETE RESTRICT
	  ON UPDATE CASCADE;

ALTER TABLE `vn`.`calendar` 
	DROP FOREIGN KEY `calendar_businessFk`;

ALTER TABLE `vn`.`calendar` 
DROP INDEX `business_id_date` ;

ALTER TABLE `vn`.`calendar` 
	ADD CONSTRAINT `calendar_businessFk`
	  FOREIGN KEY (`businessFk`)
	  REFERENCES `vn`.`business` (`id`)
	  ON DELETE RESTRICT
	  ON UPDATE CASCADE;

ALTER TABLE `vn`.`workerJourney` 
	DROP FOREIGN KEY `workerJourney_businessFk`;

ALTER TABLE `vn`.`workerJourney` 
	DROP INDEX `workerJourney_businessFk_idx` ;

ALTER TABLE `vn`.`workerJourney` 
	ADD CONSTRAINT `workerJourney_businessFk`
	  FOREIGN KEY (`businessFk`)
	  REFERENCES `vn`.`business` (`id`)
	  ON DELETE RESTRICT
	  ON UPDATE CASCADE;