chapter-organizer/tools/GoogleSheetsScheduleImport/ScheduleGridExtractor.cs

namespace GoogleSheetsScheduleImport;

/// <summary>
/// Interprets location headers + time rows + colored/value blocks as occurrence lines.
/// </summary>
public static class ScheduleGridExtractor
{
    public static List<ParsedOccurrenceLine> Extract(
        GridSheetModel grid,
        string month,
        int day,
        List<string> warnings)
    {
        var result = new List<ParsedOccurrenceLine>();
        if (grid.RowCount < 2 || grid.ColCount < 2)
        {
            warnings.Add($"Sheet '{grid.SheetTitle}': grid too small; need at least a header row and time column.");
            return result;
        }

        var locations = new string[grid.ColCount];
        for (var c = 1; c < grid.ColCount; c++)
        {
            var header = grid.Values[0][c];
            locations[c] = string.IsNullOrWhiteSpace(header) ? $"Column {c + 1}" : header.Trim();
        }

        var rowTimes = new TimeOnly?[grid.RowCount];
        rowTimes[0] = null;
        for (var r = 1; r < grid.RowCount; r++)
        {
            var cell = grid.Values[r][0];
            if (!TimeCellParser.TryParse(cell, out var t))
            {
                if (!string.IsNullOrWhiteSpace(cell))
                    warnings.Add($"Sheet '{grid.SheetTitle}' row {r + 1}: could not parse time label '{cell}'.");
                rowTimes[r] = null;
            }
            else
                rowTimes[r] = t;
        }

        var slot = InferSlotDuration(rowTimes, warnings, grid.SheetTitle);

        for (var c = 1; c < grid.ColCount; c++)
        {
            var location = locations[c];
            var r = 1;
            while (r < grid.RowCount)
            {
                if (!IsOccupied(grid, r, c))
                {
                    r++;
                    continue;
                }

                var startRow = r;
                var name = grid.Values[r][c] ?? "";
                var key = BlockKey(grid, r, c);

                var endRow = r;
                while (endRow + 1 < grid.RowCount &&
                       IsOccupied(grid, endRow + 1, c) &&
                       BlockKey(grid, endRow + 1, c) == key)
                {
                    endRow++;
                }

                var startTime = rowTimes[startRow];
                if (startTime == null)
                {
                    warnings.Add(
                        $"Sheet '{grid.SheetTitle}' ({location}): block at row {startRow + 1} has no parseable start time in column A.");
                    r = endRow + 1;
                    continue;
                }

                var endInstant = ComputeEndTime(rowTimes, endRow, grid.RowCount, slot);

                var timeRange = TimeFormatter.ToParserTimeRange(startTime.Value, endInstant);
                var title = name.Trim();
                if (string.IsNullOrEmpty(title))
                    title = "(no title)";

                result.Add(new ParsedOccurrenceLine(
                    Name: title,
                    Month: month,
                    Day: day,
                    TimeRange: timeRange,
                    Location: location,
                    SourceRowStart: startRow,
                    SourceRowEnd: endRow,
                    SourceCol: c));

                r = endRow + 1;
            }
        }

        return result;
    }

    private static bool IsOccupied(GridSheetModel grid, int r, int c)
    {
        var v = grid.Values[r][c];
        var bg = grid.BackgroundKeys?[r][c];
        if (!string.IsNullOrWhiteSpace(v))
            return true;
        return bg != null; // colored empty cell
    }

    private static (string NameKey, string? Bg) BlockKey(GridSheetModel grid, int r, int c)
    {
        var raw = grid.Values[r][c] ?? "";
        var v = raw.Trim();
        var bg = grid.BackgroundKeys?[r][c];
        return (v, bg);
    }

    private static TimeOnly ComputeEndTime(TimeOnly?[] rowTimes, int endRow, int rowCount, TimeSpan slot)
    {
        if (endRow + 1 < rowCount && rowTimes[endRow + 1] != null)
            return rowTimes[endRow + 1]!.Value;

        var lastStart = rowTimes[endRow] ?? throw new InvalidOperationException();
        return lastStart.Add(slot);
    }

    private static TimeSpan InferSlotDuration(TimeOnly?[] rowTimes, List<string> warnings, string sheetTitle)
    {
        var deltas = new List<int>();
        for (var i = 1; i < rowTimes.Length - 1; i++)
        {
            if (rowTimes[i] == null || rowTimes[i + 1] == null)
                continue;
            var minutes = (int)(rowTimes[i + 1]!.Value - rowTimes[i]!.Value).TotalMinutes;
            if (minutes > 0 && minutes <= 24 * 60)
                deltas.Add(minutes);
        }

        if (deltas.Count == 0)
        {
            warnings.Add($"Sheet '{sheetTitle}': could not infer time-slot length from column A; defaulting to 30 minutes.");
            return TimeSpan.FromMinutes(30);
        }

        var g = deltas.GroupBy(d => d).OrderByDescending(g => g.Count()).First();
        return TimeSpan.FromMinutes(g.Key);
    }
}