import pg from 'pg';

const pool = new pg.Pool({
  connectionString: 'postgresql://edumanager:EduManager2026!Seguro@150.230.87.131:5432/edumanager'
});

const files = [
  'student_1777139875689_4l971l.webp',
  'student_1777139911929_21v6av.webp',
  'student_1777139942925_aoxyls.webp',
  'student_1777140008401_3966cd.webp',
  'student_1777140038192_xntcja.webp',
  'student_1777140066701_dfzecd.webp',
  'student_1777140098875_tcan7k.webp',
  'student_1777743393187_rf50t.webp'
];

async function run() {
  try {
    const { rows: tables } = await pool.query(
      "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'"
    );

    console.log(`Buscando em ${tables.length} tabelas no banco...`);

    for (const t of tables) {
      const tableName = t.table_name;
      const { rows: cols } = await pool.query(
        "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = $1",
        [tableName]
      );

      for (const c of cols) {
        if (['text', 'character varying', 'jsonb'].includes(c.data_type)) {
          for (const f of files) {
            try {
              const queryStr = `SELECT id FROM ${tableName} WHERE "${c.column_name}"::text LIKE $1`;
              const { rows: matches } = await pool.query(queryStr, [`%${f}%`]);
              if (matches.length > 0) {
                console.log(`🔍 [ENCONTRADO] Arquivo ${f} na tabela [${tableName}], coluna [${c.column_name}]! IDs:`, matches.map(m => m.id));
              }
            } catch (e) {
              // Ignorar erros de colunas sem coluna 'id'
              try {
                const queryStr = `SELECT count(*) FROM ${tableName} WHERE "${c.column_name}"::text LIKE $1`;
                const { rows: matches } = await pool.query(queryStr, [`%${f}%`]);
                if (parseInt(matches[0].count) > 0) {
                  console.log(`🔍 [ENCONTRADO] Arquivo ${f} na tabela [${tableName}], coluna [${c.column_name}] (Count: ${matches[0].count})`);
                }
              } catch (e2) {}
            }
          }
        }
      }
    }
    console.log('Busca finalizada.');
  } catch (err) {
    console.error('Erro na busca:', err);
  } finally {
    await pool.end();
  }
}

run();