/**
 * Cart query module — all DB access for cart goes through here.
 *
 * Qty clamping rule: min(deal.maxPerUser, deal.stockRemaining).
 * Null maxPerUser = unbounded (clamp to remaining stock only).
 *
 * Stale/removed reasons returned by getCart:
 *   'expired'   — deal windowEnd has passed or dealState is EXPIRED
 *   'inactive'  — dealState is not ACTIVE (DRAFT, PAUSED, SOLD_OUT, etc.)
 *   'sold_out'  — no remaining stock
 *
 * Join path (post-contract): cart_line.variant_id → dealSkus.id → deals.
 * Display price source: dealSkus.discountedPrice (live).
 * Checkout price source: cart_line.amount (stored at add-time).
 * Stock source: deals.stockRemaining (cache col, aggregate across all SKUs).
 */

import { eq, and, inArray, sql } from 'drizzle-orm';
import type { DrizzleClient, DrizzleDb } from '../client.js';
import { bumpMhVersion } from '@/server/auth/session.js';
import { cart, cartItems, cartLine, dealImages, deals, dealSkus, vendors } from '../schema.js';
import { executeRows, firstExecuteRow } from '../execute-rows.js';
import { getQtyTiersForSkus } from './sku-qty-tiers.js';
import { applyQtyTier } from '../../pricing/qty-tier.js';
import { formatAgorotPlain } from '@/lib/money';
import { getOrCreateCartForUser, toPriceSnapshot } from '@/server/cart/cart-platform.js';
import { captureCaught } from '@/server/observability/capture.server';

// ---------------------------------------------------------------------------
// Types
// ---------------------------------------------------------------------------

export type CartItemRow = typeof cartItems.$inferSelect;

export type CartLineItem = {
  cartItemId: string;
  dealId: string;
  dealSkuId: string | null;
  qty: number;
  addedAt: Date;
  // deal snapshot
  dealTitle: string;
  vendorId: string;
  vendorName: string;
  discountedPrice: string;
  originalPrice: string;
  commissionRate: string;
  maxPerUser: number | null;
  remainingStock: number;
  dealState: string;
  windowEnd: Date | null;
  imageUrl: string | null;
  qtyTiers: { minQty: number; discountPercent: number }[];
};

export type StaleEntry = {
  dealId: string;
  reason: 'expired' | 'inactive' | 'sold_out';
};

export type GetCartResult = {
  items: CartLineItem[];
  removed: StaleEntry[];
  subtotal: string;
};

// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------

/** Compute effective remaining stock from cache column (never negative). */
function remainingStock(deal: { stockRemaining: number | null }): number {
  return Math.max(0, deal.stockRemaining ?? 0);
}

/** Clamp qty to min(maxPerUser, remainingStock). maxPerUser=null means no per-user cap. */
export function clampQty(requestedQty: number, maxPerUser: number | null, stock: number): number {
  const cap = maxPerUser !== null ? Math.min(maxPerUser, stock) : stock;
  return Math.max(0, Math.min(requestedQty, cap));
}

/** Check if a deal is stale (inactive/expired/sold-out). */
function staleness(deal: {
  dealState: string;
  windowEnd: Date | null;
  stockRemaining: number | null;
}): 'expired' | 'inactive' | 'sold_out' | null {
  const now = new Date();
  if (deal.windowEnd && deal.windowEnd < now) return 'expired';
  if (deal.dealState === 'EXPIRED') return 'expired';
  if (deal.dealState !== 'ACTIVE') return 'inactive';
  if (remainingStock(deal) <= 0) return 'sold_out';
  return null;
}

function storedAgorotToDecimalString(agorot: bigint | string | number): string {
  const n = BigInt(agorot);
  return (n / 100n).toString() + '.' + String(n % 100n).padStart(2, '0');
}

async function bumpCartUpdatedAt(db: DrizzleClient, cartId: string): Promise<void> {
  await db.update(cart).set({ updatedAt: new Date() }).where(eq(cart.id, cartId));
}

