import { NextRequest, NextResponse } from 'next/server'
import ExcelJS from 'exceljs'
import { TEMPLATE_HEADERS } from '@/lib/qaf-parser'
import { createClient } from '@/lib/supabase/server'

const VERSION_FILE_NAMES: Record<string, string> = {
  zur_vergabe: 'Zur_Vergabe',
  nach_vergabe: 'Nach_Vergabe',
  aktuell: 'Aktuell',
}

// Approximate column widths based on content
const COL_WIDTHS = [
  28, // Positionsnummer Fertigungsschritt
  22, // Teilebenennung
  30, // Prozessbezeichnung
  28, // Bezeichnung Anlage/Maschine/Typ
  16, // Standort
  22, // Beschaffungswährung BW
  14, // Zykluszeit [s]
  14, // Teile pro Zyklus
  22, // Anzahl direkte Mitarbeiter
  38, // Kalkulatorisch angesetzte direkte Lohnkosten [BW/h]
  40, // Kalkulatorisch angesetzte Lohn-zuschlagssätze SGK [%]
  24, // Maschinenstundensatz MSS [BW/h]
  22, // Rüstkosten pro Stück [BW]
  24, // Fertigungseinzelkosten FEK [BW]
  44, // Restfertigungsgemeinkosten (RFGK) Stundensatz [BW/h]
  24, // Fertigungskosten FK [BW]
  20, // Angebotswährung AW
  18, // Wechselkurs [AW/BW]
  22, // Anzahl pro Angebotsteil
  22, // Fertigungskosten FK [AW]
  30, // Ausschuss pro Prozessschritt [%]
  28, // Ausschusskosten Fertigung [AW]
]

export async function GET(req: NextRequest) {
  // Auth gate — template generation is tenant-scoped, not public
  const supabase = await createClient()
  const { data: claimsData } = await supabase.auth.getClaims()
  if (!claimsData?.claims) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
  }

  const version = req.nextUrl.searchParams.get('version') ?? 'zur_vergabe'
  const versionLabel = VERSION_FILE_NAMES[version] ?? 'QAF'

  const wb = new ExcelJS.Workbook()
  wb.creator = 'SupplierDev'
  wb.created = new Date()

  const ws = wb.addWorksheet('Fertigungskosten')

  // Set column widths
  ws.columns = TEMPLATE_HEADERS.map((_, i) => ({ width: COL_WIDTHS[i] ?? 18 }))

  // Add header row
  const headerRow = ws.addRow([...TEMPLATE_HEADERS])
  headerRow.height = 52

  // Style each header cell: brand primary background, white bold text, center+wrap
  headerRow.eachCell((cell) => {
    cell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FF0066B1' },
    }
    cell.font = {
      bold: true,
      color: { argb: 'FFFFFFFF' },
      size: 10,
      name: 'Calibri',
    }
    cell.alignment = {
      vertical: 'middle',
      horizontal: 'center',
      wrapText: true,
    }
    cell.border = {
      bottom: { style: 'medium', color: { argb: 'FF003D6B' } },
      right: { style: 'thin', color: { argb: 'FF004F8C' } },
    }
  })

  // Freeze the header row
  ws.views = [{ state: 'frozen', ySplit: 1, xSplit: 0, activeCell: 'A2' }]

  // Auto-filter on header row
  ws.autoFilter = {
    from: { row: 1, column: 1 },
    to: { row: 1, column: TEMPLATE_HEADERS.length },
  }

  // Add 20 empty data rows with alternating row shading
  for (let i = 0; i < 20; i++) {
    const dataRow = ws.addRow(new Array(TEMPLATE_HEADERS.length).fill(''))
    dataRow.height = 18
    if (i % 2 === 1) {
      dataRow.eachCell({ includeEmpty: true }, (cell) => {
        cell.fill = {
          type: 'pattern',
          pattern: 'solid',
          fgColor: { argb: 'FFF7F9FB' },
        }
      })
    }
  }

  const buf = await wb.xlsx.writeBuffer()

  return new NextResponse(buf as ArrayBuffer, {
    headers: {
      'Content-Type':
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      'Content-Disposition': `attachment; filename="Template_${versionLabel}.xlsx"`,
    },
  })
}