import { auth, sheets, type sheets_v4 } from "@googleapis/sheets"
import * as z from "zod"
import {
  BANDED_RANGE_SCHEMA,
  BANDING_PROPERTIES_UPDATE_SCHEMA,
  CONDITIONAL_FORMAT_RULE_SCHEMA,
  DATE_TIME_RENDER_SCHEMA,
  GRID_RANGE_SCHEMA,
  MAJOR_DIMENSION_SCHEMA,
  RANGE_TARGET_SCHEMA,
  ROW_SOURCE_SCHEMA,
  SHEET_REFERENCE_SCHEMA,
  SHEET_SCHEMA,
  SPREADSHEET_SCHEMA,
  TABLE_COLUMN_TYPE_SCHEMA,
  TABLE_SCHEMA,
  VALUE_RANGE_SCHEMA,
  VALUE_RENDER_SCHEMA,
  VALUE_UPDATE_SCHEMA,
  CELL_VALUE_SCHEMA,
} from "./schemas"
import {
  normalizeGoogleBandingProperties,
  normalizeGoogleConditionalRule,
  normalizeGoogleTableStyle,
} from "./formatting"

const GOOGLE_SHEETS_URL_HOSTS = new Set([
  "docs.google.com",
  "sheets.google.com",
])
const GOOGLE_SHEETS_SECRET_SCHEMA = z.object({
  accessToken: z.string().min(1),
})
const TABLE_COLUMN_TYPES = {
  boolean: "BOOLEAN",
  currency: "CURRENCY",
  date: "DATE",
  dateTime: "DATE_TIME",
  dropdown: "DROPDOWN",
  file: "FILES_CHIP",
  finance: "FINANCE_CHIP",
  number: "DOUBLE",
  percent: "PERCENT",
  person: "PEOPLE_CHIP",
  place: "PLACE_CHIP",
  rating: "RATINGS_CHIP",
  text: "TEXT",
  time: "TIME",
  unspecified: "COLUMN_TYPE_UNSPECIFIED",
} satisfies Record<z.infer<typeof TABLE_COLUMN_TYPE_SCHEMA>, string>
export interface ResolvedRowSource {
  bodyEndRow?: number
  bodyStartRow: number
  columnCount: number
  headers: string[]
  sheetId: number
  sheetTitle: string
  tableId?: string
}

/**
 * Creates the official Sheets client from a resolved Google integration secret.
 *
 * @param secret - Refreshed Google integration secret.
 */
export function getGoogleSheetsApi(secret: Record<string, unknown>) {
  const { accessToken } = GOOGLE_SHEETS_SECRET_SCHEMA.parse(secret)
  return sheets({
    auth: new auth.OAuth2({
      credentials: { access_token: accessToken },
    }),
    version: "v4",
  })
}

/**
 * Accepts either a raw spreadsheet ID or a standard Google Sheets URL.
 *
 * @param spreadsheet - Spreadsheet ID or URL.
 * @throws When a URL is not a recognizable Google Sheets URL.
 */
export function normalizeSpreadsheetId(spreadsheet: string) {
  const value = spreadsheet.trim()
  if (!URL.canParse(value)) return value

  const url = new URL(value)
  if (!GOOGLE_SHEETS_URL_HOSTS.has(url.hostname)) {
    throw new Error(`Expected a Google Sheets URL, received "${value}".`)
  }

  const parts = url.pathname.split("/")
  const spreadsheetsIndex = parts.indexOf("spreadsheets")
  const spreadsheetId =
    spreadsheetsIndex === -1 || parts[spreadsheetsIndex + 1] !== "d"
      ? undefined
      : parts[spreadsheetsIndex + 2]
  if (!spreadsheetId) {
    throw new Error(`Could not find a spreadsheet ID in "${value}".`)
  }
  return spreadsheetId
}

/**
 * Resolves a sheet title or immutable ID with one metadata request.
 *
 * @param sheetsApi - Authenticated Sheets client.
 * @param spreadsheetId - Immutable spreadsheet ID.
 * @param reference - Sheet title or ID.
 * @throws When the referenced sheet does not exist.
 */
