/**
 * Time Reports routes — time-reports spec.
 *
 * Mounted at /api/time/reports (declared in manifest route_mounts).
 * NOT touching any shared index.ts barrel — manifest declares these.
 *
 * Routes:
 *   GET  /           → aggregated JSON  { rows, totals }
 *   GET  /export     → CSV download
 *
 * Access:
 *   - Requires `time:read` (authMiddleware + requirePermission).
 *   - OWNER / ADMIN: see all users.
 *   - MEMBER: locked to own userId (filter.userId forced to session.sub).
 *   - CONTRACTOR / CLIENT_PORTAL: 403.
 *
 * Zod schemas exported via this file so they can be re-exported from
 * @zync/db/queries barrel by the integrating barrel update (manifest declares
 * barrel_exports for queries/index.ts).
 */
import { Hono } from 'hono'
import { z } from 'zod'
import type { AppEnv } from '../../types'
import {
  isContractorRole,
  isOwnerOrAdminRole,
} from '../../lib/system-roles'
import { authMiddleware } from '../../middleware/auth'
import { requirePermission } from '../../middleware/guards'
import {
  getTimeReportByPerson,
  getTimeReportByProject,
  getTimeReportByTask,
  getTimeReportPeopleOptions,
  getTimeReportTotals,
  secondsToHours,
  type TimeReportFilter,
} from '@zync/db/queries'
import { writeFinancialWorkbook, sanitizeCell } from '../../lib/financial-export'

// ── Zod schemas ───────────────────────────────────────────────────────────────

const dateRangeRefinement = (
  value: { from?: string; to?: string },
  ctx: z.RefinementCtx,
) => {
  if (value.from && value.to && value.to < value.from) {
    ctx.addIssue({
      code: z.ZodIssueCode.custom,
      message: '`to` must be on or after `from`',
      path: ['to'],
    })
  }
}

const TimeReportQuerySchemaBase = z.object({
  groupBy: z.enum(['person', 'project', 'task']).default('person'),
  from: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
  to: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
  userId: z.string().uuid().optional(),
  contractorId: z.string().uuid().optional(),
  projectId: z.string().uuid().optional(),
  billable: z
    .enum(['true', 'false'])
    .transform((v) => v === 'true')
    .optional(),
})

export const TimeReportQuerySchema = TimeReportQuerySchemaBase.superRefine(dateRangeRefinement)

export const TimeReportExportQuerySchema = TimeReportQuerySchemaBase.extend({
  format: z.enum(['csv', 'xlsx']).default('csv'),
}).superRefine(dateRangeRefinement)

export type TimeReportQuery = z.infer<typeof TimeReportQuerySchema>

// ── CSV helpers (re-use project convention from audit-log.ts) ────────────────

/**
 * CSV-escape a single field value (RFC 4180) + neutralize spreadsheet formula
 * injection: a cell beginning with = + - @ TAB or CR is evaluated as a formula
 * by Excel/Sheets, so prefix those with a single quote before quoting.
 */
function csvField(value: string | number | null | undefined): string {
  let s = String(value ?? '')
  if (/^[=+\-@\t\r]/.test(s)) {
    s = `'${s}`
  }
  if (s.includes('"') || s.includes(',') || s.includes('\n') || s.includes('\r')) {
    return `"${s.replace(/"/g, '""')}"`
  }
  return s
}

function csvRow(fields: (string | number | null | undefined)[]): string {
  return fields.map(csvField).join(',') + '\r\n'
}

// ── Access-control helper ─────────────────────────────────────────────────────

export function checkReportAccess(
  role: string,
  sub: string,
  query: TimeReportQuery,
  permissions: string[] | undefined,
): { allowed: boolean; forceUserId?: string } {
  if (role === 'client_portal') {
    return { allowed: false }
  }
  if (isContractorRole(role)) {
    return { allowed: false }
  }
  if (isOwnerOrAdminRole(role)) {
    if (permissions?.includes('reports:read')) return { allowed: true }
    return { allowed: true, forceUserId: sub }
  }
  // member: lock to own data
  return { allowed: true, forceUserId: sub }
}

function canSeeAllReportPeople(role: string, permissions: string[] | undefined): boolean {
  return isOwnerOrAdminRole(role) && permissions?.includes('reports:read') === true
}

// ── Router ────────────────────────────────────────────────────────────────────

export const timeReportsRoutes = new Hono<AppEnv>()

timeReportsRoutes.use('*', authMiddleware)
timeReportsRoutes.use('*', requirePermission('time:read'))

