import _ from "lodash"
import moment from "moment"

// List of test expressions with their value. Each is { expr:, value: }. Can also have context to use to test field, scalar and aggregates
// Value can be truth function
const testExprs: any = []
export default testExprs

function add(expr: any, value: any, context?: any) {
  return testExprs.push({ expr, value, context })
}

// Compare two polygon coordinate arrays, allowing different starting index and ring direction
function polygonsEqual(actual: any, expected: any): boolean {
  if (!actual || !expected) return false
  if (actual.type !== "Polygon" || expected.type !== "Polygon") return false
  const actRings = actual.coordinates
  const expRings = expected.coordinates
  if (actRings.length !== expRings.length) return false

  const normalizeRing = (ring: number[][]) => {
    // remove final closing point if present (same as first)
    const r = ring.slice()
    if (r.length > 1) {
      const first = r[0]
      const last = r[r.length - 1]
      if (first[0] === last[0] && first[1] === last[1]) {
        r.pop()
      }
    }
    // rotate so smallest lexicographic coordinate first for stable compare
    const idx = r.reduce((best, p, i) => {
      const b = r[best]
      if (p[0] < b[0] || (p[0] === b[0] && p[1] < b[1])) return i
      return best
    }, 0)
    const rotated = r.slice(idx).concat(r.slice(0, idx))
    return rotated
  }

  const equalRing = (a: number[][], b: number[][]) => {
    if (a.length !== b.length) return false
    const na = normalizeRing(a)
    const nb = normalizeRing(b)
    const revnb = nb.slice().reverse()
    const same = na.every((p, i) => p[0] === nb[i][0] && p[1] === nb[i][1])
    const sameRev = na.every((p, i) => p[0] === revnb[i][0] && p[1] === revnb[i][1])
    return same || sameRev
  }

  for (let i = 0; i < actRings.length; i++) {
    if (!equalRing(actRings[i], expRings[i])) return false
  }
  return true
}

function literal(value: any, type: any) {
  return {
    type: "literal",
    valueType: type,
    value
  }
}

// Adds a test for an op. Pass result, op, exprs
function addOp(result: any, op: any, ...exprs: any[]) {
  return add({ type: "op", op, exprs }, result)
}

// Create sample rows for testing
function makeRow(data: any) {
  return {
    getPrimaryKey() {
      return Promise.resolve(data.id)
    },
    getField(columnId: any) {
      return Promise.resolve(data[columnId])
    },
    followJoin(columnId: any) {
      return Promise.resolve(data["join:" + columnId])
    }
  }
}

// Null
add(null, null)

// Literal
add(literal(3, "number"), 3)

// === Basic ops
addOp(5, "+", literal(3, "number"), literal(2, "number"))
addOp(3, "+", literal(3, "number"), literal(null, "number")) // Note that is different from SQL standard in null handling

addOp(2, "-", literal(3, "number"), literal(1, "number"))
addOp(null, "-", literal(3, "number"), literal(null, "number"))

addOp(6, "*", literal(3, "number"), literal(2, "number"))
addOp(null, "*", literal(3, "number"), literal(null, "number"))

addOp(3, "/", literal(6, "number"), literal(2, "number"))
addOp(null, "/", literal(6, "number"), literal(null, "number"))
// Divide by zero gives null to prevent SQL errors
addOp(null, "/", literal(6, "number"), literal(0, "number"))

// === and or not
addOp(true, "and", literal(true, "boolean"), literal(true, "boolean"), literal(true, "boolean"))
addOp(false, "and", literal(true, "boolean"), literal(true, "boolean"), literal(false, "boolean"))
addOp(null, "and")

// Null handling matches SQL
addOp(null, "and", literal(true, "boolean"), literal(true, "boolean"), literal(null, "boolean"))
addOp(false, "and", literal(false, "boolean"), literal(true, "boolean"), literal(null, "boolean"))

addOp(true, "or", literal(true, "boolean"), literal(true, "boolean"), literal(false, "boolean"))
addOp(false, "or", literal(false, "boolean"), literal(false, "boolean"), literal(false, "boolean"))
addOp(null, "or")

// Null handling matches SQL
addOp(null, "or", literal(false, "boolean"), literal(null, "boolean"))
addOp(true, "or", literal(true, "boolean"), literal(null, "boolean"))

// Null handling different than SQL!
addOp(false, "not", literal(true, "boolean"))
addOp(true, "not", literal(false, "boolean"))
addOp(true, "not", literal(null, "boolean"))

addOp(false, "=", literal(1, "number"), literal(2, "number"))
addOp(true, "=", literal(2, "number"), literal(2, "number"))
addOp(null, "=", literal(2, "number"), literal(null, "number"))
addOp(true, "=", literal("a", "text"), literal("a", "text"))

addOp(false, ">", literal(2, "number"), literal(2, "number"))
addOp(true, ">", literal(2, "number"), literal(1, "number"))
addOp(null, ">", literal(2, "number"), literal(null, "number"))

addOp(true, ">=", literal(2, "number"), literal(2, "number"))
addOp(true, ">=", literal(2, "number"), literal(1, "number"))
addOp(null, ">=", literal(2, "number"), literal(null, "number"))

addOp(false, "<", literal(2, "number"), literal(2, "number"))
addOp(true, "<", literal(1, "number"), literal(2, "number"))
addOp(null, "<", literal(1, "number"), literal(null, "number"))

addOp(true, "<=", literal(2, "number"), literal(2, "number"))
addOp(true, "<=", literal(1, "number"), literal(2, "number"))
addOp(null, "<=", literal(1, "number"), literal(null, "number"))

