/**
 * Test script for PostgreSQL connection and CRUD operations
 * Run with: npx tsx scripts/test-postgres.ts
 */

// Load environment variables from .env.local
import { config } from 'dotenv';
import { resolve } from 'path';

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

import { postgresClient } from '../lib/services/postgres-client';

interface TestCompany {
  id: number;
  name: string;
  phone?: string;
  address?: string;
  city?: string;
  state?: string;
  zip_code?: string;
  country?: string;
  website?: string;
  is_active: boolean;
  created_at?: Date;
  updated_at?: Date;
  synced_at?: Date;
  is_deleted: boolean;
  deleted_at?: Date;
}

async function runTests() {
  console.log('🧪 Starting PostgreSQL Connection and CRUD Tests\n');
  
  let testsPassed = 0;
  let testsFailed = 0;

  try {
    // Test 1: Connection Test
    console.log('Test 1: Testing database connection...');
    const isConnected = await postgresClient.testConnection();
    if (isConnected) {
      console.log('✅ Database connection successful\n');
      testsPassed++;
    } else {
      console.log('❌ Database connection failed\n');
      testsFailed++;
      return;
    }

    // Test 2: Insert Operation
    console.log('Test 2: Testing INSERT operation...');
    const testCompany = {
      id: 999999,
      name: 'Test Company Inc',
      phone: '555-0123',
      address: '123 Test Street',
      city: 'Test City',
      state: 'TS',
      zip_code: '12345',
      country: 'USA',
      website: 'https://testcompany.com',
      is_active: true,
      is_deleted: false,
      synced_at: new Date(),
    };

    const inserted = await postgresClient.insert<TestCompany>('companies', testCompany);
    if (inserted && inserted.id === testCompany.id) {
      console.log('✅ INSERT successful:', inserted.name);
      console.log(`   ID: ${inserted.id}, Name: ${inserted.name}\n`);
      testsPassed++;
    } else {
      console.log('❌ INSERT failed\n');
      testsFailed++;
    }

    // Test 3: Find by ID
    console.log('Test 3: Testing findById operation...');
    const found = await postgresClient.findById<TestCompany>('companies', 999999);
    if (found && found.name === 'Test Company Inc') {
      console.log('✅ findById successful:', found.name);
      console.log(`   Phone: ${found.phone}, City: ${found.city}\n`);
      testsPassed++;
    } else {
      console.log('❌ findById failed\n');
      testsFailed++;
    }

    // Test 4: Update Operation
    console.log('Test 4: Testing UPDATE operation...');
    const updated = await postgresClient.update<TestCompany>('companies', 999999, {
      name: 'Updated Test Company Inc',
      phone: '555-9999',
    });
    if (updated && updated.name === 'Updated Test Company Inc') {
      console.log('✅ UPDATE successful:', updated.name);
      console.log(`   New phone: ${updated.phone}\n`);
      testsPassed++;
    } else {
      console.log('❌ UPDATE failed\n');
      testsFailed++;
    }

    // Test 5: Upsert Operation (Update existing)
    console.log('Test 5: Testing UPSERT operation (update existing)...');
    const upserted1 = await postgresClient.upsert<TestCompany>('companies', {
      id: 999999,
      name: 'Upserted Test Company',
      phone: '555-8888',
      is_active: true,
      is_deleted: false,
      synced_at: new Date(),
    });
    if (upserted1 && upserted1.name === 'Upserted Test Company') {
      console.log('✅ UPSERT (update) successful:', upserted1.name);
      console.log(`   Phone: ${upserted1.phone}\n`);
      testsPassed++;
    } else {
      console.log('❌ UPSERT (update) failed\n');
      testsFailed++;
    }

    // Test 6: Upsert Operation (Insert new)
    console.log('Test 6: Testing UPSERT operation (insert new)...');
    const upserted2 = await postgresClient.upsert<TestCompany>('companies', {
      id: 999998,
      name: 'Another Test Company',
      phone: '555-7777',
      is_active: true,
      is_deleted: false,
      synced_at: new Date(),
    });
    if (upserted2 && upserted2.id === 999998) {
      console.log('✅ UPSERT (insert) successful:', upserted2.name);
      console.log(`   ID: ${upserted2.id}\n`);
      testsPassed++;
    } else {
      console.log('❌ UPSERT (insert) failed\n');
      testsFailed++;
    }

    // Test 7: Find with criteria
    console.log('Test 7: Testing find with criteria...');
    const foundCompanies = await postgresClient.find<TestCompany>('companies', {
      is_active: true,
    }, {
      limit: 5,
      orderBy: 'name ASC',
    });
    if (foundCompanies && foundCompanies.length > 0) {
      console.log(`✅ find successful: Found ${foundCompanies.length} active companies`);
      console.log(`   First company: ${foundCompanies[0].name}\n`);
      testsPassed++;
    } else {
      console.log('❌ find failed\n');
      testsFailed++;
    }

    // Test 8: Count operation
    console.log('Test 8: Testing count operation...');
    const count = await postgresClient.count('companies', { is_active: true });
    if (count >= 2) {
      console.log(`✅ count successful: ${count} active companies\n`);
      testsPassed++;
    } else {
      console.log('❌ count failed\n');
      testsFailed++;
    }

    // Test 9: Bulk insert
    console.log('Test 9: Testing bulk insert...');
    const bulkCompanies = [
      {
        id: 999997,
        name: 'Bulk Test Company 1',
        is_active: true,
        is_deleted: false,
        synced_at: new Date(),
      },
      {
        id: 999996,
        name: 'Bulk Test Company 2',
        is_active: true,
        is_deleted: false,
        synced_at: new Date(),
      },
    ];
    const bulkInserted = await postgresClient.bulkInsert('companies', bulkCompanies);
    if (bulkInserted === 2) {
      console.log(`✅ bulk insert successful: ${bulkInserted} records inserted\n`);
      testsPassed++;
    } else {
      console.log(`❌ bulk insert failed: Expected 2, got ${bulkInserted}\n`);
      testsFailed++;
    }

    // Test 10: Soft delete
    console.log('Test 10: Testing soft delete...');
    await postgresClient.softDelete('companies', 999999);
    const deletedCompany = await postgresClient.findById<TestCompany>('companies', 999999, false);
    const deletedCompanyWithDeleted = await postgresClient.findById<TestCompany>('companies', 999999, true);
    if (!deletedCompany && deletedCompanyWithDeleted && deletedCompanyWithDeleted.is_deleted) {
      console.log('✅ soft delete successful: Record marked as deleted');
      console.log(`   is_deleted: ${deletedCompanyWithDeleted.is_deleted}\n`);
      testsPassed++;
    } else {
      console.log('❌ soft delete failed\n');
      testsFailed++;
    }

    // Test 11: Transaction test
    console.log('Test 11: Testing transaction (rollback)...');
    try {
      await postgresClient.transaction(async (client) => {
        await client.query('INSERT INTO companies (id, name, is_active, is_deleted, synced_at) VALUES ($1, $2, $3, $4, $5)', 
          [999995, 'Transaction Test', true, false, new Date()]);
        // Force an error to test rollback
        throw new Error('Intentional error for rollback test');
      });
      console.log('❌ transaction rollback failed: Should have thrown error\n');
      testsFailed++;
    } catch (error) {
      // Check if the record was NOT inserted (rollback worked)
      const notInserted = await postgresClient.findById<TestCompany>('companies', 999995, true);
      if (!notInserted) {
        console.log('✅ transaction rollback successful: Record not inserted after error\n');
        testsPassed++;
      } else {
        console.log('❌ transaction rollback failed: Record was inserted\n');
        testsFailed++;
      }
    }

    // Test 12: Transaction test (commit)
    console.log('Test 12: Testing transaction (commit)...');
    await postgresClient.transaction(async (client) => {
      await client.query('INSERT INTO companies (id, name, is_active, is_deleted, synced_at) VALUES ($1, $2, $3, $4, $5)', 
        [999994, 'Transaction Commit Test', true, false, new Date()]);
    });
    const committed = await postgresClient.findById<TestCompany>('companies', 999994);
    if (committed && committed.name === 'Transaction Commit Test') {
      console.log('✅ transaction commit successful:', committed.name);
      console.log(`   ID: ${committed.id}\n`);
      testsPassed++;
    } else {
      console.log('❌ transaction commit failed\n');
      testsFailed++;
    }

    // Cleanup: Delete all test records
    console.log('Cleanup: Removing test records...');
    await postgresClient.query('DELETE FROM companies WHERE id >= 999994 AND id <= 999999');
    console.log('✅ Cleanup complete\n');

  } catch (error) {
    console.error('❌ Test suite error:', error);
    testsFailed++;
  }

  // Summary
  console.log('═══════════════════════════════════════');
  console.log('Test Summary:');
  console.log(`✅ Passed: ${testsPassed}`);
  console.log(`❌ Failed: ${testsFailed}`);
  console.log(`📊 Total: ${testsPassed + testsFailed}`);
  console.log('═══════════════════════════════════════\n');

  if (testsFailed === 0) {
    console.log('🎉 All tests passed!\n');
    process.exit(0);
  } else {
    console.log('⚠️  Some tests failed. Please review the output above.\n');
    process.exit(1);
  }
}

// Run tests
runTests().catch((error) => {
  console.error('Fatal error running tests:', error);
  process.exit(1);
});