using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ResponseStandard;
using PayRollDAL.DTO.Employee;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Unisoft.PayrollDAL.Employee;

namespace PayRollDAL.CustomeCode.Employee
{
    public interface IEmployeeDAL
    {
        /// <summary>
        /// Retrieves employee details by employee ID and language ID.
        /// </summary>
        /// <param name="EmployeeId">Unique identifier of the employee.</param>
        /// <param name="LanguageId">Language ID used to fetch localized data.</param>
        /// <param name="LoginDTO">User login context for authorization and auditing.</param>
        /// <returns>A JSON string containing employee details.</returns>
        /// <exception cref="Exception">Thrown if an error occurs while fetching employee data.</exception>
        Task<string> GetEmployee(int EmployeeId, string LanguageId, LoginDTO LoginDTO);

        /// <summary>
        /// Inserts a new employee record into the database.
        /// </summary>
        /// <param name="EmployeeDTO">Data transfer object containing employee details.</param>
        /// <param name="LoginDTO">User login context for authorization and auditing.</param>
        /// <returns>The number of rows affected.</returns>
        /// <exception cref="Exception">Thrown when the employee save operation fails.</exception>
        Task<int> SaveEmployee(EmployeeDTO employeeDTO, LoginDTO LoginDTO);

        /// <summary>
        /// Counts the number of translation employee (TRM Employee) records for the given employee and language.
        /// </summary>
        /// <param name="EmployeeDTO">Data transfer object containing employee details.</param>
        /// <param name="LanguageId">Language ID for which the count is required.</param>
        /// <param name="LoginDTO">User login context for authorization and auditing.</param>
        /// <returns>The total count of TRM Employee records.</returns>
        /// <exception cref="Exception">Thrown if counting translation employees fails.</exception>
        Task<int> CountTRMEmployee(EmployeeDTO EmployeeDTO, string LanguageId, LoginDTO LoginDTO);

        /// <summary>
        /// Updates an existing employee record in the database.
        /// </summary>
        /// <param name="EmployeeDTO">Data transfer object containing updated employee details.</param>
        /// <param name="LoginDTO">User login context for authorization and auditing.</param>
        /// <returns>The number of rows affected.</returns>
        /// <exception cref="Exception">Thrown when the employee update operation fails.</exception>
        Task<int> UpdateEmployee(EmployeeDTO employeeDTO, LoginDTO LoginDTO);

        /// <summary>
        /// Retrieves the future version of an employee record for version control validation.
        /// </summary>
        /// <param name="employeeId">Unique identifier of the employee.</param>
        /// <param name="versionFrom">The version effective start date to validate.</param>
        /// <param name="loginDTO">User login context for authorization and auditing.</param>
        /// <returns>
        /// An <see cref="EmployeeVersionDTO"/> representing the next version of the employee, if any; otherwise <c>null</c>.
        /// </returns>
        /// <exception cref="Exception">Thrown when fetching the future version fails.</exception>
        Task<EmployeeVersionDTO> GetFutureEmployeeVersionAsync(int employeeId, DateTime versionFrom, LoginDTO loginDTO);
        Task SaveOrUpdateEmployeeESIAsync(EmployeeDTO employee, LoginDTO loginDTO);
        Task SaveOrUpdateEmployeePFAsync(EmployeeDTO employee, LoginDTO loginDTO);
        Task CloseEmployeeESIAsync(EmployeeDTO employee, LoginDTO loginDTO);
        Task CloseEmployeePFAsync(EmployeeDTO employee, LoginDTO loginDTO);
        Task<int> UpdateAttendanceAfterResignationAsync(int employeeId, DateTime resignationDate, LoginDTO LoginDTO);
        Task<bool> UpdateUserStatusAfterResignationAsync(int employeeId, DateTime resignationDate, LoginDTO LoginDTO);
        Task UpdateEmployeeShiftPatternAsync(EmployeeShiftPatternDTO dto, LoginDTO loginDTO);
        Task UpdateEmployeeShiftPatternAsync(EmployeeShiftPatternDTO dto, LoginDTO loginDTO, DbTransaction transaction, CancellationToken ct);

        /// <summary>
        /// Deletes an employee record from the database by employee ID.
        /// </summary>
        /// 
        /// <param name="employeeId">Unique identifier of the employee to delete.</param>
        /// <param name="LoginDTO">User login context for authorization and auditing.</param>
        /// <returns>
        /// A <see cref="Result{T}"/> object indicating success or failure with a descriptive message.
        /// </returns>
        /// <exception cref="Exception">Thrown if an error occurs while deleting the employee.</exception>
        Task<Result<string>> DeleteEmployee(int EmployeeId, LoginDTO LoginDTO);
        Task<TransferAdvanceResultDTO> PostTransferEmployeeAdvance(
         List<PostTransferEmployeeAdvanceDTO> PostTransferEmployeeAdvanceDTOs,
         LoginDTO LoginDTO);
        Task<string> GetTransferEmployeeAdvance(LoginDTO LoginDTO);
        Task<string> SaveEmployeeSecretKey(int EmployeeId, string SecretKey, LoginDTO LoginDTO);
        Task<(string Json, int Total)> GetSelectListEmployee(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO);