/** getOrCreateCartForUser expects DrizzleDb; cart queries receive DrizzleClient (same runtime). */
function asCartDb(db: DrizzleClient): DrizzleDb {
  return db as unknown as DrizzleDb;
}

// ---------------------------------------------------------------------------
// CartRawRow — the shape expected by processCartRows
// ---------------------------------------------------------------------------

/** Raw row shape accepted by processCartRows. Date fields must already be Date instances. */
export type CartRawRow = {
  cartItemId: string;
  dealId: string;
  dealSkuId: string | null;
  qty: number;
  addedAt: Date;
  dealTitle: string;
  vendorId: string;
  vendorName: string;
  discountedPrice: string;
  originalPrice: string;
  commissionRate: string;
  maxPerUser: number | null;
  stockRemaining: number | null;
  dealState: string;
  windowEnd: Date | null;
  imageUrl: string | null;
  qtyTiers: { minQty: number; discountPercent: number }[];
};

// ---------------------------------------------------------------------------
// processCartRows — shared post-processing (staleness + clamp + subtotal)
// ---------------------------------------------------------------------------

/**
 * Apply staleness filtering, qty clamping, and subtotal to raw cart rows.
 * Date fields (addedAt, windowEnd) must be Date instances before calling.
 */
export function processCartRows(rows: CartRawRow[]): GetCartResult {
  const validItems: CartLineItem[] = [];
  const removed: StaleEntry[] = [];

  for (const row of rows) {
    const stale = staleness(row);
    if (stale) {
      removed.push({ dealId: row.dealId, reason: stale });
      continue;
    }
    const stock = remainingStock(row);
    const effectiveQty = clampQty(row.qty, row.maxPerUser, stock);
    validItems.push({
      cartItemId: row.cartItemId,
      dealId: row.dealId,
      dealSkuId: row.dealSkuId,
      qty: effectiveQty,
      addedAt: row.addedAt,
      dealTitle: row.dealTitle,
      vendorId: row.vendorId,
      vendorName: row.vendorName,
      discountedPrice: row.discountedPrice,
      originalPrice: row.originalPrice,
      commissionRate: row.commissionRate,
      maxPerUser: row.maxPerUser,
      remainingStock: stock,
      dealState: row.dealState,
      windowEnd: row.windowEnd,
      imageUrl: row.imageUrl,
      qtyTiers: row.qtyTiers,
    });
  }

  const subtotalAgorot = validItems.reduce((sum, item) => {
    const unitAgorot = Math.round(parseFloat(item.discountedPrice) * 100);
    const { lineTotalAgorot } = applyQtyTier(unitAgorot, item.qty, item.qtyTiers);
    return sum + lineTotalAgorot;
  }, 0);
  const subtotal = formatAgorotPlain(subtotalAgorot);

  return { items: validItems, removed, subtotal };
}

// ---------------------------------------------------------------------------
// getCart
// ---------------------------------------------------------------------------

/**
 * Return cart items joined with deal snapshots via variant_id → dealSkus → deals.
 * Stale items are filtered out of `items` and listed in `removed`.
 */
