using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using Deltares.Standard.Extensions;
using System.Text;
using Deltares.Standard.Application;
//using log4net.Config;
//using log4net.Repository.Hierarchy;

namespace Deltares.Dam.Data.Sensors
{
    /// <summary>
    /// Import sensor data from Excel sheet
    /// </summary>
    public class SensorImportFromExcelSheet
    {
        const string SensorProfileSheetName = "SensorProfileID";
        const string SensorGroupSheetName = "SensorGroupID";
        const string SensorSheetName = "SensorID";
        const string DikeLineInProfileSheetName = "DikeLineInProfile";
        const string IDColumnName = "ID";
        const string SensorNameColumnName = "SensorName";
        const string PLLineMappingColumnName = "PLLine-Mapping";
        const string ProfileColumnName = "Profile";

        internal readonly static LogHelper Logger = LogHelper.Create("Main Program.");
        
        /// <summary>
        /// Reads the sensor data from excel.
        /// </summary>
        /// <param name="sourceFile">The source file.</param>
        /// <param name="dike">The dike.</param>
        /// <exception cref="System.InvalidOperationException">
        /// Error reading excel file
        /// or
        /// The + PLLineMappingColumnName + column has an error. Make sure the cells are not empty and are formatted as text
        /// </exception>
        public static void ReadSensorDataFromExcel(string sourceFile, Dike dike)
        {
            var sheets = new[]
                         {
                             SensorProfileSheetName, 
                             SensorGroupSheetName, 
                             SensorSheetName,
                             DikeLineInProfileSheetName
                         };

            DataSet ds = null;
            string fileName = sourceFile;
            var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
            using (var conn = new OleDbConnection(connectionString))
            {
                ds = FillDataSet(conn, ds, sheets);
            }

            if (ds == null)
            {
                throw new InvalidOperationException("Error reading excel file");
            }

            var profiles = ds.Tables[SensorProfileSheetName].AsEnumerable();
            var groups = ds.Tables[SensorGroupSheetName].AsEnumerable();
            
            var sensors = ParseSensors(ds);

            var count = 0;
            foreach (var location in dike.Locations)
            {
                var repository = new SensorRepository(location);

                int id;
                if (ParseSensorProfileWorksheet(location, profiles, out id))
                {
                    continue;
                }

                // Parsing sensor groups
                if (ParseSensorGroups(groups, id, sensors, repository))
                {
                    continue;

                }

                count++;
            }

            Logger.LogInfo(string.Format("Successfully imported {0} sensor groups(s).", count));
            Logger.LogInfo(string.Format("Successfully imported {0} sensor profile(s).", count));

        }



        private static List<Sensor> ParseSensors(DataSet ds)
        {
            var sensors = new List<Sensor>();
            var sensorRows = ds.Tables[SensorSheetName].AsEnumerable();
            int count = 0;

            foreach (var row in sensorRows)
            {

                if (row.IsNull(IDColumnName))
                {
                    Logger.LogWarning(string.Format(
                        "Error reading row {0} in worksheet {1}. This row will be skipped.", count + 2, SensorSheetName));
                    continue;
                }
                count++;

                var id = (int) row.Field<double>(IDColumnName);
                var name = row.Field<string>(SensorNameColumnName);
                var relativeLocation = row.Field<double>("RelativeLocationSensorAlongProfileManual");
                var depth = row.Field<double>("DepthSensor");
                var enumTypeIgnoreCase = row.Field<string>("SensorType").ToEnumTypeIgnoreCase<SensorType>();

                var sensor = new Sensor
                {
                    ID = id,
                    Name = name,
                    RelativeLocation = relativeLocation,
                    Depth = depth,
                    Type = enumTypeIgnoreCase
                    //1	255364,05597700000	598646,53034800000
                    //XRd = 255364.05597700000,
                    //YRd = 598646.53034800000,
                };

                try
                {
                    var mappings = row.Field<string>(PLLineMappingColumnName)
                        .Split(new[] {';'}, StringSplitOptions.RemoveEmptyEntries)
                        .Select(tag => tag.Trim())
                        //.Where(tag => !string.IsNullOrWhiteSpace(tag))
                        .ToArray();

                    foreach (var mapping in mappings)
                    {
                        switch (mapping)
                        {
                            case "1":
                                sensor.Add(PLLineType.PL1);
                                break;
                            case "2":
                                sensor.Add(PLLineType.PL2);
                                break;
                            case "3":
                                sensor.Add(PLLineType.PL3);
                                break;
                            case "4":
                                sensor.Add(PLLineType.PL4);
                                break;
                        }
                    }
                }
                catch (Exception e)
                {
                    throw new InvalidOperationException(
                        "The " + PLLineMappingColumnName +
                        " column has an error. Make sure the cells are not empty and are formatted as text", e);
                }

                Logger.LogInfo(sensor.ToString());
                sensors.Add(sensor);
            }

            Logger.LogInfo(string.Format("Successfully imported {0} sensor(s).", count));
            return sensors;
        }