        /// <summary>
        /// Resolves an EmployeeCode to its EmployeeId — used only by the manual raw-code-entry
        /// scan path (QR codes already carry EmployeeId directly). Returns -1 if not found.
        /// </summary>
        Task<int> GetEmployeeIdByCode(string employeeCode, LoginDTO loginDTO);
        Task DeleteEmployeeAddon(int employeeId, LoginDTO loginDTO, CancellationToken ct = default);
        Task UpsertEmployeeAddon(EmployeeAddon addon, LoginDTO loginDTO, CancellationToken ct = default);

        /// <summary>
        /// Splits the calendar gap between an employee's DateOfJoining and a given separation date
        /// into years/months/days. SQL-Server-only (matches GB4); other database types return zeros.
        /// </summary>
        Task<(int SeparationYears, int SeparationMonths, int SeparationDays)> GetDifferenceFromJoiningDate(
            int employeeId, DateTime separationDate, LoginDTO loginDTO);

        /// <summary>
        /// Flat (non-recursive) tenant-wide active-employee list with reporting-chain links,
        /// used by the BLL to build the org hierarchy tree. See EmployeeQB.GET_EMPLOYEE_HIERARCHY.
        /// </summary>
        Task<List<EmployeeHierarchyDTO>> GetEmployeeHierarchy(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>
        /// Fans out to the ~11 HRMS dashboard widget queries and assembles the aggregate DTO.
        /// The legacy workflow-pending-approvals widget is intentionally not populated here —
        /// GB5 has no live Activiti equivalent (deferred, see migration plan).
        /// </summary>
        Task<EmployeesDashboardDTO> GetHRMSDashboard(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>
        /// Paged composite employee profile report: master fields + Salary/Skill/Working/
        /// Experience/Qualification/Reference sub-collections (the 6 in-scope legacy sub-reports —
        /// ESI/PF/Gratuity/Appointment/BioData/Form25/Induction are deferred, see migration plan).
        /// Unlike the legacy GB4 version, the master list is genuinely paged via QueryPagedAsync
        /// and Total reflects the real matching count.
        /// </summary>
        Task<(List<EmployeeProfileReportDTO> Items, int Total)> GetEmployeeProfileReport(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>
        /// Migrated from GB4 EmployeeDAL.EmployeePostingSelectionList — lists TPOSTINGPOSITION
        /// rows for a given employee (MM posting/allocation history), with an optional
        /// FromInventory variant (distinct, no daily-attendance/timesheet enrichment).
        /// GB4's CrewTimesheetActivitiHistoryDTOs drilldown enrichment is not ported (see
        /// EmployeePostingSelectListDTO — the property is commented out there already).
        /// </summary>
        Task<(List<EmployeePostingSelectListDTO> Items, int Total)> EmployeePostingSelectionList(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>GB4 Employee.svc.cs GetEmployeeVsShiftPattern — employee vs. shift pattern listing.</summary>
        Task<(List<EmployeeVsShiftPatternDTO> Items, int Total)> GetEmployeeVsShiftPattern(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>
        /// GB4 Employee.svc.cs "Get/Employee/Vs/ShiftPattern/Load" — EmployeeBLL.GetEmployeeVsShiftPatternNew.
        /// Distinct from GetEmployeeVsShiftPattern: per-shift-pattern-day DayType/NumberofDays rows.
        /// </summary>
        Task<(List<EmployeeVsShiftPatternNewDTO> Items, int Total)> GetEmployeeVsShiftPatternNew(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>GB4 Employee.svc.cs GetEmployeeVersionPicklist (spelling corrected from GB4's "Verison").</summary>
        Task<List<EmployeeVersionPickListDTO>> GetEmployeeVersionPicklist(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>GB4 Employee.svc.cs LegecyBasedEmployeeInformation — recursive legacy-code chain.</summary>
        Task<List<EmployeeBasedLegecyInformationDTO>> LegecyBasedEmployeeInformation(
            int employeeId, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>GB4 Employee.svc.cs GetEmployeeVersionInPayProcess.</summary>
        Task<List<EmployeeVersionInPayProcessDTO>> GetEmployeeVersionInPayProcess(
            CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>GB4 Employee.svc.cs GetEmployeeVersion — full parity explicit-SQL replacement of GB4's NHibernate lazy load.</summary>
        Task<EmployeeVersionDTO?> GetEmployeeVersion(int employeeVersionId, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>GB4 Employee.svc.cs GetDuplicateEmployee — parameterized optional-filter query.</summary>
        Task<List<DuplicateEmployeeDTO>> GetDuplicateEmployee(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct);

        /// <summary>GB4 Employee.svc.cs DeleteEmployeeVersion guard/support queries + rollback update, called inside BLL transaction.</summary>
        Task<int> GetPayProcessDoneAfterEmployeeVersion(int employeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct);
        Task<int> GetLatestVersionAfterGivenVersion(int employeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct);
        Task<int> GetEmployeeIdFromVersion(int employeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct);
        Task DeleteEmployeeAddonVersionByVersion(int employeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct);
        Task DeleteEmployeeVersionById(int employeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct);
        Task<int> GetEmployeeVersionLatestId(int employeeId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct);
        Task RollbackEmployeeToLatestVersion(int latestEmployeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct);
        Task RollbackEmployeeAddonToLatestVersion(int latestEmployeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct);
    }
}