#include "ORMLite.h"
using namespace BOT_ORM;
using namespace BOT_ORM::Expression;

struct UserModel
{
    int user_id;
    std::string user_name;
    double credit_count;

    Nullable<int> age;
    Nullable<double> salary;
    Nullable<std::string> title;

    // Inject ORM-Lite into this Class :-)
    ORMAP ("UserModel", user_id, user_name, credit_count,
           age, salary, title);
};

// Open a Connection with *test.db*
ORMapper mapper ("test.db");

// Create a table for "UserModel"
mapper.CreateTbl (UserModel {});

// Drop the table "UserModel"
mapper.DropTbl (UserModel {});

std::vector<UserModel> initObjs =
{
    { 0, "John", 0.2, 21, nullptr, nullptr },
    { 1, "Jack", 0.4, nullptr, 3.14, nullptr },
    { 2, "Jess", 0.6, nullptr, nullptr, std::string ("Dr.") }
};

// Insert Values into the table
for (const auto &obj : initObjs)
    mapper.Insert (obj);

initObjs[1].salary = nullptr;
initObjs[1].title = "St.";

// Update Entity by Primary Key (WHERE UserModel.id = 1)
mapper.Update (initObjs[1]);

// Delete Entity by Primary Key (WHERE UserModel.id = 2)
mapper.Delete (initObjs[2]);

// Transactional Statements
try
{
    mapper.Transaction ([&] ()
    {
        mapper.Delete (initObjs[0]);  // OK
        mapper.Insert (UserModel { 1, "Joke", 0 });  // Failed
    });
}
catch (const std::exception &ex)
{
    // If any statement Failed, throw an exception
    // "SQL error: UNIQUE constraint failed: UserModel.id"

    // Remarks:
    // mapper.Delete (initObjs[0]); will not applied :-)
}

// Select All to List
auto result1 = mapper.Query (UserModel {}).ToList ();
//   result1 = [{ 0, 0.2, "John", 21,   null, null  },
//              { 1, 0.4, "Jack", null, null, "St." }]

std::vector<UserModel> dataToSeed;
for (int i = 50; i < 100; i++)
    dataToSeed.emplace_back (
        UserModel { i, "July_" + std::to_string (i), i * 0.2 });

// Insert by Batch Insert
mapper.Transaction ([&] () {
    mapper.InsertRange (dataToSeed);
});

for (size_t i = 0; i < 20; i++)
{
    dataToSeed[i + 30].age = 30 + (int) i / 2;
    dataToSeed[i + 20].title = "Mr. " + std::to_string (i);
}

// Update by Batch Update
mapper.Transaction ([&] () {
    mapper.UpdateRange (dataToSeed);
});

// Define a Query Helper Object and its Field Extractor
UserModel helper;
FieldExtractor field { helper };

// Select by Query
auto result2 = mapper.Query (UserModel {})
    .Where (
        field (helper.user_name) & std::string ("July%") &&
        (field (helper.age) >= 32 &&
         field (helper.title) != nullptr)
    )
    .OrderByDescending (field (helper.age))
    .OrderBy (field (helper.user_id))
    .Take (3)
    .Skip (1)
    .ToVector ();

// Remarks:
// sql = SELECT * FROM UserModel
//       WHERE (user_name LIKE 'July%' AND
//             (age>=32 AND title IS NOT NULL))
//       ORDER BY age DESC
//       ORDER BY id
//       LIMIT 3 OFFSET 1
// result2 = [{ 89, 17.8, "July_89", 34, null, "Mr. 19" },
//            { 86, 17.2, "July_86", 33, null, "Mr. 16" },
//            { 87, 17.4, "July_87", 33, null, "Mr. 17" }]

// Calculate Aggregate Function by Query
auto avg = mapper.Query (UserModel {})
    .Where (field (helper.user_name) & std::string ("July%"))
    .Select (Avg (field (helper.credit_count)));

// Remarks:
// sql = SELECT AVG (credit_count) FROM UserModel
//       WHERE (user_name LIKE 'July%')
// avg = 14.9

auto count = mapper.Query (UserModel {})
    .Where (field (helper.user_name) | std::string ("July%"))
    .Select (Count ());

