/* global XLSX */
// ============================================================================
// SAT IMPORT — read a filled SAT_REFT Africa template and upsert responses
// ----------------------------------------------------------------------------
// Strict mapping: column positions match the REFT Africa template exactly.
// Copies of the template (any file name) work as long as the structure stays.
//
//   Sheet "Sommaire":
//     I4  = project/programme name (informational, we already know it)
//     AH4 = evaluation date
//     I6  = country
//     AH6 = organization
//     I8  = team_lead
//     U8  = organization_lead
//
//   Sheets A..H (norms):
//     Row 4         = header (Liste de Contrôle | MOV | Notation | Value | Max | Observations)
//     Row 6 onward  = one norm per row, until "Recommendations Prioritaires"
//       col A (1)  = norm number ("1." etc.)
//       col B (2)  = norm text
//       col E (5)  = rating label (FR)
//       col F (6)  = numeric value
//       col H (8)  = observation
//
// Public API exposed at window.satImporter:
//   importSatFromFile(file, opts) → { metadata, ratings: Map<normKey, payload>, errors }
//   applyImport(importedData, evaluation, norms) → upserts responses + updates evaluation metadata
// ============================================================================

(function () {
  if (typeof XLSX === "undefined") {
    window.satImporter = {
      available: false,
      reason: "La librairie SheetJS (xlsx) n'a pas pu être chargée.",
    };
    return;
  }

  // Rating-label → code mapping. Templates use minor variations of these
  // strings (whitespace, accents). We normalize before comparing.
  function norm(s) {
    if (s == null) return "";
    return String(s).toLowerCase()
      .replace(/[àâä]/g, "a")
      .replace(/[éèêë]/g, "e")
      .replace(/[îï]/g, "i")
      .replace(/[ôö]/g, "o")
      .replace(/[ûüù]/g, "u")
      .replace(/[ç]/g, "c")
      .replace(/\s+/g, " ")
      .trim();
  }

  // Standard scale (A-D, F-H): N/A / 0 / 1 / 2
  const STD_LABELS = {
    "n/a": "na",
    "na": "na",
    "ne repond pas du tout": "none",
    "ne repond pas": "none",
    "repond partiellement": "partial",
    "repond pleinement": "full",
  };
  // Domain E: N/A / 0 / 5 / 10
  const E_LABELS = {
    "n/a": "na",
    "na": "na",
    "plus de 10% des donnees rapportees": "above_10",
    "plus de 10%": "above_10",
    "entre 5 et 10% des donnees rapportees": "5_to_10",
    "entre 5 et 10%": "5_to_10",
    "moins de 5% des donnees rapportees": "under_5",
    "moins de 5%": "under_5",
  };

  function ratingFromLabel(domain, label) {
    const key = norm(label);
    if (!key) return null;
    const map = domain === "E" ? E_LABELS : STD_LABELS;
    if (map[key]) return map[key];
    // Fallback: contains-match for slight variations
    for (const k of Object.keys(map)) {
      if (key.includes(k)) return map[k];
    }
    return null;
  }

  function ratingValue(domain, code, fallbackMax) {
    if (code === "na" || code == null) return null;
    if (domain === "E") {
      return code === "above_10" ? 0 : code === "5_to_10" ? 5 : code === "under_5" ? 10 : null;
    }
    return code === "none" ? 0 : code === "partial" ? 1 : code === "full" ? 2 : null;
  }

  // Parse one domain sheet → list of { position, rating, value, observation }
  function parseDomainSheet(ws, domainCode) {
    const out = [];
    // Find the last row of norms (stop at "Recommendations Prioritaires" or empty)
    const range = XLSX.utils.decode_range(ws["!ref"] || "A1:L30");
    for (let r = 5; r <= Math.min(range.e.r, 30); r++) {  // row 5 = Excel row 6 (0-indexed)
      const cellNum = ws[XLSX.utils.encode_cell({ r, c: 0 })];  // col A
      const cellTxt = ws[XLSX.utils.encode_cell({ r, c: 1 })];  // col B
      const text = cellTxt && cellTxt.v != null ? String(cellTxt.v) : "";
      if (!text) continue;
      if (text.toLowerCase().includes("recommendation")) break;
      // Parse the position number
      let position = null;
      if (cellNum && cellNum.v != null) {
        const m = String(cellNum.v).match(/(\d+)/);
        if (m) position = parseInt(m[1], 10);
      }
      if (!position) continue;
      // Read rating label + value + observation
      const cellRating = ws[XLSX.utils.encode_cell({ r, c: 4 })];   // col E
      const cellValue  = ws[XLSX.utils.encode_cell({ r, c: 5 })];   // col F
      const cellObs    = ws[XLSX.utils.encode_cell({ r, c: 7 })];   // col H
      const ratingLabel = cellRating && cellRating.v != null ? String(cellRating.v) : null;
      const rawValue    = cellValue && cellValue.v != null ? cellValue.v : null;
      const observation = cellObs && cellObs.v != null ? String(cellObs.v).trim() : null;
      const rating = ratingFromLabel(domainCode, ratingLabel);
      let value = ratingValue(domainCode, rating);
      // If the cell has a numeric value that disagrees, prefer the cell value
      // when the rating couldn't be resolved.
      if (value == null && typeof rawValue === "number" && rating !== "na") {
        value = rawValue;
      }
      out.push({ position, rating, value, observation, ratingLabelRaw: ratingLabel });
    }
    return out;
  }

  // Parse the Sommaire sheet → metadata
  function parseSommaire(ws) {
    if (!ws) return {};
    const get = (addr) => {
      const c = ws[addr];
      return c && c.v != null ? String(c.v).trim() : null;
    };
    return {
      projectName:        get("I4"),
      date:               get("AH4"),
      country:            get("I6"),
      organization:       get("AH6"),
      team_lead:          get("I8"),
      organization_lead:  get("U8"),
    };
  }

  // ---------- Public entry points ------------------------------------------

  async function importSatFromFile(file) {
    const buf = await file.arrayBuffer();
    const wb = XLSX.read(buf, { type: "array" });
    const errors = [];
    const responses = {};  // { "A_1": { position, rating, value, observation }, ... }
    const DOMAINS = ["A", "B", "C", "D", "E", "F", "G", "H"];
    for (const d of DOMAINS) {
      const ws = wb.Sheets[d];
      if (!ws) {
        errors.push("Onglet manquant : " + d);
        continue;
      }
      const rows = parseDomainSheet(ws, d);
      rows.forEach((row) => {
        responses[d + "_" + row.position] = row;
      });
    }
    const metadata = parseSommaire(wb.Sheets["Sommaire"]);
    return { metadata, responses, errors, sheetNames: wb.SheetNames };
  }

  // Persist parsed data to the database:
  //   - patches sat_evaluations (country, organization, team_lead, etc.)
  //   - for each norm with a parsed response, upserts sat_responses
  // Returns { upserted, skipped, evalPatch }.
  async function applyImport(parsed, evaluation, norms) {
    const sb = window.melr && window.melr.supabase;
    if (!sb) throw new Error("Base de données indisponible");
    if (!evaluation) throw new Error("Évaluation absente");

    // 1) Patch the evaluation metadata where the imported data has values
    const evalPatch = {};
    if (parsed.metadata.country)           evalPatch.country = parsed.metadata.country;
    if (parsed.metadata.organization)      evalPatch.organization = parsed.metadata.organization;
    if (parsed.metadata.team_lead)         evalPatch.team_lead = parsed.metadata.team_lead;
    if (parsed.metadata.organization_lead) evalPatch.organization_lead = parsed.metadata.organization_lead;
    if (parsed.metadata.date) {
      // Try to parse as date if it looks like one
      try {
        const d = new Date(parsed.metadata.date);
        if (!isNaN(d.getTime())) evalPatch.end_date = d.toISOString().slice(0, 10);
      } catch (e) {}
    }
    if (Object.keys(evalPatch).length > 0) {
      await window.melr.satEvaluationsCrud.update(evaluation.id, evalPatch);
    }

    // 2) Upsert each parsed response
    const normByKey = new Map();
    (norms || []).forEach((n) => { normByKey.set(n.domain_code + "_" + n.position, n); });

    let upserted = 0, skipped = 0;
    const errors = [];
    for (const key of Object.keys(parsed.responses)) {
      const row = parsed.responses[key];
      const norm = normByKey.get(key);
      if (!norm) { skipped++; continue; }
      if (!row.rating && !row.observation) { skipped++; continue; }
      try {
        await window.melr.satResponsesCrud.upsert(evaluation.id, norm.id, {
          rating: row.rating || null,
          value: row.value,
          observation: row.observation || null,
        });
        upserted++;
      } catch (e) {
        errors.push("Norme " + key + " : " + e.message);
      }
    }

    return { upserted, skipped, errors, evalPatch };
  }

  window.satImporter = {
    available: true,
    importSatFromFile,
    applyImport,
  };
})();