(*---------------------------------------------------------------------------
  Copyright (c) 2025 Thomas Gazagnaire. All rights reserved.
  SPDX-License-Identifier: ISC
  ---------------------------------------------------------------------------*)

(** Tests for {!Catalog.Value_ops}: SQLite's value-level semantics --
    storage-class ordering, three-valued logic, text conversion, aggregates and
    affinity. *)

module V = Catalog.Value_ops
module Val = Catalog.Value

let vstr : Val.t -> string = function
  | Null -> "NULL"
  | Int i -> Fmt.str "int:%Ld" i
  | Float f -> Fmt.str "float:%.6g" f
  | Text s -> "text:" ^ s
  | Blob b -> "blob:" ^ b

let value =
  Alcotest.testable (Fmt.of_to_string vstr) (fun a b -> vstr a = vstr b)

let sign n = compare n 0

let test_compare_storage_classes () =
  (* NULL < number < text < blob, numbers compared exactly across int/real. *)
  Alcotest.(check int) "NULL < int" (-1) (sign (V.compare_values Null (Int 1L)));
  Alcotest.(check int)
    "int < text" (-1)
    (sign (V.compare_values (Int 9L) (Text "a")));
  Alcotest.(check int)
    "text < blob" (-1)
    (sign (V.compare_values (Text "z") (Blob "a")));
  Alcotest.(check int)
    "int equals the same real" 0
    (sign (V.compare_values (Int 5L) (Float 5.0)));
  Alcotest.(check int)
    "int ordering" (-1)
    (sign (V.compare_values (Int 1L) (Int 2L)));
  Alcotest.(check int)
    "3 < 3.5 across int and real" (-1)
    (sign (V.compare_values (Int 3L) (Float 3.5)));
  (* int64 max must sort BELOW the double it would round up to, not equal it *)
  Alcotest.(check int)
    "int64 max < 2^63 as a double" (-1)
    (sign (V.compare_values (Int Int64.max_int) (Float 9223372036854775808.0)))

(* Collated comparison: BINARY by bytes, NOCASE folds ASCII case, RTRIM ignores
   trailing spaces; the collation is irrelevant to a non-text operand. The
   sequence is resolved before it gets here -- {!Sql.Collation.resolve} reads
   the name, and refuses one nothing carries. *)
let test_collation () =
  let seq = Sql.Collation.resolve in
  let eq name a b = V.compare_values_coll (seq name) (Text a) (Text b) = 0 in
  Alcotest.(check bool) "NOCASE folds case" true (eq "NOCASE" "abc" "ABC");
  Alcotest.(check bool) "BINARY keeps case" false (eq "BINARY" "abc" "ABC");
  Alcotest.(check bool)
    "RTRIM ignores trailing spaces" true (eq "RTRIM" "a  " "a");
  Alcotest.(check bool) "RTRIM keeps leading spaces" false (eq "RTRIM" " a" "a");
  Alcotest.(check int)
    "collation is irrelevant to ints" 0
    (V.compare_values_coll (seq "NOCASE") (Int 1L) (Int 1L));
  Alcotest.(check bool)
    "a name is matched without regard to case" true (eq "nocase" "abc" "ABC");
  (* collation_key gives values equal under the collation a shared form *)
  Alcotest.(check value)
    "NOCASE key folds case" (Text "abc")
    (V.collation_key (seq "NOCASE") (Text "ABC"));
  Alcotest.(check value)
    "collation_key leaves a non-text value alone" (Int 7L)
    (V.collation_key (seq "NOCASE") (Int 7L));
  Alcotest.check_raises "an unknown collation raises"
    (Failure "no such collation sequence: BOGUS") (fun () ->
      ignore (seq "BOGUS"))

let test_text_of_value () =
  Alcotest.(check string) "int" "42" (V.text_of_value (Int 42L));
  Alcotest.(check string) "null renders empty" "" (V.text_of_value Null);
  Alcotest.(check string)
    "real keeps a point" "1.5"
    (V.text_of_value (Float 1.5));
  Alcotest.(check string)
    "a whole real keeps .0" "2.0"
    (V.text_of_value (Float 2.0));
  Alcotest.(check string) "text is itself" "hi" (V.text_of_value (Text "hi"))

let test_text_of_real_matches_sqlite3 () =
  let case expected f =
    Alcotest.(check string)
      (Fmt.str "real %h" f) expected
      (V.text_of_value (Float f))
  in
  case "1.5" 1.5;
  case "2.0" 2.0;
  case "0.1" 0.1;
  case "100000000000000.0" 1e14;
  case "1000000000000000.0" 1e15;
  case "10000000000000000.0" 1e16;
  case "Inf" Float.infinity;
  case "-Inf" Float.neg_infinity;
  case "NaN" Float.nan;
  (* and the frontend must not disagree with the engine core about any of it *)
  List.iter
    (fun f ->
      Alcotest.(check string)
        (Fmt.str "agrees with Catalog.Value.to_text on %h" f)
        (Catalog.Value.to_text (Float f))
        (V.text_of_value (Float f)))
    [
      1.5;
      2.0;
      0.1;
      1e14;
      1e15;
      1e16;
      Float.infinity;
      Float.neg_infinity;
      Float.nan;
    ]

