using Microsoft.AnalysisServices.AdomdClient;
using System.Configuration;
using System;
using System.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace Helpers
{
    public static class DatabaseHelper
    {
        public static AdomdConnection Connection()
        {
            return new AdomdConnection(ConfigurationManager.ConnectionStrings["Analytics"].ConnectionString);
        }

        public static string ClearModel()
        {
            var conn = Connection();
            var error = String.Empty;

            try
            {
                conn.Open();

                const string dmx = "DELETE FROM MINING STRUCTURE [Sequence]";

                var cmd = new AdomdCommand(dmx, conn);

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                error = ex.Message;
            }
            finally
            {
                conn.Close();
            }

            return error;
        }

        public static string Train()
        {
            var conn = Connection();
            var error = String.Empty;

            try
            {
                conn.Open();

                const string dmx = @"INSERT INTO MINING MODEL [Sequence]
                            ([Sequence],
                            [Seq Lines] (SKIP,[Position],[Button]))
                            SHAPE {OPENQUERY([Analytics],'SELECT Sequence FROM dbo.Sequence ORDER BY Sequence')}
                            APPEND ({OPENQUERY([Analytics],'SELECT Sequence,Position,Button FROM dbo.SeqLines ORDER BY Sequence,Position')}
                            RELATE [Sequence] TO [Sequence]) AS Seq";

                var cmd = new AdomdCommand(dmx, conn);

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                error = ex.Message;
            }
            finally
            {
                conn.Close();
            }

            return error;
        }

        private static int GetNextSequence()
        {
            var db = new AnalyticsDataContext();

            var maxSeq = (from s in db.Sequences
                          select s.Sequence1).Max();

            return Convert.ToInt32(maxSeq) + 1;
        }

        public static void InsertSeqList(IEnumerable<Seq> seqList)
        {
            var seq = GetNextSequence();
            InsertSeq(seq);
            foreach (var item in seqList)
            {
                InsertSeqLines(seq, item.Position, item.Button);
            }
        }

        private static void InsertSeq(int sequence)
        {
            var db = new AnalyticsDataContext();
            var s = new Sequence { Sequence1 = sequence };
            db.Sequences.InsertOnSubmit(s);
            db.SubmitChanges();
        }

        private static void InsertSeqLines(int sequence, short position, char button)
        {
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString);

            try
            {
                conn.Open();
                var comm = new SqlCommand("INSERT INTO SeqLines VALUES(@Sequence, @Position, @Button)", conn);
                comm.Parameters.AddWithValue("@Sequence", sequence);
                comm.Parameters.AddWithValue("@Position", position);
                comm.Parameters.AddWithValue("@Button", button);

                comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public static Predictions GetDefaultButton()
        {
            var predictions = new Predictions();
            var conn = Connection();

            try
            {
                conn.Open();

                const string dmx = @"SELECT FLATTENED
                                (SELECT $SEQUENCE, Button, PredictProbability(Button),  
                                PredictProbability(Button,NULL) as ProbToLeave FROM
                                PredictSequence([Sequence].[Seq Lines],1)) as Seq
                            From
                                [Sequence]
                            NATURAL PREDICTION JOIN
                                (SELECT (SELECT 1 AS [Position],'NULL' AS [Button]
                                ) AS [Seq Lines]) AS t";

                var cmd = new AdomdCommand(dmx, conn);

                var r = cmd.ExecuteReader();

                if (r != null)
                    while (r.Read())
                    {
                        var p = new Prediction
                        {
                            Sequence = r.GetInt32(r.GetOrdinal("Seq.$SEQUENCE")),
                            Button = r.GetString(r.GetOrdinal("Seq.Button")).ToArray().First(),
                            Expression = r.GetDouble(r.GetOrdinal("Seq.Expression")),
                            ProbToLeave = r.GetDouble(r.GetOrdinal("Seq.ProbToLeave"))
                        };
                        predictions.Prediction.Add(p);
                    }
            }
            catch (Exception ex)
            {
                predictions.Error = ex.Message;
            }
            finally
            {
                conn.Close();
            }

            return predictions;
        }

        public static Predictions GetButtons(List<Seq> seqList)
        {
            var predictions = new Predictions();
            var conn = Connection();

            try
            {
                conn.Open();

                string dmx;
                AdomdCommand cmd;

                if (seqList.Count == 1)
                {
                    dmx = @"SELECT FLATTENED
                            (SELECT $SEQUENCE, Button, PredictProbability(Button),  
                            PredictProbability(Button,NULL) as ProbToLeave FROM
                              PredictSequence([Sequence].[Seq Lines],2)) as Seq
                            From
                              [Sequence]
                            NATURAL PREDICTION JOIN
                            (SELECT (SELECT 1 AS [Position],'" + seqList.First().Button + "' AS [Button]) AS [Seq Lines]) AS t";

                    cmd = new AdomdCommand(dmx, conn);
                }
                else
                {
                    dmx = @"SELECT FLATTENED
                                (SELECT $SEQUENCE, Button, PredictProbability(Button),  
                                PredictProbability(Button,NULL) as ProbToLeave FROM
                                PredictSequence([Sequence].[Seq Lines],2)) as Seq
                            From
                                [Sequence]
                            NATURAL PREDICTION JOIN
                            (SELECT (SELECT 1 AS [Position],'" + seqList[0].Button + @"' AS [Button]";

                    for (var i = 1; i < seqList.Count; i++)
                    {

                        dmx += "UNION SELECT " + i + " AS [Position],'" + seqList[i].Button + "' AS [Button]";
                    }

                    dmx += ") AS [Seq Lines]) AS t";

                    cmd = new AdomdCommand(dmx, conn);
                }

                var r = cmd.ExecuteReader();

                if (r != null)
                    while (r.Read())
                    {
                        var p = new Prediction
                        {
                            Sequence = r.GetInt32(r.GetOrdinal("Seq.$SEQUENCE")),
                            Button = r.GetString(r.GetOrdinal("Seq.Button")).ToArray().First(),
                            Expression = r.GetDouble(r.GetOrdinal("Seq.Expression")),
                            ProbToLeave = r.GetDouble(r.GetOrdinal("Seq.ProbToLeave"))
                        };
                        predictions.Prediction.Add(p);
                    }
            }
            catch (Exception ex)
            {
                predictions.Error = ex.Message;
            }
            finally
            {
                conn.Close();
            }

            return predictions;
        }

        public static CustomerProbabilities GetCustomerProbability(string age, string children, string commuteDistance, string country, string education, string gender,
            string hof, string maritalStatus, string numberCarsOwned, string occupation, string region, string YearlyIncome)
        {
            var cp = new CustomerProbabilities();
            var conn = DatabaseHelper.Connection();

            try
            {
                conn.Open();

                var cmd = new AdomdCommand(@"
                            SELECT
                              PredictCaseLikelihood() as test,
                              PredictProbability([CL].[Age],@Age)/PredictProbability([CL].[Age])
                                AS Age,
                             DMExt.GetProbabilityDistribution (@ChildrenPar,Predict([CL].[Children]),PredictVariance([CL].[Children])) 
                                AS ChildrenPar,
                              PredictProbability([CL].[Commute Distance],@CommuteDistance)/PredictProbability([CL].[Commute Distance]) 
                                AS CommuteDistance,
                              PredictProbability([CL].[Country],@Country)/PredictProbability([CL].[Country]) 
                                AS Country,
                              PredictProbability([CL].[Education],@Education)/PredictProbability([CL].[Education]) 
                                AS Education,
                              PredictProbability([CL].[Gender],@Gender)/PredictProbability([CL].[Gender]) 
                                AS Gender,
                              PredictProbability([CL].[House Owner Flag],@HOF)/PredictProbability([CL].[House Owner Flag]) 
                                AS HOF,
                              PredictProbability([CL].[Marital Status],@MaritalStatus)/PredictProbability([CL].[Marital Status]) 
                                AS MaritalStatus,
                              DMExt.GetProbabilityDistribution (@NumberCarsOwned,Predict([CL].[Number Cars Owned]),PredictVariance([CL].[Number Cars Owned])) 
                                AS NumberCarsOwned,
                              PredictProbability([CL].[Occupation],@Occupation)/PredictProbability([CL].[Occupation]) 
                                AS Occupation,
                              PredictProbability([CL].[Region],@Region)/PredictProbability([CL].[Region]) 
                                AS Region,
                              DMExt.GetProbabilityDistribution (@YearlyIncome,Predict([CL].[Yearly Income]),PredictVariance([CL].[Yearly Income])) 
                                AS YearlyIncome
                            FROM [CL]
                            NATURAL PREDICTION JOIN
                                (SELECT @Age AS [Age],
                                        @ChildrenPar AS [Children],
                                        @CommuteDistance AS [Commute Distance],
                                        @Country AS [Country],
                                        @Education AS [Education],
                                        @Gender AS [Gender],
                                        @HOF AS [House Owner Flag],
                                        @MaritalStatus AS [Marital Status],
                                        @NumberCarsOwned AS [Number Cars Owned],
                                        @Occupation AS [Occupation],
                                        @Region AS [Region],
                                        @YearlyIncome AS [Yearly Income]) AS t", conn);

                cmd.Parameters.Add("Age", age);
                cmd.Parameters.Add("ChildrenPar", Convert.ToByte(children));
                cmd.Parameters.Add("CommuteDistance", commuteDistance);
                cmd.Parameters.Add("Country", country);
                cmd.Parameters.Add("Education", education);
                cmd.Parameters.Add("Gender", gender);
                cmd.Parameters.Add("HOF", hof);
                cmd.Parameters.Add("MaritalStatus", maritalStatus);
                cmd.Parameters.Add("NumberCarsOwned", numberCarsOwned);
                cmd.Parameters.Add("Occupation", occupation);
                cmd.Parameters.Add("Region", region);
                cmd.Parameters.Add("YearlyIncome", YearlyIncome);

                var r = cmd.ExecuteReader();

                if (r != null)
                    while (r.Read())
                    {
                        var probability = new CustomerProbability
                        {
                            PredictCaseLikelihood = r.GetDouble(r.GetOrdinal("test")),
                            Age = r.GetDouble(r.GetOrdinal("Age")),
                            Children = r.GetDouble(r.GetOrdinal("ChildrenPar")),
                            CommuteDistance = r.GetDouble(r.GetOrdinal("CommuteDistance")),
                            Country = r.GetDouble(r.GetOrdinal("Country")),
                            Education = r.GetDouble(r.GetOrdinal("Education")),
                            Gender = r.GetDouble(r.GetOrdinal("Gender")),
                            HOF = r.GetDouble(r.GetOrdinal("HOF")),
                            MaritalStatus = r.GetDouble(r.GetOrdinal("MaritalStatus")),
                            NumberCarsOwned = r.GetDouble(r.GetOrdinal("NumberCarsOwned")),
                            Occupation = r.GetDouble(r.GetOrdinal("Occupation")),
                            Region = r.GetDouble(r.GetOrdinal("Region")),
                            YearlyIncome = r.GetDouble(r.GetOrdinal("YearlyIncome"))
                        };

                        cp.Probabilities = probability;
                    }
            }
            catch (Exception ex)
            {
                cp.Error = ex.Message;
            }
            finally
            {
                conn.Close();
            }

            return cp;
        }

        public static AgeProbability GetAgeProbability(string children, string commuteDistance, string country, string gender, string hof, string maritalStatus, string numberCars,
            string occupation, string region, string yearlyIncome)
        {
            var ap = new AgeProbability();
            var conn = DatabaseHelper.Connection();

            try
            {
                conn.Open();


                const string sAge = @"
                            SELECT
                                Predict ([DT].[Age]) AS Age,
                                PredictProbability([DT].[Age]) AS AgeProbability
                            FROM
                                [DT]
                            NATURAL PREDICTION JOIN
                                (SELECT @ChildrenPar AS [Children],
                                @CommuteDistance AS [Commute Distance],
                                @Country AS [Country],
                                @Gender AS [Gender],
                                @HOF AS [House Owner Flag],
                                @MaritalStatus AS [Marital Status],
                                @NumberCarsOwned AS [Number Cars Owned],
                                @Occupation AS [Occupation],
                                @Region AS [Region],
                                @YearlyIncome AS [Yearly Income]) AS t";

                // Fill Age
                var cmdAge = new AdomdCommand(sAge, conn);

                cmdAge.Parameters.Add("ChildrenPar", children);
                cmdAge.Parameters.Add("CommuteDistance", commuteDistance);
                cmdAge.Parameters.Add("Country", country);
                cmdAge.Parameters.Add("Gender", gender);
                cmdAge.Parameters.Add("HOF", hof);
                cmdAge.Parameters.Add("MaritalStatus", maritalStatus);
                cmdAge.Parameters.Add("NumberCarsOwned", numberCars);
                cmdAge.Parameters.Add("Occupation", occupation);
                cmdAge.Parameters.Add("Region", region);
                cmdAge.Parameters.Add("YearlyIncome", yearlyIncome);

                var rAge = cmdAge.ExecuteReader();

                if (rAge != null)
                    while (rAge.Read())
                    {
                        ap.Age = rAge.GetInt32(rAge.GetOrdinal("Age"));
                        ap.Probability = rAge.GetDouble(rAge.GetOrdinal("AgeProbability"));
                    }
            }
            catch (Exception ex)
            {
                ap.Error = ex.Message;
            }
            finally
            {
                conn.Close();
            }

            return ap;
        }

        public static EducationProbability GetEducationProbability(string children, string commuteDistance, string country, string gender, string hof, string maritalStatus, 
            string numberCars, string occupation, string region, string yearlyIncome)
        {
            var ep = new EducationProbability();
            var conn = DatabaseHelper.Connection();

            try
            {
                conn.Open();

                // Fill Education
                const string sEducation = @"
                             SELECT
                              Predict ([DT].[Education]) AS Education,
                              PredictProbability([DT].[Education]) AS EducationProbability
                            FROM
                              [DT]
                            NATURAL PREDICTION JOIN
                                (SELECT 
                                  @ChildrenPar AS [Children],
                                  @CommuteDistance AS [Commute Distance],
                                  @Country AS [Country],
                                  @Gender AS [Gender],
                                  @HOF AS [House Owner Flag],
                                  @MaritalStatus AS [Marital Status],
                                  @NumberCarsOwned AS [Number Cars Owned],
                                  @Occupation AS [Occupation],
                                  @Region AS [Region],
                                  @YearlyIncome AS [Yearly Income]) AS t";

                var cmdEducation = new AdomdCommand(sEducation, conn);

                cmdEducation.Parameters.Add("ChildrenPar", children);
                cmdEducation.Parameters.Add("CommuteDistance", commuteDistance);
                cmdEducation.Parameters.Add("Country", country);
                cmdEducation.Parameters.Add("Gender", gender);
                cmdEducation.Parameters.Add("HOF", hof);
                cmdEducation.Parameters.Add("MaritalStatus", maritalStatus);
                cmdEducation.Parameters.Add("NumberCarsOwned", numberCars);
                cmdEducation.Parameters.Add("Occupation", occupation);
                cmdEducation.Parameters.Add("Region", region);
                cmdEducation.Parameters.Add("YearlyIncome", yearlyIncome);

                var rEducation = cmdEducation.ExecuteReader();

                if (rEducation != null)
                    while (rEducation.Read())
                    {
                        ep.Education = rEducation.GetString(rEducation.GetOrdinal("Education"));
                        ep.Probability = rEducation.GetDouble(rEducation.GetOrdinal("EducationProbability"));
                    }
            }
            catch (Exception ex)
            {
                ep.Error = ex.Message;
            }
            finally
            {
                conn.Close();
            }

            return ep;
        }
    }
}
