export async function readSqlTextFromFile(file, appendLog) {
  const rawBytes = new Uint8Array(await file.arrayBuffer());
  const inputBytes = isLikelyGzip(file, rawBytes) ? await gunzipBytes(rawBytes, appendLog) : rawBytes;
  return new TextDecoder("utf-8").decode(inputBytes);
}

function isLikelyGzip(file, bytes) {
  const byName = /\.gz$/i.test(file.name);
  const byMagic = bytes.length > 2 && bytes[0] === 0x1f && bytes[1] === 0x8b;
  return byName || byMagic;
}

async function gunzipBytes(bytes, appendLog) {
  appendLog("[gzip] compressed file detected");

  if (typeof DecompressionStream === "undefined") {
    throw new Error("This app requires DecompressionStream support for .sql.gz files.");
  }

  try {
    const ds = new DecompressionStream("gzip");
    const stream = new Blob([bytes]).stream().pipeThrough(ds);
    const ab = await new Response(stream).arrayBuffer();
    appendLog("[gzip] decompressed via DecompressionStream");
    return new Uint8Array(ab);
  } catch (error) {
    throw new Error(`Failed to decompress .gz file: ${error.message}`);
  }
}

export function normalizeDump(sql) {
  let text = sql;

  if (text.charCodeAt(0) === 0xfeff) {
    text = text.slice(1);
  }

  text = text.replace(/\r\n?/g, "\n");

  text = text.replace(/\/\*![0-9]{5}\s*([\s\S]*?)\*\//g, "$1");
  text = text.replace(/\/\*(?!\!)[\s\S]*?\*\//g, " ");

  text = text.replace(/^\s*--.*$/gm, "");
  text = text.replace(/^\s*#.*$/gm, "");
  text = text.replace(/^\s*LOCK TABLES\b.*$/gim, "");
  text = text.replace(/^\s*UNLOCK TABLES\b.*$/gim, "");
  text = text.replace(/^\s*DELIMITER\b.*$/gim, "");
  text = text.replace(/^\s*SET\b.*$/gim, "");
  text = text.replace(/^\s*START TRANSACTION\b.*$/gim, "");
  text = text.replace(/^\s*COMMIT\b.*$/gim, "");

  return text;
}

export function splitSqlStatements(sql) {
  const statements = [];
  let current = "";
  let inSingle = false;
  let inDouble = false;
  let inBacktick = false;

  for (let i = 0; i < sql.length; i += 1) {
    const ch = sql[i];

    if ((inSingle || inDouble) && ch === "\\") {
      current += ch;
      i += 1;
      if (i < sql.length) {
        current += sql[i];
      }
      continue;
    }

    if (!inDouble && !inBacktick && ch === "'") {
      inSingle = !inSingle;
      current += ch;
      continue;
    }

    if (!inSingle && !inBacktick && ch === '"') {
      inDouble = !inDouble;
      current += ch;
      continue;
    }

    if (!inSingle && !inDouble && ch === "`") {
      inBacktick = !inBacktick;
      current += ch;
      continue;
    }

    if (!inSingle && !inDouble && !inBacktick && ch === ";") {
      const trimmed = current.trim();
      if (trimmed) {
        statements.push(trimmed);
      }
      current = "";
      continue;
    }

    current += ch;
  }

  const last = current.trim();
  if (last) {
    statements.push(last);
  }

  return statements;
}

export function rewriteStatementForSqlite(statement) {
  let s = statement.trim();

  if (!s) {
    return [];
  }

  if (/^(CREATE|DROP)\s+DATABASE\b/i.test(s)) return [];
  if (/^USE\b/i.test(s)) return [];
  if (/^ALTER\s+TABLE\b/i.test(s)) return [];
  if (/^(LOCK|UNLOCK)\s+TABLES\b/i.test(s)) return [];
  if (/^DELIMITER\b/i.test(s)) return [];
  if (/^SOURCE\b/i.test(s)) return [];
  if (/^FLUSH\b/i.test(s)) return [];
  if (/^SELECT\b/i.test(s) && /@@/.test(s)) return [];

  s = s.replace(/`/g, '"');
  s = s.replace(/\bUNSIGNED\b/gi, "");
  s = s.replace(/\bZEROFILL\b/gi, "");
  s = s.replace(/\bAUTO_INCREMENT\s*=\s*\d+\b/gi, "");
  s = s.replace(/\bAUTO_INCREMENT\b/gi, "");
  s = s.replace(/\bCHARACTER\s+SET\s+\w+/gi, "");
  s = s.replace(/\bCOLLATE\s+\w+/gi, "");
  s = s.replace(/\bON\s+UPDATE\s+CURRENT_TIMESTAMP(?:\(\))?/gi, "");
  s = s.replace(/\bCURRENT_TIMESTAMP\(\)/gi, "CURRENT_TIMESTAMP");

  if (/^DROP\s+TABLE\b/i.test(s)) {
    return rewriteDropTable(s);
  }

  if (/^CREATE\s+TABLE\b/i.test(s)) {
    return rewriteCreateTable(s);
  }

  if (/^CREATE\s+OR\s+REPLACE\s+VIEW\b/i.test(s)) {
    return rewriteCreateOrReplaceView(s);
  }

  s = s.replace(/^INSERT\s+IGNORE\s+INTO\b/i, "INSERT OR IGNORE INTO");
  s = rewriteMySqlStringLiterals(s);

  return [s.trim()];
}

function rewriteDropTable(sql) {
  const dropMatch = sql.match(/^DROP\s+TABLE\s+(IF\s+EXISTS\s+)?([\s\S]+)$/i);
  if (!dropMatch) {
    return [sql.trim()];
  }

  const hasIfExists = Boolean(dropMatch[1]);
  const tableList = dropMatch[2].trim();
  const tableTokens = splitTopLevelCsv(tableList).map((token) => token.trim()).filter(Boolean);

  if (tableTokens.length <= 1) {
    return [sql.trim()];
  }

  return tableTokens.map((token) => {
    const normalizedName = normalizeIdentifierToken(token);
    return `DROP TABLE ${hasIfExists ? "IF EXISTS " : ""}${normalizedName}`;
  });
}

function rewriteCreateOrReplaceView(sql) {
  const createView = sql.replace(/^CREATE\s+OR\s+REPLACE\s+VIEW\b/i, "CREATE VIEW").trim();
  const viewMatch = createView.match(/^CREATE\s+VIEW\s+("[^"]+"|[^\s]+)\s+AS\b/i);

  if (!viewMatch) {
    return [rewriteMySqlStringLiterals(createView)];
  }

  const viewName = normalizeIdentifierToken(viewMatch[1]);
  const normalizedCreateView = createView.replace(
    /^CREATE\s+VIEW\s+("[^"]+"|[^\s]+)\s+AS\b/i,
    `CREATE VIEW ${viewName} AS`
  );

  return [`DROP VIEW IF EXISTS ${viewName}`, rewriteMySqlStringLiterals(normalizedCreateView)];
}

function rewriteCreateTable(sql) {
  const tableMatch = sql.match(/^CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?("[^"]+"|[^\s(]+)/i);
  const openParen = sql.indexOf("(");
  const closeParen = sql.lastIndexOf(")");

  if (!tableMatch || openParen === -1 || closeParen <= openParen) {
    return [rewriteMySqlStringLiterals(sql)];
  }

  const tableToken = normalizeIdentifierToken(tableMatch[1]);
  const tableNameRaw = stripIdentifierQuotes(tableToken);
  const body = sql.slice(openParen + 1, closeParen);
  const parts = splitTopLevelCsv(body);
  const columns = [];
  const tableConstraints = [];
  const indexStatements = [];

  for (const rawPart of parts) {
    const part = rawPart.trim();
    if (!part) continue;

    const partWithoutConstraintName = part.replace(
      /^CONSTRAINT\s+(?:"[^"]+"|`[^`]+`|[^\s]+)\s+/i,
      ""
    );

    if (/^(KEY|INDEX|FULLTEXT\s+KEY|SPATIAL\s+KEY|UNIQUE\s+KEY|UNIQUE\s+INDEX)\b/i.test(partWithoutConstraintName)) {
      const indexSql = buildIndexStatement(partWithoutConstraintName, tableToken, tableNameRaw);
      if (indexSql) {
        indexStatements.push(indexSql);
      }
      continue;
    }

    const tableConstraint = normalizeTableConstraint(partWithoutConstraintName);
    if (tableConstraint) {
      tableConstraints.push(tableConstraint);
      continue;
    }

    if (/^CONSTRAINT\b/i.test(part)) {
      continue;
    }

    const columnSql = normalizeColumnDefinition(part);
    if (columnSql) {
      columns.push(columnSql);
    }
  }

  if (!columns.length) {
    return [rewriteMySqlStringLiterals(sql.slice(0, closeParen + 1).replace(/,\s*\)/g, ")"))];
  }

  const createBody = [...columns, ...tableConstraints].join(",\n  ");
  const createStmt = rewriteMySqlStringLiterals(`CREATE TABLE ${tableToken} (\n  ${createBody}\n)`);
  return [createStmt, ...indexStatements];
}

function normalizeColumnDefinition(part) {
  const match = part.match(/^("[^"]+"|`[^`]+`|[^\s]+)\s+([\s\S]+)$/);
  if (!match) {
    return null;
  }

  const colName = normalizeIdentifierToken(match[1]);
  let definition = match[2];
  const hasAutoIncrement = /\bAUTO_INCREMENT\b/i.test(definition);

  definition = definition.replace(/\bUNSIGNED\b/gi, "");
  definition = definition.replace(/\bZEROFILL\b/gi, "");
  definition = definition.replace(/\bAUTO_INCREMENT\b/gi, "");
  definition = definition.replace(/\bCHARACTER\s+SET\s+[^\s,]+/gi, "");
  definition = definition.replace(/\bCOLLATE\s+[^\s,]+/gi, "");
  definition = definition.replace(/\bCOMMENT\s+'(?:\\.|[^'])*'/gi, "");
  definition = definition.replace(/\bON\s+UPDATE\s+CURRENT_TIMESTAMP(?:\(\))?/gi, "");

  definition = normalizeDataType(definition);
  definition = definition.replace(/\s+/g, " ").trim();

  if (!definition) {
    definition = "TEXT";
  }

  if (hasAutoIncrement) {
    if (/\bPRIMARY\s+KEY\b/i.test(definition)) {
      return `${colName} INTEGER PRIMARY KEY`;
    }
    if (!/^INTEGER\b/i.test(definition)) {
      definition = `INTEGER ${definition}`;
    }
  }

  return `${colName} ${definition}`.replace(/\s+/g, " ").trim();
}

function normalizeDataType(definition) {
  let out = definition;
  out = out.replace(/\bTINYINT\s*\(\s*1\s*\)(?=\s|,|\)|$)/gi, "INTEGER");
  out = out.replace(/\b(?:TINYINT|SMALLINT|MEDIUMINT|INT|INTEGER|BIGINT)(?:\s*\(\s*\d+\s*\))?(?=\s|,|\)|$)/gi, "INTEGER");
  out = out.replace(/\b(?:DOUBLE\s+PRECISION|DOUBLE|FLOAT|DECIMAL|NUMERIC)(?:\s*\(\s*\d+\s*(?:,\s*\d+\s*)?\))?(?=\s|,|\)|$)/gi, "REAL");
  out = out.replace(/\b(?:VARCHAR|CHAR|TEXT|TINYTEXT|MEDIUMTEXT|LONGTEXT|JSON)(?:\s*\(\s*\d+\s*\))?(?=\s|,|\)|$)/gi, "TEXT");
  out = out.replace(/\b(?:ENUM|SET)\s*\([^)]*\)/gi, "TEXT");
  out = out.replace(/\b(?:DATE|TIME|DATETIME|TIMESTAMP|YEAR)\b/gi, "TEXT");
  out = out.replace(/\b(?:BLOB|TINYBLOB|MEDIUMBLOB|LONGBLOB|BINARY|VARBINARY)(?:\s*\(\s*\d+\s*\))?(?=\s|,|\)|$)/gi, "BLOB");
  out = out.replace(/\bBOOL(?:EAN)?\b/gi, "INTEGER");
  out = out.replace(/\bCURRENT_TIMESTAMP\(\)/gi, "CURRENT_TIMESTAMP");
  return out;
}