// Remarks:
// sql = SELECT COUNT (*) FROM UserModel
//       WHERE (user_name NOT LIKE 'July%')
// count = 2

// Update by Condition
mapper.Update (
    UserModel {},
    (field (helper.age) = 10) &&
    (field (helper.credit_count) = 1.0),
    field (helper.user_name) == std::string ("July"));

// Remarks:
// sql = UPDATE UserModel SET age=10,credit_count=1.0
//       WHERE (user_name='July')

// Delete by Condition
mapper.Delete (UserModel {},
               field (helper.user_id) >= 90);

// Remarks:
// sql = DELETE FROM UserModel WHERE (id>=90)

// Define more Query Helper Objects and their Field Extractor
UserModel user;
SellerModel seller;
OrderModel order;
field = FieldExtractor { user, seller, order };

// Insert Values into the table
// mapper.Insert (..., false) means Insert without Primary Key
for (size_t i = 0; i < 50; i++)
    mapper.Insert (
        OrderModel { 0,
        (int) i / 2 + 50,
        (int) i / 4 + 50,
        "Item " + std::to_string (i),
        i * 0.5 }, false);

// Join Tables for Query
auto joinedQuery = mapper.Query (UserModel {})
    .Join (OrderModel {},
           field (user.user_id) ==
           field (order.user_id))
    .LeftJoin (SellerModel {},
               field (seller.seller_id) ==
               field (order.seller_id))
    .Where (field (user.user_id) >= 65);

// Get Result to List
// Results are Nullable-Tuples
auto result3 = joinedQuery.ToList ();

// Remarks:
// sql = SELECT * FROM UserModel
//       JOIN OrderModel
//       ON UserModel.user_id=OrderModel.user_id
//       LEFT JOIN SellerModel
//       ON SellerModel.seller_id=OrderModel.seller_id
//       WHERE (UserModel.user_id>=65)
// result3 = [(65, "July_65", 13, null, null, null,
//             31, 65, 57, "Item 30", 15,
//             null, null, null),
//            (65, "July_65", 13, null, null, null,
//             32, 65, 57, "Item 31", 15.5,
//             null, null, null),
//            ... ]

// Group & Having ~
// Results are Nullable-Tuples
auto result4 = joinedQuery
    .Select (field (order.user_id),
             field (user.user_name),
             Avg (field (order.fee)))
    .GroupBy (field (user.user_name))
    .Having (Sum (field (order.fee)) >= 40.5)
    .Skip (3)
    .ToList ();

// Remarks:
// sql = SELECT OrderModel.user_id,
//              UserModel.user_name,
//              AVG (OrderModel.fee)
//       FROM UserModel
//            JOIN OrderModel
//            ON UserModel.user_id=OrderModel.user_id
//            LEFT JOIN SellerModel
//            ON SellerModel.seller_id=OrderModel.seller_id
//       WHERE (UserModel.user_id>=65)
//       GROUP BY UserModel.user_name
//       HAVING SUM (OrderModel.fee)>=40.5
//       LIMIT ~0 OFFSET 3
// result4 = [(73, "July_73", 23.25),
//            (74, "July_74", 24.25)]

// Compound Select
// Results are Nullable-Tuples
auto result5 = mapper.Query (OrderModel {})
    .Select (field (order.product_name), field (order.user_id))
    .Where (field (order.user_id) == 50)
    .Union (
        joinedQuery
        .Select (field (user.user_name), field (order.order_id))
    )
    .Take (4)
    .ToList ();

// sql = SELECT OrderModel.product_name,
//              OrderModel.user_id
//       FROM OrderModel
//            WHERE (OrderModel.user_id==50)
//       UNION
//       SELECT UserModel.user_name,
//              OrderModel.order_id
//       FROM UserModel
//            JOIN OrderModel
//            ON UserModel.user_id=OrderModel.user_id
//            LEFT JOIN SellerModel
//            ON SellerModel.seller_id=OrderModel.seller_id
//            WHERE (UserModel.user_id>=65)
//       LIMIT 4;
// result5 = [("Item 0", 50),
//            ("Item 1", 50),
//            ("July_65", 31),
//            ("July_65", 32)]