// GET /api/time/reports/people-options
timeReportsRoutes.get('/people-options', async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) {
    return c.json({ error: 'Unauthorized' }, 401)
  }
  if (session.role === 'client_portal' || isContractorRole(session.role)) {
    return c.json({ error: 'Forbidden' }, 403)
  }

  const people = await getTimeReportPeopleOptions(c.get('db'), session.tid, {
    includeAllPeople: canSeeAllReportPeople(session.role, session.permissions),
    userId: session.sub,
  })

  return c.json({ people }, 200)
})

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

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

  const query = parsed.data
  const access = checkReportAccess(session.role, session.sub, query, session.permissions)
  if (!access.allowed) {
    return c.json({ error: 'Forbidden' }, 403)
  }
  const tenantId = session.tid

  const filter: TimeReportFilter = {
    tenantId,
    from: query.from,
    to: query.to,
    userId: access.forceUserId ?? query.userId,
    contractorId: access.forceUserId ? undefined : query.contractorId,
    projectId: query.projectId,
    billable: query.billable,
  }

  const db = c.get('db')

  const [rows, totals] = await Promise.all([
    query.groupBy === 'person'
      ? getTimeReportByPerson(db, filter)
      : query.groupBy === 'project'
      ? getTimeReportByProject(db, filter)
      : getTimeReportByTask(db, filter),
    getTimeReportTotals(db, filter),
  ])

  return c.json({ rows, totals })
})

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

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

  const query = parsed.data
  const access = checkReportAccess(session.role, session.sub, query, session.permissions)
  if (!access.allowed) {
    return c.json({ error: 'Forbidden' }, 403)
  }
  const tenantId = session.tid

  const filter: TimeReportFilter = {
    tenantId,
    from: query.from,
    to: query.to,
    userId: access.forceUserId ?? query.userId,
    contractorId: access.forceUserId ? undefined : query.contractorId,
    projectId: query.projectId,
    billable: query.billable,
  }

  const db = c.get('db')

  // Sanitize for Content-Disposition header (no CR/LF/quote injection)
  const safe = (s: string) => s.replace(/[^A-Za-z0-9._-]/g, '')
  const fromLabel = safe(query.from ?? 'all')
  const toLabel = safe(query.to ?? 'now')
  const groupLabel = safe(query.groupBy)
  const filename = `time-report-${groupLabel}-${fromLabel}-${toLabel}.csv`

  let csvContent = ''

  if (query.groupBy === 'person') {
    const headers = ['Person', 'Role', 'Total Hours', 'Billable Hours', 'Entries']
    const rows = await getTimeReportByPerson(db, filter)

    if (query.format === 'xlsx') {
      const bytes = await writeFinancialWorkbook((wb) => {
        const ws = wb.addWorksheet('Time Reports')
        ws.addRow(headers)
        for (const row of rows) {
          ws.addRow([
            sanitizeCell(row.userName ?? row.contractorId ?? 'Unknown'),
            sanitizeCell(row.roleLabel),
            secondsToHours(row.totalSeconds),
            secondsToHours(row.billableSeconds),
            row.entryCount,
          ])
        }
      })

      return new Response(bytes, {
        headers: {
          'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
          'Content-Disposition': `attachment; filename="${filename.replace(/\.csv$/, '.xlsx')}"`,
        },
      })
    }

    csvContent = csvRow(headers)
    for (const row of rows) {
      csvContent += csvRow([
        row.userName ?? row.contractorId ?? 'Unknown',
        row.roleLabel,
        secondsToHours(row.totalSeconds),
        secondsToHours(row.billableSeconds),
        row.entryCount,
      ])
    }
  } else if (query.groupBy === 'project') {
    const headers = ['Project', 'Customer', 'Total Hours', 'Members']
    const rows = await getTimeReportByProject(db, filter)

    if (query.format === 'xlsx') {
      const bytes = await writeFinancialWorkbook((wb) => {
        const ws = wb.addWorksheet('Time Reports')
        ws.addRow(headers)
        for (const row of rows) {
          ws.addRow([
            sanitizeCell(row.projectName),
            sanitizeCell(row.customerName ?? ''),
            secondsToHours(row.totalSeconds),
            sanitizeCell(row.memberNames.join(', ')),
          ])
        }
      })

      return new Response(bytes, {
        headers: {
          'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
          'Content-Disposition': `attachment; filename="${filename.replace(/\.csv$/, '.xlsx')}"`,
        },
      })
    }

    csvContent = csvRow(headers)
    for (const row of rows) {
      csvContent += csvRow([
        row.projectName,
        row.customerName ?? '',
        secondsToHours(row.totalSeconds),
        row.memberNames.join(', '),
      ])
    }
  } else {
    const headers = ['Task', 'Project', 'Person', 'Total Hours']
    const rows = await getTimeReportByTask(db, filter)

    if (query.format === 'xlsx') {
      const bytes = await writeFinancialWorkbook((wb) => {
        const ws = wb.addWorksheet('Time Reports')
        ws.addRow(headers)
        for (const row of rows) {
          ws.addRow([
            sanitizeCell(row.taskTitle ?? '[No task]'),
            sanitizeCell(row.projectName),
            sanitizeCell(row.personName),
            secondsToHours(row.totalSeconds),
          ])
        }
      })

      return new Response(bytes, {
        headers: {
          'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
          'Content-Disposition': `attachment; filename="${filename.replace(/\.csv$/, '.xlsx')}"`,
        },
      })
    }

    csvContent = csvRow(headers)
    for (const row of rows) {
      csvContent += csvRow([
        row.taskTitle ?? '[No task]',
        row.projectName,
        row.personName,
        secondsToHours(row.totalSeconds),
      ])
    }
  }

  return new Response(csvContent, {
    headers: {
      'Content-Type': 'text/csv; charset=utf-8',
      'Content-Disposition': `attachment; filename="${filename}"`,
    },
  })
})