-- Ronda noturna / bastão móvel: checkpoints com QR criados pelo RH; colaborador escaneia e grava patrol_scans.

SET NAMES utf8mb4;

USE `ponto-sas`;

CREATE TABLE IF NOT EXISTS `patrol_checkpoints` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `area_name` VARCHAR(120) NOT NULL COMMENT 'Área (ex.: Bloco A, Pátio)',
  `point_name` VARCHAR(160) NOT NULL COMMENT 'Nome do ponto estratégico',
  `qr_token` VARCHAR(128) NOT NULL COMMENT 'Conteúdo exato do QR (ou token dentro de JSON/URL)',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `notes` VARCHAR(500) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_patrol_qr_tenant` (`tenant_id`, `qr_token`),
  KEY `idx_patrol_tenant_active` (`tenant_id`, `is_active`),
  CONSTRAINT `fk_patrol_checkpoint_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `patrol_scans` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `checkpoint_id` BIGINT UNSIGNED NOT NULL,
  `scanned_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `raw_qr_text` VARCHAR(2000) NULL,
  `client_scan_id` VARCHAR(96) NULL,
  KEY `idx_patrol_scans_tenant_time` (`tenant_id`, `scanned_at`),
  KEY `idx_patrol_scans_employee` (`tenant_id`, `employee_id`, `scanned_at`),
  KEY `idx_patrol_scans_checkpoint` (`tenant_id`, `checkpoint_id`, `scanned_at`),
  UNIQUE KEY `uk_patrol_scan_client` (`tenant_id`, `employee_id`, `client_scan_id`),
  CONSTRAINT `fk_patrol_scan_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_patrol_scan_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_patrol_scan_checkpoint`
    FOREIGN KEY (`checkpoint_id`) REFERENCES `patrol_checkpoints`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- RH: POST/GET/PATCH desktop/public/api/manager/patrol_checkpoints.php
-- RH: GET desktop/public/api/manager/patrol_scans_report.php
-- App: POST desktop/public/api/employee/patrol_scan.php | GET patrol_scans_recent.php
-- O QR pode ser só o qr_token, ou JSON {"rp_patrol":"<token>"}, ou URL ?t=<token>
--
-- Ordem sugerida de migrations: 10 (atestado), 11 (ronda flag), 12 (esta).