/**
 * ============================================
 * 🗄️ db.js — Cliente Turso + Operaciones DB
 * ============================================
 */

import { createClient } from '@libsql/client';
import { readConfig } from './utils.js';

const config = readConfig();

export const db = createClient({
  url      : config.turso.url,
  authToken: config.turso.token,
});

// Desactivar foreign keys para evitar constraint errors en inserts rápidos
db.execute('PRAGMA foreign_keys = OFF').catch(() => {});

// ── Migraciones automáticas ───────────────────────────────────────────────────
// Se ejecutan al arrancar. Usan try/catch para ser idempotentes.
async function runMigrations() {
  const migrations = [
    // v1.5: columna username en conversation_context
    `ALTER TABLE conversation_context ADD COLUMN username TEXT`,
    // v1.5: tabla de mensajes eliminados para auditoría
    `CREATE TABLE IF NOT EXISTS deleted_messages (
      id           INTEGER PRIMARY KEY AUTOINCREMENT,
      message_id   TEXT NOT NULL,
      channel_id   TEXT NOT NULL,
      channel_name TEXT,
      user_id      TEXT NOT NULL,
      username     TEXT,
      content      TEXT,
      had_attachments INTEGER DEFAULT 0,
      deleted_at   TEXT NOT NULL
    )`,
  ];
  for (const sql of migrations) {
    try {
      await db.execute({ sql, args: [] });
    } catch {
      // Ignorar: columna ya existe, tabla ya existe, etc.
    }
  }
}
// Ejecutar migraciones al importar el módulo
runMigrations().catch(err => console.warn('[DB] Migration warning:', err.message));

// ── Guardar mensaje eliminado ─────────────────────────────────────────────────
export async function saveDeletedMessage({ messageId, channelId, channelName, userId, username, content, hadAttachments }) {
  await run(`
    INSERT OR IGNORE INTO deleted_messages
      (message_id, channel_id, channel_name, user_id, username, content, had_attachments, deleted_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `, [messageId, channelId, channelName ?? null, userId, username ?? null, content ?? null, hadAttachments ? 1 : 0, new Date().toISOString()]);
}

// ── Obtener mensajes eliminados recientes ─────────────────────────────────────
export async function getDeletedMessages({ channelId, userId, limit = 20 } = {}) {
  let sql = `SELECT * FROM deleted_messages`;
  const args = [];
  const where = [];
  if (channelId) { where.push(`channel_id = ?`); args.push(channelId); }
  if (userId)    { where.push(`user_id = ?`);    args.push(userId); }
  if (where.length) sql += ` WHERE ${where.join(' AND ')}`;
  sql += ` ORDER BY deleted_at DESC LIMIT ?`;
  args.push(limit);
  const r = await run(sql, args);
  return r.rows;
}


// ── Helpers ─────────────────────────────────────────────────────────────────

function now() { return new Date().toISOString(); }

async function run(sql, args = []) {
  try {
    return await db.execute({ sql, args });
  } catch (err) {
    console.error('[DB] Error:', err.message, '|', sql.substring(0, 80));
    throw err;
  }
}

// ── USUARIOS ─────────────────────────────────────────────────────────────────

export async function upsertUser(member) {
  const u = member.user ?? member;
  await run(`
    INSERT INTO users (user_id, username, global_name, nickname, avatar_url, bot,
      joined_at, account_created, last_seen, is_active, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 1, ?)
    ON CONFLICT(user_id) DO UPDATE SET
      username = excluded.username,
      global_name = excluded.global_name,
      nickname = excluded.nickname,
      avatar_url = excluded.avatar_url,
      last_seen = excluded.last_seen,
      is_active = 1,
      updated_at = excluded.updated_at
  `, [
    u.id,
    u.username,
    u.globalName ?? null,
    member.nickname ?? null,
    u.displayAvatarURL?.({ size: 128 }) ?? null,
    u.bot ? 1 : 0,
    member.joinedAt?.toISOString() ?? null,
    u.createdAt?.toISOString() ?? null,
    now(),
    now(),
  ]);
}

export async function getUser(userId) {
  const r = await run(`SELECT * FROM users WHERE user_id = ?`, [userId]);
  return r.rows[0] ?? null;
}

export async function getUserWithRoles(userId) {
  const user = await getUser(userId);
  if (!user) return null;
  const rolesR = await run(`
    SELECT r.name FROM user_roles ur
    JOIN roles r ON ur.role_id = r.role_id
    WHERE ur.user_id = ?
    ORDER BY r.position DESC
  `, [userId]);
  return { ...user, roles: rolesR.rows.map(r => r.name) };
}

