-- Várias empresas (CNPJs) por tenant no painel RH.
-- Pré-requisito: desktop/database/29_tenant_company_cnpj.sql já aplicado (colunas em `tenants`).

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `tenant_companies` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `display_name` VARCHAR(150) NOT NULL,
  `cnpj` CHAR(14) NULL DEFAULT NULL COMMENT 'Somente dígitos',
  `legal_name` VARCHAR(200) NULL DEFAULT NULL,
  `trade_name` VARCHAR(200) NULL DEFAULT NULL,
  `company_phone` VARCHAR(30) NULL DEFAULT NULL,
  `company_cep` VARCHAR(10) NULL DEFAULT NULL,
  `company_street` VARCHAR(200) NULL DEFAULT NULL,
  `company_number` VARCHAR(20) NULL DEFAULT NULL,
  `company_complement` VARCHAR(120) NULL DEFAULT NULL,
  `company_neighborhood` VARCHAR(120) NULL DEFAULT NULL,
  `company_city` VARCHAR(120) NULL DEFAULT NULL,
  `company_state` CHAR(2) NULL DEFAULT NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_tc_tenant_cnpj` (`tenant_id`, `cnpj`),
  KEY `idx_tc_tenant` (`tenant_id`),
  KEY `idx_tc_tenant_default` (`tenant_id`, `is_default`),
  CONSTRAINT `fk_tc_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Copia o cadastro único que estava em `tenants` para a primeira linha de `tenant_companies`.
INSERT INTO `tenant_companies` (
  `tenant_id`,
  `display_name`,
  `cnpj`,
  `legal_name`,
  `trade_name`,
  `company_phone`,
  `company_cep`,
  `company_street`,
  `company_number`,
  `company_complement`,
  `company_neighborhood`,
  `company_city`,
  `company_state`,
  `is_default`
)
SELECT
  `t`.`id`,
  `t`.`name`,
  NULLIF(TRIM(`t`.`cnpj`), ''),
  NULLIF(TRIM(`t`.`legal_name`), ''),
  NULLIF(TRIM(`t`.`trade_name`), ''),
  NULLIF(TRIM(`t`.`company_phone`), ''),
  NULLIF(TRIM(`t`.`company_cep`), ''),
  NULLIF(TRIM(`t`.`company_street`), ''),
  NULLIF(TRIM(`t`.`company_number`), ''),
  NULLIF(TRIM(`t`.`company_complement`), ''),
  NULLIF(TRIM(`t`.`company_neighborhood`), ''),
  NULLIF(TRIM(`t`.`company_city`), ''),
  NULLIF(TRIM(`t`.`company_state`), ''),
  1
FROM `tenants` `t`
WHERE NOT EXISTS (
  SELECT 1 FROM `tenant_companies` `c` WHERE `c`.`tenant_id` = `t`.`id`
);

-- CNPJ deixa de ser único globalmente em `tenants` (agora há N empresas por conta).
SET @idx := (
  SELECT COUNT(*) FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'tenants'
    AND index_name = 'uk_tenants_cnpj'
);
SET @q := IF(@idx > 0, 'ALTER TABLE `tenants` DROP INDEX `uk_tenants_cnpj`', 'SELECT 1');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;