#!/usr/bin/env tsx
/**
 * Script to identify corrupted data in the database
 * Finds records with invalid characters that cause P2023 Prisma conversion errors
 */

import { PrismaClient } from '@prisma/client';
import * as dotenv from 'dotenv';

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

const prisma = new PrismaClient({
  log: ['error'],
});

/**
 * Check if a string contains invalid UTF-8 characters
 */
function hasInvalidChars(str: string | null | undefined): boolean {
  if (!str) return false;
  try {
    // Try to encode/decode the string
    const encoded = new TextEncoder().encode(str);
    const decoded = new TextDecoder('utf-8', { fatal: true }).decode(encoded);
    return decoded !== str;
  } catch (e) {
    return true;
  }
}

/**
 * Find problematic characters in a string
 */
function findProblematicChars(str: string): string[] {
  const problematic: string[] = [];
  for (let i = 0; i < str.length; i++) {
    const char = str[i];
    const code = char.charCodeAt(0);
    // Check for invalid UTF-8 sequences or control characters (except common ones)
    if (
      (code >= 0 && code < 32 && code !== 9 && code !== 10 && code !== 13) ||
      (code >= 127 && code < 160) ||
      code === 0xfffe ||
      code === 0xffff
    ) {
      problematic.push(`U+${code.toString(16).padStart(4, '0')} (${char})`);
    }
  }
  return problematic;
}

async function findCorruptedPeople() {
  console.log('🔍 Checking Person table for corrupted data...\n');

  try {
    // Try to fetch all people
    const people = await prisma.person.findMany({
      select: {
        id: true,
        first_name: true,
        last_name: true,
        middle_name: true,
        maiden_name: true,
        date_of_birth: true,
        created_date: true,
      },
    });

    console.log(`✅ Successfully fetched ${people.length} people\n`);
    console.log('Checking for invalid characters in text fields...\n');

    let corruptedCount = 0;
    const corruptedRecords: Array<{
      id: number;
      field: string;
      value: string;
      problematicChars: string[];
    }> = [];

    for (const person of people) {
      const fields = [
        { name: 'first_name', value: person.first_name },
        { name: 'last_name', value: person.last_name },
        { name: 'middle_name', value: person.middle_name },
        { name: 'maiden_name', value: person.maiden_name },
      ];

      for (const field of fields) {
        if (field.value && hasInvalidChars(field.value)) {
          const problematic = findProblematicChars(field.value);
          corruptedRecords.push({
            id: person.id,
            field: field.name,
            value: field.value,
            problematicChars: problematic,
          });
          corruptedCount++;
        }
      }
    }

    if (corruptedCount === 0) {
      console.log('✅ No corrupted text data found in Person table\n');
    } else {
      console.log(`⚠️  Found ${corruptedCount} corrupted field(s) in ${corruptedRecords.length} record(s):\n`);
      for (const record of corruptedRecords) {
        console.log(`  Person ID ${record.id}, Field: ${record.field}`);
        console.log(`    Value: ${JSON.stringify(record.value)}`);
        console.log(`    Problematic characters: ${record.problematicChars.join(', ')}`);
        console.log('');
      }
    }

    return corruptedRecords;
  } catch (error: any) {
    if (error?.code === 'P2023' || error?.message?.includes('Conversion failed')) {
      console.error('❌ Cannot query Person table due to conversion error');
      console.error('   This means there is definitely corrupted data, but we cannot identify which records\n');
      console.error('   Error details:', error.message);
      
      // Try to query with raw SQL to identify problematic records
      console.log('\n🔍 Attempting to identify corrupted records using raw SQL...\n');
      try {
        // First, get all records with text fields only
        const textFields = await prisma.$queryRaw<Array<{
          id: number;
          first_name: string;
          last_name: string;
          middle_name: string | null;
          maiden_name: string | null;
        }>>`
          SELECT id, first_name, last_name, middle_name, maiden_name
          FROM people
        `;

        console.log(`Found ${textFields.length} records via raw SQL (text fields only)\n`);
        console.log('Checking text fields for invalid characters...\n');

        let corruptedCount = 0;
        for (const person of textFields) {
          const fields = [
            { name: 'first_name', value: person.first_name },
            { name: 'last_name', value: person.last_name },
            { name: 'middle_name', value: person.middle_name },
            { name: 'maiden_name', value: person.maiden_name },
          ];

          for (const field of fields) {
            if (field.value && hasInvalidChars(field.value)) {
              const problematic = findProblematicChars(field.value);
              console.log(`  Person ID ${person.id}, Field: ${field.name}`);
              console.log(`    Value: ${JSON.stringify(field.value)}`);
              console.log(`    Problematic characters: ${problematic.join(', ')}`);
              console.log('');
              corruptedCount++;
            }
          }
        }

        if (corruptedCount === 0) {
          console.log('✅ No invalid characters found in text fields');
        }

        // Now check date fields
        console.log('\n🔍 Checking date fields (date_of_birth, created_date)...\n');
        try {
          const dateFields = await prisma.$queryRaw<Array<{
            id: number;
            date_of_birth: string | null;
            created_date: string;
          }>>`
            SELECT id, date_of_birth, created_date
            FROM people
          `;

          console.log(`Checking ${dateFields.length} records for corrupted date fields...\n`);

          let dateCorruptedCount = 0;
          for (const person of dateFields) {
            const issues: string[] = [];

            // Check created_date
            if (person.created_date) {
              try {
                const date = new Date(person.created_date);
                if (isNaN(date.getTime())) {
                  issues.push(`created_date: invalid date value "${person.created_date}"`);
                }
              } catch (e) {
                issues.push(`created_date: cannot parse "${person.created_date}"`);
              }
            }

            // Check date_of_birth
            if (person.date_of_birth) {
              try {
                const date = new Date(person.date_of_birth);
                if (isNaN(date.getTime())) {
                  issues.push(`date_of_birth: invalid date value "${person.date_of_birth}"`);
                }
              } catch (e) {
                issues.push(`date_of_birth: cannot parse "${person.date_of_birth}"`);
              }
            }

            if (issues.length > 0) {
              console.log(`  Person ID ${person.id}:`);
              for (const issue of issues) {
                console.log(`    ❌ ${issue}`);
              }
              console.log('');
              dateCorruptedCount++;
            }
          }

          if (dateCorruptedCount === 0) {
            console.log('✅ No corrupted date fields found\n');
          } else {
            console.log(`⚠️  Found ${dateCorruptedCount} record(s) with corrupted date fields\n`);
          }

          // Try to identify which specific field is causing the issue
          console.log('🔍 Testing individual field queries...\n');
          
          // Test querying without date_of_birth
          try {
            await prisma.$queryRaw`SELECT id, first_name, last_name, middle_name, maiden_name, created_date FROM people LIMIT 1`;
            console.log('✅ Query without date_of_birth works');
          } catch (e: any) {
            console.log('❌ Query without date_of_birth still fails:', e.message);
          }

          // Test querying without created_date
          try {
            await prisma.$queryRaw`SELECT id, first_name, last_name, middle_name, maiden_name, date_of_birth FROM people LIMIT 1`;
            console.log('✅ Query without created_date works');
          } catch (e: any) {
            console.log('❌ Query without created_date still fails:', e.message);
          }

        } catch (dateError: any) {
          console.error('❌ Error checking date fields:', dateError.message);
        }
      } catch (rawError: any) {
        console.error('❌ Raw SQL query also failed:', rawError.message);
      }
    } else {
      throw error;
    }
  }
}

