using System;

using System.Collections.Generic;

using System.Linq;

public class Program

{

    private static List<Client> clientsTable = new List<Client>();

    private static List<ClientLocation> clientLocationsTable = new List<ClientLocation>();

    private static List<LoadComplete> loadsTable = new List<LoadComplete>();

    private static List<Invoice> invoicesTable = new List<Invoice>();

    private static List<ClientLocationNote> notesTable = new List<ClientLocationNote>();

    public static void Main()

    {       

        PopulateTables();

        QueryData();

    }

    private static void QueryData()

    {

        var dataModel = new Data();

        var dataOne = from client in clientsTable

                      join clientLocTable in clientLocationsTable on client.ClientId equals clientLocTable.ClientId into clientLocJoin

                      from clientLoc in clientLocJoin.DefaultIfEmpty()

                      join loadTable in loadsTable on (clientLoc == null ? -1 : clientLoc.LocationId) equals loadTable.LocationId into loadJoin

                      from load in loadJoin.DefaultIfEmpty()

                      join invoiceTable in invoicesTable on (load == null ? -1 : load.LoadId) equals invoiceTable.LoadId into invoiceJoin

                      from invoice in invoiceJoin.DefaultIfEmpty()

                      join noteTable in notesTable on (clientLoc == null ? -1 : clientLoc.LocationId) equals noteTable.LocationId into noteJoin

                      from note in noteJoin.DefaultIfEmpty()

                      //TODO: WHERE clause

                      group new { client, clientLoc, load, invoice, note } by new { client.ClientId, clientLoc.LocationId, load.LoadId } into grp

                      select new { grp.Key.ClientId, grp.Key.LocationId, grp.Key.LoadId, Items = grp.OrderBy(g => g.load.LoadId).Take(2) };

​

        dataModel.Clients = dataOne.GroupBy(d => new { d.client.ClientId, d.client.Name })

                            .Select(g => new ClientModel()

                                        {

                                            //Build ClientModel

                                            ClientId = g.Key.ClientId,

                                            Name = g.Key.Name,

                                            ClientLocations = g.Where(cg => cg.clientLoc != null).GroupBy(d => new

                                                                          {

                                                                              d.client.ClientId,

                                                                              d.clientLoc.LocationId,

                                                                              d.clientLoc.Name

                                                                          })

                                                                    .Select(g1 => new ClientLocationModel()

                                                                            {

                                                                                //Build ClientLocationModel

                                                                                LocationId = g1.Key.LocationId,

                                                                                ClientId = g1.Key.ClientId,

                                                                                Name = g1.Key.Name,

                                                                                Pickups = g1.Where(lg1 => lg1.load != null).GroupBy(lg => new

                                                                                                                                    {

                                                                                                                                        lg.client.ClientId,

                                                                                                                                        lg.clientLoc.LocationId,

                                                                                                                                        lg.load.LoadId

                                                                                                                                    })

                                                                                                            .Select(lg => new PickupModel()

                                                                                                                    {

                                                                                                                        LoadId = lg.Key.LoadId,

                                                                                                                        LocationId = lg.Key.LocationId,

                                                                                                                        PickupItems = lg.Where(pig => pig.invoice != null).GroupBy(pig => new

                                                                                                                                    {

                                                                                                                                        pig.client.ClientId,

                                                                                                                                        pig.clientLoc.LocationId,

                                                                                                                                        pig.load.LoadId,

                                                                                                                                        pig.invoice.InvoiceId

                                                                                                                                    })

                                                                                                                                    .Select(pig => new PickupItemModel()

                                                                                                                                            {

                                                                                                                                                Id = pig.Key.InvoiceId,

                                                                                                                                                LoadId = lg.Key.LoadId,

                                                                                                                                                LocationId = lg.Key.LocationId

                                                                                                                                            }).ToList()

                                                                                                                    }).ToList(),

                                                                                Notes = g1.Where(lg1 => lg1.note != null).GroupBy(ng => new

                                                                                                                                    {

                                                                                                                                        ng.client.ClientId,

                                                                                                                                        ng.clientLoc.LocationId,

                                                                                                                                        ng.note.NoteId,

                                                                                                                                        ng.note.Note

                                                                                                                                    })

                                                                                                            .Select(ng => new ClientLocationNoteModel()

                                                                                                                    {

                                                                                                                        NoteId = ng.Key.NoteId,

                                                                                                                        LocationId = ng.Key.LocationId,

                                                                                                                        Note = ng.Key.Note

                                                                                                                    }).ToList()

                                                                            }).ToList()

                                        }).ToList();

        foreach(var clientId in dataModel.Clients)

        {

            Console.WriteLine($"Client: {clientId.ClientId}");

            foreach(var clientLocation in clientId.ClientLocations)

            {

                Console.WriteLine($"\t\tClientLocation: {clientLocation.LocationId}");

                foreach(var load in clientLocation.Pickups)

                {

                    Console.WriteLine($"\t\t\t\tLoad: {load.LoadId}");

                    foreach(var loadItem in load.PickupItems)

                    {

                        Console.WriteLine($"\t\t\t\t\t\tLoad Item: {loadItem.Id}");

                    }

                }

                foreach(var clNote in clientLocation.Notes)

                {

                    Console.WriteLine($"\t\t\t\tNote: {clNote.NoteId}");

                }

            }

        }

    }

