/**
 * Updates NexusExamples.xlsx with missing columns and documentation.
 * Adds: Display Name, Email, Skills, Nexus Notes columns to EID sheet.
 * Adds: Start Date, End Date, Status, Nexus Notes columns to Projects sheet.
 */

import ExcelJS from "exceljs";
import { fileURLToPath } from "url";
import { dirname, join } from "path";

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const EXCEL_PATH = join(__dirname, "../../../samples/NexusExamples.xlsx");

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

function toDisplayName(eid) {
  return eid
    .split(".")
    .map((p) => p.charAt(0).toUpperCase() + p.slice(1))
    .join(" ");
}

function toEmail(eid) {
  return `${eid}@nexus.example`;
}

function computeSkillLabel(chapter, typeOfWork) {
  if (chapter === "Project Management") return "Project Manager | Scrum Master";
  if (chapter === "Digital Content Production") return "Unreal Engine | 3D Lighting | 3D Modeling";
  if (chapter === "Product Data Management") return "Visualization Logic | Quality Assurance";
  if (chapter === "Art Direction") return "Art Direction | 2D Compositing";
  if (chapter === "CGI-Dev" && typeOfWork === "Dev Unreal") return "Unreal Dev | Technical Architect";
  if (chapter === "CGI-Dev" && typeOfWork === "Dev Frontend") return "Frontend Dev | Backend Dev";
  return typeOfWork;
}

function computeNexusEid(eid) {
  // In Nexus the EID stays as firstname.lastname (unique key)
  return eid;
}

// Project date table (matches seed.ts)
const PROJECT_DATES = {
  JLFJFL: ["2024-01-15", "2024-05-31", "COMPLETED"],
  JZTFG:  ["2024-02-01", "2024-09-30", "COMPLETED"],
  UZGHD:  ["2024-03-01", "2024-07-31", "COMPLETED"],
  DSFGHF: ["2024-04-01", "2024-12-31", "COMPLETED"],
  TZUZG:  ["2024-05-15", "2025-01-31", "COMPLETED"],
  KLJLOH: ["2024-06-01", "2024-10-31", "COMPLETED"],
  FGHJJ:  ["2024-07-01", "2025-01-31", "COMPLETED"],
  FFGHG:  ["2024-08-01", "2024-12-31", "COMPLETED"],
  ARGFH:  ["2024-09-01", "2025-03-31", "COMPLETED"],
  ERTGR:  ["2024-10-01", "2025-01-31", "COMPLETED"],
  HFGRT:  ["2024-11-01", "2025-06-30", "COMPLETED"],
  WERTF:  ["2024-12-01", "2025-04-30", "COMPLETED"],
  RDFGH:  ["2025-01-15", "2025-08-31", "ACTIVE"],
  HJZGJ:  ["2025-02-01", "2025-10-31", "ACTIVE"],
  SRTZRT: ["2025-03-01", "2025-09-30", "DRAFT"],
  STTHG:  ["2025-04-01", "2026-01-31", "ACTIVE"],
  EDDGG:  ["2025-05-01", "2025-12-31", "ACTIVE"],
  FGHHGF: ["2025-06-01", "2026-03-31", "ACTIVE"],
  AFDGH:  ["2025-07-01", "2025-12-31", "ACTIVE"],
  HTZUG:  ["2025-08-01", "2026-04-30", "ON_HOLD"],
  MUIUF:  ["2025-09-01", "2026-06-30", "ACTIVE"],
  FGJUO:  ["2025-10-01", "2026-02-28", "ACTIVE"],
  ZUITJZ: ["2025-11-01", "2026-08-31", "ACTIVE"],
  SACVFH: ["2025-12-01", "2026-06-30", "ACTIVE"],
  EWFSFH: ["2026-01-15", "2026-09-30", "ACTIVE"],
  NTZJJ:  ["2026-02-01", "2026-10-31", "ACTIVE"],
  EFGTZ:  ["2026-03-01", "2026-12-31", "ACTIVE"],
  SJUIL:  ["2026-04-01", "2026-11-30", "DRAFT"],
  SAERZ:  ["2026-05-01", "2027-01-31", "DRAFT"],
  HJUOP:  ["2026-06-01", "2027-03-31", "ACTIVE"],
  AERGH:  ["2026-07-01", "2026-12-31", "DRAFT"],
  POUILZ: ["2026-08-01", "2027-02-28", "DRAFT"],
  EWERZ:  ["2026-09-01", "2027-06-30", "DRAFT"],
  HZIOP:  ["2026-10-01", "2027-04-30", "DRAFT"],
  DGHBN:  ["2026-11-01", "2027-05-31", "DRAFT"],
  KUOOL:  ["2026-12-01", "2027-09-30", "ACTIVE"],
};