function normalizeTableConstraint(part) {
  const normalized = part.replace(/`/g, '"').trim();

  const primaryMatch = normalized.match(/^PRIMARY\s+KEY\s*\(([^)]+)\)/i);
  if (primaryMatch) {
    const cols = normalizeIndexColumns(primaryMatch[1]);
    return cols.length ? `PRIMARY KEY (${cols.join(", ")})` : null;
  }

  const uniqueMatch = normalized.match(/^UNIQUE(?:\s+KEY|\s+INDEX)?(?:\s+(?:"[^"]+"|[^\s(]+))?\s*\(([^)]+)\)/i);
  if (uniqueMatch) {
    const cols = normalizeIndexColumns(uniqueMatch[1]);
    return cols.length ? `UNIQUE (${cols.join(", ")})` : null;
  }

  const foreignMatch = normalized.match(
    /^FOREIGN\s+KEY\s*\(([^)]+)\)\s+REFERENCES\s+("[^"]+"|[^\s(]+)\s*\(([^)]+)\)\s*([\s\S]*)$/i
  );
  if (foreignMatch) {
    const cols = normalizeIndexColumns(foreignMatch[1]);
    const refTable = normalizeIdentifierToken(foreignMatch[2]);
    const refCols = normalizeIndexColumns(foreignMatch[3]);
    const actions = normalizeForeignKeyActions(foreignMatch[4]);
    if (cols.length && refCols.length && refTable) {
      return `FOREIGN KEY (${cols.join(", ")}) REFERENCES ${refTable} (${refCols.join(", ")})${actions}`;
    }
    return null;
  }

  return null;
}

function normalizeForeignKeyActions(actionsSpec) {
  if (!actionsSpec) {
    return "";
  }

  const clauses = [];
  const actionRegex = /\bON\s+(DELETE|UPDATE)\s+(RESTRICT|CASCADE|SET\s+NULL|SET\s+DEFAULT|NO\s+ACTION)\b/gi;
  let match;

  while ((match = actionRegex.exec(actionsSpec)) !== null) {
    const eventType = match[1].toUpperCase();
    const actionType = match[2].replace(/\s+/g, " ").toUpperCase();
    clauses.push(`ON ${eventType} ${actionType}`);
  }

  return clauses.length ? ` ${clauses.join(" ")}` : "";
}

function buildIndexStatement(part, tableToken, tableNameRaw) {
  const normalized = part.replace(/`/g, '"').trim();
  const match = normalized.match(/^(UNIQUE\s+)?(?:FULLTEXT\s+|SPATIAL\s+)?(?:KEY|INDEX)\s*(?:"([^"]+)"|([^\s(]+))?\s*\(([^)]+)\)/i);
  if (!match) {
    return null;
  }

  const unique = Boolean(match[1]);
  const explicitName = match[2] || match[3] || "";
  const cols = normalizeIndexColumns(match[4]);
  if (!cols.length) {
    return null;
  }

  const generatedName = `${tableNameRaw}_${cols.map(stripIdentifierQuotes).join("_")}${unique ? "_uidx" : "_idx"}`;
  const indexName = quoteIdentifier(explicitName || generatedName);
  return `CREATE ${unique ? "UNIQUE " : ""}INDEX IF NOT EXISTS ${indexName} ON ${tableToken} (${cols.join(", ")})`;
}