export async function resolveSheet(
  sheetsApi: sheets_v4.Sheets,
  spreadsheetId: string,
  reference: z.infer<typeof SHEET_REFERENCE_SCHEMA>,
) {
  const { data } = await sheetsApi.spreadsheets.get({
    fields: "sheets(properties,tables)",
    spreadsheetId,
  })
  const sheet = data.sheets?.find(({ properties }) =>
    typeof reference === "number"
      ? properties?.sheetId === reference
      : properties?.title === reference,
  )
  if (!sheet?.properties?.title || sheet.properties.sheetId == null) {
    throw new Error(`Google Sheets sheet "${reference}" was not found.`)
  }
  return sheet
}

/**
 * Resolves a native table name or ID and its owning sheet.
 *
 * @param sheetsApi - Authenticated Sheets client.
 * @param spreadsheetId - Immutable spreadsheet ID.
 * @param reference - Native table name or ID.
 * @throws When the referenced table does not exist.
 */
export async function resolveTable(
  sheetsApi: sheets_v4.Sheets,
  spreadsheetId: string,
  reference: string,
) {
  const { data } = await sheetsApi.spreadsheets.get({
    fields: "sheets(properties(sheetId,title),tables)",
    spreadsheetId,
  })
  for (const sheet of data.sheets ?? []) {
    const table = sheet.tables?.find(
      ({ name, tableId }) => name === reference || tableId === reference,
    )
    if (table && sheet.properties?.sheetId != null && sheet.properties.title) {
      return { sheet, table }
    }
  }
  throw new Error(`Google Sheets table "${reference}" was not found.`)
}

/**
 * Resolves a header-backed sheet or native table into row coordinates.
 *
 * @param sheetsApi - Authenticated Sheets client.
 * @param spreadsheetId - Immutable spreadsheet ID.
 * @param source - Header-backed sheet or native table.
 */
export async function resolveRowSource(
  sheetsApi: sheets_v4.Sheets,
  spreadsheetId: string,
  source: z.infer<typeof ROW_SOURCE_SCHEMA>,
): Promise<ResolvedRowSource> {
  if ("table" in source) {
    const { sheet, table } = await resolveTable(
      sheetsApi,
      spreadsheetId,
      source.table,
    )
    const range = table.range
    const headers = (table.columnProperties ?? []).map(
      ({ columnName }) => columnName?.trim() ?? "",
    )
    validateHeaders(headers)
    return {
      bodyEndRow:
        range?.endRowIndex == null
          ? undefined
          : range.endRowIndex -
            (table.rowsProperties?.footerColorStyle ? 1 : 0),
      bodyStartRow: (range?.startRowIndex ?? 0) + 2,
      columnCount: headers.length,
      headers,
      sheetId: sheet.properties!.sheetId!,
      sheetTitle: sheet.properties!.title!,
      tableId: table.tableId ?? undefined,
    }
  }

  const sheet = await resolveSheet(sheetsApi, spreadsheetId, source.sheet)
  const headerRow = source.headerRow ?? 1
  const { data } = await sheetsApi.spreadsheets.values.get({
    majorDimension: "ROWS",
    range: buildA1Range(sheet.properties!.title!, {
      endRow: headerRow,
      startRow: headerRow,
    }),
    spreadsheetId,
    valueRenderOption: "UNFORMATTED_VALUE",
  })
  const headers = (data.values?.[0] ?? []).map((header) =>
    String(header).trim(),
  )
  validateHeaders(headers)
  return {
    bodyStartRow: headerRow + 1,
    columnCount: headers.length,
    headers,
    sheetId: sheet.properties!.sheetId!,
    sheetTitle: sheet.properties!.title!,
  }
}

/**
 * Converts one-based public coordinates into a native zero-based GridRange.
 *
 * @param sheetsApi - Authenticated Sheets client.
 * @param spreadsheetId - Immutable spreadsheet ID.
 * @param range - Public structured grid range.
 */
