-- Modelos de advertência do RH (PDF). Atribuição ao colaborador é feita em fluxo separado (employee_id NULL).

SET NAMES utf8mb4;

USE `ponto-sas`;

CREATE TABLE IF NOT EXISTS `employee_advertencias` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NULL COMMENT 'Preenchido quando a advertência for atribuída a um colaborador',
  `title` VARCHAR(200) NOT NULL COMMENT 'Nome interno do modelo',
  `severity` ENUM('verbal','escrita','suspensao') NOT NULL DEFAULT 'escrita',
  `subject` VARCHAR(500) NOT NULL,
  `body_text` TEXT NOT NULL,
  `stored_pdf_filename` VARCHAR(180) NOT NULL,
  `created_by_user_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY `idx_employee_advertencias_tenant` (`tenant_id`, `created_at`),
  KEY `idx_employee_advertencias_tenant_emp` (`tenant_id`, `employee_id`),

  CONSTRAINT `fk_employee_advertencias_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_employee_advertencias_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_employee_advertencias_creator`
    FOREIGN KEY (`created_by_user_id`) REFERENCES `tenant_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;