1 files changed, 37 insertions, 0 deletions
diff --git a/db/migrations/0289_watery_stryfe.sql b/db/migrations/0289_watery_stryfe.sql
new file mode 100644
index 00000000..1b026749
--- /dev/null
+++ b/db/migrations/0289_watery_stryfe.sql
@@ -0,0 +1,37 @@
+DROP VIEW "public"."sync_status_view";--> statement-breakpoint
+ALTER TABLE "sync_configs" RENAME COLUMN "project_id" TO "vendor_id";--> statement-breakpoint
+DROP INDEX "idx_sync_configs_contract_system";--> statement-breakpoint
+CREATE INDEX "idx_sync_configs_contract_system" ON "sync_configs" USING btree ("vendor_id","target_system");--> statement-breakpoint
+CREATE VIEW "public"."sync_status_view" AS (
+  WITH change_stats AS (
+    SELECT 
+      cl.vendor_id,
+      sc.target_system,
+      COUNT(*) as total_changes,
+      COUNT(CASE WHEN cl.is_synced = false AND cl.sync_attempts < sc.retry_max_attempts THEN 1 END) as pending_changes,
+      COUNT(CASE WHEN cl.is_synced = true THEN 1 END) as synced_changes,
+      COUNT(CASE WHEN cl.sync_attempts >= sc.retry_max_attempts AND cl.is_synced = false THEN 1 END) as failed_changes,
+      MAX(cl.synced_at) as last_sync_at
+    FROM change_logs cl
+    CROSS JOIN sync_configs sc 
+    WHERE cl.vendor_id = sc.vendor_id
+      AND (cl.target_systems IS NULL OR cl.target_systems @> to_jsonb(ARRAY[sc.target_system]))
+    GROUP BY cl.vendor_id, sc.target_system
+  )
+  SELECT 
+    cs.vendor_id,
+    cs.target_system,
+    COALESCE(cs.total_changes, 0) as total_changes,
+    COALESCE(cs.pending_changes, 0) as pending_changes,
+    COALESCE(cs.synced_changes, 0) as synced_changes,
+    COALESCE(cs.failed_changes, 0) as failed_changes,
+    cs.last_sync_at,
+    CASE 
+      WHEN sc.sync_enabled = true AND sc.last_successful_sync IS NOT NULL 
+      THEN sc.last_successful_sync + (sc.sync_interval_minutes || ' minutes')::interval
+      ELSE NULL
+    END as next_sync_at,
+    sc.sync_enabled
+  FROM sync_configs sc
+  LEFT JOIN change_stats cs ON sc.vendor_id = cs.vendor_id AND sc.target_system = cs.target_system
+);
+\ No newline at end of file