using AccountsDAL.CustomCode.AccountReports;
using AccountsDAL.DTO.AccountsReports;
using AccountsDAL.DTO.Reports;
using AccountsDAL.Query.Reports;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace AccountsDAL.CustomCode.Reports
{
    public class PAndLReportDAL : IPAndLReportDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public PAndLReportDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        public async Task<List<PAndLVerticalReportDTO>> GetPAndLVertical(int groupType, AccountReportCriteria criteria, LoginDTO loginDTO)
        {
            var (sql, parameters) = BuildQuery(groupType, criteria, loginDTO);
            var rows = await _queryExecutor
                .QueryAsync<PAndLVerticalReportDTO>(loginDTO, sql, parameters)
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<List<PAndLHorizontalReportDTO>> GetPAndLHorizontal(int groupType, AccountReportCriteria criteria, LoginDTO loginDTO)
        {
            var (filter, parameters) = AccountReportFilterBuilder.Build(criteria, loginDTO);
            parameters.Add("CurrencyBasis", criteria.CurrencyBasis);

            // Horizontal's CTEs apply {DynamicFilter} twice (once per Income/Expense side), each
            // needing its own copy of every bound parameter — Dapper's DynamicParameters already
            // tolerates a parameter being referenced more than once in the same SQL text, so a
            // single Replace(..., filter) covering both {DynamicFilter} occurrences is sufficient.
            var sql = groupType switch
            {
                1 => PAndLReportQB.GET_PANDL_HORIZONTAL_ACCOUNTGROUP,
                2 => PAndLReportQB.GET_PANDL_HORIZONTAL_ACCOUNTSCHEDULE,
                _ => PAndLReportQB.GET_PANDL_HORIZONTAL_ACCOUNT,
            };

            var finalSql = sql.Replace("{DynamicFilter}", filter);
            var rows = await _queryExecutor
                .QueryAsync<PAndLHorizontalReportDTO>(loginDTO, finalSql, parameters)
                .ConfigureAwait(false);
            return rows.ToList();
        }

        // GroupType: 0 = Account, 1 = AccountGroup, 2 = AccountSchedule — matches the 3 real live
        // menus per report (AC20094/95/96 for Vertical, AC20091/92/93 for Horizontal).
        private static (string sql, Dapper.DynamicParameters parameters) BuildQuery(
            int groupType, AccountReportCriteria criteria, LoginDTO loginDTO)
        {
            var (filter, parameters) = AccountReportFilterBuilder.Build(criteria, loginDTO);
            parameters.Add("CurrencyBasis", criteria.CurrencyBasis);

            var (sql, defaultOrderBy) = groupType switch
            {
                1 => (PAndLReportQB.GET_PANDL_ACCOUNTGROUP, "AG.ACCOUNTGROUPCODE"),
                2 => (PAndLReportQB.GET_PANDL_ACCOUNTSCHEDULE, "AGS.ACCOUNTSCHEDULECODE"),
                _ => (PAndLReportQB.GET_PANDL_ACCOUNT, "ACC.ACCOUNTCODE"),
            };

            var finalSql = sql.Replace("{DynamicFilter}", filter).Replace("{OrderBy}", defaultOrderBy);
            return (finalSql, parameters);
        }
    }
}