import initSqlJs from "sql.js";
import sqlWasmUrl from "sql.js/dist/sql-wasm.wasm?url";
import {
  formatBytes,
  normalizeDump,
  quoteIdentifier,
  quoteSqlString,
  readSqlTextFromFile,
  rewriteStatementForSqlite,
  splitSqlStatements,
  yieldToUi,
} from "../../lib/sqlDumpUtils";

let SQL = null;
let db = null;
let importInProgress = false;
let importCancelRequested = false;

self.onmessage = async (event) => {
  const message = event.data || {};
  const { type, requestId } = message;

  if (type === "cancelImport") {
    if (importInProgress) {
      importCancelRequested = true;
      postEvent("import-cancel-requested", {});
    }
    return;
  }

  if (!requestId) {
    return;
  }

  try {
    let data;

    switch (type) {
      case "init":
        data = await initEngine();
        break;
      case "reset":
        data = resetDatabase();
        break;
      case "getTables":
        data = getTables();
        break;
      case "getTablePage":
        data = getTablePage(message);
        break;
      case "runQuery":
        data = runQuery(message.sql);
        break;
      case "importDump":
        data = await importDump(message.file);
        break;
      default:
        throw new Error(`Unknown worker command: ${type}`);
    }

    postResponse(requestId, true, data);
  } catch (error) {
    postResponse(requestId, false, {
      message: error?.message || "Worker command failed.",
      stack: error?.stack || "",
    });
  }
};

async function initEngine() {
  if (!SQL) {
    SQL = await initSqlJs({
      locateFile: () => sqlWasmUrl,
    });
  }

  if (db) {
    try {
      db.close();
    } catch {
      // no-op
    }
  }

  db = new SQL.Database();
  return { ready: true };
}

function resetDatabase() {
  ensureDbReady();

  try {
    db.close();
  } catch {
    // no-op
  }

  db = new SQL.Database();
  return { ok: true };
}

function getTables() {
  ensureDbReady();

  const result = db.exec(
    "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
  );

  return {
    tables: result[0]?.values?.map((row) => String(row[0])) || [],
  };
}

function getTablePage({ tableName, page, pageSize }) {
  ensureDbReady();

  if (!tableName) {
    return {
      tableName: "",
      page: 0,
      pageSize: 50,
      totalRows: 0,
      columns: [],
      rows: [],
      canPrevPage: false,
      canNextPage: false,
    };
  }

  const safePageSize = Math.max(1, Number(pageSize) || 50);
  const requestedPage = Math.max(0, Number(page) || 0);
  const escapedName = quoteIdentifier(tableName);

  const countRes = db.exec(`SELECT COUNT(*) AS n FROM ${escapedName}`);
  const totalRows = Number(countRes[0]?.values?.[0]?.[0] || 0);

  const maxPage = totalRows > 0 ? Math.floor((totalRows - 1) / safePageSize) : 0;
  const safePage = Math.min(requestedPage, maxPage);
  const offset = safePage * safePageSize;

  const dataRes = db.exec(`SELECT * FROM ${escapedName} LIMIT ${safePageSize} OFFSET ${offset}`);

  let columns = [];
  let rows = [];
  if (dataRes.length > 0) {
    columns = dataRes[0].columns;
    rows = dataRes[0].values;
  } else {
    const infoRes = db.exec(`PRAGMA table_info(${quoteSqlString(tableName)})`);
    columns = infoRes[0]?.values?.map((row) => String(row[1])) || [];
  }

  return {
    tableName,
    page: safePage,
    pageSize: safePageSize,
    totalRows,
    columns,
    rows,
    canPrevPage: safePage > 0,
    canNextPage: offset + rows.length < totalRows,
  };
}

function runQuery(sql) {
  ensureDbReady();

  const statement = String(sql || "").trim();
  if (!statement) {
    throw new Error("Enter a query first.");
  }

  const resultSets = db.exec(statement);
  if (!resultSets.length) {
    return {
      columns: [],
      rows: [],
      meta: "Query executed successfully. No result set returned.",
    };
  }

  const first = resultSets[0];
  return {
    columns: first.columns,
    rows: first.values,
    meta: `Returned ${first.values.length.toLocaleString()} rows in first result set (${resultSets.length} set${
      resultSets.length === 1 ? "" : "s"
    }).`,
  };
}