addOp(false, "<>", literal(2, "number"), literal(2, "number"))
addOp(true, "<>", literal(1, "number"), literal(2, "number"))
addOp(null, "<>", literal(1, "number"), literal(null, "number"))

addOp(true, "~*", literal("abc", "text"), literal("ab", "text"))
addOp(true, "~*", literal("ABC", "text"), literal("ab", "text"))
addOp(false, "~*", literal("C", "text"), literal("ab", "text"))

addOp(true, "~", literal("abc", "text"), literal("ab", "text"))
addOp(false, "~", literal("ABC", "text"), literal("ab", "text"))
addOp(false, "~", literal("C", "text"), literal("ab", "text"))

addOp(true, "= false", literal(false, "boolean"))
addOp(false, "= false", literal(true, "boolean"))

addOp(false, "is null", literal(false, "boolean"))
addOp(true, "is null", literal(null, "boolean"))

addOp(true, "is not null", literal(false, "boolean"))
addOp(false, "is not null", literal(null, "boolean"))

addOp(true, "= any", literal("a", "enum"), literal(["a", "b"], "enumset"))
addOp(false, "= any", literal("a", "enum"), literal(["c", "b"], "enumset"))
addOp(null, "= any", literal(null, "enum"), literal(["c", "b"], "enumset"))

addOp(true, "between", literal(3, "number"), literal(2, "number"), literal(4, "number"))
addOp(true, "between", literal(2, "number"), literal(2, "number"), literal(4, "number"))
addOp(false, "between", literal(1, "number"), literal(2, "number"), literal(4, "number"))

addOp(3.4, "greatest", literal(3.4, "number"), literal(3.2, "number"))
addOp(3.4, "greatest", literal(3.2, "number"), literal(3.4, "number"))
addOp(3.4, "greatest", literal(3.4, "number"), literal(null, "number"))

addOp(3.2, "least", literal(3.4, "number"), literal(3.2, "number"))
addOp(3.2, "least", literal(3.2, "number"), literal(3.4, "number"))
addOp(3.2, "least", literal(3.2, "number"), literal(null, "number"))

addOp(4, "round", literal(3.6, "number"))

addOp(3, "round", literal(3.4, "number"))
addOp(4, "round", literal(3.6, "number"))

addOp(3, "floor", literal(3.6, "number"))

addOp(4, "ceiling", literal(3.6, "number"))

// Square root
addOp(4, "sqrt", literal(16, "number"))
addOp(3, "sqrt", literal(9, "number"))
addOp(0, "sqrt", literal(0, "number"))
addOp(null, "sqrt", literal(-1, "number"))
addOp(null, "sqrt", literal(null, "number"))

// Cube root
addOp(3, "cbrt", literal(27, "number"))
addOp(-2, "cbrt", literal(-8, "number"))
addOp(null, "cbrt", literal(null, "number"))

// Power (exponent)
addOp(8, "power", literal(2, "number"), literal(3, "number"))
addOp(1, "power", literal(5, "number"), literal(0, "number"))
addOp(0.25, "power", literal(2, "number"), literal(-2, "number"))
addOp(-8, "power", literal(-2, "number"), literal(3, "number")) // negative base with integer exponent is valid
addOp(null, "power", literal(-2, "number"), literal(0.5, "number")) // negative base with non-integer exponent yields complex
addOp(null, "power", literal(null, "number"), literal(2, "number"))
addOp(null, "power", literal(2, "number"), literal(null, "number"))

// Logarithm (base 10)
addOp(2, "log10", literal(100, "number"))
addOp(1, "log10", literal(10, "number"))
addOp(0, "log10", literal(1, "number"))
addOp(null, "log10", literal(-1, "number"))
addOp(null, "log10", literal(0, "number"))
addOp(null, "log10", literal(null, "number"))

// Natural logarithm
addOp((v: number) => Math.abs(v - 1) < 0.0001, "ln", literal(Math.E, "number"))
addOp(0, "ln", literal(1, "number"))
addOp(null, "ln", literal(-1, "number"))
addOp(null, "ln", literal(0, "number"))
addOp(null, "ln", literal(null, "number"))

addOp("ab", "concat", literal("ab", "text"), literal(null, "text"))
addOp("abcd", "concat", literal("ab", "text"), literal("cd", "text"))

addOp(2, "latitude", literal({ type: "Point", coordinates: [1, 2] }, "geometry"))
addOp(1, "longitude", literal({ type: "Point", coordinates: [1, 2] }, "geometry"))

addOp(null, "altitude", literal({ type: "Point", coordinates: [1, 2] }, "geometry"))
addOp(5, "altitude", literal({ type: "Point", coordinates: [1, 2, 5] }, "geometry"))
// TODO: add support for non-points to all three ops
// addOp(7, "altitude", literal({ type: "LineString", coordinates: [[1, 2, 5], [2, 3, 7]] }, "geometry"))

addOp(
  (v: any) => v > 310000 && v < 320000,
  "line length",
  literal(
    {
      type: "LineString",
      coordinates: [
        [1, 2],
        [3, 4]
      ]
    },
    "geometry"
  )
)

addOp(
  (v: any) => v > 310000 && v < 320000,
  "line length",
  literal(
    {
      type: "LineString",
      coordinates: [
        [1, 2, 0],
        [3, 4, 0]
      ]
    },
    "geometry"
  )
)

addOp(
  (v: any) => v > 310000 && v < 320000,
  "line length",
  literal(
    {
      type: "LineString",
      coordinates: [
        [1, 2, 100000],
        [3, 4, 0]
      ]
    },
    "geometry"
  )
)

