-- Chat RH (painel) <-> colaborador (app) com persistencia por tenant.

SET NAMES utf8mb4;
USE `ponto-sas`;

CREATE TABLE IF NOT EXISTS `tenant_employee_chats` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `last_message_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY `uk_tenant_employee_chats_tenant_employee` (`tenant_id`, `employee_id`),
  KEY `idx_tenant_employee_chats_tenant_last` (`tenant_id`, `last_message_at`),

  CONSTRAINT `fk_tenant_employee_chats_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tenant_employee_chats_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tenant_employee_chat_messages` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `chat_id` BIGINT UNSIGNED NOT NULL,
  `sender_kind` ENUM('panel','employee') NOT NULL,
  `sender_user_id` BIGINT UNSIGNED NULL,
  `sender_employee_id` BIGINT UNSIGNED NULL,
  `message_text` VARCHAR(1000) NOT NULL,
  `read_at_panel` TIMESTAMP NULL DEFAULT NULL,
  `read_at_employee` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY `idx_tenant_chat_messages_tenant_chat_id` (`tenant_id`, `chat_id`, `id`),
  KEY `idx_tenant_chat_messages_chat_created` (`chat_id`, `created_at`),

  CONSTRAINT `fk_tenant_chat_messages_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tenant_chat_messages_chat`
    FOREIGN KEY (`chat_id`) REFERENCES `tenant_employee_chats`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tenant_chat_messages_sender_user`
    FOREIGN KEY (`sender_user_id`) REFERENCES `tenant_users`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_tenant_chat_messages_sender_employee`
    FOREIGN KEY (`sender_employee_id`) REFERENCES `employees`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;