/**
 * Migration Runner
 * Executes selected SQL migrations in sequence
 * Usage: tsx scripts/run-migrations.ts
 */

import { readFileSync } from 'fs';
import { pgQuery } from '../lib/postgres/client';

const migrations = [
  {
    name: '025_bank_reconciliation_periods',
    path: './db/migrations/025_bank_reconciliation_periods.sql',
  },
  {
    name: '026_bank_transactions',
    path: './db/migrations/026_bank_transactions.sql',
  },
  {
    name: '027_ledger_transactions',
    path: './db/migrations/027_ledger_transactions.sql',
  },
  {
    name: '028_reconciliation_matches',
    path: './db/migrations/028_reconciliation_matches.sql',
  },
  {
    name: '029_bank_reconciliation_differences',
    path: './db/migrations/029_bank_reconciliation_differences.sql',
  },
  {
    name: '030_recurring_expenses',
    path: './db/migrations/030_recurring_expenses.sql',
  },
  {
    name: '031_expense_document_history',
    path: './db/migrations/031_expense_document_history.sql',
  },
];

async function runMigrations() {
  console.log('🚀 Starting SQL Migrations...\n');

  try {
    for (const migration of migrations) {
      console.log(`▶️  Running: ${migration.name}`);

      try {
        const sql = readFileSync(migration.path, 'utf-8');

        // Split by ; to handle multiple statements
        const statements = sql
          .split(';')
          .map((s) => s.trim())
          .filter((s) => s.length > 0);

        for (const statement of statements) {
          await pgQuery(statement, []);
        }

        console.log(`✅ ${migration.name} completed successfully\n`);
      } catch (error) {
        console.error(`❌ ${migration.name} failed:`);
        console.error(error instanceof Error ? error.message : error);
        throw error;
      }
    }

    // Verify key migrations
    console.log('📋 Verifying key tables created...\n');

    const result = await pgQuery<{ table_name: string }>(
      `
      SELECT table_name FROM information_schema.tables
      WHERE table_schema = 'public'
      AND table_name IN (
        'bank_reconciliation_periods',
        'bank_transactions',
        'ledger_transactions',
        'reconciliation_matches',
        'bank_reconciliation_differences',
        'recurring_expense_templates',
        'expense_documents',
        'expense_document_history',
        'expense_payments'
      )
      ORDER BY table_name
      `,
      []
    );

    if (result.rows.length === 9) {
      console.log('✅ All key tables created successfully:');
      result.rows.forEach((row) => {
        console.log(`   - ${row.table_name}`);
      });
    } else {
      console.warn(`⚠️  Expected 9 tables but found ${result.rows.length}`);
      result.rows.forEach((row) => {
        console.log(`   - ${row.table_name}`);
      });
    }

    console.log('\n✅ All migrations completed successfully!');
    console.log('\n📝 Next steps:');
    console.log('   1. Test data access layer with sample data');
    console.log('   2. Create test API requests');
    console.log('   3. Verify posting and payment workflows');

    process.exit(0);
  } catch (error) {
    console.error('\n❌ Migration failed. Please review errors above.');
    process.exit(1);
  }
}

runMigrations();