export async function resolveGridRange(
  sheetsApi: sheets_v4.Sheets,
  spreadsheetId: string,
  range: z.infer<typeof GRID_RANGE_SCHEMA>,
) {
  return {
    endColumnIndex: range.endColumn,
    endRowIndex: range.endRow,
    sheetId: (await resolveSheet(sheetsApi, spreadsheetId, range.sheet))
      .properties!.sheetId!,
    startColumnIndex:
      range.startColumn === undefined ? undefined : range.startColumn - 1,
    startRowIndex:
      range.startRow === undefined ? undefined : range.startRow - 1,
  } satisfies sheets_v4.Schema$GridRange
}

/**
 * Resolves a structured range or native-table section into a grid range.
 *
 * @param sheetsApi - Authenticated Sheets client.
 * @param spreadsheetId - Immutable spreadsheet ID.
 * @param target - Public grid range or native-table section.
 * @throws When the table section does not exist or lacks bounded coordinates.
 */
export async function resolveRangeTarget(
  sheetsApi: sheets_v4.Sheets,
  spreadsheetId: string,
  target: z.infer<typeof RANGE_TARGET_SCHEMA>,
) {
  if (!("table" in target)) {
    return await resolveGridRange(sheetsApi, spreadsheetId, target)
  }

  const { table } = await resolveTable(sheetsApi, spreadsheetId, target.table)
  return resolveTableSectionRange(table, target)
}

/**
 * Resolves one native-table section against provider table metadata.
 *
 * @param table - Provider native-table metadata.
 * @param target - Public native-table section target.
 * @throws When the table section does not exist or lacks bounded coordinates.
 */
export function resolveTableSectionRange(
  table: sheets_v4.Schema$Table,
  target: Extract<z.infer<typeof RANGE_TARGET_SCHEMA>, { table: string }>,
) {
  const range = table.range
  if (range?.sheetId == null) {
    throw new Error("Google returned a table without a complete range.")
  }
  const section = target.section ?? "all"
  if (section === "all") return normalizeGridRange(range)

  const startRowIndex = range.startRowIndex ?? 0
  if (section === "header") {
    return normalizeGridRange({
      ...range,
      endRowIndex: startRowIndex + 1,
      startRowIndex,
    })
  }

  const endRowIndex = range.endRowIndex
  if (endRowIndex === undefined || endRowIndex === null) {
    throw new Error("Google returned a table without a bounded ending row.")
  }
  const hasFooter = table.rowsProperties?.footerColorStyle != null
  if (section === "footer") {
    if (!hasFooter) {
      throw new Error(`Google Sheets table "${target.table}" has no footer.`)
    }
    return normalizeGridRange({
      ...range,
      endRowIndex,
      startRowIndex: endRowIndex - 1,
    })
  }

  const bodyEndRowIndex = endRowIndex - (hasFooter ? 1 : 0)
  if (bodyEndRowIndex <= startRowIndex + 1) {
    throw new Error(`Google Sheets table "${target.table}" has no body rows.`)
  }
  return normalizeGridRange({
    ...range,
    endRowIndex: bodyEndRowIndex,
    startRowIndex: startRowIndex + 1,
  })
}

/**
 * Resolves non-empty range targets and ensures they share one sheet.
 *
 * @param sheetsApi - Authenticated Sheets client.
 * @param spreadsheetId - Immutable spreadsheet ID.
 * @param targets - Public grid ranges or table sections.
 * @throws When the resolved ranges do not share one sheet.
 */
export async function resolveSameSheetRangeTargets(
  sheetsApi: sheets_v4.Sheets,
  spreadsheetId: string,
  targets: z.infer<typeof RANGE_TARGET_SCHEMA>[],
) {
  const ranges = await Promise.all(
    targets.map(
      async (target) =>
        await resolveRangeTarget(sheetsApi, spreadsheetId, target),
    ),
  )
  const sheetId = ranges[0]?.sheetId
  if (sheetId == null || ranges.some((range) => range.sheetId !== sheetId)) {
    throw new Error(
      "Google Sheets conditional-format ranges must all be on the same sheet.",
    )
  }
  return { ranges, sheetId }
}

/**
 * Resolves a mutable numeric banded-range ID.
 *
 * @param sheetsApi - Authenticated Sheets client.
 * @param spreadsheetId - Immutable spreadsheet ID.
 * @param bandedRangeId - Numeric provider banded-range ID.
 * @throws When the banded range does not exist.
 */
