let fs = require('fs');

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


      Helper.query(a_tester, { query: "SELECT name, externalref FROM fcftest_externref_test WHERE name=${1}", args: ["second"], fullLog: false}, (a_record) =>{
        uniTest.equal(a_record, [[
          {
            "name": "second",
            "externalref": [
              {
                "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, externalref:short FROM fcftest_externref_test WHERE name=${1}", args: ["second"], fullLog: false}, (a_record) =>{
        uniTest.equal(a_record, [[
          {
            "name": "second",
            "externalref": [
              {
                "@key": 3,
                "@title": "name: entity3"
              },
              {
                "@key": 4,
                "@title": "name: entity4"
              }
            ],
            "@key": 2,
          }
        ]]);
      });

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

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

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

      Helper.query(a_tester, { query: "DELETE FROM fcftest_externref_test_dynamic", args: []}, (a_record) =>{ });

      Helper.query(a_tester, 
        { 
          query: "INSERT INTO fcftest_externref_test_dynamic (name, externalref) VALUES (${1}, ${2})", 
          args: ["test1", [1,2]]
        }, 
        (a_record) =>{
          lastInsertKey = a_record[0][0]["@key"];
        }
      );

      a_tester.actions.then(()=>{
        return Helper.query(a_tester, { actions: fcf.actions(), query: "SELECT name, externalref:short FROM fcftest_externref_test_dynamic WHERE id=${1}", args: [lastInsertKey], fullLog: false}, (a_record) =>{
          delete a_record[0][0]["@key"];
          uniTest.equal(
            a_record,
            [ [
                {
                  "name": "test1",
                  "externalref": [
                    { "@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 WHERE id = ${1}", args: [lastInsertKey], fullLog: false});
      });

      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, entity_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 (name, externalref) 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, externalref:short FROM fcftest_externref_test_dynamic WHERE id=${1}", args: [lastInsertKey], fullLog: false}, (a_record) =>{
          delete a_record[0][0]["@key"];
          uniTest.equal(
            a_record,
            [ [
                {
                  "name": "test1",
                  "externalref": [
                    { "@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 (name, externalref) 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 SET name = ${1}, externalref = ${2} WHERE id = ${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, externalref:short FROM fcftest_externref_test_dynamic WHERE id=${1}", args: [lastInsertKey], fullLog: false}, (a_record) =>{
          delete a_record[0][0]["@key"]
          uniTest.equal(
            a_record,
            [ [
              {
                "name": "test3",
                "externalref": [
                  { "@key": 2, "@title": "name: entity2" },
                  { "@key": 4, "@title": "name: entity4" }
                ]
              }
            ] ]
          );
        });
      });


    });
  }
});