addOp(
  (v: any) => v > 310000 && v < 320000,
  "line length",
  literal(
    {
      type: "LineString",
      coordinates: [
        [1, 2, 100000],
        [3, 4, 100000]
      ]
    },
    "geometry"
  )
)

addOp(
  (v: any) => v > 320000,
  "line length",
  literal(
    {
      type: "LineString",
      coordinates: [
        [1, 2, 100000],
        [3, 4, 1]
      ]
    },
    "geometry"
  )
)


addOp(
  (v: any) => v > 12300000000 && v < 12400000000,
  "polygon area",
  literal(
    {
      type: "Polygon",
      coordinates: [
        [
          [0, 0],
          [1, 0],
          [1, 1],
          [0, 1]
        ]
      ]
    },
    "geometry"
  )
)

addOp(
  0,
  "distance",
  literal({ type: "Point", coordinates: [1, 2] }, "geometry"),
  literal({ type: "Point", coordinates: [1, 2] }, "geometry")
)
addOp(
  (v: any) => v > 310000 && v < 320000,
  "distance",
  literal({ type: "Point", coordinates: [1, 2] }, "geometry"),
  literal({ type: "Point", coordinates: [3, 4] }, "geometry")
)

addOp(30, "days difference", literal("2015-12-31", "date"), literal("2015-12-01", "date"))
addOp(
  1.5,
  "days difference",
  literal("2016-06-23T17:36:51.412Z", "datetime"),
  literal("2016-06-22T05:36:51.412Z", "datetime")
)
addOp(null, "days difference", literal("2016-06-23T17:36:51.412Z", "datetime"), literal(null, "datetime"))
addOp(30, "days difference", literal("2015-12-31T00:00:00Z", "date"), literal("2015-12-01", "date"))
addOp(30.5, "days difference", literal("2015-12-31T12:00:00Z", "date"), literal("2015-12-01", "date"))

addOp(30 / 30.5, "months difference", literal("2015-12-31", "date"), literal("2015-12-01", "date"))
addOp(
  30 / 30.5,
  "months difference",
  literal("2016-07-22T05:36:51.412Z", "datetime"),
  literal("2016-06-22T05:36:51.412Z", "datetime")
)
addOp(null, "months difference", literal("2016-06-23T17:36:51.412Z", "datetime"), literal(null, "datetime"))

addOp(1, "years difference", literal("2015-12-01", "date"), literal("2014-12-01", "date"))
addOp(
  1,
  "years difference",
  literal("2015-07-22T05:36:51.412Z", "datetime"),
  literal("2014-07-22T05:36:51.412Z", "datetime")
)
addOp(null, "years difference", literal("2016-06-23T17:36:51.412Z", "datetime"), literal(null, "datetime"))

addOp((v: any) => v > 0.9 && v < 1.1, "days since", literal(moment().subtract(1, "days").toISOString(), "datetime"))

addOp(true, "contains", literal(["a", "b", "c"], "enumset"), literal(["a", "b"], "enumset"))
addOp(false, "contains", literal(["a", "b", "c"], "enumset"), literal(["a", "b", "d"], "enumset"))
addOp(false, "contains", literal(["a", "b"], "enumset"), literal(["a", "b", "c"], "enumset"))
addOp(null, "contains", literal(null, "enumset"), literal(["c", "b"], "enumset"))

addOp(true, "intersects", literal(["a", "b", "c"], "enumset"), literal(["a", "x"], "enumset"))
addOp(false, "intersects", literal(["a", "b", "c"], "enumset"), literal(["d"], "enumset"))

addOp(true, "intersects", literal(["a", "b", "c"], "text[]"), literal(["a", "x"], "text[]"))
addOp(false, "intersects", literal(["a", "b", "c"], "text[]"), literal(["d"], "text[]"))

addOp(true, "includes", literal(["a", "b", "c"], "text[]"), literal("a", "text"))
addOp(false, "includes", literal(["a", "b", "c"], "enumset"), literal("d", "enum"))

// Length of null returns 0 as enumsets are not stored as [] when empty, but rather as null
addOp(2, "length", literal(["a", "b"], "enumset"))
addOp(0, "length", literal(null, "enumset"))

const sampleRow = makeRow({ enum: "a" })
add({ type: "op", table: "t1", op: "to text", exprs: [{ type: "field", table: "t1", column: "enum" }] }, "A", {
  row: sampleRow
})

addOp("2.5", "to text", literal(2.5, "number"))

addOp("a, b", "to text", literal(["a", "b"], "text[]"))

addOp(2.5, "to number", literal("2.5", "text"))
addOp(null, "to number", literal("x2.5", "text"))
addOp(null, "to number", literal("2.5x", "text"))

addOp(true, "thisyear", literal(moment().subtract(1, "minutes").format("YYYY-MM-DD"), "date"))
addOp(false, "thisyear", literal(moment().subtract(1, "minutes").add(1, "years").format("YYYY-MM-DD"), "date"))

addOp(false, "lastyear", literal(moment().subtract(1, "minutes").format("YYYY-MM-DD"), "date"))
addOp(true, "lastyear", literal(moment().subtract(1, "minutes").subtract(1, "years").format("YYYY-MM-DD"), "date"))

addOp(true, "thismonth", literal(moment().subtract(1, "minutes").format("YYYY-MM-DD"), "date"))
addOp(false, "thismonth", literal(moment().subtract(1, "minutes").add(1, "years").format("YYYY-MM-DD"), "date"))

