-- Catálogo de benefícios por tenant + atribuições a colaboradores (RH)

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `tenant_benefit_catalog` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(180) NOT NULL,
  `provider_label` VARCHAR(120) NULL,
  `category` VARCHAR(80) NULL,
  `default_amount` DECIMAL(12,2) NULL,
  `icon_key` VARCHAR(40) NOT NULL DEFAULT 'card_giftcard',
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tbc_tenant_active` (`tenant_id`, `is_active`, `sort_order`),
  CONSTRAINT `fk_tbc_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_benefit_assignments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `catalog_id` BIGINT UNSIGNED NOT NULL,
  `starts_on` DATE NOT NULL,
  `ends_on` DATE NULL,
  `company_subsidy_amount` DECIMAL(12,2) NULL,
  `coparticipation` ENUM('default_20','exempt','full_100') NOT NULL DEFAULT 'default_20',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_eba_emp_active` (`tenant_id`, `employee_id`, `is_active`),
  KEY `idx_eba_catalog` (`tenant_id`, `catalog_id`),
  CONSTRAINT `fk_eba_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_eba_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_eba_catalog`
    FOREIGN KEY (`catalog_id`) REFERENCES `tenant_benefit_catalog` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;