import { getSqlForDurableStore } from "@/lib/db";
import { computeEstimate, type EntityType, type Frequency } from "@/lib/data";
import { serviceById } from "@/lib/services";

export interface OrderConfig {
  markets?: string[];
  type?: EntityType;
  fieldIds?: string[];
  enrichmentIds?: string[];
  volume?: number;
  frequency?: Frequency;
  confidence?: number;
  formats?: string[];
  service_type?: string;
}

export interface Order {
  order_id: string;
  /** NULL for legacy anonymous drafts; never inferred from request config. */
  tenant_id?: string | null;
  buyer_subject?: string | null;
  flow: string;
  status: string;
  config: OrderConfig;
  total_ore: number;
  deposit_ore: number;
  balance_ore: number;
  est_days: number;
  est_confidence: number;
  created_at: string;
}

export interface OrderOwner {
  tenantId: string;
  subject: string;
}

/** Collision-free namespace within the existing global idempotency index. */
function ownedIdempotencyKey(owner: OrderOwner, key: string): string {
  return `owned:${owner.tenantId.length}:${owner.tenantId}:${key}`;
}

// Non-durable fallback used only until a database is provisioned.
const mem = new Map<string, Order>();
// idempotency_key -> order_id, mirroring the Postgres unique index added by
// sql/006_orders_idempotency.sql for the in-memory fallback path.
const memIdempotencyIndex = new Map<string, string>();

/**
 * Thrown by {@link createOrder} when a caller reuses an `Idempotency-Key`
 * for a semantically different request (different `flow` and/or `config`)
 * than the one that key was first used with. A route handler should map
 * this to HTTP 409 -- silently returning either order would be wrong (the
 * caller asked for the SAME operation to be safely retried, not for two
 * different operations to collapse into one).
 */
export class IdempotencyKeyConflictError extends Error {
  constructor(public readonly idempotencyKey: string) {
    super(`Idempotency-Key "${idempotencyKey}" was already used to create a different order (different flow/config)`);
    this.name = "IdempotencyKeyConflictError";
  }
}

/** Postgres error code 23505 = unique_violation (postgres.js surfaces it as `.code`). */
function isUniqueViolation(err: unknown): boolean {
  return typeof err === "object" && err !== null && (err as { code?: unknown }).code === "23505";
}

function sameSubmission(a: Order, flow: string, config: OrderConfig, owner?: OrderOwner): boolean {
  return a.flow === flow && JSON.stringify(a.config) === JSON.stringify(config)
    && (!owner || (a.tenant_id === owner.tenantId && a.buyer_subject === owner.subject));
}

export async function findOrderByIdempotencyKey(key: string): Promise<Order | null> {
  const sql = getSqlForDurableStore("0pt.orders");
  if (sql) {
    const rows = await sql<Order[]>`
      SELECT order_id, tenant_id, buyer_subject, flow, status, config, total_ore, deposit_ore, balance_ore, est_days, est_confidence, created_at
      FROM orders WHERE idempotency_key = ${key} LIMIT 1`;
    return rows[0] ? coerceOrder(rows[0]) : null;
  }
  const id = memIdempotencyIndex.get(key);
  return id ? mem.get(id) ?? null : null;
}

// postgres.js (pinned v3.4.9) returns bigint (total_ore/deposit_ore/balance_ore)
// and numeric (est_confidence) columns as strings by default, not JS numbers.
// Coerce only these four fields on rows read back from SQL -- not the shared
// `sql` client's global type map, which would silently affect every other
// table's bigint/numeric columns too (e.g. jobs.pct, deliveries.avg_confidence).
function coerceOrder(row: Order): Order {
  return {
    ...row,
    total_ore: Number(row.total_ore),
    deposit_ore: Number(row.deposit_ore),
    balance_ore: Number(row.balance_ore),
    est_confidence: Number(row.est_confidence),
  };
}

function estimateFor(config: OrderConfig) {
  return computeEstimate({
    markets: config.markets ?? [],
    type: config.type ?? "Bolag",
    fieldIds: config.fieldIds ?? [],
    enrichmentIds: config.enrichmentIds ?? [],
    volume: config.volume ?? 1000,
    frequency: config.frequency ?? "Engångs",
    confidence: config.confidence ?? 92,
    serviceMult: serviceById(config.service_type ?? "")?.mult ?? 1,
  });
}

