espace-paie-odentas/scripts/verify-contracts-before-migration.ts

/**
 * Script de vérification des contrats avant migration des avenants
 * 
 * Ce script vérifie que tous les contrats référencés dans le CSV Airtable
 * existent bien dans la base Supabase.
 * 
 * Utilisation:
 *   npx tsx scripts/verify-contracts-before-migration.ts
 */

import { createClient } from '@supabase/supabase-js';
import * as fs from 'fs';
import * as path from 'path';
import * as readline from 'readline';
import { config } from 'dotenv';

// Charger les variables d'environnement
config({ path: path.join(process.cwd(), '.env.local') });

// Configuration
const SUPABASE_URL = process.env.NEXT_PUBLIC_SUPABASE_URL!;
const SUPABASE_SERVICE_KEY = process.env.SUPABASE_SERVICE_ROLE_KEY!;
const CSV_FILE_PATH = path.join(process.cwd(), 'Contrats de travail-Tous les CDDU.csv');

if (!SUPABASE_URL || !SUPABASE_SERVICE_KEY) {
  console.error('❌ Variables d\'environnement manquantes');
  process.exit(1);
}

const supabase = createClient(SUPABASE_URL, SUPABASE_SERVICE_KEY);

interface ContractReference {
  reference: string;
  structure_api: string;
  hasAvenant: boolean;
}

/**
 * Lit le CSV et extrait les références uniques
 */
async function readContractReferences(): Promise<ContractReference[]> {
  return new Promise((resolve, reject) => {
    const refs = new Map<string, ContractReference>();
    const fileStream = fs.createReadStream(CSV_FILE_PATH);
    const rl = readline.createInterface({
      input: fileStream,
      crlfDelay: Infinity
    });
    
    let isFirstLine = true;
    
    rl.on('line', (line) => {
      if (isFirstLine) {
        isFirstLine = false;
        return;
      }
      
      const matches = line.match(/(?:^|,)("(?:[^"]|"")*"|[^,]*)/g);
      if (!matches || matches.length < 5) return;
      
      const cols = matches.map(m => {
        let val = m.replace(/^,/, '');
        if (val.startsWith('"') && val.endsWith('"')) {
          val = val.slice(1, -1).replace(/""/g, '"');
        }
        return val.trim();
      });
      
      const reference = cols[0] || '';
      const structure = cols[1] || '';
      const dateAvenant = cols[2] || '';
      
      if (reference) {
        refs.set(reference, {
          reference,
          structure_api: structure,
          hasAvenant: dateAvenant !== ''
        });
      }
    });
    
    rl.on('close', () => resolve(Array.from(refs.values())));
    rl.on('error', reject);
  });
}

/**
 * Vérifie l'existence d'un contrat dans Supabase
 */
async function checkContract(reference: string) {
  const { data, error } = await supabase
    .from('cddu_contracts')
    .select('id, contract_number, reference, org_id, organizations(name)')
    .or(`contract_number.eq.${reference},reference.eq.${reference}`)
    .maybeSingle();
  
  if (error) {
    return { found: false, error: error.message };
  }
  
  if (!data) {
    return { found: false };
  }
  
  return {
    found: true,
    data: {
      id: data.id,
      contract_number: data.contract_number,
      org_name: (data.organizations as any)?.name || 'Unknown'
    }
  };
}

/**
 * Fonction principale
 */
async function verify() {
  console.log('🔍 Vérification des contrats avant migration des avenants\n');
  
  if (!fs.existsSync(CSV_FILE_PATH)) {
    console.error(`❌ Fichier CSV non trouvé: ${CSV_FILE_PATH}`);
    process.exit(1);
  }
  
  console.log(`📄 Lecture du fichier: ${CSV_FILE_PATH}\n`);
  
  const references = await readContractReferences();
  console.log(`📊 Nombre de contrats uniques: ${references.length}\n`);
  
  const stats = {
    total: references.length,
    found: 0,
    notFound: 0,
    withAvenants: 0,
    withoutAvenants: 0,
    errors: 0
  };
  
  const notFound: string[] = [];
  const foundWithAvenants: Array<{ ref: string; org: string }> = [];
  
  for (const ref of references) {
    process.stdout.write(`   Vérification de ${ref.reference}...`);
    
    const result = await checkContract(ref.reference);
    
    if (result.found && result.data) {
      console.log(` ✅ Trouvé (${result.data.org_name})`);
      stats.found++;
      
      if (ref.hasAvenant) {
        stats.withAvenants++;
        foundWithAvenants.push({
          ref: ref.reference,
          org: result.data.org_name
        });
      } else {
        stats.withoutAvenants++;
      }
    } else if (result.error) {
      console.log(` ⚠️  Erreur: ${result.error}`);
      stats.errors++;
    } else {
      console.log(` ❌ Non trouvé`);
      stats.notFound++;
      notFound.push(ref.reference);
    }
  }
  
  // Résumé
  console.log('\n' + '='.repeat(70));
  console.log('📊 RÉSUMÉ DE LA VÉRIFICATION');
  console.log('='.repeat(70));
  console.log(`Total de contrats uniques:              ${stats.total}`);
  console.log(`Contrats trouvés:                       ${stats.found} ✅`);
  console.log(`  └─ Avec avenants à migrer:            ${stats.withAvenants}`);
  console.log(`  └─ Sans avenants:                     ${stats.withoutAvenants}`);
  console.log(`Contrats non trouvés:                   ${stats.notFound} ❌`);
  console.log(`Erreurs:                                ${stats.errors} ⚠️`);
  console.log('='.repeat(70));
  
  if (notFound.length > 0) {
    console.log('\n⚠️  CONTRATS MANQUANTS DANS SUPABASE:');
    console.log('─'.repeat(70));
    notFound.forEach(ref => {
      const refData = references.find(r => r.reference === ref);
      console.log(`   ${ref.padEnd(20)} (${refData?.structure_api || 'N/A'})`);
    });
    console.log('\n💡 Ces contrats doivent être migrés avant de lancer la migration des avenants');
  }
  
  if (stats.withAvenants > 0) {
    console.log(`\n✅ ${stats.withAvenants} contrats sont prêts pour la migration des avenants`);
  }
  
  if (stats.notFound === 0 && stats.errors === 0) {
    console.log('\n🎉 Tous les contrats sont présents dans Supabase !');
    console.log('   Vous pouvez lancer la migration avec :');
    console.log('   npx ts-node scripts/migrate-avenants-from-airtable.ts\n');
  } else {
    console.log('\n⚠️  Résolvez les contrats manquants avant de continuer\n');
  }
}

verify().catch(console.error);