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

import dotenv from 'dotenv';
import path from 'path';
import { Pool, PoolClient } from 'pg';

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

interface TestCompany {
  id: number;
  company_name: string;
  company_number?: string;
  phone?: string;
  is_active?: boolean;
  created_at?: Date;
  updated_at?: Date;
  synced_at?: Date;
  is_deleted?: boolean;
  deleted_at?: Date;
}

// Create a simple database client for testing
class TestDBClient {
  private pool: Pool;

  constructor() {
    // Use localhost when running outside Docker, postgres hostname is for Docker network
    const host = process.env.POSTGRES_HOST === 'postgres' ? 'localhost' : (process.env.POSTGRES_HOST || 'localhost');
    
    this.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,
    });
  }

  async query(text: string, params?: any[]) {
    return await this.pool.query(text, params);
  }

  async testConnection(): Promise<boolean> {
    try {
      await this.query('SELECT 1');
      return true;
    } catch (error) {
      console.error('Database connection test failed:', error);
      return false;
    }
  }

  async close() {
    await this.pool.end();
  }
}

async function testPostgresConnection() {
  console.log('🔍 Testing PostgreSQL Connection and CRUD Operations\n');
  
  // Debug: Check environment variables
  console.log('Environment variables:');
  console.log('  POSTGRES_HOST:', process.env.POSTGRES_HOST);
  console.log('  POSTGRES_PORT:', process.env.POSTGRES_PORT);
  console.log('  POSTGRES_DB:', process.env.POSTGRES_DB);
  console.log('  POSTGRES_USER:', process.env.POSTGRES_USER);
  console.log('  POSTGRES_PASSWORD:', process.env.POSTGRES_PASSWORD ? '[SET]' : '[NOT SET]');
  console.log('  Password type:', typeof process.env.POSTGRES_PASSWORD);
  console.log();
  
  const db = new TestDBClient();
  
  try {
    // Test 1: Connection Test
    console.log('Test 1: Connection Test');
    const isConnected = await db.testConnection();
    if (isConnected) {
      console.log('✅ Database connection successful\n');
    } else {
      console.log('❌ Database connection failed\n');
      return;
    }

    // Test 2: Insert Operation
    console.log('Test 2: Insert Operation');
    const insertResult = await db.query(
      `INSERT INTO companies (id, company_name, company_number, phone, is_active, synced_at, is_deleted)
       VALUES ($1, $2, $3, $4, $5, $6, $7)
       RETURNING *`,
      [999999, 'Test Company Inc', 'TEST-001', '555-0123', true, new Date(), false]
    );
    const inserted = insertResult.rows[0];
    console.log('✅ Insert successful:', inserted.company_name);
    console.log('   ID:', inserted.id, '\n');

    // Test 3: Find by ID
    console.log('Test 3: Find by ID');
    const findResult = await db.query(
      'SELECT * FROM companies WHERE id = $1 AND is_deleted = false',
      [999999]
    );
    const found = findResult.rows[0];
    if (found && found.company_name === 'Test Company Inc') {
      console.log('✅ Find by ID successful:', found.company_name, '\n');
    } else {
      console.log('❌ Find by ID failed\n');
    }

    // Test 4: Update Operation
    console.log('Test 4: Update Operation');
    const updateResult = await db.query(
      `UPDATE companies
       SET company_name = $1, phone = $2, updated_at = CURRENT_TIMESTAMP
       WHERE id = $3
       RETURNING *`,
      ['Test Company Updated', '555-9999', 999999]
    );
    const updated = updateResult.rows[0];
    console.log('✅ Update successful:', updated.company_name);
    console.log('   Phone:', updated.phone, '\n');

    // Test 5: Upsert Operation (Update existing)
    console.log('Test 5: Upsert Operation (Update existing)');
    const upsertResult1 = await db.query(
      `INSERT INTO companies (id, company_name, company_number, phone, is_active, synced_at, is_deleted)
       VALUES ($1, $2, $3, $4, $5, $6, $7)
       ON CONFLICT (id)
       DO UPDATE SET company_name = EXCLUDED.company_name, phone = EXCLUDED.phone, updated_at = CURRENT_TIMESTAMP
       RETURNING *`,
      [999999, 'Test Company Upserted', 'TEST-001', '555-8888', true, new Date(), false]
    );
    const upserted1 = upsertResult1.rows[0];
    console.log('✅ Upsert (update) successful:', upserted1.company_name, '\n');

    // Test 6: Upsert Operation (Insert new)
    console.log('Test 6: Upsert Operation (Insert new)');
    const upsertResult2 = await db.query(
      `INSERT INTO companies (id, company_name, company_number, phone, is_active, synced_at, is_deleted)
       VALUES ($1, $2, $3, $4, $5, $6, $7)
       ON CONFLICT (id)
       DO UPDATE SET company_name = EXCLUDED.company_name, phone = EXCLUDED.phone, updated_at = CURRENT_TIMESTAMP
       RETURNING *`,
      [999998, 'Test Company 2', 'TEST-002', '555-7777', true, new Date(), false]
    );
    const upserted2 = upsertResult2.rows[0];
    console.log('✅ Upsert (insert) successful:', upserted2.company_name, '\n');

    // Test 7: Find with criteria
    console.log('Test 7: Find with criteria');
    const findAllResult = await db.query(
      `SELECT * FROM companies
       WHERE is_active = true AND is_deleted = false
       ORDER BY company_name
       LIMIT 5`
    );
    const companies = findAllResult.rows;
    console.log(`✅ Find successful: Found ${companies.length} active companies`);
    companies.slice(0, 3).forEach((c: TestCompany) => {
      console.log(`   - ${c.company_name} (ID: ${c.id})`);
    });
    console.log();

    // Test 8: Count records
    console.log('Test 8: Count records');
    const countResult = await db.query(
      'SELECT COUNT(*) as count FROM companies WHERE is_active = true AND is_deleted = false'
    );
    const count = parseInt(countResult.rows[0].count);
    console.log(`✅ Count successful: ${count} active companies\n`);

    // Test 9: Soft Delete
    console.log('Test 9: Soft Delete');
    await db.query(
      `UPDATE companies
       SET is_deleted = true, deleted_at = CURRENT_TIMESTAMP
       WHERE id = $1`,
      [999999]
    );
    const deletedResult = await db.query(
      'SELECT * FROM companies WHERE id = $1',
      [999999]
    );
    const deletedRecord = deletedResult.rows[0];
    if (deletedRecord && deletedRecord.is_deleted) {
      console.log('✅ Soft delete successful');
      console.log('   Record marked as deleted:', deletedRecord.is_deleted);
      console.log('   Deleted at:', deletedRecord.deleted_at, '\n');
    } else {
      console.log('❌ Soft delete failed\n');
    }

    // Test 10: Verify soft-deleted record is excluded by default
    console.log('Test 10: Verify soft-deleted record excluded by default');
    const notFoundResult = await db.query(
      'SELECT * FROM companies WHERE id = $1 AND is_deleted = false',
      [999999]
    );
    if (notFoundResult.rows.length === 0) {
      console.log('✅ Soft-deleted record correctly excluded from default queries\n');
    } else {
      console.log('❌ Soft-deleted record should not be returned\n');
    }

    // Cleanup: Delete all test records
    console.log('Cleanup: Deleting test records');
    const testIds = [999999, 999998];
    await db.query(
      'DELETE FROM companies WHERE id = ANY($1::bigint[])',
      [testIds]
    );
    console.log('✅ Cleanup complete\n');

    console.log('🎉 All tests passed successfully!');
    
  } catch (error) {
    console.error('❌ Test failed with error:', error);
    throw error;
  } finally {
    // Close the connection pool
    await db.close();
    console.log('\n🔌 Database connection closed');
  }
}

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