function normalizeIndexColumns(columnsSpec) {
  return splitTopLevelCsv(columnsSpec)
    .map((column) => column.trim())
    .map((column) => column.replace(/\(\s*\d+\s*\)$/, ""))
    .map((column) => column.replace(/\s+(ASC|DESC)$/i, ""))
    .map((column) => normalizeIdentifierToken(column))
    .filter(Boolean);
}

function splitTopLevelCsv(input) {
  const parts = [];
  let current = "";
  let depth = 0;
  let inSingle = false;
  let inDouble = false;
  let inBacktick = false;

  for (let i = 0; i < input.length; i += 1) {
    const ch = input[i];

    if ((inSingle || inDouble) && ch === "\\") {
      current += ch;
      i += 1;
      if (i < input.length) {
        current += input[i];
      }
      continue;
    }

    if (!inDouble && !inBacktick && ch === "'") {
      inSingle = !inSingle;
      current += ch;
      continue;
    }

    if (!inSingle && !inBacktick && ch === '"') {
      inDouble = !inDouble;
      current += ch;
      continue;
    }

    if (!inSingle && !inDouble && ch === "`") {
      inBacktick = !inBacktick;
      current += ch;
      continue;
    }

    if (!inSingle && !inDouble && !inBacktick) {
      if (ch === "(") {
        depth += 1;
      } else if (ch === ")") {
        depth = Math.max(0, depth - 1);
      } else if (ch === "," && depth === 0) {
        if (current.trim()) {
          parts.push(current.trim());
        }
        current = "";
        continue;
      }
    }

    current += ch;
  }

  if (current.trim()) {
    parts.push(current.trim());
  }

  return parts;
}

