open StdLabels
module A = ImportAnalyser.Dependency
module Q = ImportAnalyser.Query
module Syntax = ImporterSyntax
module Expr = Expression_builder
open Test_migration

(** This is sample configuration used in the tests *)
let conf =
  Syntax.
    {
      ConfLoader.conf with
      columns =
        [
          Concat [ Path { alias = None; column = 1 }; Literal "_"; Empty ];
          Path { alias = None; column = 2 };
          Path { alias = Some "last_file"; column = 5 };
        ];
    }

let create_table =
  "Create table" >:: fun _ ->
  let out = A.get_process_order conf in

  let query = Q.create_table (List.hd out) in

  Alcotest.check Alcotest.string ""
    "CREATE TABLE 'last' (id INTEGER PRIMARY KEY,'key_last_file','col_5')" query

let select =
  "Select" >:: fun _ ->
  let query, _ = Q.select conf in
  let expected_query =
    {|SELECT COALESCE('source'.'col_1','') || ? || '' AS result_0,
'source'.'col_2' AS result_1,
'last_file'.'col_5' AS result_2
FROM 'source' AS 'source'
LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.'col_1')) = 'other'.'key_other'
LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.'col_1')) = 'last_file'.'key_last_file'|}
  in

  Alcotest.check Alcotest.string "" expected_query query.q

let check_externals =
  "Check external" >:: fun _ ->
  let query = Q.check_external conf (List.hd conf.externals) in

  let expected_query =
    "SELECT 'source'.'id', 'source'.'col_1'\n\
     FROM 'source' AS 'source'\n\
     LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.'col_1')) = \
     'other'.'key_other' WHERE 'other'.'key_other' IS NULL AND \
     'source'.'col_1' IS NOT NULL AND 'source'.'col_1' <> ''"
  in

  Alcotest.check Alcotest.string "" expected_query query.q

let previous =
  "Test window previous" >:: fun _ ->
  (* This is sample configuration used in the tests *)
  let conf =
    Syntax.
      {
        version = 1;
        locale = None;
        source = { file = "source.xlsx"; tab = 1; name = "previous" };
        externals = [];
        columns =
          [
            Window
              ( Previous (Path { alias = None; column = 5 }),
                [ Path { alias = None; column = 1 } ],
                [ Path { alias = None; column = 3 } ] );
          ];
        filters = [];
        sort = [];
        uniq = [];
      }
  in

  let query, _ = ImportAnalyser.Query.select conf in
  let expected_query =
    "SELECT LAG('previous'.'col_5') OVER (PARTITION BY 'previous'.'col_1' \
     ORDER BY 'previous'.'col_3') AS result_0\n\
     FROM 'source' AS 'previous'"
  in
  Alcotest.check Alcotest.string "" expected_query query.q

let sum =
  "Test window sum" >:: fun _ ->
  (* This is sample configuration used in the tests *)
  let conf =
    Syntax.
      {
        version = 1;
        locale = None;
        source = { file = "source.xlsx"; tab = 1; name = "previous" };
        externals = [];
        columns =
          [
            Window
              ( Sum (Path { alias = None; column = 5 }),
                [ Path { alias = None; column = 1 } ],
                [] );
          ];
        filters = [];
        sort = [];
        uniq = [];
      }
  in

  let query, _ = ImportAnalyser.Query.select conf in
  let expected_query =
    "SELECT SUM('previous'.'col_5') OVER (PARTITION BY 'previous'.'col_1') AS \
     result_0\n\
     FROM 'source' AS 'previous'"
  in
  Alcotest.check Alcotest.string "" expected_query query.q

let sum_total =
  "Test sum over the whole range" >:: fun _ ->
  (* This is sample configuration used in the tests *)
  let conf =
    Syntax.
      {
        version = 1;
        locale = None;
        source = { file = "source.xlsx"; tab = 1; name = "previous" };
        externals = [];
        columns = [ Window (Sum (Path { alias = None; column = 5 }), [], []) ];
        filters = [];
        sort = [];
        uniq = [];
      }
  in

  let query, _ = ImportAnalyser.Query.select conf in
  let expected_query =
    "SELECT SUM('previous'.'col_5') AS result_0\nFROM 'source' AS 'previous'"
  in
  Alcotest.check Alcotest.string "" expected_query query.q

