dow-prod-tracker/scripts/clean-slate.ts

import "dotenv/config";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "../src/generated/prisma/client";
import { execSync } from "child_process";

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL! });
const prisma = new PrismaClient({ adapter });

// ─── CLI Argument Parsing ──────────────────────────────

function parseArgs() {
  const args = process.argv.slice(2);
  const flags: Record<string, string | boolean> = {};

  for (let i = 0; i < args.length; i++) {
    if (args[i] === "--confirm") {
      flags.confirm = true;
    } else if (args[i] === "--org-name" && args[i + 1]) {
      flags.orgName = args[++i];
    } else if (args[i] === "--domain" && args[i + 1]) {
      flags.domain = args[++i];
    }
  }

  return flags;
}

function printUsage() {
  console.log(`
Usage: npm run db:clean-slate -- [--confirm] --org-name <name> --domain <domain>

Options:
  --confirm          Execute the purge (without this flag, runs in dry-run mode)
  --org-name <name>  The real organization name (e.g., "Oliver Agency")
  --domain <domain>  The real organization domain (e.g., "oliver.agency")

Examples:
  npm run db:clean-slate -- --org-name "Oliver Agency" --domain oliver.agency
  npm run db:clean-slate -- --confirm --org-name "Oliver Agency" --domain oliver.agency
`);
}

// ─── Record Counting ───────────────────────────────────

async function getRecordCounts() {
  const counts: Record<string, number> = {};

  counts["organizations"] = await prisma.organization.count();
  counts["users"] = await prisma.user.count();
  counts["accounts"] = await prisma.account.count();
  counts["sessions"] = await prisma.session.count();
  counts["projects"] = await prisma.project.count();
  counts["deliverables"] = await prisma.deliverable.count();
  counts["deliverable_stages"] = await prisma.deliverableStage.count();
  counts["stage_assignments"] = await prisma.stageAssignment.count();
  counts["revisions"] = await prisma.revision.count();
  counts["comments"] = await prisma.comment.count();
  counts["annotations"] = await prisma.annotation.count();
  counts["client_teams"] = await prisma.clientTeam.count();
  counts["client_team_memberships"] = await prisma.clientTeamMembership.count();
  counts["pods"] = await prisma.pod.count();
  counts["notifications"] = await prisma.notification.count();
  counts["chat_messages"] = await prisma.chatMessage.count();
  counts["search_logs"] = await prisma.searchLog.count();
  counts["automation_rules"] = await prisma.automationRule.count();
  counts["automation_executions"] = await prisma.automationExecution.count();
  counts["invitations"] = await prisma.invitation.count();
  counts["custom_field_definitions"] = await prisma.customFieldDefinition.count();
  counts["notification_rules"] = await prisma.notificationRule.count();
  counts["user_skills"] = await prisma.userSkill.count();
  counts["skills"] = await prisma.skill.count();
  counts["stage_skill_requirements"] = await prisma.stageSkillRequirement.count();
  counts["pipeline_templates"] = await prisma.pipelineTemplate.count();
  counts["pipeline_stage_definitions"] = await prisma.pipelineStageDefinition.count();
  counts["pipeline_stage_deps_v2"] = await prisma.pipelineStageDependencyV2.count();
  counts["pipeline_stage_templates"] = await prisma.pipelineStageTemplate.count();
  counts["pipeline_stage_deps"] = await prisma.pipelineStageDependency.count();
  counts["org_role_permissions"] = await prisma.orgRolePermission.count();

  return counts;
}

function printCounts(counts: Record<string, number>, label: string) {
  console.log(`\n${label}:`);
  console.log("─".repeat(50));

  const total = Object.values(counts).reduce((a, b) => a + b, 0);
  for (const [table, count] of Object.entries(counts)) {
    if (count > 0) {
      console.log(`  ${table.padEnd(30)} ${count}`);
    }
  }

  const zeroCount = Object.values(counts).filter((c) => c === 0).length;
  if (zeroCount > 0) {
    console.log(`  ... and ${zeroCount} tables with 0 records`);
  }
  console.log(`  ${"TOTAL".padEnd(30)} ${total}`);
}

// ─── Purge ─────────────────────────────────────────────

