import {
  CHECKLIST_COLUMNS,
  CHECKLIST_ROW_COUNT,
  CHECKLIST_START_ROW,
  HEADER_CELLS,
  HEADER_FIELD_KEYWORDS,
  normalizeQuestionText,
  normalizeRoleLabel,
  PERMIT_DATETIME_CELLS,
  SIGNOFF_ROWS,
  SIGNOFF_VALUE_COLUMNS,
  TEMPLATE_CHECKLIST_LABELS,
  TEMPLATE_PATH
} from "../config/generalPtwExcelMap.js";
import { SECTION_KIND, resolveSectionKind } from "./sectionTypeHelpers.js";
import {
  getSignoffRoleLabel,
  isAuthorizationQuestionType,
  parseSignoffAnswer,
  QUESTION_TYPE_KIND,
  resolveQuestionTypeKind
} from "./questionTypeHelpers.js";

function sectionCacheKey(sectionId) {
  return String(sectionId);
}

function readAnswer(snapshot, questionId) {
  if (!snapshot?.answers) return "";
  return snapshot.answers[questionId] ?? snapshot.answers[String(questionId)] ?? "";
}

function readMedia(snapshot, questionId) {
  if (!snapshot?.mediaByQuestion) return [];
  const files =
    snapshot.mediaByQuestion[questionId] ||
    snapshot.mediaByQuestion[String(questionId)] ||
    [];
  return Array.isArray(files) ? files.filter(Boolean) : [];
}

function typeOptionsForQuestion(question, questionTypeOptions) {
  if (question?.question_type) {
    return [
      {
        value: String(question.question_type_id ?? ""),
        label: question.question_type
      }
    ];
  }
  return questionTypeOptions;
}

function isSectionSkipped(section, snapshot) {
  if (!snapshot?.skipChoice || snapshot.skipChoice !== "yes") return false;
  return true;
}

function questionMatchesKeywords(questionText, keywords) {
  const normalized = normalizeQuestionText(questionText);
  return keywords.some((kw) => normalized.includes(normalizeQuestionText(kw)));
}

export function extractChecklistNumber(text) {
  const match = String(text || "")
    .trim()
    .match(/^(\d+|x)\s*[-–—]/i);
  return match ? match[1].toLowerCase() : null;
}

export function resolveChecklistColumn(answer) {
  const value = String(answer || "").trim().toLowerCase();
  if (value === "yes") return CHECKLIST_COLUMNS.yes;
  if (value === "no") return CHECKLIST_COLUMNS.no;
  if (value === "n/a" || value === "na" || value === "n/a.") return CHECKLIST_COLUMNS.na;
  return null;
}

export function resolveSignoffRow(roleLabel) {
  const key = normalizeRoleLabel(roleLabel);
  return SIGNOFF_ROWS[key] ?? null;
}

function fuzzyMatchChecklistIndex(questionText) {
  const normalized = normalizeQuestionText(questionText);
  let bestIdx = -1;
  let bestScore = 0;

  for (let i = 0; i < TEMPLATE_CHECKLIST_LABELS.length; i += 1) {
    const label = TEMPLATE_CHECKLIST_LABELS[i];
    const words = label.split(" ").filter((w) => w.length > 4);
    const hits = words.filter((w) => normalized.includes(w)).length;
    const score = hits / Math.max(words.length, 1);
    if (score > bestScore && score >= 0.35) {
      bestScore = score;
      bestIdx = i;
    }
  }

  return bestIdx;
}

export function matchChecklistRow(questionText, fallbackIndex = null) {
  const prefix = extractChecklistNumber(questionText);
  if (prefix === "x") {
    return CHECKLIST_START_ROW + 3;
  }
  if (prefix && /^\d+$/.test(prefix)) {
    const num = Number(prefix);
    if (num >= 1 && num <= 20) {
      const rowOffset = num <= 3 ? num - 1 : num;
      return CHECKLIST_START_ROW + rowOffset;
    }
  }

  const fuzzyIdx = fuzzyMatchChecklistIndex(questionText);
  if (fuzzyIdx >= 0) {
    return CHECKLIST_START_ROW + fuzzyIdx;
  }

  if (fallbackIndex != null && fallbackIndex >= 0 && fallbackIndex < CHECKLIST_ROW_COUNT) {
    return CHECKLIST_START_ROW + fallbackIndex;
  }

  return null;
}

export function splitDateTimeParts(iso) {
  if (!iso) return null;
  const date = new Date(iso);
  if (Number.isNaN(date.getTime())) return null;
  return {
    day: date.getDate(),
    month: date.getMonth() + 1,
    year: date.getFullYear(),
    hour: date.getHours(),
    minute: date.getMinutes()
  };
}

