Driverscore Card

// ==========================================
// DEELBARE DEMOVERSIE SCORE ENGINE
//
// Privacy: dit bestand bevat geen live spreadsheet-ID's, persoonsgegevens,
// interne tabnamen, hubcodes of opslaglocaties. De scorelogica is ongewijzigd.
// De placeholders hieronder zijn bewust niet gekoppeld aan productiegegevens.
// ==========================================

const IL20_EXTERNAL_SPREADSHEET_ID = 'VUL_HIER_EEN_EIGEN_SPREADSHEET_ID_IN';

const IL20_SOURCE_SHEET_NAME = 'DRIVER_KPI_DATA';
const IL20_ROUTES_DATA_SHEET_NAME = 'ROUTES_DATA';
const IL20_INACTIVE_SHEET_NAME = 'INACTIVE_DRIVERS';
const IL20_SAMSARA_SHEET_NAME = 'SAFETY_SCORES';

const IL20_TARGET_HUB = 'HUB_X';

const IL20_HUBS = {
  'HUB_X': 'Voorbeeldhub',
  'NL_HUB_1': 'Nederlandse voorbeeldhub',
  'BE_HUB_1': 'Belgische voorbeeldhub'
};

const IL20_DAMAGE_POINTS = {
  one_sided: 15,
  multi_sided: 30,
  immobilization: 40
};

const IL20_WEIGHTS = {
  damage: 0.30,
  fines: 0.10,
  driving_behavior: 0.60
};

// Minimaal 2 actieve weken en per actieve week minimaal 2 routes
const IL20_MIN_ACTIVE_WEEKS = 2;
const IL20_MIN_ROUTES_PER_ACTIVE_WEEK = 2;

const IL20_WEEKS_TO_ANALYZE = 4;
const IL20_FINES_WEEKS_TO_ANALYZE = 4;

const IL20_REQUIRE_SAMSARA_SCORE = true;

const IL20_KPI_BADGE_INDEX = 1;

const IL20_SAMSARA_BADGE_INDEX = 0;
const IL20_SAMSARA_SCORE_INDEX = 3;

// Voorbeeld: de kolom met het aantal 5-sterrenreacties = index 20
const IL20_FIVE_STAR_COUNT_INDEX = 20;

// 5-star bonus:
// five_star_ratio = five_star_comments / routes
// routes < 4 = 0 bonus
// five_star_comments <= 0 = 0 bonus
// five_star_ratio > 1 = +8
// five_star_ratio >= 0.5 en <= 1 = +6
// five_star_ratio >= 0.1 = +3
// five_star_ratio > 0 = +1
const IL20_FIVE_STAR_MIN_ROUTES = 4;
const IL20_FIVE_STAR_MAX_BONUS = 8;

// ==========================================
// HOOFD FUNCTIE
// ==========================================

function il20_generateHubBattleRankings() {
  try {
    const ss = SpreadsheetApp.getActive();
    const sheet = ss.getSheetByName(IL20_SOURCE_SHEET_NAME);

    if (!sheet) {
      throw new Error('Tabblad "' + IL20_SOURCE_SHEET_NAME + '" niet gevonden.');
    }

    const data = sheet.getDataRange().getValues();

    if (data.length < 2) {
      throw new Error('Geen data gevonden in tabblad "' + IL20_SOURCE_SHEET_NAME + '".');
    }

    const headers = data[0];
    const rows = data.slice(1);

    const allWeeks = il20_getAllWeeks(rows, headers);
    const lastPeriodWeeks = allWeeks.slice(-IL20_WEEKS_TO_ANALYZE);
    const finesWeeks = allWeeks.slice(-IL20_FINES_WEEKS_TO_ANALYZE);
    const lifetimeWeeks = allWeeks.filter(week => week >= '2026-W01');

    if (lastPeriodWeeks.length === 0) {
      throw new Error('Geen weken gevonden in brondata.');
    }

    console.log(`IL2.0 - Scoreweken: ${lastPeriodWeeks.join(', ')}`);
    console.log(`IL2.0 - Fines weken: ${finesWeeks.join(', ')}`);

    const inactiveDriverIds = il20_getInactiveDriverIds();
    const samsaraScoreMap = il20_getSamsaraSafetyScoreMap();
    const fiveStarCountMap = il20_getFiveStarCountMap(lastPeriodWeeks);

    const aggregatedDataLastPeriod = il20_aggregateDriverData(
      rows,
      headers,
      lastPeriodWeeks,
      finesWeeks,
      samsaraScoreMap,
      fiveStarCountMap
    );

    let activeDriversOnly = aggregatedDataLastPeriod.filter(driver => {
      return (
        !inactiveDriverIds.includes(il20_normalizeBadgeNumber(driver.badge_number)) &&
        il20_isDriverEligible(driver)
      );
    });

    if (IL20_REQUIRE_SAMSARA_SCORE) {
      activeDriversOnly = activeDriversOnly.filter(driver => driver.samsara_safety_score !== null);
    }

    const skippedDrivers = aggregatedDataLastPeriod.length - activeDriversOnly.length;
    console.log(`IL2.0 - Chauffeurs genegeerd: ${skippedDrivers}`);

    const lifetimeData = il20_calculateLifetimeStats(rows, headers, lifetimeWeeks);

    const driverScores = il20_calculateDriverScores(
      activeDriversOnly,
      aggregatedDataLastPeriod,
      lifetimeData
    );

    const rankChanges = il20_calculateRankChanges(
      rows,
      headers,
      allWeeks,
      lastPeriodWeeks,
      finesWeeks,
      lifetimeData,
      inactiveDriverIds,
      samsaraScoreMap
    );

    const hubRankings = il20_createHubRankings(driverScores, rankChanges);
    const stats = il20_createHubRankings._lastHubStats || [];

    const weeklyPenaltyPerformance = il20_computeWeeklyPenaltyPerformance(
      rows,
      headers,
      lastPeriodWeeks,
      aggregatedDataLastPeriod,
      samsaraScoreMap
    );

    const jsonData = {
      period: `${lastPeriodWeeks[0]} to ${lastPeriodWeeks[lastPeriodWeeks.length - 1]}`,
      weeks_analyzed: lastPeriodWeeks,
      lifetime_period_start: lifetimeWeeks[0] || null,
      generated_at: new Date().toISOString(),
      target_hub: IL20_TARGET_HUB,
      hubs: hubRankings,
      stats: stats,
      weekly_penalty_performance: weeklyPenaltyPerformance,
      total_drivers: driverScores.length,
      scoring_config: {
        target_hub: IL20_TARGET_HUB,
        minimum_routes: IL20_MIN_ACTIVE_WEEKS * IL20_MIN_ROUTES_PER_ACTIVE_WEEK,
        minimum_active_weeks: IL20_MIN_ACTIVE_WEEKS,
        minimum_routes_per_active_week: IL20_MIN_ROUTES_PER_ACTIVE_WEEK,
        eligibility_calculation: 'weeks_active >= 2 AND every active week has at least 2 routes',
        weeks_analyzed: IL20_WEEKS_TO_ANALYZE,
        weights: IL20_WEIGHTS,
        require_samsara_score: IL20_REQUIRE_SAMSARA_SCORE,
        source_sheet: IL20_SOURCE_SHEET_NAME,
        routes_data_sheet: IL20_ROUTES_DATA_SHEET_NAME,
        samsara_sheet: IL20_SAMSARA_SHEET_NAME,
        badge_match: 'Unieke chauffeurreferentie uit SAFETY_SCORES wordt gekoppeld aan DRIVER_KPI_DATA',
        calculation_method: 'three_penalty_tables_with_five_star_bonus',
        removed_from_score: ['speeding', 'g-force'],
        note: 'JSON output toont raw_points, weighted_raw_points, weighted_raw_penalty_per_route en five_star_bonus_points.',
        five_star_bonus: {
          source_sheet: IL20_ROUTES_DATA_SHEET_NAME,
          source_column: 'U',
          source_column_index: IL20_FIVE_STAR_COUNT_INDEX,
          calculation: 'five_star_ratio = five_star_comments / routes',
          min_routes: IL20_FIVE_STAR_MIN_ROUTES,
          max_bonus_points: IL20_FIVE_STAR_MAX_BONUS,
          score_cap: 100,
          ranges: {
            no_five_star_comments: 0,
            five_star_ratio_above_0_to_below_0_1: 1,
            five_star_ratio_0_1_to_below_0_5: 3,
            five_star_ratio_0_5_to_1: 6,
            five_star_ratio_above_1: 8
          }
        }
      }
    };

    const lastWeek = lastPeriodWeeks[lastPeriodWeeks.length - 1];
    const weekNumber = String(lastWeek).split('-W')[1] || 'unknown';

    const filename = `HUB-driverscore-demo-W${weekNumber}.json`;
    const file = il20_saveJsonToDrive(jsonData, filename);

    return {
      jsonData,
      fileId: file.getId(),
      fileUrl: file.getUrl()
    };

  } catch (error) {
    console.error('IL2.0 - Fout bij genereren van de ranking:', error);
    throw error;
  }
}

