import { sql } from 'drizzle-orm'
import type { Database, PostgresTransaction } from '@platform-modules/db'
import type { InventorySchema } from '../schema.js'
import type { OnHand, Valuation } from '../types.js'
import { firstRows } from '../internal/public-helpers.js'
import { blendReceipt, consumeAvg, type AvgValuation } from '../valuation/weighted-average.js'

export interface GetOnHandInput {
  readonly tenantId: string
  readonly itemId: string
  readonly locationId?: string
}

export interface GetValuationInput {
  readonly tenantId: string
  readonly itemId?: string
  readonly asOf: Date
  readonly locationId?: string
}

export async function getOnHand(
  db: Database<InventorySchema> | PostgresTransaction<InventorySchema>,
  input: GetOnHandInput,
): Promise<OnHand> {
  if (input.locationId) {
    const result = await db.execute(sql`
      SELECT COALESCE(SUM(qty_delta), 0)::numeric AS qty
      FROM stock_movement
      WHERE tenant_id = ${input.tenantId}::uuid
        AND item_id = ${input.itemId}::uuid
        AND location_id = ${input.locationId}::uuid
    `)
    const row = firstRows(result)[0]
    return {
      itemId: input.itemId,
      locationId: input.locationId,
      qty: String(row?.qty ?? 0),
    }
  }

  const byLocation = firstRows(await db.execute(sql`
    SELECT location_id, COALESCE(SUM(qty_delta), 0)::numeric AS qty
    FROM stock_movement
    WHERE tenant_id = ${input.tenantId}::uuid
      AND item_id = ${input.itemId}::uuid
    GROUP BY location_id
    ORDER BY location_id ASC
  `))

  const total = byLocation.reduce((sum, row) => sum + Number(row.qty ?? 0), 0)
  return {
    itemId: input.itemId,
    qty: String(total),
    byLocation: byLocation.map((row) => ({
      locationId: String(row.location_id),
      qty: String(row.qty ?? 0),
    })),
  }
}

export async function getValuation(
  db: Database<InventorySchema> | PostgresTransaction<InventorySchema>,
  input: GetValuationInput,
): Promise<Valuation> {
  const itemFilter = input.itemId
    ? sql`AND item_id = ${input.itemId}::uuid`
    : sql``
  const locationFilter = input.locationId
    ? sql`AND location_id = ${input.locationId}::uuid`
    : sql``

  const weightedMovementRows = firstRows(await db.execute(sql`
    SELECT item_id, location_id, qty_delta, unit_cost
    FROM stock_movement
    WHERE tenant_id = ${input.tenantId}::uuid
      ${itemFilter}
      ${locationFilter}
      AND occurred_at <= ${input.asOf.toISOString()}::timestamptz
      AND item_id IN (
        SELECT id FROM stock_item WHERE tenant_id = ${input.tenantId}::uuid AND method = 'weighted_average'
      )
    ORDER BY item_id ASC, location_id ASC, occurred_at ASC, created_at ASC
  `))

  const perLocation = new Map<string, AvgValuation>()
  for (const row of weightedMovementRows) {
    const key = `${String(row.item_id)}:${String(row.location_id)}`
    const state = perLocation.get(key) ?? { quantity: 0, averageCost: 0 }
    const qty = Number(row.qty_delta ?? 0)
    const rowUnitCost = row.unit_cost === null || typeof row.unit_cost === 'undefined'
      ? state.averageCost
      : Number(row.unit_cost)

    let next: AvgValuation
    if (qty > 0) {
      next = state.quantity >= 0
        ? blendReceipt(state, { quantity: qty, unitCost: rowUnitCost })
        : {
            quantity: state.quantity + qty,
            averageCost: state.quantity + qty > 0 ? rowUnitCost : state.averageCost,
          }
    } else if (qty < 0) {
      const absQty = -qty
      next = state.quantity >= absQty && state.quantity >= 0
        ? consumeAvg(state, absQty)
        : { quantity: state.quantity + qty, averageCost: rowUnitCost }
    } else {
      next = state
    }

    perLocation.set(key, next)
  }

  const perItem = new Map<string, { qty: number; value: number }>()
  for (const [key, state] of perLocation) {
    const itemId = key.split(':')[0] as string
    const agg = perItem.get(itemId) ?? { qty: 0, value: 0 }
    agg.qty += state.quantity
    agg.value += state.quantity * state.averageCost
    perItem.set(itemId, agg)
  }

  const weightedRows = [...perItem.entries()].map(([itemId, agg]) => ({
    item_id: itemId,
    qty: agg.qty,
    unit_cost: agg.qty === 0 ? 0 : agg.value / agg.qty,
    value: agg.value,
  }))

  const fifoRows = firstRows(await db.execute(sql`
    SELECT item_id,
           COALESCE(SUM(qty_remaining), 0)::numeric AS qty,
           CASE WHEN COALESCE(SUM(qty_remaining), 0) = 0 THEN 0
             ELSE COALESCE(SUM(qty_remaining * unit_cost), 0) / NULLIF(SUM(qty_remaining), 0)
           END::numeric AS unit_cost,
           COALESCE(SUM(qty_remaining * unit_cost), 0)::numeric AS value
    FROM stock_cost_layer
    WHERE tenant_id = ${input.tenantId}::uuid
      ${itemFilter}
      ${locationFilter}
      AND created_at <= ${input.asOf.toISOString()}::timestamptz
      AND item_id IN (
        SELECT id FROM stock_item WHERE tenant_id = ${input.tenantId}::uuid AND method = 'fifo'
      )
    GROUP BY item_id
  `))

  const rows = [...weightedRows, ...fifoRows]
  const totalQty = rows.reduce((sum, row) => sum + Number(row.qty ?? 0), 0)
  const totalValue = rows.reduce((sum, row) => sum + Number(row.value ?? 0), 0)
  const unitCost = totalQty === 0 ? 0 : totalValue / totalQty

  return {
    itemId: input.itemId ?? '*',
    locationId: input.locationId,
    qty: String(totalQty),
    unitCost: String(unitCost),
    value: String(totalValue),
  }
}