export async function resolveBandedRange(
  sheetsApi: sheets_v4.Sheets,
  spreadsheetId: string,
  bandedRangeId: number,
) {
  const { data } = await sheetsApi.spreadsheets.get({
    fields: "sheets(bandedRanges,properties(sheetId,title))",
    spreadsheetId,
  })
  for (const sheet of data.sheets ?? []) {
    const bandedRange = sheet.bandedRanges?.find(
      (candidate) => candidate.bandedRangeId === bandedRangeId,
    )
    if (bandedRange) return bandedRange
  }
  throw new Error(
    `Google Sheets banded range "${bandedRangeId}" was not found.`,
  )
}

/**
 * Ensures a banded-range update keeps row or column banding.
 *
 * @param bandedRange - Existing provider banded-range metadata.
 * @param columns - Requested column update or clear.
 * @param rows - Requested row update or clear.
 * @throws When the update would leave no banding direction.
 */
export function assertBandedRangeUpdateKeepsBanding(
  bandedRange: sheets_v4.Schema$BandedRange,
  columns: z.infer<typeof BANDING_PROPERTIES_UPDATE_SCHEMA> | null | undefined,
  rows: z.infer<typeof BANDING_PROPERTIES_UPDATE_SCHEMA> | null | undefined,
) {
  if (
    (columns === null ||
      (columns === undefined && bandedRange.columnProperties == null)) &&
    (rows === null || (rows === undefined && bandedRange.rowProperties == null))
  ) {
    throw new Error(
      "A banded range must keep row or column banding. Delete the banded range to remove both.",
    )
  }
}

/**
 * Reads one conditional-format rule by sheet and zero-based index.
 *
 * @param sheetsApi - Authenticated Sheets client.
 * @param spreadsheetId - Immutable spreadsheet ID.
 * @param sheetReference - Sheet title or immutable numeric ID.
 * @param index - Zero-based rule priority.
 * @throws When the indexed rule does not exist on the selected sheet.
 */
export async function resolveConditionalFormatRule(
  sheetsApi: sheets_v4.Sheets,
  spreadsheetId: string,
  sheetReference: z.infer<typeof SHEET_REFERENCE_SCHEMA>,
  index: number,
) {
  const sheetId = (await resolveSheet(sheetsApi, spreadsheetId, sheetReference))
    .properties!.sheetId!
  const { data } = await sheetsApi.spreadsheets.get({
    fields: "sheets(conditionalFormats,properties(sheetId))",
    spreadsheetId,
  })
  const rule = data.sheets
    ?.find(({ properties }) => properties?.sheetId === sheetId)
    ?.conditionalFormats?.at(index)
  if (!rule) {
    throw new Error(
      `Google Sheets conditional format rule "${index}" was not found on sheet "${sheetReference}".`,
    )
  }
  return { rule, sheetId }
}

/**
 * Normalizes provider spreadsheet metadata into the public output shape.
 *
 * @param spreadsheet - Provider spreadsheet resource.
 */
export function normalizeSpreadsheet(
  spreadsheet: sheets_v4.Schema$Spreadsheet,
) {
  return SPREADSHEET_SCHEMA.parse({
    autoRecalc: spreadsheet.properties?.autoRecalc ?? undefined,
    locale: spreadsheet.properties?.locale ?? undefined,
    namedRanges: (spreadsheet.namedRanges ?? []).flatMap((namedRange) =>
      namedRange.name &&
      namedRange.namedRangeId &&
      namedRange.range?.sheetId != null
        ? [
            {
              name: namedRange.name,
              namedRangeId: namedRange.namedRangeId,
              range: normalizeGridRange(namedRange.range),
            },
          ]
        : [],
    ),
    sheets: (spreadsheet.sheets ?? []).map(normalizeSheet),
    spreadsheetId: spreadsheet.spreadsheetId,
    timeZone: spreadsheet.properties?.timeZone ?? undefined,
    title: spreadsheet.properties?.title,
    url: spreadsheet.spreadsheetUrl,
  })
}

