-- Elogios e reclamações de moradores sobre colaboradores (portal público).
-- Pré-requisito: 31_tenant_condominiums.sql, 35_portal_public_tabs.sql (abas do portal).

SET NAMES utf8mb4;

ALTER TABLE `tenant_condominiums`
  ADD COLUMN `public_portal_tab_compliment` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Aba elogios moradores',
  ADD COLUMN `public_portal_tab_complaint` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Aba reclamações moradores';

CREATE TABLE IF NOT EXISTS `condominium_resident_feedback` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `tenant_condominium_id` BIGINT UNSIGNED NOT NULL,
  `feedback_kind` ENUM('compliment','complaint') NOT NULL,
  `employee_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `message` TEXT NOT NULL,
  `resident_name` VARCHAR(120) NOT NULL,
  `unit_label` VARCHAR(80) NULL DEFAULT NULL,
  `contact_phone` VARCHAR(40) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_crf_condo_created` (`tenant_id`, `tenant_condominium_id`, `created_at`),
  KEY `idx_crf_employee` (`tenant_id`, `employee_id`),
  CONSTRAINT `fk_crf_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_crf_condo` FOREIGN KEY (`tenant_condominium_id`) REFERENCES `tenant_condominiums` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_crf_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;