addOp(false, "lastmonth", literal(moment().subtract(1, "minutes").format("YYYY-MM-DD"), "date"))
addOp(true, "lastmonth", literal(moment().subtract(1, "minutes").subtract(1, "months").format("YYYY-MM-DD"), "date"))

addOp(true, "today", literal(moment().subtract(1, "minutes").format("YYYY-MM-DD"), "date"))
addOp(false, "today", literal(moment().subtract(1, "minutes").add(1, "years").format("YYYY-MM-DD"), "date"))

addOp(false, "yesterday", literal(moment().subtract(1, "minutes").format("YYYY-MM-DD"), "date"))
addOp(true, "yesterday", literal(moment().subtract(1, "minutes").subtract(1, "days").format("YYYY-MM-DD"), "date"))

addOp(false, "last7days", literal(moment().subtract(1, "minutes").add(3, "days").toISOString(), "date"))
addOp(true, "last7days", literal(moment().subtract(1, "minutes").subtract(1, "days").format("YYYY-MM-DD"), "date"))

addOp(false, "last30days", literal(moment().subtract(1, "minutes").add(3, "days").toISOString(), "date"))
addOp(true, "last30days", literal(moment().subtract(1, "minutes").subtract(1, "days").format("YYYY-MM-DD"), "date"))

addOp(false, "last365days", literal(moment().subtract(1, "minutes").add(3, "days").toISOString(), "date"))
addOp(true, "last365days", literal(moment().subtract(1, "minutes").subtract(1, "days").format("YYYY-MM-DD"), "date"))

addOp(true, "thisyear", literal(new Date().toISOString(), "datetime"))
addOp(false, "thisyear", literal(moment().subtract(1, "minutes").add(1, "years").toISOString(), "datetime"))

addOp(false, "lastyear", literal(new Date().toISOString(), "datetime"))
addOp(true, "lastyear", literal(moment().subtract(1, "minutes").subtract(1, "years").toISOString(), "datetime"))

addOp(true, "thismonth", literal(new Date().toISOString(), "datetime"))
addOp(false, "thismonth", literal(moment().subtract(1, "minutes").add(1, "years").toISOString(), "datetime"))

addOp(false, "lastmonth", literal(new Date().toISOString(), "datetime"))
addOp(true, "lastmonth", literal(moment().subtract(1, "minutes").subtract(1, "months").toISOString(), "datetime"))

addOp(true, "today", literal(new Date().toISOString(), "datetime"))
addOp(false, "today", literal(moment().subtract(1, "minutes").add(1, "years").toISOString(), "datetime"))

addOp(false, "yesterday", literal(new Date().toISOString(), "datetime"))
addOp(true, "yesterday", literal(moment().subtract(1, "minutes").subtract(1, "days").toISOString(), "datetime"))

addOp(false, "last24hours", literal(moment().subtract(1, "minutes").subtract(1, "days").toISOString(), "datetime"))
addOp(true, "last24hours", literal(moment().add(1, "minutes").subtract(1, "days").toISOString(), "datetime"))

addOp(false, "last7days", literal(moment().subtract(1, "minutes").add(3, "days").toISOString(), "datetime"))
addOp(true, "last7days", literal(moment().subtract(1, "minutes").subtract(1, "days").toISOString(), "datetime"))

addOp(false, "last30days", literal(moment().subtract(1, "minutes").add(3, "days").toISOString(), "datetime"))
addOp(true, "last30days", literal(moment().subtract(1, "minutes").subtract(1, "days").toISOString(), "datetime"))

addOp(false, "last365days", literal(moment().subtract(1, "minutes").add(3, "days").toISOString(), "datetime"))
addOp(true, "last365days", literal(moment().subtract(1, "minutes").subtract(1, "days").toISOString(), "datetime"))

addOp(false, "last12months", literal(moment().subtract(1, "minutes").add(3, "days").toISOString(), "datetime"))
addOp(false, "last12months", literal(moment().subtract(1, "minutes").subtract(12, "months").toISOString(), "datetime"))
addOp(true, "last12months", literal(moment().subtract(1, "minutes").subtract(3, "days").toISOString(), "datetime"))

addOp(false, "last6months", literal(moment().subtract(1, "minutes").add(3, "days").toISOString(), "datetime"))
addOp(false, "last6months", literal(moment().subtract(1, "minutes").subtract(6, "months").toISOString(), "datetime"))
addOp(true, "last6months", literal(moment().subtract(1, "minutes").subtract(3, "days").toISOString(), "datetime"))

addOp(false, "last3months", literal(moment().subtract(1, "minutes").add(3, "days").toISOString(), "datetime"))
addOp(false, "last3months", literal(moment().subtract(1, "minutes").subtract(3, "months").toISOString(), "datetime"))
addOp(true, "last3months", literal(moment().subtract(1, "minutes").subtract(3, "days").toISOString(), "datetime"))

addOp(true, "future", literal(moment().add(1, "minutes").toISOString(), "datetime"))
addOp(false, "future", literal(moment().subtract(1, "minutes").toISOString(), "datetime"))

addOp(false, "notfuture", literal(moment().add(1, "minutes").toISOString(), "datetime"))
addOp(true, "notfuture", literal(moment().subtract(1, "minutes").toISOString(), "datetime"))

addOp(true, "future", literal(moment().add(1, "days").format("YYYY-MM-DD"), "date"))
addOp(false, "future", literal(moment().format("YYYY-MM-DD"), "date"))

addOp(false, "notfuture", literal(moment().add(1, "days").format("YYYY-MM-DD"), "date"))
addOp(true, "notfuture", literal(moment().format("YYYY-MM-DD"), "date"))

