-- Messaging module: DMs, public channel, admin-managed groups
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

CREATE TABLE IF NOT EXISTS `message_conversations` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `type`                  ENUM('direct','group','public') NOT NULL DEFAULT 'direct',
    `name`                  VARCHAR(150)        NULL,
    `description`           VARCHAR(500)        NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `is_system`             TINYINT(1)          NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `type` (`type`),
    KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `message_participants` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `conversation_id`       BIGINT UNSIGNED     NOT NULL,
    `user_id`               BIGINT UNSIGNED     NOT NULL,
    `last_read_at`          DATETIME            NULL,
    `joined_at`             DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `conversation_user` (`conversation_id`, `user_id`),
    KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `messages` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `conversation_id`       BIGINT UNSIGNED     NOT NULL,
    `sender_id`             BIGINT UNSIGNED     NOT NULL,
    `body`                  TEXT                NOT NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    `deleted_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `conversation_id` (`conversation_id`),
    KEY `sender_id` (`sender_id`),
    KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed the company-wide public channel
INSERT INTO `message_conversations` (`type`, `name`, `description`, `is_system`, `created_at`, `updated_at`)
SELECT 'public', 'Public', 'Company-wide public channel — everyone can read and post.', 1, NOW(), NOW()
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM `message_conversations` WHERE `type` = 'public' AND `is_system` = 1
);

-- Permissions
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'messages', 'messages.view', 'View Messages', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'messages.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'messages', 'messages.send', 'Send Messages', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'messages.send');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'messages', 'messages.manage_groups', 'Manage Message Groups', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'messages.manage_groups');

-- Every role can view and send messages
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created_at`)
SELECT r.id, p.id, NOW()
FROM `roles` r
CROSS JOIN `permissions` p
WHERE p.slug IN ('messages.view', 'messages.send')
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

-- Only Administrator can manage groups
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created_at`)
SELECT r.id, p.id, NOW()
FROM `roles` r
CROSS JOIN `permissions` p
WHERE r.slug = 'administrator'
  AND p.slug = 'messages.manage_groups'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