export interface CreateOrderResult {
  order: Order;
  /** True when this call returned a PRE-EXISTING order (a safe retry under the same Idempotency-Key), not a newly created one. */
  replayed: boolean;
}

/**
 * P014: `idempotencyKey` is optional -- a caller that omits it (or the
 * pre-P014 call shape) behaves exactly as before, creating a fresh row every
 * call. When supplied (the `Idempotency-Key` header convention, see
 * sql/006_orders_idempotency.sql), a retry with the SAME key returns the
 * original order unchanged (`replayed: true`) instead of creating a
 * duplicate; a retry with the same key but a DIFFERENT flow/config throws
 * {@link IdempotencyKeyConflictError} rather than silently picking one.
 *
 * The Postgres path checks-then-inserts, which has a real (if narrow) race
 * window between two concurrent requests carrying the same fresh key -- the
 * UNIQUE INDEX from sql/006 is the actual source of truth, and a
 * unique-violation on INSERT is caught and re-resolved the same way a
 * pre-existing row would be, rather than surfacing a raw DB error.
 */
export async function createOrder(flow: string, config: OrderConfig, idempotencyKey?: string, owner?: OrderOwner): Promise<CreateOrderResult> {
  if (owner && (!owner.tenantId.trim() || !owner.subject.trim())) throw new Error("Verified order owner is required");
  const rawKey = idempotencyKey?.trim() || undefined;
  const key = rawKey && owner ? ownedIdempotencyKey(owner, rawKey) : rawKey;
  if (key) {
    const existing = await findOrderByIdempotencyKey(key);
    if (existing) {
      if (sameSubmission(existing, flow, config, owner)) return { order: existing, replayed: true };
      throw new IdempotencyKeyConflictError(key);
    }
  }

  const est = estimateFor(config);
  const sql = getSqlForDurableStore("0pt.orders");
  if (sql) {
    try {
      const [row] = await sql<{ order_id: string; created_at: string }[]>`
        INSERT INTO orders (tenant_id, buyer_subject, flow, status, config, total_ore, deposit_ore, balance_ore, est_days, est_confidence, idempotency_key)
        VALUES (${owner?.tenantId ?? null}, ${owner?.subject ?? null}, ${flow}, 'draft', ${sql.json(config as Parameters<typeof sql.json>[0])},
                ${est.price * 100}, ${est.deposit * 100}, ${est.remainder * 100},
                ${est.timelineDays}, ${est.estConfidence / 100}, ${key ?? null})
        RETURNING order_id, created_at`;
      return {
        order: {
          order_id: row.order_id, tenant_id: owner?.tenantId ?? null, buyer_subject: owner?.subject ?? null,
          flow, status: "draft", config,
          total_ore: est.price * 100, deposit_ore: est.deposit * 100, balance_ore: est.remainder * 100,
          est_days: est.timelineDays, est_confidence: est.estConfidence / 100, created_at: row.created_at,
        },
        replayed: false,
      };
    } catch (err) {
      if (key && isUniqueViolation(err)) {
        const raced = await findOrderByIdempotencyKey(key);
        if (raced) {
          if (sameSubmission(raced, flow, config, owner)) return { order: raced, replayed: true };
          throw new IdempotencyKeyConflictError(key);
        }
      }
      throw err;
    }
  }

  const id = (globalThis.crypto?.randomUUID?.() ?? `SX-${Math.floor(1000 + Math.random() * 9000)}`);
  const order: Order = {
    order_id: id, tenant_id: owner?.tenantId ?? null, buyer_subject: owner?.subject ?? null,
    flow, status: "draft", config,
    total_ore: est.price * 100, deposit_ore: est.deposit * 100, balance_ore: est.remainder * 100,
    est_days: est.timelineDays, est_confidence: est.estConfidence / 100, created_at: new Date().toISOString(),
  };
  mem.set(id, order);
  if (key) memIdempotencyIndex.set(key, id);
  return { order, replayed: false };
}

export async function getOrder(id: string): Promise<Order | null> {
  const sql = getSqlForDurableStore("0pt.orders");
  if (sql) {
    const rows = await sql<Order[]>`
      SELECT order_id, tenant_id, buyer_subject, flow, status, config, total_ore, deposit_ore, balance_ore, est_days, est_confidence, created_at
      FROM orders WHERE order_id::text = ${id} LIMIT 1`;
    return rows[0] ? coerceOrder(rows[0]) : null;
  }
  return mem.get(id) ?? null;
}

