using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Microsoft.AnalysisServices.AdomdServer;

namespace TSSprocs
{
    public class TSSprocs
    {
        private string ObjectEscape(string ObjectName)
        {
            return ObjectName.Replace("]", "]]");
        }

        [SafeToPrepare(true)]
        public DataTable TimeSeriesReportDMV(string ModelName, string ColumnName, string SeriesName, int NumHistoricalPoints, int NumPredictions)
        {
            AdomdCommand cmd = new AdomdCommand();
            AdomdDataReader rdr;
            string KeyTimeColumnName = "";
            string KeyColumnName = "";
            int count = 0;


            // Validate model name and type
            cmd.CommandText = "SELECT SERVICE_NAME FROM $SYSTEM.DMSCHEMA_MINING_MODELS WHERE MODEL_NAME=@Model";
            cmd.Parameters.Add("Model",ModelName);
            rdr = cmd.ExecuteReader();
            count = 0;
            while (rdr.Read())
            {
                count++;
                if (rdr.GetString(0) != "Microsoft_Time_Series")
                    throw new SystemException("Specified model is not time series");
            }
            if (count != 1)
                throw new SystemException("Model not found");
            rdr.Close();

            // Validate column name and type
            cmd.CommandText = "SELECT IS_PREDICTABLE FROM $SYSTEM.DMSCHEMA_MINING_COLUMNS WHERE MODEL_NAME=@Model AND COLUMN_NAME=@Column";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("Model", ModelName);
            cmd.Parameters.Add("Column", ColumnName);
            rdr = cmd.ExecuteReader();
            count = 0;
            while (rdr.Read())
            {
                count++;
                if (!rdr.GetBoolean(0))
                    throw new SystemException("Specified column not predictable");
            }
            if (count != 1)
                throw new SystemException("Column not found");
            rdr.Close();

            // Validate series name
            if (SeriesName != "")
            {
                cmd.CommandText = "SELECT COLUMN_NAME FROM $SYSTEM.DMSCHEMA_MINING_COLUMNS WHERE MODEL_NAME=@Model AND CONTENT_TYPE='KEY'";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("Model",ModelName);
                rdr = cmd.ExecuteReader();
                count = 0;
                while (rdr.Read())
                {
                    count++;
                    KeyColumnName = rdr.GetString(0);
                }
                if (count != 1)
                    throw new SystemException("Model structure does not contain series");
                rdr.Close();

                cmd.CommandText = "SELECT DISTINCT [" + KeyColumnName +"] FROM [" + ModelName + "] WHERE [" + KeyColumnName + "] = @Series";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("Series",SeriesName);
                rdr = cmd.ExecuteReader();
                count = 0;
                while (rdr.Read())
                {
                    count++;
                }
                if (count != 1)
                    throw new SystemException("Invalid series name");
                rdr.Close();
            }

            // Validate model format and find key time column
            cmd.CommandText = "SELECT COLUMN_NAME, CONTAINING_COLUMN FROM $SYSTEM.DMSCHEMA_MINING_COLUMNS WHERE MODEL_NAME=@Model AND CONTENT_TYPE='KEY TIME'";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("Model",ModelName);
            rdr = cmd.ExecuteReader();
            count = 0;
            while (rdr.Read())
            {
                count++;
                if (!rdr.IsDBNull(1))
                    throw new SystemException("Function does not support time series with nested tables");
                KeyTimeColumnName = rdr.GetString(0);
            }
            if (count != 1)  // This could only happen with a malformed time series models, which shouldn't be possible
                throw new SystemException("Something really bad happened");
            rdr.Close();


            // Validate integer inputs
            if (NumPredictions < 1 || NumHistoricalPoints < 0)
                throw new SystemException("Negative count");


            // Create result set
            DataTable Result = new DataTable(SeriesName);
            Result.Columns.Add("Ordinal", typeof(int));
            Result.Columns.Add(ColumnName, typeof(double));
            Result.Columns.Add("Predicted " + ColumnName, typeof(double));

            // Return empty result set if preparing
            if (Context.ExecuteForPrepare)
                return Result;


            // Get Historical Data
            string CasesCommandText;
            int RowOrdinal;
            DataRow lastRow = null;

            cmd.Parameters.Clear();

            if (SeriesName != "")
            {
                CasesCommandText = "SELECT [" + ObjectEscape(KeyTimeColumnName) + "], [" + ObjectEscape(ColumnName) + "] FROM [" + ObjectEscape(ModelName) + "].CASES WHERE [" + ObjectEscape(KeyColumnName) + "] = @Series";
                cmd.Parameters.Add("Series",SeriesName);
            }
            else
            {
                CasesCommandText = "SELECT [" + ObjectEscape(KeyTimeColumnName) + "], [" + ObjectEscape(ColumnName) + "] FROM [" + ObjectEscape(ModelName) + "].CASES ";
            }

            if (NumHistoricalPoints > 0)
            {
                // Change command text to retrieve the last N points
                CasesCommandText = "SELECT TOP " + NumHistoricalPoints.ToString() + " t.* FROM (" +
                    CasesCommandText + ") AS t ORDER BY t.[" + ObjectEscape(KeyTimeColumnName) + "] DESC";
                CasesCommandText = "SELECT * FROM (" + CasesCommandText + ") AS s ORDER BY [" + ObjectEscape(KeyTimeColumnName) + "]";
            }

            object[] values = new object[3];
            cmd.CommandText = CasesCommandText;
            rdr = cmd.ExecuteReader();
            values[0]=0;
            values[2]=null;
            while (rdr.Read())
            {
                values[1] = rdr.GetDouble(1);
                lastRow = Result.Rows.Add(values);
            }
            rdr.Close();

            // Fix up the rowset - copy the last value to the predicted column, and fill out the ordinal column
            lastRow[2] = lastRow[1];
            RowOrdinal = -Result.Rows.Count;
            foreach (DataRow row in Result.Rows)
            {
                row[0] = ++RowOrdinal;
            }

            // Get predicted data
            string PredictionCommandText;

            cmd.Parameters.Clear();
            if (SeriesName != "")
            {
                PredictionCommandText = "SELECT FLATTENED PredictTimeSeries([" + ObjectEscape(ColumnName) +
                    "], @NumPredictions ) FROM [" + ObjectEscape(ModelName) + "] WHERE [" + ObjectEscape(KeyColumnName) + "] = @Series";
                cmd.Parameters.Add("Series", SeriesName);
            }
            else
            {
                PredictionCommandText = "SELECT FLATTENED PredictTimeSeries([" + ObjectEscape(ColumnName) +
                    "], @NumPredictions ) FROM [" + ObjectEscape(ModelName) + "]";
            }
            cmd.Parameters.Add("NumPredictions", NumPredictions);

            cmd.CommandText = PredictionCommandText;
            rdr = cmd.ExecuteReader();
            values[1] = null;
            while (rdr.Read())
            {
                values[0] = ++RowOrdinal;
                values[2] = rdr.GetDouble(1);
                Result.Rows.Add(values);
            }
            rdr.Close();


            return Result;
        }