async function findCorruptedTags() {
  console.log('\n🔍 Checking Tag table for corrupted data...\n');

  try {
    const tags = await prisma.tag.findMany({
      select: {
        id: true,
        tag_name: true,
        created_date: true,
      },
    });

    console.log(`✅ Successfully fetched ${tags.length} tags\n`);

    let corruptedCount = 0;
    for (const tag of tags) {
      if (hasInvalidChars(tag.tag_name)) {
        const problematic = findProblematicChars(tag.tag_name);
        console.log(`  Tag ID ${tag.id}, Field: tag_name`);
        console.log(`    Value: ${JSON.stringify(tag.tag_name)}`);
        console.log(`    Problematic characters: ${problematic.join(', ')}`);
        console.log('');
        corruptedCount++;
      }
    }

    if (corruptedCount === 0) {
      console.log('✅ No corrupted text data found in Tag table\n');
    } else {
      console.log(`⚠️  Found ${corruptedCount} corrupted tag(s)\n`);
    }
  } catch (error: any) {
    if (error?.code === 'P2023' || error?.message?.includes('Conversion failed')) {
      console.error('❌ Cannot query Tag table due to conversion error');
      console.error('   Error details:', error.message);
    } else {
      throw error;
    }
  }
}

async function main() {
  try {
    await findCorruptedPeople();
    await findCorruptedTags();
  } catch (error: any) {
    console.error('❌ Unexpected error:', error);
    process.exit(1);
  } finally {
    await prisma.$disconnect();
  }
}

main();

