import { sql } from 'drizzle-orm'
import type { Database, PostgresTransaction } from '@platform-modules/db'
import type { InventorySchema } from './schema.js'
import type { MovementResult } from './types.js'
import { postMovement } from './internal/post-movement.js'
import {
  firstRows,
  mapMovement,
  withInventoryTransaction,
} from './internal/public-helpers.js'

export interface PostReceiptInput {
  readonly tenantId: string
  readonly itemId: string
  readonly locationId: string
  readonly qty: number
  readonly unitCost: number
  readonly holderRef?: string
  readonly occurredAt: Date
}

function trueUpHolderRef(
  originalHolderRef: string,
  receiptMovementId: string,
  qty: number,
): string {
  return `${originalHolderRef}:trueup:${receiptMovementId}:q:${qty}`
}

function settledQtyFromHolderRef(holderRef: string | null): number {
  if (!holderRef) return 0
  const match = /:q:(-?\d+(?:\.\d+)?)$/.exec(holderRef)
  return match ? Number(match[1]) : 0
}

export async function postReceipt(
  db: Database<InventorySchema> | PostgresTransaction<InventorySchema>,
  input: PostReceiptInput,
): Promise<MovementResult> {
  return withInventoryTransaction(db, async (tx) => {
    const receipt = await postMovement(tx, {
      tenantId: input.tenantId,
      itemId: input.itemId,
      locationId: input.locationId,
      kind: 'receipt',
      qtyDelta: input.qty,
      unitCost: input.unitCost,
      holderRef: input.holderRef,
      occurredAt: input.occurredAt,
    })

    const pendingRows = firstRows(await tx.execute(sql`
      SELECT *
      FROM stock_movement
      WHERE tenant_id = ${input.tenantId}::uuid
        AND item_id = ${input.itemId}::uuid
        AND location_id = ${input.locationId}::uuid
        AND pending_cost = true
      ORDER BY occurred_at ASC, created_at ASC
    `))

    let remainingQty = input.qty
    for (const row of pendingRows) {
      if (remainingQty <= 0) {
        break
      }

      const pendingMovement = mapMovement(row)
      if (!pendingMovement.holderRef) {
        continue
      }

      const existingTrueUps = await tx.execute(sql`
        SELECT holder_ref
        FROM stock_movement
        WHERE tenant_id = ${input.tenantId}::uuid
          AND holder_ref LIKE ${`${pendingMovement.holderRef}:trueup:%`}
      `)
      const alreadySettledQty = firstRows(existingTrueUps)
        .reduce((sum, trueUpRow) => sum + settledQtyFromHolderRef(String(trueUpRow.holder_ref ?? '')), 0)

      const pendingQty = Math.abs(Number(pendingMovement.qtyDelta))
      const unresolvedQty = Math.max(0, pendingQty - alreadySettledQty)
      if (unresolvedQty === 0) {
        continue
      }

      const settledQty = Math.min(remainingQty, unresolvedQty)
      const provisionalUnitCost = Number(pendingMovement.unitCost ?? 0)
      const costDelta = settledQty * (input.unitCost - provisionalUnitCost)
      const holderRef = trueUpHolderRef(pendingMovement.holderRef, receipt.movement.id, settledQty)

      const existing = await tx.execute(sql`
        SELECT id
        FROM stock_movement
        WHERE tenant_id = ${input.tenantId}::uuid
          AND holder_ref = ${holderRef}
        LIMIT 1
      `)
      if (firstRows(existing).length === 0) {
        await tx.execute(sql`
          INSERT INTO stock_movement (
            id,
            tenant_id,
            item_id,
            location_id,
            kind,
            qty_delta,
            unit_cost,
            cogs_amount,
            pending_cost,
            holder_ref,
            transfer_group,
            occurred_at
          )
          VALUES (
            ${crypto.randomUUID()}::uuid,
            ${input.tenantId}::uuid,
            ${input.itemId}::uuid,
            ${input.locationId}::uuid,
            'adjust',
            0,
            ${input.unitCost},
            ${costDelta},
            false,
            ${holderRef},
            null,
            ${input.occurredAt.toISOString()}::timestamptz
          )
        `)
      }

      remainingQty -= settledQty
    }

    return { movement: receipt.movement }
  })
}