export async function markUserLeft(userId) {
  await run(`UPDATE users SET is_active = 0, left_at = ?, updated_at = ? WHERE user_id = ?`,
    [now(), now(), userId]);
}

export async function updateUserMessageCount(userId) {
  await run(`UPDATE users SET message_count = message_count + 1, last_seen = ?, updated_at = ? WHERE user_id = ?`,
    [now(), now(), userId]);
}

export async function updateUserNotes(userId, notes) {
  await run(`UPDATE users SET notes = ?, updated_at = ? WHERE user_id = ?`,
    [notes, now(), userId]);
}

// ── ROLES ────────────────────────────────────────────────────────────────────

export async function upsertRole(role) {
  await run(`
    INSERT INTO roles (role_id, name, color, position, hoist, managed, mentionable,
      permissions, is_bot_role, is_boost_role, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(role_id) DO UPDATE SET
      name = excluded.name, color = excluded.color,
      position = excluded.position, updated_at = excluded.updated_at
  `, [
    role.id, role.name, role.hexColor, role.position,
    role.hoist ? 1 : 0, role.managed ? 1 : 0, role.mentionable ? 1 : 0,
    JSON.stringify(role.permissions?.toArray() ?? []),
    role.tags?.botId ? 1 : 0,
    role.tags?.premiumSubscriberRole ? 1 : 0,
    now(),
  ]);
}

export async function syncUserRoles(userId, roleIds) {
  await run(`DELETE FROM user_roles WHERE user_id = ?`, [userId]);
  for (const roleId of roleIds) {
    await run(`INSERT OR IGNORE INTO user_roles (user_id, role_id) VALUES (?, ?)`,
      [userId, roleId]);
  }
}

// ── CANALES ───────────────────────────────────────────────────────────────────

export async function upsertChannel(channel) {
  const typeMap = {
    0: 'GUILD_TEXT', 2: 'GUILD_VOICE', 4: 'GUILD_CATEGORY',
    5: 'GUILD_ANNOUNCEMENT', 13: 'GUILD_STAGE', 15: 'GUILD_FORUM',
  };
  await run(`
    INSERT INTO channels (channel_id, name, type, category_id, position, topic, nsfw, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(channel_id) DO UPDATE SET
      name = excluded.name, position = excluded.position,
      topic = excluded.topic, updated_at = excluded.updated_at
  `, [
    channel.id, channel.name,
    typeMap[channel.type] ?? String(channel.type),
    channel.parentId ?? null,
    channel.rawPosition ?? 0,
    channel.topic ?? null,
    channel.nsfw ? 1 : 0,
    now(),
  ]);
}

// ── MENSAJES ──────────────────────────────────────────────────────────────────

export async function saveMessage(message) {
  // Asegurar que el usuario y canal existen antes de guardar el mensaje
  try {
    const u = message.author;
    await run(`
      INSERT OR IGNORE INTO users (user_id, username, global_name, avatar_url, bot, last_seen, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `, [u.id, u.username, u.globalName ?? null, u.displayAvatarURL?.({ size: 128 }) ?? null,
        u.bot ? 1 : 0, now(), now(), now()]);

    await run(`
      INSERT OR IGNORE INTO channels (channel_id, name, type, updated_at)
      VALUES (?, ?, ?, ?)
    `, [message.channelId, message.channel?.name ?? 'unknown', 'GUILD_TEXT', now()]);
  } catch {}

  await run(`
    INSERT OR IGNORE INTO messages
      (message_id, channel_id, user_id, content, has_attachment, has_embed,
       reply_to_id, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `, [
    message.id,
    message.channelId,
    message.author.id,
    message.content ?? '',
    message.attachments.size > 0 ? 1 : 0,
    message.embeds.length > 0 ? 1 : 0,
    message.reference?.messageId ?? null,
    message.createdAt.toISOString(),
  ]);
  await updateUserMessageCount(message.author.id);
}

export async function markMessageEdited(messageId, newContent) {
  await run(`UPDATE messages SET is_edited = 1, edit_content = ? WHERE message_id = ?`,
    [newContent, messageId]);
}

export async function markMessageDeleted(messageId) {
  await run(`UPDATE messages SET is_deleted = 1, deleted_at = ? WHERE message_id = ?`,
    [now(), messageId]);
}

export async function getRecentMessages(channelId, limit = 50) {
  const r = await run(`
    SELECT m.*, u.username, u.nickname FROM messages m
    LEFT JOIN users u ON m.user_id = u.user_id
    WHERE m.channel_id = ? AND m.is_deleted = 0
    ORDER BY m.created_at DESC LIMIT ?
  `, [channelId, limit]);
  return r.rows.reverse();
}

