/* global XLSX */
// ============================================================================
// DVT IMPORT — read a filled REFT Africa DVT workbook and upsert content
// ----------------------------------------------------------------------------
// Strict mapping per the template:
//   Sheet "Vérification des Données":
//     Row 1  A:label "Pays :"             B: country     | F:label "Région de :"               G: region
//     Row 3  A:label "Département/Unité :" B: department | F:label "District Sanitaire de :"   G: district
//     Row 5  A:label "Nom du Site :"      B: site_name (programme-level) | F:label "Nom du site :" G: site
//     Row 7  A:label "Nom du Programme :" B: programme   | F:label "Période Auditée :"          G: period
//     Row 9  A: "Chef d'Equipe :"         B: team_lead   | F: "Chef d'Equipe site :"            G: site_team_lead
//     Row 11 A: "Staff #1 :"              B: staff_1     | F: "Staff du site #1 :"              G: site_staff_1
//     Row 13 / 15 — staff_2 / staff_3 and site_staff_2 / site_staff_3
//     Row 17 A: "Autres Participants :"   B: other_participants
//     Row 19 — header line (skipped)
//     Row 20-21 — column headers (skipped)
//     Rows 22-41 — 20 indicator rows:
//        A = indicator_name | B = doc_source
//        C = m1_verified    | D = m1_reported   | E = (variance, skip)
//        F = m2_verified    | G = m2_reported   | H = (variance, skip)
//        I = m3_verified    | J = m3_reported   | K = (variance, skip)
//        L = (avg variance, skip)
//     Row 43+ — "Comments:" + content rows below
//
//   Sheet "Amélioration des Données":
//     Same metadata block but right column at D (label) / E (value).
//     Rows 21-36 — improvement rows:
//        A = indicator | B = problem | C = corrective | D = followup
//        E = due_date  | F = responsible
//     Row 37+ — "Commentaires :" + content rows below
//
// Public API exposed at window.dvtImporter:
//   importDvtFromFile(file) → { metadata, verifications[], improvements[], errors[] }
//   applyImport(parsed, audit) → upserts everything (audit row + child rows)
// ============================================================================

