using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PayRollDAL.Query.Advance
{
    public static class AdvanceQB
    {
        public const string GET_ADVANCE = @"SELECT
    A.ADVANCEID AS AdvanceId,
    A.OUID AS OUId,
    OU.ORGANIZATIONUNITCODE AS OUCode,
    OU.ORGANIZATIONUNITNAME AS OUName,

    A.PERIODID AS PeriodId,
    P.PERIODCODE AS PeriodCode,
    P.PERIODNAME AS PeriodName,
    P.FROMDATE AS PeriodFromDate,
    P.TODATE AS PeriodToDate,

    A.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,

    A.ADVANCENUMBER AS AdvanceAdvanceNumber,
    A.ADVANCEDATE AS AdvanceAdvanceDate,
    A.REFERENCENUMBER AS AdvanceReferenceNumber,
    A.REFERENCEDATE AS AdvanceReferenceDate,

    A.EMPLOYEEID AS EmployeeId,
    E.EMPLOYEECODE AS EmployeeCode, 
    E.EMPLOYEENAME AS EmployeeName,

    A.ADVANCETYPEID AS AdvanceTypeId,
    AT.GCMCODE AS AdvanceTypeCode,
    AT.GCMNAME AS AdvanceTypeName,

    A.AMOUNTREQUIRED AS AdvanceAmountRequired,
    A.EXPECTEDDATE AS AdvanceExpectedDate,
    A.PAYMENTSTATUS AS AdvancePaymentStatus,
    A.AMOUNTSANCTIONED AS AdvanceAmountSanctioned,
    A.ISREPAYMENT AS AdvanceIsRepayment,
    A.REPAYMENTFROMDATE AS AdvanceRepaymentFromDate,
    A.NUMBEROFINSTALLMENT AS AdvanceNumberOfInstallment,
    A.INSTALLMENTREMAINING AS AdvanceInstallmentRemaining,
    A.INTERESTTYPE AS AdvanceInterestType,
    A.INTERESTPERCENTAGE AS AdvanceInterestPercentage,
    A.INTERESTAMOUNT AS AdvanceInterestAmount,
    A.MONTHDEDUCTION AS AdvanceMonthDeduction,
    A.AMOUNTPAID AS AdvanceAmountPaid,
    A.PAYMENTDATE AS AdvancePaymentDate,
    A.PAYMENTREFERENCENUMBER AS AdvancePaymentReferenceNumber,

    A.INSTRUMENTTYPEID AS InstrumentTypeId,
    IT.INSTRUMENTNAME AS InstrumentTypeName,
    A.INSTRUMENTNUMBER AS AdvanceInstrumentNumber,
    A.INSTRUMENTDATE AS AdvanceInstrumentDate,

    A.AMOUNTREMAINING AS AdvanceAmountRemaining,
    A.ISACCOUNTPOST AS AdvanceIsAccountPost,

    A.DEBITACCOUNTID AS DebitAccountId,
    DA.ACCOUNTCODE AS DebitAccountCode,
    DA.ACCOUNTNAME AS DebitAccountName,

    A.CREDITACCOUNTID AS CreditAccountId,
    CA.ACCOUNTCODE AS CreditAccountCode,
    CA.ACCOUNTNAME AS CreditAccountName,

    A.PAYMENTVOUCHERID AS PaymentVoucherId,
    V.VOUCHERNUMBER AS PaymentVoucherVoucherNumber,

    A.REMARKS AS AdvanceRemarks,
    A.STATUS AS AdvanceStatus,
    A.INTERESTTYPE AS AdvanceSourceType,
    A.VERSION AS AdvanceVersion,

    A.CREATEDBYID AS AdvanceCreatedById,
    CB.USERNAME AS AdvanceCreatedByName,
    A.CREATEDON AS AdvanceCreatedOn,

    A.MODIFIEDBYID AS AdvanceModifiedById,
    MB.USERNAME AS AdvanceModifiedByName,
    A.MODIFIEDON AS AdvanceModifiedOn,

    A.PERIODID AS PayperiodId,
    A.SL_NO AS AdvanceSL_NO,

    -- placeholders (kept as null so DTO properties exist even if not in DB)
    NULL AS FunctionReportingToMailId,
    NULL AS FunctionReportingToMobileNumber,
    NULL AS AdminReportingToMailId,
    NULL AS AdminReportingToMobileNumber,
    NULL AS FunctionAndAdminReportingToMailId,
    NULL AS FunctionAndAdminReportingToMobileNumber,

    -- additional fields you used earlier
    A.PRINCIPLEBALANCE AS AdvancePrincipleBalance,
    A.PRINCIPLEASON AS AdvancePrincipleAson,
    A.INTERESTPAIDTILL AS AdvanceInterestPaidTill

FROM TADVANCE A
LEFT JOIN MORGANIZATIONUNIT OU     ON A.OUID = OU.OUID
LEFT JOIN MPERIOD P                ON A.PERIODID = P.PERIODID
LEFT JOIN MBIZTRANSACTIONTYPE BTT  ON A.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MEMPLOYEE E              ON A.EMPLOYEEID = E.EMPLOYEEID
LEFT JOIN MGCM AT                  ON A.ADVANCETYPEID = AT.GCMID
LEFT JOIN MINSTRUMENT IT           ON A.INSTRUMENTTYPEID = IT.INSTRUMENTID
LEFT JOIN MACCOUNT DA              ON A.DEBITACCOUNTID = DA.ACCOUNTID
LEFT JOIN MACCOUNT CA              ON A.CREDITACCOUNTID = CA.ACCOUNTID
LEFT JOIN TVOUCHER V               ON A.PAYMENTVOUCHERID = V.VOUCHERID
LEFT JOIN MUSER CB                 ON A.CREATEDBYID = CB.USERID
LEFT JOIN MUSER MB                 ON A.MODIFIEDBYID = MB.USERID
WHERE A.ADVANCEID = @advanceid;
";

        // Migrated from GB4 Advance.svc/Advance (IAdvance.GetAdvanceReport) —
        // GB4 filtered via HQL entity paths (advancereport.Employee.Id, etc.);
        // here CriteriaBuilder.Build fills /*CRITERIA*/, so FieldName must match
        // a SELECT-list alias below (e.g. OUId, EmployeeId, BizTransactionTypeId,
        // BIZTransactionClassId, AdvanceAdvanceNumber, AdvanceAdvanceDate,
        // AdvancePaymentStatus, AdvanceIsRepayment, AdvanceInterestType,
        // InstrumentTypeId, AdvanceIsAccountPost, AdvanceStatus, AdvanceTypeId).
        public const string GET_ADVANCE_LIST = @"
WITH PagedAdvanceReport AS (
SELECT
    A.ADVANCEID AS AdvanceId,
    A.OUID AS OUId,
    OU.ORGANIZATIONUNITCODE AS OUCode,
    OU.ORGANIZATIONUNITNAME AS OUName,

    A.PERIODID AS PeriodId,
    P.PERIODCODE AS PeriodCode,
    P.PERIODNAME AS PeriodName,
    P.FROMDATE AS PeriodFromDate,
    P.TODATE AS PeriodToDate,

    A.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
    BTT.BIZTRANSACTIONCLASSID AS BIZTransactionClassId,

    A.ADVANCENUMBER AS AdvanceAdvanceNumber,
    A.ADVANCEDATE AS AdvanceAdvanceDate,
    A.REFERENCENUMBER AS AdvanceReferenceNumber,
    A.REFERENCEDATE AS AdvanceReferenceDate,

    A.EMPLOYEEID AS EmployeeId,
    E.EMPLOYEECODE AS EmployeeCode,
    E.EMPLOYEENAME AS EmployeeName,

    A.ADVANCETYPEID AS AdvanceTypeId,
    AT.GCMCODE AS AdvanceTypeCode,
    AT.GCMNAME AS AdvanceTypeName,

    A.AMOUNTREQUIRED AS AdvanceAmountRequired,
    A.EXPECTEDDATE AS AdvanceExpectedDate,
    A.PAYMENTSTATUS AS AdvancePaymentStatus,
    A.AMOUNTSANCTIONED AS AdvanceAmountSanctioned,
    A.ISREPAYMENT AS AdvanceIsRepayment,
    A.REPAYMENTFROMDATE AS AdvanceRepaymentFromDate,
    A.NUMBEROFINSTALLMENT AS AdvanceNumberOfInstallment,
    A.INSTALLMENTREMAINING AS AdvanceInstallmentRemaining,
    A.INTERESTTYPE AS AdvanceInterestType,
    A.INTERESTPERCENTAGE AS AdvanceInterestPercentage,
    A.INTERESTAMOUNT AS AdvanceInterestAmount,
    A.MONTHDEDUCTION AS AdvanceMonthDeduction,
    A.AMOUNTPAID AS AdvanceAmountPaid,
    A.PAYMENTDATE AS AdvancePaymentDate,
    A.PAYMENTREFERENCENUMBER AS AdvancePaymentReferenceNumber,

    A.INSTRUMENTTYPEID AS InstrumentTypeId,
    IT.INSTRUMENTNAME AS InstrumentTypeName,
    A.INSTRUMENTNUMBER AS AdvanceInstrumentNumber,
    A.INSTRUMENTDATE AS AdvanceInstrumentDate,

    A.AMOUNTREMAINING AS AdvanceAmountRemaining,
    A.ISACCOUNTPOST AS AdvanceIsAccountPost,

    A.DEBITACCOUNTID AS DebitAccountId,
    DA.ACCOUNTCODE AS DebitAccountCode,
    DA.ACCOUNTNAME AS DebitAccountName,

    A.CREDITACCOUNTID AS CreditAccountId,
    CA.ACCOUNTCODE AS CreditAccountCode,
    CA.ACCOUNTNAME AS CreditAccountName,

    A.PAYMENTVOUCHERID AS PaymentVoucherId,
    V.VOUCHERNUMBER AS PaymentVoucherVoucherNumber,

    A.REMARKS AS AdvanceRemarks,
    A.STATUS AS AdvanceStatus,
    A.INTERESTTYPE AS AdvanceSourceType,
    A.VERSION AS AdvanceVersion,

    A.CREATEDBYID AS AdvanceCreatedById,
    CB.USERNAME AS AdvanceCreatedByName,
    A.CREATEDON AS AdvanceCreatedOn,

    A.MODIFIEDBYID AS AdvanceModifiedById,
    MB.USERNAME AS AdvanceModifiedByName,
    A.MODIFIEDON AS AdvanceModifiedOn,

    A.PERIODID AS PayperiodId,
    A.SL_NO AS AdvanceSL_NO,

    A.PRINCIPLEBALANCE AS AdvancePrincipleBalance,
    A.PRINCIPLEASON AS AdvancePrincipleAson,
    A.INTERESTPAIDTILL AS AdvanceInterestPaidTill,

    ROW_NUMBER() OVER (ORDER BY A.ADVANCEDATE, A.ADVANCENUMBER) AS RowNum

FROM TADVANCE A
LEFT JOIN MORGANIZATIONUNIT OU     ON A.OUID = OU.OUID
LEFT JOIN MPERIOD P                ON A.PERIODID = P.PERIODID
LEFT JOIN MBIZTRANSACTIONTYPE BTT  ON A.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MEMPLOYEE E              ON A.EMPLOYEEID = E.EMPLOYEEID
LEFT JOIN MGCM AT                  ON A.ADVANCETYPEID = AT.GCMID
LEFT JOIN MINSTRUMENT IT           ON A.INSTRUMENTTYPEID = IT.INSTRUMENTID
LEFT JOIN MACCOUNT DA              ON A.DEBITACCOUNTID = DA.ACCOUNTID
LEFT JOIN MACCOUNT CA              ON A.CREDITACCOUNTID = CA.ACCOUNTID
LEFT JOIN TVOUCHER V               ON A.PAYMENTVOUCHERID = V.VOUCHERID
LEFT JOIN MUSER CB                 ON A.CREATEDBYID = CB.USERID
LEFT JOIN MUSER MB                 ON A.MODIFIEDBYID = MB.USERID
WHERE 1 = 1
/*CRITERIA*/
)
SELECT *
FROM PagedAdvanceReport
WHERE
(@firstnumber = -1 AND @maxresult = -1)
OR
(RowNum BETWEEN @firstnumber AND @maxresult)
ORDER BY RowNum;
";

        // CriteriaBuilder.Build fills in /*CRITERIA*/. FieldName must match a
        // SELECT-list alias below (e.g. BizTransactionTypeId, AdvanceNumber,
        // EmployeeCode, EmployeeName, OUId, EmployeeId, PeriodFromDate,
        // PeriodToDate, BIZTransactionClassId) so ExtractFieldAliasMapFromSql
        // can resolve it back to the correct joined table.
        public const string GET_SELECTLIST_ADVANCE = @"
                            WITH PagedAdvance AS (
                            SELECT
                            AD.ADVANCEID AS Id,
                            AD.ADVANCENUMBER AS AdvanceNumber,
                            AD.ADVANCEDATE AS AdvanceDate,
                            AD.REFERENCENUMBER AS ReferenceNumber,
                            AD.REFERENCEDATE AS ReferenceDate,
                            AD.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                            AD.OUID AS OUId,
                            AD.EMPLOYEEID AS EmployeeId,
                            P.FROMDATE AS PeriodFromDate,
                            P.TODATE AS PeriodToDate,
                            BTT.BIZTRANSACTIONCLASSID AS BIZTransactionClassId,
                            E.EMPLOYEECODE AS EmployeeCode,
                            E.EMPLOYEENAME AS EmployeeName,
                            ROW_NUMBER() OVER (ORDER BY AD.ADVANCEID) AS RowNum
                            FROM TADVANCE AD
                            LEFT JOIN MEMPLOYEE E              ON AD.EMPLOYEEID = E.EMPLOYEEID
                            LEFT JOIN MPERIOD P                ON AD.PERIODID = P.PERIODID
                            LEFT JOIN MBIZTRANSACTIONTYPE BTT  ON AD.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
                            WHERE 1 = 1
                            /*CRITERIA*/
                            )
                            SELECT
                            Id,
                            AdvanceNumber,
                            AdvanceDate,
                            ReferenceNumber,
                            ReferenceDate,
                            BizTransactionTypeId,
                            EmployeeCode,
                            EmployeeName
                            FROM PagedAdvance
                            WHERE
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";







        // Migrated from GB4 AdvanceQueryBuilder.PICKLIST_ADVANCEEMPLOYEEBASED
        // (Advance.svc/AdvanceEmployee/SelectList). GB4 built this HQL with a free-text
        // filter appended directly (AdvanceNumber like/EmployeeCode like/EmployeeName like)
        // in addition to whitelisted criteria (BizTransactionTypeId, InstallmentRemaining,
        // IsRepayment, PaymentStatus, EmployeeId) — CriteriaBuilder.Build fills /*CRITERIA*/
        // for the latter, and @searchtext (wrapped in %...% by the caller) covers the former.
        public const string GET_SELECTLIST_ADVANCEEMPLOYEE = @"
                            WITH PagedAdvanceEmployee AS (
                            SELECT
                            A.ADVANCEID AS AdvanceId,
                            A.ADVANCENUMBER AS AdvanceNumber,
                            E.EMPLOYEECODE AS EmployeeCode,
                            E.EMPLOYEENAME AS EmployeeName,
                            A.NUMBEROFINSTALLMENT AS NumberOfInstallment,
                            A.INSTALLMENTREMAINING AS InstallmentRemaining,
                            A.ADVANCEDATE AS AdvanceDate,
                            A.AMOUNTREQUIRED AS AdvanceAmount,
                            A.AMOUNTREMAINING AS AdvanceAmountRemaining,
                            A.MONTHDEDUCTION AS AdvanceMonthDeduction,
                            CASE A.PAYMENTSTATUS
                                WHEN 0 THEN 'Pending'
                                WHEN 1 THEN 'Accepted'
                                WHEN 2 THEN 'Rejected'
                                WHEN 3 THEN 'Paid'
                                WHEN 4 THEN 'Repayment'
                                ELSE 'Closed'
                            END AS PaymentStatus,
                            A.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                            A.EMPLOYEEID AS EmployeeId,
                            A.PAYMENTSTATUS AS AdvancePaymentStatus,
                            A.ISREPAYMENT AS AdvanceIsRepayment,
                            ROW_NUMBER() OVER (ORDER BY A.ADVANCEID DESC) AS RowNum
                            FROM TADVANCE A
                            LEFT JOIN MEMPLOYEE E ON A.EMPLOYEEID = E.EMPLOYEEID
                            WHERE 1 = 1
                            /*CRITERIA*/
                            AND (A.ADVANCENUMBER LIKE '%' + @searchtext + '%'
                                 OR E.EMPLOYEECODE LIKE '%' + @searchtext + '%'
                                 OR E.EMPLOYEENAME LIKE '%' + @searchtext + '%')
                            )
                            SELECT
                            AdvanceId,
                            AdvanceNumber,
                            EmployeeCode,
                            EmployeeName,
                            NumberOfInstallment,
                            InstallmentRemaining,
                            AdvanceDate,
                            AdvanceAmount,
                            AdvanceAmountRemaining,
                            AdvanceMonthDeduction,
                            PaymentStatus
                            FROM PagedAdvanceEmployee
                            WHERE
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult)
                            ORDER BY RowNum;
                            ";

        public const string SAVE_ADVANCE = @"INSERT INTO TAdvance
(
    ADVANCEID,
    OUID,
    PERIODID,
    BIZTRANSACTIONTYPEID,
    ADVANCENUMBER,
    ADVANCEDATE,
    REFERENCENUMBER,
    REFERENCEDATE,
    EMPLOYEEID,
    AMOUNTREQUIRED,
    EXPECTEDDATE,
    PAYMENTSTATUS,
    AMOUNTSANCTIONED,
    ISREPAYMENT,
    REPAYMENTFROMDATE,
    NUMBEROFINSTALLMENT,
    INSTALLMENTREMAINING,
    INTERESTTYPE,
    INTERESTPERCENTAGE,
    INTERESTAMOUNT,
    MONTHDEDUCTION,
    AMOUNTPAID,
    PAYMENTDATE,
    PAYMENTREFERENCENUMBER,
    INSTRUMENTTYPEID,
    INSTRUMENTNUMBER,
    INSTRUMENTDATE,
    AMOUNTREMAINING,
    ISACCOUNTPOST,
    DEBITACCOUNTID,
    CREDITACCOUNTID,
    PAYMENTVOUCHERID,
    REMARKS,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    ADVANCETYPEID,
    SL_NO,
    PRINCIPLEBALANCE,
    PRINCIPLEASON,
    INTERESTPAIDTILL
)
VALUES
(
    @AdvanceId,
    @OUId,
    @PeriodId,
    @BizTransactionTypeId,
    @AdvanceAdvanceNumber,
    @AdvanceAdvanceDate,
    @AdvanceReferenceNumber,
    @AdvanceReferenceDate,
    @EmployeeId,
    @AdvanceAmountRequired,
    @AdvanceExpectedDate,
    @AdvancePaymentStatus,
    @AdvanceAmountSanctioned,
    @AdvanceIsRepayment,
    @AdvanceRepaymentFromDate,
    @AdvanceNumberOfInstallment,
    @AdvanceInstallmentRemaining,
    @AdvanceInterestType,
    @AdvanceInterestPercentage,
    @AdvanceInterestAmount,
    @AdvanceMonthDeduction,
    @AdvanceAmountPaid,
    @AdvancePaymentDate,
    @AdvancePaymentReferenceNumber,
    @InstrumentTypeId,
    @AdvanceInstrumentNumber,
    @AdvanceInstrumentDate,
    @AdvanceAmountRemaining,
    @AdvanceIsAccountPost,
    @DebitAccountId,
    @CreditAccountId,
    @PaymentVoucherId,
    @AdvanceRemarks,
    @AdvanceStatus,
    @AdvanceVersion,
    @AdvanceCreatedById,
    @AdvanceCreatedOn,
    @AdvanceModifiedById,
    @AdvanceModifiedOn,
    @AdvanceTypeId,
    @AdvanceSL_NO,
    @AdvancePrincipleBalance,
    @AdvancePrincipleAson,
    @AdvanceInterestPaidTill
);";



        public const string UPDATE_ADVANCE = @"UPDATE TAdvance
    SET
        OUID                    = @OUId,
        PERIODID                = @PeriodId,
        BIZTRANSACTIONTYPEID    = @BizTransactionTypeId,
        ADVANCENUMBER           = @AdvanceAdvanceNumber,
        ADVANCEDATE             = @AdvanceAdvanceDate,
        REFERENCENUMBER         = @AdvanceReferenceNumber,
        REFERENCEDATE           = @AdvanceReferenceDate,
        EMPLOYEEID              = @EmployeeId,
        AMOUNTREQUIRED          = @AdvanceAmountRequired,
        EXPECTEDDATE            = @AdvanceExpectedDate,
        PAYMENTSTATUS           = @AdvancePaymentStatus,
        AMOUNTSANCTIONED        = @AdvanceAmountSanctioned,
        ISREPAYMENT             = @AdvanceIsRepayment,
        REPAYMENTFROMDATE       = @AdvanceRepaymentFromDate,
        NUMBEROFINSTALLMENT     = @AdvanceNumberOfInstallment,
        INSTALLMENTREMAINING    = @AdvanceInstallmentRemaining,
        INTERESTTYPE            = @AdvanceInterestType,
        INTERESTPERCENTAGE      = @AdvanceInterestPercentage,
        INTERESTAMOUNT          = @AdvanceInterestAmount,
        MONTHDEDUCTION          = @AdvanceMonthDeduction,
        AMOUNTPAID              = @AdvanceAmountPaid,
        PAYMENTDATE             = @AdvancePaymentDate,
        PAYMENTREFERENCENUMBER  = @AdvancePaymentReferenceNumber,
        INSTRUMENTTYPEID        = @InstrumentTypeId,
        INSTRUMENTNUMBER        = @AdvanceInstrumentNumber,
        INSTRUMENTDATE          = @AdvanceInstrumentDate,
        AMOUNTREMAINING         = @AdvanceAmountRemaining,
        ISACCOUNTPOST           = @AdvanceIsAccountPost,
        DEBITACCOUNTID          = @DebitAccountId,
        CREDITACCOUNTID         = @CreditAccountId,
        PAYMENTVOUCHERID        = @PaymentVoucherId,
        REMARKS                 = @AdvanceRemarks,
        STATUS                  = @AdvanceStatus,
        VERSION                 = @AdvanceVersion,
        MODIFIEDBYID            = @AdvanceModifiedById,
        MODIFIEDON              = @AdvanceModifiedOn,
        ADVANCETYPEID           = @AdvanceTypeId,
        SL_NO                   = @AdvanceSL_NO,
        PRINCIPLEBALANCE        = @AdvancePrincipleBalance,
        PRINCIPLEASON           = @AdvancePrincipleAson,
        INTERESTPAIDTILL        = @AdvanceInterestPaidTill
    WHERE
        ADVANCEID = @AdvanceId;";




        public const string DELETE_ADVANCE = @"DELETE FROM TAdvance
                                            WHERE ADVANCEID = @AdvanceId;";

    }
}