-- Holerites (PDF) por colaborador — upload pelo RH (tenant_users); leitura no app (employee token).

SET NAMES utf8mb4;

USE `ponto-sas`;

CREATE TABLE IF NOT EXISTS `employee_holerites` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `reference_year` SMALLINT UNSIGNED NULL,
  `reference_month` TINYINT UNSIGNED NULL,
  `stored_filename` VARCHAR(128) NOT NULL,
  `original_filename` VARCHAR(255) NULL,
  `mime_type` VARCHAR(100) NOT NULL DEFAULT 'application/pdf',
  `uploaded_by_user_id` BIGINT UNSIGNED NULL,
  `signed_at` TIMESTAMP NULL DEFAULT NULL,
  `signed_bundle_filename` VARCHAR(255) NULL DEFAULT NULL,
  `signature_verify_token` VARCHAR(64) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY `idx_holerites_employee` (`tenant_id`, `employee_id`, `created_at`),
  KEY `idx_holerites_ref` (`tenant_id`, `employee_id`, `reference_year`, `reference_month`),
  UNIQUE KEY `uk_holerite_signature_verify_token` (`signature_verify_token`),

  CONSTRAINT `fk_holerites_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_holerites_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_holerites_uploader`
    FOREIGN KEY (`uploaded_by_user_id`) REFERENCES `tenant_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;