// Excel / Markdown export utilities — runs in browser only (dynamic import of exceljs)
import type { Assessment, QuestionWithCategories, ResponseDraft, AssessmentLang } from './assessment-types'
import type { SubCatStat } from './assessment-analytics'
import { getAssessmentText } from './assessment-lang'

// ── Localised UI labels ───────────────────────────────────────────────────────

export const exportLabels = {
  de: {
    mainCat:      'Hauptkategorie',
    subCat:       'Unterkategorie',
    question:     'Frage',
    relevant:     'Relevant',
    flagged:      'Markiert',
    rating:       'Bewertung',
    ratingText:   'Bewertungstext',
    comment:      'Kommentar',
    answered:     'Beantwortet',
    total:        'Relevante Fragen',
    progress:     'Fortschritt %',
    avgRating:    'Ø Bewertung',
    yes:          'Ja',
    no:           'Nein',
    notRated:     '— (nicht bewertet)',
    scale:        '1 = Nicht erfüllt | 2 = Teilweise | 3 = Erfüllt | 4 = Best Practice',
    ratingLabels: { 1: 'Nicht erfüllt', 2: 'Teilweise', 3: 'Erfüllt', 4: 'Best Practice' } as Record<number, string>,
    aiInstruction: 'Erstelle eine Management Summary pro Unterkategorie. Berücksichtige die Bewertung UND die Kommentare. Hebe kritische Bereiche (Bewertung 1–2) hervor und formuliere konkrete Handlungsempfehlungen.',
    summaryHeader: 'Gesamtübersicht',
    aiHeader:      'Anweisung für KI',
    critical:      'Kritische Punkte (Bewertung 1–2)',
    avgLabel:      'Durchschnitt',
    ratedLabel:    'Bewertet',
    questions:     'Fragen',
  },
  en: {
    mainCat:      'Main Category',
    subCat:       'Sub-Category',
    question:     'Question',
    relevant:     'Relevant',
    flagged:      'Flagged',
    rating:       'Rating',
    ratingText:   'Rating Text',
    comment:      'Comment',
    answered:     'Answered',
    total:        'Relevant Questions',
    progress:     'Progress %',
    avgRating:    'Avg Rating',
    yes:          'Yes',
    no:           'No',
    notRated:     '— (not rated)',
    scale:        '1 = Not met | 2 = Partially | 3 = Met | 4 = Best Practice',
    ratingLabels: { 1: 'Not met', 2: 'Partially', 3: 'Met', 4: 'Best Practice' } as Record<number, string>,
    aiInstruction: 'Create a management summary per sub-category. Consider both the rating AND the comments. Highlight critical areas (rating 1–2) and formulate concrete recommendations.',
    summaryHeader: 'Overall Summary',
    aiHeader:      'AI Instruction',
    critical:      'Critical points (rating 1–2)',
    avgLabel:      'Average',
    ratedLabel:    'Rated',
    questions:     'Questions',
  },
  zh: {
    mainCat:      '主类别',
    subCat:       '子类别',
    question:     '问题',
    relevant:     '相关',
    flagged:      '已标记',
    rating:       '评分',
    ratingText:   '评分说明',
    comment:      '评语',
    answered:     '已回答',
    total:        '相关问题',
    progress:     '进度 %',
    avgRating:    '平均分',
    yes:          '是',
    no:           '否',
    notRated:     '— (未评分)',
    scale:        '1 = 不符合 | 2 = 部分符合 | 3 = 符合 | 4 = 最佳实践',
    ratingLabels: { 1: '不符合', 2: '部分符合', 3: '符合', 4: '最佳实践' } as Record<number, string>,
    aiInstruction: '请为每个子类别撰写管理摘要。综合考虑评分和评语，重点突出关键问题（评分1–2），并提出具体改进建议。',
    summaryHeader: '总体概览',
    aiHeader:      'AI 指令',
    critical:      '关键问题（评分 1–2）',
    avgLabel:      '平均分',
    ratedLabel:    '已评分',
    questions:     '问题',
  },
} as const

// ── Helpers ───────────────────────────────────────────────────────────────────

const BMW_BLUE_ARGB = 'FF003D6B'
const WHITE_ARGB    = 'FFFFFFFF'

