-- 013 (L27-L28): comunidad (preguntas, respuestas, experiencias) y mensajería con gate
CREATE TABLE IF NOT EXISTS com_preguntas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  articulacion ENUM('cadera','rodilla','hombro','columna','tobillo','general') NOT NULL DEFAULT 'general',
  titulo VARCHAR(200) NOT NULL,
  cuerpo VARCHAR(2000) NULL,
  estado ENUM('pendiente','publicada','rechazada') NOT NULL DEFAULT 'pendiente',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_cp (estado, articulacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS com_respuestas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pregunta_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  cuerpo VARCHAR(2000) NOT NULL,
  es_profesional TINYINT(1) NOT NULL DEFAULT 0,
  estado ENUM('pendiente','publicada','rechazada') NOT NULL DEFAULT 'pendiente',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_cr (pregunta_id, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS experiencias (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  articulacion ENUM('cadera','rodilla','hombro','columna','tobillo') NOT NULL,
  titulo VARCHAR(200) NOT NULL,
  cuerpo VARCHAR(4000) NOT NULL,
  meses_postop SMALLINT NULL,
  verificada_por_episodio TINYINT(1) NOT NULL DEFAULT 0,
  estado ENUM('pendiente','publicada','rechazada') NOT NULL DEFAULT 'pendiente',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ex (estado, articulacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS msg_hilos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  paciente_id BIGINT UNSIGNED NOT NULL,
  cirujano_id BIGINT UNSIGNED NOT NULL,
  abierto TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_hilo (paciente_id, cirujano_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS msg_mensajes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  hilo_id BIGINT UNSIGNED NOT NULL,
  emisor_id BIGINT UNSIGNED NOT NULL,
  cuerpo VARCHAR(2000) NOT NULL,
  leido_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_msg (hilo_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;