export async function getCart(db: DrizzleClient, userId: string): Promise<GetCartResult> {
  const cartId = await getOrCreateCartForUser(asCartDb(db), userId);

  const rows = await db
    .select({
      cart_item_id: cartLine.id,
      deal_sku_id: cartLine.variantId,
      qty: cartLine.qty,
      added_at: cart.updatedAt,
      deal_id: dealSkus.dealId,
      deal_title: deals.title,
      vendor_id: deals.vendorId,
      vendor_name: vendors.displayName,
      discounted_price: dealSkus.discountedPrice,
      original_price: dealSkus.originalPrice,
      commission_rate: deals.commissionRate,
      max_per_user: deals.maxPerUser,
      stock_remaining: deals.stockRemaining,
      deal_state: deals.dealState,
      window_end: deals.windowEnd,
      image_url: dealImages.url,
    })
    .from(cartLine)
    .innerJoin(cart, eq(cartLine.cartId, cart.id))
    .innerJoin(dealSkus, eq(dealSkus.id, cartLine.variantId))
    .innerJoin(deals, eq(deals.id, dealSkus.dealId))
    .innerJoin(vendors, eq(vendors.id, deals.vendorId))
    .leftJoin(dealImages, and(eq(dealImages.dealId, deals.id), eq(dealImages.isPrimary, true)))
    .where(eq(cartLine.cartId, cartId));

  const skuIds = rows.map((r) => r.deal_sku_id).filter((id): id is string => id !== null);
  const tierMap = await getQtyTiersForSkus(db, skuIds);
  const rawRows: CartRawRow[] = rows.map((r) => ({
    cartItemId: r.cart_item_id,
    dealId: r.deal_id,
    dealSkuId: r.deal_sku_id,
    qty: r.qty,
    addedAt: new Date(r.added_at),
    dealTitle: r.deal_title,
    vendorId: r.vendor_id,
    vendorName: r.vendor_name,
    discountedPrice: r.discounted_price,
    originalPrice: r.original_price,
    commissionRate: r.commission_rate,
    maxPerUser: r.max_per_user,
    stockRemaining: r.stock_remaining,
    dealState: r.deal_state,
    windowEnd: r.window_end ? new Date(r.window_end) : null,
    imageUrl: r.image_url,
    qtyTiers: r.deal_sku_id ? (tierMap.get(r.deal_sku_id) ?? []) : [],
  }));

  return processCartRows(rawRows);
}

// ---------------------------------------------------------------------------
// upsertCartItem
// ---------------------------------------------------------------------------

/**
 * Insert or update a cart line item, clamping qty to allowed limits.
 * Returns the clamped qty (which may be less than requested if at cap).
 * Uses (userId, dealId) conflict target matching the current DB constraint.
 */
export async function upsertCartItem(
  db: DrizzleClient,
  userId: string,
  dealSkuId: string,
  requestedQty: number,
): Promise<{ qty: number }> {
  const [sku] = await db
    .select({
      dealId: dealSkus.dealId,
      stockRemaining: deals.stockRemaining,
      maxPerUser: deals.maxPerUser,
      vendorId: deals.vendorId,
      discountedPrice: dealSkus.discountedPrice,
    })
    .from(dealSkus)
    .innerJoin(deals, eq(dealSkus.dealId, deals.id))
    .where(eq(dealSkus.id, dealSkuId))
    .limit(1);

  if (!sku) throw new Error('Deal SKU not found');

  const stock = remainingStock(sku);
  const cap = sku.maxPerUser !== null ? Math.min(sku.maxPerUser, stock) : stock;
  const clampedQty = Math.max(0, Math.min(requestedQty, cap));
  const cartId = await getOrCreateCartForUser(asCartDb(db), userId);
  const priceSnapshot = toPriceSnapshot(sku.discountedPrice, sku.vendorId);

  const upsertResult = await db.execute<{ qty: number }>(sql`
    INSERT INTO cart_line (cart_id, variant_id, qty, amount, currency, price_mode, vendor_id)
    VALUES (
      ${cartId},
      ${dealSkuId},
      ${clampedQty},
      ${priceSnapshot.amount},
      ${priceSnapshot.currency},
      ${priceSnapshot.priceMode},
      ${priceSnapshot.vendorId}
    )
    ON CONFLICT (cart_id, variant_id) DO UPDATE
    SET qty = LEAST(cart_line.qty + EXCLUDED.qty, ${cap}::int), amount = EXCLUDED.amount
    RETURNING qty
  `);

  const row = firstExecuteRow<{ qty: number }>(upsertResult);
  const resultQty = row?.qty ?? clampedQty;

  await bumpCartUpdatedAt(db, cartId);

  try {
    await db
      .insert(cartItems)
      .values({ userId, dealSkuId, qty: resultQty })
      .onConflictDoUpdate({
        target: [cartItems.userId, cartItems.dealSkuId],
        set: { qty: resultQty },
      });
  } catch (err) {
    captureCaught(err, {
      scope: 'cart.dual-write',
      severity: 'warning',
      extra: { op: 'upsertCartItem' },
    });
  }

  await bumpMhVersion(db, userId);
  return { qty: resultQty };
}

