import { existsSync } from "node:fs";
import { readdir, readFile } from "node:fs/promises";
import path from "node:path";
import { fileURLToPath } from "node:url";

import { pool } from "./pool.js";

const migrationDirectoryCandidates = [
  fileURLToPath(new URL("./migrations", import.meta.url)),
  fileURLToPath(new URL("../../src/db/migrations", import.meta.url))
];

const migrationsDirectory =
  migrationDirectoryCandidates.find((candidate) => existsSync(candidate)) ?? migrationDirectoryCandidates[0]!;

export const isMigrationFileName = (entry: string) => /^\d{3}[\w.-]*\.sql$/i.test(entry);

export const splitSqlStatements = (sql: string) => {
  const statements: string[] = [];
  let current = "";
  let index = 0;
  let singleQuote = false;
  let doubleQuote = false;
  let lineComment = false;
  let blockComment = false;
  let dollarTag: string | null = null;

  while (index < sql.length) {
    const char = sql[index];
    const next = sql[index + 1];

    if (lineComment) {
      current += char;

      if (char === "\n") {
        lineComment = false;
      }

      index += 1;
      continue;
    }

    if (blockComment) {
      current += char;

      if (char === "*" && next === "/") {
        current += next;
        blockComment = false;
        index += 2;
        continue;
      }

      index += 1;
      continue;
    }

    if (singleQuote) {
      current += char;

      if (char === "'" && next === "'") {
        current += next;
        index += 2;
        continue;
      }

      if (char === "'") {
        singleQuote = false;
      }

      index += 1;
      continue;
    }

    if (doubleQuote) {
      current += char;

      if (char === "\"" && next === "\"") {
        current += next;
        index += 2;
        continue;
      }

      if (char === "\"") {
        doubleQuote = false;
      }

      index += 1;
      continue;
    }

    if (dollarTag) {
      if (sql.startsWith(dollarTag, index)) {
        current += dollarTag;
        index += dollarTag.length;
        dollarTag = null;
        continue;
      }

      current += char;
      index += 1;
      continue;
    }

    if (char === "-" && next === "-") {
      current += char;
      current += next;
      lineComment = true;
      index += 2;
      continue;
    }

    if (char === "/" && next === "*") {
      current += char;
      current += next;
      blockComment = true;
      index += 2;
      continue;
    }

    if (char === "'") {
      current += char;
      singleQuote = true;
      index += 1;
      continue;
    }

    if (char === "\"") {
      current += char;
      doubleQuote = true;
      index += 1;
      continue;
    }

    if (char === "$") {
      const match = sql.slice(index).match(/^\$[A-Za-z_][A-Za-z0-9_]*?\$|^\$\$/);

      if (match) {
        dollarTag = match[0];
        current += dollarTag;
        index += dollarTag.length;
        continue;
      }
    }

    if (char === ";") {
      const statement = current.trim();

      if (statement.length > 0) {
        statements.push(statement);
      }

      current = "";
      index += 1;
      continue;
    }

    current += char;
    index += 1;
  }

  const remainder = current.trim();

  if (remainder.length > 0) {
    statements.push(remainder);
  }

  return statements;
};

export const runMigrations = async () => {
  await pool.query(`
    create table if not exists _migrations (
      id serial primary key,
      name text not null unique,
      executed_at timestamptz not null default now()
    )
  `);

  const entries = (await readdir(migrationsDirectory))
    .filter((entry) => isMigrationFileName(entry))
    .sort((left, right) => left.localeCompare(right));

  for (const entry of entries) {
    const alreadyApplied = await pool.query<{ name: string }>("select name from _migrations where name = $1", [entry]);

    if (alreadyApplied.rowCount) {
      continue;
    }

    const migrationPath = path.join(migrationsDirectory, entry);
    const sql = await readFile(migrationPath, "utf8");
    const statements = splitSqlStatements(sql);

    const client = await pool.connect();

    try {
      await client.query("begin");

      for (const statement of statements) {
        await client.query(statement);
      }

      await client.query("insert into _migrations (name) values ($1)", [entry]);
      await client.query("commit");
    } catch (error) {
      await client.query("rollback");
      throw error;
    } finally {
      client.release();
    }
  }
};

if (process.argv[1] && fileURLToPath(import.meta.url) === path.resolve(process.argv[1])) {
  runMigrations()
    .then(async () => {
      await pool.end();
    })
    .catch(async (error) => {
      console.error(error);
      await pool.end();
      process.exit(1);
    });
}