using System;

using System.Linq;

using NUnit.Framework;

using PropertyExpression.Common;

using PropertyExpression.ControllableQuery;

using PropertyExpression.ControllableQuery.Toolkit;



namespace ConsoleApplication2

{

    [TestFixture]

    public class QueryTest

    {

        [Test]

        public void CheckAllQueries()

        {

            var qRepository = new FindUsagesQRepository();

            QueryChecker.CheckAllQueries(

                QExecutor.ConnectionString,

                typeof (Program).Assembly,

                (type, context) => {

                    if (typeof(Program.RedSwitch).Equals(type))

                        return typeof (Program).Assembly.GetTypes().Where(type.IsAssignableFrom)

                            .SelectMany(_ => context.GetConstructorInvocations(_));

                    throw new ApplicationException();

                },

                qRepository);

            foreach (var qLocationString in qRepository.FindUsages(QExecutor.ConnectionString, "Model", "ModelId".AsOption())

                .Select(QLocationExtensions.GetQLocationString))

            {

                Console.WriteLine(qLocationString);                

            }

        }



        private class IdGenerator

        {

            private int i;



            public int NewId()

            {

                return i++;

            }

        }



        [Test]

        public void InsertTestData()

        {

            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM ServicePartsPage").NonQuery());

            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM ModelPublication").NonQuery());

            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM LocalizedPublication").NonQuery());

            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM Publication").NonQuery());

            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM PublicationType").NonQuery());

            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM BillOfMaterialsLine").NonQuery());

            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM BillOfMaterialsHeader").NonQuery());

            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM Model").NonQuery());



            var idGenerator = new IdGenerator();

            var modelCode = "M1";

            var modelSequence = "MS1";

            var modelId = idGenerator.NewId();

            InsertModel(modelId, modelCode, modelSequence);

            var publicationTypeID = idGenerator.NewId();

            InsertPublicationType(publicationTypeID, 1);

            var publicationID1 = idGenerator.NewId();

            InsertPublication(publicationID1, "P1", publicationTypeID, Option.Nothing<int>());

            var languageCode = "en";

            InsertLocalizedPublication("PubName1", idGenerator.NewId(), publicationID1, languageCode);

            var modelPublicationId = idGenerator.NewId();

            InsertModelPublication(modelPublicationId, modelCode, modelSequence, publicationID1);

            var billOfMaterialsHeaderId1 = idGenerator.NewId();

            InsertBillOfMaterialsHeader(billOfMaterialsHeaderId1);

            var partNumber1 = "PN1";

            InsertBillOfMaterialsLine(idGenerator.NewId(), 1, 1, 0, 1, 1, partNumber1, billOfMaterialsHeaderId1);

            var partNumber2 = "PN2";

            InsertBillOfMaterialsLine(idGenerator.NewId(), 0, 1, 1, 1, 2, partNumber2, billOfMaterialsHeaderId1);

            var partNumber3 = "PN3";

            InsertBillOfMaterialsLine(idGenerator.NewId(), 0, 1, 1, 1, 3, partNumber3, billOfMaterialsHeaderId1);

            var servicePartsPageId1 = idGenerator.NewId();

            InsertServicePartsPage(servicePartsPageId1, modelPublicationId, billOfMaterialsHeaderId1);

            var billOfMaterialsHeaderId2 = idGenerator.NewId();

            InsertBillOfMaterialsHeader(billOfMaterialsHeaderId2);

            InsertBillOfMaterialsLine(idGenerator.NewId(), 1, 1, 1, 0, 1, partNumber1, billOfMaterialsHeaderId2);

            InsertServicePartsPage(idGenerator.NewId(), modelPublicationId, billOfMaterialsHeaderId2);

            var billOfMaterialsHeaderId3 = idGenerator.NewId();

            InsertBillOfMaterialsHeader(billOfMaterialsHeaderId3);

            InsertBillOfMaterialsLine(idGenerator.NewId(), 1, 0, 1, 1, 1, partNumber2, billOfMaterialsHeaderId3);

            InsertServicePartsPage(idGenerator.NewId(), modelPublicationId, billOfMaterialsHeaderId3);

            var publicationID2 = idGenerator.NewId();

            InsertPublication(publicationID2, "P2", publicationTypeID, publicationID2.AsOption());

            InsertLocalizedPublication("PubName2", idGenerator.NewId(), publicationID2, languageCode);

            var modelPublicationId2 = idGenerator.NewId();

            InsertModelPublication(modelPublicationId2, modelCode, modelSequence, publicationID2);

            var billOfMaterialsHeaderId4 = idGenerator.NewId();

            InsertBillOfMaterialsHeader(billOfMaterialsHeaderId4);

            InsertBillOfMaterialsLine(idGenerator.NewId(), 1, 0, 1, 1, 1, partNumber3, billOfMaterialsHeaderId4);

            InsertServicePartsPage(idGenerator.NewId(), modelPublicationId2, billOfMaterialsHeaderId4);



            Assert.AreEqual(

                1,

                LineWithLink(modelId, languageCode, billOfMaterialsHeaderId1, servicePartsPageId1, modelPublicationId, false, false));

            Assert.AreEqual(

                3,

                LineWithLink(modelId, languageCode, billOfMaterialsHeaderId1, servicePartsPageId1, modelPublicationId, true, false));

            Assert.AreEqual(

                2,

                LineWithLink(modelId, languageCode, billOfMaterialsHeaderId1, servicePartsPageId1, modelPublicationId, true, true));

        }



