// scripts/run-category-migration.ts
// Script to run only the categories migration on Neon PostgreSQL

import { Pool } from '@neondatabase/serverless';
import * as fs from 'fs';
import * as path from 'path';
import * as dotenv from 'dotenv';

// Load environment variables from .env.local
dotenv.config({ path: '.env.local' });

async function runCategoryMigration() {
  console.log('🚀 Starting categories migration...\n');

  // Check if DATABASE_URL exists
  if (!process.env.DATABASE_URL) {
    console.error('❌ ERROR: DATABASE_URL not found in environment variables');
    console.error('Please ensure .env.local file exists with DATABASE_URL');
    process.exit(1);
  }

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

  try {
    // Test connection
    console.log('📡 Testing database connection...');
    const testResult = await pool.query('SELECT version()');
    console.log('✅ Connected to PostgreSQL:', testResult.rows[0].version.split(' ')[0], testResult.rows[0].version.split(' ')[1]);
    console.log('');

    // Read migration file
    const migrationPath = path.join(process.cwd(), 'migrations', '003_create_categories_table.sql');

    console.log('📄 Reading migration file...');
    const migrationSQL = fs.readFileSync(migrationPath, 'utf-8');
    console.log('✅ Migration file loaded: 003_create_categories_table.sql');
    console.log('');

    // Run Migration 003
    console.log('🔨 Running Migration 003: Create categories table...');
    await pool.query(migrationSQL);
    console.log('✅ Migration 003 completed successfully');
    console.log('   - Created categories table');
    console.log('   - Created indexes on external_id and slug');
    console.log('   - Populated categories from existing blog_posts');
    console.log('');

    // Verify categories table
    console.log('🔍 Verifying categories table...');
    const columnCount = await pool.query(`
      SELECT COUNT(*) as count 
      FROM information_schema.columns 
      WHERE table_name = 'categories'
    `);
    console.log(`✅ categories table has ${columnCount.rows[0].count} columns`);

    // List all columns
    const columns = await pool.query(`
      SELECT column_name, data_type, is_nullable, column_default
      FROM information_schema.columns 
      WHERE table_name = 'categories'
      ORDER BY ordinal_position
    `);
    
    console.log('\n📋 Categories Table Structure:');
    console.log('─'.repeat(80));
    columns.rows.forEach((col, index) => {
      const nullable = col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL';
      const defaultVal = col.column_default ? ` DEFAULT ${col.column_default}` : '';
      console.log(`${(index + 1).toString().padStart(2)}. ${col.column_name.padEnd(25)} ${col.data_type.padEnd(20)} ${nullable}${defaultVal}`);
    });
    console.log('─'.repeat(80));

    // Check indexes
    const indexes = await pool.query(`
      SELECT indexname, indexdef
      FROM pg_indexes
      WHERE tablename = 'categories'
      ORDER BY indexname
    `);
    
    console.log(`\n🔑 Indexes Created: ${indexes.rows.length}`);
    console.log('─'.repeat(80));
    indexes.rows.forEach((idx, index) => {
      console.log(`${(index + 1).toString().padStart(2)}. ${idx.indexname}`);
    });
    console.log('─'.repeat(80));

    // Check populated categories
    const categoriesResult = await pool.query(`
      SELECT id, japanese, english, slug, icon
      FROM categories
      ORDER BY id
    `);
    
    console.log(`\n📊 Categories Populated: ${categoriesResult.rows.length}`);
    console.log('─'.repeat(80));
    categoriesResult.rows.forEach((cat, index) => {
      console.log(`${(index + 1).toString().padStart(2)}. ${cat.icon || '📝'} ${cat.japanese.padEnd(25)} (${cat.slug})`);
    });
    console.log('─'.repeat(80));

    console.log('\n' + '='.repeat(80));
    console.log('🎉 CATEGORIES MIGRATION COMPLETED SUCCESSFULLY!');
    console.log('='.repeat(80));
    console.log('\n✅ Categories table created and populated!');
    console.log(`✅ ${categoriesResult.rows.length} categories imported from blog_posts`);
    console.log('✅ Indexes created for performance');
    console.log('\n📝 Next steps:');
    console.log('   1. Test the sync API: POST /api/sync-categories');
    console.log('   2. Configure Bubble webhooks');
    console.log('   3. Verify frontend displays categories from database');
    console.log('');

  } catch (error) {
    console.error('\n❌ ERROR during migration:');
    console.error(error);
    process.exit(1);
  } finally {
    await pool.end();
  }
}

// Run migration
runCategoryMigration();
