/**
 * Mileage logbook routes — mileage-logbook / יומן נסיעות (wave-10 leaf3).
 *
 * GET    /api/mileage                → list entries (filterable by startDate/endDate)
 * POST   /api/mileage                → create entry
 * GET    /api/mileage/summary        → annual summary (query: year=YYYY)
 *
 * Auth: authMiddleware + expenses:write (mutations) / expenses:read (reads)
 */
import { Hono } from 'hono'
import { z } from 'zod'
import type { AppEnv } from '../../types'
import { authMiddleware } from '../../middleware/auth'
import { requirePermission } from '../../middleware/guards'
import {
  listMileageEntries,
  createMileageEntry,
  getMileageSummary,
  createMileageEntrySchema,
} from '@zync/db/queries'

export const mileageRoute = new Hono<AppEnv>()

mileageRoute.use('*', authMiddleware)

const listQuerySchema = z.object({
  startDate: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
  endDate: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
})

const summaryQuerySchema = z.object({
  year: z.coerce.number().int().min(2000).max(2100).default(new Date().getFullYear()),
})

// ── GET /api/mileage/summary (must be before /:id) ───────────────────────────

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

  const parsed = summaryQuerySchema.safeParse({ year: c.req.query('year') })
  if (!parsed.success) {
    return c.json({ error: 'Invalid year parameter' }, 400)
  }

  const db = c.get('db')
  const summary = await getMileageSummary(db, session.tid, session.sub, parsed.data.year)

  return c.json({ summary })
})

// ── GET /api/mileage/report/annual/xlsx ──────────────────────────────────────

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

  const parsed = summaryQuerySchema.safeParse({ year: c.req.query('year') })
  if (!parsed.success) {
    return c.json({ error: 'Invalid year parameter' }, 400)
  }

  const db = c.get('db')
  const year = parsed.data.year
  const yearStart = `${year}-01-01`
  const yearEnd = `${year}-12-31`
  const entries = await listMileageEntries(db, session.tid, session.sub, yearStart, yearEnd)
  const summary = await getMileageSummary(db, session.tid, session.sub, year)

  const ExcelJS = (await import('exceljs')).default
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet(`Mileage ${year}`, { views: [{ rightToLeft: true }] })
  ws.columns = [
    { header: 'Date', key: 'date', width: 14 },
    { header: 'From', key: 'fromLocation', width: 24 },
    { header: 'To', key: 'toLocation', width: 24 },
    { header: 'Distance (km)', key: 'distanceKm', width: 14 },
    { header: 'Purpose', key: 'purpose', width: 24 },
    { header: 'Reimbursement (ILS)', key: 'reimbursement', width: 18 },
  ]
  for (const entry of entries) {
    ws.addRow({
      date: entry.date.toISOString().slice(0, 10),
      fromLocation: entry.fromLocation,
      toLocation: entry.toLocation,
      distanceKm: parseFloat(String(entry.distanceKm)),
      purpose: entry.purpose,
      reimbursement: entry.reimbursementAmount ? parseFloat(String(entry.reimbursementAmount)) : '',
    })
  }
  ws.addRow({})
  ws.addRow({ purpose: 'Total km', distanceKm: summary.totalKm })
  ws.addRow({ purpose: 'Business km', distanceKm: summary.businessKm })
  ws.addRow({ purpose: 'Reimbursement total', reimbursement: summary.reimbursementTotal })

  const buffer = await wb.xlsx.writeBuffer()
  return new Response(buffer, {
    status: 200,
    headers: {
      'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      'Content-Disposition': `attachment; filename="mileage-report-${year}.xlsx"`,
    },
  })
})

// ── GET /api/mileage ──────────────────────────────────────────────────────────

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

  const parsed = listQuerySchema.safeParse({
    startDate: c.req.query('startDate'),
    endDate: c.req.query('endDate'),
  })
  if (!parsed.success) {
    return c.json({ error: 'Invalid query parameters', details: parsed.error.flatten() }, 400)
  }

  const db = c.get('db')
  const entries = await listMileageEntries(
    db,
    session.tid,
    session.sub,
    parsed.data.startDate,
    parsed.data.endDate,
  )

  return c.json({ entries })
})

// ── POST /api/mileage ─────────────────────────────────────────────────────────

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

  const body = await c.req.json().catch(() => null)
  if (!body) return c.json({ error: 'Invalid JSON body' }, 400)

  const parsed = createMileageEntrySchema.safeParse(body)
  if (!parsed.success) {
    return c.json({ error: 'Validation failed', details: parsed.error.flatten() }, 400)
  }

  const db = c.get('db')
  try {
    const entry = await createMileageEntry(db, session.tid, session.sub, parsed.data)
    return c.json({ entry }, 201)
  } catch (err) {
    const msg = err instanceof Error ? err.message : 'Unknown error'
    return c.json({ error: msg }, 500)
  }
})