import { sql } from 'drizzle-orm';
import {
  CommandClaimLostError,
  CommandNotFoundError,
  CommandTerminalTransitionError,
} from './errors.js';
import { mapCommandRow, selectCommandById } from './shared.js';
import type { CommandDb, CommandStatus, FailCommandInput, FailCommandResult } from './types.js';

interface UpdatedCommandRow {
  id: string;
  command_key: string;
  command_type: string;
  aggregate_type: string;
  aggregate_id: string;
  payload_hash: string;
  payload: unknown;
  status: CommandStatus;
  claimed_by: string;
  claimed_at: Date | string;
  claim_generation: number | string;
  lease_expires_at: Date | string;
  completed_at: Date | string | null;
  failed_at: Date | string | null;
  result_payload: unknown;
  failure_code: string | null;
  failure_message: string | null;
  outbox_id: string;
}

type UpdatedCommandRowResult = { rows: UpdatedCommandRow[] };

export async function failCommand(
  db: CommandDb,
  input: FailCommandInput,
): Promise<FailCommandResult> {
  const updated = (await db.execute(sql`
    WITH updated AS (
      UPDATE "command_records"
    SET
      "status" = 'FAILED',
      "completed_at" = NULL,
      "failed_at" = ${input.failedAt},
      "result_payload" = NULL,
      "failure_code" = ${input.failureCode},
      "failure_message" = ${input.failureMessage}
    WHERE "id" = ${input.commandId}::uuid
      AND "status" = 'CLAIMED'
      AND "claimed_by" = ${input.expectedClaimedBy}
      AND "claim_generation" = ${input.expectedClaimGeneration}
      AND "lease_expires_at" > CURRENT_TIMESTAMP
      RETURNING
      "id",
      "command_key",
      "command_type",
      "aggregate_type",
      "aggregate_id",
      "payload_hash",
      "payload",
      "status",
      "claimed_by",
      "claimed_at",
      "claim_generation",
      "lease_expires_at",
      "completed_at",
      "failed_at",
      "result_payload",
      "failure_code",
        "failure_message"
    ), inserted_outbox AS (
      INSERT INTO "outbox" (
        "aggregate_type", "aggregate_id", "event_type", "payload"
      )
      SELECT
        "aggregate_type",
        "aggregate_id",
        ${input.outbox.eventType},
        ${JSON.stringify(input.outbox.payload)}::jsonb
      FROM updated
      RETURNING "id"
    )
    SELECT updated.*, inserted_outbox."id" AS "outbox_id"
    FROM updated
    CROSS JOIN inserted_outbox
  `)) as UpdatedCommandRowResult;

  const updatedRow = updated.rows[0];
  if (!updatedRow) {
    const existing = await selectCommandById(db, input.commandId);
    if (!existing) throw new CommandNotFoundError(input.commandId);
    if (existing.status !== 'CLAIMED') {
      throw new CommandTerminalTransitionError(existing.status, 'fail');
    }
    throw new CommandClaimLostError(input.commandId, 'fail');
  }

  return { record: mapCommandRow(updatedRow), outboxId: updatedRow.outbox_id };
}