        [SafeToPrepare(true)]
        public DataTable TimeSeriesReport(string ModelName, string ColumnName, string SeriesName, int NumHistoricalPoints, int NumPredictions)
        {
            AdomdCommand cmd = new AdomdCommand();
            AdomdDataReader rdr;
            string KeyTimeColumnName = "";
            string KeyColumnName = "";
            int count = 0;


            // Validate model name and type
            MiningModel model = Context.MiningModels.Find(ModelName);
            if (model == null)
                throw new SystemException("Model not found");
            if (model.Algorithm != "Microsoft_Time_Series")
                throw new SystemException("Specified model is not time series");

            // Validate column name and type
            MiningModelColumn PredictableColumn = model.Columns.Find(ColumnName);
            if (PredictableColumn == null)
                throw new SystemException("Column not found");
            if (!PredictableColumn.IsPredictable)
                throw new SystemException("Specified column not predictable");


            // Validate model format and find key columns
            MiningModelColumn KeyColumn = null;
            foreach (MiningModelColumn column in model.Columns)
            {
                if (column.Content == "Key Time")
                {
                    KeyTimeColumnName = column.Name;
                    if (column.ContainingColumn != null)
                        throw new SystemException("Function does not support time series with nested tables");
                }
                if (column.Content == "Key")
                {
                    KeyColumn = column;
                    KeyColumnName = column.Name;
                }
            }
            if (KeyTimeColumnName == "")  // This could only happen with a malformed time series models, which shouldn't be possible
                throw new SystemException("Something really bad happened");

            // Validate series name
            if (SeriesName != "")
            {
                count = 0;
                foreach (MiningValue value in KeyColumn.Values)
                {
                    if (value.ToString() == SeriesName)
                        count++;
                }
                if (count != 1)
                    throw new SystemException("Invalid series name");
            }

            // Validate integer inputs
            if (NumPredictions < 1 || NumHistoricalPoints < 0)
                throw new SystemException("Negative count");


            // Create result set
            DataTable Result = new DataTable(SeriesName);
            Result.Columns.Add("Ordinal", typeof(int));
            Result.Columns.Add(ColumnName, typeof(double));
            Result.Columns.Add("Predicted " + ColumnName, typeof(double));

            // Return empty result set if preparing
            if (Context.ExecuteForPrepare)
                return Result;


            // Get Historical Data
            string CasesCommandText;
            int RowOrdinal;
            DataRow lastRow = null;

            cmd.Parameters.Clear();

            if (SeriesName != "")
            {
                CasesCommandText = "SELECT [" + ObjectEscape(KeyTimeColumnName) + "], [" + ObjectEscape(ColumnName) + "] FROM [" + ObjectEscape(ModelName) + "].CASES WHERE [" + ObjectEscape(KeyColumnName) + "] = @Series";
                cmd.Parameters.Add("Series",SeriesName);
            }
            else
            {
                CasesCommandText = "SELECT [" + ObjectEscape(KeyTimeColumnName) + "], [" + ObjectEscape(ColumnName) + "] FROM [" + ObjectEscape(ModelName) + "].CASES ";
            }

            if (NumHistoricalPoints > 0)
            {
                // Change command text to retrieve the last N points
                CasesCommandText = "SELECT TOP " + NumHistoricalPoints.ToString() + " t.* FROM (" +
                    CasesCommandText + ") AS t ORDER BY t.[" + ObjectEscape(KeyTimeColumnName) + "] DESC";
                CasesCommandText = "SELECT * FROM (" + CasesCommandText + ") AS s ORDER BY [" + ObjectEscape(KeyTimeColumnName) + "]";
            }

            object[] values = new object[3];
            cmd.CommandText = CasesCommandText;
            rdr = cmd.ExecuteReader();
            values[0]=0;
            values[2]=null;
            while (rdr.Read())
            {
                values[1] = rdr.GetDouble(1);
                lastRow = Result.Rows.Add(values);
            }
            rdr.Close();

            // Fix up the rowset - copy the last value to the predicted column, and fill out the ordinal column
            lastRow[2] = lastRow[1];
            RowOrdinal = -Result.Rows.Count;
            foreach (DataRow row in Result.Rows)
            {
                row[0] = ++RowOrdinal;
            }

            // Get predicted data
            string PredictionCommandText;

            cmd.Parameters.Clear();
            if (SeriesName != "")
            {
                PredictionCommandText = "SELECT FLATTENED PredictTimeSeries([" + ObjectEscape(ColumnName) +
                    "], @NumPredictions ) FROM [" + ObjectEscape(ModelName) + "] WHERE [" + ObjectEscape(KeyColumnName) + "] = @Series";
                cmd.Parameters.Add("Series", SeriesName);
            }
            else
            {
                PredictionCommandText = "SELECT FLATTENED PredictTimeSeries([" + ObjectEscape(ColumnName) +
                    "], @NumPredictions ) FROM [" + ObjectEscape(ModelName) + "]";
            }
            cmd.Parameters.Add("NumPredictions", NumPredictions);

            cmd.CommandText = PredictionCommandText;
            rdr = cmd.ExecuteReader();
            values[1] = null;
            while (rdr.Read())
            {
                values[0] = ++RowOrdinal;
                values[2] = rdr.GetDouble(1);
                Result.Rows.Add(values);
            }
            rdr.Close();


            return Result;
        }