addOp("1", "weekofmonth", literal("2015-05-07", "date"))
addOp("2", "weekofmonth", literal("2015-05-08", "date"))

addOp("1", "weekofmonth", literal("2015-05-07", "datetime"))
addOp("2", "weekofmonth", literal("2015-05-08", "datetime"))

addOp("07", "dayofmonth", literal("2015-05-07", "date"))

addOp("1", "dayofweek", literal("2023-12-31", "date"))  // Sunday
addOp("2", "dayofweek", literal("2024-01-01", "date"))  // Monday
addOp("3", "dayofweek", literal("2024-01-02", "date"))  // Tuesday
addOp("4", "dayofweek", literal("2024-01-03", "date"))  // Wednesday
addOp("5", "dayofweek", literal("2024-01-04", "date"))  // Thursday
addOp("6", "dayofweek", literal("2024-01-05", "date"))  // Friday
addOp("7", "dayofweek", literal("2024-01-06", "date"))  // Saturday

addOp("1", "dayofweek", literal("2023-12-31", "datetime"))  // Sunday
addOp("2", "dayofweek", literal("2024-01-01", "datetime"))  // Monday
addOp("3", "dayofweek", literal("2024-01-02", "datetime"))  // Tuesday
addOp("4", "dayofweek", literal("2024-01-03", "datetime"))  // Wednesday
addOp("5", "dayofweek", literal("2024-01-04", "datetime"))  // Thursday
addOp("6", "dayofweek", literal("2024-01-05", "datetime"))  // Friday
addOp("7", "dayofweek", literal("2024-01-06", "datetime"))  // Saturday


addOp("05", "month", literal("2015-05-08", "date"))
addOp("05", "month", literal("2015-05-08", "datetime"))

addOp("2015-05-01", "yearmonth", literal("2015-05-08", "date"))
addOp("2015-05-01", "yearmonth", literal("2015-05-08", "datetime"))

addOp("2015-01-01", "year", literal("2015-05-08", "date"))
addOp("2015-01-01", "year", literal("2015-05-08", "datetime"))

addOp("2015-01-01", "year", literal("2015-05-08", "date"))
addOp("2015-01-01", "year", literal("2015-05-08", "datetime"))

addOp("2015-1", "yearquarter", literal("2015-01-08", "date"))
addOp("2015-4", "yearquarter", literal("2015-12-08", "datetime"))

// Fiscal year tests
addOp("FY2025", "fiscalyear", literal("2024-07-01", "date"), literal("07", "enum"))
addOp("FY2024", "fiscalyear", literal("2024-06-30", "date"), literal("07", "enum"))
addOp("FY2024", "fiscalyear", literal("2024-01-15", "datetime"), literal("01", "enum"))
addOp("FY2025", "fiscalyear", literal("2024-12-31", "datetime"), literal("07", "enum"))
addOp("FY2024", "fiscalyear", literal("2024-01-01", "date"), literal("04", "enum"))

// Fiscal quarter tests
addOp("FY2025/Q1", "fiscalquarter", literal("2024-07-01", "date"), literal("07", "enum"))
addOp("FY2025/Q2", "fiscalquarter", literal("2024-10-01", "date"), literal("07", "enum"))
addOp("FY2025/Q3", "fiscalquarter", literal("2025-01-01", "date"), literal("07", "enum"))
addOp("FY2025/Q4", "fiscalquarter", literal("2025-04-01", "date"), literal("07", "enum"))
addOp("FY2024/Q4", "fiscalquarter", literal("2024-06-30", "date"), literal("07", "enum"))
addOp("FY2024/Q1", "fiscalquarter", literal("2024-01-15", "datetime"), literal("01", "enum"))

addOp("2015-01", "yearweek", literal("2015-01-04", "date"))
addOp("2015-02", "yearweek", literal("2015-01-05", "datetime"))

addOp("01", "weekofyear", literal("2015-01-04", "date"))
addOp("02", "weekofyear", literal("2015-01-05", "datetime"))

addOp(moment().format("YYYY-MM-DD"), "current date")
addOp((d: any) => d.startsWith(moment().toISOString().substr(0, 10)), "current datetime")

addOp("2015-01-02", "to date", literal("2015-01-02T12:34:56Z", "datetime"))

let sampleRows = [
  makeRow({ id: "1", a: 1, b: 1, c: true, d: true, e: "x", f: 1, ordering: 3 }),
  makeRow({ id: "2", a: 2, b: 4, c: false, d: true, e: "y", f: null, ordering: 4 }),
  makeRow({ id: "3", a: 3, b: 9, c: true, d: true, e: "z", f: 3, ordering: 1 }),
  makeRow({ id: "4", a: 4, b: 16, c: false, d: false, e: "z", f: 4, ordering: 2 })
]

add({ type: "field", table: "t1", column: "a" }, 4, { row: makeRow({ a: 4 }) })

// expression columns
add({ type: "field", table: "t1", column: "expr_number" }, 4, { row: makeRow({ number: 4 }) })

add({ type: "id", table: "t1" }, "1", { row: makeRow({ id: "1", a: 4 }) })

add({ type: "op", table: "t1", op: "sum", exprs: [{ type: "field", table: "t1", column: "a" }] }, 10, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "avg", exprs: [{ type: "field", table: "t1", column: "a" }] }, 2.5, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "min", exprs: [{ type: "field", table: "t1", column: "a" }] }, 1, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "max", exprs: [{ type: "field", table: "t1", column: "a" }] }, 4, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "count", exprs: [] }, 4, { rows: sampleRows })

