using AdminDAL.DTO.LocationTrack;
using AdminDAL.Query.LocationTrack;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace AdminDAL.CustomCode.LocationTrack
{
    public class LocationTrackDAL : ILocationTrackDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public LocationTrackDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        // GB4 parity: LocationTrackDAL.GetLocationTrack — Type selects the underlying SQL
        // (0=track map, 1=latest position, 2=punch map, 3=today's punch load, 4=mobile punch load
        // for a caller-supplied AttendanceDate). Criteria filters (OUId/UserId/EmployeeId/DeviceId/
        // DepartmentId/PeriodFromDate/PeriodToDate) are applied per-type exactly as GB4 wired them
        // (Track/Map queries key off alias a/c/d; Punch queries key off alias a/b).
        //
        // NOTE: GB4's Type == -2 branch (attachment-augmented track list via PunchBLL.SiteBasedPunchInfo
        // + Alfresco file metadata) is intentionally NOT ported here — it pulls in the Punch/Alfresco
        // modules and is a distinct concern from plain location tracking. Add it as its own migration
        // if the attachment-aware variant is needed.
        public async Task<string> GetLocationTrack(int Type, int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool Count = false, CancellationToken ct = default)
        {
            try
            {
                DateTime? periodFromDate = null;
                DateTime? periodToDate = null;
                DateTime? attendanceDate = null;
                int? ouId = null;
                int? userId = null;
                int? employeeId = null;
                int? deviceId = null;
                int? departmentId = null;

                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attr in section.AttributesCriteriaList ?? new())
                        {
                            string field = attr?.FieldName?.ToLower() ?? string.Empty;
                            string value = attr?.FieldValue?.ToString() ?? string.Empty;
                            if (string.IsNullOrWhiteSpace(value)) continue;

                            switch (field)
                            {
                                case "periodfromdate":
                                    periodFromDate = ParseDate(value);
                                    break;
                                case "periodtodate":
                                    periodToDate = ParseDate(value);
                                    break;
                                case "attendancedate":
                                    attendanceDate = ParseDate(value);
                                    break;
                                case "ouid":
                                    ouId = Convert.ToInt32(value);
                                    break;
                                case "userid":
                                    userId = Convert.ToInt32(value);
                                    break;
                                case "employeeid":
                                    employeeId = Convert.ToInt32(value);
                                    break;
                                case "deviceid":
                                    deviceId = Convert.ToInt32(value);
                                    break;
                                case "departmentid":
                                    departmentId = Convert.ToInt32(value);
                                    break;
                            }
                        }
                    }
                }

                string sql = Type switch
                {
                    0 => LocationTrackQB.GET_LOCATION_TRACK_MAP,
                    1 => LocationTrackQB.GET_POSITION_MAP,
                    2 => LocationTrackQB.GET_PUNCH_MAP,
                    3 => LocationTrackQB.GET_PUNCH_LOAD,
                    4 => LocationTrackQB.GET_PUNCH_LOAD_MOBILE,
                    _ => throw new MethodNotAllowedException($"Unsupported LocationTrack Type: {Type}")
                };

                var parameters = new DynamicParameters();

                if (Type == 0 || Type == 1)
                {
                    if (ouId.HasValue) { sql += " AND c.workouid IN (@ouid)"; parameters.Add("ouid", ouId.Value); }
                    if (userId.HasValue) { sql += " AND a.UserId IN (@userid)"; parameters.Add("userid", userId.Value); }
                    if (employeeId.HasValue) { sql += " AND c.employeeid IN (@employeeid)"; parameters.Add("employeeid", employeeId.Value); }
                    if (deviceId.HasValue) { sql += " AND d.deviceid IN (@deviceid)"; parameters.Add("deviceid", deviceId.Value); }
                    if (departmentId.HasValue) { sql += " AND c.departmentid IN (@departmentid)"; parameters.Add("departmentid", departmentId.Value); }
                    if (periodFromDate.HasValue && periodToDate.HasValue)
                    {
                        sql += " AND a.timestamp BETWEEN @periodfromdate AND @periodtodate";
                        parameters.Add("periodfromdate", periodFromDate.Value);
                        parameters.Add("periodtodate", periodToDate.Value);
                    }
                }
                else if (Type == 2)
                {
                    if (employeeId.HasValue) { sql += " AND a.employeeid IN (@employeeid)"; parameters.Add("employeeid", employeeId.Value); }
                    if (departmentId.HasValue) { sql += " AND b.departmentid IN (@departmentid)"; parameters.Add("departmentid", departmentId.Value); }
                    if (periodFromDate.HasValue && periodToDate.HasValue)
                    {
                        sql += " AND a.attendancedate BETWEEN @periodfromdate AND @periodtodate";
                        sql += " AND a.Punchdate BETWEEN @periodfromdate AND @periodtodate";
                        parameters.Add("periodfromdate", periodFromDate.Value);
                        parameters.Add("periodtodate", periodToDate.Value);
                    }
                }
                else if (Type == 3)
                {
                    if (employeeId.HasValue) { sql += " AND EMP.EMPLOYEEID = @employeeid"; parameters.Add("employeeid", employeeId.Value); }
                    if (departmentId.HasValue) { sql += " AND EMP.DEPARTMENTID = @departmentid"; parameters.Add("departmentid", departmentId.Value); }
                }
                else if (Type == 4)
                {
                    if (!attendanceDate.HasValue)
                        throw new MethodNotAllowedException("AttendanceDate is required for LocationTrack Type 4 (mobile punch load).");
                    parameters.Add("attendancedate", attendanceDate.Value);

                    if (employeeId.HasValue) { sql += " AND EMP.EMPLOYEEID = @employeeid"; parameters.Add("employeeid", employeeId.Value); }
                    if (departmentId.HasValue) { sql += " AND EMP.DEPARTMENTID = @departmentid"; parameters.Add("departmentid", departmentId.Value); }
                }

                if (Count)
                {
                    int totalCount = await _QueryExecutor.ExecuteScalarAsync<int>(
                        LoginDTO, $"SELECT COUNT(1) FROM ({sql}) AS LocationTrackTotal", parameters, cancellationToken: ct);
                    return totalCount.ToString();
                }

                var result = await _QueryExecutor.QueryAsync<LocationTrackDTO>(LoginDTO, sql, parameters, cancellationToken: ct);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // GB4 parity: LocationTrackDAL.GetLocationBasedonGeo.
        // GB5 hardening: GeoCode ("lat,lng") is parsed and validated here in C# and bound as
        // two decimal parameters, instead of GB4's raw string.Replace(":geocode", GeoCode) into SQL text.
        public async Task<List<LocationBasedonGeoDTO>> GetLocationBasedonGeo(string GeoCode, decimal Distance, int NumberOfRecord, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(GeoCode))
                    throw new MethodNotAllowedException("GeoCode is required.");

                var parts = GeoCode.Split(',');
                if (parts.Length != 2
                    || !decimal.TryParse(parts[0], NumberStyles.Float, CultureInfo.InvariantCulture, out var lat)
                    || !decimal.TryParse(parts[1], NumberStyles.Float, CultureInfo.InvariantCulture, out var lng))
                {
                    throw new MethodNotAllowedException($"GeoCode must be in 'lat,lng' format. Received: {GeoCode}");
                }

                var parameters = new DynamicParameters();
                parameters.Add("lat", lat);
                parameters.Add("lng", lng);
                parameters.Add("distancemeter", Distance);
                parameters.Add("numberofrecord", NumberOfRecord);

                var result = await _QueryExecutor.QueryAsync<LocationBasedonGeoDTO>(
                    LoginDTO, LocationTrackQB.GET_LOCATION_BASED_ON_GEO_CODE, parameters, cancellationToken: ct);

                return result.ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static DateTime ParseDate(string value)
        {
            if (DateTime.TryParse(value, CultureInfo.InvariantCulture, DateTimeStyles.None, out var dt))
                return dt;

            if (long.TryParse(value, out long epochMillis))
                return DateTimeOffset.FromUnixTimeMilliseconds(epochMillis).UtcDateTime;

            throw new MethodNotAllowedException($"Invalid date format: {value}");
        }
    }
}