        [SafeToPrepare(true)]
        public DataTable TimeSeriesReportNested(string ModelName, string ColumnName, string SeriesName, int NumHistoricalPoints, int NumPredictions)
        {
            AdomdCommand cmd = new AdomdCommand();
            AdomdDataReader rdr;
            string KeyTimeColumnName = "";
            string KeyColumnName = "";
            string NestedTableName = "";
            int count = 0;


            // Validate model name and type
            MiningModel model = Context.MiningModels.Find(ModelName);
            if (model == null)
                throw new SystemException("Model not found");
            if (model.Algorithm != "Microsoft_Time_Series")
                throw new SystemException("Specified model is not time series");

            // Find nested table, validate key column, and validate selected column name and type
            MiningModelColumn KeyColumn = null;
            MiningModelColumn PredictableColumn = null;
            foreach (MiningModelColumn column in model.Columns)
            {
                if (column.Content == "Key Time")
                {
                    // Should not be a key time column at the case level
                    throw new SystemException("Key Time at case level not supported");
                }
                if (column.Content == "Key")
                {
                    KeyColumn = column;
                    KeyColumnName = column.Name;
                }
                if (column.IsTable)
                {
                    NestedTableName = column.Name;
                    foreach (MiningModelColumn nestedColumn in column.Columns)
                    {
                        if (nestedColumn.Content == "Key Time")
                            KeyTimeColumnName = nestedColumn.Name;
                        if (ColumnName == nestedColumn.Name)
                        {
                            PredictableColumn = nestedColumn;
                        }
                    }
                    if (PredictableColumn == null)
                        throw new SystemException("Column not found");
                    if (!PredictableColumn.IsPredictable)
                        throw new SystemException("Specified column not predictable");
                }
            }

            if (KeyTimeColumnName == "")  // This could only happen with a malformed time series models, which shouldn't be possible
                throw new SystemException("Something really bad happened");

            // Validate series name
            if (SeriesName == "")
                throw new SystemException("Series Name Required");

            count = 0;
            foreach (MiningValue value in KeyColumn.Values)
            {
                if (value.ToString() == SeriesName)
                    count++;
            }
            if (count != 1)
                throw new SystemException("Invalid series name");

            // Validate integer inputs
            if (NumPredictions < 1 || NumHistoricalPoints < 0)
                throw new SystemException("Negative count");


            // Create result set
            DataTable Result = new DataTable(SeriesName);
            Result.Columns.Add("Ordinal", typeof(int));
            Result.Columns.Add(ColumnName, typeof(double));
            Result.Columns.Add("Predicted " + ColumnName, typeof(double));

            // Return empty result set if preparing
            if (Context.ExecuteForPrepare)
                return Result;

            // Template for historical query
            // SELECT * FROM (select FLATTENED 
            //       (SELECT [Year Month],[Store Cost] FROM 
            //                      TopCount([Time],[Year Month],5)) 
            //        AS ex FROM [Store Forecasting].CASES 
            //        WHERE Store='Store 1') as t 
            //  ORDER BY [ex.Year Month]

            // Get Historical Data
            string CasesCommandText;
            int RowOrdinal;
            DataRow lastRow = null;

            cmd.Parameters.Clear();

            if (NumHistoricalPoints > 0)
            {
                CasesCommandText = "SELECT * FROM (SELECT FLATTENED (SELECT [" + ObjectEscape(KeyTimeColumnName) + "], [" +
                            ObjectEscape(ColumnName) + "] FROM TopCount([" + ObjectEscape(NestedTableName) + "], [" +
                            ObjectEscape(KeyTimeColumnName) + "], " + NumHistoricalPoints.ToString() + ")) AS ex FROM [" +
                            ObjectEscape(ModelName) + "].CASES WHERE [" + ObjectEscape(KeyColumnName) + "]=@Series) AS t " +
                            "ORDER BY [ex." + ObjectEscape(KeyTimeColumnName) + "]";
            }
            else
            {
                // use simpler query  SELECT FLATTENED (SELECT [Year Month],[Store Cost] FROM  [Time]) FROM [Store Forecasting].CASES WHERE Store='Store 1'
                CasesCommandText = "SELECT FLATTENED (SELECT [" + ObjectEscape(KeyTimeColumnName) + "], [" +
                            ObjectEscape(ColumnName) + "] FROM [" + ObjectEscape(NestedTableName) + "])  FROM [" +
                            ObjectEscape(ModelName) + "].CASES WHERE [" + ObjectEscape(KeyColumnName) + "]=@Series";
            }
            cmd.Parameters.Add("Series", SeriesName);

            object[] values = new object[3];
            cmd.CommandText = CasesCommandText;
            rdr = cmd.ExecuteReader();
            values[0] = 0;
            values[2] = null;
            while (rdr.Read())
            {
                values[1] = rdr.GetDouble(1);
                lastRow = Result.Rows.Add(values);
            }
            rdr.Close();

            // Fix up the rowset - copy the last value to the predicted column, and fill out the ordinal column
            lastRow[2] = lastRow[1];
            RowOrdinal = -Result.Rows.Count;
            foreach (DataRow row in Result.Rows)
            {
                row[0] = ++RowOrdinal;
            }


            // Get predicted data

            // Template Query
            // SELECT FLATTENED  
            //   (SELECT PredictTimeSeries([Store Cost],5) FROM Time) AS ts 
            //   FROM [Store Forecasting] WHERE [Store] = 'Store 1'
            string PredictionCommandText;


            PredictionCommandText = "SELECT FLATTENED (SELECT PredictTimeSeries([" 
                + ObjectEscape(ColumnName) +"], @NumPredictions ) FROM [" + ObjectEscape(NestedTableName)
                + "]) AS ts FROM [" + ObjectEscape(ModelName) + "] WHERE [" + ObjectEscape(KeyColumnName) + "] = @Series";
            cmd.Parameters.Add("NumPredictions", NumPredictions);

            cmd.CommandText = PredictionCommandText;
            rdr = cmd.ExecuteReader();
            values[1] = null;
            while (rdr.Read())
            {
                values[0] = ++RowOrdinal;
                values[2] = rdr.GetDouble(1);
                Result.Rows.Add(values);
            }
            rdr.Close();

            return Result;
        }
    }
}