// TODO: Doesn't work for evaluating correctly as is a window function. Use 100 for now
add({ type: "op", table: "t1", op: "percent", exprs: [] }, 100, { rows: sampleRows })

add({ type: "op", table: "t1", op: "last", exprs: [{ type: "field", table: "t1", column: "a" }] }, 2, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "last", exprs: [{ type: "field", table: "t1", column: "f" }] }, 1, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "last", exprs: [{ type: "field", table: "t1", column: "f" }, { type: "field", table: "t1", column: "b" }] }, 4, {
  rows: sampleRows
})

add({ type: "op", table: "t1", op: "previous", exprs: [{ type: "field", table: "t1", column: "a" }] }, 1, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "previous", exprs: [{ type: "field", table: "t1", column: "f" }, { type: "field", table: "t1", column: "b" }] }, 3, {
  rows: sampleRows
})

add(
  {
    type: "op",
    table: "t1",
    op: "last where",
    exprs: [
      { type: "field", table: "t1", column: "a" },
      { type: "field", table: "t1", column: "c" }
    ]
  },
  1,
  { rows: sampleRows }
)
add({ type: "op", table: "t1", op: "last where", exprs: [{ type: "field", table: "t1", column: "a" }, null] }, 2, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "last where", exprs: [{ type: "field", table: "t1", column: "f" }, null] }, 1, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "last where", exprs: [{ type: "field", table: "t1", column: "f" }, null, { type: "field", table: "t1", column: "b" }] }, 4, {
  rows: sampleRows
})

add({ type: "op", table: "t1", op: "first", exprs: [{ type: "field", table: "t1", column: "a" }] }, 3, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "first", exprs: [{ type: "field", table: "t1", column: "f" }] }, 3, {
  rows: sampleRows
})

add(
  {
    type: "op",
    table: "t1",
    op: "first where",
    exprs: [
      { type: "field", table: "t1", column: "a" },
      { type: "field", table: "t1", column: "c" }
    ]
  },
  3,
  { rows: sampleRows }
)
add(
  {
    type: "op",
    table: "t1",
    op: "first where",
    exprs: [
      { type: "field", table: "t1", column: "a" },
      { type: "op", table: "t1", op: "not", exprs: [{ type: "field", table: "t1", column: "c" }] }
    ]
  },
  4,
  { rows: sampleRows }
)
add({ type: "op", table: "t1", op: "first where", exprs: [{ type: "field", table: "t1", column: "a" }, null] }, 3, {
  rows: sampleRows
})
add({ type: "op", table: "t1", op: "first where", exprs: [{ type: "field", table: "t1", column: "f" }, null] }, 3, {
  rows: sampleRows
})

add({ type: "op", table: "t1", op: "count where", exprs: [{ type: "field", table: "t1", column: "c" }] }, 2, {
  rows: sampleRows
})

add(
  {
    type: "op",
    table: "t1",
    op: "sum where",
    exprs: [
      { type: "field", table: "t1", column: "a" },
      { type: "field", table: "t1", column: "c" }
    ]
  },
  4,
  { rows: sampleRows }
)

add(
  {
    type: "op",
    table: "t1",
    op: "min where",
    exprs: [
      { type: "field", table: "t1", column: "a" },
      { type: "field", table: "t1", column: "c" }
    ]
  },
  1,
  { rows: sampleRows }
)

add(
  {
    type: "op",
    table: "t1",
    op: "max where",
    exprs: [
      { type: "field", table: "t1", column: "a" },
      { type: "field", table: "t1", column: "c" }
    ]
  },
  3,
  { rows: sampleRows }
)

add({ type: "op", table: "t1", op: "percent where", exprs: [{ type: "field", table: "t1", column: "c" }] }, 50, {
  rows: sampleRows
})
add(
  {
    type: "op",
    table: "t1",
    op: "percent where",
    exprs: [
      { type: "field", table: "t1", column: "c" },
      { type: "field", table: "t1", column: "d" }
    ]
  },
  (v: any) => Math.abs(v - 200 / 3) < 0.1,
  { rows: sampleRows }
)

add({ type: "op", table: "t1", op: "count distinct", exprs: [{ type: "field", table: "t1", column: "e" }] }, 3, {
  rows: sampleRows
})

add(
  { type: "op", table: "t1", op: "array_agg", exprs: [{ type: "field", table: "t1", column: "e" }] },
  ["x", "y", "z", "z"],
  { rows: sampleRows }
)

// Row with join
const singleJoinRow = makeRow({ j: "j1", "join:j": makeRow({ id: "j1", a: 1, b: 2 }) })

add({ type: "scalar", joins: ["j"], expr: { type: "field", table: "t2", column: "b" } }, 2, { row: singleJoinRow })

add({ type: "field", table: "t1", column: "j" }, "j1", { row: singleJoinRow })

// Row with join
const multipleJoinRow = makeRow({ "join:j": sampleRows, j: ["1", "2", "3", "4"] })

add(
  {
    type: "scalar",
    joins: ["j"],
    expr: { type: "op", table: "t2", op: "sum", exprs: [{ type: "field", table: "t2", column: "a" }] }
  },
  10,
  { row: multipleJoinRow }
)

add({ type: "field", table: "t1", column: "j" }, ["1", "2", "3", "4"], { row: multipleJoinRow })

// Row with joins
let multipleJoinsRow = makeRow({ "join:j1": makeRow({ "join:j2": sampleRows }) })
add(
  {
    type: "scalar",
    joins: ["j1", "j2"],
    expr: { type: "op", table: "t3", op: "sum", exprs: [{ type: "field", table: "t3", column: "a" }] }
  },
  10,
  { row: multipleJoinsRow }
)