const HEADER_FILL = {
  type: "pattern",
  pattern: "solid",
  fgColor: { argb: "FF4F46E5" }, // indigo-600
};
const HEADER_FONT = { bold: true, color: { argb: "FFFFFFFF" }, size: 10 };
const DOC_FILL = {
  type: "pattern",
  pattern: "solid",
  fgColor: { argb: "FFF0F9FF" }, // light blue
};
const DOC_FONT = { italic: true, color: { argb: "FF475569" }, size: 9 };
const NEW_FILL = {
  type: "pattern",
  pattern: "solid",
  fgColor: { argb: "FFEEF2FF" }, // indigo-50
};

function styleNewHeader(cell, label) {
  cell.value = label;
  cell.fill = HEADER_FILL;
  cell.font = HEADER_FONT;
  cell.alignment = { wrapText: true, vertical: "middle" };
  cell.border = { bottom: { style: "thin", color: { argb: "FF6366F1" } } };
}

function styleDocCell(cell, value) {
  cell.value = value;
  cell.fill = DOC_FILL;
  cell.font = DOC_FONT;
  cell.alignment = { wrapText: true, vertical: "top" };
}

function styleDataCell(cell, value) {
  cell.value = value;
  cell.fill = NEW_FILL;
  cell.font = { size: 10 };
  cell.alignment = { wrapText: false, vertical: "middle" };
}

// ─── Main ─────────────────────────────────────────────────────────────────────