(function () {
  if (typeof XLSX === "undefined") {
    window.dvtImporter = { available: false, reason: "XLSX manquant" };
    return;
  }

  // ---------- helpers --------------------------------------------------------
  function cellVal(ws, addr) {
    const c = ws[addr];
    if (!c || c.v == null) return null;
    return c.v;
  }
  function cellStr(ws, addr) {
    const v = cellVal(ws, addr);
    if (v == null) return null;
    return String(v).trim();
  }
  function cellNum(ws, addr) {
    const v = cellVal(ws, addr);
    if (v == null || v === "") return null;
    const n = Number(v);
    return isFinite(n) ? n : null;
  }
  function cellDate(ws, addr) {
    const v = cellVal(ws, addr);
    if (v == null || v === "") return null;
    // Excel serial date number → ISO date
    if (typeof v === "number") {
      // 1900-based with the leap-year bug
      const days = v - 25569;
      const d = new Date(days * 86400000);
      if (!isNaN(d.getTime())) return d.toISOString().slice(0, 10);
    }
    // Try parsing as a string date
    try {
      const d = new Date(v);
      if (!isNaN(d.getTime())) return d.toISOString().slice(0, 10);
    } catch (e) {}
    return String(v);
  }

  // Parse metadata block from a sheet, given the right-column label cell letter.
  // Both sheets share the same row layout but the right-column label sits at
  // column F on "Vérification" and column D on "Amélioration".
  function parseMetadataBlock(ws, rightColLabel) {
    if (!ws) return {};
    const rightValueCol = String.fromCharCode(rightColLabel.charCodeAt(0) + 1);  // F→G, D→E
    return {
      country:            cellStr(ws, "B1")  || cellStr(ws, "C1"),
      region:             cellStr(ws, rightValueCol + "1"),
      department:         cellStr(ws, "B3")  || cellStr(ws, "C3"),
      district:           cellStr(ws, rightValueCol + "3"),
      programme_name:     cellStr(ws, "B7")  || cellStr(ws, "C7"),
      site_name:          cellStr(ws, rightValueCol + "5"),
      period_audited:     cellStr(ws, rightValueCol + "7"),
      team_lead:          cellStr(ws, "B9")  || cellStr(ws, "C9"),
      site_team_lead:     cellStr(ws, rightValueCol + "9"),
      staff_1:            cellStr(ws, "B11") || cellStr(ws, "C11"),
      site_staff_1:       cellStr(ws, rightValueCol + "11"),
      staff_2:            cellStr(ws, "B13") || cellStr(ws, "C13"),
      site_staff_2:       cellStr(ws, rightValueCol + "13"),
      staff_3:            cellStr(ws, "B15") || cellStr(ws, "C15"),
      site_staff_3:       cellStr(ws, rightValueCol + "15"),
      other_participants: cellStr(ws, "B17") || cellStr(ws, "C17"),
    };
  }

  function parseVerifications(ws) {
    if (!ws) return [];
    const out = [];
    // Rows 22..41 (Excel 1-indexed) → 20 rows
    for (let row = 22; row <= 41; row++) {
      const indicator_name = cellStr(ws, "A" + row);
      const doc_source     = cellStr(ws, "B" + row);
      const m1v = cellNum(ws, "C" + row);
      const m1r = cellNum(ws, "D" + row);
      const m2v = cellNum(ws, "F" + row);
      const m2r = cellNum(ws, "G" + row);
      const m3v = cellNum(ws, "I" + row);
      const m3r = cellNum(ws, "J" + row);
      // Skip rows with no content at all
      if (!indicator_name && !doc_source && m1v == null && m1r == null && m2v == null && m2r == null && m3v == null && m3r == null) continue;
      out.push({
        position: row - 21,  // row 22 = position 1
        indicator_name, doc_source,
        m1_verified: m1v, m1_reported: m1r,
        m2_verified: m2v, m2_reported: m2r,
        m3_verified: m3v, m3_reported: m3r,
      });
    }
    return out;
  }

  function parseImprovements(ws) {
    if (!ws) return [];
    const out = [];
    // Rows 21..36 (Excel 1-indexed) → 16 rows
    for (let row = 21; row <= 36; row++) {
      const indicator_name    = cellStr(ws, "A" + row);
      const problem           = cellStr(ws, "B" + row);
      const corrective_action = cellStr(ws, "C" + row);
      const followup_steps    = cellStr(ws, "D" + row);
      const due_date          = cellDate(ws, "E" + row);
      const responsible       = cellStr(ws, "F" + row);
      if (!indicator_name && !problem && !corrective_action && !followup_steps && !due_date && !responsible) continue;
      out.push({
        position: row - 20,  // row 21 = position 1
        indicator_name, problem, corrective_action,
        followup_steps, due_date, responsible,
      });
    }
    return out;
  }

  // Parse comments from a sheet — labelled "Comments:" or "Commentaires :"
  // Look at the next 5 rows after the label.
  function parseComments(ws, labels) {
    if (!ws) return null;
    const range = XLSX.utils.decode_range(ws["!ref"] || "A1:A50");
    for (let r = range.s.r; r <= range.e.r; r++) {
      const cell = ws[XLSX.utils.encode_cell({ r, c: 0 })];
      if (!cell || cell.v == null) continue;
      const s = String(cell.v).toLowerCase();
      if (labels.some((l) => s.startsWith(l))) {
        // Read content from the cell to the right (column B) of the same row,
        // and the next 3 rows of column A.
        const parts = [];
        const next = ws[XLSX.utils.encode_cell({ r, c: 1 })];
        if (next && next.v) parts.push(String(next.v).trim());
        for (let i = 1; i <= 3; i++) {
          const c2 = ws[XLSX.utils.encode_cell({ r: r + i, c: 0 })];
          if (c2 && c2.v) parts.push(String(c2.v).trim());
        }
        return parts.join("\n").trim() || null;
      }
    }
    return null;
  }

  // ---------- public entry points -------------------------------------------
  async function importDvtFromFile(file) {
    const buf = await file.arrayBuffer();
    const wb = XLSX.read(buf, { type: "array" });
    const errors = [];

    // Tolerate small variations in sheet naming
    const findSheet = (...candidates) => {
      for (const c of candidates) {
        for (const name of wb.SheetNames) {
          if (name.toLowerCase().replace(/[éè]/g, "e") === c.toLowerCase().replace(/[éè]/g, "e")) {
            return wb.Sheets[name];
          }
        }
      }
      return null;
    };
    const wsVerif = findSheet("Vérification des Données", "Verification des Donnees");
    const wsImpr  = findSheet("Amélioration des Données", "Amelioration des Donnees");
    if (!wsVerif) errors.push("Onglet manquant : Vérification des Données");
    if (!wsImpr)  errors.push("Onglet manquant : Amélioration des Données");

    // Metadata: prefer the verification sheet; fallback to improvement
    const metaA = wsVerif ? parseMetadataBlock(wsVerif, "F") : {};
    const metaB = wsImpr  ? parseMetadataBlock(wsImpr,  "D") : {};
    const metadata = {};
    [metaA, metaB].forEach((m) => {
      Object.keys(m).forEach((k) => {
        if (metadata[k] == null && m[k] != null) metadata[k] = m[k];
      });
    });
    metadata.comments_verification = wsVerif ? parseComments(wsVerif, ["comments", "commentaires"]) : null;
    metadata.comments_improvement  = wsImpr  ? parseComments(wsImpr,  ["comments", "commentaires"]) : null;

    const verifications = wsVerif ? parseVerifications(wsVerif) : [];
    const improvements  = wsImpr  ? parseImprovements(wsImpr)   : [];

    return { metadata, verifications, improvements, errors, sheetNames: wb.SheetNames };
  }

  async function applyImport(parsed, audit) {
    const sb = window.melr && window.melr.supabase;
    if (!sb) throw new Error("Base de données indisponible");
    if (!audit) throw new Error("Audit absent");

    // 1) Patch metadata on the audit row
    const meta = parsed.metadata || {};
    const patch = {};
    [
      "country", "region", "department", "district",
      "programme_name", "site_name", "period_audited",
      "team_lead", "staff_1", "staff_2", "staff_3",
      "site_team_lead", "site_staff_1", "site_staff_2", "site_staff_3",
      "other_participants",
      "comments_verification", "comments_improvement",
    ].forEach((k) => { if (meta[k]) patch[k] = meta[k]; });
    if (Object.keys(patch).length > 0) {
      await window.melr.dvtAuditsCrud.update(audit.id, patch);
    }

    // 2) Upsert verifications
    let verifCount = 0;
    for (const row of parsed.verifications) {
      try {
        await window.melr.dvtVerificationsCrud.upsert(audit.id, row.position, {
          indicator_name: row.indicator_name || null,
          doc_source: row.doc_source || null,
          m1_verified: row.m1_verified,
          m1_reported: row.m1_reported,
          m2_verified: row.m2_verified,
          m2_reported: row.m2_reported,
          m3_verified: row.m3_verified,
          m3_reported: row.m3_reported,
        });
        verifCount++;
      } catch (e) {
        console.warn("[dvt-import] verif #" + row.position + ":", e.message);
      }
    }

    // 3) Upsert improvements
    let improvCount = 0;
    for (const row of parsed.improvements) {
      try {
        await window.melr.dvtImprovementsCrud.upsert(audit.id, row.position, {
          indicator_name: row.indicator_name || null,
          problem: row.problem || null,
          corrective_action: row.corrective_action || null,
          followup_steps: row.followup_steps || null,
          due_date: row.due_date || null,
          responsible: row.responsible || null,
        });
        improvCount++;
      } catch (e) {
        console.warn("[dvt-import] improv #" + row.position + ":", e.message);
      }
    }

    return { verif: verifCount, improv: improvCount, metaPatchKeys: Object.keys(patch).length };
  }

  window.dvtImporter = {
    available: true,
    importDvtFromFile,
    applyImport,
  };
})();