-- =============================================================================
-- Registro de Limpeza — cronograma semanal (RH) + colaborador + QR de fechamento
-- =============================================================================
-- Fluxo:
-- 1) RH cadastra PONTOS (QR) por local físico (cleaning_checkpoints).
-- 2) RH monta um PLANO: para cada dia da semana (1=seg … 7=dom) há BLOCOS;
--    cada bloco tem título, instruções, checklist (tarefas) e um checkpoint_id
--    (QR que a colaboradora escaneia ao concluir aquele bloco naquele dia).
-- 3) RH ATRIBUI o plano a uma colaboradora (cleaning_assignments) com vigência.
-- 4) No app, no dia corrente, só aparecem os blocos daquele weekday; ao finalizar
--    (fotos + observações), ela escaneia o QR do local → cleaning_completions.
-- =============================================================================

SET NAMES utf8mb4;

USE `ponto-sas`;

CREATE TABLE IF NOT EXISTS `cleaning_checkpoints` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `area_name` VARCHAR(120) NOT NULL,
  `location_name` VARCHAR(160) NOT NULL,
  `qr_token` VARCHAR(128) NOT NULL,
  `latitude` DECIMAL(10,7) NULL,
  `longitude` DECIMAL(10,7) NULL,
  `radius_m` INT UNSIGNED NOT NULL DEFAULT 80,
  `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_clean_qr_tenant` (`tenant_id`, `qr_token`),
  KEY `idx_clean_cp_tenant` (`tenant_id`, `is_active`),
  CONSTRAINT `fk_clean_cp_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 `cleaning_plans` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(160) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_clean_plan_tenant` (`tenant_id`, `is_active`),
  CONSTRAINT `fk_clean_plan_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 `cleaning_plan_blocks` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `plan_id` BIGINT UNSIGNED NOT NULL,
  `weekday` TINYINT UNSIGNED NOT NULL COMMENT '1=segunda … 7=domingo (PHP date N)',
  `title` VARCHAR(200) NOT NULL,
  `instructions` TEXT NULL,
  `checkpoint_id` BIGINT UNSIGNED NOT NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_clean_block_plan_day` (`plan_id`, `weekday`, `sort_order`),
  CONSTRAINT `fk_clean_block_plan`
    FOREIGN KEY (`plan_id`) REFERENCES `cleaning_plans`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_clean_block_checkpoint`
    FOREIGN KEY (`checkpoint_id`) REFERENCES `cleaning_checkpoints`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cleaning_plan_tasks` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `block_id` BIGINT UNSIGNED NOT NULL,
  `task_text` VARCHAR(500) NOT NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  KEY `idx_clean_task_block` (`block_id`, `sort_order`),
  CONSTRAINT `fk_clean_task_block`
    FOREIGN KEY (`block_id`) REFERENCES `cleaning_plan_blocks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cleaning_assignments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `plan_id` BIGINT UNSIGNED NOT NULL,
  `valid_from` DATE NOT NULL,
  `valid_to` DATE NULL COMMENT 'NULL = sem fim',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_clean_asg_emp` (`tenant_id`, `employee_id`, `is_active`),
  KEY `idx_clean_asg_dates` (`tenant_id`, `valid_from`, `valid_to`),
  CONSTRAINT `fk_clean_asg_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_clean_asg_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_clean_asg_plan`
    FOREIGN KEY (`plan_id`) REFERENCES `cleaning_plans`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cleaning_completions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `plan_block_id` BIGINT UNSIGNED NOT NULL,
  `checkpoint_id` BIGINT UNSIGNED NOT NULL,
  `work_date` DATE NOT NULL COMMENT 'Dia útil do cronograma (fuso servidor / SP)',
  `notes` VARCHAR(2000) NULL,
  `photo_paths` TEXT NULL COMMENT 'JSON array de paths relativos storage',
  `raw_qr_text` VARCHAR(2000) NULL,
  `client_completion_id` VARCHAR(96) NULL,
  `completed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_clean_done_day` (`tenant_id`, `employee_id`, `plan_block_id`, `work_date`),
  UNIQUE KEY `uk_clean_client` (`tenant_id`, `employee_id`, `client_completion_id`),
  KEY `idx_clean_done_tenant_time` (`tenant_id`, `completed_at`),
  CONSTRAINT `fk_clean_done_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_clean_done_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_clean_done_block`
    FOREIGN KEY (`plan_block_id`) REFERENCES `cleaning_plan_blocks`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_clean_done_checkpoint`
    FOREIGN KEY (`checkpoint_id`) REFERENCES `cleaning_checkpoints`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;