let sum_unfiltered =
  "Test sum over the whole range" >:: fun _ ->
  (* This is sample configuration used in the tests *)
  let conf =
    Syntax.
      {
        version = 1;
        locale = None;
        source = { file = "source.xlsx"; tab = 1; name = "previous" };
        externals = [];
        columns =
          [
            Window
              ( Sum (Path { alias = None; column = 5 }),
                [],
                [ Path { alias = None; column = 1 } ] );
          ];
        filters = [];
        sort = [];
        uniq = [];
      }
  in

  let query, _ = ImportAnalyser.Query.select conf in
  let expected_query =
    "SELECT SUM('previous'.'col_5') OVER (ORDER BY 'previous'.'col_1') AS \
     result_0\n\
     FROM 'source' AS 'previous'"
  in
  Alcotest.check Alcotest.string "" expected_query query.q

let prepare_insert =
  "Test prepare_insert" >:: fun _ ->
  let key =
    ImportAnalyser.Dependency.
      {
        name = "key_test";
        expression = Concat [ Path 1; Literal "_"; Empty ];
        columns = lazy (ImportContainers.IntSet.singleton 1);
      }
  in

  let buffer = Buffer.create 16 in
  let () = ImportAnalyser.Query.build_key_insert buffer key in
  let contents = Buffer.contents buffer in

  let expected = "rtrim(upper(COALESCE(:col_1,'') || '_' || ''))" in

  Alcotest.check Alcotest.string "" expected contents

(** Test a request with a group in a filter.

    This generate a CTE expression in order to evaluate the group before loading
    the results from the query. *)
let filter_group =
  "Test filter_group" >:: fun _ ->
  let c col = Expr.path ImportDataTypes.Path.{ alias = None; column = col } in
  let conf =
    {
      conf with
      columns = [ c 1 ];
      filters = [ Expr.(max (c 3) [ c 1 ] [ c 1 ]) ];
    }
  in
  let contents, _ = ImportAnalyser.Query.select conf in

  let expected =
    {|WITH filter0 AS (SELECT source.id, LAST_VALUE('source'.'col_3') OVER (PARTITION BY 'source'.'col_1' ORDER BY 'source'.'col_1' RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS group_function
FROM 'source' AS 'source'
LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.'col_1')) = 'other'.'key_other'
LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.'col_1')) = 'last_file'.'key_last_file')
SELECT 'source'.'col_1' AS result_0
FROM 'source' AS 'source'
LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.'col_1')) = 'other'.'key_other'
LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.'col_1')) = 'last_file'.'key_last_file'
INNER JOIN 'filter0' ON filter0.id = source.id
WHERE filter0.group_function|}
  in

  Alcotest.check Alcotest.string "" expected contents.q

(** Test a request with a group in a filter.

    This generate a CTE expression in order to evaluate the group before loading
    the results from the query. *)
let filter_group2 =
  "Test filter_group" >:: fun _ ->
  let c col = Expr.path ImportDataTypes.Path.{ alias = None; column = col } in
  let conf =
    {
      conf with
      columns = [ c 1 ];
      filters =
        [ Expr.(max (c 3) [ c 1 ] [ c 1 ]); Expr.equal (c 3) Expr.integer_zero ];
    }
  in
  let contents, _ = ImportAnalyser.Query.select conf in

  let expected =
    {|WITH filter0 AS (SELECT source.id, LAST_VALUE('source'.'col_3') OVER (PARTITION BY 'source'.'col_1' ORDER BY 'source'.'col_1' RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS group_function
FROM 'source' AS 'source'
LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.'col_1')) = 'other'.'key_other'
LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.'col_1')) = 'last_file'.'key_last_file')
SELECT 'source'.'col_1' AS result_0
FROM 'source' AS 'source'
LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.'col_1')) = 'other'.'key_other'
LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.'col_1')) = 'last_file'.'key_last_file'
INNER JOIN 'filter0' ON filter0.id = source.id
WHERE COALESCE('source'.'col_3',0)=0 AND filter0.group_function|}
  in

  Alcotest.check Alcotest.string "" expected contents.q

let test_suit =
  [
    create_table;
    select;
    check_externals;
    previous;
    sum;
    sum_total;
    sum_unfiltered;
    prepare_insert;
    filter_group;
    filter_group2;
  ]

let tests = "analyser_query_test" >::: test_suit