-- 50_rep_p_core.sql — Store REP-P imutável + config (reversível: DROP das tabelas novas)
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `rep_p_program_config` (
  `id` TINYINT UNSIGNED NOT NULL PRIMARY KEY DEFAULT 1,
  `inpi_number` VARCHAR(17) NULL DEFAULT NULL COMMENT 'Somente digitos; pendente se NULL',
  `software_name` VARCHAR(120) NOT NULL DEFAULT 'Registro Pro REP-P',
  `software_version` VARCHAR(40) NOT NULL DEFAULT '1.0.0',
  `developer_legal_name` VARCHAR(200) NULL,
  `developer_cnpj` CHAR(14) NULL,
  `developer_id_type` CHAR(1) NOT NULL DEFAULT '1' COMMENT '1=CNPJ 2=CPF',
  `timezone_offset` CHAR(5) NOT NULL DEFAULT '-0300',
  `updated_by_user_id` BIGINT UNSIGNED NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `rep_p_program_config` (`id`) VALUES (1);

CREATE TABLE IF NOT EXISTS `rep_p_establishment_settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `tenant_company_id` BIGINT UNSIGNED NOT NULL,
  `dual_write_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `official_export_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `cutover_at` DATETIME NULL DEFAULT NULL,
  `timezone_offset` CHAR(5) NOT NULL DEFAULT '-0300',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_rep_p_est` (`tenant_company_id`),
  KEY `idx_rep_p_est_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rep_p_nsr_counters` (
  `tenant_company_id` BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employer_doc` CHAR(14) NOT NULL COMMENT 'CNPJ/CPF normalizado snapshot',
  `last_nsr` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_rep_p_nsr_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rep_p_marks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `tenant_company_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `nsr` BIGINT UNSIGNED NOT NULL,
  `record_type` CHAR(1) NOT NULL DEFAULT '7',
  `marked_at` DATETIME NOT NULL COMMENT 'Horario da marcacao (captura)',
  `marked_at_afd` CHAR(24) NOT NULL COMMENT 'AAAA-MM-DDThh:mm:00ZZZZZ',
  `recorded_at` DATETIME NOT NULL COMMENT 'Horario da gravacao no servidor',
  `recorded_at_afd` CHAR(24) NOT NULL,
  `employee_cpf` CHAR(11) NOT NULL,
  `employee_cpf_afd` CHAR(12) NOT NULL COMMENT '12 digitos com zero a esquerda',
  `collector_id` CHAR(2) NOT NULL COMMENT '01..05',
  `online_offline` CHAR(1) NOT NULL DEFAULT '0' COMMENT '0=online 1=offline',
  `sha256` CHAR(64) NOT NULL,
  `prev_sha256` CHAR(64) NULL DEFAULT NULL,
  `employer_doc` CHAR(14) NOT NULL,
  `employer_doc_type` CHAR(1) NOT NULL DEFAULT '1',
  `local_uuid` VARCHAR(64) NULL DEFAULT NULL,
  `source_punch_id` BIGINT UNSIGNED NULL,
  `source_channel` VARCHAR(32) NULL,
  `ip_address` VARCHAR(45) NULL,
  `device_label` VARCHAR(100) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_rep_p_marks_est_nsr` (`tenant_company_id`, `nsr`),
  UNIQUE KEY `uk_rep_p_marks_local_uuid` (`tenant_id`, `local_uuid`),
  KEY `idx_rep_p_marks_employee` (`tenant_id`, `employee_id`, `marked_at`),
  KEY `idx_rep_p_marks_punch` (`source_punch_id`),
  KEY `idx_rep_p_marks_period` (`tenant_company_id`, `marked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rep_p_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `tenant_company_id` BIGINT UNSIGNED NOT NULL,
  `nsr` BIGINT UNSIGNED NOT NULL,
  `event_type` VARCHAR(40) NOT NULL,
  `event_at` DATETIME NOT NULL,
  `event_at_afd` CHAR(24) NOT NULL,
  `unavailable_from` DATETIME NULL,
  `unavailable_to` DATETIME NULL,
  `service_name` VARCHAR(80) NULL,
  `origin` VARCHAR(80) NULL,
  `details_json` JSON NULL,
  `sha256` CHAR(64) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_rep_p_events_est_nsr` (`tenant_company_id`, `nsr`),
  KEY `idx_rep_p_events_tenant` (`tenant_id`, `event_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rep_p_punch_attempts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NULL,
  `tenant_company_id` BIGINT UNSIGNED NULL,
  `result` VARCHAR(40) NOT NULL,
  `reason` VARCHAR(255) NULL,
  `channel` VARCHAR(32) NULL,
  `client_event_at` DATETIME NULL,
  `ip_address` VARCHAR(45) NULL,
  `payload_json` JSON NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_rep_p_attempts_tenant` (`tenant_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rep_p_receipts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `rep_p_mark_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `pdf_path` VARCHAR(500) NULL,
  `signature_status` ENUM('pending','signed','failed','unsigned_dev') NOT NULL DEFAULT 'pending',
  `signed_at` DATETIME NULL,
  `cert_fingerprint` VARCHAR(64) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_rep_p_receipt_mark` (`rep_p_mark_id`),
  KEY `idx_rep_p_receipt_emp` (`tenant_id`, `employee_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rep_p_exports` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `tenant_company_id` BIGINT UNSIGNED NOT 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_rep_p_exports_tenant` (`tenant_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `compliance_audit_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NULL,
  `actor_user_id` BIGINT UNSIGNED NULL,
  `actor_cpf` VARCHAR(14) NULL,
  `action` VARCHAR(80) NOT NULL,
  `entity` VARCHAR(80) NOT NULL,
  `entity_id` VARCHAR(64) NULL,
  `result` VARCHAR(40) NOT NULL DEFAULT 'ok',
  `ip_address` VARCHAR(45) NULL,
  `device_label` VARCHAR(100) NULL,
  `before_json` JSON NULL,
  `after_json` JSON NULL,
  `justification` VARCHAR(500) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_compliance_audit_tenant` (`tenant_id`, `created_at`),
  KEY `idx_compliance_audit_action` (`action`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;