using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using Waypoint.Models;
using Waypoint.Models.Queries;
using Dapper;
using Dapper.Contrib;
using System.Data;
using Waypoint.Models.Contracts;

namespace Waypoint.DAL
{
    public class TrackingQueryHandler : IQueryHandler<TrackingQuery, List<TrackingQueryResult>>, IQueryHandler<TrackingCountQuery, int>
    {
        public string ConnectionString { get; set; }

        public TrackingQueryHandler()
        {
        }
        public TrackingQueryHandler(string connectionString)
        {
            ConnectionString = connectionString;
        }
        public async Task<List<TrackingQueryResult>> Handle(TrackingQuery query)
        {
            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                sqlConnection.Open();

                var vehicleGroupIds = GetUserVehicleGroupId(query, sqlConnection);
                var sqlTemplate = await BuildSql(query, vehicleGroupIds, false);

                var map = new Dictionary<string, TrackingQueryResult>();
                var map_t = new Dictionary<string, TrackingData>();

                var list = await sqlConnection.QueryAsync<TrackingQueryResult, TrackingData, TrackingAlert, TrackingQueryResult>
                    (sqlTemplate.Sql, (v, t, a) =>
                    {
                        TrackingQueryResult vehicle;
                        var key = $"id:{v.VehicleId}";
                        var key_t = $"id:{v.VehicleId}_cid:{t.CrumbId}";

                        if (!map.TryGetValue(key, out vehicle))
                        {
                            map[key] = vehicle = v;
                        }
                        if (t != null) //left join
                        {
                            TrackingData crumb;
                            if (!map_t.TryGetValue(key_t, out crumb))
                            {
                                map_t[key_t] = crumb = t;
                                vehicle.TrackingItems.Add(t);
                            }

                            if (a != null)
                            {
                                crumb.TrackingAlerts.Add(a);
                            }
                        }
                        return vehicle;
                    }, sqlTemplate.Template.Parameters, splitOn: "CrumbId,AlertId")
                    .ContinueWith(t => t.Result.ToList());

                var trackingResult = map.Values.ToList();
                if (!query.Filter.IsNullOrWhiteSpace())
                {
                    trackingResult = trackingResult
                        .Where(t => t.VehicleName.Has(query.Filter) || t.PlateNo.Has(query.Filter) || t.IMEI.Has(query.Filter) || t.Phone.Has(query.Filter) || t.ChassisNo.Has(query.Filter))
                        .ToList();
                }

                return await Task.FromResult(trackingResult);
            }
        }