        private static int LineWithLink(int modelId, string languageCode, int billOfMaterialsHeaderId1, int servicePartsPageId1, int modelPublicationId, bool redCondition, bool blueCondition)

        {

            return QExecutor.MaterializeReader(

                context => new {

                                   billOfMaterialsHeaderId = context.Param(billOfMaterialsHeaderId1),

                                   modelPublicationId = context.Param(modelPublicationId),

                                   servicePartsPageId = context.Param(servicePartsPageId1),

                                   languageCode = context.Param(languageCode),

                                   modelId = context.Param(modelId),

                                   redCondition,

                                   blueCondition

                               },

                p => {

                    var q = new Q(@"FROM    BillOfMaterialsLine

WHERE   BillOfMaterialsHeaderId = ")._(p.billOfMaterialsHeaderId)._(@"

        AND ( ( BillOfMaterialsLine.MainLinkExcludedFrom = 0

                AND EXISTS ( SELECT 1

                             FROM   MainPartLink

                             WHERE  MainPartLink.PartNumber = BillOfMaterialsLine.PartNumber

                                    AND MainPartLink.ModelPublicationId = ")._(p.modelPublicationId)._(@"

                                    AND MainPartLink.ServicePartsPageId <> ")._(p.servicePartsPageId)._(@" )

              )");

                    if (p.redCondition)

                    {

                        q._(@"

              OR ( BillOfMaterialsLine.InnerLinkExcludedFrom = 0

                   AND EXISTS ( SELECT  1

                                FROM    InnerPartLink

                                        INNER JOIN Model

                                            ON InnerPartLink.ModelCode = Model.ModelCode

                                               AND InnerPartLink.ModelSequence = Model.ModelSequence");

                        if (p.blueCondition)

                            q._(@"

                                        INNER JOIN Publication

                                            ON InnerPartLink.PublicationId = Publication.PublicationId");

                        q._(@"

                                        INNER JOIN LocalizedPublication

                                            ON InnerPartLink.PublicationId = LocalizedPublication.PublicationId

                                               AND LocalizedPublication.LanguageCode = ")._(p.languageCode)._(@"

                                WHERE   InnerPartLink.PartNumber = BillOfMaterialsLine.PartNumber

                                        AND Model.ModelId = ")._(p.modelId);

                        if (p.blueCondition)

                            q._(@"

                                        AND Publication.FollowingPublicationId IS NULL");

                        q._(@" )

                 )");

                    }

                    q._(@"

            )");

                    return q.Select(_ => new {

                                                 BillOfMaterialsLineId = _._<int>(),

                                                 PartNumber = _._<string>()

                                             });

                }).Count();

        }



        private static void InsertPublicationType(int publicationTypeID, int code)

        {

            QExecutor.ExecuteNonQuery(

                context => new {

                                   PublicationTypeId = context.Param(publicationTypeID),

                                   Code = context.Param(code)

                               },

                p => new Q(@"INSERT  INTO PublicationType

        ( PublicationTypeId,

          Code,

          Decimal1,

          Money1

        )

VALUES  ( ")._(p.PublicationTypeId)._(@",

          ")._(p.Code)._(@",

          7,

          7

        )").NonQuery());

        }



        private static void InsertPublication(int publicationID, string code, int publicationTypeId, IOption<int> followingPublicationId)

        {

            QExecutor.ExecuteNonQuery(

                context => new {

                                   PublicationId = context.Param(publicationID),

                                   Code = context.Param(code),

                                   PublicationTypeId = context.Param(publicationTypeId),

                                   FollowingPublicationId = context.Param(followingPublicationId)

                               },

                p => new Q(@"INSERT  INTO Publication

        ( PublicationId,

          Code,

          PublicationTypeId,

          FollowingPublicationId

        )

VALUES  ( ")._(p.PublicationId)._(@",

          ")._(p.Code)._(@",

          ")._(p.PublicationTypeId)._(@",

          ")._(p.FollowingPublicationId)._(@"

        )").NonQuery());

        }



        private static void InsertModelPublication(int modelPublicationId, string modelCode, string modelSequence, int publicationId)

        {

            QExecutor.ExecuteNonQuery(

                context => new {

                                   ModelPublicationId = context.Param(modelPublicationId),

                                   ModelCode = context.Param(modelCode),

                                   ModelSequence = context.Param(modelSequence),

                                   PublicationId = context.Param(publicationId)

                               },

                p => new Q(@"INSERT  INTO ModelPublication

        ( ModelPublicationId,

          ModelCode,

          ModelSequence,

          PublicationId

        )

VALUES  ( ")._(p.ModelPublicationId)._(@",

          ")._(p.ModelCode)._(@",

          ")._(p.ModelSequence)._(@",

          ")._(p.PublicationId)._(@"

        )").NonQuery());

        }



        private static void InsertServicePartsPage(int servicePartsPageId, int modelPublicationId, int billOfMaterialsHeaderId)

        {

            QExecutor.ExecuteNonQuery(

                context => new {

                                   ServicePartsPageId = context.Param(servicePartsPageId),

                                   ModelPublicationId = context.Param(modelPublicationId),

                                   BillOfMaterialsHeaderId = context.Param(billOfMaterialsHeaderId)

                               },

                p => new Q(@"INSERT  INTO ServicePartsPage

        ( ServicePartsPageId,

          ModelPublicationId,

          BillOfMaterialsHeaderId

        )

VALUES  ( ")._(p.ServicePartsPageId)._(@",

          ")._(p.ModelPublicationId)._(@",

          ")._(p.BillOfMaterialsHeaderId)._(@"

        )").NonQuery());

        }



        private static void InsertBillOfMaterialsHeader(int billOfMaterialsHeaderId)

        {

            QExecutor.ExecuteNonQuery(

                context => new {

                                   BillOfMaterialsHeaderId = context.Param(billOfMaterialsHeaderId)

                               },

                p => new Q(@"INSERT  INTO BillOfMaterialsHeader

        ( BillOfMaterialsHeaderId

        )

VALUES  ( ")._(p.BillOfMaterialsHeaderId)._(@"

        )").NonQuery());

        }



        private static void InsertBillOfMaterialsLine(

            int billOfMaterialsLineId,

            int innerLinkExcludedFrom,

            int innerLinkExcludedTo,

            int mainLinkExcludedFrom,

            int mainLinkExcludedTo,

            int itemSequence,

            string partNumber,

            int billOfMaterialsHeaderId)

        {

            QExecutor.ExecuteNonQuery(

                context => new {

                                   BillOfMaterialsLineId = context.Param(billOfMaterialsLineId),

                                   InnerLinkExcludedFrom = context.Param(innerLinkExcludedFrom),

                                   InnerLinkExcludedTo = context.Param(innerLinkExcludedTo),

                                   MainLinkExcludedFrom = context.Param(mainLinkExcludedFrom),

                                   MainLinkExcludedTo = context.Param(mainLinkExcludedTo),

                                   ItemSequence = context.Param(itemSequence),

                                   PartNumber = context.Param(partNumber),

                                   BillOfMaterialsHeaderId = context.Param(billOfMaterialsHeaderId),

                               },

                p => new Q(@"INSERT  INTO BillOfMaterialsLine

        ( BillOfMaterialsLineId,

          InnerLinkExcludedFrom,

          InnerLinkExcludedTo,

          MainLinkExcludedFrom,

          MainLinkExcludedTo,

          ItemSequence,

          PartNumber,

          BillOfMaterialsHeaderId

        )

VALUES  ( ")._(p.BillOfMaterialsLineId)._(@",

          ")._(p.InnerLinkExcludedFrom)._(@",

          ")._(p.InnerLinkExcludedTo)._(@",

          ")._(p.MainLinkExcludedFrom)._(@",

          ")._(p.MainLinkExcludedTo)._(@",

          ")._(p.ItemSequence)._(@",

          ")._(p.PartNumber)._(@",

          ")._(p.BillOfMaterialsHeaderId)._(@"

        )").NonQuery());

        }



        private static void InsertModel(int modelId, string modelCode, string modelSequence)

        {

            QExecutor.ExecuteNonQuery(

                context => new {

                                   ModelId = context.Param(modelId),

                                   ModelCode = context.Param(modelCode),

                                   ModelSequence = context.Param(modelSequence)

                               },

                p => new Q(@"INSERT  INTO Model

        ( ModelId,

          ModelCode,

          ModelSequence

        )

VALUES  ( ")._(p.ModelId)._(@",

          ")._(p.ModelCode)._(@",

          ")._(p.ModelSequence)._(@"

        )").NonQuery());

        }



        private static void InsertLocalizedPublication(string publicationName, int localizedPublicationId, int publicationId, string languageCode)

        {

            QExecutor.ExecuteNonQuery(

                context => new {

                                   PublicationName = context.Param(publicationName),

                                   LocalizedPublicationId = context.Param(localizedPublicationId),

                                   PublicationId = context.Param(publicationId),

                                   LanguageCode = context.Param(languageCode)

                               },

                p => new Q(@"INSERT  INTO LocalizedPublication

        ( PublicationName,

          LocalizedPublicationId,

          PublicationId,

          LanguageCode

        )

VALUES  ( ")._(p.PublicationName)._(@",

          ")._(p.LocalizedPublicationId)._(@",

          ")._(p.PublicationId)._(@",

          ")._(p.LanguageCode)._(@"

        )").NonQuery());

        }

    }

}