let tstr = function V.T -> "T" | V.F -> "F" | V.U -> "U"
let tri = Alcotest.testable (Fmt.of_to_string tstr) (fun a b -> tstr a = tstr b)

let test_three_valued_logic () =
  Alcotest.(check tri) "NULL is unknown" V.U (V.tri_of Null);
  Alcotest.(check tri) "zero is false" V.F (V.tri_of (Int 0L));
  Alcotest.(check tri) "non-zero is true" V.T (V.tri_of (Int 5L));
  Alcotest.(check tri) "true AND false is false" V.F (V.tri_and V.T V.F);
  Alcotest.(check tri) "true AND unknown is unknown" V.U (V.tri_and V.T V.U);
  Alcotest.(check tri) "false AND unknown is false" V.F (V.tri_and V.F V.U);
  Alcotest.(check tri) "false OR unknown is unknown" V.U (V.tri_or V.F V.U);
  Alcotest.(check tri) "true OR unknown is true" V.T (V.tri_or V.T V.U);
  Alcotest.(check tri) "not unknown is unknown" V.U (V.tri_not V.U);
  (* text is truthy by the number it leads with, not by being non-empty *)
  Alcotest.(check tri) "'5x' coerces to true" V.T (V.tri_of (Text "5x"));
  Alcotest.(check tri) "'abc' coerces to false" V.F (V.tri_of (Text "abc"));
  (* not_value is the same logic as a value: NULL stays NULL *)
  Alcotest.(check value) "NOT 0 is 1" (Int 1L) (V.not_value (Int 0L));
  Alcotest.(check value) "NOT 5 is 0" (Int 0L) (V.not_value (Int 5L));
  Alcotest.(check value) "NOT NULL is NULL" Null (V.not_value Null)

let test_is_value () =
  Alcotest.(check value) "NULL IS NULL is 1" (Int 1L) (V.is_value Null Null);
  Alcotest.(check value) "NULL IS 1 is 0" (Int 0L) (V.is_value Null (Int 1L));
  Alcotest.(check value) "1 IS 1 is 1" (Int 1L) (V.is_value (Int 1L) (Int 1L))

let test_numeric_agg () =
  Alcotest.(check value)
    "integer sum stays integer" (Int 6L)
    (V.numeric_agg "sum" [ Int 1L; Int 2L; Int 3L ]);
  Alcotest.(check value)
    "avg is real" (Float 2.0)
    (V.numeric_agg "avg" [ Int 1L; Int 3L ]);
  Alcotest.(check value)
    "a sum with a real is real" (Float 6.0)
    (V.numeric_agg "sum" [ Int 1L; Float 2.0; Int 3L ]);
  Alcotest.(check value)
    "max uses storage-class order" (Int 5L)
    (V.numeric_agg "max" [ Int 1L; Int 5L; Int 3L ]);
  Alcotest.(check value)
    "min uses storage-class order" (Int 1L)
    (V.numeric_agg "min" [ Int 3L; Int 1L; Int 2L ]);
  Alcotest.(check value) "empty sum is NULL" Null (V.numeric_agg "sum" []);
  Alcotest.(check value)
    "empty total is 0.0" (Float 0.0) (V.numeric_agg "total" []);
  (* an integer sum stays integer until it cannot, and then it raises *)
  Alcotest.check_raises "an int64 sum overflow raises"
    (Failure "integer overflow") (fun () ->
      ignore (V.numeric_agg "sum" [ Int Int64.max_int; Int 1L ]))

let test_like_and_glob () =
  Alcotest.(check bool) "% matches a run" true (V.like_match "a%" "abc");
  Alcotest.(check bool) "_ matches one char" true (V.like_match "a_c" "abc");
  Alcotest.(check bool)
    "LIKE is case-insensitive (ASCII)" true (V.like_match "A%" "abc");
  Alcotest.(check bool) "no match" false (V.like_match "a%" "xyz");
  Alcotest.(check bool) "% is not anchored" true (V.like_match "%b_" "abc");
  Alcotest.(check bool)
    "_ matches one char only" false (V.like_match "a_" "abc");
  Alcotest.(check bool) "* matches a run" true (V.glob_match "a*" "abc");
  Alcotest.(check bool) "? matches one char" true (V.glob_match "a?c" "abc");
  Alcotest.(check bool) "GLOB is case-sensitive" false (V.glob_match "A*" "abc");
  Alcotest.(check bool) "GLOB *.txt matches" true (V.glob_match "*.txt" "a.txt");
  Alcotest.(check bool) "GLOB [a-c] class" true (V.glob_match "[a-c]" "b");
  (* the value-level forms propagate NULL *)
  Alcotest.(check value) "LIKE NULL is NULL" Null (V.like_op (Text "abc") Null);
  Alcotest.(check value)
    "'a' || 'b' is 'ab'" (Text "ab")
    (V.concat_op (Text "a") (Text "b"))

