import * as z from "zod"
import { defineAction } from "../../../automation/actions"
import {
  getGoogleSheetsApi,
  normalizeSpreadsheetId,
  readSourceRows,
  resolveRowSource,
} from "../lib/google-sheets"
import { GOOGLE_SHEETS_READ_REQUIREMENT } from "../lib/scopes"
import {
  ROW_FILTER_SCHEMA,
  ROW_SOURCE_SCHEMA,
  SHEET_ROW_SCHEMA,
  SPREADSHEET_REFERENCE_SCHEMA,
  VALUE_RENDER_SCHEMA,
} from "../lib/schemas"

/** Finds header-keyed rows matching every supplied filter. */
export const findGoogleSheetsRows = defineAction("Find Google Sheets rows")
  .describe("Scans a sheet or native table and returns matching rows.")
  .account("google", GOOGLE_SHEETS_READ_REQUIREMENT)
  .input(
    z.object({
      /** Filters combined with logical AND. */
      filters: ROW_FILTER_SCHEMA.array().min(1),

      /** Maximum matching rows returned. */
      limit: z.number().int().min(1).max(10_000).optional(),

      /** Header-backed sheet or native table. */
      source: ROW_SOURCE_SCHEMA,

      /** Spreadsheet ID or standard Google Sheets URL. */
      spreadsheet: SPREADSHEET_REFERENCE_SCHEMA,

      /** How formulas and formatted cells are represented and compared. */
      valueRender: VALUE_RENDER_SCHEMA.optional(),
    }),
  )
  .output(SHEET_ROW_SCHEMA.array())
  .retry({ replaySafety: "safe" })
  .handler(async ({ account, input }) => {
    const sheetsApi = getGoogleSheetsApi(account.secret)
    const spreadsheetId = normalizeSpreadsheetId(input.spreadsheet)
    const source = await resolveRowSource(
      sheetsApi,
      spreadsheetId,
      input.source,
    )
    const unknownColumns = input.filters
      .map(({ column }) => column)
      .filter((column) => !source.headers.includes(column))
    if (unknownColumns.length) {
      throw new Error(
        `Unknown Google Sheets columns: ${[...new Set(unknownColumns)].join(", ")}. Available columns: ${source.headers.join(", ")}.`,
      )
    }

    return (
      await readSourceRows(sheetsApi, spreadsheetId, source, {
        valueRender: input.valueRender,
      })
    )
      .filter(({ values }) =>
        input.filters.every((filter) =>
          matchesFilter(values[filter.column] ?? null, filter),
        ),
      )
      .slice(0, input.limit ?? 100)
  })

/**
 * Tests one cell value against a structured row filter.
 *
 * @param value - Cell value being tested.
 * @param filter - Structured filter.
 */
function matchesFilter(
  value: string | number | boolean | null,
  filter: z.infer<typeof ROW_FILTER_SCHEMA>,
) {
  switch (filter.operator) {
    case "equals":
      return value === filter.value
    case "notEquals":
      return value !== filter.value
    case "contains": {
      const actual = String(value ?? "")
      return filter.caseSensitive
        ? actual.includes(filter.value)
        : actual.toLowerCase().includes(filter.value.toLowerCase())
    }
    case "matchesRegex":
      return new RegExp(filter.value, filter.caseSensitive ? "" : "i").test(
        String(value ?? ""),
      )
    case "greaterThan":
      return typeof value === "number" && value > filter.value
    case "greaterThanOrEqual":
      return typeof value === "number" && value >= filter.value
    case "lessThan":
      return typeof value === "number" && value < filter.value
    case "lessThanOrEqual":
      return typeof value === "number" && value <= filter.value
    case "empty":
      return value === null || value === ""
    case "notEmpty":
      return value !== null && value !== ""
    case "isTrue":
      return value === true
    case "isFalse":
      return value === false
  }
}