function normalizeIdentifierToken(token) {
  const trimmed = String(token || "").trim();
  if (!trimmed) {
    return "";
  }

  if (trimmed === "*") {
    return trimmed;
  }

  if (trimmed.includes("(")) {
    return trimmed;
  }

  if (trimmed.includes(".")) {
    return trimmed
      .split(".")
      .map((part) => quoteIdentifier(stripIdentifierQuotes(part)))
      .join(".");
  }

  return quoteIdentifier(stripIdentifierQuotes(trimmed));
}

function stripIdentifierQuotes(value) {
  const trimmed = String(value || "").trim();
  if (!trimmed) {
    return "";
  }

  if (
    (trimmed.startsWith('"') && trimmed.endsWith('"')) ||
    (trimmed.startsWith("`") && trimmed.endsWith("`")) ||
    (trimmed.startsWith("[") && trimmed.endsWith("]"))
  ) {
    return trimmed.slice(1, -1).replace(/""/g, '"');
  }

  return trimmed;
}

function rewriteMySqlStringLiterals(sql) {
  let out = "";
  let inSingle = false;

  for (let i = 0; i < sql.length; i += 1) {
    const ch = sql[i];

    if (!inSingle) {
      out += ch;
      if (ch === "'") {
        inSingle = true;
      }
      continue;
    }

    if (ch === "\\") {
      const next = sql[i + 1];
      if (next === undefined) {
        out += ch;
        continue;
      }

      if (next === "'") {
        out += "''";
        i += 1;
        continue;
      }

      if (next === "\\") {
        out += "\\";
        i += 1;
        continue;
      }

      if (next === "n") {
        out += "\n";
        i += 1;
        continue;
      }

      if (next === "r") {
        out += "\r";
        i += 1;
        continue;
      }

      if (next === "t") {
        out += "\t";
        i += 1;
        continue;
      }

      out += next;
      i += 1;
      continue;
    }

    if (ch === "'") {
      if (sql[i + 1] === "'") {
        out += "''";
        i += 1;
        continue;
      }

      out += ch;
      inSingle = false;
      continue;
    }

    out += ch;
  }

  return out;
}

export function formatCell(value) {
  if (value === null || value === undefined) return "NULL";
  if (value instanceof Uint8Array) return `[BLOB ${value.length} bytes]`;
  if (typeof value === "object") return JSON.stringify(value);
  return String(value);
}

export function quoteIdentifier(identifier) {
  return `"${String(identifier).replace(/"/g, '""')}"`;
}

export function quoteSqlString(value) {
  return `'${String(value).replace(/'/g, "''")}'`;
}

export function formatBytes(bytes) {
  if (bytes < 1024) return `${bytes} B`;
  if (bytes < 1024 * 1024) return `${(bytes / 1024).toFixed(1)} KB`;
  return `${(bytes / (1024 * 1024)).toFixed(1)} MB`;
}

export function yieldToUi() {
  return new Promise((resolve) => setTimeout(resolve, 0));
}