-- Portal público por condomínio (QR para moradores) + diário do zelador.
-- Pré-requisitos: 31_tenant_condominiums.sql, 16_cleaning_schedule.sql (planos), 32_employee_tenant_company_condominium.sql (opcional, para filtrar colaboradores).

SET NAMES utf8mb4;

-- Colunas no condomínio (execute uma vez; se já existirem, ignore o erro ou aplique manualmente).
ALTER TABLE `tenant_condominiums`
  ADD COLUMN `public_portal_token` VARCHAR(40) NULL DEFAULT NULL COMMENT 'Token secreto na URL pública',
  ADD COLUMN `public_portal_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN `public_portal_plan_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'Plano de limpeza exibido; NULL = inferir do zelador',
  ADD COLUMN `public_portal_title` VARCHAR(160) NULL DEFAULT NULL COMMENT 'Título na página pública',
  ADD COLUMN `public_portal_subtitle` VARCHAR(500) NULL DEFAULT NULL;

ALTER TABLE `tenant_condominiums`
  ADD UNIQUE KEY `uk_tcondo_public_token` (`public_portal_token`);

ALTER TABLE `tenant_condominiums`
  ADD KEY `idx_tcondo_portal` (`tenant_id`, `public_portal_enabled`);

ALTER TABLE `tenant_condominiums`
  ADD CONSTRAINT `fk_tcondo_public_plan`
    FOREIGN KEY (`public_portal_plan_id`) REFERENCES `cleaning_plans` (`id`) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS `condominium_zelador_daily_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `tenant_condominium_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `work_date` DATE NOT NULL COMMENT 'Dia do registro (fuso SP)',
  `notes` TEXT NULL,
  `photo_paths` TEXT NULL COMMENT 'JSON array paths storage/condominium_zelador/...',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_zelador_condo_day` (`tenant_id`, `tenant_condominium_id`, `work_date`),
  KEY `idx_zelador_emp_day` (`tenant_id`, `employee_id`, `work_date`),
  CONSTRAINT `fk_zelador_log_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_zelador_log_condo` FOREIGN KEY (`tenant_condominium_id`) REFERENCES `tenant_condominiums` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_zelador_log_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;