import { sql } from 'drizzle-orm'
import type { DbTx } from '../client'

type SqlRow = Record<string, unknown>

function rowsFromResult(result: unknown): SqlRow[] {
  return (Array.isArray(result) ? result : (result as { rows?: SqlRow[] }).rows) ?? []
}

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

export async function postInventoryReceipt(
  tx: DbTx,
  input: InventoryReceiptInput,
): Promise<boolean> {
  const receiptState = await tx.execute(sql`
    WITH owned AS (
      SELECT ii.sku_id AS item_id, sl.id AS location_id
      FROM inventory_item ii
      JOIN stock_locations sl
        ON sl.id = ${input.locationId}::uuid
       AND sl.tenant_id = ${input.tenantId}::uuid
      WHERE ii.sku_id = ${input.itemId}::uuid
        AND ii.vendor_id = ${input.tenantId}
    ), inserted AS (
      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
      )
      SELECT
        ${crypto.randomUUID()}::uuid,
        ${input.tenantId}::uuid,
        owned.item_id,
        owned.location_id,
        'receipt',
        ${input.qty},
        ${input.unitCost},
        null,
        false,
        ${input.holderRef ?? null},
        null,
        ${input.occurredAt.toISOString()}::timestamptz
      FROM owned
      ON CONFLICT (tenant_id, holder_ref, kind) DO NOTHING
      RETURNING id
    )
    SELECT
      EXISTS (SELECT 1 FROM owned) AS owned,
      EXISTS (SELECT 1 FROM inserted) AS inserted
  `)

  const [state] = rowsFromResult(receiptState)
  if (state?.owned !== true) {
    throw new Error(`Inventory item or location is outside tenant ${input.tenantId}`)
  }
  if (state.inserted !== true) return false

  await tx.execute(sql`
    INSERT INTO stock_position (
      tenant_id,
      item_id,
      location_id,
      qty_on_hand,
      avg_unit_cost,
      updated_at
    )
    VALUES (
      ${input.tenantId}::uuid,
      ${input.itemId}::uuid,
      ${input.locationId}::uuid,
      ${input.qty},
      ${input.unitCost},
      now()
    )
    ON CONFLICT (tenant_id, item_id, location_id) DO UPDATE
    SET qty_on_hand = stock_position.qty_on_hand + EXCLUDED.qty_on_hand,
        avg_unit_cost = CASE
          WHEN stock_position.qty_on_hand + EXCLUDED.qty_on_hand <= 0 THEN stock_position.avg_unit_cost
          ELSE (
            (stock_position.qty_on_hand * stock_position.avg_unit_cost)
            + (EXCLUDED.qty_on_hand * EXCLUDED.avg_unit_cost)
          ) / (stock_position.qty_on_hand + EXCLUDED.qty_on_hand)
        END,
        updated_at = now()
  `)

  return true
}

export async function getDefaultInventoryLocationId(
  tx: DbTx,
  tenantId: string,
): Promise<string> {
  const rows = rowsFromResult(await tx.execute(sql`
    SELECT id::text AS id
    FROM stock_locations
    WHERE tenant_id = ${tenantId}::uuid
      AND is_default = true
    LIMIT 1
  `))
  const id = rows[0]?.id
  if (typeof id !== 'string' || id.length === 0) {
    throw new Error(`Default stock location is missing for tenant ${tenantId}`)
  }
  return id
}

export async function reverseInventorySale(
  tx: DbTx,
  input: { tenantId: string; itemId: string; quantity: number },
): Promise<boolean> {
  const rows = rowsFromResult(await tx.execute(sql`
    UPDATE inventory_item
    SET quantity_sold = GREATEST(quantity_sold - ${input.quantity}, 0),
        updated_at = now()
    WHERE sku_id = ${input.itemId}::uuid
      AND vendor_id = ${input.tenantId}
    RETURNING sku_id
  `))
  return rows.length > 0
}