1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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
);
|
