Derive domain status from subscription status and add backfill

Reviewed-on: http://gitea.lan:3000/robbond/pleskSaas/pulls/2

components/dashboard/dashboard-controls.tsx

@@ -52,11 +52,19 @@ type Props = {
 function getDomainStatusBadge(status: string | null) {
   const normalized = (status ?? "unknown").toLowerCase();
 
-  if (normalized === "active") return "bg-emerald-100 text-emerald-700";
+  if (
+    normalized.includes("active") ||
+    normalized.includes("ok") ||
+    normalized.includes("enabled")
+  ) {
+    return "bg-emerald-100 text-emerald-700";
+  }
 
-  if (normalized === "suspended") return "bg-rose-100 text-rose-700";
+  if (normalized.includes("suspended") || normalized.includes("disabled")) {
+    return "bg-rose-100 text-rose-700";
+  }
 
-  if (normalized === "inactive") return "bg-amber-100 text-amber-700";
+  if (normalized.includes("inactive")) return "bg-amber-100 text-amber-700";
 
   return "bg-slate-100 text-slate-600";
 }

lib/plesk/sync.ts

@@ -113,15 +113,44 @@ export async function syncPleskInstance(
       if (error) throw new Error(error.message);
     }
 
+    const { data: localSubscriptions, error: localSubscriptionsError } =
+      await supabase
+        .from("plesk_subscriptions")
+        .select("id, plesk_subscription_id, status")
+        .eq("agency_id", instance.agency_id)
+        .eq("plesk_instance_id", instance.id);
+
+    if (localSubscriptionsError) {
+      throw new Error(localSubscriptionsError.message);
+    }
+
+    const subscriptionByExternalId = new Map<
+      string,
+      { id: string; status: string | null }
+    >(
+      (localSubscriptions ?? [])
+        .filter((row: any) => row?.plesk_subscription_id && row?.id)
+        .map((row: any) => [
+          String(row.plesk_subscription_id),
+          {
+            id: String(row.id),
+            status: row?.status ? String(row.status) : null,
+          },
+        ]),
+    );
+
     const domainsForUpsert = domainsRaw
       .map((item: any) => {
         const domainId = item?.id ?? item?.guid ?? item?.name;
         const domainName = item?.name ?? item?.domain_name ?? item?.domain;
-        const status =
+        const subExternalId =
-          item?.status ??
-          (item?.hosting_type === "virtual" ? "active" : "inactive");
-        const externalSubscriptionId =
           item?.subscription?.id ?? item?.subscription_id ?? item?.webspace_id;
+        const externalSubscriptionId = subExternalId
+          ? String(subExternalId)
+          : null;
+        const localSubscription = externalSubscriptionId
+          ? subscriptionByExternalId.get(externalSubscriptionId)
+          : undefined;
         const rawCreated = item?.created_at ?? item?.created;
         const sourceCreatedAt = rawCreated
           ? new Date(String(rawCreated)).toISOString()
@@ -138,13 +167,11 @@ export async function syncPleskInstance(
         return {
           agency_id: instance.agency_id,
           plesk_instance_id: instance.id,
-          subscription_id: null,
+          subscription_id: localSubscription?.id ?? null,
-          external_subscription_id: externalSubscriptionId
+          external_subscription_id: externalSubscriptionId,
-            ? String(externalSubscriptionId)
-            : null,
           plesk_domain_id: String(domainId),
           domain_name: String(domainName),
-          status: status ? String(status) : "unknown",
+          status: localSubscription?.status ?? "unknown",
           hosting_type: item?.hosting_type ? String(item.hosting_type) : null,
           source_created_at: sourceCreatedAt,
           aliases_count: aliasesCount,

supabase/migrations/CL37_domain_status_backfill_from_subscription.sql

@@ -0,0 +1,6 @@
+-- One-time backfill: align existing domain status with linked subscription status
+update public.plesk_domains d
+set status = s.status
+from public.plesk_subscriptions s
+where d.subscription_id = s.id
+  and d.agency_id = s.agency_id;