CREATE TABLE "change_logs" (
	"id" serial PRIMARY KEY NOT NULL,
	"contract_id" integer NOT NULL,
	"entity_type" varchar(50) NOT NULL,
	"entity_id" integer NOT NULL,
	"action" varchar(20) NOT NULL,
	"changed_fields" jsonb,
	"old_values" jsonb,
	"new_values" jsonb,
	"user_id" integer,
	"user_name" varchar(255),
	"created_at" timestamp DEFAULT now() NOT NULL,
	"is_synced" boolean DEFAULT false,
	"sync_attempts" integer DEFAULT 0,
	"last_sync_error" text,
	"synced_at" timestamp,
	"target_systems" jsonb DEFAULT '[]'::jsonb
);
--> statement-breakpoint
CREATE TABLE "sync_batches" (
	"id" serial PRIMARY KEY NOT NULL,
	"contract_id" integer NOT NULL,
	"target_system" varchar(50) NOT NULL,
	"batch_size" integer NOT NULL,
	"status" varchar(20) DEFAULT 'PENDING' NOT NULL,
	"started_at" timestamp,
	"completed_at" timestamp,
	"error_message" text,
	"retry_count" integer DEFAULT 0,
	"change_log_ids" jsonb NOT NULL,
	"success_count" integer DEFAULT 0,
	"failure_count" integer DEFAULT 0,
	"sync_metadata" jsonb,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "sync_configs" (
	"id" serial PRIMARY KEY NOT NULL,
	"contract_id" integer NOT NULL,
	"target_system" varchar(50) NOT NULL,
	"sync_enabled" boolean DEFAULT true,
	"sync_interval_minutes" integer DEFAULT 30,
	"last_successful_sync" timestamp,
	"last_sync_attempt" timestamp,
	"endpoint_url" text NOT NULL,
	"auth_token" text,
	"api_version" varchar(20) DEFAULT 'v1',
	"max_batch_size" integer DEFAULT 100,
	"retry_max_attempts" integer DEFAULT 3,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "sync_configs" ADD CONSTRAINT "sync_configs_contract_id_contracts_id_fk" FOREIGN KEY ("contract_id") REFERENCES "public"."contracts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_change_logs_contract_synced" ON "change_logs" USING btree ("contract_id","is_synced");--> statement-breakpoint
CREATE INDEX "idx_change_logs_created_at" ON "change_logs" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "idx_change_logs_entity" ON "change_logs" USING btree ("entity_type","entity_id");--> statement-breakpoint
CREATE INDEX "idx_change_logs_sync_attempts" ON "change_logs" USING btree ("sync_attempts");--> statement-breakpoint
CREATE INDEX "idx_sync_batches_contract_system" ON "sync_batches" USING btree ("contract_id","target_system");--> statement-breakpoint
CREATE INDEX "idx_sync_batches_status" ON "sync_batches" USING btree ("status");--> statement-breakpoint
CREATE INDEX "idx_sync_batches_created_at" ON "sync_batches" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "idx_sync_configs_contract_system" ON "sync_configs" USING btree ("contract_id","target_system");--> statement-breakpoint
CREATE VIEW "public"."sync_status_view" AS (
  WITH change_stats AS (
    SELECT 
      cl.contract_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.contract_id = sc.contract_id
      AND (cl.target_systems IS NULL OR cl.target_systems @> to_jsonb(sc.target_system))
    GROUP BY cl.contract_id, sc.target_system
  )
  SELECT 
    cs.contract_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.contract_id = cs.contract_id AND sc.target_system = cs.target_system
);