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

      Helper.query(a_tester, { query: "SELECT id, name, ref:value FROM fcftest_singleref_test_join LIMIT ${1}", args: [1], fullLog: false}, (a_records) =>{
        uniTest.equal(a_records,
                [ [ {
                    "id": 1,
                    "name": "first",
                    "ref": 2,
                    "@key": 1,
                  } ] ]
              );
      });


      Helper.query(a_tester, { query: "SELECT id, name, ref FROM fcftest_singleref_test_join LIMIT ${1}", args: [1], fullLog: false}, (a_records) =>{
        uniTest.equal(a_records,
                [ [ {
                    "id": 1,
                    "name": "first",
                    "ref": {
                      "index": 2,
                      "entity_name": "second entity",
                      "@key": 2,
                      "@title": "second entity"
                    },
                    "@key": 1,
                  } ] ]
              );
      });


      Helper.query(a_tester, { query: "SELECT id, name, ref:short FROM fcftest_singleref_test_join LIMIT ${1}", args: [1], fullLog: false}, (a_records) =>{
        uniTest.equal(a_records,
                [ [ {
                    "id": 1,
                    "name": "first",
                    "ref": {
                      "@key": 2,
                      "@title": "second entity"
                    },
                    "@key": 1,
                  } ] ]
              );
      });

      Helper.query(a_tester, { query: "SELECT id, name, ref->entity_name FROM fcftest_singleref_test_join LIMIT ${1}", args: [1], fullLog: false}, (a_records) =>{
        uniTest.equal(a_records,
                [ [ {
                    "id": 1,
                    "name": "first",
                    "ref->entity_name":  "second entity",
                    "@key": 1
                  } ] ]
              );
      });

      Helper.query(a_tester, { query: "SELECT id, name, ref->@title FROM fcftest_singleref_test_join LIMIT ${1}", args: [1], fullLog: false}, (a_records) =>{
        uniTest.equal(a_records,
                [ [ {
                    "id": 1,
                    "name": "first",
                    "ref->@title":  "second entity",
                    "@key": 1
                  } ] ]
              );
      });


      Helper.query(a_tester, { query: "SELECT id, name, ref FROM fcftest_singleref_test_join WHERE ref = ${1}", args: [3], fullLog: false}, (a_records) =>{
        uniTest.equal(a_records,
                [ [ {
                    "id": 4,
                    "name": "fourth",
                    "ref": {
                      "index": 3,
                      "entity_name": "thrid entity",
                      "@key": 3,
                      "@title": "thrid entity"
                    },
                    "@key": 4,
                  } ] ]
              );
      });


      Helper.query(a_tester, { query: "SELECT id, name, ref FROM fcftest_singleref_test_join WHERE ref->@title = ${1}", args: ["thrid entity"], fullLog: false}, (a_records) =>{
        uniTest.equal(a_records,
                [ [ {
                    "id": 4,
                    "name": "fourth",
                    "ref": {
                      "index": 3,
                      "entity_name": "thrid entity",
                      "@key": 3,
                      "@title": "thrid entity"
                    },
                    "@key": 4,
                  } ] ]
              );
      });

      Helper.query(a_tester, { query: "SELECT id, name, ref FROM fcftest_singleref_test_join WHERE ref->@key = ${1}", args: [3], fullLog: false}, (a_records) =>{
        uniTest.equal(a_records,
                [ [ {
                    "id": 4,
                    "name": "fourth",
                    "ref": {
                      "index": 3,
                      "entity_name": "thrid entity",
                      "@key": 3,
                      "@title": "thrid entity"
                    },
                    "@key": 4,
                  } ] ]
              );
      });

      Helper.query(a_tester, { query: "SELECT id, name, ref FROM fcftest_singleref_test_join WHERE ref->index = ${1}", args: [3], fullLog: false}, (a_records) =>{
        uniTest.equal(a_records,
                [ [ {
                    "id": 4,
                    "name": "fourth",
                    "ref": {
                      "index": 3,
                      "entity_name": "thrid entity",
                      "@key": 3,
                      "@title": "thrid entity"
                    },
                    "@key": 4,
                  } ] ]
              );
      });

      Helper.query(a_tester, { query: "DELETE FROM fcftest_singleref_test_dynamic", fullLog: false}, (a_records) =>{
      });
      Helper.query(a_tester, { query: "DELETE FROM fcftest_singleref_test_dynamic_join ", fullLog: false}, (a_records) =>{
      });

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

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "SELECT name, ref FROM  fcftest_singleref_test_dynamic_join WHERE index = ${1}", args: [lastInsertKey], fullLog: false}, (a_records) =>{
          delete a_records[0][0]["@key"];
          uniTest.equal(
            a_records,
            [ [
                {
                  "name": "test1",
                  "ref": {
                    "index": 3,
                    "entity_name": "thrid entity",
                    "@key": 3,
                    "@title": "thrid entity"
                  }
                }
              ]
            ]
          );
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "SELECT name, ref FROM  fcftest_singleref_test_dynamic_join", args: [], fullLog: false}, (a_records) =>{
          delete a_records[0][0]["@key"];
          uniTest.equal(
            a_records,
            [ [
                {
                  "name": "test1",
                  "ref": {
                    "index": 3,
                    "entity_name": "thrid entity",
                    "@key": 3,
                    "@title": "thrid entity"
                  }
                }
              ]
            ]
          );
        });
      });

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "DELETE FROM fcftest_singleref_test_dynamic_join ", fullLog: false}, (a_records) =>{
        });
      });

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

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


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

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "SELECT name, ref FROM  fcftest_singleref_test_dynamic_join", args: [], fullLog: false}, (a_records) =>{
          delete a_records[0][0]["@key"];
          uniTest.equal(
            a_records,
            [ [ {
              "name": "test1",
              "ref": {
                "index": 2,
                "entity_name": "second entity",
                "@key": 2,
                "@title": "second entity"
              }
            } ] ]
          );
        });
      });

    });
  }
});