// ---------------------------------------------------------------------------
// updateCartItemQty
// ---------------------------------------------------------------------------

export async function updateCartItemQty(
  db: DrizzleClient,
  userId: string,
  dealSkuId: string,
  requestedQty: number,
): Promise<{ qty: number } | null> {
  const [sku] = await db
    .select({
      stockRemaining: deals.stockRemaining,
      maxPerUser: deals.maxPerUser,
    })
    .from(dealSkus)
    .innerJoin(deals, eq(dealSkus.dealId, deals.id))
    .where(eq(dealSkus.id, dealSkuId))
    .limit(1);

  if (!sku) return null;

  const stock = remainingStock(sku);
  const qty = clampQty(requestedQty, sku.maxPerUser, stock);
  const cartId = await getOrCreateCartForUser(asCartDb(db), userId);

  const updated = await db
    .update(cartLine)
    .set({ qty })
    .where(and(eq(cartLine.cartId, cartId), eq(cartLine.variantId, dealSkuId)))
    .returning({ qty: cartLine.qty });

  if (!updated[0]) return null;

  await bumpCartUpdatedAt(db, cartId);

  try {
    await db
      .update(cartItems)
      .set({ qty })
      .where(and(eq(cartItems.userId, userId), eq(cartItems.dealSkuId, dealSkuId)));
  } catch (err) {
    captureCaught(err, {
      scope: 'cart.dual-write',
      severity: 'warning',
      extra: { op: 'updateCartItemQty' },
    });
  }

  await bumpMhVersion(db, userId);
  return { qty: updated[0].qty };
}

// ---------------------------------------------------------------------------
// removeCartItem
// ---------------------------------------------------------------------------

export async function removeCartItem(
  db: DrizzleClient,
  userId: string,
  dealSkuId: string,
): Promise<boolean> {
  const cartId = await getOrCreateCartForUser(asCartDb(db), userId);

  const deleted = await db
    .delete(cartLine)
    .where(and(eq(cartLine.cartId, cartId), eq(cartLine.variantId, dealSkuId)))
    .returning({ id: cartLine.id });

  if (deleted.length === 0) return false;

  await bumpCartUpdatedAt(db, cartId);

  try {
    await db
      .delete(cartItems)
      .where(and(eq(cartItems.userId, userId), eq(cartItems.dealSkuId, dealSkuId)));
  } catch (err) {
    captureCaught(err, {
      scope: 'cart.dual-write',
      severity: 'warning',
      extra: { op: 'removeCartItem' },
    });
  }

  await bumpMhVersion(db, userId);
  return true;
}

// ---------------------------------------------------------------------------
// clearCart
// ---------------------------------------------------------------------------

export async function clearCart(db: DrizzleClient, userId: string): Promise<void> {
  const cartId = await getOrCreateCartForUser(asCartDb(db), userId);

  const deleted = await db
    .delete(cartLine)
    .where(eq(cartLine.cartId, cartId))
    .returning({ id: cartLine.id });

  if (deleted.length === 0) return;

  await bumpCartUpdatedAt(db, cartId);

  try {
    await db.delete(cartItems).where(eq(cartItems.userId, userId));
  } catch (err) {
    captureCaught(err, {
      scope: 'cart.dual-write',
      severity: 'warning',
      extra: { op: 'clearCart' },
    });
  }

  await bumpMhVersion(db, userId);
}