// ==========================================
// SAMSARA DATA
// ==========================================

function il20_getSamsaraSafetyScoreMap() {
  try {
    const ss = SpreadsheetApp.getActive();
    const sheet = ss.getSheetByName(IL20_SAMSARA_SHEET_NAME);

    if (!sheet) {
      console.warn(`IL2.0 - Samsara-tabblad "${IL20_SAMSARA_SHEET_NAME}" niet gevonden.`);
      return {};
    }

    const data = sheet.getDataRange().getValues();

    if (data.length < 2) {
      console.warn('IL2.0 - Samsara-tabblad bevat geen data.');
      return {};
    }

    const scoreMap = {};

    for (let i = 1; i < data.length; i++) {
      const badge = il20_normalizeBadgeNumber(data[i][IL20_SAMSARA_BADGE_INDEX]);
      const score = il20_normalizeScore(data[i][IL20_SAMSARA_SCORE_INDEX]);

      if (!badge || score === null) continue;

      scoreMap[badge] = score;
    }

    console.log(`IL2.0 - Samsara Safety Scores gevonden: ${Object.keys(scoreMap).length}`);
    return scoreMap;

  } catch (error) {
    console.error('IL2.0 - Fout bij ophalen Samsara Safety Scores:', error);
    return {};
  }
}

// ==========================================
// DATA OPHALEN
// ==========================================

function il20_getInactiveDriverIds() {
  try {
    const externalSs = SpreadsheetApp.openById(IL20_EXTERNAL_SPREADSHEET_ID);
    let sheet = externalSs.getSheetByName(IL20_INACTIVE_SHEET_NAME);

    if (!sheet) {
      const sheets = externalSs.getSheets();

      if (sheets.length >= 7) {
        sheet = sheets[6];
      } else {
        console.warn("IL2.0 - Tabblad 'NOT ACTIVE' of 7e tabblad niet gevonden.");
        return [];
      }
    }

    const data = sheet.getRange('C:C').getValues();
    const inactiveIds = [];

    for (let i = 1; i < data.length; i++) {
      const id = il20_normalizeBadgeNumber(data[i][0]);

      if (id !== '') {
        inactiveIds.push(id);
      }
    }

    console.log(`IL2.0 - Inactieve drivers gevonden: ${inactiveIds.length}`);
    return inactiveIds;

  } catch (error) {
    console.error('IL2.0 - Fout bij openen externe sheet: ' + error.message);
    return [];
  }
}

function il20_getFiveStarCountMap(scoreWeeks) {
  try {
    const ss = SpreadsheetApp.getActive();
    const sheet = ss.getSheetByName(IL20_ROUTES_DATA_SHEET_NAME);

    if (!sheet) {
      console.warn(`IL2.0 - Tabblad "${IL20_ROUTES_DATA_SHEET_NAME}" niet gevonden.`);
      return {};
    }

    const data = sheet.getDataRange().getValues();

    if (data.length < 2) {
      console.warn(`IL2.0 - Tabblad "${IL20_ROUTES_DATA_SHEET_NAME}" bevat geen data.`);
      return {};
    }

    const headers = data[0];
    const rows = data.slice(1);

    const weekIndex = il20_getColIndex(headers, [
      'hellofresh_week',
      'hf_week',
      'week'
    ]);

    if (weekIndex === -1) {
      console.warn(`IL2.0 - Geen weekkolom gevonden in "${IL20_ROUTES_DATA_SHEET_NAME}".`);
      return {};
    }

    let hubIndex = il20_getColIndex(headers, [
      'hub_code',
      'hub',
      'hubcode'
    ]);

    if (hubIndex === -1) {
      hubIndex = 2;
    }

    let badgeIndex = il20_getColIndex(headers, [
      'badge_number',
      'driver_id',
      'driverid',
      'hf_driver_id',
      'hellofresh_driver_id'
    ]);

    if (badgeIndex === -1) {
      badgeIndex = IL20_KPI_BADGE_INDEX;
    }

    const scoreWeekSet = new Set(scoreWeeks || []);
    const fiveStarCountMap = {};

    rows.forEach(row => {
      const week = row[weekIndex];

      if (!week || !scoreWeekSet.has(week)) {
        return;
      }

      const hubCode = il20_mapHubCode(row[hubIndex] || 'UNKNOWN');

      if (hubCode !== IL20_TARGET_HUB) {
        return;
      }

      const badge = il20_normalizeBadgeNumber(row[badgeIndex]);

      if (!badge) {
        return;
      }

      const fiveStarCount = Number(row[IL20_FIVE_STAR_COUNT_INDEX] || 0);

      if (!fiveStarCountMap[badge]) {
        fiveStarCountMap[badge] = 0;
      }

      fiveStarCountMap[badge] += fiveStarCount;
    });

    console.log(`IL2.0 - 5-sterren data gevonden voor ${Object.keys(fiveStarCountMap).length} chauffeurs.`);
    return fiveStarCountMap;

  } catch (error) {
    console.error('IL2.0 - Fout bij ophalen 5-sterren data:', error);
    return {};
  }
}

