using System;
using System.Data;
using System.Linq;
using System.Text;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;
using ServiceStack.OrmLite.Dapper;
using ServiceStack.OrmLite.Tests.UseCase;
using ServiceStack.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace ServiceStack.OrmLite.Tests;

[TestFixtureOrmLite]
[NonParallelizable]
public class LoadReferencesJoinTests(DialectContext context) : OrmLiteProvidersTestBase(context)
{
    private IDbConnection db;

    [OneTimeSetUp]
    public void TestFixtureSetUp()
    {
        db = base.OpenDbConnection();
        ResetTables();
    }

    private void ResetTables()
    {
        CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table

        if(DialectFeatures.SchemaSupport) db.CreateSchema<ProjectTask>();
        db.DropAndCreateTable<Order>();
        db.DropAndCreateTable<Customer>();
        db.DropAndCreateTable<CustomerAddress>();
        db.DropAndCreateTable<Country>();
    }

    [SetUp]
    public void SetUp()
    {
        db.DeleteAll<Order>();
        db.DeleteAll<CustomerAddress>();
        db.DeleteAll<Customer>();
        db.DeleteAll<Country>();
    }

    [OneTimeTearDown]
    public void TestFixtureTearDown()
    {
        db.Dispose();
    }

    private Customer AddCustomerWithOrders()
    {
        var customer = new Customer
        {
            Name = "Customer 1",
            PrimaryAddress = new CustomerAddress
            {
                AddressLine1 = "1 Australia Street",
                Country = "Australia"
            },
            Orders = new[]
            {
                new Order {LineItem = "Line 1", Qty = 1, Cost = 1.99m},
                new Order {LineItem = "Line 2", Qty = 2, Cost = 2.99m},
            }.ToList(),
        };

        db.Save(customer, references: true);

        return customer;
    }

    [Test]
    public async Task Can_execute_LoadSelectAsync_with_OrderBy()
    {
        var customers = AddCustomersWithOrders();

        var q = db.From<Customer>()
            .OrderByFields("Id");

        var results = await db.LoadSelectAsync(q);
            
        Assert.That(results.Count, Is.GreaterThan(1));
    }

