#!/usr/bin/env node

/**
 * Migration Script for Neon PostgreSQL
 * Runs migrations 009, 010, and 011 to create articles, categories, and newsletter_subscribers tables
 */

const { Client } = require('pg');
const fs = require('fs');
const path = require('path');

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

// Extract DATABASE_URL (handle double declaration if present)
let databaseUrl = process.env.DATABASE_URL;
if (databaseUrl && databaseUrl.includes('DATABASE_URL=')) {
  // Fix double declaration issue
  const match = databaseUrl.match(/postgresql:\/\/[^"]+/);
  if (match) {
    databaseUrl = match[0];
  }
}

console.log('🚀 Starting database migration...\n');

async function runMigrations() {
  const client = new Client({
    connectionString: databaseUrl,
    ssl: { rejectUnauthorized: false }
  });

  try {
    // Connect to database
    console.log('📡 Connecting to Neon PostgreSQL...');
    await client.connect();
    console.log('✅ Connected successfully!\n');

    // Migration files to run
    const migrations = [
      { file: '009_create_articles_table.sql', name: 'Articles Table' },
      { file: '010_create_categories_table.sql', name: 'Categories Table' },
      { file: '011_create_newsletter_subscribers_table.sql', name: 'Newsletter Subscribers Table' }
    ];

    // Run each migration
    for (const migration of migrations) {
      const filePath = path.join(__dirname, '..', 'migrations', migration.file);
      
      console.log(`📄 Running migration: ${migration.file}`);
      console.log(`   Creating: ${migration.name}`);
      
      const sql = fs.readFileSync(filePath, 'utf8');
      await client.query(sql);
      
      console.log(`✅ ${migration.name} created successfully!\n`);
    }

    // Verify tables were created
    console.log('🔍 Verifying tables...\n');
    
    const tablesQuery = `
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public' 
      AND table_name IN ('articles', 'categories', 'newsletter_subscribers')
      ORDER BY table_name;
    `;
    
    const tablesResult = await client.query(tablesQuery);
    console.log('📊 Created tables:');
    tablesResult.rows.forEach(row => {
      console.log(`   ✓ ${row.table_name}`);
    });
    console.log('');

    // Verify indexes
    console.log('🔍 Verifying indexes...\n');
    
    const indexesQuery = `
      SELECT 
        tablename,
        indexname
      FROM pg_indexes
      WHERE schemaname = 'public'
      AND tablename IN ('articles', 'categories', 'newsletter_subscribers')
      ORDER BY tablename, indexname;
    `;
    
    const indexesResult = await client.query(indexesQuery);
    
    const indexesByTable = {};
    indexesResult.rows.forEach(row => {
      if (!indexesByTable[row.tablename]) {
        indexesByTable[row.tablename] = [];
      }
      indexesByTable[row.tablename].push(row.indexname);
    });
    
    console.log('📊 Created indexes:');
    Object.keys(indexesByTable).sort().forEach(table => {
      console.log(`   ${table}: ${indexesByTable[table].length} indexes`);
      indexesByTable[table].forEach(index => {
        console.log(`      ✓ ${index}`);
      });
    });
    console.log('');

    // Verify categories were populated
    const categoriesCountQuery = 'SELECT COUNT(*) as count FROM categories;';
    const categoriesCount = await client.query(categoriesCountQuery);
    console.log(`📊 Categories populated: ${categoriesCount.rows[0].count} categories\n`);

    // Show category details
    const categoriesQuery = 'SELECT japanese, english, slug FROM categories ORDER BY id;';
    const categoriesResult = await client.query(categoriesQuery);
    console.log('📋 Category details:');
    categoriesResult.rows.forEach(cat => {
      console.log(`   ✓ ${cat.japanese} (${cat.english}) - slug: ${cat.slug}`);
    });
    console.log('');

    console.log('🎉 All migrations completed successfully!');
    console.log('\n📈 Summary:');
    console.log('   • Articles table: 41 fields, 10 indexes');
    console.log('   • Categories table: 8 fields, 2 indexes, 5 categories');
    console.log('   • Newsletter subscribers table: 5 fields, 2 indexes');

  } catch (error) {
    console.error('❌ Migration failed:', error.message);
    console.error('\nError details:', error);
    process.exit(1);
  } finally {
    await client.end();
    console.log('\n📡 Database connection closed.');
  }
}

// Run migrations
runMigrations();