        public async Task<int> Handle(TrackingCountQuery query)
        {
            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                sqlConnection.Open();

                var vehicleGroupIds = GetUserVehicleGroupId(query, sqlConnection);
                var sqlTemplate = await BuildSql(query, vehicleGroupIds, true);

                var count = await sqlConnection.ExecuteScalarAsync<int>
                    (sql: sqlTemplate.Sql, param: sqlTemplate.Template.Parameters);

                return await Task.FromResult(count);
            }
        }

        private List<int> GetUserVehicleGroupId(TrackingQuery query, SqlConnection sqlConnection)
        {
            try
            {
                if (query.IsAdminUser)
                {
                    return new List<int>();
                }
                var vehicleGroupIds = sqlConnection
                    .Query<int>("select VehicleGroupId from UserVehicleGroups where UserId = @UserId", param: new { query.UserId })
                    .ToList();
                return vehicleGroupIds;
            }
            catch (Exception x)
            {
                throw;
            }
        }

        private async Task<SqlTemplate> BuildSql(TrackingQuery query, List<int> vehicleGroupIds, bool isCount)
        {
            var builder = new Dapper.SqlBuilder();
            var sql = "";
            var template = builder.AddTemplate(
@"
/**filtered_vehicles**/

select /**select_result**/ from
(
	/**select**/
	from VehicleStatuses vs WITH (NOLOCK)
	/**join**/
	join ContractVehicleView v with(nolock)  on v.VehicleId = vs.VehicleId and v.TenantId = vs.TenantId
	--join Vehicles v with(nolock)  on v.VehicleId = vs.VehicleId and v.TenantId = vs.TenantId
	--join Devices d with(nolock)  on d.VehicleId = v.VehicleId
	left join EventTranslation et with(nolock)  on et.EventId = vs.EventId
   inner join ContractDetails cd with(nolock)  on vs.VehicleId = cd.VehicleId and cd.IsActive = 1
	/**join_alerts**/
	/**where**/
) t
/**paging**/
/**orderby**/

OPTION(RECOMPILE,MAXDOP 1)
"
);
            builder.Select(@"SELECT ROW_NUMBER() OVER (ORDER BY v.VehicleId asc, vs.TimeStamp asc) RowNo, DENSE_RANK() OVER (ORDER BY v.VehicleId asc) DenseRowNo,
		--v.VehicleId, v.Name VehicleName, v.Make, v.Model, v.PlateNo, d.IMEI, d.Phone,
		v.VehicleId, v.VehicleName VehicleName, v.Make, v.Model, v.PlateNo, v.IMEI, v.Phone, v.ChassisNo,
        v.StreamingDeviceIMEI, v.StreamingDeviceType,
		vs.CrumbId, vs.TimeStamp, Latitude Lat, Longitude Lng, Angle, HDOP, Speed, vs.CreationDate, vs.Temperature, vs.IsEngineOn, vs.BrakingHarsh, vs.BrakingExtreme, vs.AccelerationHarsh,
		ISNULL(et.EventId, 0) EventId, ISNULL(et.Name, '') EventName");

            builder.Where("vs.TenantId = @tenantId", new { tenantId = query.TenantId });
            //builder.Where("vs.TimeStamp ")
            //  builder.InnerJoin("ContractDetails cd on c.VehicleId = cd.VehicleId && cd.IsActive = true");

            if (query.VehicleState == VehicleState.Active)
            {
                var from = DateTime.UtcNow.AddHours(-1);
                builder.Where("vs.TimeStamp >= @from", new { from = from });
            }
            else if (query.OnlyLastLocation)
            {
                builder.Where("vs.TimeStamp >= @from", new { from = DateTime.UtcNow.Subtract(query.Time) });
            }
            else
            {
                builder.Where("vs.TimeStamp >= @from", new { from = DateTime.UtcNow.Subtract(query.Time) });
            }

            if (query.VehicleId > 0)
            {
                builder.Where("v.VehicleId = @vehicleId", new { vehicleId = query.VehicleId });
            }

            if (vehicleGroupIds.Count > 0)
            {
                builder.Where(@"(exists (
    select * from
        Vehicles vv with(nolock)  join
        VehicleItems vi with(nolock)  on vv.VehicleId = vi.VehicleId join
        VehicleGroups vg with(nolock)  on vi.VehicleGroupId = vg.VehicleGroupId
    where vg.VehicleGroupId in @VehicleGroupIds and v.VehicleId = vv.VehicleId))", new { VehicleGroupIds = vehicleGroupIds });
            }

            if (query.IncludeAlerts)
            {
                builder.Select(@"tac.AlertId, a.Name, ISNULL(a.Description, '') AlertDescription, a.Priority, ta.FirstTime, ta.LastTime");

                builder.AddClause("join_alerts",
               @"left join TriggeredAlertCrumbs tac with(nolock)  on tac.CrumbId = vs.CrumbId
				 left join TriggeredAlerts ta with(nolock)  on tac.TriggeredAlertId = ta.TriggeredAlertId
				 left join Alerts a with(nolock)  on tac.AlertId = a.AlertId and a.IsReported = 1");

                //builder.Where("(a.IsReported is NULL OR a.IsReported = 1)");
            }

            if (!isCount)
            {
                builder.AddClause("select_result", "*");
                builder.OrderBy("DenseRowNo asc, RowNo asc");
                if (query.IsPaged)
                {
                    _IncludePaging(query, builder);
                }
            }
            else
            {
                builder.AddClause("select_result", "count(*)");
            }

            sql = template.ResolveSql();
            return new SqlTemplate
            {
                Sql = sql,
                Template = template
            };
        }

        private void _IncludePaging(TrackingQuery query, SqlBuilder builder)
        {
            var page = query.Page >= 1 ? query.Page : 1;
            builder.AddClause("paging",
                @"WHERE RowNo >= ((@page-1)*@pagesize)+1 AND RowNo <= ((@page)*@pagesize)",
                new { page = page, pagesize = query.PageSize });
        }


        private async Task _FilterByVehicles(VehicleFilters filters, SqlBuilder builder)
        {
            var filterBuilder = new Dapper.SqlBuilder();
            var filterTemplate = filterBuilder.AddTemplate(
@"
declare @tbl_vehicle_ids table(Id int);

insert into @tbl_vehicle_ids(Id)
select ref.value('.[1]', 'int')
from @vehicle_ids.nodes('List/Item') n(ref);"
);

            var filteredVehiclesXml = await FilteredoXml(filters.FilteredVehicleIds);
            filterBuilder.AddParameter("vehicle_ids", filteredVehiclesXml, DbType.Xml);

            builder.AddClause("filtered_vehicles", filterTemplate.ResolveSql(), filterTemplate.Parameters);
            builder.Where("exists (select * from @tbl_vehicle_ids where Id = v.VehicleId)");
        }


        private void _IncludePaging(VehicleQuery query, SqlBuilder builder)
        {
            builder.AddClause("paging",
                @"WHERE DenseRowNo >= ((@page-1)*@pagesize)+1 AND DenseRowNo <= ((@page)*@pagesize)",
                new { page = query.Page, pagesize = query.PageSize });
        }

        public async Task<List<int>> FilterVehicles(SqlConnection connection, VehicleQuery query)

        {
            var builder = new Dapper.SqlBuilder();

            var waypointBuilder = new Dapper.SqlBuilder();
            var waypointTemplate = waypointBuilder.AddTemplate(
@"select v.VehicleId
from	Vehicles v with(nolock)  join
		Devices d with(nolock)  on v.VehicleId = d.VehicleId
where	v.TenantId = @tenantId
		and (v.Name like @filter or v.PlateNo = @filter or d.IMEI = @filter or d.Phone = @filter)
		and exists(select * from RfidItems where VehicleId = v.VehicleId and TenantId = v.TenantId and ScannedOn >= @from and ScannedOn < @to)"
);

            waypointBuilder.AddParameters(new
            {
                tenantId = query.TennantId,
                filter = query.VehiclesFilter.SqlLike(),
                from = query.From,
                to = query.To
            });

            var vehicleHistory = await connection.QueryAsync<int>(waypointTemplate.ResolveSql(), waypointTemplate.Parameters)
                    .ContinueWith(t => t.Result.ToList());

            return await Task.FromResult(vehicleHistory);
        }


        public async Task<string> FilteredoXml<T>(List<T> list)
        {
            var xml = new XElement("List", list.Select(x =>
                new XElement("Item", x.ToString()))
            );
            var result = xml.ToString();
            return await Task.FromResult(result);
        }
    }
}