import { db } from "./index.js";
import {
  onboardingWeekActivitiesTable,
} from "./schema/index.js";
import { and, asc, eq, sql } from "drizzle-orm";

type DuplicateGroup = {
  weekId: number;
  title: string;
  keepId: number;
  deleteIds: number[];
};

async function findDuplicateGroups(): Promise<DuplicateGroup[]> {
  const groups = await db.execute<{
    week_id: number;
    title: string;
    ids: number[];
    cnt: number;
  }>(sql`
    select
      week_id,
      title,
      array_agg(id order by id asc) as ids,
      count(*)::int as cnt
    from onboarding_week_activities
    group by week_id, title
    having count(*) > 1
    order by cnt desc, week_id asc, title asc
  `);

  return groups.rows.map((row) => {
    const ids = row.ids ?? [];
    const keepId = ids[0] ?? 0;
    return {
      weekId: row.week_id,
      title: row.title,
      keepId,
      deleteIds: ids.slice(1),
    };
  });
}

async function main() {
  const groups = await findDuplicateGroups();
  if (!groups.length) {
    process.stdout.write("No duplicate onboarding_week_activities found.\n");
    return;
  }

  let deleted = 0;

  for (const group of groups) {
    for (const id of group.deleteIds) {
      await db
        .delete(onboardingWeekActivitiesTable)
        .where(
          and(
            eq(onboardingWeekActivitiesTable.id, id),
            eq(onboardingWeekActivitiesTable.weekId, group.weekId),
            eq(onboardingWeekActivitiesTable.title, group.title),
          ),
        );
      deleted += 1;
    }
  }

  process.stdout.write(
    `Deleted ${deleted} duplicate onboarding_week_activities rows.\n`,
  );

  const remaining = await findDuplicateGroups();
  if (remaining.length) {
    throw new Error("Duplicates still exist after cleanup.");
  }
}

main().catch((err) => {
  process.stderr.write(String(err instanceof Error ? err.stack : err));
  process.stderr.write("\n");
  process.exit(1);
});