/**
 * Normalizes provider sheet metadata into the public output shape.
 *
 * @param sheet - Provider sheet resource.
 * @throws When required sheet identity fields are absent.
 */
export function normalizeSheet(sheet: sheets_v4.Schema$Sheet) {
  const properties = sheet.properties
  if (
    properties?.index == null ||
    properties.sheetId == null ||
    !properties.title
  ) {
    throw new Error("Google returned incomplete sheet metadata.")
  }
  return SHEET_SCHEMA.parse({
    bandedRanges: (sheet.bandedRanges ?? []).map(normalizeBandedRange),
    columnCount: properties.gridProperties?.columnCount ?? undefined,
    conditionalFormats: (sheet.conditionalFormats ?? []).map(
      normalizeConditionalFormatRule,
    ),
    frozenColumnCount: properties.gridProperties?.frozenColumnCount ?? 0,
    frozenRowCount: properties.gridProperties?.frozenRowCount ?? 0,
    hidden: properties.hidden ?? false,
    index: properties.index,
    rightToLeft: properties.rightToLeft ?? false,
    rowCount: properties.gridProperties?.rowCount ?? undefined,
    sheetId: properties.sheetId,
    sheetType: properties.sheetType ?? "GRID",
    tables: (sheet.tables ?? []).map(normalizeTable),
    title: properties.title,
  })
}

/**
 * Normalizes a provider native table into the public output shape.
 *
 * @param table - Provider table resource.
 * @throws When required table identity fields are absent.
 */
export function normalizeTable(table: sheets_v4.Schema$Table) {
  if (!table.name || !table.tableId || table.range?.sheetId == null) {
    throw new Error("Google returned incomplete table metadata.")
  }
  return TABLE_SCHEMA.parse({
    columns: (table.columnProperties ?? []).map((column, index) => ({
      index: column.columnIndex ?? index,
      name: column.columnName ?? "",
      options:
        column.dataValidationRule?.condition?.values?.flatMap(
          ({ userEnteredValue }) =>
            userEnteredValue === null || userEnteredValue === undefined
              ? []
              : [userEnteredValue],
        ) ?? [],
      type: toPublicTableColumnType(column.columnType),
    })),
    name: table.name,
    range: normalizeGridRange(table.range),
    style: normalizeGoogleTableStyle(table.rowsProperties),
    tableId: table.tableId,
  })
}

/**
 * Normalizes a provider banded range into the public output shape.
 *
 * @param bandedRange - Provider banded-range metadata.
 * @throws When required identity, range, or color metadata is absent.
 */
export function normalizeBandedRange(
  bandedRange: sheets_v4.Schema$BandedRange,
) {
  if (bandedRange.range?.sheetId == null) {
    throw new Error("Google returned a banded range without a complete range.")
  }
  return BANDED_RANGE_SCHEMA.parse({
    bandedRangeId: bandedRange.bandedRangeId ?? undefined,
    columns: bandedRange.columnProperties
      ? normalizeGoogleBandingProperties(bandedRange.columnProperties)
      : undefined,
    range: normalizeGridRange(bandedRange.range),
    reference: bandedRange.bandedRangeReference ?? undefined,
    rows: bandedRange.rowProperties
      ? normalizeGoogleBandingProperties(bandedRange.rowProperties)
      : undefined,
  })
}

/**
 * Normalizes a provider conditional-format rule and its sheet priority.
 *
 * @param rule - Provider conditional-format rule.
 * @param index - Zero-based rule priority.
 * @throws When required ranges or rule metadata are absent.
 */
export function normalizeConditionalFormatRule(
  rule: sheets_v4.Schema$ConditionalFormatRule,
  index: number,
) {
  if (!rule.ranges?.length) {
    throw new Error("Google returned a conditional format rule without ranges.")
  }
  return CONDITIONAL_FORMAT_RULE_SCHEMA.parse({
    index,
    ranges: rule.ranges.map(normalizeGridRange),
    rule: normalizeGoogleConditionalRule(rule),
  })
}

/**
 * Normalizes a provider value range into the public matrix shape.
 *
 * @param valueRange - Provider value range.
 */
