// scripts/run-remove-icon-migration.ts
// Script to remove icon column from categories table

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 removeIconColumn() {
  console.log('🚀 Starting migration to remove icon column...\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', '004_remove_icon_from_categories.sql');

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

    // Check if icon column exists
    console.log('🔍 Checking if icon column exists...');
    const columnCheck = await pool.query(`
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name = 'categories' AND column_name = 'icon'
    `);

    if (columnCheck.rows.length === 0) {
      console.log('ℹ️  Icon column does not exist. Nothing to do.');
      console.log('');
      return;
    }

    console.log('✅ Icon column found. Proceeding with removal...');
    console.log('');

    // Run Migration 004
    console.log('🔨 Running Migration 004: Remove icon column...');
    await pool.query(migrationSQL);
    console.log('✅ Migration 004 completed successfully');
    console.log('   - Dropped icon column from categories table');
    console.log('');

    // Verify icon column is removed
    console.log('🔍 Verifying icon column removal...');
    const verifyCheck = await pool.query(`
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name = 'categories' AND column_name = 'icon'
    `);

    if (verifyCheck.rows.length === 0) {
      console.log('✅ Icon column successfully removed');
    } else {
      console.log('⚠️  Warning: Icon column still exists');
    }
    console.log('');

    // Show current table structure
    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('📋 Updated 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));

    console.log('\n' + '='.repeat(80));
    console.log('🎉 ICON COLUMN REMOVAL COMPLETED SUCCESSFULLY!');
    console.log('='.repeat(80));
    console.log('\n✅ Icon column removed from categories table');
    console.log('✅ Table structure updated');
    console.log('\n📝 Next steps:');
    console.log('   1. Verify the change in Neon dashboard');
    console.log('   2. Test the API endpoints');
    console.log('   3. Commit and push the changes');
    console.log('');

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

// Run migration
removeIconColumn();
