let fs = require('fs');

fcf.module({
  name: "fcf:tests/NFSQL/DB.FieldExternalRefJoin.js",
  dependencies: ["fcf:NTest/uniTest.js", "fcf:tests/NFSQL/Helpers/Helper.js"],
  module: function(uniTest, Helper) {
    uniTest.add("FSQL DB" , "DB Field ExternalRef Join", function(a_tester) {
      let lastInsertKey = undefined;


      Helper.query(a_tester, { query: "SELECT name, externalref1 FROM fcftest_externref_test_join WHERE name=${1}", args: ["join_second"], fullLog: false}, (a_record) =>{
        uniTest.equal(a_record, [[
          {
            "name": "join_second",
            "externalref1": [
              {
                "id": 3,
                "name": "entity3",
                "@key": 3,
                "@title": "name: entity3"
              },
              {
                "id": 4,
                "name": "entity4",
                "@key": 4,
                "@title": "name: entity4"
              }
            ],
            "@key": 2,
          }
        ]]);
      });

      Helper.query(a_tester, { query: "SELECT name, externalref1:short FROM fcftest_externref_test_join WHERE name=${1}", args: ["join_second"], fullLog: false}, (a_record) =>{
        uniTest.equal(a_record, [[
          {
            "name": "join_second",
            "externalref1": [
              {
                "@key": 3,
                "@title": "name: entity3"
              },
              {
                "@key": 4,
                "@title": "name: entity4"
              }
            ],
            "@key": 2,
          }
        ]]);
      });

      Helper.query(a_tester, { query: "SELECT name, externalref1:short FROM fcftest_externref_test_join WHERE externalref1->name=${1}", args: ["entity4"], fullLog: false}, (a_record) =>{
        uniTest.equal(a_record,
          [ [
            {
              "name": "join_second",
              "externalref1": [
                { "@key": 3, "@title": "name: entity3"},
                { "@key": 4, "@title": "name: entity4" }
              ],
              "@key": 2,
            },
            {
              "name": "join_fourth",
              "externalref1": [
                { "@key": 3, "@title": "name: entity3" },
                { "@key": 4, "@title": "name: entity4" }
              ],
              "@key": 4,
            }
        ] ] );
      });

      Helper.query(a_tester, { query: "SELECT name, externalref1:short FROM fcftest_externref_test_join WHERE externalref1->@key=${1}", args: [4], fullLog: false}, (a_record) =>{
        uniTest.equal(a_record,
          [ [
            {
              "name": "join_second",
              "externalref1": [
                { "@key": 3, "@title": "name: entity3"},
                { "@key": 4, "@title": "name: entity4" }
              ],
              "@key": 2,
            },
            {
              "name": "join_fourth",
              "externalref1": [
                { "@key": 3, "@title": "name: entity3" },
                { "@key": 4, "@title": "name: entity4" }
              ],
              "@key": 4,
            }
        ] ] );
      });

      Helper.query(a_tester, { query: "SELECT name, externalref1:short FROM fcftest_externref_test_join WHERE externalref1->@title=${1}", args: ["name: entity1"], fullLog: false}, (a_record) =>{
        uniTest.equal(
          a_record,
          [[
            {
              "name": "join_first",
              "externalref1": [
                {"@key": 1, "@title": "name: entity1" },
                {"@key": 3, "@title": "name: entity3" }
              ],
              "@key": 1,
            }
          ]]
        );
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "DELETE FROM fcftest_externref_test_dynamic_join", args: [], fullLog: false}, (a_record) =>{
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "DELETE FROM fcftest_externref_test_dynamic", args: [], fullLog: false}, (a_record) =>{
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "INSERT INTO fcftest_externref_test_dynamic_join (name, externalref1) VALUES (${1}, ${2})", args: ["test1", [1,2]], fullLog: false}, (a_record) =>{
          lastInsertKey = a_record[0][0]["@key"];
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "SELECT name, externalref1:short FROM fcftest_externref_test_dynamic_join WHERE index=${1}", args: [lastInsertKey], fullLog: false}, (a_record) =>{
          delete a_record[0][0]["@key"];
          uniTest.equal(
            a_record,
            [ [
                {
                  "name": "test1",
                  "externalref1": [
                    { "@key": 1, "@title": "name: entity1" },
                    { "@key": 2, "@title": "name: entity2" }
                  ]
                }
            ] ]
          );
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "DELETE FROM fcftest_externref_test_dynamic_join WHERE index = ${1}", args: [lastInsertKey], fullLog: false});
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "SELECT name, externalref1:short FROM fcftest_externref_test_dynamic_join", args: [], fullLog: false}, (a_record) =>{
          uniTest.equal(a_record, [[]]);
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "SELECT name, externalref:short FROM fcftest_externref_test_dynamic", args: [], fullLog: false}, (a_record) =>{
          uniTest.equal(a_record, [[]]);
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "SELECT self_ref FROM fcftest_externref_test_ref_dynamic", args: [], fullLog: false}, (a_record) =>{
          uniTest.equal(a_record, [[]]);
        });
      });









      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "INSERT INTO fcftest_externref_test_dynamic_join (name, externalref1) VALUES (${1}, ${2})", args: ["test1", [{id: 1}, {id: 2}]], fullLog: false}, (a_record) =>{
          lastInsertKey = a_record[0][0]["@key"];
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "SELECT name, externalref1:short FROM fcftest_externref_test_dynamic_join WHERE index=${1}", args: [lastInsertKey], fullLog: false}, (a_record) =>{
          delete a_record[0][0]["@key"];
          uniTest.equal(
            a_record,
            [ [
                {
                  "name": "test1",
                  "externalref1": [
                    { "@key": 1, "@title": "name: entity1" },
                    { "@key": 2, "@title": "name: entity2" }
                  ]
                }
            ] ]
          );
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "INSERT INTO fcftest_externref_test_dynamic_join (name, externalref1) VALUES (${1}, ${2})", args: ["test2", [3]], fullLog: false}, (a_record) =>{
          lastInsertKey = a_record[0][0]["@key"];
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "UPDATE fcftest_externref_test_dynamic_join SET name = ${1}, externalref1 = ${2} WHERE index = ${3}", args: ["test3", [2, 4], lastInsertKey], fullLog: false}, (a_record) =>{
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "SELECT name, externalref1:short FROM fcftest_externref_test_dynamic_join WHERE index=${1}", args: [lastInsertKey], fullLog: false}, (a_record) =>{
          delete a_record[0][0]["@key"];
          uniTest.equal(
            a_record,
            [ [
              {
                "name": "test3",
                "externalref1": [
                  { "@key": 2, "@title": "name: entity2" },
                  { "@key": 4, "@title": "name: entity4" }
                ]
              }
            ] ]
          );
        });
      });




    });
  }
});
