2 files changed, 84 insertions, 2 deletions
diff --git a/packages/console/core/script/lookup-user.ts b/packages/console/core/script/lookup-user.ts
index 15cbe80f8..b06fb5654 100644
--- a/packages/console/core/script/lookup-user.ts
+++ b/packages/console/core/script/lookup-user.ts
@@ -41,8 +41,8 @@ if (identifier.startsWith("wrk_")) {
         subscribed: SubscriptionTable.timeCreated,
       })
       .from(UserTable)
-      .innerJoin(WorkspaceTable, eq(WorkspaceTable.id, UserTable.workspaceID))
-      .innerJoin(SubscriptionTable, eq(SubscriptionTable.userID, UserTable.id))
+      .rightJoin(WorkspaceTable, eq(WorkspaceTable.id, UserTable.workspaceID))
+      .leftJoin(SubscriptionTable, eq(SubscriptionTable.userID, UserTable.id))
       .where(eq(UserTable.accountID, accountID))
       .then((rows) =>
         rows.map((row) => ({
@@ -113,6 +113,8 @@ async function printWorkspace(workspaceID: string) {
       .select({
         balance: BillingTable.balance,
         customerID: BillingTable.customerID,
+        subscriptionID: BillingTable.subscriptionID,
+        subscriptionCouponID: BillingTable.subscriptionCouponID,
       })
       .from(BillingTable)
       .where(eq(BillingTable.workspaceID, workspace.id))
@@ -149,6 +151,7 @@ async function printWorkspace(workspaceID: string) {
       ),
   )
 
+  /*
   await printTable("Usage", (tx) =>
     tx
       .select({
@@ -174,6 +177,7 @@ async function printWorkspace(workspaceID: string) {
         })),
       ),
   )
+        */
 }
 
 function formatMicroCents(value: number | null | undefined) {
diff --git a/packages/console/core/script/remove-black.ts b/packages/console/core/script/remove-black.ts
new file mode 100644
index 000000000..0803c8f83
--- /dev/null
+++ b/packages/console/core/script/remove-black.ts
@@ -0,0 +1,78 @@
+import { Billing } from "../src/billing.js"
+import { and, Database, eq } from "../src/drizzle/index.js"
+import { BillingTable, PaymentTable, SubscriptionTable } from "../src/schema/billing.sql.js"
+
+const workspaceID = process.argv[2]
+
+if (!workspaceID) {
+  console.error("Usage: bun remove-black.ts <workspaceID>")
+  process.exit(1)
+}
+
+console.log(`Removing subscription from workspace ${workspaceID}`)
+
+// Look up the workspace billing
+const billing = await Database.use((tx) =>
+  tx
+    .select({
+      customerID: BillingTable.customerID,
+      subscriptionID: BillingTable.subscriptionID,
+    })
+    .from(BillingTable)
+    .where(eq(BillingTable.workspaceID, workspaceID))
+    .then((rows) => rows[0]),
+)
+
+if (!billing) {
+  console.error(`Error: No billing record found for workspace ${workspaceID}`)
+  process.exit(1)
+}
+
+if (!billing.subscriptionID) {
+  console.error(`Error: Workspace ${workspaceID} does not have a subscription`)
+  process.exit(1)
+}
+
+console.log(`  Customer ID: ${billing.customerID}`)
+console.log(`  Subscription ID: ${billing.subscriptionID}`)
+
+// Clear workspaceID from Stripe customer metadata
+if (billing.customerID) {
+  //await Billing.stripe().customers.update(billing.customerID, {
+  //  metadata: {
+  //    workspaceID: "",
+  //  },
+  //})
+  //console.log(`Cleared workspaceID from Stripe customer metadata`)
+}
+
+await Database.transaction(async (tx) => {
+  // Clear subscription-related fields from billing table
+  await tx
+    .update(BillingTable)
+    .set({
+      //      customerID: null,
+      subscriptionID: null,
+      subscriptionCouponID: null,
+      //     paymentMethodID: null,
+      //     paymentMethodLast4: null,
+      //     paymentMethodType: null,
+    })
+    .where(eq(BillingTable.workspaceID, workspaceID))
+
+  // Delete from subscription table
+  await tx.delete(SubscriptionTable).where(eq(SubscriptionTable.workspaceID, workspaceID))
+
+  // Delete from payments table
+  await tx
+    .delete(PaymentTable)
+    .where(
+      and(
+        eq(PaymentTable.workspaceID, workspaceID),
+        eq(PaymentTable.enrichment, { type: "subscription" }),
+        eq(PaymentTable.amount, 20000000000),
+      ),
+    )
+})
+
+console.log(`Successfully removed subscription from workspace ${workspaceID}`)