multipleJoinsRow = makeRow({ j1: null, "join:j1": null })
add(
  {
    type: "scalar",
    joins: ["j1", "j2"],
    expr: { type: "op", table: "t3", op: "sum", exprs: [{ type: "field", table: "t3", column: "a" }] }
  },
  null,
  { row: multipleJoinsRow }
)

// Scalar with no row
add({ type: "scalar", joins: ["j"], expr: { type: "field", table: "t2", column: "b" } }, null, {
  row: makeRow({ "join:j": null, j: null })
})

// addOpItem(op: "within", name: "in", resultType: "boolean", exprTypes: ["id", "id"], lhsCond: (lhsExpr, exprUtils) =>
//   lhsIdTable = exprUtils.getExprIdTable(lhsExpr)
//   if lhsIdTable
//     return exprUtils.schema.getTable(lhsIdTable).ancestry?
//   return false
// )

add(
  {
    type: "case",
    cases: [
      { when: literal(true, "boolean"), then: { type: "literal", valueType: "number", value: 1 } },
      { when: literal(true, "boolean"), then: { type: "literal", valueType: "number", value: 2 } }
    ],
    else: { type: "literal", valueType: "number", value: 3 }
  },
  1
)

add(
  {
    type: "case",
    cases: [
      { when: literal(false, "boolean"), then: { type: "literal", valueType: "number", value: 1 } },
      { when: literal(true, "boolean"), then: { type: "literal", valueType: "number", value: 2 } }
    ],
    else: { type: "literal", valueType: "number", value: 3 }
  },
  2
)

add(
  {
    type: "case",
    cases: [
      { when: literal(false, "boolean"), then: { type: "literal", valueType: "number", value: 1 } },
      { when: literal(false, "boolean"), then: { type: "literal", valueType: "number", value: 2 } }
    ],
    else: { type: "literal", valueType: "number", value: 3 }
  },
  3
)

add(
  {
    type: "score",
    input: literal("a", "enum"),
    scores: {
      a: { type: "literal", valueType: "number", value: 3 },
      b: { type: "literal", valueType: "number", value: 4 }
    }
  },
  3
)

add(
  {
    type: "score",
    input: literal("c", "enum"),
    scores: {
      a: { type: "literal", valueType: "number", value: 3 },
      b: { type: "literal", valueType: "number", value: 4 }
    }
  },
  0
)

add(
  {
    type: "score",
    input: literal(["a"], "enumset"),
    scores: {
      a: { type: "literal", valueType: "number", value: 3 },
      b: { type: "literal", valueType: "number", value: 4 }
    }
  },
  3
)

add(
  {
    type: "score",
    input: literal(["a", "b"], "enumset"),
    scores: {
      a: { type: "literal", valueType: "number", value: 3 },
      b: { type: "literal", valueType: "number", value: 4 }
    }
  },
  7
)

// Build enumset
add(
  {
    type: "build enumset",
    values: {
      a: { type: "literal", valueType: "boolean", value: true },
      b: { type: "literal", valueType: "boolean", value: false },
      c: { type: "literal", valueType: "boolean", value: true }
    }
  },
  ["a", "c"]
)

// is latest
sampleRows = [
  makeRow({ id: "1", a: "x", b: true, c: 1, ordering: 3 }),
  makeRow({ id: "2", a: "y", b: true, c: 2, ordering: 4 }),
  makeRow({ id: "3", a: "x", b: false, c: 3, ordering: 1 }),
  makeRow({ id: "4", a: "y", b: false, c: 4, ordering: 2 })
]

add({ type: "op", op: "is latest", table: "t1", exprs: [{ type: "field", table: "t1", column: "a" }] }, true, {
  row: sampleRows[0],
  rows: sampleRows
})
add({ type: "op", op: "is latest", table: "t1", exprs: [{ type: "field", table: "t1", column: "a" }] }, true, {
  row: sampleRows[1],
  rows: sampleRows
})
add({ type: "op", op: "is latest", table: "t1", exprs: [{ type: "field", table: "t1", column: "a" }] }, false, {
  row: sampleRows[2],
  rows: sampleRows
})
add({ type: "op", op: "is latest", table: "t1", exprs: [{ type: "field", table: "t1", column: "a" }] }, false, {
  row: sampleRows[3],
  rows: sampleRows
})
add({ type: "op", op: "is latest", table: "t1", exprs: [{ type: "field", table: "t1", column: "a" }, null, { type: "field", table: "t1", column: "c" }] }, true, {
  row: sampleRows[3],
  rows: sampleRows
})
add({ type: "op", op: "is latest", table: "t1", exprs: [{ type: "field", table: "t1", column: "a" }, null, { type: "field", table: "t1", column: "c" }] }, false, {
  row: sampleRows[1],
  rows: sampleRows
})

// expression columns
add({ type: "field", table: "t1", column: "expr_number" }, 4, { row: makeRow({ number: 4 }) })

// variable values
add({ type: "variable", variableId: "varnumber" }, 123, { row: makeRow({ number: 4 }) })

// extensions
add({ type: "extension", extension: "test" }, 4)

//   describe "scalar", ->
//     it "n-1 scalar", ->
//       @check({ type: "scalar", joins: ['x'], expr: { type: "field", table: "t2", column: "y" }}, { x: { getField: (col) -> (if col == "y" then 4) }}, 4)

//     it "n-1 null scalar", ->
//       @check({ type: "scalar", joins: ['x'], expr: { type: "field", table: "t2", column: "y" }}, { x: null }, null)

// Add after other geometry operation tests

