1 files changed, 43 insertions, 0 deletions
diff --git a/db/migrations/0247_bright_bullseye.sql b/db/migrations/0247_bright_bullseye.sql
new file mode 100644
index 00000000..f6432bb5
--- /dev/null
+++ b/db/migrations/0247_bright_bullseye.sql
@@ -0,0 +1,43 @@
+DROP VIEW "public"."sync_status_view";--> statement-breakpoint
+ALTER TABLE "sync_configs" DROP CONSTRAINT "sync_configs_contract_id_contracts_id_fk";
+--> statement-breakpoint
+DROP INDEX "idx_change_logs_contract_synced";--> statement-breakpoint
+DROP INDEX "idx_sync_batches_contract_system";--> statement-breakpoint
+DROP INDEX "idx_sync_configs_contract_system";--> statement-breakpoint
+CREATE INDEX "idx_sync_configs_contract_system" ON "sync_configs" USING btree ("project_id","target_system");--> statement-breakpoint
+ALTER TABLE "change_logs" DROP COLUMN "contract_id";--> statement-breakpoint
+ALTER TABLE "sync_batches" DROP COLUMN "contract_id";--> statement-breakpoint
+ALTER TABLE "sync_configs" DROP COLUMN "contract_id";--> statement-breakpoint
+CREATE VIEW "public"."sync_status_view" AS (
+  WITH change_stats AS (
+    SELECT 
+      cl.project_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.project_id = sc.project_id
+      AND (cl.target_systems IS NULL OR cl.target_systems @> to_jsonb(ARRAY[sc.target_system]))
+    GROUP BY cl.project_id, sc.target_system
+  )
+  SELECT 
+    cs.project_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.project_id = cs.project_id AND sc.target_system = cs.target_system
+);
+\ No newline at end of file