function il20_getAllWeeks(rows, headers) {
  const weekIndex = il20_getColIndex(headers, ['hellofresh_week']);

  if (weekIndex === -1) {
    return [];
  }

  const allWeeks = [...new Set(
    rows
      .map(row => row[weekIndex])
      .filter(week => week)
  )];

  allWeeks.sort();

  return allWeeks;
}

function il20_getColIndex(headers, possibleNames) {
  const cleanHeaders = headers.map(header => {
    return String(header).toLowerCase().replace(/[^a-z0-9]/g, '');
  });

  for (const name of possibleNames) {
    const cleanName = String(name).toLowerCase().replace(/[^a-z0-9]/g, '');
    const index = cleanHeaders.indexOf(cleanName);

    if (index !== -1) {
      return index;
    }
  }

  return -1;
}

function il20_mapHubCode(rawHubCode) {
  const hubMappings = {
    OUDE_HUBCODE: 'HUB_X'
  };

  const upperCode = String(rawHubCode || 'UNKNOWN')
    .toUpperCase()
    .trim();

  return hubMappings[upperCode] || upperCode;
}

function il20_normalizeBadgeNumber(value) {
  return String(value || '').trim().toUpperCase();
}

function il20_normalizeScore(value) {
  if (value === null || value === undefined || value === '') {
    return null;
  }

  const score = Number(value);

  if (isNaN(score)) {
    return null;
  }

  return Math.max(0, Math.min(100, score));
}

function il20_round(value) {
  return Math.round(Number(value || 0) * 100) / 100;
}

// NIEUW: eligibility-controle
function il20_isDriverEligible(driver) {
  const weeksActive = Number(driver?.weeks_active || 0);

  const routesPerActiveWeek = Array.isArray(driver?.routes_per_active_week)
    ? driver.routes_per_active_week.map(routes => Number(routes || 0))
    : [];

  return (
    weeksActive >= IL20_MIN_ACTIVE_WEEKS &&
    routesPerActiveWeek.length === weeksActive &&
    routesPerActiveWeek.every(routes => routes >= IL20_MIN_ROUTES_PER_ACTIVE_WEEK)
  );
}

// ==========================================
// AGGREGATIE
// ==========================================

function il20_aggregateDriverData(
  rows,
  headers,
  scoreWeeks,
  finesWeeks,
  samsaraScoreMap,
  fiveStarCountMap = {}
) {
  const weekIndex = il20_getColIndex(headers, ['hellofresh_week']);
  const badgeIndex = IL20_KPI_BADGE_INDEX;

  const routesIndex = il20_getColIndex(headers, ['number_of_routes']);
  const oneSidedIndex = il20_getColIndex(headers, ['number_of_one_sided_accidents']);
  const multiSidedIndex = il20_getColIndex(headers, ['number_of_multi_sided_accidents']);
  const immobilizationIndex = il20_getColIndex(headers, ['number_of_vehicle_immobilizations']);
  const finesIndex = il20_getColIndex(headers, ['number_of_fines']);
  const finesAmountIndex = il20_getColIndex(headers, ['fines_amount']);

  let hubIndex = il20_getColIndex(headers, ['hub_code', 'hub', 'hubcode']);

  if (hubIndex === -1) {
    hubIndex = 2;
  }

  const scoreWeekSet = new Set(scoreWeeks || []);
  const finesWeekSet = new Set(finesWeeks || []);

  const allRelevantWeeks = new Set([
    ...(scoreWeeks || []),
    ...(finesWeeks || [])
  ]);

  const driverMap = {};

  for (const row of rows) {
    const week = weekIndex !== -1 ? row[weekIndex] : null;

    if (!week || !allRelevantWeeks.has(week)) {
      continue;
    }

    const hubCode = il20_mapHubCode(row[hubIndex] || 'UNKNOWN');

    if (hubCode !== IL20_TARGET_HUB) {
      continue;
    }

    const badge = il20_normalizeBadgeNumber(row[badgeIndex]);

    if (!badge) {
      continue;
    }

    if (!driverMap[badge]) {
      driverMap[badge] = {
        badge_number: badge,
        total_routes: 0,
        total_fines_routes: 0,
        total_one_sided: 0,
        total_multi_sided: 0,
        total_immobilization: 0,
        total_fines: 0,
        total_fines_amount: 0,
        weeks_active_set: new Set(),
        routes_by_week: {},
        hub_counts: {}
      };
    }

    const driver = driverMap[badge];
    const routes = routesIndex !== -1 ? Number(row[routesIndex] || 0) : 0;

    if (scoreWeekSet.has(week)) {
      driver.total_routes += routes;
      driver.total_one_sided += oneSidedIndex !== -1 ? Number(row[oneSidedIndex] || 0) : 0;
      driver.total_multi_sided += multiSidedIndex !== -1 ? Number(row[multiSidedIndex] || 0) : 0;
      driver.total_immobilization += immobilizationIndex !== -1 ? Number(row[immobilizationIndex] || 0) : 0;

      if (routes > 0) {
        driver.weeks_active_set.add(week);
        driver.routes_by_week[week] = (driver.routes_by_week[week] || 0) + routes;
      }
    }

    if (finesWeekSet.has(week)) {
      driver.total_fines_routes += routes;
      driver.total_fines += finesIndex !== -1 ? Number(row[finesIndex] || 0) : 0;
      driver.total_fines_amount += finesAmountIndex !== -1 ? Number(row[finesAmountIndex] || 0) : 0;
    }

    driver.hub_counts[hubCode] = (driver.hub_counts[hubCode] || 0) + 1;
  }

  return Object.values(driverMap)
    .filter(driver => driver.total_routes > 0)
    .map(driver => {
      const hub = Object.entries(driver.hub_counts)
        .sort((a, b) => b[1] - a[1])[0]?.[0] || IL20_TARGET_HUB;

      const badge = il20_normalizeBadgeNumber(driver.badge_number);
      let samsaraSafetyScore = null;

      if (
        samsaraScoreMap &&
        Object.prototype.hasOwnProperty.call(samsaraScoreMap, badge)
      ) {
        samsaraSafetyScore = il20_normalizeScore(samsaraScoreMap[badge]);
      }

      return {
        badge_number: badge,
        total_routes: driver.total_routes,
        total_fines_routes: driver.total_fines_routes,
        total_one_sided: driver.total_one_sided,
        total_multi_sided: driver.total_multi_sided,
        total_immobilization: driver.total_immobilization,
        total_fines: driver.total_fines,
        total_fines_amount: driver.total_fines_amount,
        five_star_count: Number(fiveStarCountMap[badge] || 0),
        samsara_safety_score: samsaraSafetyScore,
        weeks_active: driver.weeks_active_set.size,
        routes_per_active_week: Object.values(driver.routes_by_week),
        hub
      };
    });
}

// ==========================================
// LIFETIME STATS
// ==========================================

function il20_calculateLifetimeStats(rows, headers, weeks) {
  const weekIndex = il20_getColIndex(headers, ['hellofresh_week']);
  const badgeIndex = IL20_KPI_BADGE_INDEX;

  const oneSidedIndex = il20_getColIndex(headers, ['number_of_one_sided_accidents']);
  const multiSidedIndex = il20_getColIndex(headers, ['number_of_multi_sided_accidents']);
  const immobilizationIndex = il20_getColIndex(headers, ['number_of_vehicle_immobilizations']);
  const finesAmountIndex = il20_getColIndex(headers, ['fines_amount']);

  let hubIndex = il20_getColIndex(headers, ['hub_code', 'hub', 'hubcode']);

  if (hubIndex === -1) {
    hubIndex = 2;
  }

  const lifetimeMap = {};

  rows.forEach(row => {
    const week = weekIndex !== -1 ? row[weekIndex] : null;

    if (!week || !weeks.includes(week)) {
      return;
    }

    const hubCode = il20_mapHubCode(row[hubIndex] || 'UNKNOWN');

    if (hubCode !== IL20_TARGET_HUB) {
      return;
    }

    const badge = il20_normalizeBadgeNumber(row[badgeIndex]);

    if (!badge) {
      return;
    }

    if (!lifetimeMap[badge]) {
      lifetimeMap[badge] = {
        accidents_1A_one_sided: 0,
        accidents_1B_multi_sided: 0,
        accidents_1C_immobilization: 0,
        fines_amount: 0
      };
    }

    const stats = lifetimeMap[badge];

    stats.accidents_1A_one_sided += oneSidedIndex !== -1 ? Number(row[oneSidedIndex] || 0) : 0;
    stats.accidents_1B_multi_sided += multiSidedIndex !== -1 ? Number(row[multiSidedIndex] || 0) : 0;
    stats.accidents_1C_immobilization += immobilizationIndex !== -1 ? Number(row[immobilizationIndex] || 0) : 0;
    stats.fines_amount += finesAmountIndex !== -1 ? Number(row[finesAmountIndex] || 0) : 0;
  });

  return lifetimeMap;
}

// ==========================================
// SCOREBEREKENING
// ==========================================

function il20_calculateDriverScores(
  driverDataForScoring,
  aggregatedDataForHubAvg,
  lifetimeData
) {
  const scores = [];
  const avgRoutesPerHub = il20_calculateAverageRoutesPerHub(aggregatedDataForHubAvg);

  for (const driver of driverDataForScoring) {
    const badge = il20_normalizeBadgeNumber(driver.badge_number);
    const routes = Number(driver.total_routes || 0);
    const finesRoutes = Number(driver.total_fines_routes || 0);
    const hub = driver.hub || IL20_TARGET_HUB;
    const hubAvgRoutes = avgRoutesPerHub[hub] || 1;

    const damageRawPoints =
      (driver.total_one_sided * IL20_DAMAGE_POINTS.one_sided) +
      (driver.total_multi_sided * IL20_DAMAGE_POINTS.multi_sided) +
      (driver.total_immobilization * IL20_DAMAGE_POINTS.immobilization);

    const finesResult = il20_calculateFinesPoints(driver.total_fines_amount, hub);
    const finesRawPoints = finesResult.points;
    const finesSurchargeRaw = finesResult.surcharge;

    const drivingBehaviorRawPenalty = il20_calculateDrivingBehaviorPenalty(
      driver.samsara_safety_score
    );

    const totalRawPenalty =
      damageRawPoints +
      finesRawPoints +
      finesSurchargeRaw +
      drivingBehaviorRawPenalty;

    const damageRawPerRoute = routes > 0 ? damageRawPoints / routes : 0;
    const finesRawPerRoute = finesRoutes > 0 ? finesRawPoints / finesRoutes : 0;
    const finesSurchargeRawPerRoute = finesRoutes > 0 ? finesSurchargeRaw / finesRoutes : 0;
    const drivingBehaviorRawPerRoute = routes > 0 ? drivingBehaviorRawPenalty / routes : 0;
    const totalRawPenaltyPerRoute = routes > 0 ? totalRawPenalty / routes : 0;

    const damagePenalty = damageRawPoints * IL20_WEIGHTS.damage;
    const finesPenalty = finesRawPoints * IL20_WEIGHTS.fines;
    const finesSurcharge = finesSurchargeRaw * IL20_WEIGHTS.fines;
    const drivingBehaviorPenalty =
      drivingBehaviorRawPenalty * IL20_WEIGHTS.driving_behavior;

    const totalPenalty =
      damagePenalty +
      finesPenalty +
      finesSurcharge +
      drivingBehaviorPenalty;

    const totalPenaltyPerRoute = routes > 0 ? totalPenalty / routes : 0;
    const scaledPenaltyPer10Routes = totalPenaltyPerRoute * 10;

    const scoreBeforeBonus = Math.max(
      0,
      Math.min(100, 100 - scaledPenaltyPer10Routes)
    );

    const fiveStarBonusResult = il20_calculateFiveStarBonus(
      driver.five_star_count,
      routes
    );

    const finalScore = Math.max(
      0,
      scoreBeforeBonus + fiveStarBonusResult.bonus_points
    );

    const lifetimeStats = lifetimeData[badge] || {
      accidents_1A_one_sided: 0,
      accidents_1B_multi_sided: 0,
      accidents_1C_immobilization: 0,
      fines_amount: 0
    };

    scores.push({
      badge_number: badge,
      hub,
      routes,
      weeks_active: driver.weeks_active,
      hub_avg_routes: hubAvgRoutes,

      damage_points: damagePenalty,
      fines_points: finesPenalty,
      driving_behavior_points: drivingBehaviorPenalty,
      fines_surcharge: finesSurcharge,

      total_penalty: totalPenalty,
      total_penalty_per_route: totalPenaltyPerRoute,
      scaled_penalty_per_10_routes: scaledPenaltyPer10Routes,
      score_before_bonus: scoreBeforeBonus,
      five_star_bonus_points: fiveStarBonusResult.bonus_points,
      final_score: finalScore,

      five_star_bonus: fiveStarBonusResult,

      raw_points: {
        damage: damageRawPoints,
        fines: finesRawPoints,
        fines_surcharge: finesSurchargeRaw,
        driving_behavior: drivingBehaviorRawPenalty,
        total_penalty: totalRawPenalty
      },

      raw_penalty_per_route: {
        damage: damageRawPerRoute,
        fines: finesRawPerRoute,
        fines_surcharge: finesSurchargeRawPerRoute,
        driving_behavior: drivingBehaviorRawPerRoute,
        total_penalty: totalRawPenaltyPerRoute
      },

      penalty_per_route: {
        damage: routes > 0 ? damagePenalty / routes : 0,
        fines: finesRoutes > 0 ? finesPenalty / finesRoutes : 0,
        fines_surcharge: finesRoutes > 0 ? finesSurcharge / finesRoutes : 0,
        driving_behavior: routes > 0 ? drivingBehaviorPenalty / routes : 0,
        total_penalty: totalPenaltyPerRoute
      },

      raw_data: {
        one_sided_accidents: driver.total_one_sided,
        multi_sided_accidents: driver.total_multi_sided,
        vehicle_immobilizations: driver.total_immobilization,
        fines_count: driver.total_fines,
        fines_amount: driver.total_fines_amount,
        five_star_comments: Number(driver.five_star_count || 0),
        samsara_safety_score: driver.samsara_safety_score,
        has_samsara_safety_score: driver.samsara_safety_score !== null
      },

      calculation_details: {
        badge_match: 'Unieke chauffeurreferentie uit SAFETY_SCORES wordt gekoppeld aan DRIVER_KPI_DATA',
        score_logic: 'final_score = 100 - ((total_weighted_penalty / routes) * 10) + five_star_bonus_points',
        breakdown_logic: 'De gewogen penalty wordt gedeeld door het aantal routes en daarna vermenigvuldigd met 10.',
        five_star_logic: 'five_star_ratio = five_star_comments / routes. Alleen eligible vanaf 4 routes en minimaal 1 five-star comment.',
        removed_from_score: ['speeding', 'g-force']
      },

      lifetime_stats: lifetimeStats
    });
  }

  return scores;
}

function il20_calculateDrivingBehaviorPenalty(samsaraSafetyScore) {
  const score = il20_normalizeScore(samsaraSafetyScore);

  if (score === null) {
    return 0;
  }

  return 100 - score;
}

function il20_calculateFiveStarBonus(fiveStarCount, routes) {
  const comments = Number(fiveStarCount || 0);
  const totalRoutes = Number(routes || 0);

  if (totalRoutes < IL20_FIVE_STAR_MIN_ROUTES) {
    return {
      eligible: false,
      five_star_comments: comments,
      routes: totalRoutes,
      five_star_ratio: 0,
      bonus_points: 0,
      reason: `Niet eligible: minder dan ${IL20_FIVE_STAR_MIN_ROUTES} routes`
    };
  }

  const fiveStarRatio = totalRoutes > 0 ? comments / totalRoutes : 0;

  let bonusPoints = 0;

  if (comments <= 0) {
    bonusPoints = 0;
  } else if (fiveStarRatio > 1) {
    bonusPoints = 8;
  } else if (fiveStarRatio >= 0.5) {
    bonusPoints = 6;
  } else if (fiveStarRatio >= 0.1) {
    bonusPoints = 3;
  } else if (fiveStarRatio > 0) {
    bonusPoints = 1;
  }

  bonusPoints = Math.min(IL20_FIVE_STAR_MAX_BONUS, bonusPoints);

  return {
    eligible: true,
    five_star_comments: comments,
    routes: totalRoutes,
    five_star_ratio: fiveStarRatio,
    bonus_points: bonusPoints,
    reason: 'Bonus berekend op basis van matrix: 0 comments=0, >0-0.1=1, 0.1-0.5=3, 0.5-1=6, >1=8'
  };
}

function il20_calculateAverageRoutesPerHub(driverList) {
  const hubRoutes = {};

  driverList.forEach(driver => {
    const hub = driver.hub || IL20_TARGET_HUB;
    const routes = Number(driver.total_routes || 0);

    if (!hubRoutes[hub]) {
      hubRoutes[hub] = [];
    }

    if (routes > 0) {
      hubRoutes[hub].push(routes);
    }
  });

  const hubAverages = {};

  Object.keys(hubRoutes).forEach(hub => {
    const total = hubRoutes[hub].reduce((a, b) => a + b, 0);
    hubAverages[hub] = hubRoutes[hub].length ? total / hubRoutes[hub].length : 1;
  });

  return hubAverages;
}

function il20_calculateFinesPoints(amount, hubOrCode) {
  const amountNumber = Number(amount || 0);

  if (amountNumber <= 0) {
    return {
      points: 0,
      surcharge: 0
    };
  }

  const hubString = String(hubOrCode || '').toUpperCase();

  const isBelgium = ['BE_HUB_1']
    .some(code => hubString.includes(code));

  let points = 0;
  let surcharge = 0;

  if (isBelgium) {
    if (amountNumber <= 50) points = 5;
    else if (amountNumber <= 100) points = 10;
    else if (amountNumber <= 150) points = 20;
    else {
      points = 35;
      surcharge = 15;
    }
  } else {
    if (amountNumber <= 100) points = 5;
    else if (amountNumber <= 200) points = 10;
    else if (amountNumber <= 300) points = 20;
    else if (amountNumber <= 400) points = 25;
    else {
      points = 35;
      surcharge = 15;
    }
  }

  return {
    points,
    surcharge
  };
}

// ==========================================
// HUB RANKINGS
// ==========================================

function il20_createHubRankings(driverScores, rankChanges = {}) {
  const hubRankings = {};
  const hubGroups = {};

  for (const driver of driverScores) {
    const hub = driver.hub || IL20_TARGET_HUB;

    if (hub !== IL20_TARGET_HUB) {
      continue;
    }

    if (!hubGroups[hub]) {
      hubGroups[hub] = [];
    }

    hubGroups[hub].push(driver);
  }

  const hubStats = [];

  for (const [hubCode, drivers] of Object.entries(hubGroups)) {
    const sortedDrivers = drivers.sort((a, b) => b.final_score - a.final_score);

    const topDrivers = sortedDrivers.map((driver, index) => ({
      rank: index + 1,
      badge_number: driver.badge_number,

      final_score: il20_round(driver.final_score),
      score_before_bonus: il20_round(driver.score_before_bonus),
      five_star_bonus_points: il20_round(driver.five_star_bonus_points),

      total_penalty: il20_round(driver.total_penalty),
      total_penalty_per_route: il20_round(driver.total_penalty_per_route),
      scaled_penalty_per_10_routes: il20_round(driver.scaled_penalty_per_10_routes),

      rank_change: rankChanges[`${hubCode}_${driver.badge_number}`] ?? 0,
      routes: driver.routes,
      weeks_active: driver.weeks_active,
      hub_avg_routes: il20_round(driver.hub_avg_routes),

      raw_points: {
        damage: il20_round(driver.raw_points.damage),
        fines: il20_round(driver.raw_points.fines),
        fines_surcharge: il20_round(driver.raw_points.fines_surcharge),
        driving_behavior: il20_round(driver.raw_points.driving_behavior),
        total_penalty: il20_round(driver.raw_points.total_penalty)
      },

      weighted_raw_points: {
        damage: il20_round(driver.damage_points),
        fines: il20_round(driver.fines_points),
        fines_surcharge: il20_round(driver.fines_surcharge),
        driving_behavior: il20_round(driver.driving_behavior_points),
        total_penalty: il20_round(driver.total_penalty)
      },

      weighted_raw_penalty_per_route: {
        damage: il20_round(driver.routes > 0 ? driver.damage_points / driver.routes : 0),
        fines: il20_round(driver.routes > 0 ? driver.fines_points / driver.routes : 0),
        fines_surcharge: il20_round(driver.routes > 0 ? driver.fines_surcharge / driver.routes : 0),
        driving_behavior: il20_round(driver.routes > 0 ? driver.driving_behavior_points / driver.routes : 0),
        total_penalty: il20_round(driver.routes > 0 ? driver.total_penalty / driver.routes : 0)
      },

      five_star_bonus: {
        eligible: driver.five_star_bonus.eligible,
        five_star_comments: il20_round(driver.five_star_bonus.five_star_comments),
        routes: il20_round(driver.five_star_bonus.routes),
        five_star_ratio: il20_round(driver.five_star_bonus.five_star_ratio),
        bonus_points: il20_round(driver.five_star_bonus.bonus_points),
        reason: driver.five_star_bonus.reason
      },

      incidents: driver.raw_data,
      lifetime_stats: driver.lifetime_stats
    }));

    const damagePenTotal = drivers.reduce((sum, driver) => sum + Number(driver.damage_points || 0), 0);
    const finesPenTotal = drivers.reduce((sum, driver) => sum + Number(driver.fines_points || 0), 0);
    const drivingBehaviorPenTotal = drivers.reduce((sum, driver) => sum + Number(driver.driving_behavior_points || 0), 0);
    const totalPenTotal = drivers.reduce((sum, driver) => sum + Number(driver.total_penalty || 0), 0);
    const fiveStarBonusTotal = drivers.reduce((sum, driver) => sum + Number(driver.five_star_bonus_points || 0), 0);

    const finesCountTotal = drivers.reduce((sum, driver) => sum + Number(driver.raw_data?.fines_count || 0), 0);
    const finesAmountTotal = drivers.reduce((sum, driver) => sum + Number(driver.raw_data?.fines_amount || 0), 0);
    const fiveStarCommentsTotal = drivers.reduce((sum, driver) => sum + Number(driver.raw_data?.five_star_comments || 0), 0);

    const driversWithSamsaraScore = drivers.filter(
      driver => driver.raw_data?.has_samsara_safety_score
    ).length;

    const avgTotalPenaltyPerDriver = drivers.length > 0
      ? totalPenTotal / drivers.length
      : 0;

    const avgFiveStarBonusPerDriver = drivers.length > 0
      ? fiveStarBonusTotal / drivers.length
      : 0;

    hubStats.push({
      hub_code: hubCode,
      hub_name: IL20_HUBS[hubCode],
      damage_pen_total: il20_round(damagePenTotal),
      fines_pen_total: il20_round(finesPenTotal),
      driving_behavior_pen_total: il20_round(drivingBehaviorPenTotal),
      total_pen_total: il20_round(totalPenTotal),
      five_star_bonus_total: il20_round(fiveStarBonusTotal),
      avg_five_star_bonus_per_driver: il20_round(avgFiveStarBonusPerDriver),
      five_star_comments_total: il20_round(fiveStarCommentsTotal),
      fines_count_total: finesCountTotal,
      fines_amount_total: il20_round(finesAmountTotal),
      drivers_with_samsara_score: driversWithSamsaraScore,
      drivers_without_samsara_score: drivers.length - driversWithSamsaraScore,
      avg_total_penalty_per_driver: il20_round(avgTotalPenaltyPerDriver)
    });

    hubRankings[hubCode] = {
      hub_name: IL20_HUBS[hubCode],
      hub_code: hubCode,
      total_drivers: drivers.length,
      top_drivers: topDrivers,
      statistics: il20_calculateHubStatistics(drivers)
    };
  }

  il20_createHubRankings._lastHubStats = hubStats;

  return hubRankings;
}

function il20_calculateHubStatistics(drivers) {
  if (drivers.length === 0) {
    return {};
  }

  const scores = drivers.map(driver => Number(driver.final_score || 0));
  const scoreBeforeBonus = drivers.map(driver => Number(driver.score_before_bonus || 0));
  const routes = drivers.map(driver => Number(driver.routes || 0));

  const fiveStarBonusPoints = drivers.map(
    driver => Number(driver.five_star_bonus_points || 0)
  );

  const fiveStarComments = drivers.map(
    driver => Number(driver.raw_data?.five_star_comments || 0)
  );

  const samsaraScores = drivers
    .map(driver => driver.raw_data?.samsara_safety_score)
    .filter(score => score !== null && score !== undefined && score !== '');

  const avgScore = scores.reduce((a, b) => a + b, 0) / scores.length;
  const avgScoreBeforeBonus = scoreBeforeBonus.reduce((a, b) => a + b, 0) / scoreBeforeBonus.length;
  const avgRoutes = routes.reduce((a, b) => a + b, 0) / routes.length;
  const avgFiveStarBonus = fiveStarBonusPoints.reduce((a, b) => a + b, 0) / fiveStarBonusPoints.length;
  const totalFiveStarComments = fiveStarComments.reduce((a, b) => a + b, 0);

  const avgSamsaraScore = samsaraScores.length
    ? samsaraScores.reduce((a, b) => a + Number(b), 0) / samsaraScores.length
    : null;

  return {
    avg_score: il20_round(avgScore),
    avg_score_before_bonus: il20_round(avgScoreBeforeBonus),
    avg_routes: il20_round(avgRoutes),
    avg_five_star_bonus_points: il20_round(avgFiveStarBonus),
    total_five_star_comments: il20_round(totalFiveStarComments),
    avg_samsara_safety_score: avgSamsaraScore === null ? null : il20_round(avgSamsaraScore),
    top_score: il20_round(Math.max(...scores)),
    lowest_score: il20_round(Math.min(...scores))
  };
}

// ==========================================
// RANK CHANGES
// ==========================================

function il20_calculateRankChanges(
  rows,
  headers,
  allWeeks,
  currentWeeks,
  currentFinesWeeks,
  lifetimeData,
  inactiveDriverIds,
  samsaraScoreMap
) {
  const windowSize = IL20_WEEKS_TO_ANALYZE;

  if (
    !Array.isArray(allWeeks) ||
    !Array.isArray(currentWeeks) ||
    currentWeeks.length < windowSize ||
    allWeeks.length < IL20_FINES_WEEKS_TO_ANALYZE + 1
  ) {
    return {};
  }

  const lastWeeksSet = currentWeeks.slice(-windowSize);
  const prevWeeksSet = allWeeks.slice(-(windowSize + 1), -1);
  const prevFinesWeeksSet = allWeeks.slice(-(IL20_FINES_WEEKS_TO_ANALYZE + 1), -1);

  if (
    prevWeeksSet.length < windowSize ||
    prevFinesWeeksSet.length < IL20_FINES_WEEKS_TO_ANALYZE
  ) {
    return {};
  }

  const lastFiveStarCountMap = il20_getFiveStarCountMap(lastWeeksSet);
  const prevFiveStarCountMap = il20_getFiveStarCountMap(prevWeeksSet);

  const lastData = il20_aggregateDriverData(
    rows,
    headers,
    lastWeeksSet,
    currentFinesWeeks,
    samsaraScoreMap,
    lastFiveStarCountMap
  );

  const prevData = il20_aggregateDriverData(
    rows,
    headers,
    prevWeeksSet,
    prevFinesWeeksSet,
    samsaraScoreMap,
    prevFiveStarCountMap
  );

  let activeLastData = lastData.filter(driver => {
    return (
      !inactiveDriverIds.includes(il20_normalizeBadgeNumber(driver.badge_number)) &&
      il20_isDriverEligible(driver)
    );
  });

  let activePrevData = prevData.filter(driver => {
    return (
      !inactiveDriverIds.includes(il20_normalizeBadgeNumber(driver.badge_number)) &&
      il20_isDriverEligible(driver)
    );
  });

  if (IL20_REQUIRE_SAMSARA_SCORE) {
    activeLastData = activeLastData.filter(driver => driver.samsara_safety_score !== null);
    activePrevData = activePrevData.filter(driver => driver.samsara_safety_score !== null);
  }

  const lastScores = il20_calculateDriverScores(activeLastData, lastData, lifetimeData);
  const prevScores = il20_calculateDriverScores(activePrevData, prevData, lifetimeData);

  const prevRankMap = {};
  const changes = {};
  const prevGrouped = {};

  prevScores.forEach(driver => {
    if (!prevGrouped[driver.hub]) {
      prevGrouped[driver.hub] = [];
    }

    prevGrouped[driver.hub].push(driver);
  });

  Object.keys(prevGrouped).forEach(hub => {
    const sorted = prevGrouped[hub].sort((a, b) => b.final_score - a.final_score);

    sorted.forEach((driver, index) => {
      prevRankMap[`${hub}_${driver.badge_number}`] = index + 1;
    });
  });

  const lastGrouped = {};

  lastScores.forEach(driver => {
    if (!lastGrouped[driver.hub]) {
      lastGrouped[driver.hub] = [];
    }

    lastGrouped[driver.hub].push(driver);
  });

  Object.keys(lastGrouped).forEach(hub => {
    const sorted = lastGrouped[hub].sort((a, b) => b.final_score - a.final_score);

    sorted.forEach((driver, index) => {
      const currentRank = index + 1;
      const previousRank = prevRankMap[`${hub}_${driver.badge_number}`];

      let delta = null;

      if (previousRank !== undefined) {
        delta = previousRank - currentRank;
      }

      changes[`${hub}_${driver.badge_number}`] = delta;
    });
  });

  return changes;
}

// ==========================================
// WEEKLY PENALTY PERFORMANCE
// ==========================================