export async function getUserMessages(userId, limit = 40) {
  const r = await run(`
    SELECT m.content FROM messages m
    WHERE m.user_id = ? AND m.is_deleted = 0 AND length(m.content) > 5
    ORDER BY m.created_at DESC LIMIT ?
  `, [userId, limit]);
  return r.rows.map(r => r.content);
}

export async function searchMessages(query, limit = 20) {
  const r = await run(`
    SELECT m.*, u.username FROM messages m
    LEFT JOIN users u ON m.user_id = u.user_id
    WHERE m.content LIKE ? AND m.is_deleted = 0
    ORDER BY m.created_at DESC LIMIT ?
  `, [`%${query}%`, limit]);
  return r.rows;
}

// ── MODERACIÓN ────────────────────────────────────────────────────────────────

export async function logModAction({
  action, targetUserId, moderatorId = 'zelin',
  channelId = null, messageId = null,
  reason, durationMs = null, evidence = null,
  ruleViolated = null, auto = true,
}) {
  await run(`
    INSERT INTO moderation_logs
      (action, target_user_id, moderator_id, channel_id, message_id,
       reason, duration_ms, evidence, rule_violated, auto)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `, [action, targetUserId, moderatorId, channelId, messageId,
      reason, durationMs, evidence, ruleViolated, auto ? 1 : 0]);
}

export async function getUserModHistory(userId, limit = 10) {
  const r = await run(`
    SELECT * FROM moderation_logs
    WHERE target_user_id = ?
    ORDER BY created_at DESC LIMIT ?
  `, [userId, limit]);
  return r.rows;
}

// ── EVENTOS DEL SERVIDOR ──────────────────────────────────────────────────────

export async function logServerEvent(type, userId = null, targetId = null, oldVal = null, newVal = null, meta = null) {
  await run(`
    INSERT INTO server_events (type, user_id, target_id, old_value, new_value, metadata)
    VALUES (?, ?, ?, ?, ?, ?)
  `, [type, userId, targetId, oldVal, newVal, meta ? JSON.stringify(meta) : null]);
}

// ── MEMORIA DE ZELIN ──────────────────────────────────────────────────────────

export async function memGet(key) {
  const r = await run(`SELECT value FROM zelin_memory WHERE key = ?`, [key]);
  if (!r.rows[0]) return null;
  try { return JSON.parse(r.rows[0].value); } catch { return r.rows[0].value; }
}

export async function memSet(key, value, category = 'general', expiresAt = null) {
  const val = typeof value === 'string' ? value : JSON.stringify(value);
  await run(`
    INSERT INTO zelin_memory (key, value, category, expires_at, updated_at)
    VALUES (?, ?, ?, ?, ?)
    ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at
  `, [key, val, category, expiresAt, now()]);
}

export async function memGetCategory(category) {
  const r = await run(`SELECT key, value FROM zelin_memory WHERE category = ?`, [category]);
  const out = {};
  for (const row of r.rows) {
    try { out[row.key] = JSON.parse(row.value); } catch { out[row.key] = row.value; }
  }
  return out;
}

// ── CONTEXTO DE CONVERSACIÓN ───────────────────────────────────────────────────

export async function addContext(channelId, role, content, userId = null, username = null) {
  // username es opcional — si la columna no existe el INSERT sigue funcionando
  try {
    await run(`
      INSERT INTO conversation_context (channel_id, role, content, user_id, username)
      VALUES (?, ?, ?, ?, ?)
    `, [channelId, role, content.substring(0, 2000), userId, username]);
  } catch {
    // Fallback sin username si la columna no existe aún
    await run(`
      INSERT INTO conversation_context (channel_id, role, content, user_id)
      VALUES (?, ?, ?, ?)
    `, [channelId, role, content.substring(0, 2000), userId]);
  }

  // Mantener solo los últimos N registros por canal
  await run(`
    DELETE FROM conversation_context
    WHERE channel_id = ? AND id NOT IN (
      SELECT id FROM conversation_context
      WHERE channel_id = ?
      ORDER BY created_at DESC LIMIT 100
    )
  `, [channelId, channelId]);
}

export async function getContext(channelId, limit = 20) {
  try {
    const r = await run(`
      SELECT role, content, user_id, username, created_at FROM conversation_context
      WHERE channel_id = ?
      ORDER BY created_at DESC LIMIT ?
    `, [channelId, limit]);
    return r.rows.reverse();
  } catch {
    // Fallback sin username (migración aún no aplicada en esta DB)
    const r = await run(`
      SELECT role, content, user_id, created_at FROM conversation_context
      WHERE channel_id = ?
      ORDER BY created_at DESC LIMIT ?
    `, [channelId, limit]);
    return r.rows.reverse();
  }
}

