#!/usr/bin/env bash
# Script d'application de la migration 005 sur la DB locale Docker
# Usage : bash backend-api/migrations/apply_005_local.sh

set -euo pipefail

MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3316"
MYSQL_USER="root"
MYSQL_PASS='CBAsJg97c=h*nWypYMV8!jbzG6e45XKDiw#dZHlUOqLmv0uf'
MYSQL_DBNAME="faildaily"

q() {
  mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
    "$MYSQL_DBNAME" --execute="$1" 2>/dev/null
}

col_exists() {
  local count
  count=$(q "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='$1' AND COLUMN_NAME='$2'" | tail -1)
  [ "$count" -gt 0 ]
}

idx_exists() {
  local count
  count=$(q "SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='$1' AND INDEX_NAME='$2'" | tail -1)
  [ "$count" -gt 0 ]
}

constraint_exists() {
  local count
  count=$(q "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='$1' AND CONSTRAINT_NAME='$2'" | tail -1)
  [ "$count" -gt 0 ]
}

echo "=== Migration 005 : Améliorations messagerie ==="

# ─── Point 2 : Soft-delete ───────────────────────────────────────────────────
if ! col_exists messages deleted_at; then
  q "ALTER TABLE messages ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL"
  echo "✅ messages.deleted_at ajouté"
else echo "  messages.deleted_at déjà présent"; fi

if ! col_exists messages deleted_by; then
  q "ALTER TABLE messages ADD COLUMN deleted_by CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL"
  echo "✅ messages.deleted_by ajouté"
else echo "  messages.deleted_by déjà présent"; fi

# ─── Point 5 : Reply ─────────────────────────────────────────────────────────
if ! col_exists messages reply_to_id; then
  q "ALTER TABLE messages ADD COLUMN reply_to_id BIGINT UNSIGNED NULL DEFAULT NULL"
  echo "✅ messages.reply_to_id ajouté"
else echo "  messages.reply_to_id déjà présent"; fi

if ! constraint_exists messages fk_messages_reply_to; then
  q "ALTER TABLE messages ADD CONSTRAINT fk_messages_reply_to FOREIGN KEY (reply_to_id) REFERENCES messages(id) ON DELETE SET NULL"
  echo "✅ FK fk_messages_reply_to ajoutée"
else echo "  FK fk_messages_reply_to déjà présente"; fi

# ─── Point 7 : Index messages ─────────────────────────────────────────────────
if ! idx_exists messages idx_messages_conv_id; then
  q "CREATE INDEX idx_messages_conv_id ON messages (conversation_id, id)"
  echo "✅ idx_messages_conv_id créé"
else echo "  idx_messages_conv_id déjà présent"; fi

if ! idx_exists messages idx_messages_deleted; then
  q "CREATE INDEX idx_messages_deleted ON messages (conversation_id, deleted_at)"
  echo "✅ idx_messages_deleted créé"
else echo "  idx_messages_deleted déjà présent"; fi

if ! idx_exists messages idx_messages_sender_read; then
  q "CREATE INDEX idx_messages_sender_read ON messages (sender_id, read_at)"
  echo "✅ idx_messages_sender_read créé"
else echo "  idx_messages_sender_read déjà présent"; fi

# ─── Point 8 : Fulltext ──────────────────────────────────────────────────────
if ! idx_exists messages ft_messages_content; then
  q "ALTER TABLE messages ADD FULLTEXT INDEX ft_messages_content (content)"
  echo "✅ ft_messages_content créé"
else echo "  ft_messages_content déjà présent"; fi

# ─── Point 7 : Index conversations ───────────────────────────────────────────
if ! idx_exists conversations idx_conversations_user1_last; then
  q "CREATE INDEX idx_conversations_user1_last ON conversations (user1_id, last_message_at)"
  echo "✅ idx_conversations_user1_last créé"
else echo "  idx_conversations_user1_last déjà présent"; fi

if ! idx_exists conversations idx_conversations_user2_last; then
  q "CREATE INDEX idx_conversations_user2_last ON conversations (user2_id, last_message_at)"
  echo "✅ idx_conversations_user2_last créé"
else echo "  idx_conversations_user2_last déjà présent"; fi

# ─── Point 3 : message_reports ────────────────────────────────────────────────
q "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"
echo "✅ message_reports OK"

# ─── Point 4 : message_reactions ──────────────────────────────────────────────
q "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"
echo "✅ message_reactions OK"

# ─── Points 6 + 10 : conversation_settings ────────────────────────────────────
q "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"
echo "✅ conversation_settings OK"

# ─── Point 9 : user_blocks ────────────────────────────────────────────────────
q "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"
echo "✅ user_blocks OK"

echo ""
echo "=== Migration 005 appliquée avec succès ==="