-- Vincula colaborador à empresa (CNPJ) e ao condomínio de trabalho.
-- Pré-requisitos: `30_tenant_companies.sql` e `31_tenant_condominiums.sql` já aplicados.

SET NAMES utf8mb4;

ALTER TABLE `employees`
  ADD COLUMN `tenant_company_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'Empresa (CNPJ) em que está registrado' AFTER `department_id`,
  ADD COLUMN `tenant_condominium_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'Condomínio onde trabalha' AFTER `tenant_company_id`;

-- Preenche empresa: preferência para a marcada como principal; senão, menor id.
UPDATE `employees` `e`
INNER JOIN `tenant_companies` `tc` ON `tc`.`tenant_id` = `e`.`tenant_id` AND `tc`.`is_default` = 1
SET `e`.`tenant_company_id` = `tc`.`id`
WHERE `e`.`tenant_company_id` IS NULL;

UPDATE `employees` `e`
INNER JOIN (
  SELECT `tenant_id`, MIN(`id`) AS `pick_id` FROM `tenant_companies` GROUP BY `tenant_id`
) `x` ON `x`.`tenant_id` = `e`.`tenant_id`
SET `e`.`tenant_company_id` = `x`.`pick_id`
WHERE `e`.`tenant_company_id` IS NULL;

-- Preenche condomínio quando houver um principal.
UPDATE `employees` `e`
INNER JOIN `tenant_condominiums` `tco` ON `tco`.`tenant_id` = `e`.`tenant_id` AND `tco`.`is_default` = 1
SET `e`.`tenant_condominium_id` = `tco`.`id`
WHERE `e`.`tenant_condominium_id` IS NULL;

ALTER TABLE `employees`
  ADD KEY `idx_employees_tenant_company` (`tenant_company_id`),
  ADD KEY `idx_employees_tenant_condominium` (`tenant_condominium_id`),
  ADD CONSTRAINT `fk_employees_tenant_company`
    FOREIGN KEY (`tenant_company_id`) REFERENCES `tenant_companies` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_employees_tenant_condominium`
    FOREIGN KEY (`tenant_condominium_id`) REFERENCES `tenant_condominiums` (`id`) ON DELETE SET NULL;