// Test centroid of point (should return same point)
addOp(
  { type: "Point", coordinates: [1, 2] },
  "centroid",
  literal({ type: "Point", coordinates: [1, 2] }, "geometry")
)

// Test centroid of polygon
addOp(
  { type: "Point", coordinates: [0.5, 0.5] },
  "centroid",
  literal({
    type: "Polygon",
    coordinates: [
      [
        [0, 0],
        [1, 0],
        [1, 1],
        [0, 1],
        [0, 0]  // Polygon needs to close
      ]
    ]
  }, "geometry")
)

// Test centroid of linestring
addOp(
  { type: "Point", coordinates: [2, 3] },
  "centroid",
  literal({
    type: "LineString", 
    coordinates: [
      [1, 2],
      [2, 3],
      [3, 4]
    ]
  }, "geometry")
)

// Test null handling
addOp(
  null,
  "centroid",
  literal(null, "geometry") 
)

addOp(2015, "year number", literal("2015-12-01", "date"))
addOp(2015, "year number", literal("2015-07-22T05:36:51.412Z", "datetime"))
addOp(null, "year number", literal(null, "datetime"))

// === Geometry union tests
// Test union of two overlapping polygons
addOp(
  (value: any) => polygonsEqual(value, {
    type: "Polygon",
    coordinates: [
      [
        // L-shape union of [0,0]-[2,2] and [1,1]-[3,3]
        [0, 0], [2, 0], [2, 1], [3, 1], [3, 3], [1, 3], [1, 2], [0, 2], [0, 0]
      ]
    ]
  }),
  "geometry union",
  literal({
    type: "Polygon",
    coordinates: [
      [
        [0, 0], [2, 0], [2, 2], [0, 2], [0, 0]
      ]
    ]
  }, "geometry"),
  literal({
    type: "Polygon",
    coordinates: [
      [
        [1, 1], [3, 1], [3, 3], [1, 3], [1, 1]
      ]
    ]
  }, "geometry")
)

// Test union with null
addOp(
  null,
  "geometry union",
  literal(null, "geometry"),
  literal({ type: "Point", coordinates: [1, 2] }, "geometry")
)

// === Geometry intersect tests  
// Test intersection of two overlapping polygons
addOp(
  (value: any) => polygonsEqual(value, {
    type: "Polygon",
    coordinates: [
      [
        [1, 1], [2, 1], [2, 2], [1, 2], [1, 1]
      ]
    ]
  }),
  "geometry intersect",
  literal({
    type: "Polygon",
    coordinates: [
      [
        [0, 0], [2, 0], [2, 2], [0, 2], [0, 0]
      ]
    ]
  }, "geometry"),
  literal({
    type: "Polygon",
    coordinates: [
      [
        [1, 1], [3, 1], [3, 3], [1, 3], [1, 1]
      ]
    ]
  }, "geometry")
)

// Test intersection with null
addOp(
  null,
  "geometry intersect",
  literal(null, "geometry"),
  literal({ type: "Point", coordinates: [1, 2] }, "geometry")
)

// Test intersection of non-overlapping polygons (should return null)
addOp(
  null,
  "geometry intersect",
  literal({
    type: "Polygon",
    coordinates: [
      [
        [0, 0],
        [1, 0],
        [1, 1],
        [0, 1],
        [0, 0]
      ]
    ]
  }, "geometry"),
  literal({
    type: "Polygon",
    coordinates: [
      [
        [2, 2],
        [3, 2],
        [3, 3],
        [2, 3],
        [2, 2]
      ]
    ]
  }, "geometry")
)

// === Geometry buffer tests
// Test buffering a point
addOp(
  (value: any) => value && value.type === "Polygon",  // Buffering a point should create a polygon
  "geometry buffer",
  literal({ type: "Point", coordinates: [0, 0] }, "geometry"),
  literal(100, "number")  // 100 meters
)

// Test buffer with null geometry
addOp(
  null,
  "geometry buffer",
  literal(null, "geometry"),
  literal(100, "number")
)

// Test buffer with null distance
addOp(
  null,
  "geometry buffer",
  literal({ type: "Point", coordinates: [0, 0] }, "geometry"),
  literal(null, "number")
)

// === Geometry union aggregate tests
// Create sample rows with geometry data
const geoRows = [
  makeRow({ 
    id: "1", 
    geom: { 
      type: "Polygon", 
      coordinates: [
        [
          [0, 0],
          [1, 0],
          [1, 1],
          [0, 1],
          [0, 0]
        ]
      ]
    }
  }),
  makeRow({ 
    id: "2", 
    geom: { 
      type: "Polygon", 
      coordinates: [
        [
          [0.5, 0.5],
          [1.5, 0.5],
          [1.5, 1.5],
          [0.5, 1.5],
          [0.5, 0.5]
        ]
      ]
    }
  }),
  makeRow({ 
    id: "3", 
    geom: null  // Test null handling
  })
]

// Test aggregate union of geometries
add(
  { 
    type: "op", 
    table: "t1", 
    op: "geometry union agg", 
    exprs: [{ type: "field", table: "t1", column: "geom" }] 
  }, 
  (value: any) => value && value.type === "Polygon",  // Result should be a polygon
  { rows: geoRows }
)

// Test aggregate union with all null geometries
const nullGeoRows = [
  makeRow({ id: "1", geom: null }),
  makeRow({ id: "2", geom: null })
]

add(
  { 
    type: "op", 
    table: "t1", 
    op: "geometry union agg", 
    exprs: [{ type: "field", table: "t1", column: "geom" }] 
  }, 
  null,
  { rows: nullGeoRows }
)