function avgFillArgb(avg: number | null): string {
  if (avg === null) return 'FFF7F9FB'
  if (avg < 2)   return 'FFFEE2E2'
  if (avg < 3)   return 'FFFFF3CD'
  if (avg < 3.5) return 'FFD1FAE5'
  return 'FFDBEAFE'
}

const RATING_FILL: Record<number, string> = {
  1: 'FFFEE2E2',
  2: 'FFFFF3CD',
  3: 'FFD1FAE5',
  4: 'FFDBEAFE',
}

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)
}

function localLabel(
  obj: unknown,
  field: string,
  lang: AssessmentLang,
): string {
  return getAssessmentText(obj as Record<string, string | null | undefined>, field, lang)
}

// ── Export 1: Sub-category ratings (Excel) ────────────────────────────────────

export async function exportSubcategoryRatings(
  subStats: SubCatStat[],
  assessmentTitle: string,
  lang: AssessmentLang = 'de',
  questions: QuestionWithCategories[] = [],
) {
  const L = exportLabels[lang]

  // Build localized label maps from questions when available
  const subLabelMap  = new Map<string, string>()
  const mainLabelMap = new Map<string, string>()
  for (const q of questions) {
    if (!subLabelMap.has(q.sub_category_id)) {
      subLabelMap.set(q.sub_category_id, localLabel(q.sub_category, 'label', lang))
    }
    if (!mainLabelMap.has(q.main_category_id)) {
      mainLabelMap.set(q.main_category_id, localLabel(q.main_category, 'label', lang))
    }
  }

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

  const ws = wb.addWorksheet('Ratings')

  ws.columns = [
    { key: 'main',     width: 32 },
    { key: 'sub',      width: 36 },
    { key: 'avg',      width: 15 },
    { key: 'total',    width: 20 },
    { key: 'answered', width: 16 },
    { key: 'progress', width: 16 },
  ]

  ws.addRow([L.mainCat, L.subCat, L.avgRating, L.total, L.answered, L.progress])
  const headerRow = ws.getRow(1)
  headerRow.height = 22
  headerRow.eachCell((cell) => {
    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: 'center' }
    cell.border = { bottom: { style: 'medium', color: { argb: 'FF0066B1' } } }
  })

  for (const s of subStats) {
    const mainLbl = mainLabelMap.get(s.mainId) ?? s.mainLabel
    const subLbl  = subLabelMap.get(s.id)     ?? s.label

    const row = ws.addRow({
      main:     mainLbl,
      sub:      subLbl,
      avg:      s.avg !== null ? Math.round(s.avg * 100) / 100 : null,
      total:    s.total,
      answered: s.answered,
      progress: s.total > 0 ? Math.round((s.answered / s.total) * 100) : 0,
    })

    const avgCell = row.getCell('avg')
    avgCell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: avgFillArgb(s.avg) } }
    if (s.avg !== null) {
      avgCell.font = { bold: true, size: 11, name: 'Calibri' }
      avgCell.numFmt = '0.00'
    }

    row.getCell('total').alignment    = { horizontal: 'center' }
    row.getCell('answered').alignment = { horizontal: 'center' }
    row.getCell('progress').alignment = { horizontal: 'center' }
    row.getCell('progress').numFmt    = '0"%"'
    row.height = 18
  }

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

  const buffer = await wb.xlsx.writeBuffer()
  await triggerDownload(buffer as ArrayBuffer, `assessment_subcategory_ratings_${lang}.xlsx`)
}

// ── Export 2: Question details (Excel) ────────────────────────────────────────

