-- Vale transporte por cidade (catálogo) + atribuição ao colaborador (dias/semana → estimativa mensal)

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `tenant_transport_pass_cities` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `city_name` VARCHAR(120) NOT NULL,
  `state` CHAR(2) NULL,
  `operator_label` VARCHAR(120) NULL,
  `unit_price` DECIMAL(10,2) NOT NULL COMMENT 'Valor de uma viagem (passe unitário)',
  `trips_per_workday` DECIMAL(4,2) NOT NULL DEFAULT 2.00 COMMENT 'Ex.: 2 = ida e volta',
  `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_ttpc_tenant` (`tenant_id`, `is_active`, `sort_order`),
  CONSTRAINT `fk_ttpc_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_transport_pass_assignments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `city_catalog_id` BIGINT UNSIGNED NOT NULL,
  `workdays_per_week` TINYINT UNSIGNED NOT NULL DEFAULT 5 COMMENT '1 a 7 dias com deslocamento',
  `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_etpa_emp` (`tenant_id`, `employee_id`, `is_active`),
  KEY `idx_etpa_city` (`tenant_id`, `city_catalog_id`),
  CONSTRAINT `fk_etpa_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_etpa_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_etpa_city`
    FOREIGN KEY (`city_catalog_id`) REFERENCES `tenant_transport_pass_cities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;