-- Pesquisa de satisfação dos moradores com a empresa (estrelas 1–5, comentário opcional).
-- Pré-requisito: 35_portal_public_tabs.sql

SET NAMES utf8mb4;

ALTER TABLE `tenant_condominiums`
  ADD COLUMN `public_portal_tab_satisfaction` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Aba pesquisa satisfação';

CREATE TABLE IF NOT EXISTS `condominium_resident_satisfaction` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `tenant_condominium_id` BIGINT UNSIGNED NOT NULL,
  `stars` TINYINT UNSIGNED NOT NULL COMMENT '1 a 5',
  `comment` TEXT NULL DEFAULT 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_crs_condo_created` (`tenant_id`, `tenant_condominium_id`, `created_at`),
  CONSTRAINT `fk_crs_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_crs_condo` FOREIGN KEY (`tenant_condominium_id`) REFERENCES `tenant_condominiums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;