import { sql } from 'drizzle-orm';
import { insertOutboxRow } from '@/server/db/queries/outbox.js';
import type { CommandDb, CommandOutboxInput, CommandRecord, CommandStatus } from './types.js';

interface CommandRow {
  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;
}

type CommandRowResult = { rows: CommandRow[] };

const COMMAND_COLUMNS = sql.raw(`
  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
`);

function asDate(value: Date | string | null): Date | null {
  if (value == null) return null;
  return value instanceof Date ? value : new Date(value);
}

function asPayload(value: unknown): Record<string, unknown> | null {
  if (value == null) return null;
  if (typeof value === 'string') return JSON.parse(value) as Record<string, unknown>;
  return value as Record<string, unknown>;
}

export function mapCommandRow(row: CommandRow): CommandRecord {
  return {
    id: row.id,
    commandKey: row.command_key,
    commandType: row.command_type,
    aggregateType: row.aggregate_type,
    aggregateId: row.aggregate_id,
    payloadHash: row.payload_hash,
    payload: asPayload(row.payload) ?? {},
    status: row.status,
    claimedBy: row.claimed_by,
    claimedAt: asDate(row.claimed_at) ?? new Date(0),
    claimGeneration: Number(row.claim_generation),
    leaseExpiresAt: asDate(row.lease_expires_at) ?? new Date(0),
    completedAt: asDate(row.completed_at),
    failedAt: asDate(row.failed_at),
    resultPayload: asPayload(row.result_payload),
    failureCode: row.failure_code,
    failureMessage: row.failure_message,
  };
}

export async function selectCommandByKey(
  db: CommandDb,
  commandKey: string,
): Promise<CommandRecord | null> {
  const result = (await db.execute(sql`
    SELECT ${COMMAND_COLUMNS}
    FROM "command_records"
    WHERE "command_key" = ${commandKey}
    LIMIT 1
  `)) as CommandRowResult;
  const row = result.rows[0];
  return row ? mapCommandRow(row) : null;
}

export async function selectCommandById(
  db: CommandDb,
  commandId: string,
): Promise<CommandRecord | null> {
  const result = (await db.execute(sql`
    SELECT ${COMMAND_COLUMNS}
    FROM "command_records"
    WHERE "id" = ${commandId}::uuid
    LIMIT 1
  `)) as CommandRowResult;
  const row = result.rows[0];
  return row ? mapCommandRow(row) : null;
}

export async function insertCommandOutbox(
  db: CommandDb,
  input: {
    aggregateType: string;
    aggregateId: string;
    outbox: CommandOutboxInput;
  },
): Promise<string> {
  const { id } = await insertOutboxRow(db, {
    aggregateType: input.aggregateType,
    aggregateId: input.aggregateId,
    eventType: input.outbox.eventType,
    payload: input.outbox.payload,
  });
  return id;
}