import { Hono } from 'hono'
import { z } from 'zod'
import { getInventoryReport } from '@zync/db/queries'
import { applyHebrewFont, newRtlWorksheet, sanitizeCell, toCsvRow, writeFinancialWorkbook } from '../../lib/financial-export'
import { authMiddleware } from '../../middleware/auth'
import { requirePermission } from '../../middleware/guards'
import type { AppEnv } from '../../types'
import type { InventoryReportRow } from '@zync/types'

export const inventoryReportRoutes = new Hono<AppEnv>()

inventoryReportRoutes.use('*', authMiddleware)

const inventoryReportQuerySchema = z.object({
  asOf: z.string().date().optional(),
  from: z.string().date().optional(),
  to: z.string().date().optional(),
  locationId: z.string().uuid().optional(),
})

function todayIsoDate(): string {
  return new Date().toISOString().slice(0, 10)
}

function startOfMonthIsoDate(): string {
  const now = new Date()
  return new Date(now.getFullYear(), now.getMonth(), 1).toISOString().slice(0, 10)
}

export function buildInventoryCountCsv(rows: InventoryReportRow[]): string {
  const lines = [
    toCsvRow([
      'Product',
      'Stock item ID',
      'Location',
      'Location code',
      'Category',
      'Unit',
      'Quantity on hand',
      'Average unit cost',
      'Valuation amount',
      'COGS in range',
    ]),
  ]

  for (const row of rows) {
    lines.push(
      toCsvRow([
        row.productName,
        row.stockItemId,
        row.locationName ?? '',
        row.locationCode ?? '',
        row.category ?? '',
        row.unit,
        row.quantityOnHand.toFixed(2),
        row.avgUnitCost.toFixed(2),
        row.valuationAmount.toFixed(2),
        row.cogsAmount.toFixed(2),
      ]),
    )
  }

  return lines.join('\r\n') + '\r\n'
}

const INVENTORY_COUNT_HEADERS = [
  'Product',
  'Stock item ID',
  'Location',
  'Location code',
  'Category',
  'Unit',
  'Quantity on hand',
  'Average unit cost',
  'Valuation amount',
  'COGS in range',
] as const

export async function buildInventoryCountWorkbook(rows: InventoryReportRow[]): Promise<Uint8Array> {
  return writeFinancialWorkbook(async (wb) => {
    const sheet = newRtlWorksheet(wb, 'Inventory Count')
    const headerRow = sheet.addRow(INVENTORY_COUNT_HEADERS)
    headerRow.eachCell((cell) => applyHebrewFont(cell))

    for (const row of rows) {
      const nextRow = sheet.addRow([
        sanitizeCell(row.productName),
        sanitizeCell(row.stockItemId),
        sanitizeCell(row.locationName ?? ''),
        sanitizeCell(row.locationCode ?? ''),
        sanitizeCell(row.category ?? ''),
        sanitizeCell(row.unit),
        row.quantityOnHand,
        row.avgUnitCost,
        row.valuationAmount,
        row.cogsAmount,
      ])
      nextRow.eachCell((cell) => applyHebrewFont(cell))
    }
  })
}

inventoryReportRoutes.get('/', requirePermission('reports:read'), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) {
    return c.json({ error: 'Unauthorized' }, 401)
  }

  const parsed = inventoryReportQuerySchema.safeParse(c.req.query())
  if (!parsed.success) {
    return c.json({ error: 'Invalid query params', issues: parsed.error.issues }, 400)
  }

  const asOf = parsed.data.asOf ?? todayIsoDate()
  const from = parsed.data.from ?? startOfMonthIsoDate()
  const to = parsed.data.to ?? asOf

  if (to < from) {
    return c.json({ error: '`to` must be on or after `from`' }, 400)
  }

  const report = await getInventoryReport(c.get('db'), session.tid, {
    asOf,
    from,
    to,
    locationId: parsed.data.locationId ?? null,
  })

  return c.json(report)
})

inventoryReportRoutes.get('/export.csv', requirePermission('reports:export'), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) {
    return c.json({ error: 'Unauthorized' }, 401)
  }

  const parsed = inventoryReportQuerySchema.safeParse(c.req.query())
  if (!parsed.success) {
    return c.json({ error: 'Invalid query params', issues: parsed.error.issues }, 400)
  }

  const asOf = parsed.data.asOf ?? todayIsoDate()
  const from = parsed.data.from ?? startOfMonthIsoDate()
  const to = parsed.data.to ?? asOf

  if (to < from) {
    return c.json({ error: '`to` must be on or after `from`' }, 400)
  }

  const report = await getInventoryReport(c.get('db'), session.tid, {
    asOf,
    from,
    to,
    locationId: parsed.data.locationId ?? null,
  })

  return new Response(buildInventoryCountCsv(report.rows), {
    headers: {
      'Content-Type': 'text/csv; charset=utf-8',
      'Content-Disposition': `attachment; filename="inventory-count-${report.asOf}.csv"`,
    },
  })
})

inventoryReportRoutes.get('/export.xlsx', requirePermission('reports:export'), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) {
    return c.json({ error: 'Unauthorized' }, 401)
  }

  const parsed = inventoryReportQuerySchema.safeParse(c.req.query())
  if (!parsed.success) {
    return c.json({ error: 'Invalid query params', issues: parsed.error.issues }, 400)
  }

  const asOf = parsed.data.asOf ?? todayIsoDate()
  const from = parsed.data.from ?? startOfMonthIsoDate()
  const to = parsed.data.to ?? asOf

  if (to < from) {
    return c.json({ error: '`to` must be on or after `from`' }, 400)
  }

  const report = await getInventoryReport(c.get('db'), session.tid, {
    asOf,
    from,
    to,
    locationId: parsed.data.locationId ?? null,
  })

  return new Response(await buildInventoryCountWorkbook(report.rows), {
    headers: {
      'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      'Content-Disposition': `attachment; filename="inventory-count-${report.asOf}.xlsx"`,
    },
  })
})