import type { RowDataPacket } from 'mysql2'
import { db, DB } from './db'
import { localizeForum } from './forum-i18n'

// Foro estilo FusionCMS (ver sql/forum_fusion.sql). Esquema:
//   forum_categories(id, order, name)
//   forum_forums(id, category, order, name, description, icon, colortitle, type)
//   forum_topics(id, forum, name, sticky, locked, deleted, created)
//   forum_posts(id, topic, poster, text, time, deleted)
// `poster` es el AccountID de acore_auth (entero); el nombre se resuelve aparte
// con resolvePosters(). Los temas no guardan autor: se deriva del primer/último post.

// Tipos de foro (columna `type`):
//   0 = fila normal (icono de forum_icons)
//   1 = disciplina de clase (icon = clase CSS, colortitle = color)
//   2 = subforo por idioma (icon = ruta a una bandera)
export interface ForumRow {
  id: number
  category: number
  order: number
  name: string
  description: string
  icon: string | null
  colortitle: string | null
  type: number
  topic_count: number
  post_count: number
  last_topic_id: number | null
  last_topic_name: string | null
  last_poster: number | null
  last_time: string | null
}

export interface Category {
  id: number
  name: string
  forums: ForumRow[]
}

export interface TopicRow {
  id: number
  name: string
  sticky: boolean
  locked: boolean
  deleted: boolean
  created: string | null
  poster: number | null
  last_poster: number | null
  time_updated: string | null
}

export interface PostRow {
  id: number
  poster: number
  text: string
  time: string | null
  deleted: boolean
}

export interface ForumMeta {
  id: number
  name: string
  description: string
  icon: string | null
  colortitle: string | null
  type: number
}

export interface ForumPath {
  category_id: number
  category: string
  forum_id: number
  forum: string
  forum_description: string
}

export interface TopicPath extends ForumPath {
  topic_id: number
  topic: string
}

export interface PosterInfo {
  id: number
  name: string
  gmlevel: number
  isStaff: boolean
  joindate: string | null
  postCount: number
  avatar: string
  charName: string | null
  race: number
  gender: number
  classId: number
  level: number
  guildName: string | null
  achPoints: number
}

// Imagen de raza (CDN wowhead) según raza+género del personaje elegido.
const FORUM_RACE_SLUG: Record<number, string> = {
  1: 'human', 2: 'orc', 3: 'dwarf', 4: 'nightelf', 5: 'scourge', 6: 'tauren',
  7: 'gnome', 8: 'troll', 9: 'goblin', 10: 'bloodelf', 11: 'draenei', 22: 'worgen',
}
function raceAvatar(race: number, gender: number): string {
  const slug = FORUM_RACE_SLUG[race] || 'human'
  return `https://wow.zamimg.com/images/wow/icons/large/race_${slug}_${gender === 1 ? 'female' : 'male'}.jpg`
}

// Avatar por clase (FusionCMS) del personaje de más nivel de la cuenta. Solo clases
// jugables en WotLK 3.4.3 (1..9 y 11). Sin personaje → guerrero por defecto.
const AVATAR_DIR = '/forum/avatars'
const AVATAR_CLASSES = new Set([1, 2, 3, 4, 5, 6, 7, 8, 9, 11])
function avatarForClass(classId: number | undefined): string {
  const c = classId && AVATAR_CLASSES.has(classId) ? classId : 1
  return `${AVATAR_DIR}/class-${c}.gif`
}

/** Nivel GM mínimo para considerarse «staff» en el foro (badge y color). */
const STAFF_GMLEVEL = Number(process.env.FORUM_MOD_GMLEVEL || 2)

