knowledge/tests/force-migrate.ts

import 'dotenv/config';
import { Pool } from 'pg';
import fs from 'fs';
import path from 'path';

async function forceMigrate() {
  const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
  });

  console.log('🚀 Starting Force Migration (Manual SQL Execution)...');

  const migrationDir = path.join(process.cwd(), 'drizzle');
  const files = fs.readdirSync(migrationDir)
    .filter(file => file.endsWith('.sql'))
    .sort();

  console.log('Found migration files:', files);

  for (const file of files) {
    const filePath = path.join(migrationDir, file);
    const sql = fs.readFileSync(filePath, 'utf8');

    console.log(`\n--- Executing: ${file} ---`);
    try {
      // We split by semicolon to execute statements one by one for better error granularity
      // Note: This is a simple splitter and might not handle complex multi-line statements perfectly,
      // but for standard Drizzle migrations it usually works.
      const statements = sql.split(';').map(s => s.trim()).filter(s => s.length > 0);

      for (const statement of statements) {
        console.log(`Executing: ${statement.substring(0, 50)}${statement.length > 50 ? '...' : ''}`);
        await pool.query(statement);
      }
      console.log(`✅ Successfully applied ${file}`);
    } catch (err: any) {
      console.error(`❌ Failed executing ${file}`);
      console.error('Error Message:', err.message);
      console.error('Error Code:', err.code);
      if (err.detail) console.error('Detail:', err.detail);
      if (err.hint) console.error('Hint:', err.hint);
      // We stop on error to prevent cascading failures
      process.exit(1);
    }
  }

  console.log('\n✨ All migrations applied successfully!');
  await pool.end();
}

forceMigrate();