export function formatTaskDuration(validFrom, validUntil) {
  const start = validFrom ? new Date(validFrom).getTime() : NaN;
  const end = validUntil ? new Date(validUntil).getTime() : NaN;
  if (!Number.isFinite(start) || !Number.isFinite(end) || end <= start) return "";
  const hours = Math.round((end - start) / (1000 * 60 * 60));
  return `${hours} hour${hours === 1 ? "" : "s"}`;
}

function isYesNoQuestion(question, questionTypeOptions) {
  const typeOpts = typeOptionsForQuestion(question, questionTypeOptions);
  return (
    resolveQuestionTypeKind(
      question.question_type_id,
      typeOpts,
      question.question_type
    ) === QUESTION_TYPE_KIND.YES_NO
  );
}

function isChecklistSectionKind(kind) {
  return kind === SECTION_KIND.STANDARD || kind === SECTION_KIND.RISK;
}

function isGeneralSectionKind(kind) {
  return kind === SECTION_KIND.GENERAL;
}

function assignHeaderField(header, key, value) {
  if (value == null || String(value).trim() === "") return;
  if (header[key] == null || String(header[key]).trim() === "") {
    header[key] = String(value).trim();
  }
}

function detectHeaderFieldKey(questionText) {
  for (const [field, keywords] of Object.entries(HEADER_FIELD_KEYWORDS)) {
    if (questionMatchesKeywords(questionText, keywords)) {
      return field;
    }
  }
  return null;
}

export function buildExportViewModel(snapshot) {
  if (!snapshot?.orderedSections?.length || !snapshot?.sectionCache) {
    throw new Error("Export snapshot is missing form data.");
  }

  const { orderedSections, sectionCache, questionTypeOptions = [] } = snapshot;
  const header = {
    permitNumber: snapshot.permitNumber || "",
    jobLocation: "",
    jobDetails: "",
    otherWork: "",
    otherPrecautions: "",
    safetyEquipment: ""
  };
  const checklistByRow = {};
  const signoffsByRow = {};
  let checklistFallbackIndex = 0;
  const generalTextFallback = [];

  for (const section of orderedSections) {
    const cacheKey = sectionCacheKey(section.section_id);
    const sectionSnapshot = sectionCache[cacheKey];
    if (!sectionSnapshot || isSectionSkipped(section, sectionSnapshot)) continue;

    const kind = resolveSectionKind(section);
    const questions = section.questions || [];

    for (const question of questions) {
      const typeOpts = typeOptionsForQuestion(question, questionTypeOptions);
      const qText = question.question || "";
      const answer = readAnswer(sectionSnapshot, question.question_id);

      if (
        isAuthorizationQuestionType(
          question.question_type_id,
          typeOpts,
          question.question_type
        )
      ) {
        const role = getSignoffRoleLabel(question);
        const row = resolveSignoffRow(role);
        if (row) {
          const parsed = parseSignoffAnswer(answer);
          signoffsByRow[row] = {
            role,
            name: parsed.name || "",
            company: parsed.company || "",
            signatureFile: readMedia(sectionSnapshot, question.question_id)[0] || null
          };
        } else {
          console.warn("[generalPtwExcelExport] Unmapped sign-off role:", role);
        }
        continue;
      }

      if (isYesNoQuestion(question, questionTypeOptions) && isChecklistSectionKind(kind)) {
        const row = matchChecklistRow(qText, checklistFallbackIndex);
        if (row) {
          checklistByRow[row] = answer;
          checklistFallbackIndex += 1;
        } else {
          console.warn("[generalPtwExcelExport] Unmatched checklist question:", qText);
        }
        continue;
      }

      if (isGeneralSectionKind(kind)) {
        const fieldKey = detectHeaderFieldKey(qText);
        if (fieldKey) {
          assignHeaderField(header, fieldKey, answer);
        } else if (
          resolveQuestionTypeKind(
            question.question_type_id,
            typeOpts,
            question.question_type
          ) === QUESTION_TYPE_KIND.TEXT
        ) {
          generalTextFallback.push({ qText, answer });
        }
      }
    }
  }

  if (!header.jobLocation && generalTextFallback[0]) {
    header.jobLocation = String(generalTextFallback[0].answer || "").trim();
  }
  if (!header.jobDetails && generalTextFallback[1]) {
    header.jobDetails = String(generalTextFallback[1].answer || "").trim();
  }
  if (!header.otherWork && generalTextFallback[2]) {
    header.otherWork = String(generalTextFallback[2].answer || "").trim();
  }

  return {
    header,
    checklistByRow,
    signoffsByRow,
    validFrom: snapshot.validFrom || null,
    validUntil: snapshot.validUntil || null
  };
}

function writeCell(sheet, address, value) {
  if (value == null || value === "") return;
  sheet.getCell(address).value = value;
}

