// Catalog export and template download — browser-side, dynamic import of exceljs
import type { QuestionWithCategories } from './assessment-types'

const BMW_BLUE_ARGB = 'FF003D6B'
const WHITE_ARGB    = 'FFFFFFFF'
const SUBHDR_ARGB   = 'FFE6F0F8'

async function triggerDownload(buffer: unknown, filename: string) {
  const blob = new Blob([buffer as ArrayBuffer], {
    type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  })
  const url = URL.createObjectURL(blob)
  const a   = document.createElement('a')
  a.href = url; a.download = filename; a.click()
  URL.revokeObjectURL(url)
}

const HEADERS = [
  'Index',
  'Hauptkategorie',
  'Unterkategorie',
  'Frage',
  'Antwort 1',
  'Antwort 2',
  'Antwort 3',
  'Antwort 4',
  'Aktiv',
]

const COL_WIDTHS = [8, 22, 22, 62, 42, 42, 42, 42, 8]

function applyHeader(row: import('exceljs').Row) {
  row.height = 22
  row.eachCell((cell, col) => {
    cell.fill   = { type: 'pattern', pattern: 'solid', fgColor: { argb: BMW_BLUE_ARGB } }
    cell.font   = { bold: true, color: { argb: WHITE_ARGB }, size: 11, name: 'Calibri' }
    cell.alignment = { vertical: 'middle', horizontal: col === 1 ? 'center' : 'left', wrapText: false }
    cell.border = { bottom: { style: 'medium', color: { argb: 'FF0066B1' } } }
  })
}

export async function exportCatalog(questions: QuestionWithCategories[]) {
  const { default: ExcelJS } = await import('exceljs')
  const wb = new ExcelJS.Workbook()
  wb.creator = 'SupplierPulse'
  wb.created = new Date()

  const ws = wb.addWorksheet('Fragenkatalog')
  ws.columns = HEADERS.map((_, i) => ({ width: COL_WIDTHS[i] }))

  ws.addRow(HEADERS)
  applyHeader(ws.getRow(1))

  const sorted = [...questions].sort((a, b) => a.index_number - b.index_number)

  let lastMainCode = ''
  for (const q of sorted) {
    // Light section separator for new main category
    if (q.main_category.code !== lastMainCode) {
      lastMainCode = q.main_category.code
    }
    const row = ws.addRow([
      q.index_number,
      q.main_category.code,
      q.sub_category.code,
      q.question_text,
      q.answer_text_1,
      q.answer_text_2,
      q.answer_text_3,
      q.answer_text_4,
      q.is_active ? 'Ja' : 'Nein',
    ])
    row.height = 36
    row.eachCell((cell, col) => {
      cell.alignment = { wrapText: true, vertical: 'top', horizontal: col === 1 || col === 9 ? 'center' : 'left' }
      cell.font = { name: 'Calibri', size: 10 }
    })
    // Shade active column
    const activeCell = row.getCell(9)
    activeCell.fill = {
      type: 'pattern', pattern: 'solid',
      fgColor: { argb: q.is_active ? 'FFD1FAE5' : 'FFFEE2E2' },
    }
    activeCell.font = { name: 'Calibri', size: 10, bold: true, color: { argb: q.is_active ? 'FF065F46' : 'FF991B1B' } }
  }

  ws.views = [{ state: 'frozen', ySplit: 1 }]
  ws.autoFilter = { from: { row: 1, column: 1 }, to: { row: 1, column: 9 } }

  await triggerDownload(await wb.xlsx.writeBuffer(), 'question_catalog.xlsx')
}

export async function downloadImportTemplate() {
  const { default: ExcelJS } = await import('exceljs')
  const wb = new ExcelJS.Workbook()
  wb.creator = 'SupplierPulse'
  wb.created = new Date()

  const ws = wb.addWorksheet('Fragenkatalog')
  ws.columns = HEADERS.map((_, i) => ({ width: COL_WIDTHS[i] }))

  ws.addRow(HEADERS)
  applyHeader(ws.getRow(1))

  // Example row with placeholder text
  const ex = ws.addRow([
    1, 'DEV', 'DEV.1',
    'Beispiel-Fragetext hier eingeben',
    'Antworttext für Bewertung 1 (Nicht erfüllt)',
    'Antworttext für Bewertung 2 (Teilweise)',
    'Antworttext für Bewertung 3 (Erfüllt)',
    'Antworttext für Bewertung 4 (Best Practice)',
    'Ja',
  ])
  ex.height = 40
  ex.eachCell((cell, col) => {
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: SUBHDR_ARGB } }
    cell.font = { name: 'Calibri', size: 10, italic: true, color: { argb: 'FF6B7A8D' } }
    cell.alignment = { wrapText: true, vertical: 'top', horizontal: col === 1 || col === 9 ? 'center' : 'left' }
  })

  ws.views = [{ state: 'frozen', ySplit: 1 }]
  await triggerDownload(await wb.xlsx.writeBuffer(), 'import_template.xlsx')
}