(* The two numeric coercions: arithmetic affinity (non-numeric text becomes 0)
   against comparison affinity (non-numeric text stays text, so it equals no
   number), plus the integer coercion the bitwise operators apply. *)
let test_coercion () =
  Alcotest.(check value)
    "numeric_affinity of '12' is 12" (Int 12L)
    (V.numeric_affinity (Text "12"));
  Alcotest.(check value)
    "numeric_affinity of 'abc' is 0" (Int 0L)
    (V.numeric_affinity (Text "abc"));
  Alcotest.(check value)
    "num_coerce of '12' is 12" (Int 12L) (V.num_coerce (Text "12"));
  Alcotest.(check value)
    "num_coerce of 'abc' stays text" (Text "abc")
    (V.num_coerce (Text "abc"));
  Alcotest.(check bool) "'3.5' reads as real" true (V.is_real (Text "3.5"));
  Alcotest.(check bool) "'3' does not read as real" false (V.is_real (Text "3"));
  Alcotest.(check int64) "intify 3.9 truncates to 3" 3L (V.intify (Float 3.9));
  Alcotest.(check int64) "intify '12x' reads 12" 12L (V.intify (Text "12x"))

let test_affinity_and_cast () =
  let aff = function
    | `Integer -> "int"
    | `Text -> "text"
    | `Blob -> "blob"
    | `Real -> "real"
    | `Numeric -> "num"
  in
  Alcotest.(check string)
    "INT name is integer" "int"
    (aff (V.affinity_of_type "INTEGER"));
  Alcotest.(check string)
    "VARCHAR is text" "text"
    (aff (V.affinity_of_type "VARCHAR(10)"));
  Alcotest.(check string) "empty is blob" "blob" (aff (V.affinity_of_type ""));
  Alcotest.(check string)
    "REAL is real" "real"
    (aff (V.affinity_of_type "REAL"));
  Alcotest.(check string)
    "DECIMAL is numeric" "num"
    (aff (V.affinity_of_type "DECIMAL"));
  Alcotest.(check string)
    "BIGINT is integer" "int"
    (aff (V.affinity_of_type "BIGINT"));
  Alcotest.(check value)
    "CAST text to integer" (Int 42L)
    (V.cast "INTEGER" (Text "42"));
  Alcotest.(check value)
    "CAST int to text" (Text "42") (V.cast "TEXT" (Int 42L));
  Alcotest.(check value) "CAST preserves NULL" Null (V.cast "INTEGER" Null);
  (* a CAST reads the prefix its target affinity accepts *)
  Alcotest.(check value)
    "CAST('12.9' AS INTEGER) reads the integer prefix" (Int 12L)
    (V.cast "INTEGER" (Text "12.9"));
  Alcotest.(check value)
    "CAST('3.5x' AS REAL) reads the numeric prefix" (Float 3.5)
    (V.cast "REAL" (Text "3.5x"));
  (* NUMERIC collapses an integral real to an integer *)
  Alcotest.(check value)
    "CAST('5.0' AS NUMERIC) collapses to an integer" (Int 5L)
    (V.cast "NUMERIC" (Text "5.0"));
  (* column write affinity numifies only well-formed text *)
  Alcotest.(check value)
    "apply_affinity INTEGER numifies '42'" (Int 42L)
    (V.apply_affinity "INTEGER" (Text "42"));
  Alcotest.(check value)
    "apply_affinity INTEGER keeps non-numeric text" (Text "abc")
    (V.apply_affinity "INTEGER" (Text "abc"))

let test_intify_and_distinct () =
  Alcotest.(check (list value))
    "distinct keeps first occurrences" [ Int 1L; Int 2L; Null ]
    (V.distinct_values [ Int 1L; Int 1L; Int 2L; Null; Null ]);
  (* an int and the equal real are one value, so only the first survives *)
  Alcotest.(check (list value))
    "an int and the equal real dedup" [ Int 1L ]
    (V.distinct_values [ Int 1L; Float 1.0 ])

let suite =
  ( "value_ops",
    [
      Alcotest.test_case "storage-class comparison" `Quick
        test_compare_storage_classes;
      Alcotest.test_case "text_of_value" `Quick test_text_of_value;
      Alcotest.test_case "real text matches sqlite3" `Quick
        test_text_of_real_matches_sqlite3;
      Alcotest.test_case "three-valued logic" `Quick test_three_valued_logic;
      Alcotest.test_case "IS (NULL-safe equality)" `Quick test_is_value;
      Alcotest.test_case "numeric aggregates" `Quick test_numeric_agg;
      Alcotest.test_case "LIKE and GLOB" `Quick test_like_and_glob;
      Alcotest.test_case "numeric coercion" `Quick test_coercion;
      Alcotest.test_case "collation" `Quick test_collation;
      Alcotest.test_case "affinity and CAST" `Quick test_affinity_and_cast;
      Alcotest.test_case "distinct values" `Quick test_intify_and_distinct;
    ] )