async function main() {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile(EXCEL_PATH);

  // ─── EID_Informationen ───────────────────────────────────────────────────

  const eidSheet = workbook.getWorksheet("EID_Informationen");
  if (!eidSheet) throw new Error("Sheet EID_Informationen not found");

  // Add a "Documentation" row 2 below headers explaining each column
  // First, let's add new columns to the right: N, O, P, Q
  const eidLastCol = 14; // N

  // Column N: Display Name
  // Column O: Email
  // Column P: Skills (derived)
  // Column Q: Description / Notes for Nexus

  const newEidCols = [
    {
      col: 14, // N
      header: "Display Name\n(auto-generated)",
      doc: "Full display name derived from EID (firstname.lastname → Firstname Lastname). Used as the person's name in Nexus.",
    },
    {
      col: 15, // O
      header: "Email\n(generated)",
      doc: "Generated email: firstname.lastname@nexus.example. Required unique field in Nexus. Replace with real email in production.",
    },
    {
      col: 16, // P
      header: "Skills\n(derived from Chapter)",
      doc: "Skill tags assigned based on Chapter + Type of Work. Format: 'SkillA | SkillB'. Stored as JSON array in Nexus with proficiency 1-5. Senior (LCR >= 118) -> 5, Mid-Senior (LCR >= 95) -> 4, Mid -> 3.",
    },
    {
      col: 17, // Q
      header: "Nexus Notes",
      doc: "How data maps to Nexus:\n- EID = unique key (col A)\n- Chapter = chapter field\n- LCR / UCR -> multiply by 100 for integer cents (EUR85.00 -> 8500)\n- Hours fraction x 8 = daily availability hours\n- Chargeability -> multiply by 100 for % (0.75 -> 75%)\n- Employee type, City, Client Unit -> stored in dynamicFields JSONB",
    },
  ];

  // Add row 1 column headers for new columns
  const eidHeaderRow = eidSheet.getRow(1);
  for (const { col, header } of newEidCols) {
    styleNewHeader(eidSheet.getCell(1, col), header);
  }

  // Add documentation row after header (insert at row 2, shifting data down)
  eidSheet.spliceRows(2, 0, []); // insert empty row
  const eidDocRow = eidSheet.getRow(2);
  eidDocRow.height = 55;
  // Add doc text to new columns
  for (const { col, doc } of newEidCols) {
    styleDocCell(eidSheet.getCell(2, col), doc);
  }
  // Style existing header cells for doc row
  for (let c = 1; c <= 13; c++) {
    const cell = eidSheet.getCell(2, c);
    cell.fill = DOC_FILL;
    cell.font = DOC_FONT;
    cell.alignment = { wrapText: true, vertical: "top" };
  }
  eidSheet.getCell(2, 1).value = "← Column documentation (row 2)";

  // Now fill data rows (row 3 onward = data rows 1-36)
  for (let r = 3; r <= 38; r++) {
    const row = eidSheet.getRow(r);
    const eid = row.getCell(1).value?.toString().trim();
    if (!eid) continue;

    const chapter = row.getCell(2).value?.toString().trim() ?? "";
    const typeOfWork = row.getCell(3).value?.toString().trim() ?? "";
    const lcr = parseFloat(row.getCell(8).value?.toString() ?? "0");

    styleDataCell(eidSheet.getCell(r, 14), toDisplayName(eid));
    styleDataCell(eidSheet.getCell(r, 15), toEmail(eid));
    styleDataCell(eidSheet.getCell(r, 16), computeSkillLabel(chapter, typeOfWork));

    if (r === 3) {
      styleDataCell(eidSheet.getCell(r, 17), "See header row notes →");
    }
  }

  // Set column widths
  eidSheet.getColumn(14).width = 22;
  eidSheet.getColumn(15).width = 34;
  eidSheet.getColumn(16).width = 36;
  eidSheet.getColumn(17).width = 50;

  // Also add doc notes to existing header columns A-M in row 1
  const eidExistingDocs = [
    "Unique identifier. Used as EID in Nexus (no EMP-XXX prefix needed). e.g. steve.rogers",
    "Team / department. Maps to 'chapter' field in Nexus.",
    "Specialization within chapter. Stored in dynamicFields.workType.",
    "Assigned client account. Stored in dynamicFields.clientUnit.",
    "Unit-specific field. Currently unused — can be stored in dynamicFields.",
    "Office city location. Stored in dynamicFields.city.",
    "Employment type: Employee or Freelancer. Stored in dynamicFields.employeeType.",
    "Loaded Cost Rate (LCR) in EUR/h. Multiply × 100 for Nexus cents. e.g. 133.77 → 13377",
    "Unloaded/Utilization Cost Rate (UCR) in EUR/h. Multiply × 100 for cents.",
    "FTE fraction (1.0 = 40h/week, 0.8 = 4 days, 0.5 = 20h/week). Combined with col K for availability JSON.",
    "Available weekdays. 'all' = Mon-Fri. Specific days listed = only those days active at 8h.",
    "Chargeability target as decimal. Multiply × 100 for Nexus % (0.75 → 75%).",
    "(unused)",
  ];
  for (let c = 1; c <= 13; c++) {
    const doc = eidExistingDocs[c - 1];
    if (doc) {
      eidSheet.getCell(2, c).value = doc;
    }
  }

  // ─── Projektinfomartionen ────────────────────────────────────────────────

  const projSheet = workbook.getWorksheet("Projektinfomartionen");
  if (!projSheet) throw new Error("Sheet Projektinfomartionen not found");

  const newProjCols = [
    {
      col: 17, // Q
      header: "Start Date\n(synthesized)",
      doc: "Synthesized project start date (YYYY-MM-DD). Source Excel had no dates — spread across 2024-2027 based on win probability and project order.",
    },
    {
      col: 18, // R
      header: "End Date\n(synthesized)",
      doc: "Synthesized end date (YYYY-MM-DD). Duration based on resource costs planned and person hours. Ranges from 3-12 months.",
    },
    {
      col: 19, // S
      header: "Status\n(derived)",
      doc: "Nexus status derived from 'is ordered' + win probability + date:\n• COMPLETED: ordered + 100% + past dates\n• ACTIVE: ordered + 100% + current/future\n• ON_HOLD: ordered but paused\n• DRAFT: not ordered or low win probability",
    },
    {
      col: 20, // T
      header: "Nexus Notes",
      doc: "How data maps to Nexus:\n• Col C (short code) → shortCode (unique key)\n• Col B → name\n• BD/CH/UN → OrderType: BD / CHARGEABLE / INTERNAL\n• Internal/External → allocationType: INT / EXT\n• Resource Costs (col I) × 100 = budgetCents in Nexus\n• Col H (chargability %) → stored in dynamicFields.chargeabilityPercent\n• Col J (person hours) → stored in dynamicFields.personHoursSold\n• Col O (classification) → stored in dynamicFields.classification",
    },
  ];

  // Add header row columns
  const projHeaderRow = projSheet.getRow(1);
  for (const { col, header } of newProjCols) {
    styleNewHeader(projSheet.getCell(1, col), header);
  }

  // Insert documentation row 2
  projSheet.spliceRows(2, 0, []);
  const projDocRow = projSheet.getRow(2);
  projDocRow.height = 55;
  for (const { col, doc } of newProjCols) {
    styleDocCell(projSheet.getCell(2, col), doc);
  }
  projSheet.getCell(2, 1).value = "← Column documentation (row 2)";
  for (let c = 1; c <= 16; c++) {
    const cell = projSheet.getCell(2, c);
    cell.fill = DOC_FILL;
    cell.font = DOC_FONT;
    cell.alignment = { wrapText: true, vertical: "top" };
  }

  const projExistingDocs = [
    "Client Unit tag. e.g. [DAI]=Daimler, [PAG]=Porsche AG, [BMW], [JLR]=Jaguar Land Rover. Stored in dynamicFields.clientUnit.",
    "Full project name → maps to Nexus 'name' field.",
    "Short internal project code (5-6 chars) → maps to Nexus 'shortCode' (unique key). e.g. JLFJFL",
    "'yes'/'no' — whether the project is formally ordered. Drives status: yes+100% → ACTIVE/COMPLETED.",
    "Order type: BD=Business Development, CH=Chargeable, UN=Internal/Unordered. Maps to Nexus OrderType.",
    "Win probability 0-100. Used in Nexus 'winProbability' field for pipeline forecasting.",
    "Allocation type: Internal → INT, External → EXT. Maps to Nexus 'allocationType' field.",
    "Chargeability % as decimal. Stored in dynamicFields.chargeabilityPercent.",
    "Planned resource cost in EUR. Multiply × 100 for Nexus budgetCents. e.g. 78799 → 7879900 cents.",
    "Person hours planned/sold. Stored in dynamicFields.personHoursSold for budget tracking.",
    "Team staffing (empty in source). Would list assigned EIDs. Handled by Allocations in Nexus.",
    "Day project sold (empty in source). Could be stored as dynamicFields.dateSold.",
    "Project start date (empty in source → synthesized in col Q). Maps to Nexus 'startDate'.",
    "Project end date (empty in source → synthesized in col R). Maps to Nexus 'endDate'.",
    "Confidentiality: Confidential / Not Confidential. Stored in dynamicFields.classification.",
    "Responsible EID / Owner (empty in source). Would map to a PM allocation in Nexus.",
  ];
  for (let c = 1; c <= 16; c++) {
    const doc = projExistingDocs[c - 1];
    if (doc) projSheet.getCell(2, c).value = doc;
  }

  // Fill data rows
  for (let r = 3; r <= 38; r++) {
    const row = projSheet.getRow(r);
    const shortCode = row.getCell(3).value?.toString().trim();
    if (!shortCode) continue;

    const dates = PROJECT_DATES[shortCode];
    if (dates) {
      styleDataCell(projSheet.getCell(r, 17), dates[0]);
      styleDataCell(projSheet.getCell(r, 18), dates[1]);
      styleDataCell(projSheet.getCell(r, 19), dates[2]);
    }
    if (r === 3) {
      styleDataCell(projSheet.getCell(r, 20), "See header row notes →");
    }
  }

  projSheet.getColumn(17).width = 18;
  projSheet.getColumn(18).width = 18;
  projSheet.getColumn(19).width = 16;
  projSheet.getColumn(20).width = 50;

  // ─── Add a "Nexus Data Model" sheet ──────────────────────────────────

  let modelSheet = workbook.getWorksheet("Nexus Data Model");
  if (!modelSheet) {
    modelSheet = workbook.addWorksheet("Nexus Data Model");
  }

  const modelData = [
    ["Nexus Data Model — Field Reference", "", "", "", ""],
    ["", "", "", "", ""],
    ["RESOURCE FIELDS", "", "", "", ""],
    ["Field", "Type", "Required", "Example", "Description"],
    ["eid", "String (unique)", "Yes", "steve.rogers", "Employee identifier. Must be unique. Shown in UI as EID badge."],
    ["displayName", "String", "Yes", "Steve Rogers", "Full display name shown in all views."],
    ["email", "String (unique)", "Yes", "steve.rogers@company.com", "Unique email address. Used for login if resource is also a user."],
    ["chapter", "String", "No", "Project Management", "Team or department. Used for grouping in dashboard and filters."],
    ["lcrCents", "Integer (cents)", "Yes", "13377", "Loaded Cost Rate in euro-cents (€/h × 100). e.g. €133.77/h → 13377"],
    ["ucrCents", "Integer (cents)", "Yes", "7465", "Unloaded/Utilization Cost Rate in euro-cents. e.g. €74.65/h → 7465"],
    ["currency", "String", "Yes", "EUR", "Currency code for LCR/UCR. Default EUR."],
    ["chargeabilityTarget", "Float (0-100)", "Yes", "75", "Target chargeability percentage. 75 = 75% of available hours should be chargeable."],
    ["availability", "JSON", "Yes", "{monday:8, ...}", "Available hours per weekday. Partial days for part-time. e.g. 4h = half day."],
    ["skills", "JSON Array", "No", "[{skill:'Unreal Engine', proficiency:4}]", "Skill entries with proficiency 1(Junior)–5(Expert). Optional category and yearsExperience."],
    ["isActive", "Boolean", "Yes", "true", "Active resources appear in planning. Deactivated resources are hidden but kept in history."],
    ["dynamicFields", "JSON", "No", "{city:'Stuttgart', ...}", "Additional fields defined by Blueprint (e.g. city, clientUnit, workType, employeeType)."],
    ["", "", "", "", ""],
    ["PROJECT FIELDS", "", "", "", ""],
    ["Field", "Type", "Required", "Example", "Description"],
    ["shortCode", "String (unique)", "Yes", "STTHG", "Short internal project code. Unique key. Shown as monospace badge in UI."],
    ["name", "String", "Yes", "Spider-Man: Homecoming (2017)", "Full project name."],
    ["orderType", "Enum", "Yes", "CHARGEABLE", "BD = Business Development, CHARGEABLE = billable client work, INTERNAL = internal, OVERHEAD = overhead."],
    ["allocationType", "Enum", "Yes", "EXT", "INT = Internal allocation (internal team), EXT = External (client-facing)."],
    ["winProbability", "Integer (0-100)", "Yes", "100", "Probability this project proceeds. Used in pipeline reports. 100 = confirmed."],
    ["budgetCents", "Integer (cents)", "Yes", "6789900", "Total project budget in euro-cents. e.g. €67,899 → 6789900. Used for utilization tracking."],
    ["startDate", "Date", "Yes", "2025-04-01", "Project start date (ISO format)."],
    ["endDate", "Date", "Yes", "2026-01-31", "Project end date (ISO format)."],
    ["status", "Enum", "Yes", "ACTIVE", "DRAFT / ACTIVE / ON_HOLD / COMPLETED / CANCELLED. Drives timeline filtering."],
    ["staffingReqs", "JSON Array", "No", "[{role:'3D Artist', fteCount:2}]", "Staffing requirements. Used in Demand View widget to show gaps vs actual allocations."],
    ["dynamicFields", "JSON", "No", "{clientUnit:'[DAI]', ...}", "Blueprint fields: clientUnit, personHoursSold, classification, etc."],
    ["", "", "", "", ""],
    ["ALLOCATION FIELDS", "", "", "", ""],
    ["Field", "Type", "Required", "Example", "Description"],
    ["resourceId", "String (FK)", "Yes", "(cuid)", "Reference to Resource.id"],
    ["projectId", "String (FK)", "Yes", "(cuid)", "Reference to Project.id"],
    ["startDate", "Date", "Yes", "2025-04-01", "Start of allocation (often same as project start)."],
    ["endDate", "Date", "Yes", "2026-01-31", "End of allocation (can be partial project duration)."],
    ["hoursPerDay", "Float", "Yes", "8", "Hours per working day. 8 = full day. Can exceed 8 (overtime, shown in amber)."],
    ["percentage", "Float (0-100)", "Yes", "100", "Allocation percentage (100 = full-time, 50 = half-time)."],
    ["role", "String", "Yes", "Project Manager", "Role on this project (free text)."],
    ["dailyCostCents", "Integer (cents)", "Yes", "106960", "LCR × hoursPerDay × 100. Computed automatically on create."],
    ["status", "Enum", "Yes", "ACTIVE", "PROPOSED / CONFIRMED / ACTIVE / COMPLETED / CANCELLED"],
  ];

  modelSheet.addRows(modelData);

  // Style the model sheet
  modelSheet.getColumn(1).width = 28;
  modelSheet.getColumn(2).width = 22;
  modelSheet.getColumn(3).width = 12;
  modelSheet.getColumn(4).width = 35;
  modelSheet.getColumn(5).width = 72;

  // Style title row
  const titleCell = modelSheet.getCell(1, 1);
  titleCell.font = { bold: true, size: 14, color: { argb: "FF4F46E5" } };
  titleCell.value = "Nexus Data Model — Field Reference";

  // Style section headers and field headers
  const sectionRows = [3, 17, 31];
  const fieldHeaderRows = [4, 18, 32];

  for (const r of sectionRows) {
    for (let c = 1; c <= 5; c++) {
      const cell = modelSheet.getCell(r, c);
      cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF4F46E5" } };
      cell.font = { bold: true, color: { argb: "FFFFFFFF" }, size: 11 };
    }
  }

  for (const r of fieldHeaderRows) {
    for (let c = 1; c <= 5; c++) {
      const cell = modelSheet.getCell(r, c);
      cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFE0E7FF" } };
      cell.font = { bold: true, color: { argb: "FF3730A3" }, size: 10 };
    }
  }

  // ─── Save ────────────────────────────────────────────────────────────────

  await workbook.xlsx.writeFile(EXCEL_PATH);
  console.log(`✅ Excel updated: ${EXCEL_PATH}`);
  console.log("  - EID_Informationen: added Display Name, Email, Skills, Notes columns");
  console.log("  - Projektinfomartionen: added Start Date, End Date, Status, Notes columns");
  console.log("  - Added new sheet: 'Nexus Data Model' (field reference)");
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});