export function normalizeValueRange(valueRange: sheets_v4.Schema$ValueRange) {
  return VALUE_RANGE_SCHEMA.parse({
    majorDimension:
      valueRange.majorDimension === "COLUMNS" ? "columns" : "rows",
    range: valueRange.range ?? "",
    values: valueRange.values ?? [],
  })
}

/**
 * Normalizes provider value-update counts and optional returned values.
 *
 * @param update - Provider update response.
 */
export function normalizeValueUpdate(
  update: sheets_v4.Schema$UpdateValuesResponse,
) {
  return VALUE_UPDATE_SCHEMA.parse({
    updatedCells: update.updatedCells ?? 0,
    updatedColumns: update.updatedColumns ?? 0,
    updatedRange: update.updatedRange ?? "",
    updatedRows: update.updatedRows ?? 0,
    values: update.updatedData
      ? normalizeValueRange(update.updatedData)
      : undefined,
  })
}

/**
 * Maps the public dimension name to the provider enum.
 *
 * @param dimension - Public major dimension.
 */
export function toGoogleMajorDimension(
  dimension: z.infer<typeof MAJOR_DIMENSION_SCHEMA> | undefined,
) {
  return dimension === "columns" ? "COLUMNS" : "ROWS"
}

/**
 * Maps the public value rendering mode to the provider enum.
 *
 * @param render - Public value rendering mode.
 */
export function toGoogleValueRender(
  render: z.infer<typeof VALUE_RENDER_SCHEMA> | undefined,
) {
  if (render === "formula") return "FORMULA"
  if (render === "formatted") return "FORMATTED_VALUE"
  return "UNFORMATTED_VALUE"
}

/**
 * Maps the public date rendering mode to the provider enum.
 *
 * @param render - Public date rendering mode.
 */
export function toGoogleDateTimeRender(
  render: z.infer<typeof DATE_TIME_RENDER_SCHEMA> | undefined,
) {
  return render === "formattedString" ? "FORMATTED_STRING" : "SERIAL_NUMBER"
}

/**
 * Converts one public cell value into provider cell data.
 *
 * @param value - Public cell value.
 * @param inputMode - Whether formula-looking strings are parsed.
 */
export function toGoogleCellData(
  value: z.infer<typeof CELL_VALUE_SCHEMA>,
  inputMode: "raw" | "userEntered" = "userEntered",
): sheets_v4.Schema$CellData {
  if (value === null) return {}
  if (typeof value === "boolean") {
    return { userEnteredValue: { boolValue: value } }
  }
  if (typeof value === "number") {
    return { userEnteredValue: { numberValue: value } }
  }
  return {
    userEnteredValue:
      inputMode === "userEntered" && value.startsWith("=")
        ? { formulaValue: value }
        : { stringValue: value },
  }
}

/**
 * Converts a public native-table column into provider metadata.
 *
 * @param column - Public column configuration.
 * @param column.name - Display name for the column.
 * @param column.options - Allowed values for dropdown columns.
 * @param column.type - Native table column type.
 * @param index - Zero-based position in the table.
 */
export function toGoogleTableColumn(
  column: {
    name: string
    options?: string[]
    type?: z.infer<typeof TABLE_COLUMN_TYPE_SCHEMA>
  },
  index: number,
): sheets_v4.Schema$TableColumnProperties {
  return {
    columnIndex: index,
    columnName: column.name,
    columnType: TABLE_COLUMN_TYPES[column.type ?? "unspecified"],
    dataValidationRule: column.options
      ? {
          condition: {
            type: "ONE_OF_LIST",
            values: column.options.map((userEnteredValue) => ({
              userEnteredValue,
            })),
          },
        }
      : undefined,
  }
}

/**
 * Converts a six-digit hex color into provider RGB fractions.
 *
 * @param hex - Six-digit hex color.
 */
/**
 * Maps provider row matrices to exact header-keyed records.
 *
 * @param rows - Provider row matrices.
 * @param source - Resolved row source.
 * @param startRow - One-based row number for the first matrix row.
 */
