using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tango.BL;
using Tango.BL.Entities;
using Tango.Core;
using Tango.Core.DI;
using Tango.Core.ExtensionMethods;
using Tango.FSE.BL;
using Tango.FSE.Common.Connection;
using Tango.FSE.Common.SQL;
using Tango.PPC.Shared.SQL;
using Tango.Transport;

namespace Tango.FSE.UI.SQL
{
    public class DefaultRemoteSqlProvider : ExtendedObject, IRemoteSqlProvider
    {
        private List<String> protected_keywords = new List<string>()
        {
            "DROP",
            "DELETE",
            "ALTER",
            "CREATE"
        };

        private List<String> protected_tables = new List<string>()
        {
            nameof(DAL.Remote.DB.ACTION_LOGS),
            nameof(DAL.Remote.DB.ADDRESS),
            nameof(DAL.Remote.DB.APPLICATION_DISPLAY_PANEL_VERSIONS),
            nameof(DAL.Remote.DB.APPLICATION_FIRMWARE_VERSIONS),
            nameof(DAL.Remote.DB.APPLICATION_OS_VERSIONS),
            nameof(DAL.Remote.DB.CARTRIDGE_TYPES),
            nameof(DAL.Remote.DB.CCT),
            nameof(DAL.Remote.DB.COLOR_CATALOGS),
            nameof(DAL.Remote.DB.COLOR_CATALOGS_GROUPS),
            nameof(DAL.Remote.DB.COLOR_CATALOGS_ITEMS),
            nameof(DAL.Remote.DB.COLOR_CATALOGS_ITEMS_RECIPES),
            nameof(DAL.Remote.DB.COLOR_SPACES),
            nameof(DAL.Remote.DB.CONTACT),
            nameof(DAL.Remote.DB.CUSTOMER),
            nameof(DAL.Remote.DB.DISPENSER_TYPES),
            nameof(DAL.Remote.DB.EMBEDDED_FIRMWARE_VERSIONS),
            nameof(DAL.Remote.DB.EVENT_TYPES),
            nameof(DAL.Remote.DB.FIBER_SHAPES),
            nameof(DAL.Remote.DB.FIBER_SYNTHS),
            nameof(DAL.Remote.DB.FSE_VERSIONS),
            nameof(DAL.Remote.DB.HARDWARE_BLOWER_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_BREAK_SENSOR_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_DANCER_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_MOTOR_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_PID_CONTROL_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_SPEED_SENSOR_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_VERSIONS),
            nameof(DAL.Remote.DB.HARDWARE_WINDER_TYPES),
            nameof(DAL.Remote.DB.IDS_PACK_FORMULAS),
            nameof(DAL.Remote.DB.JOB_RUNS),
            nameof(DAL.Remote.DB.LINEAR_MASS_DENSITY_UNITS),
            nameof(DAL.Remote.DB.LIQUID_TYPES),
            nameof(DAL.Remote.DB.LIQUID_TYPES_RMLS),
            nameof(DAL.Remote.DB.MACHINES_EVENTS),
            nameof(DAL.Remote.DB.MACHINE_STUDIO_VERSIONS),
            nameof(DAL.Remote.DB.MACHINE_VERSIONS),
            nameof(DAL.Remote.DB.MEDIA_CONDITIONS),
            nameof(DAL.Remote.DB.MEDIA_MATERIALS),
            nameof(DAL.Remote.DB.MEDIA_PURPOSES),
            nameof(DAL.Remote.DB.MID_TANK_TYPES),
            nameof(DAL.Remote.DB.PERMISSION),
            nameof(DAL.Remote.DB.PROCESS_PARAMETERS_TABLES),
            nameof(DAL.Remote.DB.PROCESS_PARAMETERS_TABLES_GROUPS),
            nameof(DAL.Remote.DB.PUBLISHED_PROCEDURE_PROJECTS),
            nameof(DAL.Remote.DB.PUBLISHED_PROCEDURE_PROJECTS_VERSIONS),
            nameof(DAL.Remote.DB.RML),
            nameof(DAL.Remote.DB.RMLS_SPOOLS),
            nameof(DAL.Remote.DB.ROLE),
            nameof(DAL.Remote.DB.ROLES_PERMISSIONS),
            nameof(DAL.Remote.DB.SITE),
            nameof(DAL.Remote.DB.SITES_CATALOGS),
            nameof(DAL.Remote.DB.SITES_RMLS),
            nameof(DAL.Remote.DB.SPOOL_TYPES),
            nameof(DAL.Remote.DB.TANGO_UPDATES),
            nameof(DAL.Remote.DB.TANGO_VERSIONS),
            nameof(DAL.Remote.DB.TECH_CONTROLLERS),
            nameof(DAL.Remote.DB.TECH_DISPENSERS),
            nameof(DAL.Remote.DB.TECH_HEATERS),
            nameof(DAL.Remote.DB.TECH_IOS),
            nameof(DAL.Remote.DB.TECH_MONITORS),
            nameof(DAL.Remote.DB.TECH_VALVES),
            nameof(DAL.Remote.DB.USER),
            nameof(DAL.Remote.DB.USERS_ROLES),
            nameof(DAL.Remote.DB.WINDING_METHODS),
        };