    public class FullCustomerInfo
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string CustomerName { get; set; }
        public int CustomerAddressId { get; set; }
        public string AddressLine1 { get; set; }
        public string City { get; set; }
        public int OrderId { get; set; }
        public string LineItem { get; set; }
        public decimal Cost { get; set; }
        public decimal OrderCost { get; set; }
        public string CountryCode { get; set; }
    }

    public class MixedCustomerInfo
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string AliasedCustomerName { get; set; }
        public string aliasedcustomername { get; set; }
        public int Q_CustomerId { get; set; }
        public int Q_CustomerAddressQ_CustomerId { get; set; }
        public string CountryName { get; set; }
        public int CountryId { get; set; }
    }

    [Test]
    public void Can_do_multiple_joins_with_SqlExpression()
    {
        AddCustomerWithOrders();

        var results = db.Select<FullCustomerInfo>(db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>());

        var costs = results.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 1.99m, 2.99m }));

        var expr = db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>();

        results = db.Select<FullCustomerInfo>(expr);

        costs = results.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 1.99m, 2.99m }));
    }

    [Test]
    public void Can_do_joins_with_wheres_using_SqlExpression()
    {
        AddCustomerWithOrders();

        var results = db.Select<FullCustomerInfo>(db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>((c, o) => c.Id == o.CustomerId && o.Cost < 2));

        var costs = results.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 1.99m }));

        var orders = db.Select(db.From<Order>()
            .Join<Order, Customer>()
            .Join<Customer, CustomerAddress>()
            .Where(o => o.Cost < 2)
            .And<Customer>(c => c.Name == "Customer 1"));

        costs = orders.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 1.99m }));

        results = db.Select<FullCustomerInfo>(db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>()
            .Where<Order>(o => o.Cost < 2));

        costs = results.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 1.99m }));

        results = db.Select<FullCustomerInfo>(db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>()
            .Where<Order>(o => o.Cost < 2 || o.LineItem == "Line 2"));

        costs = results.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 1.99m, 2.99m }));

        var expr = db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>()
            .Where<Order>(o => o.Cost < 2 || o.LineItem == "Line 2");
        results = db.Select<FullCustomerInfo>(expr);

        costs = results.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 1.99m, 2.99m }));
    }

    [Test]
    public void Can_do_joins_with_complex_wheres_using_SqlExpression()
    {
        var customers = AddCustomersWithOrders();

        db.Insert(
            new Country { CountryName = "Australia", CountryCode = "AU" },
            new Country { CountryName = "USA", CountryCode = "US" });

        var results = db.Select<FullCustomerInfo>(db.From<Customer>()
            .Join<CustomerAddress>() //implicit
            .Join<Customer, Order>() //explicit
            .Where(c => c.Name == "Customer 1")
            .And<Order>(o => o.Cost < 2)
            .Or<Order>(o => o.LineItem == "Australia Flag"));

        var costs = results.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 1.99m, 1.49m, 9.99m }));
        var orderIds = results.ConvertAll(x => x.OrderId);
        var expectedOrderIds = new[] { customers[0].Orders[0].Id, customers[0].Orders[2].Id, customers[0].Orders[4].Id };
        Assert.That(orderIds, Is.EquivalentTo(expectedOrderIds));

        //Same as above using using db.From<Customer>()
        results = db.Select<FullCustomerInfo>(db.From<Customer>()
            .Join<CustomerAddress>() //implicit
            .Join<Customer, Order>() //explicit
            .Where(c => c.Name == "Customer 1")
            .And<Order>(o => o.Cost < 2)
            .Or<Order>(o => o.LineItem == "Australia Flag"));

        costs = results.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 1.99m, 1.49m, 9.99m }));

        results = db.Select<FullCustomerInfo>(db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>()
            .Where(c => c.Name == "Customer 2")
            .And<CustomerAddress, Order>((a, o) => a.Country == o.LineItem));

        costs = results.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 20m }));

        var countryResults = db.Select<FullCustomerInfo>(db.From<Customer>()
            .Join<CustomerAddress>()                     //implicit join with Customer
            .Join<Order>((c, o) => c.Id == o.CustomerId) //explicit join condition
            .Join<CustomerAddress, Country>((ca, c) => ca.Country == c.CountryName)
            .Where(c => c.Name == "Customer 2")          //implicit condition with Customer
            .And<CustomerAddress, Order>((a, o) => a.Country == o.LineItem));

        costs = countryResults.ConvertAll(x => x.Cost);
        Assert.That(costs, Is.EquivalentTo(new[] { 20m }));
        Assert.That(countryResults.ConvertAll(x => x.CountryCode), Is.EquivalentTo(new[] { "US" }));
    }

    private Customer[] AddCustomersWithOrders()
    {
        var customers = new[]
        {
            new Customer
            {
                Name = "Customer 1",
                PrimaryAddress = new CustomerAddress
                {
                    AddressLine1 = "1 Australia Street",
                    Country = "Australia"
                },
                Orders = new[]
                {
                    new Order {LineItem = "Line 1", Qty = 1, Cost = 1.99m},
                    new Order {LineItem = "Line 1", Qty = 2, Cost = 3.98m},
                    new Order {LineItem = "Line 2", Qty = 1, Cost = 1.49m},
                    new Order {LineItem = "Line 2", Qty = 2, Cost = 2.98m},
                    new Order {LineItem = "Australia Flag", Qty = 1, Cost = 9.99m},
                }.ToList(),
            },
            new Customer
            {
                Name = "Customer 2",
                PrimaryAddress = new CustomerAddress
                {
                    AddressLine1 = "2 Prospect Park",
                    Country = "USA"
                },
                Orders = new[]
                {
                    new Order {LineItem = "USA", Qty = 1, Cost = 20m},
                }.ToList(),
            },
        };

        customers.Each(c =>
            db.Save(c, references: true));

        return customers;
    }

    [Test]
    public void Can_do_LeftJoins_using_SqlExpression()
    {
        AddCustomers();

        db.Insert(
            new Country { CountryName = "Australia", CountryCode = "AU" },
            new Country { CountryName = "USA", CountryCode = "US" },
            new Country { CountryName = "Italy", CountryCode = "IT" },
            new Country { CountryName = "Spain", CountryCode = "ED" });

        //Normal Join
        var dbCustomers = db.Select(db.From<Customer>()
            .Join<CustomerAddress>()
            .Join<CustomerAddress, Country>((ca, c) => ca.Country == c.CountryName));

        Assert.That(dbCustomers.Count, Is.EqualTo(2));

        //Left Join
        dbCustomers = db.Select(db.From<Customer>()
            .Join<CustomerAddress>()
            .LeftJoin<CustomerAddress, Country>((ca, c) => ca.Country == c.CountryName));

        Assert.That(dbCustomers.Count, Is.EqualTo(3));

        //Warning: Right and Full Joins are not implemented by Sqlite3. Avoid if possible.
        var dbCountries = db.Select(db.From<Country>()
            .LeftJoin<CustomerAddress>((c, ca) => ca.Country == c.CountryName)
            .LeftJoin<CustomerAddress, Customer>());

        Assert.That(dbCountries.Count, Is.EqualTo(4));

        var dbAddresses = db.Select(db.From<CustomerAddress>()
            .LeftJoin<Country>((ca, c) => ca.Country == c.CountryName)
            .LeftJoin<CustomerAddress, Customer>());

        Assert.That(dbAddresses.Count, Is.EqualTo(3));
    }

    private void AddCustomers()
    {
        var customers = new[]
        {
            new Customer
            {
                Name = "Customer 1",
                PrimaryAddress = new CustomerAddress
                {
                    AddressLine1 = "1 Australia Street",
                    Country = "Australia"
                },
            },
            new Customer
            {
                Name = "Customer 2",
                PrimaryAddress = new CustomerAddress
                {
                    AddressLine1 = "2 America Street",
                    Country = "USA"
                },
            },
            new Customer
            {
                Name = "Customer 3",
                PrimaryAddress = new CustomerAddress
                {
                    AddressLine1 = "3 Canada Street",
                    Country = "Canada"
                },
            },
        };

        customers.Each(c =>
            db.Save(c, references: true));
    }

    [Test]
    public void Can_Join_on_matching_Alias_convention()
    {
        Country[] countries;
        AddAliasedCustomers(out countries);

        //Normal Join
        var dbCustomers = db.Select(db.From<AliasedCustomer>()
            .Join<AliasedCustomerAddress>()
            .Join<AliasedCustomerAddress, Country>((ca, c) => ca.Country == c.CountryName));

        Assert.That(dbCustomers.Count, Is.EqualTo(2));

        //Left Join
        dbCustomers = db.Select(db.From<AliasedCustomer>()
            .Join<AliasedCustomerAddress>()
            .LeftJoin<AliasedCustomerAddress, Country>((ca, c) => ca.Country == c.CountryName));

        Assert.That(dbCustomers.Count, Is.EqualTo(3));

        //Warning: Right and Full Joins are not implemented by Sqlite3. Avoid if possible.
        var dbCountries = db.Select(db.From<Country>()
            .LeftJoin<AliasedCustomerAddress>((c, ca) => ca.Country == c.CountryName)
            .LeftJoin<AliasedCustomerAddress, AliasedCustomer>());

        Assert.That(dbCountries.Count, Is.EqualTo(4));

        var dbAddresses = db.Select(db.From<AliasedCustomerAddress>()
            .LeftJoin<Country>((ca, c) => ca.Country == c.CountryName)
            .LeftJoin<AliasedCustomerAddress, AliasedCustomer>());

        Assert.That(dbAddresses.Count, Is.EqualTo(3));
    }

    private AliasedCustomer[] AddAliasedCustomers(out Country[] countries)
    {
        db.DropAndCreateTable<AliasedCustomer>();
        db.DropAndCreateTable<AliasedCustomerAddress>();
        db.DropAndCreateTable<Country>();

        var customers = new[]
        {
            new AliasedCustomer
            {
                Name = "Customer 1",
                PrimaryAddress = new AliasedCustomerAddress
                {
                    AddressLine1 = "1 Australia Street",
                    Country = "Australia"
                },
            },
            new AliasedCustomer
            {
                Name = "Customer 2",
                PrimaryAddress = new AliasedCustomerAddress
                {
                    AddressLine1 = "2 America Street",
                    Country = "USA"
                },
            },
            new AliasedCustomer
            {
                Name = "Customer 3",
                PrimaryAddress = new AliasedCustomerAddress
                {
                    AddressLine1 = "3 Canada Street",
                    Country = "Canada"
                },
            },
        };

        customers.Each(c =>
            db.Save(c, references: true));

        countries = new[]
        {
            new Country {CountryName = "Australia", CountryCode = "AU"},
            new Country {CountryName = "USA", CountryCode = "US"},
            new Country {CountryName = "Italy", CountryCode = "IT"},
            new Country {CountryName = "Spain", CountryCode = "ED"}
        };
        db.Save(countries);

        return customers;
    }

    [Test]
    public void Does_populate_custom_columns_based_on_property_convention()
    {
        // Reset auto ids
        db.DropAndCreateTable<Order>();
        db.DropAndCreateTable<CustomerAddress>();
        db.DropAndCreateTable<Customer>();

        var customer = AddCustomerWithOrders();

        var results = db.Select<FullCustomerInfo>(db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>());

        var addressIds = results.ConvertAll(x => x.CustomerAddressId);
        var expectedAddressIds = new[] { customer.PrimaryAddress.Id, customer.PrimaryAddress.Id };
        Assert.That(addressIds, Is.EquivalentTo(expectedAddressIds));

        var orderIds = results.ConvertAll(x => x.OrderId);
        var expectedOrderIds = new[] { customer.Orders[0].Id, customer.Orders[1].Id };
        Assert.That(orderIds, Is.EquivalentTo(expectedOrderIds));

        var customerNames = results.ConvertAll(x => x.CustomerName);
        Assert.That(customerNames, Is.EquivalentTo(new[] { "Customer 1", "Customer 1" }));

        var orderCosts = results.ConvertAll(x => x.OrderCost);
        Assert.That(orderCosts, Is.EquivalentTo(new[] { 1.99m, 2.99m }));

        var expr = db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>()
            .Where<Order>(o => o.Cost > 2);

        results = db.Select<FullCustomerInfo>(expr);

        addressIds = results.ConvertAll(x => x.CustomerAddressId);
        Assert.That(addressIds, Is.EquivalentTo(new[] { customer.PrimaryAddress.Id }));

        orderIds = results.ConvertAll(x => x.OrderId);
        Assert.That(orderIds, Is.EquivalentTo(new[] { customer.Orders[1].Id }));

        customerNames = results.ConvertAll(x => x.CustomerName);
        Assert.That(customerNames, Is.EquivalentTo(new[] { "Customer 1" }));

        orderCosts = results.ConvertAll(x => x.OrderCost);
        Assert.That(orderCosts, Is.EquivalentTo(new[] { 2.99m }));
    }

    [Test]
    public void Does_populate_custom_mixed_columns()
    {
        Country[] countries;
        var customers = AddAliasedCustomers(out countries);

        //Normal Join
        var results = db.Select<MixedCustomerInfo>(db.From<AliasedCustomer>()
            .Join<AliasedCustomerAddress>()
            .Join<AliasedCustomerAddress, Country>((ca, c) => ca.Country == c.CountryName));

        var customerNames = results.Map(x => x.Name);
        Assert.That(customerNames, Is.EquivalentTo(new[] { "Customer 1", "Customer 2" }));

        customerNames = results.Map(x => x.AliasedCustomerName);
        Assert.That(customerNames, Is.EquivalentTo(new[] { "Customer 1", "Customer 2" }));

        customerNames = results.Map(x => x.aliasedcustomername);
        Assert.That(customerNames, Is.EquivalentTo(new[] { "Customer 1", "Customer 2" }));

        var customerIds = results.Map(x => x.Q_CustomerId);
        Assert.That(customerIds, Is.EquivalentTo(new[] { customers[0].Id, customers[1].Id }));

        customerIds = results.Map(x => x.Q_CustomerAddressQ_CustomerId);
        Assert.That(customerIds, Is.EquivalentTo(new[] { customers[0].Id, customers[1].Id }));

        var countryNames = results.Map(x => x.CountryName);
        Assert.That(countryNames, Is.EquivalentTo(new[] { "Australia", "USA" }));

        var countryIds = results.Map(x => x.CountryId);
        Assert.That(countryIds, Is.EquivalentTo(new[] { countries[0].Id, countries[1].Id }));
    }

    [Test]
    public void Can_LeftJoin_and_select_empty_relation()
    {
        AddCustomerWithOrders();

        var customer = new Customer
        {
            Name = "Customer 2",
            PrimaryAddress = new CustomerAddress
            {
                AddressLine1 = "2 America Street",
                Country = "USA"
            },
        };

        db.Save(customer, references: true);

#pragma warning disable 472
        var q = db.From<Customer>();
        q.LeftJoin<Order>()
            .Where<Order>(o => o.Id == null);
#pragma warning restore 472

        var customers = db.Select(q);

        Assert.That(customers.Count, Is.EqualTo(1));
        Assert.That(customers[0].Name, Is.EqualTo("Customer 2"));
    }

    [Test]
    public void Can_load_list_of_references()
    {
        AddCustomersWithOrders();

        var results = db.LoadSelect<Customer>();
        Assert.That(results.Count, Is.EqualTo(2));
        Assert.That(results.All(x => x.PrimaryAddress != null));
        Assert.That(results.All(x => x.Orders.Count > 0));

        var customer1 = results.First(x => x.Name == "Customer 1");
        Assert.That(customer1.PrimaryAddress.Country, Is.EqualTo("Australia"));
        Assert.That(customer1.Orders.Select(x => x.Cost),
            Is.EquivalentTo(new[] { 1.99m, 3.98m, 1.49m, 2.98m, 9.99m }));

        var customer2 = results.First(x => x.Name == "Customer 2");
        Assert.That(customer2.PrimaryAddress.Country, Is.EqualTo("USA"));
        Assert.That(customer2.Orders[0].LineItem, Is.EqualTo("USA"));

        results = db.LoadSelect<Customer>(q => q.Name == "Customer 1");
        Assert.That(results.Count, Is.EqualTo(1));
        Assert.That(results[0].PrimaryAddress.Country, Is.EqualTo("Australia"));
        Assert.That(results[0].Orders.Select(x => x.Cost),
            Is.EquivalentTo(new[] { 1.99m, 3.98m, 1.49m, 2.98m, 9.99m }));
    }

    [Test]
    public void Can_load_list_of_references_using_subselect()
    {
        AddCustomersWithOrders();

        var customers = db.Select(db.From<Customer>()
            .Join<Order>()
            .Where<Order>(o => o.Qty == 1)
            .OrderBy(x => x.Id)
            .SelectDistinct());

        var orders = db.Select<Order>(o => o.Qty == 1);

        customers.Merge(orders);

        customers.PrintDump();

        Assert.That(customers.Count, Is.EqualTo(2));
        Assert.That(customers[0].Orders.Count, Is.EqualTo(3));
        Assert.That(customers[0].Orders.All(x => x.Qty == 1));
        Assert.That(customers[1].Orders.Count, Is.EqualTo(1));
        Assert.That(customers[1].Orders.All(x => x.Qty == 1));
    }

    [Test]
    public void Can_join_on_references_attribute()
    {
        // Drop tables in order that FK allows
        db.DropTable<TABLE_3>();
        db.DropTable<TABLE_2>();
        db.DropTable<TABLE_1>();
        db.CreateTable<TABLE_1>();
        db.CreateTable<TABLE_2>();
        db.CreateTable<TABLE_3>();

        var id1 = db.Insert(new TABLE_1 { One = "A" }, selectIdentity: true);
        var id2 = db.Insert(new TABLE_1 { One = "B" }, selectIdentity: true);

        db.Insert(new TABLE_2 { Three = "C", TableOneKey = (int)id1 });

        var q = db.From<TABLE_1>()
            .Join<TABLE_2>();
        var results = db.Select(q);

        Assert.That(results.Count, Is.EqualTo(1));
        Assert.That(results[0].One, Is.EqualTo("A"));

        var row3 = new TABLE_3
        {
            Three = "3a",
            TableTwo = new TABLE_2
            {
                Three = "3b",
                TableOneKey = (int)id1,
            }
        };
        db.Save(row3, references: true);

        Assert.That(row3.TableTwoKey, Is.EqualTo(row3.TableTwo.Id));

        row3 = db.LoadSingleById<TABLE_3>(row3.Id);
        Assert.That(row3.TableTwoKey, Is.EqualTo(row3.TableTwo.Id));
    }

    [Test]
    public void Can_load_references_with_OrderBy()
    {
        AddCustomersWithOrders();

        var customers = db.LoadSelect(db.From<Customer>().OrderBy(x => x.Name));
        var addresses = customers.Select(x => x.PrimaryAddress).ToList();
        var orders = customers.SelectMany(x => x.Orders).ToList();

        Assert.That(customers.Count, Is.EqualTo(2));
        Assert.That(addresses.Count, Is.EqualTo(2));
        Assert.That(orders.Count, Is.EqualTo(6));
    }

    [Test]
    public void Can_load_select_with_join()
    {
        // Drop tables in order that FK allows
        db.DropTable<TABLE_3>();
        db.DropTable<TABLE_2>();
        db.DropTable<TABLE_1>();
        db.CreateTable<TABLE_1>();
        db.CreateTable<TABLE_2>();
        db.CreateTable<TABLE_3>();

        var id1 = db.Insert(new TABLE_1 { One = "A" }, selectIdentity: true);
        var id2 = db.Insert(new TABLE_1 { One = "B" }, selectIdentity: true);

        db.Insert(new TABLE_2 { Three = "C", TableOneKey = (int)id1 });

        var q = db.From<TABLE_1>()
            .Join<TABLE_2>();
        var results = db.LoadSelect(q);

        Assert.That(results.Count, Is.EqualTo(1));
        Assert.That(results[0].One, Is.EqualTo("A"));

        var row3 = new TABLE_3
        {
            Three = "3a",
            TableTwo = new TABLE_2
            {
                Three = "3b",
                TableOneKey = (int)id1,
            }
        };
        db.Save(row3, references: true);

        Assert.That(row3.TableTwoKey, Is.EqualTo(row3.TableTwo.Id));

        row3 = db.LoadSingleById<TABLE_3>(row3.Id);
        Assert.That(row3.TableTwoKey, Is.EqualTo(row3.TableTwo.Id));
    }

    [Test]
    public void Can_load_select_with_join_and_same_name_columns()
    {
        // Drop tables in order that FK allows
        db.DropTable<ProjectTask>();
        db.DropTable<Project>();
        db.CreateTable<Project>();
        db.CreateTable<ProjectTask>();

        db.Insert(new Project { Val = "test" });
        db.Insert(new ProjectTask { Val = "testTask", ProjectId = 1 });

        var query = db.From<ProjectTask>()
            .Join<ProjectTask, Project>((pt, p) => pt.ProjectId == p.Id);

        var selectResults = db.Select(query);

        var results = db.LoadSelect(query);

        Assert.That(results.Count, Is.EqualTo(1));
        Assert.That(results[0].Val, Is.EqualTo("testTask"));
    }

    [Test]
    [IgnoreDialect(Dialect.AnyMySql, "doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery")]
    [IgnoreDialect(Dialect.AnySqlServer, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.")]
    public void Can_load_references_with_OrderBy_and_Paging()
    {
        db.DropTable<ParentSelf>();
        db.DropTable<ChildSelf>();

        db.CreateTable<ChildSelf>();
        db.CreateTable<ParentSelf>();

        db.Save(new ChildSelf { Id = 1, Value = "Lolz" });
        db.Insert(new ParentSelf { Id = 1, ChildId = null });
        db.Insert(new ParentSelf { Id = 2, ChildId = 1 });

        // Select the Parent.Id == 2.  LoadSelect should populate the child, but doesn't.
        var q = db.From<ParentSelf>()
            .Take(1)
            .OrderByDescending<ParentSelf>(p => p.Id);

        var results = db.LoadSelect(q);

        Assert.That(results.Count, Is.EqualTo(1));
        Assert.That(results[0].Child, Is.Not.Null);
        Assert.That(results[0].Child.Value, Is.EqualTo("Lolz"));

        q = db.From<ParentSelf>()
            .Skip(1)
            .OrderBy<ParentSelf>(p => p.Id);

        results = db.LoadSelect(q);

        Assert.That(results.Count, Is.EqualTo(1));
        Assert.That(results[0].Child, Is.Not.Null);
        Assert.That(results[0].Child.Value, Is.EqualTo("Lolz"));
        results.PrintDump();
    }

    [Test]
    [IgnoreDialect(Tests.Dialect.AnyPostgreSql, "Dapper doesn't know about pgsql naming conventions")]
    public void Can_populate_multiple_POCOs_using_Dappers_QueryMultiple()
    {
        ResetTables();
        AddCustomerWithOrders();

        var q = db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>()
            .OrderBy<Order>(x => x.Id)
            .Select("*");

        using (var multi = db.QueryMultiple(q.ToSelectStatement()))
        {
            var tuples = multi.Read<Customer, CustomerAddress, Order, Tuple<Customer, CustomerAddress, Order>>(
                Tuple.Create).ToList();

            var sb = new StringBuilder();
            foreach (var tuple in tuples)
            {
                sb.AppendLine("Customer:");
                sb.AppendLine(tuple.Item1.Dump());
                sb.AppendLine("Customer Address:");
                sb.AppendLine(tuple.Item2.Dump());
                sb.AppendLine("Order:");
                sb.AppendLine(tuple.Item3.Dump());
            }

            AssertMultiCustomerOrderResults(sb);
        }
    }

    [Test]
    public void Can_populate_multiple_POCOs_using_SelectMulti2()
    {
        ResetTables();
        AddCustomerWithOrders();

        var q = db.From<Customer>()
            .Join<Customer, CustomerAddress>();

        var tuples = db.SelectMulti<Customer, CustomerAddress>(q);

        var sb = new StringBuilder();
        foreach (var tuple in tuples)
        {
            sb.AppendLine("Customer:");
            sb.AppendLine(tuple.Item1.Dump());
            sb.AppendLine("Customer Address:");
            sb.AppendLine(tuple.Item2.Dump());
        }

        Assert.That(sb.ToString().NormalizeNewLines().Trim(), Is.EqualTo(
            @"Customer:
{
	Id: 1,
	Name: Customer 1
}
Customer Address:
{
	Id: 1,
	CustomerId: 1,
	AddressLine1: 1 Australia Street,
	Country: Australia
}".NormalizeNewLines()));
    }

    [Test]
    public void Can_populate_multiple_POCOs_using_SelectMulti2_Distinct()
    {
        ResetTables();
        AddCustomerWithOrders();

        var q = db.From<Customer>()
            .Join<Customer, CustomerAddress>();

        var tuples = db.SelectMulti<Customer, CustomerAddress>(q.SelectDistinct());

        var sb = new StringBuilder();
        foreach (var tuple in tuples)
        {
            sb.AppendLine("Customer:");
            sb.AppendLine(tuple.Item1.Dump());
            sb.AppendLine("Customer Address:");
            sb.AppendLine(tuple.Item2.Dump());
        }

        var sql = db.GetLastSql();
        Assert.That(sql, Does.Contain("SELECT DISTINCT"));

        Assert.That(sb.ToString().NormalizeNewLines().Trim(), Is.EqualTo(
            @"Customer:
{
	Id: 1,
	Name: Customer 1
}
Customer Address:
{
	Id: 1,
	CustomerId: 1,
	AddressLine1: 1 Australia Street,
	Country: Australia
}".NormalizeNewLines()));
    }

    [Test]
    public void Can_populate_multiple_POCOs_using_SelectMulti3()
    {
        ResetTables();
        AddCustomerWithOrders();

        var q = db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>()
            .Where(x => x.Id == 1)
            .And<CustomerAddress>(x => x.Country == "Australia")
            .OrderBy<Order>(x => x.Id);

        var tuples = db.SelectMulti<Customer, CustomerAddress, Order>(q);

        var sb = new StringBuilder();
        foreach (var tuple in tuples)
        {
            sb.AppendLine("Customer:");
            sb.AppendLine(tuple.Item1.Dump());
            sb.AppendLine("Customer Address:");
            sb.AppendLine(tuple.Item2.Dump());
            sb.AppendLine("Order:");
            sb.AppendLine(tuple.Item3.Dump());
        }
        sb.ToString().Print();
        AssertMultiCustomerOrderResults(sb);
    }

    [Test]
    public void Can_custom_select_from_multiple_joined_tables()
    {
        ResetTables();
        AddCustomerWithOrders();

        var q = db.From<Customer>()
            .Join<Customer, CustomerAddress>()
            .Join<Customer, Order>()
            .Where(x => x.Id == 1)
            .And<CustomerAddress>(x => x.Country == "Australia")
            .OrderByDescending<Order>(x => x.Id)
            .Take(1)
            .Select<Customer,CustomerAddress,Order>((c,a,o) => new {
                o.Id,
                c.Name,
                CustomerId = c.Id,
                AddressId = a.Id,
            });

        var result = db.Select<(int id, string name, int customerId, int addressId)>(q)[0];
            
        Assert.That(result.id, Is.EqualTo(2));
        Assert.That(result.name, Is.EqualTo("Customer 1"));
        Assert.That(result.customerId, Is.EqualTo(1));
        Assert.That(result.addressId, Is.EqualTo(1));
    }

    private static void AssertMultiCustomerOrderResults(StringBuilder sb)
    {
        Assert.That(Regex.Replace(sb.ToString(), @"\.99[0]+",".99").NormalizeNewLines().Trim(), Is.EqualTo(
            @"Customer:
{
	Id: 1,
	Name: Customer 1
}
Customer Address:
{
	Id: 1,
	CustomerId: 1,
	AddressLine1: 1 Australia Street,
	Country: Australia
}
Order:
{
	Id: 1,
	CustomerId: 1,
	LineItem: Line 1,
	Qty: 1,
	Cost: 1.99
}
Customer:
{
	Id: 1,
	Name: Customer 1
}
Customer Address:
{
	Id: 1,
	CustomerId: 1,
	AddressLine1: 1 Australia Street,
	Country: Australia
}
Order:
{
	Id: 2,
	CustomerId: 1,
	LineItem: Line 2,
	Qty: 2,
	Cost: 2.99
}".NormalizeNewLines()));
    }
}

public class ParentSelf
{
    [PrimaryKey]
    public int Id { get; set; }

    [References(typeof(ChildSelf))]
    public int? ChildId { get; set; }

    [Reference]
    public ChildSelf Child { get; set; }
}

public class ChildSelf
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Value { get; set; }
}

[Alias("Table1")]
public class TABLE_1 : IHasId<int>
{
    [AutoIncrement]
    [Alias("Key")]
    public int Id { get; set; }

    [Alias("Ena")]
    public string One { get; set; }
}

[Alias("Table2")]
public class TABLE_2 : IHasId<int>
{
    [AutoIncrement]
    [Alias("Key")]
    public int Id { get; set; }

    [Alias("Tri")]
    public string Three { get; set; }

    [References(typeof(TABLE_1))]
    [Alias("Table1")]
    public int TableOneKey { get; set; }
}

[Alias("Table3")]
public class TABLE_3 : IHasId<int>
{
    [AutoIncrement]
    [Alias("Key")]
    public int Id { get; set; }

    [Alias("Tri")]
    public string Three { get; set; }

    [References(typeof(TABLE_2))]
    public int? TableTwoKey { get; set; }

    [Reference]
    public TABLE_2 TableTwo { get; set; }
}

[Schema("Schema")]
[Alias("ProjectTask")]
public class ProjectTask : IHasId<int>
{
    [Alias("ProjectTaskId")]
    [Index(Unique = true)]
    [AutoIncrement]
    public int Id { get; set; }

    [References(typeof(Project))]
    public int ProjectId { get; set; }

    [Reference]
    public Project Project { get; set; }

    public string Val { get; set; }
}

[Schema("Schema")]
[Alias("Project")]
public class Project : IHasId<int>
{
    [Alias("ProjectId")]
    [Index(Unique = true)]
    [AutoIncrement]
    public int Id { get; set; }

    public string Val { get; set; }

}