-- 51_ptrp_treatments.sql
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `ptrp_treatments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `tenant_company_id` BIGINT UNSIGNED NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `rep_p_mark_id` BIGINT UNSIGNED NULL,
  `time_punch_id` BIGINT UNSIGNED NULL,
  `treatment_type` ENUM(
    'INCLUSAO_MANUAL',
    'DESCONSIDERACAO',
    'AJUSTE_HORARIO',
    'PRE_ASSINALACAO',
    'JUSTIFICATIVA',
    'AUSENCIA',
    'BANCO_HORAS'
  ) NOT NULL,
  `motivo` VARCHAR(80) NULL,
  `justificativa` VARCHAR(500) NULL,
  `status` ENUM('PENDING','APPROVED','REJECTED','APPLIED') NOT NULL DEFAULT 'APPLIED',
  `requested_by_user_id` BIGINT UNSIGNED NULL,
  `requested_by_cpf` VARCHAR(14) NULL,
  `approved_by_user_id` BIGINT UNSIGNED NULL,
  `requested_at` DATETIME NOT NULL,
  `approved_at` DATETIME NULL,
  `value_before` VARCHAR(64) NULL,
  `value_after` VARCHAR(64) NULL,
  `ip_address` VARCHAR(45) NULL,
  `device_label` VARCHAR(100) NULL,
  `evidence_path` VARCHAR(500) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_ptrp_tr_tenant_emp` (`tenant_id`, `employee_id`, `requested_at`),
  KEY `idx_ptrp_tr_mark` (`rep_p_mark_id`),
  KEY `idx_ptrp_tr_punch` (`time_punch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ptrp_exports` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `tenant_company_id` BIGINT UNSIGNED NULL,
  `period_from` DATE NOT NULL,
  `period_to` DATE NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NULL,
  `p7s_path` VARCHAR(500) NULL,
  `is_official` TINYINT(1) NOT NULL DEFAULT 0,
  `generated_by_user_id` BIGINT UNSIGNED NULL,
  `file_sha256` CHAR(64) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_ptrp_exports_tenant` (`tenant_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ptrp_pre_assinalacao_config` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `tenant_company_id` BIGINT UNSIGNED NULL,
  `employee_id` BIGINT UNSIGNED NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `interval_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 60,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_ptrp_pre_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;