    private static void PopulateTables()

    {

        //Client 1

        //  - 2 Locations

        //      - Location 1

        //          - 2 Loads

        //              - Load 1

        //                  - 2 Load Items

        //                      - Load Item 1

        //                      - Load Item 2

        //              - Load 2

        //          - 3 Notes

        //              - Note 1

        //              - Note 2

        //              - Note 3

        //      - Location 2

        //          - 3 Loads

        //              - Load 3

        //                  - 3 Load Items

        //                      - Load Item 3

        //                      - Load Item 4

        //                      - Load Item 5

        //              - Load 4

        //              - Load 5

        //          - 2 Notes

        //              - Note 1

        //              - Note 2

        clientsTable.Add(new Client() { ClientId = 1, Name = "Client 1" });

        clientLocationsTable.Add(new ClientLocation() { LocationId = 1, ClientId = 1, Name = "Location 1" });

        clientLocationsTable.Add(new ClientLocation() { LocationId = 2, ClientId = 1, Name = "Location 2" });

        loadsTable.Add(new LoadComplete() { LoadId = 1, LocationId = 1, Name = "Load 1" });

        loadsTable.Add(new LoadComplete() { LoadId = 2, LocationId = 1, Name = "Load 2" });

        loadsTable.Add(new LoadComplete() { LoadId = 3, LocationId = 2, Name = "Load 3" });

        loadsTable.Add(new LoadComplete() { LoadId = 4, LocationId = 2, Name = "Load 4" });

        loadsTable.Add(new LoadComplete() { LoadId = 5, LocationId = 2, Name = "Load 5" });

        invoicesTable.Add(new Invoice() { InvoiceId = 1, LoadId = 1, Name = "Load Item 1" });

        invoicesTable.Add(new Invoice() { InvoiceId = 2, LoadId = 1, Name = "Load Item 2" });

        invoicesTable.Add(new Invoice() { InvoiceId = 3, LoadId = 3, Name = "Load Item 3" });

        invoicesTable.Add(new Invoice() { InvoiceId = 4, LoadId = 3, Name = "Load Item 4" });

        invoicesTable.Add(new Invoice() { InvoiceId = 5, LoadId = 3, Name = "Load Item 5" });

        notesTable.Add(new ClientLocationNote() { NoteId = 1, LocationId = 1, Note = "Note 1" });

        notesTable.Add(new ClientLocationNote() { NoteId = 2, LocationId = 1, Note = "Note 2" });

        notesTable.Add(new ClientLocationNote() { NoteId = 3, LocationId = 1, Note = "Note 3" });

        notesTable.Add(new ClientLocationNote() { NoteId = 4, LocationId = 2, Note = "Note 4" });

        notesTable.Add(new ClientLocationNote() { NoteId = 5, LocationId = 2, Note = "Note 5" });

        //Client 2

        //  - 1 Locations

        //      - Location 3

        //          - 5 Loads

        //              - Load 6

        //              - Load 7

        //              - Load 8

        //              - Load 9

        //              - Load 10

        clientsTable.Add(new Client() { ClientId = 2, Name = "Client 2" });

        clientLocationsTable.Add(new ClientLocation() { LocationId = 3, ClientId = 2, Name = "Location 3" });

        loadsTable.Add(new LoadComplete() { LoadId = 6, LocationId = 3, Name = "Load 6" });

        loadsTable.Add(new LoadComplete() { LoadId = 7, LocationId = 3, Name = "Load 7" });

        loadsTable.Add(new LoadComplete() { LoadId = 8, LocationId = 3, Name = "Load 8" });

        loadsTable.Add(new LoadComplete() { LoadId = 9, LocationId = 3, Name = "Load 9" });

        loadsTable.Add(new LoadComplete() { LoadId = 10, LocationId = 3, Name = "Load 10" });

        //Client 3

        //  - 0 Locations

        clientsTable.Add(new Client() { ClientId = 3, Name = "Client 3" });

        //Client 4

        //  - 1 Locations

        //      - Location 4

        //          - 0 loads

        clientsTable.Add(new Client() { ClientId = 4, Name = "Client 4" });

        clientLocationsTable.Add(new ClientLocation() { LocationId = 4, ClientId = 4, Name = "Location 4" });

        //Client 5

        //  - 1 Locations

        //      - Location 5

        //          - 0 loads

        //          - 3 Notes

        //              - Note 6

        //              - Note 7

        //              - Note 8

        clientsTable.Add(new Client() { ClientId = 5, Name = "Client 5" });

        clientLocationsTable.Add(new ClientLocation() { LocationId = 5, ClientId = 5, Name = "Location 5" });

        notesTable.Add(new ClientLocationNote() { NoteId = 6, LocationId = 5, Note = "Note 6" });

        notesTable.Add(new ClientLocationNote() { NoteId = 7, LocationId = 5, Note = "Note 7" });

        notesTable.Add(new ClientLocationNote() { NoteId = 8, LocationId = 5, Note = "Note 8" });

    }

}

