-- Solicitações do app (Troca / Apoio / Ausência) — employee_plantao_solicitacoes
SET NAMES utf8mb4;
USE `ponto-sas`;

CREATE TABLE IF NOT EXISTS `employee_plantao_solicitacoes` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `request_type` ENUM('troca','apoio','ausencia') NOT NULL,
  `status` ENUM('PENDING','APPROVED','REJECTED','CANCELLED') NOT NULL DEFAULT 'PENDING',

  `work_date` DATE NULL COMMENT 'troca: data do plantão',
  `work_shift_type_id` BIGINT UNSIGNED NULL,
  `colleague_employee_id` BIGINT UNSIGNED NULL COMMENT 'troca: colega',

  `apoio_description` VARCHAR(2000) NULL,
  `apoio_priority` ENUM('baixa','media','alta') NULL,

  `ausencia_date` DATE NULL,
  `ausencia_time` TIME NULL,
  `attachment_path` VARCHAR(512) NULL COMMENT 'URL ou caminho público do anexo',

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY `idx_eps_tenant_employee_created` (`tenant_id`, `employee_id`, `created_at`),

  CONSTRAINT `fk_eps_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_eps_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_eps_shift`
    FOREIGN KEY (`work_shift_type_id`) REFERENCES `work_shift_types`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_eps_colleague`
    FOREIGN KEY (`colleague_employee_id`) REFERENCES `employees`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;