import { config } from 'dotenv';
import { Pool } from 'pg';
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from '@prisma/client';

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

// Create connection pool
const connectionString = process.env.DATABASE_URL || '';
const pool = new Pool({ connectionString });

// Create Prisma adapter
const adapter = new PrismaPg(pool);

// Create Prisma client with adapter
const prisma = new PrismaClient({
  adapter,
  log: ['error'],
});

async function main() {
  console.log('Populating cityNormalized field using SQL...');

  // Use raw SQL for much faster batch update
  // Normalize: lowercase, remove special chars except spaces/numbers, trim
  const result = await prisma.$executeRaw`
    UPDATE churches
    SET city_normalized = LOWER(
      TRIM(
        REGEXP_REPLACE(
          COALESCE(city, ''),
          '[^a-zA-Z0-9 ]',
          '',
          'g'
        )
      )
    )
    WHERE city IS NOT NULL
  `;

  console.log(`✅ Updated ${result} churches with normalized cities`);
}

main()
  .then(async () => {
    await prisma.$disconnect();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });