/***** Major Flag Football – Team Generator v1.0 *****
 * How to use:
 * 1) Set TABLE/VIEW names below to match your base.
 * 2) Run script after new registrations (or put in an Automation).
 * 3) Review output log for any clusters that couldn’t be placed.
 *****************************************************/

//////////////////// CONFIG ////////////////////
const CONFIG = {
  playersTable: "Players",
  teamsTable: "Teams",
  settingsTable: "Settings",          // optional; leave blank "" if unused
  playerView: "",                      // optional: a view filtered to Status=Registered and no Team
  onlyStatus: "Registered",
  allowCreateTeams: false,             // set to true if you want the script to create placeholder teams
  defaultTeamNamePrefix: "Team",
  defaultMaxRoster: 10,
};

//////////////////// LOAD TABLES ////////////////////
const playersTbl = base.getTable(CONFIG.playersTable);
const teamsTbl = base.getTable(CONFIG.teamsTable);
const settingsTbl = CONFIG.settingsTable ? base.getTable(CONFIG.settingsTable) : null;

const playersQuery = await playersTbl.selectRecordsAsync({
  fields: ["Player Name","Division","Requested Coach","Friend Group ID","Sibling Group ID","Team","Status"]
});
const teamsQuery = await teamsTbl.selectRecordsAsync({
  fields: ["Team Name","Coach","Division","Max Roster"]
});

let settingsByDivision = {};
if (settingsTbl) {
  const settingsQuery = await settingsTbl.selectRecordsAsync({
    fields: ["Division","Min Roster","Max Roster","Allow Cross-Grade (Y/N)"]
  });
  for (let rec of settingsQuery.records) {
    const div = rec.getCellValueAsString("Division");
    if (!div) continue;
    settingsByDivision[div] = {
      min: Number(rec.getCellValue("Min Roster") || 0),
      max: Number(rec.getCellValue("Max Roster") || CONFIG.defaultMaxRoster),
      allowCross: (rec.getCellValueAsString("Allow Cross-Grade (Y/N)") || "N").toUpperCase() === "Y",
    };
  }
}

//////////////////// HELPERS ////////////////////
function getTeamCounts() {
  // Count current roster sizes from Players.Team links
  const counts = {};
  for (let p of playersQuery.records) {
    const teamLink = p.getCellValue("Team");
    if (teamLink && teamLink.length) {
      const teamId = teamLink[0].id;
      counts[teamId] = (counts[teamId] || 0) + 1;
    }
  }
  return counts;
}

function teamsByDivision(division) {
  return teamsQuery.records.filter(t => (t.getCellValueAsString("Division") || "").trim() === (division || "").trim());
}

function getTeamCapacity(teamRec, counts) {
  const max = Number(teamRec.getCellValue("Max Roster") || CONFIG.defaultMaxRoster);
  const current = counts[teamRec.id] || 0;
  return max - current;
}

function normalized(str) { return (str || "").trim().toLowerCase(); }

//////////////////// BUILD CLUSTERS ////////////////////
// Build clusters from unassigned + Registered players, merging Friend Group and Sibling Group
const unassigned = playersQuery.records.filter(p =>
  (p.getCellValueAsString("Status") === CONFIG.onlyStatus) &&
  (!p.getCellValue("Team") || p.getCellValue("Team").length === 0)
);

const clusters = new Map(); // key -> {players:[], division, requestedCoach}
for (let p of unassigned) {
  const keyParts = [
    p.getCellValueAsString("Friend Group ID"),
    p.getCellValueAsString("Sibling Group ID"),
    // if neither is set, fallback to player's own id to create a single-person cluster
    p.id
  ].filter(Boolean);
  const key = keyParts.length > 0 ? keyParts[0] : p.id;

  if (!clusters.has(key)) {
    clusters.set(key, {
      players: [],
      division: p.getCellValueAsString("Division"),
      requestedCoach: p.getCellValueAsString("Requested Coach")
    });
  }
  clusters.get(key).players.push(p);
}

// Sort clusters largest-first to place big groups first
const clusterList = Array.from(clusters.values()).sort((a,b) => b.players.length - a.players.length);

//////////////////// ASSIGNMENT ////////////////////
const teamCounts = getTeamCounts();
const updates = []; // {playerId, teamId}
const unplaced = [];

for (let cluster of clusterList) {
  const division = cluster.division;
  const divTeams = teamsByDivision(division);
  // Get settings cap if present
  const divMax = settingsByDivision[division]?.max ?? CONFIG.defaultMaxRoster;

  // Prefer requested coach if exists and has capacity
  let preferred = null;
  if (cluster.requestedCoach) {
    preferred = divTeams.find(t => normalized(t.getCellValueAsString("Coach")) === normalized(cluster.requestedCoach));
    if (preferred) {
      const cap = getTeamCapacity(preferred, teamCounts);
      if (cap >= cluster.players.length) {
        // Assign all to preferred
        for (let p of cluster.players) updates.push({playerId: p.id, teamId: preferred.id});
        teamCounts[preferred.id] = (teamCounts[preferred.id] || 0) + cluster.players.length;
        continue; // next cluster
      }
    }
  }

  // Else, place in team with most remaining capacity in this division
  const sortedByCapacity = divTeams
    .map(t => ({team: t, cap: getTeamCapacity(t, teamCounts)}))
    .sort((a,b) => b.cap - a.cap);

  let placed = false;
  for (let entry of sortedByCapacity) {
    if (entry.cap >= cluster.players.length && entry.cap > 0) {
      for (let p of cluster.players) updates.push({playerId: p.id, teamId: entry.team.id});
      teamCounts[entry.team.id] = (teamCounts[entry.team.id] || 0) + cluster.players.length;
      placed = true;
      break;
    }
  }

  // Optionally create a new team if none had space
  if (!placed && CONFIG.allowCreateTeams) {
    // Create a simple placeholder team in this division
    const newTeamName = `${CONFIG.defaultTeamNamePrefix} ${division} ${teamsByDivision(division).length + 1}`;
    const createRec = await teamsTbl.createRecordAsync({
      "Team Name": newTeamName,
      "Division": { name: division },
      "Max Roster": divMax
    });
    teamCounts[createRec] = 0;
    // Assign to the new team
    for (let p of cluster.players) updates.push({playerId: p.id, teamId: createRec});
    teamCounts[createRec] += cluster.players.length;
    placed = true;
  }

  if (!placed) {
    unplaced.push({
      clusterSize: cluster.players.length,
      division,
      requestedCoach: cluster.requestedCoach || "(none)"
    });
  }
}

//////////////////// WRITE BACK ////////////////////
let batch = [];
for (let up of updates) {
  batch.push({
    id: up.playerId,
    fields: { "Team": [ { id: up.teamId } ] }
  });
  if (batch.length === 50) {
    await playersTbl.updateRecordsAsync(batch);
    batch = [];
  }
}
if (batch.length) await playersTbl.updateRecordsAsync(batch);

//////////////////// REPORT ////////////////////
output.markdown(`### Team Generator Complete`);
output.markdown(`Assigned **${updates.length}** players across **${clusterList.length}** clusters.`);
if (unplaced.length) {
  output.markdown(`\n**Unplaced clusters (need capacity or new teams):**`);
  unplaced.forEach(u => {
    output.text(`- Division ${u.division} | size ${u.clusterSize} | requested coach: ${u.requestedCoach}`);
  });
} else {
  output.text(`All clusters placed successfully.`);
}
Major Flag Football League
