-- Documentos de admissão (CTPS, RG, CPF, comprovante, outros) — upload pelo painel web.

SET NAMES utf8mb4;

USE `ponto-sas`;

CREATE TABLE IF NOT EXISTS `employee_documents` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `doc_type` ENUM('ctps','rg','cpf_proof','address_proof','other') NOT NULL,
  `original_filename` VARCHAR(255) NOT NULL,
  `stored_filename` VARCHAR(180) NOT NULL,
  `mime_type` VARCHAR(120) NOT NULL,
  `size_bytes` INT UNSIGNED NOT NULL,
  `uploaded_by_user_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY `idx_employee_documents_employee` (`tenant_id`, `employee_id`, `created_at`),
  KEY `idx_employee_documents_type` (`tenant_id`, `employee_id`, `doc_type`),

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