// Runtime migration script — reads the drizzle journal and executes pending
// SQL migrations directly via pg, bypassing the drizzle-orm migrator module
// which may not be shipped in all drizzle-orm builds.

import pg from 'pg';
import { readFileSync } from 'node:fs';

const MIGRATIONS_DIR = './drizzle';
const MIGRATION_TABLE = '__drizzle_migrations';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

try {
  const client = await pool.connect();

  // Ensure migration tracking table exists
  await client.query(`
    CREATE TABLE IF NOT EXISTS ${MIGRATION_TABLE} (
      id SERIAL PRIMARY KEY,
      tag TEXT NOT NULL UNIQUE,
      created_at BIGINT NOT NULL
    )
  `);

  // Load journal
  const journal = JSON.parse(readFileSync(`${MIGRATIONS_DIR}/meta/_journal.json`, 'utf8'));

  // Find already-applied migrations
  const { rows: applied } = await client.query(`SELECT tag FROM ${MIGRATION_TABLE}`);
  const appliedTags = new Set(applied.map(r => r.tag));

  let count = 0;
  for (const entry of journal.entries) {
    if (appliedTags.has(entry.tag)) continue;

    const sqlFile = `${MIGRATIONS_DIR}/${entry.tag}.sql`;
    let sql;
    try {
      sql = readFileSync(sqlFile, 'utf8');
    } catch {
      console.warn(`Migration file not found: ${sqlFile}, skipping.`);
      continue;
    }

    // Split on drizzle statement breakpoints and execute each statement
    const statements = sql.split('--> statement-breakpoint')
      .map(s => s.trim())
      .filter(Boolean);

    console.log(`Applying migration: ${entry.tag} (${statements.length} statements)`);

    await client.query('BEGIN');
    try {
      for (const stmt of statements) {
        await client.query(stmt);
      }
      await client.query(
        `INSERT INTO ${MIGRATION_TABLE} (tag, created_at) VALUES ($1, $2)`,
        [entry.tag, entry.when],
      );
      await client.query('COMMIT');
      count++;
    } catch (err) {
      await client.query('ROLLBACK');
      throw new Error(`Migration ${entry.tag} failed: ${err.message}`);
    }
  }

  client.release();
  console.log(count > 0 ? `Applied ${count} migration(s).` : 'No pending migrations.');
} catch (err) {
  console.error('Migration failed:', err);
  process.exit(1);
} finally {
  await pool.end();
}