        [TangoInject]
        private IMachineProvider MachineProvider { get; set; }

        [TangoInject]
        private FSEServicesContainer Services { get; set; }

        public async Task<RemoteSqlCommandResult> ExecuteSqlCommandAsync(RemoteSqlCommand command)
        {
            if (command.Mode == RemoteSqlCommandMode.Global || command.Mode == RemoteSqlCommandMode.Both)
            {
                var machines = await Services.MachinesService.GetAllMachines();
                ValidateSqlStatement(command.SQL, machines);
            }

            LogManager.Log($"Executing remote SQL command:\n{command.ToJsonString()}");

            RemoteSqlCommandResult result = new RemoteSqlCommandResult();

            if (command.Mode == RemoteSqlCommandMode.Local || command.Mode == RemoteSqlCommandMode.Both)
            {
                LogManager.Log("Executing remote SQL command against the remote machine database.");

                if (!MachineProvider.IsPPCAvailable)
                {
                    throw new InvalidOperationException("Could not execute the remote SQL command on the local machine's database.\nNo machine connected or connection type is not supported.");
                }

                try
                {
                    var response = await MachineProvider.MachineOperator.SendGenericRequest<ExecuteSqlRequest, ExecuteSqlResponse>(new ExecuteSqlRequest()
                    {
                        SQL = command.SQL
                    }, new TransportRequestConfig() { Timeout = TimeSpan.FromSeconds(command.Timeout) });

                    result.LocalAffectedRecords = response.AffectedRecords;
                    result.LocalDatSet = response.DataSet;
                }
                catch (Exception ex)
                {
                    LogManager.Log(ex, "Remote SQL command local execution failed.");
                    result.HasLocalError = true;
                    result.LocalError = ex.FlattenMessage();
                    return result;
                }
            }

            if (command.Mode == RemoteSqlCommandMode.Global || command.Mode == RemoteSqlCommandMode.Both)
            {
                LogManager.Log("Executing remote SQL command against the global database.");

                try
                {
                    using (ObservablesContext db = ObservablesContext.CreateDefault())
                    {
                        using (SqlConnection connection = new SqlConnection(db.Database.Connection.ConnectionString))
                        {
                            connection.AccessToken = ObservablesContext.GetActualDataSource().AccessToken;
                            connection.Open();

                            SqlCommand cmd = new SqlCommand(command.SQL, connection);
                            cmd.CommandTimeout = command.Timeout;

                            SqlDataReader reader = await cmd.ExecuteReaderAsync();
                            result.GlobalAffectedRecords = reader.RecordsAffected;
                            result.GlobalDataSet = await RemoteSqlDataSet.Load(reader);
                        }
                    }
                }
                catch (Exception ex)
                {
                    LogManager.Log(ex, "Remote SQL command remote execution failed.");
                    result.HasGlobalError = true;
                    result.GlobalError = ex.FlattenMessage();
                }
            }

            LogManager.Log($"Remote SQL command completed with result:\n{result.ToJsonString()}");

            return result;
        }

        private void ValidateSqlStatement(String sql, List<Machine> machines)
        {
            sql = sql.Trim().ToUpper();

            foreach (var keyword in protected_keywords)
            {
                if (sql.Contains(keyword))
                {
                    throw new InvalidOperationException($"SQL command containing '{keyword}' cannot be executed against the global database.");
                }
            }

            foreach (var table in protected_tables)
            {
                if ((sql.Contains("INSERT") || sql.Contains("UPDATE")) && sql.Contains(table))
                {
                    throw new InvalidOperationException($"SQL command containing INSERT or UPDATE statements cannot be used on table '{table}' when executing against the global database.");
                }
            }

            if (sql.Contains("INSERT") || sql.Contains("UPDATE"))
            {
                if (!sql.Contains(MachineProvider.Machine.SerialNumber.ToUpper()))
                {
                    throw new InvalidOperationException($"SQL command containing INSERT or UPDATE statements must contain the connected machine's serial number, when executing against the global database.");
                }

                if (machines.Where(x => x.SerialNumber != MachineProvider.Machine.SerialNumber).Any(x => sql.Contains(x.SerialNumber.ToUpper())))
                {
                    throw new InvalidOperationException($"SQL command containing INSERT or UPDATE statements cannot contain a serial number other than the connected machines' serial number, when executing against the global database");
                }
            }
        }

        public RemoteSqlCommandResult ExecuteSqlCommand(RemoteSqlCommand command)
        {
            return ExecuteSqlCommandAsync(command).Result;
        }
    }
}