CREATE TABLE "jobs" (
  "id" uuid PRIMARY KEY NOT NULL,
  "type" text NOT NULL,
  "payload" jsonb NOT NULL,
  "status" text NOT NULL DEFAULT 'pending',
  "scheduled_for" timestamp with time zone NOT NULL DEFAULT now(),
  "attempts" integer NOT NULL DEFAULT 0,
  "max_attempts" integer NOT NULL DEFAULT 5,
  "last_error" text,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  "processed_at" timestamp with time zone,
  "failed_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "job_idempotency_keys" (
  "key" text PRIMARY KEY NOT NULL,
  "status" text NOT NULL DEFAULT 'processing',
  "first_seen_at" timestamp with time zone NOT NULL DEFAULT now(),
  "processed_at" timestamp with time zone,
  "expires_at" timestamp with time zone,
  "last_released_at" timestamp with time zone,
  "release_count" integer NOT NULL DEFAULT 0,
  "payload" jsonb
);
--> statement-breakpoint
ALTER TABLE "jobs"
  ADD CONSTRAINT "jobs_status_check"
  CHECK ("status" IN ('pending', 'processing', 'completed', 'failed'));
--> statement-breakpoint
ALTER TABLE "job_idempotency_keys"
  ADD CONSTRAINT "job_idempotency_keys_status_check"
  CHECK ("status" IN ('processing', 'processed'));
--> statement-breakpoint
CREATE INDEX "idx_jobs_status_scheduled_for" ON "jobs" ("status", "scheduled_for");
--> statement-breakpoint
CREATE INDEX "idx_jobs_type_created_at" ON "jobs" ("type", "created_at");
--> statement-breakpoint
CREATE INDEX "idx_job_idempotency_keys_status" ON "job_idempotency_keys" ("status", "first_seen_at");
--> statement-breakpoint
INSERT INTO "jobs" (
  "id", "type", "payload", "status", "scheduled_for", "attempts", "max_attempts",
  "last_error", "created_at", "processed_at", "failed_at"
)
SELECT
  ij."id",
  'invoice.generate',
  jsonb_build_object(
    'tenantId', ij."tenant_id",
    'jobId', ij."id",
    'legacyType', ij."type",
    'legacyMeta', coalesce(ij."column_mapping", '{}'::jsonb)
  ),
  CASE
    WHEN ij."status" = 'processing' THEN 'processing'
    WHEN ij."status" = 'completed' THEN 'completed'
    WHEN ij."status" = 'failed' THEN 'failed'
    ELSE 'pending'
  END,
  coalesce(ij."started_at", ij."created_at"),
  0,
  5,
  ij."error_message",
  ij."created_at",
  CASE WHEN ij."status" = 'completed' THEN ij."completed_at" ELSE NULL END,
  CASE WHEN ij."status" = 'failed' THEN coalesce(ij."completed_at", ij."updated_at", ij."created_at") ELSE NULL END
FROM "import_jobs" ij
WHERE ij."type" = 'bulk_action'
  AND coalesce(ij."column_mapping"->>'action', '') = 'invoice_generate'
ON CONFLICT ("id") DO NOTHING;
--> statement-breakpoint
INSERT INTO "jobs" (
  "id", "type", "payload", "status", "scheduled_for", "attempts", "max_attempts",
  "last_error", "created_at", "processed_at", "failed_at"
)
SELECT
  uj."id",
  'uniform-format',
  jsonb_build_object(
    'tenantId', uj."tenant_id",
    'jobId', uj."id",
    'mode', uj."mode",
    'periodFrom', uj."period_from",
    'periodTo', uj."period_to"
  ),
  CASE
    WHEN uj."status" = 'running' THEN 'processing'
    WHEN uj."status" = 'done' THEN 'completed'
    WHEN uj."status" = 'error' THEN 'failed'
    ELSE 'pending'
  END,
  uj."created_at",
  0,
  5,
  uj."error_message",
  uj."created_at",
  CASE WHEN uj."status" = 'done' THEN coalesce(uj."download_expires_at", uj."created_at") ELSE NULL END,
  CASE WHEN uj."status" = 'error' THEN uj."created_at" ELSE NULL END
FROM "uniform_export_jobs" uj
ON CONFLICT ("id") DO NOTHING;
--> statement-breakpoint
INSERT INTO "jobs" (
  "id", "type", "payload", "status", "scheduled_for", "attempts", "max_attempts",
  "last_error", "created_at", "processed_at", "failed_at"
)
SELECT
  tej."id",
  'tenant.export',
  jsonb_build_object('tenantId', tej."tenant_id", 'jobId', tej."id"),
  CASE
    WHEN tej."status" = 'PROCESSING' THEN 'processing'
    WHEN tej."status" = 'DONE' THEN 'completed'
    WHEN tej."status" = 'FAILED' THEN 'failed'
    ELSE 'pending'
  END,
  tej."created_at",
  0,
  5,
  NULL,
  tej."created_at",
  CASE WHEN tej."status" = 'DONE' THEN tej."completed_at" ELSE NULL END,
  CASE WHEN tej."status" = 'FAILED' THEN coalesce(tej."completed_at", tej."created_at") ELSE NULL END
FROM "tenant_export_jobs" tej
ON CONFLICT ("id") DO NOTHING;
