import fs from "node:fs";
import path from "node:path";
import { Prisma } from "@prisma/client";
import { prisma } from "@/lib/prisma/app";
import { secureLogger } from "@/lib/secure-logger";

export interface ImportStats {
  processedTables: number;
  processedItems: number;
  inserted: number;
  skipped: number;
}

const PT_TO_EN_CATEGORY_MAP: Record<string, string> = {
  espadas: "weapon",
  armaduras: "armor",
  capacetes: "helmet",
  calcas: "legs",
  botas: "boots",
  escudos: "shield",
  amuletos: "amulet",
  aneis: "ring",
  runas: "rune",
  ferramentas: "tool",
  municao: "ammunition",
};

const SQLITE_EXTRA_FIELDS = [
  "voc",
  "volume",
  "bonus_de_skill",
  "protecao",
  "dropa_de",
  "npc_price",
  "dropped_by",
  "tipo",
  "classe",
  "atributos",
  "descricao",
  "descricao_curta",
  "descricao_longa",
  "description",
] as const;

const SQL_CHUNK_SIZE = 250;

let seedPromise: Promise<ImportStats | null> | null = null;
let seedLogged = false;

function loadBetterSqlite(): any | null {
  try {
    // eslint-disable-next-line @typescript-eslint/no-var-requires
    return require("better-sqlite3");
  } catch (error) {
    secureLogger.warn(
      "better-sqlite3 não está disponível. Importação automática será ignorada.",
      {
        error: error instanceof Error ? error.message : String(error),
      },
    );
    return null;
  }
}

function openSQLiteDatabase(Database: any) {
  const dbPath = path.join(process.cwd(), "data", "tibiawiki.db");
  if (!fs.existsSync(dbPath)) {
    secureLogger.warn(
      "Base SQLite não encontrada em data/tibiawiki.db. Importação ignorada.",
    );
    return null;
  }

  try {
    return new Database(dbPath, { readonly: true, fileMustExist: true });
  } catch (error) {
    secureLogger.error(
      "Falha ao abrir base SQLite",
      error instanceof Error ? error.message : error,
    );
    return null;
  }
}

function toNullableInt(value: unknown): number | null {
  if (value === null || value === undefined || value === "") return null;
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : null;
}

function toNullableDecimal(value: unknown): Prisma.Decimal | null {
  if (value === null || value === undefined || value === "") return null;
  const parsed = Number(value);
  if (!Number.isFinite(parsed)) return null;
  return new Prisma.Decimal(parsed);
}

function collectMetadata(
  row: Record<string, unknown>,
): Record<string, unknown> {
  const metadata: Record<string, unknown> = {};

  for (const field of SQLITE_EXTRA_FIELDS) {
    if (row[field] !== undefined && row[field] !== null && row[field] !== "") {
      metadata[field] = row[field];
    }
  }

  const description =
    (row.descricao as unknown as string | undefined) ??
    (row.descricao_longa as unknown as string | undefined) ??
    (row.description as unknown as string | undefined);

  if (description) {
    metadata.description = description;
  }

  return metadata;
}

function normalizeCategory(tableName: string): string {
  return PT_TO_EN_CATEGORY_MAP[tableName] ?? tableName;
}

export async function importItemsFromSQLite(): Promise<ImportStats> {
  const Database = loadBetterSqlite();
  if (!Database) {
    return { processedTables: 0, processedItems: 0, inserted: 0, skipped: 0 };
  }

  const db = openSQLiteDatabase(Database);
  if (!db) {
    return { processedTables: 0, processedItems: 0, inserted: 0, skipped: 0 };
  }

  try {
    const tables = db
      .prepare(
        "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'",
      )
      .all() as { name: string }[];

    let processedTables = 0;
    let processedItems = 0;
    let inserted = 0;
    let skipped = 0;

    const batch: Prisma.ItemCreateManyInput[] = [];

    for (const { name: tableName } of tables) {
      const pragma = db
        .prepare(`PRAGMA table_info(${tableName})`)
        .all() as Array<{
        name: string;
      }>;
      const colNames = new Set(pragma.map((col) => col.name.toLowerCase()));

      const hasId = colNames.has("id");
      const hasNome = colNames.has("nome") || colNames.has("name");

      if (!hasId || !hasNome) {
        continue;
      }

      processedTables += 1;
      const rows = db.prepare(`SELECT * FROM ${tableName}`).all() as Record<
        string,
        unknown
      >[];

      for (const row of rows) {
        processedItems += 1;

        const rawId = row.id ?? row.item_id;
        const numericId = toNullableInt(rawId);
        if (!numericId) {
          skipped += 1;
          continue;
        }

        const nameField = (row.nome ?? row.name ?? "") as string;
        const itemName = typeof nameField === "string" ? nameField.trim() : "";
        if (!itemName) {
          skipped += 1;
          continue;
        }

        const imageUrl = (row.image_url ?? row.image ?? null) as string | null;
        const imageLocalPath = (row.image_local_path ??
          row.image_local ??
          null) as string | null;
        const rarity =
          (row.rarity ?? row.raridade ?? row.rareza ?? null) !== null
            ? String(row.rarity ?? row.raridade ?? row.rareza)
            : null;
        const level = toNullableInt(row.lvl ?? row.nivel ?? row.level);
        const weight = toNullableDecimal(
          row.peso ?? row.peso_em_oz ?? row.weight,
        );
        const slots = toNullableInt(row.slots ?? row.slot);
        const skill =
          (row.skill_para_usar ?? row.skill_required ?? row.skill ?? null)
            ? String(row.skill_para_usar ?? row.skill_required ?? row.skill)
            : null;

        const metadata = collectMetadata(row);
        const metadataPayload = metadata as Prisma.InputJsonValue;

        batch.push({
          source_table_id: numericId,
          source_table_name: tableName,
          item_name: itemName,
          category: normalizeCategory(tableName),
          image_local_path: imageLocalPath,
          image_url: imageUrl,
          rarity,
          level,
          weight,
          slots,
          skill,
          metadata: metadataPayload,
        });

        if (batch.length >= SQL_CHUNK_SIZE) {
          const { count } = await prisma.item.createMany({
            data: batch,
            skipDuplicates: true,
          });
          inserted += count;
          skipped += batch.length - count;
          batch.length = 0;
        }
      }
    }

    if (batch.length > 0) {
      const { count } = await prisma.item.createMany({
        data: batch,
        skipDuplicates: true,
      });
      inserted += count;
      skipped += batch.length - count;
    }

    return { processedTables, processedItems, inserted, skipped };
  } finally {
    try {
      db.close();
    } catch {
      // ignore close errors
    }
  }
}

export async function ensureItemsSeeded(): Promise<ImportStats | null> {
  if (seedPromise) {
    return seedPromise;
  }

  seedPromise = (async () => {
    const currentCount = await prisma.item.count();
    if (!seedLogged) {
      seedLogged = true;
      secureLogger.info("Verificação da tabela items concluída", {
        existing: currentCount,
      });
    }

    if (currentCount > 0) {
      return null;
    }

    secureLogger.info(
      "Tabela items vazia. Iniciando importação automática a partir do SQLite.",
    );
    try {
      const stats = await importItemsFromSQLite();
      secureLogger.info("Importação de itens concluída", stats);
      return stats;
    } catch (error) {
      secureLogger.error("Falha na importação automática de itens", error);
      throw error;
    }
  })();

  try {
    return await seedPromise;
  } finally {
    seedPromise = null;
  }
}