/** Índice del foro: categorías ordenadas, cada una con sus foros y agregados. */
export async function getForumIndex(locale = 'es'): Promise<Category[]> {
  try {
    const [cats] = await db(DB.web).query<RowDataPacket[]>(
      'SELECT id, name FROM forum_categories ORDER BY `order`, name',
    )
    const [forums] = await db(DB.web).query<RowDataPacket[]>(
      `SELECT f.id, f.category, f.\`order\`, f.name, f.description, f.icon, f.colortitle, f.type,
              (SELECT COUNT(*) FROM forum_topics t WHERE t.forum = f.id AND t.deleted = 0) AS topic_count,
              (SELECT COUNT(*) FROM forum_posts p JOIN forum_topics t ON p.topic = t.id
                 WHERE t.forum = f.id AND p.deleted = 0 AND t.deleted = 0) AS post_count,
              (SELECT t.id FROM forum_posts p JOIN forum_topics t ON p.topic = t.id
                 WHERE t.forum = f.id AND p.deleted = 0 AND t.deleted = 0
                 ORDER BY p.time DESC, p.id DESC LIMIT 1) AS last_topic_id,
              (SELECT t.name FROM forum_posts p JOIN forum_topics t ON p.topic = t.id
                 WHERE t.forum = f.id AND p.deleted = 0 AND t.deleted = 0
                 ORDER BY p.time DESC, p.id DESC LIMIT 1) AS last_topic_name,
              (SELECT p.poster FROM forum_posts p JOIN forum_topics t ON p.topic = t.id
                 WHERE t.forum = f.id AND p.deleted = 0 AND t.deleted = 0
                 ORDER BY p.time DESC, p.id DESC LIMIT 1) AS last_poster,
              (SELECT p.time FROM forum_posts p JOIN forum_topics t ON p.topic = t.id
                 WHERE t.forum = f.id AND p.deleted = 0 AND t.deleted = 0
                 ORDER BY p.time DESC, p.id DESC LIMIT 1) AS last_time
       FROM forum_forums f
       ORDER BY f.\`order\`, f.id`,
    )
    const byCat = new Map<number, ForumRow[]>()
    for (const f of forums) {
      const row: ForumRow = {
        id: f.id,
        category: f.category,
        order: f.order,
        name: localizeForum(f.name, locale),
        description: localizeForum(f.description, locale),
        icon: f.icon,
        colortitle: f.colortitle,
        type: f.type,
        topic_count: Number(f.topic_count ?? 0),
        post_count: Number(f.post_count ?? 0),
        last_topic_id: f.last_topic_id ?? null,
        last_topic_name: f.last_topic_name ?? null,
        last_poster: f.last_poster ?? null,
        last_time: f.last_time ? new Date(f.last_time).toISOString() : null,
      }
      if (!byCat.has(row.category)) byCat.set(row.category, [])
      byCat.get(row.category)!.push(row)
    }
    return cats
      .map((c) => ({ id: c.id, name: localizeForum(c.name, locale), forums: byCat.get(c.id) ?? [] }))
      .filter((c) => c.forums.length > 0)
  } catch {
    return []
  }
}

export async function getForum(id: number, locale = 'es'): Promise<ForumMeta | null> {
  try {
    const [rows] = await db(DB.web).query<RowDataPacket[]>(
      'SELECT id, name, description, icon, colortitle, type FROM forum_forums WHERE id = ?',
      [id],
    )
    const r = rows[0]
    return r
      ? {
          id: r.id,
          name: localizeForum(r.name, locale),
          description: localizeForum(r.description, locale),
          icon: r.icon,
          colortitle: r.colortitle,
          type: r.type,
        }
      : null
  } catch {
    return null
  }
}

export async function forumExists(id: number): Promise<boolean> {
  if (!id) return false
  try {
    const [rows] = await db(DB.web).query<RowDataPacket[]>('SELECT id FROM forum_forums WHERE id = ?', [id])
    return Boolean(rows[0])
  } catch {
    return false
  }
}

export async function getForumPath(forumId: number, locale = 'es'): Promise<ForumPath | null> {
  try {
    const [rows] = await db(DB.web).query<RowDataPacket[]>(
      `SELECT c.id AS category_id, c.name AS category, f.id AS forum_id, f.name AS forum, f.description AS forum_description
       FROM forum_categories c JOIN forum_forums f ON f.category = c.id
       WHERE f.id = ? LIMIT 1`,
      [forumId],
    )
    const r = rows[0]
    return r
      ? {
          category_id: r.category_id,
          category: localizeForum(r.category, locale),
          forum_id: r.forum_id,
          forum: localizeForum(r.forum, locale),
          forum_description: localizeForum(r.forum_description, locale),
        }
      : null
  } catch {
    return null
  }
}

export async function getTopicPath(topicId: number, locale = 'es'): Promise<TopicPath | null> {
  try {
    const [rows] = await db(DB.web).query<RowDataPacket[]>(
      `SELECT c.id AS category_id, c.name AS category, f.id AS forum_id, f.name AS forum,
              f.description AS forum_description, t.id AS topic_id, t.name AS topic
       FROM forum_categories c
       JOIN forum_forums f ON f.category = c.id
       JOIN forum_topics t ON t.forum = f.id
       WHERE t.id = ? LIMIT 1`,
      [topicId],
    )
    const r = rows[0]
    return r
      ? {
          category_id: r.category_id,
          category: localizeForum(r.category, locale),
          forum_id: r.forum_id,
          forum: localizeForum(r.forum, locale),
          forum_description: localizeForum(r.forum_description, locale),
          topic_id: r.topic_id,
          topic: r.topic,
        }
      : null
  } catch {
    return null
  }
}

