#!/usr/bin/env node
import { Command } from '@commander-js/extra-typings';
import { Pool } from 'pg';
import * as Poker from '../';

const program = new Command()
  .name('test-stats')
  .description('Test that poker_stats table has values for all HEADERS columns')
  .option(
    '-c, --connection <string>',
    'PostgreSQL connection string (e.g., postgresql://user:pass@host:port/db)',
    process.env.DATABASE_URL
  )
  .option('-t, --table <string>', 'Table name to check', 'poker_stats')
  .option('-l, --limit <number>', 'Maximum number of rows to check per column', parseInt, 1000)
  .option('-v, --verbose', 'Show detailed output')
  .version('1.0.0');

program.parse();

const options = program.opts();

if (!options.connection) {
  console.error('Error: PostgreSQL connection string is required');
  console.error('Use -c option or set DATABASE_URL environment variable');
  process.exit(1);
}

async function testStats() {
  const HEADERS = Poker.Stats.getColumnNames();
  const pool = new Pool({ connectionString: options.connection });

  try {
    console.log(`Connecting to database...`);
    const client = await pool.connect();

    try {
      // First, check if table exists
      const tableCheckResult = await client.query(
        `
        SELECT EXISTS (
          SELECT FROM information_schema.tables
          WHERE table_name = $1
        )
      `,
        [options.table]
      );

      if (!tableCheckResult.rows[0].exists) {
        console.error(`Error: Table '${options.table}' does not exist`);
        process.exit(1);
      }

      console.log(`Table '${options.table}' exists ✓`);

      // Get actual columns in the table
      const columnsResult = await client.query(
        `
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = $1
        ORDER BY ordinal_position
      `,
        [options.table]
      );

      const actualColumns = new Set(columnsResult.rows.map(row => row.column_name));
      console.log(`Found ${actualColumns.size} columns in table`);

      // Check which expected columns are missing from table schema
      const missingColumns: string[] = [];
      const presentColumns: string[] = [];

      for (const snakeCase of HEADERS) {
        if (actualColumns.has(snakeCase)) {
          presentColumns.push(snakeCase);
        } else {
          missingColumns.push(snakeCase);
        }
      }

      if (missingColumns.length > 0) {
        console.log(`\nMissing columns in table schema:`);
        missingColumns.forEach(col => console.log(`  ❌ ${col}`));
      }

      if (presentColumns.length > 0) {
        console.log(`\nColumns present in table: ${presentColumns.length}/${HEADERS.length}`);

        // Now check for non-zero values in present columns
        console.log(
          `\nChecking for non-zero values (checking first ${options.limit} rows per column)...`
        );

        const columnsWithValues: Map<string, number> = new Map();
        const columnsAllZero: string[] = [];

        for (const column of presentColumns) {
          // Skip non-numeric columns
          const nonNumericColumns = [
            'created_at',
            'venue',
            'table',
            'street',
            'player',
            'currency',
          ];
          if (nonNumericColumns.includes(column)) {
            const query = `
              SELECT COUNT(*) as non_null_count
              FROM (
                SELECT "${column}"
                FROM ${options.table}
                WHERE "${column}" IS NOT NULL
                LIMIT $1
              ) sub
            `;
            try {
              const result = await client.query(query, [options.limit]);
              const count = parseInt(result.rows[0].non_null_count);
              if (count > 0) {
                columnsWithValues.set(column, count);
              }
              if (options.verbose) {
                console.log(`  ${column}: ✓ (${count} non-null) [non-numeric]`);
              }
            } catch (err) {
              console.error(`  Error checking column ${column}: ${err}`);
            }
            continue;
          }

          const query = `
            SELECT COUNT(*) as non_zero_count
            FROM (
              SELECT "${column}"
              FROM ${options.table}
              WHERE "${column}" IS NOT NULL AND "${column}" != 0
              LIMIT $1
            ) sub
          `;

          try {
            const result = await client.query(query, [options.limit]);
            const count = parseInt(result.rows[0].non_zero_count);

            if (count > 0) {
              columnsWithValues.set(column, count);
            } else {
              columnsAllZero.push(column);
            }

            if (options.verbose) {
              console.log(`  ${column}: ${count > 0 ? `✓ (${count} non-zero)` : '✗ (all zero)'}`);
            }
          } catch (err) {
            console.error(`  Error checking column ${column}: ${err}`);
          }
        }

        // Summary
        console.log(`\n=== SUMMARY ===`);
        console.log(`Total expected columns: ${HEADERS.length}`);
        console.log(`Columns in table: ${presentColumns.length}`);
        console.log(`Missing columns: ${missingColumns.length}`);
        console.log(`Columns with non-zero values: ${columnsWithValues.size}`);
        console.log(`Columns all zero: ${columnsAllZero.length}`);

        if (columnsAllZero.length > 0) {
          console.log(`\nColumns with no non-zero values (all zero or NULL):`);
          columnsAllZero.forEach(col => {
            const camelCase = HEADERS.find(h => h === col) || col;
            console.log(`  ⚠️  ${col} (${camelCase})`);
          });
        }

        // Check total row count
        const countResult = await client.query(`SELECT COUNT(*) FROM ${options.table}`);
        const totalRows = parseInt(countResult.rows[0].count);
        console.log(`\nTotal rows in table: ${totalRows.toLocaleString()}`);

        // Show sample of data if verbose
        if (options.verbose && totalRows > 0) {
          console.log(`\nSample row (first non-null values):`);

          const sampleQuery = `
            SELECT ${presentColumns.map(c => `"${c}"`).join(', ')}
            FROM ${options.table}
            WHERE ${presentColumns.map(c => `"${c}" IS NOT NULL`).join(' OR ')}
            LIMIT 1
          `;

          const sampleResult = await client.query(sampleQuery);
          if (sampleResult.rows.length > 0) {
            const sample = sampleResult.rows[0];
            for (const [key, value] of Object.entries(sample)) {
              if (value !== null && value !== undefined) {
                const displayValue =
                  typeof value === 'string' && value.length > 50
                    ? `${value.substring(0, 50)}...`
                    : value;
                console.log(`  ${key}: ${displayValue}`);
              }
            }
          }
        }

        // Exit code based on results
        if (missingColumns.length > 0 || columnsAllZero.length > 0) {
          process.exit(1);
        }
      }
    } finally {
      client.release();
    }
  } catch (error) {
    console.error('Database error:', error);
    process.exit(1);
  } finally {
    await pool.end();
  }
}

testStats().catch(error => {
  console.error('Error:', error);
  process.exit(1);
});