// Scoped dedupe: ONLY my past-paper inserts (user_id=2, ai_generated, source = "... P<L> ... Q<n>")
$scope = function($q){
  return $q->where('qa_status','ai_generated')->where('user_id',2)
           ->where('source','REGEXP','P[1-6].* Q[0-9]+$');
};
$groups = $scope(DB::table('questions'))
  ->select('source', DB::raw('COUNT(*) c'))
  ->groupBy('source')->havingRaw('COUNT(*) > 1')->get();

$delIds = [];
foreach ($groups as $g) {
  $ids = $scope(DB::table('questions'))->where('source',$g->source)
         ->orderBy('id')->pluck('id')->all();
  array_shift($ids);                 // keep lowest id
  foreach ($ids as $i) $delIds[] = $i;
}
echo "dup_groups=".$groups->count()." rows_to_delete=".count($delIds)."\n";

if (count($delIds)) {
  // backup full rows before delete
  $rows = DB::table('questions')->whereIn('id',$delIds)
            ->get(['id','source','status_id','type_id','question','answer0','correct_answer']);
  $bk = storage_path('app/dedupe_backup_'.date('Ymd_His').'.json');
  file_put_contents($bk, json_encode($rows));
  echo "backup=".$bk." (".$rows->count()." rows)\n";

  $delQ=0;$delH=0;$delS=0;
  foreach (array_chunk($delIds, 500) as $chunk) {
    $delH += DB::table('hints')->whereIn('question_id',$chunk)->delete();
    $delS += DB::table('solutions')->whereIn('question_id',$chunk)->delete();
    $delQ += DB::table('questions')->whereIn('id',$chunk)->delete();
  }
  echo "deleted_questions=$delQ hints=$delH solutions=$delS\n";
}

// verify
$after = $scope(DB::table('questions'))->count();
$afterD= $scope(DB::table('questions'))->distinct('source')->count('source');
echo "after_rows=$after after_distinct=$afterD remaining_dups=".($after-$afterD)."\n";