clienthub/scripts/validate-services-migration.ts

import { db } from "@/db";
import { service_catalog, offer_services, services, quote_items, offer_micro_services } from "@/db/schema";
import { sql, eq } from "drizzle-orm";

async function validate() {
  let failures = 0;

  // Check 1: row counts match
  const [catalogCount] = await db.select({ n: sql<number>`count(*)::int` }).from(service_catalog);
  const [offerCount] = await db.select({ n: sql<number>`count(*)::int` }).from(offer_services);
  const [migratedCatalog] = await db
    .select({ n: sql<number>`count(*)::int` })
    .from(services)
    .where(eq(services.migrated_from, "service_catalog"));
  const [migratedOffer] = await db
    .select({ n: sql<number>`count(*)::int` })
    .from(services)
    .where(eq(services.migrated_from, "offer_services"));

  console.log(`service_catalog rows: ${catalogCount.n} | migrated: ${migratedCatalog.n}`);
  console.log(`offer_services rows: ${offerCount.n} | migrated: ${migratedOffer.n}`);

  if (catalogCount.n !== migratedCatalog.n) {
    console.log("FAIL: service_catalog row count does not match migrated count");
    failures++;
  } else {
    console.log("PASS: service_catalog fully migrated");
  }

  if (offerCount.n !== migratedOffer.n) {
    console.log("FAIL: offer_services row count does not match migrated count");
    failures++;
  } else {
    console.log("PASS: offer_services fully migrated");
  }

  // Check 2: no orphaned migrated_id (every migrated_id from service_catalog still exists in service_catalog)
  const orphanedCatalog = await db.execute(sql`
    SELECT COUNT(*)::int AS n FROM services s
    WHERE s.migrated_from = 'service_catalog'
    AND NOT EXISTS (SELECT 1 FROM service_catalog sc WHERE sc.id = s.migrated_id)
  `);
  const orphanedCatalogCount = (orphanedCatalog as unknown as Array<{ n: number }>)[0]?.n ?? 0;
  if (orphanedCatalogCount > 0) {
    console.log(`FAIL: ${orphanedCatalogCount} services rows reference missing service_catalog ids`);
    failures++;
  } else {
    console.log("PASS: no orphaned service_catalog references");
  }

  const orphanedOffer = await db.execute(sql`
    SELECT COUNT(*)::int AS n FROM services s
    WHERE s.migrated_from = 'offer_services'
    AND NOT EXISTS (SELECT 1 FROM offer_services os WHERE os.id = s.migrated_id)
  `);
  const orphanedOfferCount = (orphanedOffer as unknown as Array<{ n: number }>)[0]?.n ?? 0;
  if (orphanedOfferCount > 0) {
    console.log(`FAIL: ${orphanedOfferCount} services rows reference missing offer_services ids`);
    failures++;
  } else {
    console.log("PASS: no orphaned offer_services references");
  }

  // Check 3: existing quote_items.service_id still resolve in service_catalog (untouched FK — must remain valid)
  const orphanedQuoteItems = await db.execute(sql`
    SELECT COUNT(*)::int AS n FROM quote_items qi
    WHERE qi.service_id IS NOT NULL
    AND NOT EXISTS (SELECT 1 FROM service_catalog sc WHERE sc.id = qi.service_id)
  `);
  const orphanedQuoteItemsCount = (orphanedQuoteItems as unknown as Array<{ n: number }>)[0]?.n ?? 0;
  if (orphanedQuoteItemsCount > 0) {
    console.log(`FAIL: ${orphanedQuoteItemsCount} quote_items reference missing service_catalog ids (pre-existing FK broken!)`);
    failures++;
  } else {
    console.log("PASS: quote_items.service_id -> service_catalog FK intact (unchanged by this migration)");
  }

  // Check 4: existing offer_micro_services.service_id still resolve in offer_services (untouched FK — must remain valid)
  const orphanedMicroServices = await db.execute(sql`
    SELECT COUNT(*)::int AS n FROM offer_micro_services oms
    WHERE NOT EXISTS (SELECT 1 FROM offer_services os WHERE os.id = oms.service_id)
  `);
  const orphanedMicroServicesCount = (orphanedMicroServices as unknown as Array<{ n: number }>)[0]?.n ?? 0;
  if (orphanedMicroServicesCount > 0) {
    console.log(`FAIL: ${orphanedMicroServicesCount} offer_micro_services reference missing offer_services ids (pre-existing FK broken!)`);
    failures++;
  } else {
    console.log("PASS: offer_micro_services.service_id -> offer_services FK intact (unchanged by this migration)");
  }

  // Check 5: name collision report (informational)
  const collisions = await db.execute(sql`
    SELECT name, COUNT(*)::int AS n FROM services GROUP BY name HAVING COUNT(*) > 1
  `);
  const collisionRows = collisions as unknown as Array<{ name: string; n: number }>;
  if (collisionRows.length > 0) {
    console.log(`WARNING: ${collisionRows.length} duplicate names remain in services (review):`, collisionRows);
  } else {
    console.log("PASS: no duplicate names in services");
  }

  // Check 6: informational — count of net-new (non-migrated) services
  const [netNew] = await db
    .select({ n: sql<number>`count(*)::int` })
    .from(services)
    .where(sql`migrated_from IS NULL`);
  console.log(`INFO: ${netNew.n} net-new services created post-migration (migrated_from IS NULL)`);

  console.log(`\n${failures === 0 ? "ALL CHECKS PASSED" : `${failures} CHECK(S) FAILED`}`);
  process.exit(failures === 0 ? 0 : 1);
}

validate().catch((err) => {
  console.error("Validation failed:", err);
  process.exit(1);
});