export async function countTopics(forumId: number): Promise<number> {
  try {
    const [rows] = await db(DB.web).query<RowDataPacket[]>(
      'SELECT COUNT(*) AS n FROM forum_topics WHERE forum = ? AND deleted = 0',
      [forumId],
    )
    return Number(rows[0]?.n ?? 0)
  } catch {
    return 0
  }
}

/** Temas de un foro. Autor = primer post; última actividad = último post no borrado. */
export async function getTopics(
  forumId: number,
  offset = 0,
  limit = 10,
  includeDeleted = false,
): Promise<TopicRow[]> {
  try {
    const del = includeDeleted ? '' : 'AND t.deleted = 0'
    const [rows] = await db(DB.web).query<RowDataPacket[]>(
      `SELECT t.id, t.name, t.sticky, t.locked, t.deleted, t.created,
              (SELECT p.poster FROM forum_posts p WHERE p.topic = t.id ORDER BY p.time ASC, p.id ASC LIMIT 1) AS poster,
              (SELECT p.poster FROM forum_posts p WHERE p.topic = t.id AND p.deleted = 0 ORDER BY p.time DESC, p.id DESC LIMIT 1) AS last_poster,
              (SELECT p.time FROM forum_posts p WHERE p.topic = t.id AND p.deleted = 0 ORDER BY p.time DESC, p.id DESC LIMIT 1) AS time_updated
       FROM forum_topics t
       WHERE t.forum = ? ${del}
       ORDER BY t.sticky DESC, COALESCE((SELECT MAX(p.time) FROM forum_posts p WHERE p.topic = t.id AND p.deleted = 0), t.created) DESC, t.id DESC
       LIMIT ? OFFSET ?`,
      [forumId, limit, offset],
    )
    return rows.map((r) => ({
      id: r.id,
      name: r.name,
      sticky: r.sticky === 1,
      locked: r.locked === 1,
      deleted: r.deleted === 1,
      created: r.created ? new Date(r.created).toISOString() : null,
      poster: r.poster ?? null,
      last_poster: r.last_poster ?? null,
      time_updated: r.time_updated ? new Date(r.time_updated).toISOString() : null,
    }))
  } catch {
    return []
  }
}

export async function getTopic(id: number, includeDeleted = false): Promise<{
  id: number
  forum: number
  name: string
  sticky: boolean
  locked: boolean
  deleted: boolean
  created: string | null
} | null> {
  try {
    const where = includeDeleted ? 'id = ?' : 'id = ? AND deleted = 0'
    const [rows] = await db(DB.web).query<RowDataPacket[]>(
      `SELECT id, forum, name, sticky, locked, deleted, created FROM forum_topics WHERE ${where}`,
      [id],
    )
    const r = rows[0]
    return r
      ? {
          id: r.id,
          forum: r.forum,
          name: r.name,
          sticky: r.sticky === 1,
          locked: r.locked === 1,
          deleted: r.deleted === 1,
          created: r.created ? new Date(r.created).toISOString() : null,
        }
      : null
  } catch {
    return null
  }
}

export async function countPosts(topicId: number, includeDeleted = false): Promise<number> {
  try {
    const del = includeDeleted ? '' : 'AND deleted = 0'
    const [rows] = await db(DB.web).query<RowDataPacket[]>(
      `SELECT COUNT(*) AS n FROM forum_posts WHERE topic = ? ${del}`,
      [topicId],
    )
    return Number(rows[0]?.n ?? 0)
  } catch {
    return 0
  }
}

export async function getPosts(
  topicId: number,
  offset = 0,
  limit = 5,
  includeDeleted = false,
): Promise<PostRow[]> {
  try {
    const del = includeDeleted ? '' : 'AND deleted = 0'
    const [rows] = await db(DB.web).query<RowDataPacket[]>(
      `SELECT id, poster, text, time, deleted FROM forum_posts WHERE topic = ? ${del} ORDER BY time ASC, id ASC LIMIT ? OFFSET ?`,
      [topicId, limit, offset],
    )
    return rows.map((r) => ({
      id: r.id,
      poster: r.poster,
      text: r.text,
      time: r.time ? new Date(r.time).toISOString() : null,
      deleted: r.deleted === 1,
    }))
  } catch {
    return []
  }
}

