import fs from "node:fs/promises";
import path from "node:path";
import vm from "node:vm";
import xlsx from "xlsx";

const ROOT = process.cwd();

const LEGACY_DIR = path.join(ROOT, "legacy");
const OUT_DIR = path.join(ROOT, "apps", "web", "public", "data", "legacy");

const PERMITTED_XLSX = path.join(LEGACY_DIR, "AIS_permitted_Chinese_2025_정리완료_최종.xlsx");
const CHECKLIST_XLSX = path.join(LEGACY_DIR, "항적점검표_본선_부속선_운반선.xlsx");
const FLEET_906_JSX = path.join(LEGACY_DIR, "선단구성_906척_어업수역 (1).jsx");

function toStr(x) {
  return (x ?? "").toString().trim();
}

function toNumOrNull(x) {
  const s = toStr(x);
  if (!s) return null;
  const n = Number(s);
  return Number.isFinite(n) ? n : null;
}

function shipCodeFromChecklistSheet(sheetName) {
  if (sheetName.includes("본선")) return "PT";
  if (sheetName.includes("부속선")) return "PT-S";
  if (sheetName.includes("운반선")) return "FC";
  return null;
}

function extractArrayLiteral(txt, marker) {
  const markerIdx = txt.indexOf(marker);
  if (markerIdx < 0) throw new Error(`Marker not found: ${marker}`);
  const start = txt.indexOf("[", markerIdx);
  if (start < 0) throw new Error(`'[' not found after marker: ${marker}`);

  let depth = 0;
  let inString = false;
  let esc = false;
  for (let i = start; i < txt.length; i += 1) {
    const ch = txt[i];
    if (inString) {
      if (esc) {
        esc = false;
        continue;
      }
      if (ch === "\\") {
        esc = true;
        continue;
      }
      if (ch === "\"") {
        inString = false;
      }
      continue;
    }

    if (ch === "\"") {
      inString = true;
      continue;
    }

    if (ch === "[") depth += 1;
    if (ch === "]") depth -= 1;
    if (depth === 0) return txt.slice(start, i + 1);
  }

  throw new Error(`Unterminated array literal for marker: ${marker}`);
}

function readPermittedListXlsx(filePath) {
  const wb = xlsx.readFile(filePath, { cellDates: true });
  const sheetName = wb.SheetNames[0];
  const ws = wb.Sheets[sheetName];
  const rows = xlsx.utils.sheet_to_json(ws, { defval: "", raw: false });

  const byPermitNo = new Map();
  for (const r of rows) {
    const permitNo = toStr(r.permit_no);
    if (!permitNo) continue;
    const mmsi = toNumOrNull(r.target_id);

    const prev = byPermitNo.get(permitNo);
    if (prev) {
      if (mmsi && !prev.mmsiList.includes(mmsi)) prev.mmsiList.push(mmsi);
      continue;
    }

    byPermitNo.set(permitNo, {
      permitNo,
      shipNameRoman: toStr(r.ship_name),
      ton: toNumOrNull(r.ton),
      callSign: toStr(r.call_sign),
      shipPower: toNumOrNull(r.ship_power),
      shipLen: toNumOrNull(r.ship_len),
      shipWidth: toNumOrNull(r.ship_width),
      shipDept: toNumOrNull(r.ship_dept),
      workSeaArea: toStr(r.work_sea_area),
      workTerm1: toStr(r.work_term1),
      workTerm2: toStr(r.work_term2),
      quota: toStr(r.quota),
      shipCode: toStr(r.ship_code),
      mmsiList: mmsi ? [mmsi] : [],
      sources: { permittedList: true, checklist: false, fleet906: false },
      ownerCn: null,
      ownerRoman: null,
      shipNameCn: null,
      pairPermitNo: null,
      pairShipNameCn: null,
      checklistSheet: null,
    });
  }

  return Array.from(byPermitNo.values());
}

function readChecklistXlsx(filePath) {
  const wb = xlsx.readFile(filePath, { cellDates: true });
  const out = [];

  for (const sheetName of wb.SheetNames) {
    const code = shipCodeFromChecklistSheet(sheetName);
    if (!code) continue;
    const ws = wb.Sheets[sheetName];
    const rows = xlsx.utils.sheet_to_json(ws, { defval: "", raw: false, range: 2 });

    for (const r of rows) {
      const permitNo = toStr(r["허가번호"]);
      if (!permitNo) continue;
      out.push({
        permitNo,
        shipCode: code,
        shipNameCn: toStr(r["선박명\n(중국)"]),
        shipNameRoman: toStr(r["선박명\n(로마)"]),
        ton: toNumOrNull(r["톤수"]),
        ownerCn: toStr(r["소유주\n(중국)"]),
        ownerRoman: toStr(r["소유주\n(로마)"]),
        pairPermitNo: toStr(r["짝 허가번호"]),
        pairShipNameCn: toStr(r["짝 선박명"]),
        workSeaArea: toStr(r["허가수역"]),
        checklistSheet: sheetName,
      });
    }
  }

  return out;
}