/** Buyer read is scoped in SQL itself; legacy NULL-owned rows are invisible. */
export async function getOwnedOrder(id: string, tenantId: string): Promise<Order | null> {
  const sql = getSqlForDurableStore("0pt.orders");
  if (sql) {
    const rows = await sql<Order[]>`
      SELECT order_id, tenant_id, buyer_subject, flow, status, config, total_ore, deposit_ore, balance_ore, est_days, est_confidence, created_at
      FROM orders WHERE order_id::text = ${id} AND tenant_id = ${tenantId} LIMIT 1`;
    return rows[0] ? coerceOrder(rows[0]) : null;
  }
  const order = mem.get(id);
  return order?.tenant_id === tenantId ? order : null;
}

export async function listOwnedOrders(tenantId: string): Promise<Order[]> {
  const sql = getSqlForDurableStore("0pt.orders");
  if (sql) {
    const rows = await sql<Order[]>`
      SELECT order_id, tenant_id, buyer_subject, flow, status, config, total_ore, deposit_ore, balance_ore, est_days, est_confidence, created_at
      FROM orders WHERE tenant_id = ${tenantId} ORDER BY created_at DESC LIMIT 100`;
    return rows.map(coerceOrder);
  }
  return [...mem.values()].filter((o) => o.tenant_id === tenantId)
    .sort((a, b) => b.created_at.localeCompare(a.created_at));
}

export async function listOrders(): Promise<Order[]> {
  const sql = getSqlForDurableStore("0pt.orders");
  if (sql) {
    const rows = await sql<Order[]>`
      SELECT order_id, tenant_id, buyer_subject, flow, status, config, total_ore, deposit_ore, balance_ore, est_days, est_confidence, created_at
      FROM orders ORDER BY created_at DESC LIMIT 100`;
    return rows.map(coerceOrder);
  }
  return [...mem.values()].sort((a, b) => b.created_at.localeCompare(a.created_at));
}

export async function setStatus(id: string, status: string): Promise<Order | null> {
  const sql = getSqlForDurableStore("0pt.orders");
  if (sql) {
    const rows = await sql<Order[]>`
      UPDATE orders SET status = ${status}, updated_at = now() WHERE order_id::text = ${id}
      RETURNING order_id, tenant_id, buyer_subject, flow, status, config, total_ore, deposit_ore, balance_ore, est_days, est_confidence, created_at`;
    return rows[0] ? coerceOrder(rows[0]) : null;
  }
  const o = mem.get(id);
  if (o) { o.status = status; mem.set(id, o); }
  return o ?? null;
}

/** Atomic status transition; rejects stale concurrent writers instead of overwriting them. */
export async function setStatusIfCurrent(id: string, current: string, next: string): Promise<Order | null> {
  const sql = getSqlForDurableStore("0pt.orders");
  if (sql) {
    const rows = await sql<Order[]>`
      UPDATE orders SET status = ${next}, updated_at = now()
      WHERE order_id::text = ${id} AND status = ${current}
      RETURNING order_id, tenant_id, buyer_subject, flow, status, config, total_ore, deposit_ore, balance_ore, est_days, est_confidence, created_at`;
    return rows[0] ? coerceOrder(rows[0]) : null;
  }
  const order = mem.get(id);
  if (!order || order.status !== current) return null;
  order.status = next;
  mem.set(id, order);
  return order;
}

/** Buyer status change is both atomic and tenant-scoped at the SQL boundary. */
export async function setOwnedStatusIfCurrent(
  id: string, tenantId: string, current: string, next: string,
): Promise<Order | null> {
  const sql = getSqlForDurableStore("0pt.orders");
  if (sql) {
    const rows = await sql<Order[]>`
      UPDATE orders SET status = ${next}, updated_at = now()
      WHERE order_id::text = ${id} AND tenant_id = ${tenantId} AND status = ${current}
      RETURNING order_id, tenant_id, buyer_subject, flow, status, config, total_ore, deposit_ore,
                balance_ore, est_days, est_confidence, created_at`;
    return rows[0] ? coerceOrder(rows[0]) : null;
  }
  const order = mem.get(id);
  if (!order || order.tenant_id !== tenantId || order.status !== current) return null;
  order.status = next;
  mem.set(id, order);
  return order;
}