/**
 * Check if time_entries table exists and apply migration if needed
 * Run with: npx tsx dev/check-time-entries-table.ts
 */

import dotenv from 'dotenv';
import path from 'path';
import { Pool } from 'pg';
import fs from 'fs';

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

async function checkAndCreateTimeEntriesTable() {
  console.log('🔍 Checking time_entries table status\n');
  
  // Create database connection
  const host = process.env.POSTGRES_HOST === 'postgres' ? 'localhost' : (process.env.POSTGRES_HOST || 'localhost');
  const pool = new Pool({
    host,
    port: parseInt(process.env.POSTGRES_PORT || '5432'),
    database: process.env.POSTGRES_DB || 'pulse_autotask',
    user: process.env.POSTGRES_USER || 'pulse_user',
    password: process.env.POSTGRES_PASSWORD,
    max: 10,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
  });

  try {
    // Check if table exists
    console.log('Checking if time_entries table exists...');
    const tableCheckResult = await pool.query(`
      SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_name = 'time_entries'
      );
    `);
    
    const tableExists = tableCheckResult.rows[0].exists;
    
    if (tableExists) {
      console.log('✅ time_entries table already exists\n');
      
      // Check table structure
      console.log('Checking table structure...');
      const columnsResult = await pool.query(`
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_name = 'time_entries'
        ORDER BY ordinal_position;
      `);
      
      console.log(`Found ${columnsResult.rows.length} columns:`);
      columnsResult.rows.slice(0, 10).forEach((col: any) => {
        console.log(`  - ${col.column_name}: ${col.data_type} (nullable: ${col.is_nullable})`);
      });
      
      // Check foreign key constraints
      console.log('\nChecking foreign key constraints...');
      const fkResult = await pool.query(`
        SELECT
          tc.constraint_name,
          kcu.column_name,
          ccu.table_name AS foreign_table_name,
          ccu.column_name AS foreign_column_name
        FROM information_schema.table_constraints AS tc
        JOIN information_schema.key_column_usage AS kcu
          ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage AS ccu
          ON ccu.constraint_name = tc.constraint_name
        WHERE tc.constraint_type = 'FOREIGN KEY'
          AND tc.table_name = 'time_entries';
      `);
      
      console.log(`Found ${fkResult.rows.length} foreign key constraints:`);
      fkResult.rows.forEach((fk: any) => {
        console.log(`  - ${fk.column_name} -> ${fk.foreign_table_name}(${fk.foreign_column_name})`);
      });
      
      // Check for problematic foreign keys
      const problematicTables = ['contract_services', 'contract_service_bundles', 'roles', 'locations', 'allocation_codes'];
      const problematicFKs = fkResult.rows.filter((fk: any) => 
        problematicTables.includes(fk.foreign_table_name)
      );
      
      if (problematicFKs.length > 0) {
        console.log('\n⚠️  WARNING: Found foreign keys to non-existent tables:');
        problematicFKs.forEach((fk: any) => {
          console.log(`  - ${fk.constraint_name}: ${fk.column_name} -> ${fk.foreign_table_name}`);
        });
        console.log('\nThese constraints will cause sync failures. Dropping them...');
        
        for (const fk of problematicFKs) {
          try {
            await pool.query(`ALTER TABLE time_entries DROP CONSTRAINT IF EXISTS ${fk.constraint_name};`);
            console.log(`  ✅ Dropped constraint: ${fk.constraint_name}`);
          } catch (error) {
            console.error(`  ❌ Failed to drop ${fk.constraint_name}:`, error);
          }
        }
      } else {
        console.log('✅ No problematic foreign key constraints found');
      }
      
    } else {
      console.log('❌ time_entries table does not exist\n');
      console.log('Reading migration file...');
      
      const migrationPath = path.resolve(__dirname, '../migrations/006_add_time_entries_table.sql');
      const migrationSQL = fs.readFileSync(migrationPath, 'utf-8');
      
      console.log('Applying migration...');
      await pool.query(migrationSQL);
      
      console.log('✅ Migration applied successfully\n');
    }
    
    // Test a simple query
    console.log('\nTesting query on time_entries table...');
    const countResult = await pool.query('SELECT COUNT(*) as count FROM time_entries;');
    console.log(`✅ Query successful: ${countResult.rows[0].count} time entries in database\n`);
    
    console.log('🎉 All checks passed!');
    
  } catch (error) {
    console.error('❌ Error:', error);
    throw error;
  } finally {
    await pool.end();
    console.log('\n🔌 Database connection closed');
  }
}

// Run the check
checkAndCreateTimeEntriesTable()
  .then(() => {
    console.log('\n✅ Script completed successfully');
    process.exit(0);
  })
  .catch((error) => {
    console.error('\n❌ Script failed:', error);
    process.exit(1);
  });