import pool from '../db/index.js';

type DailyPickRow = {
  id: number;
  game_date: Date;
  player_name: string;
  stat: string;
  over_under: string;
  line: number | string | null;
  old_lock_level: string | null;
};

type PiffPickRow = {
  run_date: Date;
  player_name: string;
  stat: string;
  direction: string;
  line: number | string | null;
  tier_label: string;
};

function normalizeName(name: string | null | undefined): string {
  return (name || '')
    .normalize('NFKD')
    .replace(/[\u0300-\u036f]/g, '')
    .replace(/\b(jr\.?|sr\.?|ii|iii|iv)\b/gi, '')
    .replace(/[^A-Za-z0-9 ]/g, ' ')
    .replace(/\s+/g, ' ')
    .trim()
    .toLowerCase();
}

function normalizeStat(stat: string | null | undefined): string {
  const lower = (stat || '').trim().toLowerCase();
  const aliases: Record<string, string> = {
    shots_ongoal: 'shots',
    shots_on_goal: 'shots',
    shotsongoal: 'shots',
    shots: 'shots',
    shots_ontarget: 'shots_ontarget',
    shots_on_target: 'shots_ontarget',
    shotsontarget: 'shots_ontarget',
  };
  return aliases[lower] || lower;
}

function formatDateKey(value: Date | string): string {
  return new Date(value).toISOString().slice(0, 10);
}

function formatLineKey(value: number | string | null | undefined): string {
  const numeric = Number(value);
  return Number.isFinite(numeric) ? numeric.toFixed(2) : '';
}

function buildNormalizedKey(row: {
  game_date?: Date | string;
  run_date?: Date | string;
  player_name: string;
  stat: string;
  over_under?: string;
  direction?: string;
  line: number | string | null;
}): string {
  return [
    formatDateKey(row.game_date ?? row.run_date ?? ''),
    normalizeName(row.player_name),
    normalizeStat(row.stat),
    String(row.over_under ?? row.direction ?? '').toLowerCase(),
    formatLineKey(row.line),
  ].join('|');
}

async function main() {
  const dryRun = process.argv.includes('--dry-run');

  const exactRows = await pool.query(`
    SELECT
      dp.id,
      dp.game_date,
      dp.player_name,
      dp.stat,
      dp.over_under,
      dp.line,
      dp.lock_level AS old_lock_level,
      pp.tier_label AS new_lock_level
    FROM "DailyPick" dp
    JOIN sportsclaw.piff_picks pp
      ON pp.run_date = dp.game_date
     AND lower(pp.player_name) = lower(dp.player_name)
     AND lower(pp.stat) = lower(dp.stat)
     AND lower(pp.direction) = lower(dp.over_under)
     AND abs(coalesce(pp.line, 0) - coalesce(dp.line, 0)) < 0.001
    WHERE dp.algo_version LIKE 'piff3.0_%'
      AND pp.tier_label IN ('T1_LOCK', 'T2_STRONG', 'T3_SOLID')
      AND coalesce(dp.lock_level, '') IS DISTINCT FROM pp.tier_label
  `);

  const exactById = new Map<number, { new_lock_level: string }>();
  for (const row of exactRows.rows) {
    exactById.set(row.id, { new_lock_level: row.new_lock_level });
  }

  const unmatchedDaily = await pool.query<DailyPickRow>(`
    SELECT
      dp.id,
      dp.game_date,
      dp.player_name,
      dp.stat,
      dp.over_under,
      dp.line,
      dp.lock_level AS old_lock_level
    FROM "DailyPick" dp
    WHERE dp.algo_version LIKE 'piff3.0_%'
      AND coalesce(dp.lock_level, '') NOT IN ('T1_LOCK', 'T2_STRONG', 'T3_SOLID')
  `);

  const sourceRows = await pool.query<PiffPickRow>(`
    SELECT
      pp.run_date,
      pp.player_name,
      pp.stat,
      pp.direction,
      pp.line,
      pp.tier_label
    FROM sportsclaw.piff_picks pp
    WHERE pp.tier_label IN ('T1_LOCK', 'T2_STRONG', 'T3_SOLID')
  `);

  const sourceByNormalizedKey = new Map<string, PiffPickRow[]>();
  for (const row of sourceRows.rows) {
    const key = buildNormalizedKey(row);
    const bucket = sourceByNormalizedKey.get(key) || [];
    bucket.push(row);
    sourceByNormalizedKey.set(key, bucket);
  }

  const normalizedMatches: Array<DailyPickRow & { new_lock_level: string }> = [];
  for (const row of unmatchedDaily.rows) {
    if (exactById.has(row.id)) {
      continue;
    }
    const key = buildNormalizedKey(row);
    const candidates = sourceByNormalizedKey.get(key) || [];
    if (candidates.length === 1) {
      normalizedMatches.push({
        ...row,
        new_lock_level: candidates[0].tier_label,
      });
    }
  }

  const updates = [
    ...exactRows.rows,
    ...normalizedMatches.filter(
      (row) => (row.old_lock_level || '') !== row.new_lock_level,
    ),
  ];

  console.log(
    `[piff3-lock-backfill] exact matches: ${exactRows.rowCount || 0}, normalized matches: ${normalizedMatches.length}, total updates: ${updates.length}`,
  );

  const preview = updates
    .sort((a, b) => {
      const dateDiff =
        new Date(b.game_date).getTime() - new Date(a.game_date).getTime();
      return dateDiff || a.player_name.localeCompare(b.player_name);
    })
    .slice(0, 20);

  if (preview.length) {
    console.table(preview);
  }

  if (dryRun || updates.length === 0) {
    await pool.end();
    return;
  }

  const client = await pool.connect();
  let updated = 0;
  try {
    await client.query('BEGIN');
    for (const row of updates) {
      const result = await client.query(
        `
          UPDATE "DailyPick"
          SET lock_level = $2
          WHERE id = $1
            AND coalesce(lock_level, '') IS DISTINCT FROM $2
        `,
        [row.id, row.new_lock_level],
      );
      updated += result.rowCount || 0;
    }
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }

  console.log(`[piff3-lock-backfill] updated rows: ${updated}`);
  await pool.end();
}

main().catch(async (err) => {
  console.error('[piff3-lock-backfill] failed:', err);
  try {
    await pool.end();
  } catch {}
  process.exit(1);
});