/** Nº de posts (no borrados) de un usuario, para el «Posts: N» del perfil lateral. */
export async function getUserPostCount(posterId: number): Promise<number> {
  try {
    const [rows] = await db(DB.web).query<RowDataPacket[]>(
      'SELECT COUNT(*) AS n FROM forum_posts WHERE poster = ? AND deleted = 0',
      [posterId],
    )
    return Number(rows[0]?.n ?? 0)
  } catch {
    return 0
  }
}

/**
 * Resuelve un conjunto de AccountID a nombre + rango + antigüedad en una sola pasada.
 * El nombre es el username de la cuenta de juego (acore_auth.account), sin el sufijo
 * «#N» de Battle.net para que se lea mejor. Devuelve un Map por id.
 */
export async function resolvePosters(ids: number[]): Promise<Map<number, PosterInfo>> {
  const map = new Map<number, PosterInfo>()
  const unique = [...new Set(ids.filter((n) => Number.isFinite(n) && n > 0))]
  if (unique.length === 0) return map

  const placeholders = unique.map(() => '?').join(',')

  // Clase del personaje de más nivel de cada cuenta (para el avatar). Se tolera que
  // acore_characters no exista o esté vacía: el avatar cae a guerrero por defecto.
  // Personaje de MÁS NIVEL por cuenta (nombre, raza, género, clase, nivel).
  const charByAccount = new Map<number, { guid: number; name: string; race: number; gender: number; class: number; level: number }>()
  try {
    const [chars] = await db(DB.characters).query<RowDataPacket[]>(
      `SELECT account, guid, name, race, gender, class, level FROM characters WHERE account IN (${placeholders})`,
      unique,
    )
    const byAcc = new Map<number, RowDataPacket[]>()
    for (const c of chars) {
      const acc = Number(c.account)
      if (!byAcc.has(acc)) byAcc.set(acc, [])
      byAcc.get(acc)!.push(c)
    }
    // Personaje fijado por el usuario (si lo hay y sigue siendo suyo); si no, el de más nivel.
    const pickByAcc = new Map<number, number>()
    try {
      const [picks] = await db(DB.web).query<RowDataPacket[]>(
        `SELECT account, guid FROM forum_character WHERE account IN (${placeholders})`,
        unique,
      )
      for (const p of picks) pickByAcc.set(Number(p.account), Number(p.guid))
    } catch {
      /* sin tabla de preferencias */
    }
    for (const [acc, list] of byAcc) {
      const pg = pickByAcc.get(acc)
      const picked = pg ? list.find((x) => Number(x.guid) === pg) : undefined
      const c = picked || list.reduce((best, cur) => (Number(cur.level) > Number(best.level) ? cur : best), list[0])
      charByAccount.set(acc, {
        guid: Number(c.guid), name: String(c.name), race: Number(c.race),
        gender: Number(c.gender), class: Number(c.class), level: Number(c.level),
      })
    }
  } catch {
    /* sin personajes: avatar por defecto */
  }

  // Hermandad y puntos de logros de los personajes elegidos.
  const chosenGuids = [...charByAccount.values()].map((c) => c.guid)
  const guildByGuid = new Map<number, string>()
  const ptsByGuid = new Map<number, number>()
  if (chosenGuids.length) {
    const gp = chosenGuids.map(() => '?').join(',')
    try {
      const [gr] = await db(DB.characters).query<RowDataPacket[]>(
        `SELECT gm.guid, g.name FROM guild_member gm JOIN guild g ON g.guildid = gm.guildid WHERE gm.guid IN (${gp})`,
        chosenGuids,
      )
      for (const r of gr) guildByGuid.set(Number(r.guid), String(r.name))
    } catch { /* */ }
    try {
      const [pr] = await db(DB.characters).query<RowDataPacket[]>(
        `SELECT ca.guid, COALESCE(SUM(ap.points),0) AS pts
         FROM character_achievement ca JOIN acore_world.achievement_points ap ON ap.id = ca.achievement
         WHERE ca.guid IN (${gp}) GROUP BY ca.guid`,
        chosenGuids,
      )
      for (const r of pr) ptsByGuid.set(Number(r.guid), Number(r.pts))
    } catch { /* */ }
  }

  const buildPoster = (id: number, base: Partial<PosterInfo>): PosterInfo => {
    const ch = charByAccount.get(id)
    return {
      id,
      name: base.name ?? `#${id}`,
      gmlevel: base.gmlevel ?? 0,
      isStaff: base.isStaff ?? false,
      joindate: base.joindate ?? null,
      postCount: 0,
      avatar: ch ? raceAvatar(ch.race, ch.gender) : avatarForClass(undefined),
      charName: ch?.name ?? null,
      race: ch?.race ?? 0,
      gender: ch?.gender ?? 0,
      classId: ch?.class ?? 0,
      level: ch?.level ?? 0,
      guildName: ch ? (guildByGuid.get(ch.guid) ?? null) : null,
      achPoints: ch ? (ptsByGuid.get(ch.guid) ?? 0) : 0,
    }
  }

  try {
    const [accs] = await db(DB.auth).query<RowDataPacket[]>(
      `SELECT id, username, joindate FROM account WHERE id IN (${placeholders})`,
      unique,
    )
    const [access] = await db(DB.auth).query<RowDataPacket[]>(
      `SELECT AccountID, MAX(SecurityLevel) AS lvl FROM account_access WHERE AccountID IN (${placeholders}) GROUP BY AccountID`,
      unique,
    )
    const gm = new Map<number, number>()
    for (const a of access) gm.set(Number(a.AccountID), Number(a.lvl ?? 0))

    for (const a of accs) {
      const gmlevel = gm.get(Number(a.id)) ?? 0
      map.set(Number(a.id), buildPoster(Number(a.id), {
        name: String(a.username || '').replace(/#\d+$/, '') || `#${a.id}`,
        gmlevel,
        isStaff: gmlevel >= STAFF_GMLEVEL,
        joindate: a.joindate ? new Date(a.joindate).toISOString() : null,
      }))
    }
  } catch {
    /* acore_auth no disponible: se devuelven placeholders abajo */
  }
  // Cualquier id no resuelto (cuenta borrada) recibe un placeholder.
  for (const id of unique) {
    if (!map.has(id)) {
      map.set(id, buildPoster(id, {}))
    }
  }
  return map
}

export interface ForumCharOption {
  guid: number
  name: string
  race: number
  gender: number
  class: number
  level: number
}

/** Personajes de la cuenta para el selector del foro. */
export async function getAccountCharactersForForum(accountId: number): Promise<ForumCharOption[]> {
  if (!accountId) return []
  try {
    const [rows] = await db(DB.characters).query<RowDataPacket[]>(
      'SELECT guid, name, race, gender, class, level FROM characters WHERE account = ? ORDER BY level DESC, name ASC',
      [accountId],
    )
    return rows.map((r) => ({
      guid: Number(r.guid), name: String(r.name), race: Number(r.race),
      gender: Number(r.gender), class: Number(r.class), level: Number(r.level),
    }))
  } catch {
    return []
  }
}

/** GUID del personaje fijado por la cuenta para el foro (validado), o null. */
export async function getForumCharacterPick(accountId: number): Promise<number | null> {
  if (!accountId) return null
  try {
    const [rows] = await db(DB.web).query<RowDataPacket[]>(
      'SELECT guid FROM forum_character WHERE account = ?',
      [accountId],
    )
    const guid = rows[0] ? Number(rows[0].guid) : 0
    if (!guid) return null
    const [ok] = await db(DB.characters).query<RowDataPacket[]>(
      'SELECT 1 FROM characters WHERE guid = ? AND account = ? LIMIT 1',
      [guid, accountId],
    )
    return ok.length ? guid : null
  } catch {
    return null
  }
}

/** Fija el personaje del foro para la cuenta (valida que le pertenece). */
export async function setForumCharacterPick(accountId: number, guid: number): Promise<boolean> {
  if (!accountId || !guid) return false
  try {
    const [ok] = await db(DB.characters).query<RowDataPacket[]>(
      'SELECT 1 FROM characters WHERE guid = ? AND account = ? LIMIT 1',
      [guid, accountId],
    )
    if (!ok.length) return false
    await db(DB.web).query(
      'INSERT INTO forum_character (account, guid) VALUES (?, ?) ON DUPLICATE KEY UPDATE guid = VALUES(guid)',
      [accountId, guid],
    )
    return true
  } catch {
    return false
  }
}