export function normalizeRows(
  rows: unknown[][],
  source: ResolvedRowSource,
  startRow: number,
) {
  return rows.map((row, index) => ({
    range: buildA1Range(source.sheetTitle, {
      endColumn: source.columnCount,
      endRow: startRow + index,
      startColumn: 1,
      startRow: startRow + index,
    }),
    rowNumber: startRow + index,
    values: Object.fromEntries(
      source.headers.map((header, columnIndex) => [
        header,
        CELL_VALUE_SCHEMA.parse(row[columnIndex] ?? null),
      ]),
    ),
  }))
}

/**
 * Reads a bounded or unbounded sequence from a resolved row source.
 *
 * @param sheetsApi - Authenticated Sheets client.
 * @param spreadsheetId - Immutable spreadsheet ID.
 * @param source - Resolved row source.
 * @param options - Optional row bounds and rendering mode.
 * @param options.endRow - Inclusive one-based final row.
 * @param options.startRow - Inclusive one-based first row.
 * @param options.valueRender - How values should be rendered.
 */
export async function readSourceRows(
  sheetsApi: sheets_v4.Sheets,
  spreadsheetId: string,
  source: ResolvedRowSource,
  options?: {
    endRow?: number
    startRow?: number
    valueRender?: z.infer<typeof VALUE_RENDER_SCHEMA>
  },
) {
  const startRow = options?.startRow ?? source.bodyStartRow
  const endRow = options?.endRow ?? source.bodyEndRow
  if (endRow !== undefined && endRow < startRow) return []

  const {
    data: { values },
  } = await sheetsApi.spreadsheets.values.get({
    majorDimension: "ROWS",
    range: buildA1Range(source.sheetTitle, {
      endColumn: source.columnCount,
      endRow,
      startColumn: 1,
      startRow,
    }),
    spreadsheetId,
    valueRenderOption: toGoogleValueRender(options?.valueRender),
  })
  return normalizeRows(values ?? [], source, startRow)
}

/**
 * Validates that row numbers belong to a resolved source body.
 *
 * @param source - Resolved row source.
 * @param rowNumbers - One-based row numbers.
 * @param options - Whether the first row after the current body is valid.
 * @param options.allowAppend - Whether to accept the first row after the body.
 * @throws When any row falls outside the source body.
 */
export function assertSourceRowNumbers(
  source: ResolvedRowSource,
  rowNumbers: number[],
  options?: { allowAppend?: boolean },
) {
  const maximum = source.bodyEndRow
    ? source.bodyEndRow + (options?.allowAppend ? 1 : 0)
    : undefined
  const invalid = rowNumbers.filter(
    (rowNumber) =>
      rowNumber < source.bodyStartRow ||
      (maximum !== undefined && rowNumber > maximum),
  )
  if (invalid.length) {
    throw new Error(
      `Rows ${invalid.join(", ")} fall outside the source body, which starts at row ${source.bodyStartRow}${maximum ? ` and ends at row ${maximum}` : ""}.`,
    )
  }
}

/**
 * Orders record values by exact source headers.
 *
 * Missing columns become null values.
 *
 * @param values - Exact header-value pairs.
 * @param headers - Source headers in display order.
 * @throws When the record contains an unknown header.
 */
export function normalizeRowValues(
  values: Record<string, z.infer<typeof CELL_VALUE_SCHEMA>>,
  headers: string[],
) {
  const unknownHeaders = Object.keys(values).filter(
    (header) => !headers.includes(header),
  )
  if (unknownHeaders.length) {
    throw new Error(
      `Unknown Google Sheets columns: ${unknownHeaders.join(", ")}. Available columns: ${headers.join(", ")}.`,
    )
  }
  return headers.map((header) => values[header] ?? null)
}

/**
 * Builds an A1 range from a sheet title and one-based coordinates.
 *
 * @param sheetTitle - Human-readable sheet title.
 * @param range - Optional one-based row and column bounds.
 * @param range.endColumn - Inclusive one-based final column.
 * @param range.endRow - Inclusive one-based final row.
 * @param range.startColumn - Inclusive one-based first column.
 * @param range.startRow - Inclusive one-based first row.
 */