export async function exportQuestionDetails(
  questions: QuestionWithCategories[],
  responses: Record<string, ResponseDraft>,
  assessmentTitle: string,
  filterMode: 'all_answered' | 'rated_only' | 'all' = 'all_answered',
  lang: AssessmentLang = 'de',
) {
  const L = exportLabels[lang]

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

  const ws = wb.addWorksheet('Details')

  ws.columns = [
    { key: 'idx',        width: 8 },
    { key: 'main',       width: 26 },
    { key: 'sub',        width: 30 },
    { key: 'question',   width: 62 },
    { key: 'relevant',   width: 13 },
    { key: 'flagged',    width: 13 },
    { key: 'rating',     width: 12 },
    { key: 'ratingText', width: 36 },
    { key: 'comment',    width: 52 },
  ]

  ws.addRow(['#', L.mainCat, L.subCat, L.question, L.relevant, L.flagged, L.rating, L.ratingText, L.comment])
  const headerRow = ws.getRow(1)
  headerRow.height = 22
  headerRow.eachCell((cell) => {
    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: 'center' }
    cell.border = { bottom: { style: 'medium', color: { argb: 'FF0066B1' } } }
  })

  const sorted = [...questions]
    .sort((a, b) => a.index_number - b.index_number)
    .filter((q) => {
      if (filterMode === 'all') return true
      const r = responses[q.id]
      if (filterMode === 'rated_only') return r?.selected_rating !== null && r?.selected_rating !== undefined
      return (
        (r?.selected_rating !== null && r?.selected_rating !== undefined) ||
        !!r?.comment?.trim() ||
        !!r?.is_flagged ||
        r?.is_relevant !== false
      )
    })

  for (const q of sorted) {
    const r         = responses[q.id]
    const isRelevant = r?.is_relevant !== false
    const rating    = r?.selected_rating ?? null

    const qText     = localLabel(q, 'question_text', lang)
    const mainLbl   = localLabel(q.main_category, 'label', lang)
    const subLbl    = localLabel(q.sub_category,  'label', lang)
    const answerLbl = rating !== null
      ? (localLabel(q, `answer_text_${rating}`, lang) || L.ratingLabels[rating])
      : ''

    const row = ws.addRow({
      idx:        q.index_number,
      main:       mainLbl,
      sub:        subLbl,
      question:   qText,
      relevant:   isRelevant ? L.yes : L.no,
      flagged:    r?.is_flagged ? L.yes : L.no,
      rating:     rating ?? '',
      ratingText: answerLbl,
      comment:    r?.comment?.trim() ?? '',
    })

    row.height = 40
    row.alignment = { wrapText: true, vertical: 'top' }

    if (rating !== null) {
      const ratingCell = row.getCell('rating')
      ratingCell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: RATING_FILL[rating] } }
      ratingCell.font = { bold: true, name: 'Calibri' }
      ratingCell.alignment = { horizontal: 'center', vertical: 'top' }
    }

    if (!isRelevant) {
      row.eachCell((cell) => {
        cell.font = { color: { argb: 'FF9CA3AF' }, italic: true, name: 'Calibri' }
      })
    }

    if (r?.is_flagged) {
      row.getCell('flagged').font = { color: { argb: 'FFF97316' }, bold: true, name: 'Calibri' }
    }
  }

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

  const buffer = await wb.xlsx.writeBuffer()
  await triggerDownload(buffer as ArrayBuffer, `assessment_question_details_${lang}.xlsx`)
}

// ── Export 3: KI/AI Markdown summary ─────────────────────────────────────────