function il20_computeWeeklyPenaltyPerformance(
  rows,
  headers,
  weeks,
  aggregatedDataForHubAvg,
  samsaraScoreMap
) {
  const weekIndex = il20_getColIndex(headers, ['hellofresh_week']);
  const badgeIndex = IL20_KPI_BADGE_INDEX;

  const routesIndex = il20_getColIndex(headers, ['number_of_routes']);
  const oneSidedIndex = il20_getColIndex(headers, ['number_of_one_sided_accidents']);
  const multiSidedIndex = il20_getColIndex(headers, ['number_of_multi_sided_accidents']);
  const immobilizationIndex = il20_getColIndex(headers, ['number_of_vehicle_immobilizations']);
  const finesAmountIndex = il20_getColIndex(headers, ['fines_amount']);

  let hubIndex = il20_getColIndex(headers, ['hub_code', 'hub', 'hubcode']);

  if (hubIndex === -1) {
    hubIndex = 2;
  }

  const driverWeekMap = {};

  rows.forEach(row => {
    const week = weekIndex !== -1 ? row[weekIndex] : null;

    if (!week || !weeks.includes(week)) {
      return;
    }

    const hub = il20_mapHubCode(row[hubIndex] || 'UNKNOWN');

    if (hub !== IL20_TARGET_HUB) {
      return;
    }

    const badge = il20_normalizeBadgeNumber(row[badgeIndex]);

    if (!badge) {
      return;
    }

    const key = `${week}|${badge}`;

    if (!driverWeekMap[key]) {
      driverWeekMap[key] = {
        week,
        badge_number: badge,
        hub,
        routes: 0,
        one_sided: 0,
        multi_sided: 0,
        immobilization: 0,
        fines_amount: 0
      };
    }

    const driverWeek = driverWeekMap[key];

    driverWeek.routes += routesIndex !== -1 ? Number(row[routesIndex] || 0) : 0;
    driverWeek.one_sided += oneSidedIndex !== -1 ? Number(row[oneSidedIndex] || 0) : 0;
    driverWeek.multi_sided += multiSidedIndex !== -1 ? Number(row[multiSidedIndex] || 0) : 0;
    driverWeek.immobilization += immobilizationIndex !== -1 ? Number(row[immobilizationIndex] || 0) : 0;
    driverWeek.fines_amount += finesAmountIndex !== -1 ? Number(row[finesAmountIndex] || 0) : 0;
  });

  const hubWeekValues = {};

  Object.values(driverWeekMap).forEach(driverWeek => {
    if (driverWeek.routes <= 0) {
      return;
    }

    const badge = il20_normalizeBadgeNumber(driverWeek.badge_number);
    let samsaraSafetyScore = null;

    if (
      samsaraScoreMap &&
      Object.prototype.hasOwnProperty.call(samsaraScoreMap, badge)
    ) {
      samsaraSafetyScore = il20_normalizeScore(samsaraScoreMap[badge]);
    }

    if (IL20_REQUIRE_SAMSARA_SCORE && samsaraSafetyScore === null) {
      return;
    }

    const damageRawPoints =
      (driverWeek.one_sided * IL20_DAMAGE_POINTS.one_sided) +
      (driverWeek.multi_sided * IL20_DAMAGE_POINTS.multi_sided) +
      (driverWeek.immobilization * IL20_DAMAGE_POINTS.immobilization);

    const finesResult = il20_calculateFinesPoints(
      driverWeek.fines_amount,
      driverWeek.hub
    );

    const drivingBehaviorRawPenalty = il20_calculateDrivingBehaviorPenalty(
      samsaraSafetyScore
    );

    const totalPenalty =
      (damageRawPoints * IL20_WEIGHTS.damage) +
      (finesResult.points * IL20_WEIGHTS.fines) +
      (finesResult.surcharge * IL20_WEIGHTS.fines) +
      (drivingBehaviorRawPenalty * IL20_WEIGHTS.driving_behavior);

    if (!hubWeekValues[driverWeek.hub]) {
      hubWeekValues[driverWeek.hub] = {};
    }

    if (!hubWeekValues[driverWeek.hub][driverWeek.week]) {
      hubWeekValues[driverWeek.hub][driverWeek.week] = [];
    }

    hubWeekValues[driverWeek.hub][driverWeek.week].push(totalPenalty);
  });

  const hubWeeklyPenalties = {};

  Object.keys(hubWeekValues).forEach(hub => {
    const weekMap = hubWeekValues[hub];
    const weeklyPenalties = {};

    weeks.forEach(week => {
      const values = weekMap[week] || [];

      const average = values.length
        ? values.reduce((a, b) => a + b, 0) / values.length
        : 0;

      weeklyPenalties[week] = il20_round(average);
    });

    const values = weeks.map(week => weeklyPenalties[week]);

    const finalAverage = values.length
      ? values.reduce((a, b) => a + b, 0) / values.length
      : 0;

    const improvement = values.length >= 2
      ? values[0] - values[values.length - 1]
      : 0;

    hubWeeklyPenalties[hub] = {
      hub_name: IL20_HUBS[hub],
      weekly_penalties: weeklyPenalties,
      final_avg: il20_round(finalAverage),
      improvement: il20_round(improvement)
    };
  });

  const period1Weeks = weeks.slice(0, Math.floor(weeks.length / 2));
  const period2Weeks = weeks.slice(Math.floor(weeks.length / 2));

  const getWeekNumber = week => week?.split('-W')[1] || '';

  const getPeriodLabel = (periodWeeks, fallbackLabel) => {
    if (!periodWeeks.length) return fallbackLabel;
    if (periodWeeks.length === 1) return `Week ${getWeekNumber(periodWeeks[0])}`;

    return `Weken ${getWeekNumber(periodWeeks[0])}-${getWeekNumber(
      periodWeeks[periodWeeks.length - 1]
    )}`;
  };

  const twoWeekPeriods = {
    period_1: {
      weeks: period1Weeks,
      label: `${getPeriodLabel(period1Weeks, 'Eerste periode')} (Eerste ${period1Weeks.length} weken)`,
      description: `Periode 1: ${period1Weeks[0]} tot ${period1Weeks[period1Weeks.length - 1]}`
    },
    period_2: {
      weeks: period2Weeks,
      label: `${getPeriodLabel(period2Weeks, 'Laatste periode')} (Laatste ${period2Weeks.length} weken)`,
      description: `Periode 2: ${period2Weeks[0]} tot ${period2Weeks[period2Weeks.length - 1]}`
    },
    full_period: {
      weeks,
      label: `Alle ${weeks.length} weken (${getWeekNumber(weeks[0])}-${getWeekNumber(weeks[weeks.length - 1])})`,
      description: `Volledige periode: ${weeks[0]} tot ${weeks[weeks.length - 1]}`
    }
  };

  const twoWeekAnalysis = {};

  Object.keys(hubWeeklyPenalties).forEach(hub => {
    const weeklyPenalties = hubWeeklyPenalties[hub].weekly_penalties;

    const period1Values = period1Weeks.map(week => weeklyPenalties[week] || 0);
    const period2Values = period2Weeks.map(week => weeklyPenalties[week] || 0);

    const period1Average = period1Values.length
      ? period1Values.reduce((a, b) => a + b, 0) / period1Values.length
      : 0;

    const period2Average = period2Values.length
      ? period2Values.reduce((a, b) => a + b, 0) / period2Values.length
      : 0;

    twoWeekAnalysis[hub] = {
      hub_name: IL20_HUBS[hub],
      period_1_avg: il20_round(period1Average),
      period_2_avg: il20_round(period2Average),
      improvement: il20_round(period1Average - period2Average),
      weekly_penalties: weeklyPenalties
    };
  });

  let mostImproved = {
    hub_code: null,
    improvement: -Infinity
  };

  Object.keys(hubWeeklyPenalties).forEach(hub => {
    const improvement = hubWeeklyPenalties[hub].improvement || 0;

    if (improvement > mostImproved.improvement) {
      mostImproved = {
        hub_code: hub,
        improvement
      };
    }
  });

  const summary = {
    most_improved_hub: mostImproved.hub_code
      ? {
          hub_code: mostImproved.hub_code,
          hub_name: IL20_HUBS[mostImproved.hub_code],
          improvement: il20_round(mostImproved.improvement)
        }
      : null,

    analysis_note: 'Lagere strafpunten = betere prestatie. Verbetering = afname in strafpunten.',
    target_hub: IL20_TARGET_HUB,
    samsara_note: 'Weekly trend gebruikt dezelfde huidige Samsara Safety Score per chauffeur, tenzij je later historische weekscore-data toevoegt.',
    removed_from_score: 'Speeding en g-force zijn verwijderd omdat deze al in Samsara Safety Score zitten.'
  };

  return {
    hub_weekly_penalties: hubWeeklyPenalties,
    two_week_periods: twoWeekPeriods,
    two_week_analysis: twoWeekAnalysis,
    three_week_periods: twoWeekPeriods,
    three_week_analysis: twoWeekAnalysis,
    weeks_analyzed: weeks,
    summary
  };
}

// ==========================================
// OPSLAAN & TESTEN
// ==========================================

function il20_saveJsonToDrive(jsonData, filename) {
  try {
    const jsonString = JSON.stringify(jsonData, null, 2);

    const folderName = 'SCORE_ENGINE_OUTPUT';
    const folders = DriveApp.getFoldersByName(folderName);

    let folder;

    if (folders.hasNext()) {
      folder = folders.next();
    } else {
      folder = DriveApp.createFolder(folderName);
    }

    return folder.createFile(
      filename,
      jsonString,
      MimeType.PLAIN_TEXT
    );

  } catch (error) {
    console.error('IL2.0 - Error saving to Drive:', error);
    throw error;
  }
}

function il20_testScript() {
  console.log('IL2.0 - Start demotest...');

  const result = il20_generateHubBattleRankings();

  console.log('IL2.0 - Demotest succesvol afgerond.');

  return result;
}