// ---------------------------------------------------------------------------
// mergeCart — union-max policy
// ---------------------------------------------------------------------------

/**
 * Merge client-side cart items into the server cart using union-max policy:
 * for each dealSkuId, keep max(serverQty, clientQty), then re-clamp to limits.
 *
 * Returns the merged cart result including any removed stale entries.
 */
export async function mergeCart(
  db: DrizzleClient,
  userId: string,
  incomingItems: { dealSkuId: string; qty: number }[],
): Promise<GetCartResult> {
  if (incomingItems.length === 0) return getCart(db, userId);

  const dealSkuIds = incomingItems.map((i) => i.dealSkuId);
  const cartId = await getOrCreateCartForUser(asCartDb(db), userId);

  // Load deal limits for all incoming SKUs
  const skuRows = await db
    .select({
      id: dealSkus.id,
      dealId: dealSkus.dealId,
      discountedPrice: dealSkus.discountedPrice,
      vendorId: deals.vendorId,
      stockRemaining: deals.stockRemaining,
      maxPerUser: deals.maxPerUser,
    })
    .from(dealSkus)
    .innerJoin(deals, eq(dealSkus.dealId, deals.id))
    .where(inArray(dealSkus.id, dealSkuIds));

  const skuMap = new Map(skuRows.map((s) => [s.id, s]));

  const existingRows = await db
    .select({
      variant_id: cartLine.variantId,
      qty: cartLine.qty,
      amount: cartLine.amount,
    })
    .from(cartLine)
    .where(eq(cartLine.cartId, cartId));
  const existingMap = new Map(
    existingRows.map((r) => [r.variant_id, { qty: r.qty, amount: BigInt(r.amount) }]),
  );

  const lastClientQtyBySku = new Map<string, number>();
  for (const { dealSkuId, qty } of incomingItems) {
    lastClientQtyBySku.set(dealSkuId, qty);
  }

  const mergeRows: {
    dealSkuId: string;
    finalQty: number;
    amount: bigint;
    currency: 'ILS';
    priceMode: 'exclusive';
    vendorId: string | null;
  }[] = [];

  for (const [dealSkuId, clientQty] of lastClientQtyBySku) {
    const sku = skuMap.get(dealSkuId);
    if (!sku) continue;

    const serverQty = existingMap.get(dealSkuId)?.qty ?? 0;
    const mergedQty = Math.max(serverQty, clientQty);
    const stock = remainingStock(sku);
    const finalQty = clampQty(mergedQty, sku.maxPerUser, stock);
    const priceSnapshot = toPriceSnapshot(sku.discountedPrice, sku.vendorId);

    mergeRows.push({
      dealSkuId,
      finalQty,
      amount: priceSnapshot.amount,
      currency: priceSnapshot.currency,
      priceMode: priceSnapshot.priceMode,
      vendorId: priceSnapshot.vendorId,
    });
  }

  if (mergeRows.length > 0) {
    const valueTuples = mergeRows.map(
      (row) => sql`(
        ${cartId},
        ${row.dealSkuId},
        ${row.finalQty},
        ${row.amount},
        ${row.currency},
        ${row.priceMode},
        ${row.vendorId}
      )`,
    );

    await db.execute(sql`
      INSERT INTO cart_line (cart_id, variant_id, qty, amount, currency, price_mode, vendor_id)
      VALUES ${sql.join(valueTuples, sql`, `)}
      ON CONFLICT (cart_id, variant_id) DO UPDATE
      SET qty = EXCLUDED.qty
    `);

    await bumpCartUpdatedAt(db, cartId);
  }

  try {
    for (const row of mergeRows) {
      await db
        .insert(cartItems)
        .values({ userId, dealSkuId: row.dealSkuId, qty: row.finalQty })
        .onConflictDoUpdate({
          target: [cartItems.userId, cartItems.dealSkuId],
          set: { qty: row.finalQty },
        });
    }
  } catch (err) {
    captureCaught(err, {
      scope: 'cart.dual-write',
      severity: 'warning',
      extra: { op: 'mergeCart' },
    });
  }

  if (mergeRows.length > 0) await bumpMhVersion(db, userId);
  return getCart(db, userId);
}