async function importDump(file) {
  ensureDbReady();

  if (!file || typeof file.arrayBuffer !== "function") {
    throw new Error("No dump file was provided.");
  }

  if (importInProgress) {
    throw new Error("An import is already in progress.");
  }

  importInProgress = true;
  importCancelRequested = false;

  const appendLog = (line) => {
    postEvent("log", { line });
  };

  appendLog(`[file] ${file.name} (${formatBytes(file.size)})`);

  let transactionOpen = false;

  try {
    const sqlText = await readSqlTextFromFile(file, appendLog);
    appendLog(`[decode] extracted ${sqlText.length.toLocaleString()} characters`);

    if (importCancelRequested) {
      appendLog("[cancelled] import cancelled before parsing");
      return {
        cancelled: true,
        applied: 0,
        skipped: 0,
      };
    }

    const normalized = normalizeDump(sqlText);
    const rawStatements = splitSqlStatements(normalized);

    const sourceTargets = Array.from(
      new Set(rawStatements.map(extractSourceTarget).filter(Boolean))
    );
    if (sourceTargets.length > 0) {
      appendLog(
        `[warn] this dump references ${sourceTargets.length.toLocaleString()} SOURCE file(s) that cannot be auto-loaded by the browser.`
      );
      const sampleTargets = sourceTargets.slice(0, 8).join(", ");
      appendLog(`[hint] import referenced file(s) manually after schema load: ${sampleTargets}`);
      if (sourceTargets.length > 8) {
        appendLog(`[hint] ...and ${sourceTargets.length - 8} more SOURCE file(s).`);
      }
    }

    const statements = rawStatements
      .flatMap(rewriteStatementForSqlite)
      .filter(Boolean);

    if (!statements.length) {
      throw new Error("No executable SQL statements found in the dump.");
    }

    postEvent("import-started", {
      totalStatements: statements.length,
    });

    const start = performance.now();
    let applied = 0;
    let skipped = 0;

    db.run("BEGIN");
    transactionOpen = true;

    for (let i = 0; i < statements.length; i += 1) {
      if (importCancelRequested) {
        db.run("ROLLBACK");
        transactionOpen = false;
        appendLog("[cancelled] import cancelled by user");
        postEvent("import-cancelled", {
          processed: i,
          totalStatements: statements.length,
          applied,
          skipped,
        });

        return {
          cancelled: true,
          applied,
          skipped,
        };
      }

      const stmt = statements[i];

      try {
        db.run(stmt);
        applied += 1;
      } catch (error) {
        skipped += 1;
        if (skipped <= 25) {
          appendLog(`[skip ${i + 1}] ${error.message}`);
          appendLog(`  ${stmt.slice(0, 180).replace(/\s+/g, " ")}`);
        }
      }

      const processed = i + 1;
      if (processed % 200 === 0 || processed === statements.length) {
        postEvent("import-progress", {
          processed,
          totalStatements: statements.length,
          applied,
          skipped,
        });
        await yieldToUi();
      }
    }

    db.run("COMMIT");
    transactionOpen = false;

    const elapsedMs = Math.round(performance.now() - start);
    appendLog(`[done] applied=${applied} skipped=${skipped} time=${elapsedMs}ms`);

    postEvent("import-finished", {
      applied,
      skipped,
      elapsedMs,
    });

    return {
      cancelled: false,
      applied,
      skipped,
      elapsedMs,
    };
  } catch (error) {
    if (transactionOpen) {
      try {
        db.run("ROLLBACK");
      } catch {
        // no-op
      }
    }

    appendLog(`[error] ${error.stack || error.message}`);
    throw error;
  } finally {
    importInProgress = false;
    importCancelRequested = false;
  }
}

function ensureDbReady() {
  if (!SQL || !db) {
    throw new Error("Database engine is not ready yet.");
  }
}

function postResponse(requestId, ok, payload) {
  self.postMessage({
    type: "response",
    requestId,
    ok,
    ...(ok ? { data: payload } : { error: payload }),
  });
}

function postEvent(event, data) {
  self.postMessage({
    type: "event",
    event,
    data,
  });
}

function extractSourceTarget(statement) {
  const match = String(statement || "").trim().match(/^SOURCE\s+(.+?)\s*;?$/i);
  if (!match) {
    return null;
  }

  return match[1].replace(/^['"`]|['"`]$/g, "").trim() || null;
}