-- ============================================================
-- Migration 005 : Améliorations messagerie
-- 10 points : push, soft-delete, signalement, réactions,
--             reply, mute, index, recherche, blocage, archive
-- Fichier : 005_messaging_improvements.sql
-- Date    : 2026-03-07
-- ============================================================
-- Compatible MySQL 8.0 (pas de ADD COLUMN IF NOT EXISTS multi-colonnes)
-- Idempotente via procédures + CREATE TABLE IF NOT EXISTS

-- ─── Point 2 : Soft-delete messages ─────────────────────────────────────────
-- Ajout conditionnel colonne deleted_at
DROP PROCEDURE IF EXISTS `_migration_add_column`;
DELIMITER //
CREATE PROCEDURE `_migration_add_column`()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND COLUMN_NAME = 'deleted_at'
  ) THEN
    ALTER TABLE `messages` ADD COLUMN `deleted_at` TIMESTAMP NULL DEFAULT NULL;
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND COLUMN_NAME = 'deleted_by'
  ) THEN
    ALTER TABLE `messages` ADD COLUMN `deleted_by` CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;
  END IF;

-- ─── Point 5 : Reply to message ─────────────────────────────────────────────
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND COLUMN_NAME = 'reply_to_id'
  ) THEN
    ALTER TABLE `messages` ADD COLUMN `reply_to_id` BIGINT UNSIGNED NULL DEFAULT NULL;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND CONSTRAINT_NAME = 'fk_messages_reply_to'
  ) THEN
    ALTER TABLE `messages`
      ADD CONSTRAINT `fk_messages_reply_to`
        FOREIGN KEY (`reply_to_id`) REFERENCES `messages`(`id`) ON DELETE SET NULL;
  END IF;

-- ─── Point 7 : Index DB sur messages ────────────────────────────────────────
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND INDEX_NAME = 'idx_messages_conv_id'
  ) THEN
    CREATE INDEX `idx_messages_conv_id` ON `messages` (`conversation_id`, `id`);
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND INDEX_NAME = 'idx_messages_deleted'
  ) THEN
    CREATE INDEX `idx_messages_deleted` ON `messages` (`conversation_id`, `deleted_at`);
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND INDEX_NAME = 'idx_messages_sender_read'
  ) THEN
    CREATE INDEX `idx_messages_sender_read` ON `messages` (`sender_id`, `read_at`);
  END IF;

-- ─── Point 8 : Recherche FULLTEXT ────────────────────────────────────────────
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND INDEX_NAME = 'ft_messages_content'
  ) THEN
    ALTER TABLE `messages` ADD FULLTEXT INDEX `ft_messages_content` (`content`);
  END IF;

-- ─── Point 7 : Index sur conversations ───────────────────────────────────────
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'conversations' AND INDEX_NAME = 'idx_conversations_user1_last'
  ) THEN
    CREATE INDEX `idx_conversations_user1_last` ON `conversations` (`user1_id`, `last_message_at`);
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'conversations' AND INDEX_NAME = 'idx_conversations_user2_last'
  ) THEN
    CREATE INDEX `idx_conversations_user2_last` ON `conversations` (`user2_id`, `last_message_at`);
  END IF;

END//
DELIMITER ;
CALL `_migration_add_column`();
DROP PROCEDURE IF EXISTS `_migration_add_column`;

-- ─── Point 3 : Signalement de messages ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS `message_reports` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `message_id`    BIGINT UNSIGNED NOT NULL,
  `reporter_id`   CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `reason`        ENUM('spam','harassment','inappropriate','hate_speech','other') NOT NULL DEFAULT 'other',
  `details`       VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `status`        ENUM('pending','reviewed','dismissed') NOT NULL DEFAULT 'pending',
  `reviewed_by`   CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `reviewed_at`   TIMESTAMP NULL DEFAULT NULL,
  `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_message_report` (`message_id`, `reporter_id`),
  KEY `idx_message_reports_message` (`message_id`),
  KEY `idx_message_reports_reporter` (`reporter_id`),
  KEY `idx_message_reports_status` (`status`),
  CONSTRAINT `fk_message_reports_message`
    FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_message_reports_reporter`
    FOREIGN KEY (`reporter_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Point 4 : Réactions sur messages ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `message_reactions` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `message_id`    BIGINT UNSIGNED NOT NULL,
  `user_id`       CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `emoji`         VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_message_reaction` (`message_id`, `user_id`, `emoji`),
  KEY `idx_message_reactions_message` (`message_id`),
  KEY `idx_message_reactions_user` (`user_id`),
  CONSTRAINT `fk_message_reactions_message`
    FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_message_reactions_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Points 6 + 10 : Paramètres par conversation (mute + archive) ─────────────
CREATE TABLE IF NOT EXISTS `conversation_settings` (
  `user_id`           CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `conversation_id`   BIGINT UNSIGNED NOT NULL,
  `is_muted`          TINYINT(1) NOT NULL DEFAULT 0,
  `archived_at`       TIMESTAMP NULL DEFAULT NULL,
  `updated_at`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`, `conversation_id`),
  KEY `idx_conv_settings_conv` (`conversation_id`),
  CONSTRAINT `fk_conv_settings_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_conv_settings_conv`
    FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Point 9 : Blocage utilisateur ciblé ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS `user_blocks` (
  `blocker_id`    CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `blocked_id`    CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`blocker_id`, `blocked_id`),
  KEY `idx_user_blocks_blocked` (`blocked_id`),
  CONSTRAINT `fk_user_blocks_blocker`
    FOREIGN KEY (`blocker_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_blocks_blocked`
    FOREIGN KEY (`blocked_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