        /// <summary>
        /// Parses the sensor groups.
        /// </summary>
        /// <param name="sensorGroups">The sensor groups.</param>
        /// <param name="id">The identifier.</param>
        /// <param name="sensors">The sensors.</param>
        /// <param name="repository">The repository.</param>
        /// <returns></returns>
        /// <exception cref="System.InvalidOperationException">
        /// </exception>
        private static bool ParseSensorGroups(IEnumerable<DataRow> sensorGroups, int id, List<Sensor> sensors, SensorRepository repository)
        {
            const string sensorSelectionColumnName = "SensorSelection";
            string currentSensorId = "";
            try
            {

                string[] groupIds = null;
                foreach (var sensorGroup in sensorGroups)
                {
                    var idValue = (int)sensorGroup.Field<double>(IDColumnName);
                    if (idValue != id)
                    {
                        continue;
                    }

                    var groupIdValue = sensorGroup.Field<string>(sensorSelectionColumnName);

                    if (groupIdValue == null)
                    {
                        Logger.LogWarning(string.Format("Error reading the value from column {0} for group id {1}",
                            sensorSelectionColumnName, id));
                    }

                    groupIds = groupIdValue.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                    break;
                }

                if (groupIds == null)
                {
                    Logger.LogWarning(string.Format("Couldn't find group id '{0}' in worksheet {1}", id,
                        SensorGroupSheetName));
                    return true;
                }

                foreach (var groupId in groupIds)
                {
                    currentSensorId = groupId;
                    var single = sensors.Single(s => s.ID.ToString() == groupId);
                    repository.Add(single);
                }
            }
            catch (InvalidOperationException ioe)
            {
                if (!string.IsNullOrWhiteSpace(currentSensorId))
                {
                    string message = string.Format("Error finding sensor. The sensor with ID '{0}' was not found in the Excel worksheet '{1}'", currentSensorId, SensorSheetName);
                    throw new InvalidOperationException(message, ioe);
                }
                throw ioe;
            }
            catch (Exception e)
            {
                throw new InvalidOperationException(
                    string.Format("An unknown error occurred in column " + sensorSelectionColumnName + " in the Excel sheet " + SensorGroupSheetName +
                    ". Used the following location ID: " + id, e));
            }

            return false;
        }

