// scripts/create-three-tables.ts
// Script to create the three specific tables: articles, categories, newsletter_subscribers

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 createTables() {
  console.log('🚀 Creating three database tables in Neon PostgreSQL...\n');

  // Extract DATABASE_URL (handle potential double declaration)
  let databaseUrl = process.env.DATABASE_URL;
  if (!databaseUrl) {
    console.error('❌ ERROR: DATABASE_URL not found in environment variables');
    process.exit(1);
  }

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

  const pool = new Pool({ connectionString: databaseUrl });

  try {
    // Test connection
    console.log('📡 Connecting to Neon PostgreSQL...');
    const testResult = await pool.query('SELECT version()');
    console.log('✅ Connected successfully!');
    console.log(`   PostgreSQL ${testResult.rows[0].version.split(' ')[1]}\n`);

    // Define the three migrations to run
    const migrations = [
      { file: '009_create_articles_table.sql', name: 'Articles Table', fields: 41, indexes: 10 },
      { file: '010_create_categories_table.sql', name: 'Categories Table', fields: 8, indexes: 2 },
      { file: '011_create_newsletter_subscribers_table.sql', name: 'Newsletter Subscribers Table', fields: 5, indexes: 2 }
    ];

    console.log('📋 Migrations to run:');
    migrations.forEach((m, i) => {
      console.log(`   ${i + 1}. ${m.name} (${m.fields} fields, ${m.indexes} indexes)`);
    });
    console.log('');

    // Run each migration
    for (const migration of migrations) {
      const migrationPath = path.join(process.cwd(), 'migrations', migration.file);
      
      if (!fs.existsSync(migrationPath)) {
        console.error(`❌ Migration file not found: ${migration.file}`);
        process.exit(1);
      }

      const migrationSQL = fs.readFileSync(migrationPath, 'utf-8');
      
      console.log(`🔨 Running ${migration.file}...`);
      console.log(`   Creating: ${migration.name}`);
      
      await pool.query(migrationSQL);
      
      console.log(`✅ ${migration.name} created successfully!\n`);
    }

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

    // Verify indexes for each table
    console.log('🔍 Verifying indexes...\n');
    
    for (const table of ['articles', 'categories', 'newsletter_subscribers']) {
      const indexesQuery = `
        SELECT indexname
        FROM pg_indexes
        WHERE schemaname = 'public'
        AND tablename = $1
        AND indexname NOT LIKE '%_pkey'
        ORDER BY indexname;
      `;
      
      const indexesResult = await pool.query(indexesQuery, [table]);
      console.log(`📊 ${table}: ${indexesResult.rows.length} indexes`);
      indexesResult.rows.forEach(row => {
        console.log(`      ✓ ${row.indexname}`);
      });
    }
    console.log('');

    // Verify categories were populated
    const categoriesCountQuery = 'SELECT COUNT(*) as count FROM categories;';
    const categoriesCount = await pool.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 pool.query(categoriesQuery);
    console.log('📋 Category details:');
    categoriesResult.rows.forEach(cat => {
      console.log(`   ✓ ${cat.japanese} (${cat.english})`);
      console.log(`     Slug: ${cat.slug}`);
    });
    console.log('');

    // Test Japanese character support
    console.log('🧪 Testing Japanese character support...\n');
    
    const testInsert = await pool.query(`
      INSERT INTO articles (
        title, slug, content, category, author, language, excerpt, is_active
      ) VALUES (
        'テスト記事 - メギド72攻略ガイド',
        'test-japanese-support-' || floor(random() * 1000000),
        '<p>これはテスト記事です。日本語の文字が正しく保存されるかテストしています。</p>',
        'ストーリー攻略',
        'テスト編集部',
        'ja',
        'テスト記事の説明文です。',
        true
      )
      RETURNING id, title, category, author, language
    `);
    
    console.log('✅ Test article inserted:');
    console.log(`   ID: ${testInsert.rows[0].id}`);
    console.log(`   Title: ${testInsert.rows[0].title}`);
    console.log(`   Category: ${testInsert.rows[0].category}`);
    console.log(`   Author: ${testInsert.rows[0].author}`);
    console.log(`   Language: ${testInsert.rows[0].language}`);
    
    // Clean up test article
    await pool.query('DELETE FROM articles WHERE id = $1', [testInsert.rows[0].id]);
    console.log('✅ Test article cleaned up\n');

    // Final summary
    console.log('═'.repeat(80));
    console.log('🎉 ALL TABLES CREATED SUCCESSFULLY!');
    console.log('═'.repeat(80));
    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 ✓');
    console.log('   • Japanese UTF-8 support: Verified ✓');
    console.log('   • All indexes created properly ✓');
    console.log('\n📝 Database is ready for use!');
    console.log('');

  } catch (error: any) {
    console.error('\n❌ ERROR during table creation:');
    console.error('Message:', error.message);
    if (error.code) {
      console.error('Code:', error.code);
    }
    if (error.detail) {
      console.error('Detail:', error.detail);
    }
    process.exit(1);
  } finally {
    await pool.end();
    console.log('📡 Database connection closed.\n');
  }
}

// Run the script
createTables();
