-- Notificações do painel RH (portal do condomínio: elogios, reclamações, satisfação).
-- Pré-requisito: 03_auth_schema.sql (tenants), 24_system_users_tenant_users.sql (tenant_users)

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `tenant_panel_notifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `kind` VARCHAR(40) NOT NULL COMMENT 'portal_compliment, portal_complaint, portal_satisfaction',
  `title` VARCHAR(200) NOT NULL,
  `body` TEXT NULL,
  `link_url` VARCHAR(500) NULL DEFAULT NULL,
  `tenant_condominium_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `reference_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tpn_tenant_created` (`tenant_id`, `created_at`),
  KEY `idx_tpn_tenant_id` (`tenant_id`, `id`),
  CONSTRAINT `fk_tpn_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 `tenant_panel_notification_reads` (
  `notification_id` BIGINT UNSIGNED NOT NULL,
  `tenant_user_id` BIGINT UNSIGNED NOT NULL,
  `read_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`notification_id`, `tenant_user_id`),
  KEY `idx_tpnr_user` (`tenant_user_id`),
  CONSTRAINT `fk_tpnr_notification` FOREIGN KEY (`notification_id`) REFERENCES `tenant_panel_notifications` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tpnr_tenant_user` FOREIGN KEY (`tenant_user_id`) REFERENCES `tenant_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;