        /// <summary>
        /// Parses the sensor profile worksheet.
        /// </summary>
        /// <param name="location">The location.</param>
        /// <param name="sensorLocations">The sensor locations.</param>
        /// <param name="id">The identifier.</param>
        /// <returns></returns>
        /// <exception cref="System.InvalidOperationException">An error occurred while parsing row in worksheet  + SensorProfileSheetName</exception>
        private static bool ParseSensorProfileWorksheet(Location location, EnumerableRowCollection<DataRow> sensorLocations, out int id)
        {
            id = -1;
            try
            {
                var record = sensorLocations
                    .Where(x => x.Field<string>(ProfileColumnName) == location.Name)
                    .Select(x => new
                    {
                        ID = GetLocationIDField(x),
                        GroupID = GetGroupIDField(x),
                        Alias = GetNameAliasField(x),
                        InputPL1OuterWaterLevel = GetPL1WaterLevelRiverSide(x),
                        InputPL1PLLineOffsetBelowDikeTopAtRiver = GetPL1OffsetBelowDikeTopAtRiver(x),
                        InputPL1PLLineOffsetBelowDikeTopAtPolder = GetPL1OffsetBelowDikeTopAtPolder(x),
                        InputPL1PLLineOffsetBelowShoulderBaseInside = GetPL1OffsetBelowShoulderBaseInside(x),
                        InputPL1PLLineOffsetBelowDikeToeAtPolder = GetPL1OffsetBelowDikeToeAtPolder(x),
                        InputPL1PolderLevel = GetPL1PolderLevelSensorType(x),
                        InputPL3 = GetInputPl3(x),
                        InputPL4 = GetInputPl4(x)
                    }).SingleOrDefault();

                if (record == null)
                {
                    Logger.LogWarning(string.Format("Location profile ID '{0}' not found in worksheet {1}. Location profile skipped.",
                        location.Name, SensorProfileSheetName));
                    return true;
                }

                id = record.GroupID;

                var sensorLocation = location.SensorLocation;
                sensorLocation.Group.ID = record.GroupID;
                sensorLocation.Alias = record.Alias;
                sensorLocation.SourceTypePl1WaterLevelAtRiver = record.InputPL1OuterWaterLevel;
                sensorLocation.SourceTypePl1PlLineOffsetBelowDikeTopAtRiver = record.InputPL1PLLineOffsetBelowDikeTopAtRiver;
                sensorLocation.SourceTypePl1PlLineOffsetBelowDikeTopAtPolder = record.InputPL1PLLineOffsetBelowDikeTopAtPolder;
                sensorLocation.SourceTypePl1PlLineOffsetBelowShoulderBaseInside = record.InputPL1PLLineOffsetBelowShoulderBaseInside;
                sensorLocation.SourceTypePl1PlLineOffsetBelowDikeToeAtPolder = record.InputPL1PLLineOffsetBelowDikeToeAtPolder;
                sensorLocation.SourceTypePl1WaterLevelAtPolder = record.InputPL1PolderLevel;
                sensorLocation.SourceTypePl3 = record.InputPL3;
                sensorLocation.SourceTypePl4 = record.InputPL4;
            }
            catch (Exception e)
            {
                throw new InvalidOperationException("An error occurred while parsing row in worksheet " + SensorProfileSheetName, e);
            }
            return false;
        }
        /// <summary>
        /// Fills the data set.
        /// </summary>
        /// <param name="conn">The connection.</param>
        /// <param name="ds">The ds.</param>
        /// <param name="sheets">The sheets.</param>
        /// <returns></returns>
        private static DataSet FillDataSet(OleDbConnection conn, DataSet ds, string[] sheets)
        {
            conn.Open();
            ds = new DataSet();

            foreach (string sheet in sheets)
            {
                // string columns = String.Join(",", columnNames.ToArray());
                const string columns = "*";
                using (var da = new OleDbDataAdapter(
                    "SELECT " + columns + " FROM [" + sheet + "$]", conn))
                {
                    var dt = new DataTable(sheet);
                    da.Fill(dt);
                    ds.Tables.Add(dt);
                }
            }
            return ds;
        }

        /// <summary>
        /// Gets the p l1 water level river side.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        private static DataSourceTypeSensors GetPL1WaterLevelRiverSide(DataRow x)
        {
            DataSourceTypeSensors pl1WaterLevelRiverSide;

            try
            {
                pl1WaterLevelRiverSide = x.Field<string>("InputPL1OuterWaterLevel").ToEnumTypeIgnoreCase<DataSourceTypeSensors>();
            }
            catch (Exception)
            {
                throw;
            }

            return pl1WaterLevelRiverSide;
        }

        /// <summary>
        /// Gets the p l1 offset below dike top at river.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        private static DataSourceTypeSensors GetPL1OffsetBelowDikeTopAtRiver(DataRow x)
        {
            const string columnName = "InputPL1PLLineOffsetBelowDikeTopAtRiver";

            DataSourceTypeSensors pl1OffsetBelowDikeTopAtRiver;
            try
            {
                pl1OffsetBelowDikeTopAtRiver = x.Field<string>(columnName).ToEnumTypeIgnoreCase<DataSourceTypeSensors>();
            }
            catch (Exception)
            {
                throw;
            }

            return pl1OffsetBelowDikeTopAtRiver;
        }

        /// <summary>
        /// Gets the p l1 offset below dike top at polder.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        private static DataSourceTypeSensors GetPL1OffsetBelowDikeTopAtPolder(DataRow x)
        {
            const string columnName = "InputPL1PLLineOffsetBelowDikeTopAtPolder";

            DataSourceTypeSensors pl1OffsetBelowDikeTopAtPolder;
            try
            {
                pl1OffsetBelowDikeTopAtPolder = x.Field<string>(columnName).ToEnumTypeIgnoreCase<DataSourceTypeSensors>();
            }
            catch (Exception)
            {
                throw;
            }

            return pl1OffsetBelowDikeTopAtPolder;
        }

