import { createHash } from 'node:crypto';
import { readdir, readFile } from 'node:fs/promises';
import { dirname, resolve } from 'node:path';
import { fileURLToPath } from 'node:url';

import { config as loadEnvironment } from 'dotenv';
import { Client } from 'pg';
import type { ClientConfig } from 'pg';

const scriptDirectory = dirname(fileURLToPath(import.meta.url));
const backendDirectory = resolve(scriptDirectory, '..');
const migrationsDirectory = resolve(backendDirectory, 'migrations');

loadEnvironment({
  path: resolve(backendDirectory, '.env'),
  override: false,
  quiet: true,
});

type SslConfiguration = false | { rejectUnauthorized: boolean };

interface DatabaseSettings {
  targetDatabase: string;
  maintenanceDatabase: string;
  passwordForRedaction?: string;
  clientConfig(database?: string): ClientConfig;
}

interface Migration {
  version: string;
  fileName: string;
  checksum: string;
  sql: string;
}

interface AppliedMigration {
  version: string;
  name: string;
  checksum: string;
}

const trackingTableSql = `
  CREATE SCHEMA IF NOT EXISTS app_meta;
  CREATE TABLE IF NOT EXISTS app_meta.schema_migrations (
    version text PRIMARY KEY,
    name text NOT NULL UNIQUE,
    checksum text NOT NULL CHECK (checksum ~ '^[0-9a-f]{64}$'),
    applied_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
    execution_time_ms integer NOT NULL DEFAULT 0 CHECK (execution_time_ms >= 0)
  );
`;

function environmentValue(name: string): string | undefined {
  const value = process.env[name];
  return value === undefined || value === '' ? undefined : value;
}

function parsePort(value: string | undefined): number {
  if (value === undefined) {
    return 5432;
  }

  const port = Number(value);
  if (!Number.isInteger(port) || port < 1 || port > 65_535) {
    throw new Error('DATABASE_PORT must be an integer between 1 and 65535.');
  }

  return port;
}

function parseSsl(value: string | undefined): SslConfiguration {
  switch (value?.trim().toLowerCase()) {
    case undefined:
    case '':
    case 'false':
    case '0':
    case 'no':
    case 'disable':
      return false;
    case 'require':
    case 'no-verify':
      return { rejectUnauthorized: false };
    case 'true':
    case '1':
    case 'yes':
    case 'verify-full':
      return { rejectUnauthorized: true };
    default:
      throw new Error(
        'DATABASE_SSL must be false, true, disable, require, no-verify, or verify-full.',
      );
  }
}

