/**
 * One-off: find and clear MySQL locks blocking user_email_settings updates.
 * Usage: npx ts-node src/scripts/clear-email-settings-locks.ts
 */
import 'reflect-metadata';
import { initializeDatabase, AppDataSource } from '../models';

async function main() {
  await initializeDatabase();
  const rows = await AppDataSource.query(`
    SELECT
      trx.trx_id,
      trx.trx_state,
      trx.trx_started,
      TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS age_sec,
      trx.trx_query,
      trx.trx_mysql_thread_id AS thread_id,
      p.USER AS db_user,
      p.HOST AS db_host,
      p.DB AS db_name,
      p.COMMAND,
      p.TIME AS proc_time,
      p.STATE,
      LEFT(p.INFO, 200) AS info
    FROM information_schema.innodb_trx trx
    LEFT JOIN information_schema.processlist p
      ON p.ID = trx.trx_mysql_thread_id
    ORDER BY trx.trx_started ASC
  `);

  console.log(`Open transactions: ${rows.length}`);
  for (const r of rows) {
    console.log(
      JSON.stringify({
        thread_id: r.thread_id,
        age_sec: r.age_sec,
        state: r.trx_state,
        command: r.COMMAND,
        proc_state: r.STATE,
        info: r.info,
        trx_query: r.trx_query,
      }),
    );
  }

  const locks = await AppDataSource.query(`
    SELECT
      dl.OBJECT_SCHEMA,
      dl.OBJECT_NAME,
      dl.LOCK_TYPE,
      dl.LOCK_MODE,
      dl.LOCK_STATUS,
      dl.LOCK_DATA,
      dt.PROCESSLIST_ID,
      dt.PROCESSLIST_INFO,
      TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS age_sec
    FROM performance_schema.data_locks dl
    JOIN performance_schema.data_lock_waits dw
      ON dw.REQUESTING_ENGINE_LOCK_ID = dl.ENGINE_LOCK_ID
    JOIN performance_schema.data_locks bl
      ON bl.ENGINE_LOCK_ID = dw.BLOCKING_ENGINE_LOCK_ID
    JOIN performance_schema.threads dt
      ON dt.THREAD_ID = bl.OWNER_THREAD_ID
    JOIN information_schema.innodb_trx trx
      ON trx.trx_mysql_thread_id = dt.PROCESSLIST_ID
    WHERE dl.OBJECT_NAME = 'user_email_settings'
       OR bl.OBJECT_NAME = 'user_email_settings'
  `).catch(() => null);

  if (locks && locks.length) {
    console.log(`Lock waits involving user_email_settings: ${locks.length}`);
    console.log(JSON.stringify(locks, null, 2));
  } else {
    // Fallback: any long-running trx older than 20s
    console.log('No performance_schema lock-wait rows (or unavailable). Checking long trx…');
  }

  const toKill = rows.filter((r: { age_sec: number }) => Number(r.age_sec) >= 20);
  if (!toKill.length) {
    console.log('No transactions older than 20s to kill.');
    await AppDataSource.destroy();
    return;
  }

  for (const r of toKill) {
    const id = r.thread_id;
    console.log(`Killing stuck thread ${id} (age ${r.age_sec}s)…`);
    await AppDataSource.query(`KILL ?`, [id]);
  }
  console.log('Done. Retry saving email settings.');
  await AppDataSource.destroy();
}

main().catch(async (err) => {
  console.error(err);
  if (AppDataSource.isInitialized) await AppDataSource.destroy();
  process.exit(1);
});