dyzur-bot/sheet.go

package dyzurbot

import (
	"context"
	"fmt"
	"os"
	"time"

	"google.golang.org/api/option"
	"google.golang.org/api/sheets/v4"
)

// Column indices in the "Dyżury" sheet (see the header in row 0).
const (
	colWeek    = 0 // "Tydzień zaczynający się od" — week start date (serial)
	colPerson1 = 1 // "Osoba 1"
	colPerson2 = 2 // "Osoba 2"
	colName    = 4 // "Lista osób"
	colHandle  = 5 // Telegram handle (e.g. "@ala")
	colCount   = 6 // "Liczba dyżurów"
)

// sheetEpoch is the date corresponding to serial number 0 in Google Sheets.
var sheetEpoch = time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)

// DutyWeek is a single week of the duty rota (columns A–C).
type DutyWeek struct {
	WeekStart time.Time // Monday that starts the week
	Person1   string    // Osoba 1 — "" if unassigned
	Person2   string    // Osoba 2 — "" if unassigned
	// Row is the 1-based sheet row this week occupies, used to address its
	// cells when writing an assignment back. Valid only when the read range
	// starts at A1 (as resolveRange always produces); 0 means unknown.
	Row int
	// Tab is the sheet tab Row was fetched from (e.g. "2026"). Row is only
	// meaningful on this tab: around New Year the cache may still hold the old
	// year's rows while the clock-resolved tab has moved on, so an assignment
	// must target Tab, not the current year. "" means unknown (older cache).
	Tab string
}

// Person is a single entry from the people list (columns E–G).
type Person struct {
	Name      string // Lista osób
	Handle    string // Telegram handle, verbatim from the sheet ("" if none)
	DutyCount int    // Liczba dyżurów
}

// Rota is the parsed sheet contents: the week schedule and the people list.
type Rota struct {
	Weeks  []DutyWeek
	People []Person
}

// newSheetsService builds an authenticated Sheets client using the
// service-account key file named by GOOGLE_CREDENTIALS_FILE (loaded into the
// environment by LoadConfig, which calls godotenv.Load at startup). The
// spreadsheets scope grants read+write; the sheet must be shared with the
// service account's client_email for either to work.
func newSheetsService(ctx context.Context) (*sheets.Service, error) {
	credPath := os.Getenv("GOOGLE_CREDENTIALS_FILE")
	if credPath == "" {
		return nil, fmt.Errorf("missing GOOGLE_CREDENTIALS_FILE in environment/.env")
	}

	// WithAuthCredentialsFile pins the credential type to ServiceAccount, which
	// is the non-deprecated replacement for WithCredentialsFile: it refuses an
	// unexpected credential type instead of loading it blindly.
	srv, err := sheets.NewService(ctx,
		option.WithAuthCredentialsFile(option.ServiceAccount, credPath),
		option.WithScopes(sheets.SpreadsheetsScope), // read+write
	)
	if err != nil {
		return nil, fmt.Errorf("init sheets service: %w", err)
	}
	return srv, nil
}

// ReadSheet reads the given range from the sheet and returns its rows.
// spreadsheetID is the ID from the sheet URL, readRange e.g. "Dyżury!A1:C30".
func ReadSheet(ctx context.Context, spreadsheetID, readRange string) ([][]interface{}, error) {
	srv, err := newSheetsService(ctx)
	if err != nil {
		return nil, err
	}

	resp, err := srv.Spreadsheets.Values.
		Get(spreadsheetID, readRange).
		ValueRenderOption("UNFORMATTED_VALUE"). // raw values, without cell formatting
		Context(ctx).
		Do()
	if err != nil {
		return nil, fmt.Errorf("get values %q: %w", readRange, err)
	}

	return resp.Values, nil
}

// WriteSheet overwrites the cells starting at writeRange with values. writeRange
// anchors the top-left cell (e.g. "Dyżury!G2"); values is row-major, so each
// inner slice is one row written left-to-right. RAW input stores strings and
// numbers verbatim — a leading "=" is not interpreted as a formula. The
// service-account credential must have write access and the sheet be shared with
// it (see newSheetsService).
func WriteSheet(ctx context.Context, spreadsheetID, writeRange string, values [][]interface{}) error {
	srv, err := newSheetsService(ctx)
	if err != nil {
		return err
	}

	_, err = srv.Spreadsheets.Values.
		Update(spreadsheetID, writeRange, &sheets.ValueRange{Values: values}).
		ValueInputOption("RAW").
		Context(ctx).
		Do()
	if err != nil {
		return fmt.Errorf("update values %q: %w", writeRange, err)
	}

	return nil
}

// ParseRota interprets the raw rows from ReadSheet as two independent tables:
// the duty schedule (columns A–C) and the people list (columns E–G).
// The header row is skipped; malformed cells are ignored, not reported.
func ParseRota(rows [][]interface{}) Rota {
	var rota Rota

	for i, row := range rows {
		if i == 0 {
			continue // header
		}

		// Schedule: a row belongs to the weeks table if column A holds a
		// date serial number.
		if serial, ok := cellFloat(row, colWeek); ok {
			rota.Weeks = append(rota.Weeks, DutyWeek{
				WeekStart: serialToDate(serial),
				Person1:   cellString(row, colPerson1),
				Person2:   cellString(row, colPerson2),
				Row:       i + 1, // slice index -> 1-based sheet row (range starts at A1)
			})
		}

		// People list: add only non-empty entries (empty roster slots are
		// skipped).
		if name := cellString(row, colName); name != "" {
			count, _ := cellFloat(row, colCount)
			rota.People = append(rota.People, Person{
				Name:      name,
				Handle:    cellString(row, colHandle),
				DutyCount: int(count),
			})
		}
	}

	return rota
}

// serialToDate converts a Google Sheets serial number to a date.
func serialToDate(serial float64) time.Time {
	return sheetEpoch.AddDate(0, 0, int(serial))
}

// cellString returns the cell value as text, accounting for trailing empty
// cells that the API truncates. A missing cell yields "".
func cellString(row []interface{}, i int) string {
	if i >= len(row) {
		return ""
	}
	if s, ok := row[i].(string); ok {
		return s
	}
	return ""
}

// cellFloat returns the numeric value of a cell. ok=false when the cell is
// missing or not a number (e.g. an empty string).
func cellFloat(row []interface{}, i int) (float64, bool) {
	if i >= len(row) {
		return 0, false
	}
	if f, ok := row[i].(float64); ok {
		return f, true
	}
	return 0, false
}