fcf.module({
  name: "fcf:tests/NFSQL/DB.Field.BackRef.js",
  dependencies: ["fcf:NTest/uniTest.js", "fcf:tests/NFSQL/Helpers/Helper.js"],
  module: function(uniTest, Helper){
    uniTest.add("FSQL DB" , "DB Field BackRef", async function(a_tester) {
      let records;
      
      records = await Helper.getStorage(a_tester).query(
        { 
          query: "SELECT id, title, backref FROM fcftest_backref_test WHERE id = ${1}", 
          args: [1], 
          fullLog: false
        }, 
      );
      uniTest.equal(
        records[0],
        [ {
          "id": 1,
          "title": "first",
          "backref": [
            {
              "id": 1,
              "title": "first reference",
              "ref": {
                "@key": 1,
                "@title": "1"
              },
              "@key": 1,
              "@title": "1"
            }
          ],
          "@key": 1,
        } ]
      );


      records = await Helper.getStorage(a_tester).query(
        { 
          query: "SELECT id, title, backref FROM fcftest_backref_test WHERE id = ${1}", 
          args: [3]
        }, 
      );
      uniTest.equal(
        records[0],
        [
          {
            "id": 3,
            "title": "third",
            "backref": [],
            "@key": 3
          }
        ]
      );

      records = await Helper.getStorage(a_tester).query(
        { 
          query: "SELECT backref->title, backref->id, backref->@key, backref->@title FROM fcftest_backref_test WHERE id = ${1}", 
          args: [2], 
        }, 
      );
      uniTest.equal(
        records[0],
        [ {
          "@key": 2,
          "backref->title": [
            { "title": "first reference", "@key": 2 },
            { "title": "second reference", "@key": 3 },
            { "title": "third reference", "@key": 4 }
          ],
          "backref->id": [
            { "id": 2, "@key": 2 },
            { "id": 3, "@key": 3 },
            { "id": 4, "@key": 4 }
          ],
          "backref->@key": [
            { "@key": 2, },
            { "@key": 3, },
            { "@key": 4, }
          ],
          "backref->@title": [
            { "@title": "2", "@key": 2 },
            { "@title": "3", "@key": 3 },
            { "@title": "4", "@key": 4 }
          ]
        } ]
      );


      records = await Helper.getStorage(a_tester).query(
        { 
          query: "SELECT id, title, backref FROM fcftest_backref_test WHERE backref->id = ${1}", 
          args: [4], 
        }, 
      );
      uniTest.equal(
        records[0],
        [
          {
            "id": 2,
            "title": "second",
            "backref": [
              {
                "id": 2,
                "title": "first reference",
                "ref": {
                  "@key": 2,
                  "@title": "2"
                },
                "@title": "2",
                "@key": 2
              },
              {
                "id": 3,
                "title": "second reference",
                "ref": {
                  "@key": 2,
                  "@title": "2"
                },
                "@title": "3",
                "@key": 3
              },
              {
                "id": 4,
                "title": "third reference",
                "ref": {
                  "@key": 2,
                  "@title": "2"
                },
                "@title": "4",
                "@key": 4
              }
            ],
            "@key": 2
          }
        ]
      );


      records = await Helper.getStorage(a_tester).query(
        { 
          query: "SELECT id, title, backref FROM fcftest_backref_test WHERE backref->@key = ${1}", 
          args: [1], 
        }, 
      );
      uniTest.equal(
        records[0],
        [ {
          "id": 1,
          "title": "first",
          "backref": [
            {
              "id": 1,
              "title": "first reference",
              "ref": {
                "@key": 1,
                "@title": "1"
              },
              "@title": "1",
              "@key": 1
            }
          ],
          "@key": 1
        } ]
      );


      records = await Helper.getStorage(a_tester).query(
        { 
          query: "DELETE FROM fcftest_backref_test_dynamic", 
          args: [], 
        }, 
      );


      // INSERT TESTS
      records = await Helper.getStorage(a_tester).query(
        { 
          query: "INSERT INTO fcftest_backref_test_dynamic (title, backref) VALUES (${1}, ${2});" + 
                 "SELECT * FROM fcftest_backref_test_dynamic WHERE id = $[0][0][@key]", 
          args: ["insert first title", [{title: "subrecord 1"}, {title: "subrecord 2"}]], 
        }, 
      );

      var id = records[1][0]["id"];
      delete records[1][0]["backref"][0]["id"];
      delete records[1][0]["backref"][0]["@key"];
      delete records[1][0]["backref"][0]["@title"];
      delete records[1][0]["backref"][1]["id"];
      delete records[1][0]["backref"][1]["@key"];
      delete records[1][0]["backref"][1]["@title"];

      uniTest.equal(
        records[1][0],
        {
          "id": id,
          "title": "insert first title",
          "backref": [
            {
              "title": "subrecord 1",
              "ref": {
                "@key": id,
                "@title": id,
              },
            },
            {
              "title": "subrecord 2",
              "ref": {
                "@key": id,
                "@title": id
              },
            }
          ],
          "@title": id,
          "@key": id
        }
      );

      records = await Helper.getStorage(a_tester).query(
        {
          query: "DELETE FROM fcftest_backref_test_dynamic", 
          args: [], 
        }, 
      );

      records = await Helper.getStorage(a_tester).query(
        {
          query: "SELECT * FROM fcftest_backref_test_dynamic", 
          args: [], 
        }, 
      );
      uniTest.equal(records[0], []);


      records = await Helper.getStorage(a_tester).query(
        {
          query: "SELECT * FROM fcftest_backref_test_ref_dynamic", 
          args: [], 
        }, 
      );
      uniTest.equal(records[0], []);


      records = await Helper.getStorage(a_tester).query(
        {
          query: "INSERT INTO fcftest_backref_test_dynamic (title, backref) VALUES (${1}, ${2});" + 
                 "SELECT * FROM fcftest_backref_test_dynamic WHERE id = $[0][0][@key]", 
          args: ["insert first title", [{title: "subrecord 1"}, {title: "subrecord 2"}]], 
        }, 
      );

      var newRecords = await Helper.getStorage(a_tester).query(
        {
          query: "UPDATE fcftest_backref_test_dynamic SET backref = ${1} WHERE id = ${2};" + 
                 "SELECT * FROM fcftest_backref_test_dynamic WHERE id = ${2}", 
          args: [
            [
              {"@key": records[1][0].backref[0]["@key"], title: "new title 1"},
              {"@key": records[1][0].backref[1]["@key"], title: "new title 2"},
              {title: "new title 3"},
            ], 
            records[1][0].id
          ], 
        }, 
      );

      fcf.each(newRecords[1][0].backref, (k, a_backref)=>{
        delete a_backref["id"];
        delete a_backref["@key"];
        delete a_backref["@title"];
      });
      uniTest.equal(newRecords[1], [
        {
          "id": records[1][0].id,
          "title": "insert first title",
          "backref": [
            {
              "title": "new title 1",
              "ref": {
                "@key": records[1][0].id,
                "@title": records[1][0].id
              },
            },
            {
              "title": "new title 2",
              "ref": {
                "@key": records[1][0].id,
                "@title": records[1][0].id
              },
            },
            {
              "title": "new title 3",
              "ref": {
                "@key": records[1][0].id,
                "@title": records[1][0].id
              },
            }
          ],
          "@title": records[1][0].id,
          "@key": records[1][0].id
        }
      ]);

      var newRecords = await Helper.getStorage(a_tester).query(
        {
          query: "UPDATE fcftest_backref_test_dynamic SET backref = ${1} WHERE id = ${2};" + 
                 "SELECT * FROM fcftest_backref_test_dynamic WHERE id = ${2}", 
          args: [
            [
              {"@key": records[1][0].backref[0]["@key"], title: "new new title 1"},
            ], 
            records[1][0].id
          ], 
        }, 
      );

      delete newRecords[1][0].backref[0]["id"];
      delete newRecords[1][0].backref[0]["@key"];
      delete newRecords[1][0].backref[0]["@title"];
      uniTest.equal(newRecords[1], [
        {
          "id": records[1][0].id,
          "title": "insert first title",
          "backref": [
            {
              "title": "new new title 1",
              "ref": {
                "@key": records[1][0].id,
                "@title": records[1][0].id
              },
            },
          ],
          "@title": records[1][0].id,
          "@key": records[1][0].id
        }
      ]);

    });

  }
});
