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
);