function databaseNameFromUrl(connectionString: string): string {
  let parsedUrl: URL;

  try {
    parsedUrl = new URL(connectionString);
  } catch {
    throw new Error('DATABASE_URL is not a valid PostgreSQL URL.');
  }

  const databaseName = decodeURIComponent(parsedUrl.pathname.replace(/^\//, ''));
  if (databaseName === '') {
    throw new Error('DATABASE_URL must include a database name.');
  }

  return databaseName;
}

function connectionStringForDatabase(connectionString: string, database: string): string {
  const parsedUrl = new URL(connectionString);
  parsedUrl.pathname = `/${database}`;
  return parsedUrl.toString();
}

function readDatabaseSettings(): DatabaseSettings {
  const connectionString = environmentValue('DATABASE_URL');
  const ssl = parseSsl(environmentValue('DATABASE_SSL'));
  const maintenanceDatabase =
    environmentValue('DATABASE_MAINTENANCE_DB') ?? 'postgres';

  if (connectionString !== undefined) {
    const targetDatabase = databaseNameFromUrl(connectionString);
    const parsedUrl = new URL(connectionString);
    const passwordForRedaction = parsedUrl.password
      ? decodeURIComponent(parsedUrl.password)
      : undefined;

    return {
      targetDatabase,
      maintenanceDatabase,
      ...(passwordForRedaction === undefined ? {} : { passwordForRedaction }),
      clientConfig(database = targetDatabase): ClientConfig {
        return {
          connectionString: connectionStringForDatabase(connectionString, database),
          application_name: 'tonline-erp-migrations',
          ssl,
          connectionTimeoutMillis: 10_000,
        };
      },
    };
  }

  const password = environmentValue('DATABASE_PASSWORD');
  if (password === undefined) {
    throw new Error('DATABASE_PASSWORD is not set in backend/.env.');
  }

  const targetDatabase =
    environmentValue('DATABASE_NAME') ?? environmentValue('PGDATABASE') ?? 'tonline_erp_local';
  const host = environmentValue('DATABASE_HOST') ?? environmentValue('PGHOST') ?? '127.0.0.1';
  const port = parsePort(environmentValue('DATABASE_PORT') ?? environmentValue('PGPORT'));
  const user = environmentValue('DATABASE_USER') ?? environmentValue('PGUSER') ?? 'postgres';

  return {
    targetDatabase,
    maintenanceDatabase,
    passwordForRedaction: password,
    clientConfig(database = targetDatabase): ClientConfig {
      return {
        host,
        port,
        database,
        user,
        password,
        application_name: 'tonline-erp-migrations',
        ssl,
        connectionTimeoutMillis: 10_000,
      };
    },
  };
}

function postgresErrorCode(error: unknown): string | undefined {
  if (typeof error !== 'object' || error === null || !('code' in error)) {
    return undefined;
  }

  return typeof error.code === 'string' ? error.code : undefined;
}

function errorMessage(error: unknown): string {
  return error instanceof Error ? error.message : String(error);
}

function quoteIdentifier(identifier: string): string {
  return `"${identifier.replaceAll('"', '""')}"`;
}

async function closeAfterFailedConnection(client: Client): Promise<void> {
  try {
    await client.end();
  } catch {
    // The socket may already be closed after a connection failure.
  }
}

async function createMissingDatabase(settings: DatabaseSettings): Promise<void> {
  const maintenanceClient = new Client(
    settings.clientConfig(settings.maintenanceDatabase),
  );

  try {
    await maintenanceClient.connect();
    const existingDatabase = await maintenanceClient.query<{ exists: boolean }>(
      'SELECT EXISTS (SELECT 1 FROM pg_database WHERE datname = $1) AS exists',
      [settings.targetDatabase],
    );

    if (!existingDatabase.rows[0]?.exists) {
      await maintenanceClient.query(
        `CREATE DATABASE ${quoteIdentifier(settings.targetDatabase)} ` +
          "WITH ENCODING 'UTF8' TEMPLATE template0",
      );
      console.log(`Created PostgreSQL database "${settings.targetDatabase}".`);
    }
  } catch (error) {
    // Another runner can create the same database between the check and DDL.
    if (postgresErrorCode(error) !== '42P04') {
      throw new Error(
        `Database "${settings.targetDatabase}" does not exist and could not be created ` +
          `through "${settings.maintenanceDatabase}": ${errorMessage(error)}`,
        { cause: error },
      );
    }
  } finally {
    await maintenanceClient.end();
  }
}

async function connectToTargetDatabase(settings: DatabaseSettings): Promise<Client> {
  const client = new Client(settings.clientConfig());

  try {
    await client.connect();
    return client;
  } catch (error) {
    await closeAfterFailedConnection(client);

    if (postgresErrorCode(error) !== '3D000') {
      throw error;
    }
  }

  await createMissingDatabase(settings);

  const retryClient = new Client(settings.clientConfig());
  await retryClient.connect();
  return retryClient;
}

async function discoverMigrations(): Promise<Migration[]> {
  const entries = await readdir(migrationsDirectory, { withFileTypes: true });
  const sqlFiles = entries
    .filter((entry) => entry.isFile() && entry.name.toLowerCase().endsWith('.sql'))
    .map((entry) => entry.name);

  if (sqlFiles.length === 0) {
    throw new Error(`No SQL migrations found in ${migrationsDirectory}.`);
  }

  const migrations = await Promise.all(
    sqlFiles.map(async (fileName): Promise<Migration> => {
      const match = /^(\d+)[_-][a-z0-9][a-z0-9_-]*\.sql$/i.exec(fileName);
      if (match?.[1] === undefined) {
        throw new Error(
          `Invalid migration filename "${fileName}". Expected <version>_<name>.sql.`,
        );
      }

      const contents = await readFile(resolve(migrationsDirectory, fileName));
      const sql = contents.toString('utf8').replace(/^\uFEFF/, '');
      if (sql.trim() === '') {
        throw new Error(`Migration "${fileName}" is empty.`);
      }

      // Git can check out the same SQL as LF or CRLF. Normalize only for the
      // checksum so migration history remains portable between local and cPanel.
      const checksumSource = sql.replace(/\r\n?/g, '\n');

      return {
        version: match[1],
        fileName,
        checksum: createHash('sha256').update(checksumSource, 'utf8').digest('hex'),
        sql,
      };
    }),
  );

  migrations.sort((left, right) => {
    const byVersion = BigInt(left.version) - BigInt(right.version);
    if (byVersion < 0n) return -1;
    if (byVersion > 0n) return 1;
    return left.fileName.localeCompare(right.fileName);
  });

  const normalizedVersions = new Map<string, string>();
  for (const migration of migrations) {
    const normalizedVersion = BigInt(migration.version).toString();
    const duplicate = normalizedVersions.get(normalizedVersion);
    if (duplicate !== undefined) {
      throw new Error(
        `Migrations "${duplicate}" and "${migration.fileName}" use the same version.`,
      );
    }
    normalizedVersions.set(normalizedVersion, migration.fileName);
  }

  return migrations;
}

async function ensureTrackingTable(client: Client): Promise<void> {
  await client.query('BEGIN');
  try {
    await client.query(trackingTableSql);
    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  }
}

async function readAppliedMigrations(client: Client): Promise<AppliedMigration[]> {
  const result = await client.query<AppliedMigration>(
    `SELECT version, name, checksum
     FROM app_meta.schema_migrations
     ORDER BY version`,
  );
  return result.rows;
}

function verifyMigrationHistory(
  migrations: Migration[],
  appliedMigrations: AppliedMigration[],
): Map<string, AppliedMigration> {
  const availableByVersion = new Map(
    migrations.map((migration) => [migration.version, migration]),
  );
  const appliedByVersion = new Map<string, AppliedMigration>();

  for (const applied of appliedMigrations) {
    const available = availableByVersion.get(applied.version);
    if (available === undefined) {
      throw new Error(
        `Applied migration ${applied.version} (${applied.name}) is missing from disk.`,
      );
    }
    if (available.fileName !== applied.name) {
      throw new Error(
        `Applied migration ${applied.version} was renamed from "${applied.name}" ` +
          `to "${available.fileName}".`,
      );
    }
    if (available.checksum !== applied.checksum) {
      throw new Error(
        `Checksum mismatch for applied migration "${available.fileName}". ` +
          'Restore the originally applied file instead of editing migration history.',
      );
    }

    appliedByVersion.set(applied.version, applied);
  }

  return appliedByVersion;
}

async function applyMigration(client: Client, migration: Migration): Promise<void> {
  const startedAt = performance.now();
  await client.query('BEGIN');

  try {
    await client.query("SET LOCAL TIME ZONE 'UTC'");
    await client.query(migration.sql);

    const executionTimeMs = Math.max(
      0,
      Math.min(2_147_483_647, Math.round(performance.now() - startedAt)),
    );
    await client.query(
      `INSERT INTO app_meta.schema_migrations
        (version, name, checksum, execution_time_ms)
       VALUES ($1, $2, $3, $4)`,
      [migration.version, migration.fileName, migration.checksum, executionTimeMs],
    );
    await client.query('COMMIT');
    console.log(`Applied ${migration.fileName} (${executionTimeMs} ms).`);
  } catch (error) {
    await client.query('ROLLBACK');
    throw new Error(
      `Migration "${migration.fileName}" failed: ${errorMessage(error)}`,
      { cause: error },
    );
  }
}

function redact(message: string, settings?: DatabaseSettings): string {
  const secrets = [
    settings?.passwordForRedaction,
    environmentValue('DATABASE_PASSWORD'),
  ].filter((secret): secret is string => secret !== undefined && secret.length > 0);

  return secrets.reduce(
    (redacted, secret) => redacted.split(secret).join('[REDACTED]'),
    message,
  );
}

async function main(): Promise<void> {
  let settings: DatabaseSettings | undefined;
  let client: Client | undefined;
  let lockAcquired = false;

  try {
    settings = readDatabaseSettings();
    const migrations = await discoverMigrations();
    client = await connectToTargetDatabase(settings);
    await ensureTrackingTable(client);

    await client.query('SELECT pg_advisory_lock($1, $2)', [7249, 1]);
    lockAcquired = true;

    const appliedMigrations = await readAppliedMigrations(client);
    const appliedByVersion = verifyMigrationHistory(migrations, appliedMigrations);
    let appliedCount = 0;

    for (const migration of migrations) {
      if (appliedByVersion.has(migration.version)) {
        console.log(`Already applied ${migration.fileName}.`);
        continue;
      }

      await applyMigration(client, migration);
      appliedCount += 1;
    }

    if (appliedCount === 0) {
      console.log('Database schema is already up to date.');
    } else {
      console.log(`Migration complete: ${appliedCount} migration(s) applied.`);
    }
  } catch (error) {
    console.error(`Migration failed: ${redact(errorMessage(error), settings)}`);
    process.exitCode = 1;
  } finally {
    if (client !== undefined) {
      if (lockAcquired) {
        try {
          await client.query('SELECT pg_advisory_unlock($1, $2)', [7249, 1]);
        } catch {
          // Closing the connection also releases the session advisory lock.
        }
      }
      await client.end();
    }
  }
}

await main();