async function readFleet906(filePath) {
  const txt = await fs.readFile(filePath, "utf-8");
  const arrLit = extractArrayLiteral(txt, "const D=");
  const D = vm.runInNewContext(`(${arrLit})`, {}, { timeout: 10_000 });
  if (!Array.isArray(D)) throw new Error("Fleet906 D is not an array");

  const out = [];

  function pushVessel(v, shipCode, own, ownEn, extra = {}) {
    if (!Array.isArray(v) || v.length < 4) return;
    out.push({
      permitNo: toStr(v[0]),
      shipNameCn: toStr(v[1]),
      shipNameRoman: toStr(v[2]),
      ton: toNumOrNull(v[3]),
      shipCode,
      ownerCn: toStr(own),
      ownerRoman: toStr(ownEn),
      ...extra,
    });
  }

  for (const r of D) {
    if (!Array.isArray(r) || r.length < 9) continue;
    const [own, ownEn, pairs, gn, ot, ps, fc, upt, upts] = r;

    if (Array.isArray(pairs)) {
      for (const p of pairs) {
        if (!Array.isArray(p) || p.length < 2) continue;
        const main = p[0];
        const sub = p[1];
        const mainPermitNo = Array.isArray(main) ? toStr(main[0]) : "";
        const subPermitNo = Array.isArray(sub) ? toStr(sub[0]) : "";

        pushVessel(main, "PT", own, ownEn, { pairPermitNo: subPermitNo });
        pushVessel(sub, "PT-S", own, ownEn, { pairPermitNo: mainPermitNo });
      }
    }

    if (Array.isArray(gn)) for (const v of gn) pushVessel(v, "GN", own, ownEn);
    if (Array.isArray(ot)) for (const v of ot) pushVessel(v, "OT", own, ownEn);
    if (Array.isArray(ps)) for (const v of ps) pushVessel(v, "PS", own, ownEn);
    if (Array.isArray(fc)) for (const v of fc) pushVessel(v, "FC", own, ownEn);
    if (Array.isArray(upt)) for (const v of upt) pushVessel(v, "PT", own, ownEn);
    if (Array.isArray(upts)) for (const v of upts) pushVessel(v, "PT-S", own, ownEn);
  }

  return out;
}

async function main() {
  await fs.mkdir(OUT_DIR, { recursive: true });

  const permitted = readPermittedListXlsx(PERMITTED_XLSX);
  const checklist = readChecklistXlsx(CHECKLIST_XLSX);
  const fleet906 = await readFleet906(FLEET_906_JSX);

  const byPermitNo = new Map();
  for (const v of permitted) byPermitNo.set(v.permitNo, v);

  for (const c of checklist) {
    const prev = byPermitNo.get(c.permitNo);
    if (!prev) {
      byPermitNo.set(c.permitNo, {
        permitNo: c.permitNo,
        shipNameRoman: c.shipNameRoman,
        ton: c.ton,
        callSign: "",
        shipPower: null,
        shipLen: null,
        shipWidth: null,
        shipDept: null,
        workSeaArea: c.workSeaArea,
        workTerm1: "",
        workTerm2: "",
        quota: "",
        shipCode: c.shipCode,
        mmsiList: [],
        sources: { permittedList: false, checklist: true, fleet906: false },
        ownerCn: c.ownerCn || null,
        ownerRoman: c.ownerRoman || null,
        shipNameCn: c.shipNameCn || null,
        pairPermitNo: c.pairPermitNo || null,
        pairShipNameCn: c.pairShipNameCn || null,
        checklistSheet: c.checklistSheet || null,
      });
      continue;
    }

    prev.sources.checklist = true;
    prev.shipCode = prev.shipCode || c.shipCode;
    prev.shipNameCn = prev.shipNameCn || c.shipNameCn || null;
    prev.shipNameRoman = prev.shipNameRoman || c.shipNameRoman || "";
    prev.ton = prev.ton ?? c.ton ?? null;
    prev.ownerCn = prev.ownerCn || c.ownerCn || null;
    prev.ownerRoman = prev.ownerRoman || c.ownerRoman || null;
    prev.pairPermitNo = prev.pairPermitNo || c.pairPermitNo || null;
    prev.pairShipNameCn = prev.pairShipNameCn || c.pairShipNameCn || null;
    prev.workSeaArea = prev.workSeaArea || c.workSeaArea || "";
    prev.checklistSheet = prev.checklistSheet || c.checklistSheet || null;
  }

  for (const f of fleet906) {
    const prev = byPermitNo.get(f.permitNo);
    if (!prev) continue;
    prev.sources.fleet906 = true;
    prev.shipNameCn = prev.shipNameCn || f.shipNameCn || null;
    prev.shipNameRoman = prev.shipNameRoman || f.shipNameRoman || "";
    prev.ton = prev.ton ?? f.ton ?? null;
    prev.shipCode = prev.shipCode || f.shipCode || "";
    prev.ownerCn = prev.ownerCn || f.ownerCn || null;
    prev.ownerRoman = prev.ownerRoman || f.ownerRoman || null;
    prev.pairPermitNo = prev.pairPermitNo || f.pairPermitNo || null;
  }

  const vessels = Array.from(byPermitNo.values()).filter((v) => v && v.permitNo);

  const out = {
    generatedAt: new Date().toISOString(),
    counts: {
      permittedList: permitted.length,
      checklist: checklist.length,
      fleet906: fleet906.length,
      merged: vessels.length,
    },
    vessels,
  };

  const outPath = path.join(OUT_DIR, "chinese-permitted.v1.json");
  await fs.writeFile(outPath, JSON.stringify(out), "utf-8");
  // eslint-disable-next-line no-console
  console.log(`Wrote ${vessels.length} vessels -> ${path.relative(ROOT, outPath)}`);
}

main().catch((err) => {
  // eslint-disable-next-line no-console
  console.error(err);
  process.exit(1);
});