export function buildA1Range(
  sheetTitle: string,
  range: {
    endColumn?: number
    endRow?: number
    startColumn?: number
    startRow?: number
  },
) {
  const end = `${range.endColumn ? columnNumberToLetter(range.endColumn) : ""}${range.endRow ?? ""}`
  return `${quoteSheetTitle(sheetTitle)}!${
    range.startColumn ? columnNumberToLetter(range.startColumn) : ""
  }${range.startRow ?? ""}${end ? `:${end}` : ""}`
}

/**
 * Groups one-based row numbers into consecutive inclusive ranges.
 *
 * @param rowNumbers - Unordered one-based row numbers.
 */
export function createConsecutiveRowRanges(rowNumbers: number[]) {
  return [...new Set(rowNumbers)]
    .toSorted((left, right) => left - right)
    .reduce<{ endRow: number; startRow: number }[]>((ranges, rowNumber) => {
      const last = ranges.at(-1)
      if (last?.endRow === rowNumber - 1) {
        last.endRow = rowNumber
      } else {
        ranges.push({ endRow: rowNumber, startRow: rowNumber })
      }
      return ranges
    }, [])
}

/**
 * Normalizes a provider grid range.
 *
 * @param range - Provider grid range.
 * @throws When the provider omits the sheet ID.
 */
function normalizeGridRange(range: sheets_v4.Schema$GridRange) {
  if (range.sheetId == null) {
    throw new Error("Google returned a grid range without a sheet ID.")
  }
  return {
    endColumnIndex: range.endColumnIndex ?? undefined,
    endRowIndex: range.endRowIndex ?? undefined,
    sheetId: range.sheetId,
    startColumnIndex: range.startColumnIndex ?? undefined,
    startRowIndex: range.startRowIndex ?? undefined,
  }
}

/**
 * Ensures row actions have usable, unambiguous headers.
 *
 * @param headers - Trimmed source headers.
 * @throws When headers are empty, blank, or duplicated.
 */
function validateHeaders(headers: string[]) {
  if (!headers.length || headers.some((header) => !header)) {
    throw new Error("Google Sheets row actions require non-empty headers.")
  }
  const duplicates = headers.filter(
    (header, index) => headers.indexOf(header) !== index,
  )
  if (duplicates.length) {
    throw new Error(
      `Google Sheets row actions require unique headers. Duplicates: ${[...new Set(duplicates)].join(", ")}.`,
    )
  }
}

/**
 * Quotes and escapes a sheet title for A1 notation.
 *
 * @param sheetTitle - Human-readable sheet title.
 */
function quoteSheetTitle(sheetTitle: string) {
  return `'${sheetTitle.replaceAll("'", "''")}'`
}

/**
 * Converts a one-based column number to A1 letters.
 *
 * @param columnNumber - One-based column number.
 */
function columnNumberToLetter(columnNumber: number) {
  let column = columnNumber
  let result = ""
  while (column > 0) {
    column--
    result = String.fromCharCode(65 + (column % 26)) + result
    column = Math.floor(column / 26)
  }
  return result
}

/**
 * Maps the provider's native table column type to the public enum.
 *
 * @param type - Provider column type.
 * @throws When the provider returns an unknown column type.
 */
function toPublicTableColumnType(
  type: string | null | undefined,
): z.infer<typeof TABLE_COLUMN_TYPE_SCHEMA> {
  switch (type) {
    case "BOOLEAN":
      return "boolean"
    case "CURRENCY":
      return "currency"
    case "DATE":
      return "date"
    case "DATE_TIME":
      return "dateTime"
    case "DROPDOWN":
      return "dropdown"
    case "FILES_CHIP":
      return "file"
    case "FINANCE_CHIP":
      return "finance"
    case "DOUBLE":
      return "number"
    case "PERCENT":
      return "percent"
    case "PEOPLE_CHIP":
      return "person"
    case "PLACE_CHIP":
      return "place"
    case "RATINGS_CHIP":
      return "rating"
    case "TEXT":
      return "text"
    case "TIME":
      return "time"
    case "COLUMN_TYPE_UNSPECIFIED":
    case null:
    case undefined:
      return "unspecified"
    default:
      throw new Error(`Google returned unknown table column type "${type}".`)
  }
}