async function blobToBuffer(file) {
  if (!file) return null;
  if (file instanceof Blob) {
    return file.arrayBuffer();
  }
  return null;
}

function signatureExtension(file) {
  const name = String(file?.name || "").toLowerCase();
  if (name.endsWith(".jpg") || name.endsWith(".jpeg")) return "jpeg";
  if (name.endsWith(".gif")) return "gif";
  return "png";
}

export async function fillWorkbook(workbook, viewModel) {
  const sheet = workbook.worksheets[0];
  if (!sheet) {
    throw new Error("Template workbook has no worksheets.");
  }

  const { header, checklistByRow, signoffsByRow, validFrom, validUntil } = viewModel;

  writeCell(sheet, HEADER_CELLS.permitNumber, header.permitNumber);
  writeCell(sheet, HEADER_CELLS.jobLocation, header.jobLocation);
  writeCell(sheet, HEADER_CELLS.jobDetails, header.jobDetails);
  writeCell(sheet, HEADER_CELLS.otherWork, header.otherWork);
  writeCell(sheet, HEADER_CELLS.otherPrecautions, header.otherPrecautions);
  writeCell(sheet, HEADER_CELLS.safetyEquipment, header.safetyEquipment);

  for (const [row, answer] of Object.entries(checklistByRow)) {
    const col = resolveChecklistColumn(answer);
    if (col) {
      writeCell(sheet, `${col}${row}`, "X");
    }
  }

  for (const [row, data] of Object.entries(signoffsByRow)) {
    writeCell(sheet, `${SIGNOFF_VALUE_COLUMNS.name}${row}`, data.name);
    writeCell(sheet, `${SIGNOFF_VALUE_COLUMNS.company}${row}`, data.company);

    if (data.signatureFile) {
      const buffer = await blobToBuffer(data.signatureFile);
      if (buffer) {
        const imageId = workbook.addImage({
          buffer,
          extension: signatureExtension(data.signatureFile)
        });
        const colIndex = SIGNOFF_VALUE_COLUMNS.signature.charCodeAt(0) - 65;
        sheet.addImage(imageId, {
          tl: { col: colIndex + 0.1, row: Number(row) - 1 + 0.15 },
          ext: { width: 90, height: 36 }
        });
      }
    }
  }

  const fromParts = splitDateTimeParts(validFrom);
  if (fromParts) {
    writeCell(sheet, PERMIT_DATETIME_CELLS.validFromDate.day, fromParts.day);
    writeCell(sheet, PERMIT_DATETIME_CELLS.validFromDate.month, fromParts.month);
    writeCell(sheet, PERMIT_DATETIME_CELLS.validFromDate.year, fromParts.year);
    writeCell(sheet, PERMIT_DATETIME_CELLS.validFromTime.hour, fromParts.hour);
    writeCell(sheet, PERMIT_DATETIME_CELLS.validFromTime.minute, fromParts.minute);
  }

  const untilParts = splitDateTimeParts(validUntil);
  if (untilParts) {
    writeCell(sheet, PERMIT_DATETIME_CELLS.validUntilDate.day, untilParts.day);
    writeCell(sheet, PERMIT_DATETIME_CELLS.validUntilDate.month, untilParts.month);
    writeCell(sheet, PERMIT_DATETIME_CELLS.validUntilDate.year, untilParts.year);
    writeCell(sheet, PERMIT_DATETIME_CELLS.validUntilTime.hour, untilParts.hour);
    writeCell(sheet, PERMIT_DATETIME_CELLS.validUntilTime.minute, untilParts.minute);
  }

  writeCell(
    sheet,
    PERMIT_DATETIME_CELLS.taskDuration,
    formatTaskDuration(validFrom, validUntil)
  );

  return workbook;
}

async function loadExcelJS() {
  const { default: ExcelJS } = await import("exceljs");
  return ExcelJS;
}

export async function loadTemplateWorkbook(templateUrl = TEMPLATE_PATH) {
  const response = await fetch(templateUrl);
  if (!response.ok) {
    throw new Error("Could not load the Excel template. Contact your administrator.");
  }
  const buffer = await response.arrayBuffer();
  const ExcelJS = await loadExcelJS();
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.load(buffer);
  return workbook;
}

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

export async function downloadGeneralPtwExcel(snapshot) {
  const viewModel = buildExportViewModel(snapshot);
  const workbook = await loadTemplateWorkbook();
  await fillWorkbook(workbook, viewModel);

  const permitLabel = String(snapshot.permitNumber || "permit").replace(/[^\w.-]+/g, "_");
  const filename = `${permitLabel}-general-ptw.xlsx`;
  const buffer = await workbook.xlsx.writeBuffer();
  triggerDownload(buffer, filename);
}