// ---------------------------------------------------------------------------
// getCartItemsForCheckout — raw rows with deal locks (called inside tx)
// ---------------------------------------------------------------------------

type CheckoutSqlRow = {
  cart_item_id: string;
  deal_sku_id: string;
  qty: number;
  stored_price_agorot: string | bigint;
  deal_id: string;
  vendor_id: string;
  vendor_owner_user_id: string;
  deal_title: string;
  current_price: string;
  commission_rate: string;
  max_per_user: number | null;
  stock_remaining: number | null;
  deal_state: string;
  window_end: string | null;
};

/**
 * Fetch cart items joined with FOR UPDATE locked deal rows via variant_id path.
 * Used inside the checkout transaction to validate and decrement stock.
 */
export async function getCartItemsForCheckout(
  db: DrizzleClient,
  userId: string,
): Promise<
  Array<{
    cartItemId: string;
    dealId: string;
    /** SKU selected by the customer. */
    dealSkuId: string;
    qty: number;
    vendorId: string;
    vendorOwnerUserId: string;
    dealTitle: string;
    discountedPrice: string;
    commissionRate: string;
    maxPerUser: number | null;
    stockRemaining: number | null;
    dealState: string;
    windowEnd: Date | null;
  }>
> {
  const cartId = await getOrCreateCartForUser(asCartDb(db), userId);

  const rows = executeRows<CheckoutSqlRow>(
    await db.execute<CheckoutSqlRow>(sql`
    SELECT
      cl.id                    AS cart_item_id,
      cl.variant_id            AS deal_sku_id,
      cl.qty,
      cl.amount                AS stored_price_agorot,
      dsk.deal_id,
      d.vendor_id,
      v.owner_user_id          AS vendor_owner_user_id,
      d.title                  AS deal_title,
      dsk.discounted_price     AS current_price,
      d.commission_rate,
      d.max_per_user,
      d.stock_remaining,
      d.deal_state,
      d.window_end
    FROM cart_line cl
    INNER JOIN deal_skus dsk ON dsk.id = cl.variant_id
    INNER JOIN deals d ON d.id = dsk.deal_id
    INNER JOIN vendors v ON v.id = d.vendor_id
    WHERE cl.cart_id = ${cartId}
    FOR UPDATE
  `),
  );

  return rows.map((row) => ({
    cartItemId: row.cart_item_id,
    dealId: row.deal_id,
    dealSkuId: row.deal_sku_id,
    qty: row.qty,
    vendorId: row.vendor_id,
    vendorOwnerUserId: row.vendor_owner_user_id,
    dealTitle: row.deal_title,
    discountedPrice: storedAgorotToDecimalString(row.stored_price_agorot),
    commissionRate: row.commission_rate,
    maxPerUser: row.max_per_user,
    stockRemaining: row.stock_remaining,
    dealState: row.deal_state,
    windowEnd: row.window_end ? new Date(row.window_end) : null,
  }));
}

// ---------------------------------------------------------------------------
// getCartSkuId — resolve a dealId to its default SKU id for legacy callers
// ---------------------------------------------------------------------------

/**
 * Return the default SKU id for a deal (option_ids_hash = 'default').
 * Used by API routes that still accept dealId in the request body.
 */
export async function getCartDefaultSkuId(
  db: DrizzleClient,
  dealId: string,
): Promise<string | null> {
  const [row] = await db
    .select({ id: dealSkus.id })
    .from(dealSkus)
    .where(and(eq(dealSkus.dealId, dealId), eq(dealSkus.optionIdsHash, 'default')))
    .limit(1);
  return row?.id ?? null;
}