​

//Models

public class Data

{

    public List<ClientModel> Clients { get; set; }

}

​

public class ClientModel

{

    public int ClientId { get; set; }

    public string Name { get; set; }

    public List<ClientLocationModel> ClientLocations { get; set; }

    public ClientModel()

    {

        this.ClientLocations = new List<ClientLocationModel>();

    }

}

​

public class ClientLocationModel

{

    public int LocationId { get; set; }

    public int ClientId { get; set; }

    public string Name { get; set; }

    public List<PickupModel> Pickups { get; set; }

    public List<ClientLocationNoteModel> Notes { get; set; }

    public ClientLocationModel()

    {

        this.Pickups = new List<PickupModel>();

        this.Notes = new List<ClientLocationNoteModel>();

    }

}

​

public class PickupModel

{

    public int LoadId { get; set; }

    public int LocationId { get; set; }

    public List<PickupItemModel> PickupItems { get; set; }

    public PickupModel()

    {

        this.PickupItems = new List<PickupItemModel>();

    }

}

​

public class PickupItemModel

{

    public int Id { get; set; }

    public int LoadId { get; set; }

    public int LocationId { get; set; }

}

​

public class ClientLocationNoteModel

{

    public int NoteId { get; set; }

    public int LocationId { get; set; }

    public string Note { get; set; }

}

​

//Tables

public class Client

{

    public int ClientId { get; set; }

    public string Name { get; set; }

}

​

public class ClientLocation

{

    public int LocationId { get; set; }

    public int ClientId { get; set; }

    public string Name { get; set; }

}

​

public class LoadComplete

{

    public int LoadId { get; set; }

    public int LocationId { get; set; }

    public string Name { get; set; }

}

​

public class Invoice

{

    public int InvoiceId { get; set; }

    public int LoadId { get; set; }

    public string Name { get; set; }

}

​

public class ClientLocationNote

{

    public int NoteId { get; set; }

    public int LocationId { get; set; }

    public string Note { get; set; }

}