/**
 * Script pour vérifier la structure de la base de données
 * Usage: node scripts/check-database.js
 */
import { pool } from '../db.js';

async function checkDatabase() {
  try {
    console.log('🔍 Vérification de la base de données...\n');

    // 1. Vérifier la connexion
    console.log('📡 Test de connexion...');
    const connectionTest = await pool.query('SELECT NOW() as time, current_database() as database');
    console.log('✅ Connecté à:', connectionTest.rows[0].database);
    console.log('⏰ Heure serveur:', connectionTest.rows[0].time);
    console.log('');

    // 2. Lister toutes les tables
    console.log('📋 Tables disponibles:');
    const tablesQuery = `
      SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
      AND table_type = 'BASE TABLE'
      ORDER BY table_name;
    `;
    const tables = await pool.query(tablesQuery);

    if (tables.rows.length === 0) {
      console.log('⚠️  Aucune table trouvée. Exécutez: npm run db:schema');
      console.log('');
    } else {
      tables.rows.forEach((row, index) => {
        console.log(`   ${index + 1}. ${row.table_name}`);
      });
      console.log('');

      // 3. Compter les enregistrements dans chaque table
      console.log('📊 Nombre d\'enregistrements par table:');
      for (const table of tables.rows) {
        const countQuery = `SELECT COUNT(*) as count FROM ${table.table_name}`;
        const count = await pool.query(countQuery);
        console.log(`   ${table.table_name.padEnd(20)} : ${count.rows[0].count} enregistrements`);
      }
      console.log('');
    }

    // 4. Vérifier les types enum
    console.log('🏷️  Types ENUM disponibles:');
    const enumsQuery = `
      SELECT t.typname as enum_name,
             array_agg(e.enumlabel ORDER BY e.enumsortorder) as values
      FROM pg_type t
      JOIN pg_enum e ON t.oid = e.enumtypid
      JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
      WHERE n.nspname = 'public'
      GROUP BY t.typname
      ORDER BY t.typname;
    `;
    const enums = await pool.query(enumsQuery);

    if (enums.rows.length > 0) {
      enums.rows.forEach(row => {
        console.log(`   ${row.enum_name}: [${row.values.join(', ')}]`);
      });
      console.log('');
    }

    // 5. Vérifier les vues
    console.log('👁️  Vues disponibles:');
    const viewsQuery = `
      SELECT table_name
      FROM information_schema.views
      WHERE table_schema = 'public'
      ORDER BY table_name;
    `;
    const views = await pool.query(viewsQuery);

    if (views.rows.length === 0) {
      console.log('   Aucune vue trouvée');
      console.log('');
    } else {
      views.rows.forEach((row, index) => {
        console.log(`   ${index + 1}. ${row.table_name}`);
      });
      console.log('');
    }

    // 6. Afficher la structure de chaque table
    console.log('📐 Structure des tables:');
    console.log('='.repeat(80));

    for (const table of tables.rows) {
      const columnsQuery = `
        SELECT
          column_name,
          data_type,
          character_maximum_length,
          column_default,
          is_nullable,
          udt_name
        FROM information_schema.columns
        WHERE table_schema = 'public'
        AND table_name = $1
        ORDER BY ordinal_position;
      `;
      const columns = await pool.query(columnsQuery, [table.table_name]);

      console.log(`\n📄 Table: ${table.table_name}`);
      console.log('-'.repeat(80));

      columns.rows.forEach(col => {
        const type = col.udt_name === 'uuid' ? 'UUID' :
                     col.data_type === 'character varying' ? `VARCHAR(${col.character_maximum_length})` :
                     col.data_type === 'USER-DEFINED' ? col.udt_name :
                     col.data_type.toUpperCase();

        const nullable = col.is_nullable === 'NO' ? 'NOT NULL' : 'NULL';
        const defaultVal = col.column_default ? `DEFAULT ${col.column_default}` : '';

        console.log(`   • ${col.column_name.padEnd(25)} ${type.padEnd(20)} ${nullable.padEnd(10)} ${defaultVal}`);
      });
    }

    console.log('\n' + '='.repeat(80));
    console.log('\n✅ Vérification terminée!');

  } catch (error) {
    console.error('❌ Erreur:', error.message);
    if (error.code === 'ECONNREFUSED') {
      console.error('\n💡 La base de données n\'est pas accessible. Vérifiez:');
      console.error('   1. Que PostgreSQL est démarré');
      console.error('   2. Les paramètres de connexion dans .env');
      console.error('   3. Que le port 5433 est ouvert');
    }
  } finally {
    await pool.end();
  }
}

// Exécuter la vérification
checkDatabase();