export function exportForAISummary(
  questions: QuestionWithCategories[],
  responses: Record<string, ResponseDraft>,
  assessment: Assessment,
  lang: AssessmentLang = 'de',
): void {
  const L = exportLabels[lang]

  const dateStr = assessment.conducted_at
    ? new Date(assessment.conducted_at).toLocaleDateString('de-DE')
    : new Date().toLocaleDateString('de-DE')

  // Group questions sorted by index_number
  const mainMap = new Map<string, {
    label: string
    sort_order: number
    subs: Map<string, { label: string; sort_order: number; qs: QuestionWithCategories[] }>
  }>()

  for (const q of [...questions].sort((a, b) => a.index_number - b.index_number)) {
    const mainLbl = localLabel(q.main_category, 'label', lang)
    const subLbl  = localLabel(q.sub_category,  'label', lang)

    if (!mainMap.has(q.main_category_id)) {
      mainMap.set(q.main_category_id, { label: mainLbl, sort_order: q.main_category.sort_order, subs: new Map() })
    }
    const m = mainMap.get(q.main_category_id)!
    if (!m.subs.has(q.sub_category_id)) {
      m.subs.set(q.sub_category_id, { label: subLbl, sort_order: q.sub_category.sort_order, qs: [] })
    }
    m.subs.get(q.sub_category_id)!.qs.push(q)
  }

  const sortedMains = [...mainMap.entries()].sort((a, b) => a[1].sort_order - b[1].sort_order)
  const lines: string[] = []

  lines.push(`# Fabrikanalyse — ${assessment.title} — ${assessment.supplier_name ?? '—'} — ${dateStr}`)
  lines.push('')
  if (assessment.location)          lines.push(`**${L.relevant}:** ${assessment.location}`)
  if (assessment.assessment_number) lines.push(`**Assessment-Nr.:** ${assessment.assessment_number}`)
  lines.push('')
  lines.push(`## ${L.scale}`)
  lines.push(L.scale)
  lines.push('')
  lines.push('---')
  lines.push('')

  const summaryRows: { main: string; answered: number; total: number; avg: number | null; critical: number }[] = []

  for (const [, { label: mainLabel, subs }] of sortedMains) {
    lines.push(`## ${mainLabel}`)
    lines.push('')

    const sortedSubs = [...subs.entries()].sort((a, b) => a[1].sort_order - b[1].sort_order)
    let mainAnswered = 0, mainTotal = 0, mainRatingSum = 0, mainCritical = 0

    for (const [, { label: subLabel, qs }] of sortedSubs) {
      lines.push(`### ${subLabel}`)
      lines.push('')

      let subAnswered = 0, subRatingSum = 0, subCritical = 0
      const relevantQs = qs.filter((q) => responses[q.id]?.is_relevant !== false)

      for (const q of relevantQs) {
        const r       = responses[q.id]
        const rating  = r?.selected_rating ?? null
        const comment = r?.comment?.trim() ?? ''
        const qText   = localLabel(q, 'question_text', lang)

        lines.push(`**${L.question} ${q.index_number}:** ${qText}`)

        if (rating !== null) {
          const aText = localLabel(q, `answer_text_${rating}`, lang)
                     || L.ratingLabels[rating]
          lines.push(`- **${L.rating}:** ${rating} — ${L.ratingLabels[rating]}`)
          lines.push(`- **${L.ratingText}:** ${aText}`)
          if (comment) lines.push(`- **${L.comment}:** ${comment}`)
          subAnswered++
          subRatingSum += rating
          if (rating <= 2) subCritical++
        } else {
          lines.push(`- **${L.rating}:** ${L.notRated}`)
          if (comment) lines.push(`- **${L.comment}:** ${comment}`)
        }
        lines.push('')
      }

      const subAvg = subAnswered > 0 ? subRatingSum / subAnswered : null
      lines.push(`#### ${subLabel} — ${L.avgLabel}`)
      lines.push(`- ${L.ratedLabel}: ${subAnswered}/${relevantQs.length} ${L.questions}`)
      if (subAvg !== null) lines.push(`- ${L.avgLabel}: ${subAvg.toFixed(2)}`)
      if (subCritical > 0) {
        const critNrs = relevantQs
          .filter((q) => {
            const r = responses[q.id]
            return r?.selected_rating !== null && r?.selected_rating !== undefined && r.selected_rating <= 2
          })
          .map((q) => `${L.question} ${q.index_number}`)
          .join(', ')
        lines.push(`- ${L.critical}: ${critNrs}`)
      }
      lines.push('')
      lines.push('---')
      lines.push('')

      mainAnswered  += subAnswered
      mainTotal     += relevantQs.length
      mainRatingSum += subRatingSum
      mainCritical  += subCritical
    }

    summaryRows.push({
      main:     mainLabel,
      answered: mainAnswered,
      total:    mainTotal,
      avg:      mainAnswered > 0 ? mainRatingSum / mainAnswered : null,
      critical: mainCritical,
    })
  }

  lines.push(`## ${L.summaryHeader}`)
  lines.push('')
  lines.push(`| ${L.mainCat} | ${L.ratedLabel} | ${L.avgLabel} | ${L.critical} |`)
  lines.push('|---|---|---|---|')
  for (const row of summaryRows) {
    lines.push(`| ${row.main} | ${row.answered}/${row.total} | ${row.avg !== null ? row.avg.toFixed(2) : '—'} | ${row.critical > 0 ? `${row.critical} ${L.questions}` : '—'} |`)
  }
  lines.push('')
  lines.push(`## ${L.aiHeader}`)
  lines.push(L.aiInstruction)
  lines.push('')

  const content  = lines.join('\n')
  const blob     = new Blob([content], { type: 'text/markdown; charset=utf-8' })
  const url      = URL.createObjectURL(blob)
  const a        = document.createElement('a')
  const safeName = assessment.title.replace(/[^a-zA-Z0-9_-]/g, '_').slice(0, 40)
  a.href     = url
  a.download = `assessment_${safeName}_${lang}_${dateStr.replace(/\./g, '-')}.md`
  a.click()
  URL.revokeObjectURL(url)
}