import { sql } from 'drizzle-orm'
import type { Database, Transaction } from '@platform-modules/db'
import type { InventorySchema } from '../schema.js'
import type { CogsResult, Movement } from '../types.js'
import { firstRows, mapMovement } from '../internal/public-helpers.js'

export interface GetCogsInput {
  readonly tenantId: string
  readonly from: Date
  readonly to: Date
  readonly itemId?: string
}

export interface GetMovementsInput {
  readonly tenantId: string
  readonly itemId?: string
  readonly locationId?: string
  readonly from?: Date
  readonly to?: Date
}

export async function getCogs(
  db: Database<InventorySchema> | Transaction<InventorySchema>,
  input: GetCogsInput,
): Promise<CogsResult> {
  const itemFilter = input.itemId ? sql`AND item_id = ${input.itemId}::uuid` : sql``
  const rows = firstRows(await db.execute(sql`
    SELECT item_id, COALESCE(SUM(cogs_amount), 0)::numeric AS total
    FROM stock_movement
    WHERE tenant_id = ${input.tenantId}::uuid
      AND occurred_at BETWEEN ${input.from.toISOString()}::timestamptz AND ${input.to.toISOString()}::timestamptz
      AND (
        kind = 'issue'
        OR kind = 'transfer_out'
        OR (kind = 'count_variance' AND qty_delta < 0)
      )
      ${itemFilter}
    GROUP BY item_id
    ORDER BY item_id ASC
  `))

  const total = rows.reduce((sum, row) => sum + Number(row.total ?? 0), 0)
  return {
    total: String(total),
    byItem: rows.map((row) => ({
      itemId: String(row.item_id),
      total: String(row.total ?? 0),
    })),
  }
}

export async function getMovements(
  db: Database<InventorySchema> | Transaction<InventorySchema>,
  input: GetMovementsInput,
): Promise<Movement[]> {
  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 fromFilter = input.from ? sql`AND occurred_at >= ${input.from.toISOString()}::timestamptz` : sql``
  const toFilter = input.to ? sql`AND occurred_at <= ${input.to.toISOString()}::timestamptz` : sql``

  const rows = firstRows(await db.execute(sql`
    SELECT *
    FROM stock_movement
    WHERE tenant_id = ${input.tenantId}::uuid
      ${itemFilter}
      ${locationFilter}
      ${fromFilter}
      ${toFilter}
    ORDER BY occurred_at ASC, created_at ASC
  `))

  return rows.map(mapMovement)
}