        /// <summary>
        /// Gets the p l1 offset below shoulder base inside.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        private static DataSourceTypeSensors GetPL1OffsetBelowShoulderBaseInside(DataRow x)
        {
            DataSourceTypeSensors pl1OffsetBelowShoulderBaseInside;
            try
            {
                pl1OffsetBelowShoulderBaseInside = x.Field<string>("InputPL1PLLineOffsetBelowShoulderBaseInside").ToEnumTypeIgnoreCase<DataSourceTypeSensors>();
            }
            catch (Exception)
            {
                throw;
            }

            return pl1OffsetBelowShoulderBaseInside;
        }

        /// <summary>
        /// Gets the p l1 offset below dike toe at polder.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        private static DataSourceTypeSensors GetPL1OffsetBelowDikeToeAtPolder(DataRow x)
        {
            DataSourceTypeSensors pl1OffsetBelowDikeToeAtPolder;

            try
            {
                pl1OffsetBelowDikeToeAtPolder = x.Field<string>("InputPL1PLLineOffsetBelowDikeToeAtPolder").ToEnumTypeIgnoreCase<DataSourceTypeSensors>();
            }
            catch (Exception)
            {
                throw;
            }
            return pl1OffsetBelowDikeToeAtPolder;
        }

        /// <summary>
        /// Gets the type of the p l1 polder level sensor.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        private static DataSourceTypeSensors GetPL1PolderLevelSensorType(DataRow x)
        {
            const string columnName = "InputPL1PolderLevel";
            DataSourceTypeSensors pl1PolderLevelSensorType;

            try
            {
                pl1PolderLevelSensorType = x.Field<string>(columnName).ToEnumTypeIgnoreCase<DataSourceTypeSensors>();
            }
            catch (Exception)
            {

                throw;
            }

            return pl1PolderLevelSensorType;
        }

        /// <summary>
        /// Gets the input PL4.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        private static DataSourceTypeSensors GetInputPl4(DataRow x)
        {
            DataSourceTypeSensors dataSourceTypeSensors;

            try
            {
                dataSourceTypeSensors = x.Field<string>("InputPL4").ToEnumTypeIgnoreCase<DataSourceTypeSensors>();
            }
            catch (Exception)
            {

                throw;
            }

            return dataSourceTypeSensors;
        }

        /// <summary>
        /// Gets the input PL3.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        private static DataSourceTypeSensors GetInputPl3(DataRow x)
        {
            DataSourceTypeSensors pl3;

            try
            {
                pl3 = x.Field<string>("InputPL3").ToEnumTypeIgnoreCase<DataSourceTypeSensors>();
            }
            catch (Exception)
            {
                throw;
            }

            return pl3;
        }

        /// <summary>
        /// Gets the name alias field.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">nameAliasField</exception>
        private static string GetNameAliasField(DataRow x)
        {
            var nameAliasField = x.Field<string>("NameAlias");
            if (nameAliasField == null)
            {
                throw new ArgumentNullException("nameAliasField");
            }
            return nameAliasField;
        }

        /// <summary>
        /// Gets the group identifier field.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        /// <exception cref="System.InvalidCastException">Error parsing value :  + x.Field<object>(columnName)</exception>
        private static int GetGroupIDField(DataRow x)
        {
            int groupIdField;
            const string columnName = "SensorGroup";
            try
            {
                groupIdField = (int)x.Field<double>(columnName);
            }
            catch (InvalidCastException)
            {
                throw new InvalidCastException("Error parsing value : " + x.Field<object>(columnName));
            }
            catch (Exception)
            {

                throw;
            }

            return groupIdField;
        }

        /// <summary>
        /// Gets the location identifier field.
        /// </summary>
        /// <param name="x">The x.</param>
        /// <returns></returns>
        /// <exception cref="System.InvalidCastException">Error parsing value :  + x.Field<object>(columnName) + . LocationID should be a numeric value.</exception>
        private static int GetLocationIDField(DataRow x)
        {
            int locationIdField;
            const string columnName = "locationID";

            try
            {
                locationIdField = (int)x.Field<double>(columnName);
            }
            catch (InvalidCastException)
            {
                throw new InvalidCastException("Error parsing value : " + x.Field<object>(columnName) + ". LocationID should be a numeric value.");
            }
            catch (Exception)
            {
                throw;
            }
            return locationIdField;
        }

    }
}