export async function getZelinUserHistory(userId, limit = 30) {
  const r = await run(`
    SELECT role, content, created_at FROM conversation_context
    WHERE user_id = ?
    ORDER BY created_at DESC LIMIT ?
  `, [userId, limit]);
  return r.rows.reverse();
}

// ── RESÚMENES ─────────────────────────────────────────────────────────────────

export async function saveSummary({ channelId, periodStart, periodEnd, messageCount, summary, topUsers, topTopics }) {
  await run(`
    INSERT INTO summaries (channel_id, period_start, period_end, message_count, summary, top_users, top_topics)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `, [channelId, periodStart, periodEnd, messageCount, summary,
      JSON.stringify(topUsers ?? []), JSON.stringify(topTopics ?? [])]);
}

export async function getRecentSummaries(limit = 5) {
  const r = await run(`SELECT * FROM summaries ORDER BY created_at DESC LIMIT ?`, [limit]);
  return r.rows;
}

// ── LIMPIEZA ──────────────────────────────────────────────────────────────────

export async function getOldMessages(days) {
  const cutoff = new Date(Date.now() - days * 86400000).toISOString();
  const r = await run(`
    SELECT channel_id, COUNT(*) as count, MIN(created_at) as start, MAX(created_at) as end
    FROM messages WHERE created_at < ? AND is_deleted = 0
    GROUP BY channel_id
  `, [cutoff]);
  return r.rows;
}

export async function deleteOldMessages(channelId, beforeDate) {
  await run(`DELETE FROM messages WHERE channel_id = ? AND created_at < ?`,
    [channelId, beforeDate]);
}

// ── ESTADÍSTICAS ──────────────────────────────────────────────────────────────

export async function getServerStats() {
  const [users, msgs, modActions, activeToday] = await Promise.all([
    run(`SELECT COUNT(*) as c FROM users WHERE is_active = 1 AND bot = 0`),
    run(`SELECT COUNT(*) as c FROM messages WHERE is_deleted = 0`),
    run(`SELECT COUNT(*) as c FROM moderation_logs`),
    run(`SELECT COUNT(DISTINCT user_id) as c FROM messages WHERE created_at > datetime('now', '-1 day')`),
  ]);
  return {
    activeUsers  : users.rows[0].c,
    totalMessages: msgs.rows[0].c,
    modActions   : modActions.rows[0].c,
    activeToday  : activeToday.rows[0].c,
  };
}

export async function getTopUsers(days = 7, limit = 10) {
  const cutoff = new Date(Date.now() - days * 86400000).toISOString();
  const r = await run(`
    SELECT u.username, u.nickname, COUNT(m.message_id) as count
    FROM messages m JOIN users u ON m.user_id = u.user_id
    WHERE m.created_at > ? AND u.bot = 0 AND m.is_deleted = 0
    GROUP BY m.user_id ORDER BY count DESC LIMIT ?
  `, [cutoff, limit]);
  return r.rows;
}

// ── Lista completa de canales para el prompt ──────────────────────────────────
export async function getAllChannels() {
  const r = await run(`
    SELECT channel_id, name, type, category_id, topic
    FROM channels
    ORDER BY position ASC, name ASC
  `);
  return r.rows;
}

// ── Limpieza de memoria expirada y datos old ─────────────────────────────────
export async function cleanExpiredMemory() {
  const nowIso = new Date().toISOString();
  // Borrar keys expiradas
  await run(`DELETE FROM zelin_memory WHERE expires_at IS NOT NULL AND expires_at < ?`, [nowIso]).catch(() => {});
  // Borrar feedback de learning con más de 60 días
  const cutoff60 = new Date(Date.now() - 60 * 86400000).toISOString();
  await run(`DELETE FROM zelin_memory WHERE category = 'learning' AND updated_at < ?`, [cutoff60]).catch(() => {});
  // Borrar audit logs con más de 30 días
  const cutoff30 = new Date(Date.now() - 30 * 86400000).toISOString();
  await run(`DELETE FROM zelin_memory WHERE category = 'audit_log' AND updated_at < ?`, [cutoff30]).catch(() => {});
  console.log('[DB] Limpieza de memoria expirada completada');
}

// ── Lista completa de usuarios activos para el prompt ─────────────────────────
export async function getAllActiveUsers() {
  const r = await run(`
    SELECT user_id, username, nickname, global_name, message_count
    FROM users
    WHERE is_active = 1 AND bot = 0
    ORDER BY message_count DESC
    LIMIT 80
  `);
  return r.rows;
}