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