async function purgeAllData() {
  console.log("\n🗑️  Purging all data...\n");

  // Null out self-referential FKs first
  console.log("  Nulling self-referential FKs...");
  await prisma.comment.updateMany({ data: { parentId: null } });

  // Leaf tables
  console.log("  Deleting leaf tables (annotations, client team memberships)...");
  await prisma.annotation.deleteMany();
  await prisma.clientTeamMembership.deleteMany();

  // Mid-level tables
  console.log("  Deleting mid-level tables (revisions, comments, stage assignments, deliverable stages)...");
  await prisma.revision.deleteMany();
  await prisma.comment.deleteMany();
  await prisma.stageAssignment.deleteMany();
  await prisma.deliverableStage.deleteMany();

  // Parent tables
  console.log("  Deleting parent tables (deliverables, projects)...");
  await prisma.deliverable.deleteMany();
  await prisma.project.deleteMany();

  // Org-linked standalone tables
  console.log("  Deleting org-linked standalone tables...");
  await prisma.chatMessage.deleteMany();
  await prisma.notification.deleteMany();
  await prisma.searchLog.deleteMany();
  await prisma.automationExecution.deleteMany();
  await prisma.automationRule.deleteMany();
  await prisma.invitation.deleteMany();
  await prisma.customFieldDefinition.deleteMany();
  await prisma.notificationRule.deleteMany();
  await prisma.clientTeam.deleteMany();
  // Pods deleted BEFORE users (users have a nullable FK to pods; Prisma SetNull on delete
  // handles it, but being explicit here mirrors the rest of this dependency-ordered teardown).
  await prisma.pod.deleteMany();

  // Auth tables
  console.log("  Deleting auth tables (accounts, sessions, verification tokens)...");
  await prisma.account.deleteMany();
  await prisma.session.deleteMany();
  await prisma.$executeRawUnsafe(`DELETE FROM verification_tokens`);

  // User-linked tables
  console.log("  Deleting user-linked tables (user skills, stage skill requirements, skills)...");
  await prisma.userSkill.deleteMany();
  await prisma.stageSkillRequirement.deleteMany();
  await prisma.skill.deleteMany();

  // Pipeline v2 (org-scoped dynamic templates)
  console.log("  Deleting pipeline v2 tables (stage deps v2, stage definitions, pipeline templates)...");
  await prisma.pipelineStageDependencyV2.deleteMany();
  await prisma.pipelineStageDefinition.deleteMany();
  await prisma.pipelineTemplate.deleteMany();

  // Pipeline v1 (global templates)
  console.log("  Deleting pipeline v1 tables (stage dependencies, stage templates)...");
  await prisma.pipelineStageDependency.deleteMany();
  await prisma.pipelineStageTemplate.deleteMany();

  // RBAC
  console.log("  Deleting RBAC permissions...");
  await prisma.orgRolePermission.deleteMany();

  // Users and org
  console.log("  Deleting users and organization...");
  await prisma.user.deleteMany();
  await prisma.organization.deleteMany();

  console.log("\n✅ Purge complete — all tables empty.");
}

// ─── Reseed ────────────────────────────────────────────

function reseed() {
  console.log("\n🌱 Re-seeding structural data via seed.ts...\n");
  try {
    execSync("npx tsx prisma/seed.ts", {
      cwd: process.cwd(),
      stdio: "inherit",
      env: process.env,
    });
    console.log("\n✅ Reseed complete.");
  } catch (error) {
    console.error("\n❌ Reseed FAILED. The database may be empty.");
    console.error("   To recover, manually run: npm run db:seed-team");
    throw error;
  }
}

// ─── Main ──────────────────────────────────────────────

async function main() {
  const flags = parseArgs();

  if (!flags.orgName || !flags.domain) {
    console.error("Error: --org-name and --domain are required.\n");
    printUsage();
    process.exit(1);
  }

  console.log("╔══════════════════════════════════════════════╗");
  console.log("║        Dow Jones Studio Tracker              ║");
  console.log("║        Clean Slate Toolkit                   ║");
  console.log("╚══════════════════════════════════════════════╝");
  console.log(`\n  Target org: "${flags.orgName}" (${flags.domain})`);
  console.log(`  Mode: ${flags.confirm ? "🔴 EXECUTE" : "🟢 DRY RUN"}`);

  // Count current records
  const preCounts = await getRecordCounts();
  printCounts(preCounts, flags.confirm ? "Records to purge" : "Would purge");

  if (!flags.confirm) {
    console.log("\n─── DRY RUN ───");
    console.log("No changes made. Run with --confirm to execute.\n");
    return;
  }

  // Execute purge
  console.log("\n═══════════════════════════════════════════════");
  console.log("  EXECUTING CLEAN SLATE");
  console.log("═══════════════════════════════════════════════");

  await purgeAllData();

  // Need to disconnect before reseed (reseed creates its own Prisma client)
  await prisma.$disconnect();

  reseed();

  // Reconnect after reseed
  const postAdapter = new PrismaPg({ connectionString: process.env.DATABASE_URL! });
  const postPrisma = new PrismaClient({ adapter: postAdapter });

  try {
    // Rename org
    const org = await postPrisma.organization.findFirst();
    if (!org) {
      throw new Error("No organization found after reseed.");
    }

    await postPrisma.organization.update({
      where: { id: org.id },
      data: { name: flags.orgName as string, domain: flags.domain as string },
    });

    console.log(`\n🏢 Organization updated: "${flags.orgName}" (${flags.domain})`);

    // Post-execution summary
    const postCountsObj: Record<string, number> = {};
    postCountsObj["organizations"] = await postPrisma.organization.count();
    postCountsObj["users"] = await postPrisma.user.count();
    postCountsObj["pipeline_stage_templates"] = await postPrisma.pipelineStageTemplate.count();
    postCountsObj["pipeline_templates"] = await postPrisma.pipelineTemplate.count();
    postCountsObj["pipeline_stage_definitions"] = await postPrisma.pipelineStageDefinition.count();
    postCountsObj["skills"] = await postPrisma.skill.count();
    postCountsObj["user_skills"] = await postPrisma.userSkill.count();
    postCountsObj["org_role_permissions"] = await postPrisma.orgRolePermission.count();
    postCountsObj["projects"] = await postPrisma.project.count();
    postCountsObj["deliverables"] = await postPrisma.deliverable.count();

    printCounts(postCountsObj, "Re-seeded records");

    console.log("\n═══════════════════════════════════════════════");
    console.log("  ✅ CLEAN SLATE COMPLETE");
    console.log("═══════════════════════════════════════════════");
    console.log(`\n  Organization: "${flags.orgName}" (${flags.domain})`);
    console.log("  Database is ready for production use.\n");
  } finally {
    await postPrisma.$disconnect();
  }
}

main()
  .catch((e) => {
    console.error("\n❌ Clean slate failed:", e.message || e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });