import crypto from 'crypto';
import { promises as fs } from 'fs';
import path from 'path';
import type { PoolClient } from 'pg';
import pool from './index';

const MIGRATION_TABLE = 'rm_schema_migrations';
const MIGRATION_LOCK_ID = '74392018455123001';
const VERSIONED_MIGRATION_RE = /^\d{4}-\d{2}-\d{2}-\d{2}-.+\.sql$/;
const BENIGN_EXISTING_OBJECT_ERROR_CODES = new Set(['42P07', '42701', '42710']);

export type MigrationRecord = {
  filename: string;
  checksum: string;
  status: 'applied' | 'assumed_applied';
  appliedAt: string;
  executionMs: number | null;
  note: string | null;
  errorCode: string | null;
};

export type MigrationRunResult = {
  applied: MigrationRecord[];
  skipped: string[];
  migrationDir: string;
};

export type RunPendingMigrationsOptions = {
  baselineUntracked?: boolean;
};

function resolveMigrationsDir(): string {
  return path.resolve(process.env.DB_MIGRATIONS_DIR || path.join(process.cwd(), 'src/db/migrations'));
}

function buildChecksum(sql: string): string {
  return crypto.createHash('sha256').update(sql).digest('hex');
}

function toErrorCode(error: unknown): string | null {
  if (!error || typeof error !== 'object') return null;
  const code = (error as { code?: unknown }).code;
  return typeof code === 'string' ? code : null;
}

function isBenignExistingObjectError(error: unknown): boolean {
  const code = toErrorCode(error);
  return code !== null && BENIGN_EXISTING_OBJECT_ERROR_CODES.has(code);
}

async function ensureMigrationTable(client: PoolClient): Promise<void> {
  await client.query(`
    CREATE TABLE IF NOT EXISTS ${MIGRATION_TABLE} (
      filename TEXT PRIMARY KEY,
      checksum TEXT NOT NULL,
      status TEXT NOT NULL CHECK (status IN ('applied', 'assumed_applied')),
      applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      execution_ms INTEGER,
      note TEXT,
      error_code TEXT
    )
  `);
}

async function listVersionedMigrationFiles(migrationDir: string): Promise<string[]> {
  const entries = await fs.readdir(migrationDir, { withFileTypes: true });
  return entries
    .filter((entry) => entry.isFile() && VERSIONED_MIGRATION_RE.test(entry.name))
    .map((entry) => entry.name)
    .sort((a, b) => a.localeCompare(b));
}

async function loadAppliedMigrations(client: PoolClient): Promise<Map<string, MigrationRecord>> {
  const { rows } = await client.query(
    `SELECT filename, checksum, status, applied_at, execution_ms, note, error_code
     FROM ${MIGRATION_TABLE}
     ORDER BY filename ASC`,
  );
  return new Map(
    rows.map((row) => [
      row.filename as string,
      {
        filename: row.filename as string,
        checksum: row.checksum as string,
        status: row.status as 'applied' | 'assumed_applied',
        appliedAt: String(row.applied_at),
        executionMs: typeof row.execution_ms === 'number' ? row.execution_ms : null,
        note: typeof row.note === 'string' ? row.note : null,
        errorCode: typeof row.error_code === 'string' ? row.error_code : null,
      },
    ]),
  );
}

async function recordAppliedMigration(
  client: PoolClient,
  record: Omit<MigrationRecord, 'appliedAt'>,
): Promise<MigrationRecord> {
  const { rows } = await client.query(
    `INSERT INTO ${MIGRATION_TABLE} (filename, checksum, status, execution_ms, note, error_code)
     VALUES ($1, $2, $3, $4, $5, $6)
     RETURNING filename, checksum, status, applied_at, execution_ms, note, error_code`,
    [record.filename, record.checksum, record.status, record.executionMs, record.note, record.errorCode],
  );
  const row = rows[0];
  return {
    filename: row.filename as string,
    checksum: row.checksum as string,
    status: row.status as 'applied' | 'assumed_applied',
    appliedAt: String(row.applied_at),
    executionMs: typeof row.execution_ms === 'number' ? row.execution_ms : null,
    note: typeof row.note === 'string' ? row.note : null,
    errorCode: typeof row.error_code === 'string' ? row.error_code : null,
  };
}

async function applyMigrationFile(
  client: PoolClient,
  migrationDir: string,
  filename: string,
): Promise<MigrationRecord> {
  const fullPath = path.join(migrationDir, filename);
  const sql = await fs.readFile(fullPath, 'utf8');
  const checksum = buildChecksum(sql);
  const startedAt = Date.now();

  try {
    await client.query(sql);
    return await recordAppliedMigration(client, {
      filename,
      checksum,
      status: 'applied',
      executionMs: Date.now() - startedAt,
      note: null,
      errorCode: null,
    });
  } catch (error) {
    if (!isBenignExistingObjectError(error)) {
      throw error;
    }

    return await recordAppliedMigration(client, {
      filename,
      checksum,
      status: 'assumed_applied',
      executionMs: Date.now() - startedAt,
      note: error instanceof Error ? error.message : 'Existing schema object detected',
      errorCode: toErrorCode(error),
    });
  }
}

export async function runPendingMigrations(
  options: RunPendingMigrationsOptions = {},
): Promise<MigrationRunResult> {
  const migrationDir = resolveMigrationsDir();
  const client = await pool.connect();

  try {
    await fs.access(migrationDir);
    await client.query('SELECT pg_advisory_lock($1::bigint)', [MIGRATION_LOCK_ID]);
    await ensureMigrationTable(client);

    const files = await listVersionedMigrationFiles(migrationDir);
    const applied = await loadAppliedMigrations(client);
    const appliedNow: MigrationRecord[] = [];
    const skipped: string[] = [];

    for (const filename of files) {
      const fullPath = path.join(migrationDir, filename);
      const sql = await fs.readFile(fullPath, 'utf8');
      const checksum = buildChecksum(sql);
      const existing = applied.get(filename);

      if (existing) {
        if (existing.checksum !== checksum) {
          throw new Error(
            `Migration checksum mismatch for ${filename}. Applied=${existing.checksum} current=${checksum}`,
          );
        }
        skipped.push(filename);
        continue;
      }

      const result = options.baselineUntracked
        ? await recordAppliedMigration(client, {
            filename,
            checksum,
            status: 'assumed_applied',
            executionMs: null,
            note: 'Baselined on legacy database without execution',
            errorCode: null,
          })
        : await applyMigrationFile(client, migrationDir, filename);
      appliedNow.push(result);
      console.log(
        `[db:migrate] ${result.status === 'applied' ? 'applied' : 'assumed applied'} ${filename}` +
          (result.errorCode ? ` (${result.errorCode})` : ''),
      );
    }

    return {
      applied: appliedNow,
      skipped,
      migrationDir,
    };
  } finally {
    try {
      await client.query('SELECT pg_advisory_unlock($1::bigint)', [MIGRATION_LOCK_ID]);
    } catch {
      // Unlock best-effort only.
    }
    client.release();
  }
}

async function main(): Promise<void> {
  const baselineUntracked = process.argv.includes('--baseline-untracked')
    || process.env.DB_MIGRATIONS_BASELINE_UNTRACKED === 'true';
  const result = await runPendingMigrations({ baselineUntracked });
  console.log(
    `[db:migrate] complete: applied=${result.applied.length} skipped=${result.skipped.length} baseline=${baselineUntracked} dir=${result.migrationDir}`,
  );
  await pool.end();
}

if (require.main === module) {
  main().catch(async (error) => {
    console.error('[db:migrate] fatal:', error);
    try {
      await pool.end();
    } catch {
      // Ignore shutdown errors.
    }
    process.exit(1);
  });
}