import { sql } from 'drizzle-orm'
import type { Db } from '../client'
import type {
  InventoryReportResponse,
  InventoryReportRow,
  InventoryReportSummary,
} from '@zync/types'

interface InventoryReportQueryRow {
  product_id?: unknown
  product_name?: unknown
  stock_item_id?: unknown
  location_id?: unknown
  location_name?: unknown
  location_code?: unknown
  category?: unknown
  unit?: unknown
  quantity_on_hand?: unknown
  avg_unit_cost?: unknown
  valuation_amount?: unknown
  cogs_amount?: unknown
}

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

function toStringOrNull(value: unknown): string | null {
  return typeof value === 'string' && value.length > 0 ? value : null
}

function toNumberOrNull(value: unknown): number | null {
  if (typeof value === 'number') return Number.isFinite(value) ? value : null
  if (typeof value === 'string' && value.trim() !== '') {
    const parsed = Number(value)
    return Number.isFinite(parsed) ? parsed : null
  }
  return null
}

function toIsoDate(value: unknown): string {
  if (value instanceof Date) return value.toISOString().slice(0, 10)
  if (typeof value === 'string') return value.slice(0, 10)
  throw new Error('Invalid inventory report date')
}

function serializeInventoryReportRow(row: InventoryReportQueryRow): InventoryReportRow {
  const quantityOnHand = toNumberOrNull(row.quantity_on_hand) ?? 0
  const avgUnitCost = toNumberOrNull(row.avg_unit_cost) ?? 0
  const valuationAmount = toNumberOrNull(row.valuation_amount) ?? quantityOnHand * avgUnitCost
  const cogsAmount = toNumberOrNull(row.cogs_amount) ?? 0

  return {
    productId: String(row.product_id),
    productName: String(row.product_name),
    stockItemId: String(row.stock_item_id),
    locationId: String(row.location_id),
    locationName: toStringOrNull(row.location_name),
    locationCode: toStringOrNull(row.location_code),
    category: toStringOrNull(row.category),
    unit: String(row.unit ?? 'unit'),
    quantityOnHand,
    avgUnitCost,
    valuationAmount,
    cogsAmount,
  }
}

export function summarizeInventoryReport(rows: InventoryReportRow[]): InventoryReportSummary {
  return rows.reduce<InventoryReportSummary>(
    (summary, row) => ({
      valuationAmount: summary.valuationAmount + row.valuationAmount,
      cogsAmount: summary.cogsAmount + row.cogsAmount,
      itemCount: summary.itemCount + 1,
      quantityOnHand: summary.quantityOnHand + row.quantityOnHand,
    }),
    {
      valuationAmount: 0,
      cogsAmount: 0,
      itemCount: 0,
      quantityOnHand: 0,
    },
  )
}

export async function getInventoryReport(
  db: Db,
  tenantId: string,
  params: { asOf: string; from: string; to: string; locationId?: string | null },
): Promise<InventoryReportResponse> {
  const asOfDate = toIsoDate(params.asOf)
  const fromDate = toIsoDate(params.from)
  const toDate = toIsoDate(params.to)

  const rows = rowsFromResult<InventoryReportQueryRow>(
    await db.execute(sql`
      with cogs_by_row as (
        select
          sm.item_id,
          sm.location_id,
          sum(coalesce(sm.cogs_amount, 0))::numeric as cogs_amount
        from stock_movement sm
        where sm.tenant_id = ${tenantId}::uuid
          and sm.occurred_at >= ${fromDate}::date
          and sm.occurred_at < (${toDate}::date + interval '1 day')
        group by sm.item_id, sm.location_id
      )
      select
        p.id as product_id,
        p.name as product_name,
        p.stock_item_id,
        sp.location_id,
        sl.name as location_name,
        sl.code as location_code,
        p.category,
        p.unit,
        sp.qty_on_hand as quantity_on_hand,
        sp.avg_unit_cost,
        (sp.qty_on_hand * sp.avg_unit_cost)::numeric as valuation_amount,
        coalesce(cogs.cogs_amount, 0)::numeric as cogs_amount
      from stock_position sp
      inner join products p
        on p.tenant_id = sp.tenant_id
       and p.stock_item_id = sp.item_id
       and p.is_tracked = true
      left join stock_locations sl
        on sl.tenant_id = sp.tenant_id
       and sl.id = sp.location_id
      left join cogs_by_row cogs
        on cogs.item_id = sp.item_id
       and cogs.location_id = sp.location_id
      where sp.tenant_id = ${tenantId}::uuid
        and (${params.locationId ?? null}::uuid is null or sp.location_id = ${params.locationId ?? null}::uuid)
        and sp.qty_on_hand <> 0
      order by p.name asc, sl.name asc nulls last
    `),
  )

  const serializedRows = rows.map(serializeInventoryReportRow)

  return {
    asOf: asOfDate,
    from: fromDate,
    to: toDate,
    summary: summarizeInventoryReport(serializedRows),
    rows: serializedRows,
  }
}