// scripts/run-newsletter-migration.ts
// Script to run only the newsletter_subscribers migration

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 runNewsletterMigration() {
  console.log('🚀 Running newsletter_subscribers migration...\n');

  // Check if DATABASE_URL exists
  if (!process.env.DATABASE_URL) {
    console.error('❌ ERROR: DATABASE_URL not found in environment variables');
    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');
    console.log('');

    // Check if table already exists
    const tableCheck = await pool.query(`
      SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_name = 'newsletter_subscribers'
      );
    `);

    if (tableCheck.rows[0].exists) {
      console.log('✅ newsletter_subscribers table already exists');
      
      // Verify structure
      const columns = await pool.query(`
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns 
        WHERE table_name = 'newsletter_subscribers'
        ORDER BY ordinal_position
      `);
      
      console.log('\n📋 Table Structure:');
      columns.rows.forEach((col) => {
        const nullable = col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL';
        console.log(`   - ${col.column_name.padEnd(20)} ${col.data_type.padEnd(20)} ${nullable}`);
      });

      // Check indexes
      const indexes = await pool.query(`
        SELECT indexname
        FROM pg_indexes
        WHERE tablename = 'newsletter_subscribers'
        ORDER BY indexname
      `);
      
      console.log(`\n🔑 Indexes: ${indexes.rows.length}`);
      indexes.rows.forEach((idx) => {
        console.log(`   - ${idx.indexname}`);
      });

    } else {
      console.log('📄 Table does not exist, running migration...');
      
      // Read and run migration
      const migrationPath = path.join(process.cwd(), 'migrations', '005_create_newsletter_subscribers.sql');
      const migrationSQL = fs.readFileSync(migrationPath, 'utf-8');
      
      console.log('🔨 Creating newsletter_subscribers table...');
      await pool.query(migrationSQL);
      console.log('✅ Migration completed successfully');
      
      // Verify
      const columns = await pool.query(`
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns 
        WHERE table_name = 'newsletter_subscribers'
        ORDER BY ordinal_position
      `);
      
      console.log('\n📋 Table Structure:');
      columns.rows.forEach((col) => {
        const nullable = col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL';
        console.log(`   - ${col.column_name.padEnd(20)} ${col.data_type.padEnd(20)} ${nullable}`);
      });
    }

    console.log('\n✅ Newsletter migration check completed!');

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

// Run migration
runNewsletterMigration();
