using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Claims;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace MaintenanceDAL.Query.Claim
{
    public static class ClaimQB
    {
        public const string GET_CLAIM = @"SELECT
    A.CLAIMID AS ClaimId,
    A.OUID AS OUId,
    OU.ORGANIZATIONUNITCODE AS OUCode,
    OU.ORGANIZATIONUNITNAME AS OUName,

    A.PERIODID AS PeriodId,
    P.PERIODCODE AS PeriodCode,
    P.PERIODNAME AS PeriodName,

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

    A.CLAIMNUMBER AS ClaimClaimNumber,
    A.CLAIMDATE AS ClaimClaimDate,
    A.REFERENCENUMBER AS ClaimReferenceNumber,
    A.REFERENCEDATE AS ClaimReferenceDate,
    A.PERIODFROM AS ClaimPeriodFrom,
    A.PERIODTO AS ClaimPeriodTo,

    A.ALLOCATIONID AS AllocationId,
    AL.ALLOCATIONNAME AS AllocationName,

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

    A.DEPARTMENTID AS DepartmentId,
    D.DEPARTMENTCODE AS DepartmentCode,
    D.DEPARTMENTNAME AS DepartmentName,

    A.CITYID AS CityId,
    C.CITYCODE AS CityCode,
    C.CITYNAME AS CityName,

    A.CLAIMSTATUS AS ClaimClaimOStatus,
    A.CLAIMTOTAL AS ClaimClaimTotal,
    A.ELIGIBLETOTAL AS ClaimEligibleTotal,
    A.BILLTOTAL AS ClaimBillTotal,
    A.APPROVEDTOTAL AS ClaimApprovedTotal,
    A.ADVANCE AS ClaimAdvance,
    A.BALANCE AS ClaimBalance,
    A.PAIDAMOUNT AS ClaimPaidAmount,

    A.VOUCHERID AS VoucherId,
    V.VOUCHERNUMBER AS VoucherVoucherNumber,

    A.BILLSRECEIVEDON AS BillsReceivedOn,
    A.BILLSRECEIVDBYID AS BillsReceivedById,
    BR.EMPLOYEECODE AS BillsReceivedByCode,
    BR.EMPLOYEENAME AS BillsReceivedByName,

    A.APPROVEDBYID AS ApprovedById,
    AB.EMPLOYEECODE AS ApprovedByCode,
    AB.EMPLOYEENAME AS ApprovedByName,

    A.BILLSRECEIVEDON AS ClaimBillsReceivedOn,
    A.APPROVEDON AS ClaimApprovedOn,
    A.REMARKS AS ClaimRemarks,
    A.VERSION AS ClaimVersion,
    A.STATUS AS ClaimStatus,
    A.CREATEDBYID AS ClaimCreatedById,
    U1.USERNAME AS ClaimCreatedByName,
    A.CREATEDON AS ClaimCreatedOn,
    A.MODIFIEDBYID AS ClaimModifiedById,
    U2.USERNAME AS ClaimModifiedByName,
    A.MODIFIEDON AS ClaimModifiedOn,
    NULL AS ApprovedMailId,

    A.MMDETAILID AS MMDetailId,
    A.CLAIMTYPE AS ClaimClaimType,
    A.CLAIMPOSTTYPE AS ClaimClaimPostType,

    A.PAYPERIODID AS PayPeriodId,
    PP.PAYPERIODCODE AS PayPeriodCode,
    PP.PAYPERIODNAME AS PayPeriodName,

    A.LOADEDCLAIMID AS LoadedClaimId,
    LC.CLAIMNUMBER AS LoadedClaimClaimNumber,
    LC.CLAIMDATE AS LoadedClaimClaimDate,

    A.PAYMENTID AS PaymentId,
    PMT.PAYMENTNUMBER AS PaymentNumber,
    PMT.PAYMENTDATE AS PaymentDate,

    A.REFERENCECLAIMID AS ReferenceClaimId,
    RC.CLAIMNUMBER AS ReferenceClaimClaimNumber,
    RC.CLAIMDATE AS ReferenceClaimDate,

    A.BILLDOCUMENTID AS BillDocumentId,

    A.CLAIMTYPEID AS ClaimTypeIdId,
    CT.GCMCODE AS ClaimTypeIdCode,
    CT.GCMNAME AS ClaimTypeIdName,

    A.POSTEDPAYPERIODID AS PostedPayPeriodId,
    PPP.PAYPERIODCODE AS PostedPayPeriodCode,
    PPP.PAYPERIODNAME AS PostedPayPeriodName,

    NULL AS MMDetailDocumentNumber,
    A.REFERENCENUMBER AS MMDetailDocumentReferenceNumber,
    A.REFERENCECLAIMID AS TempReferenceDocumentDetailId,
    A.PERIODFROM AS PayPeriodFromDate,
    A.PERIODTO AS PayPeriodToDate,
    NULL AS FunctionReportingToMailId,
    NULL AS FunctionReportingToMobileNumber,
    NULL AS AdminReportingToMailId,
    NULL AS AdminReportingToMobileNumber,
    NULL AS FunctionAndAdminReportingToMailId,
    NULL AS FunctionAndAdminReportingToMobileNumber,

    NULL AS TempDocumentDetailId,
    NULL AS BiztransactionClassId,
    NULL AS IsRecursiveCalling,
    NULL AS ClaimClaimDateString,
    NULL AS ClaimPeriodFromString,
    NULL AS ClaimPeriodToString,

    CD.CLAIMDETAILID AS ClaimDetailId,
    CD.CLAIMHEADID AS ClaimHeadId,
    CH.ACTIVITYCODE AS ClaimHeadCode,
    CH.ACTIVITYNAME AS ClaimHeadName,
    CH.EXPENSEACCOUNTID AS ClaimHeadExpenseAccountId,

    CD.EXPENSEACCOUNTID AS ExpenseAccountId,
    EA.ACCOUNTCODE AS ExpenseAccountCode,
    EA.ACCOUNTNAME AS ExpenseAccountName,

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

    CD.UOMID AS UOMId,
    U.UOMCODE AS UOMCode,
    U.UOMNAME AS UOMName,

    CD.CURRENCYID AS CurrencyId,
    CUR.CURRENCYCODE AS CurrencyCode,
    CUR.CURRENCYNAME AS CurrencyName,

    CD.SLNO AS ClaimDetailSlNo,
    CD.CLAIMDETAILDATE AS ClaimDetailClaimDetailDate,
    CD.QUANTITY AS ClaimDetailQuantity,
    CD.RATE AS ClaimDetailRate,
    CD.CONVERSIONRATE AS ClaimDetailConversionRate,
    CD.AMOUNT AS ClaimDetailAmount,
    CD.ELIGIBLEAMOUNT AS ClaimDetailEligibleAmount,
    CD.BILLAMOUNT AS ClaimDetailBillAmount,
    CD.BILLREFERENCE AS ClaimDetailBillReference,
    CD.CLAIMAMOUNT AS ClaimDetailClaimAmount,
    CD.APPROVEDAMOUNT AS ClaimDetailApprovedAmount,
    CD.DISTANCE AS ClaimDetailDistance,
    CD.REMARKS AS ClaimDetailRemarks,
    CD.DISTANCE AS ClaimDetailDistance,

    CD.FROMCITYID AS FromCityId,
    FC.CITYCODE AS FromCityCode,
    FC.CITYNAME AS FromCityName,

    CD.TOCITYID AS ToCityId,
    TC.CITYCODE AS ToCityCode,
    TC.CITYNAME AS ToCityName,

    CD.FROMLOCATIONID AS FromLocationId,
    FL.LOCATIONCODE AS FromLocationCode,
    FL.LOCATIONNAME AS FromLocationName,

    CD.TOLOCATIONID AS ToLocationId,
    TL.LOCATIONCODE AS ToLocationCode,
    TL.LOCATIONNAME AS ToLocationName,

    CD.MODEID AS ModeId,
    GM.GCMCODE AS ModeCode,
    GM.GCMNAME AS ModeName,

    CD.MMDETAILID AS MMDetailId,
    CD.EMPLOYEEID AS EmployeeId,
    E2.EMPLOYEECODE AS EmployeeCode,
    E2.EMPLOYEENAME AS EmployeeName,

    CD.RECOVERYTYPE AS ClaimDetailRecoveryType,
    CD.RECOVERYPERCENTAGE AS ClaimDetailRecoveryPercentage,
    CD.RECOVERYVALUE AS ClaimDetailRecoveryValue,

    NULL AS TempPartyId,
    NULL AS TempReferenceDocumentDetailId

FROM TCLAIM A
LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = A.OUID
LEFT JOIN MPERIOD P ON P.PERIODID = A.PERIODID
LEFT JOIN MBIZTRANSACTIONTYPE BT ON BT.BIZTRANSACTIONTYPEID = A.BIZTRANSACTIONTYPEID
LEFT JOIN MALLOCATION AL ON AL.ALLOCATIONID = A.ALLOCATIONID
LEFT JOIN MEMPLOYEE E ON E.EMPLOYEEID = A.EMPLOYEEID
LEFT JOIN MDEPARTMENT D ON D.DEPARTMENTID = A.DEPARTMENTID
LEFT JOIN MCITY C ON C.CITYID = A.CITYID
LEFT JOIN TVOUCHER V ON V.VOUCHERID = A.VOUCHERID
LEFT JOIN MEMPLOYEE BR ON BR.EMPLOYEEID = A.BILLSRECEIVDBYID
LEFT JOIN MEMPLOYEE AB ON AB.EMPLOYEEID = A.APPROVEDBYID
LEFT JOIN MUSER U1 ON U1.USERID = A.CREATEDBYID
LEFT JOIN MUSER U2 ON U2.USERID = A.MODIFIEDBYID
LEFT JOIN MPAYPERIOD PP ON PP.PAYPERIODID = A.PAYPERIODID
LEFT JOIN TCLAIM LC ON LC.CLAIMID = A.LOADEDCLAIMID
LEFT JOIN TPAYMENT PMT ON PMT.PAYMENTID = A.PAYMENTID
LEFT JOIN TCLAIM RC ON RC.CLAIMID = A.REFERENCECLAIMID
LEFT JOIN MGCM CT ON CT.GCMID = A.CLAIMTYPEID
LEFT JOIN MPAYPERIOD PPP ON PPP.PAYPERIODID = A.POSTEDPAYPERIODID

LEFT JOIN TCLAIMDETAIL CD ON CD.CLAIMID = A.CLAIMID
LEFT JOIN MACTIVITY CH ON CH.ACTIVITYID = CD.CLAIMHEADID
LEFT JOIN MACCOUNT EA ON EA.ACCOUNTID = CD.EXPENSEACCOUNTID
LEFT JOIN MACCOUNT CA ON CA.ACCOUNTID = CD.CREDITACCOUNTID
LEFT JOIN MUOM U ON U.UOMID = CD.UOMID
LEFT JOIN MCURRENCY CUR ON CUR.CURRENCYID = CD.CURRENCYID
LEFT JOIN MCITY FC ON FC.CITYID = CD.FROMCITYID
LEFT JOIN MCITY TC ON TC.CITYID = CD.TOCITYID
LEFT JOIN MLOCATION FL ON FL.LOCATIONID = CD.FROMLOCATIONID
LEFT JOIN MLOCATION TL ON TL.LOCATIONID = CD.TOLOCATIONID
LEFT JOIN MGCM GM ON GM.GCMID = CD.MODEID
LEFT JOIN MEMPLOYEE E2 ON E2.EMPLOYEEID = CD.EMPLOYEEID

WHERE A.CLAIMID = @claimid;
";

        public const string GET_SELECTLIST_CLAIM = @"SELECT
                            CL.CLAIMID AS Id,
                            CL.CLAIMNUMBER AS ClaimNumber,
                            CL.CLAIMDATE AS ClaimDate,
                            EM.EMPLOYEENAME AS EmployeeName
                            FROM TCLAIM CL
                            INNER JOIN MEMPLOYEE EM ON EM.EMPLOYEEID = CL.EMPLOYEEID
                            WHERE 1 = 1
                            ";



        public const string SAVE_CLAIM = @"
INSERT INTO TClaim
(
    CLAIMID,
    OUID,
    PERIODID,
    BIZTRANSACTIONTYPEID,
    CLAIMNUMBER,
    CLAIMDATE,
    REFERENCENUMBER,
    REFERENCEDATE,
    PERIODFROM,
    PERIODTO,
    EMPLOYEEID,
    CITYID,
    DEPARTMENTID,
    ALLOCATIONID,
    CLAIMSTATUS,
    CLAIMTOTAL,
    ELIGIBLETOTAL,
    BILLTOTAL,
    APPROVEDTOTAL,
    ADVANCE,
    BALANCE,
    PAIDAMOUNT,
    BILLSRECEIVDBYID,
    BILLSRECEIVEDON,
    APPROVEDBYID,
    APPROVEDON,
    VOUCHERID,
    REFERENCECLAIMID,
    POSTEDPAYPERIODID,
    REMARKS,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @ClaimId,
    @OUId,
    @PeriodId,
    @BizTransactionTypeId,
    @ClaimClaimNumber,
    @ClaimClaimDate,
    @ClaimReferenceNumber,
    @ClaimReferenceDate,
    @ClaimPeriodFrom,
    @ClaimPeriodTo,
    @EmployeeId,
    @CityId,
    @DepartmentId,
    @AllocationId,
    @ClaimClaimOStatus,
    @ClaimClaimTotal,
    @ClaimEligibleTotal,
    @ClaimBillTotal,
    @ClaimApprovedTotal,
    @ClaimAdvance,
    @ClaimBalance,
    @ClaimPaidAmount,
    @BillsReceivedById,
    @ClaimBillsReceivedOn,
    @ApprovedById,
    @ClaimApprovedOn,
    @VoucherId,
    @ReferenceClaimId,
    @PostedPayPeriodId,
    @ClaimRemarks,
    @ClaimStatus,
    @ClaimVersion,
    @ClaimCreatedById,
    @ClaimCreatedOn,
    @ClaimModifiedById,
    @ClaimModifiedOn
);";






        public const string UPDATE_CLAIM = @"
UPDATE TClaim
SET
    OUID                 = @OUId,
    PERIODID             = @PeriodId,
    BIZTRANSACTIONTYPEID = @BizTransactionTypeId,
    CLAIMNUMBER          = @ClaimClaimNumber,
    CLAIMDATE            = @ClaimClaimDate,
    REFERENCENUMBER      = @ClaimReferenceNumber,
    REFERENCEDATE        = @ClaimReferenceDate,
    PERIODFROM           = @ClaimPeriodFrom,
    PERIODTO             = @ClaimPeriodTo,
    EMPLOYEEID           = @EmployeeId,
    CITYID               = @CityId,
    DEPARTMENTID         = @DepartmentId,
    ALLOCATIONID         = @AllocationId,
    CLAIMSTATUS          = @ClaimClaimOStatus,
    CLAIMTOTAL           = @ClaimClaimTotal,
    ELIGIBLETOTAL        = @ClaimEligibleTotal,
    BILLTOTAL            = @ClaimBillTotal,
    BILLSRECEIVDBYID     = @BillsReceivedById,
    APPROVEDBYID         = @ApprovedById,
    APPROVEDTOTAL        = @ClaimApprovedTotal,
    ADVANCE              = @ClaimAdvance,
    BALANCE              = @ClaimBalance,
    PAIDAMOUNT           = @ClaimPaidAmount,
    BILLSRECEIVEDON      = @ClaimBillsReceivedOn,
    APPROVEDON           = @ClaimApprovedOn,
    VOUCHERID            = @VoucherId,
    CLAIMTYPEID          = @ClaimTypeIdId,
    REFERENCECLAIMID     = @ReferenceClaimId,
    REMARKS              = @ClaimRemarks,
    STATUS               = @ClaimStatus,
    VERSION              = @ClaimVersion,
    MODIFIEDBYID         = @ClaimModifiedById,
    MODIFIEDON           = @ClaimModifiedOn
WHERE CLAIMID = @ClaimId;";


        public const string DELETE_CLAIM = @"
DELETE FROM TClaim
WHERE CLAIMID = @claimid;";

        // Follow-up write after Accounts posts the claim's settlement voucher — a separate,
        // small transaction (the voucher post itself is a separate BLL call with its own
        // transaction; see ClaimBLL.SaveClaim).
        public const string UPDATE_CLAIM_VOUCHER_POSTING = @"
UPDATE TCLAIM
SET    VOUCHERID   = @VoucherId,
       CLAIMSTATUS = @ClaimStatus
WHERE  CLAIMID = @ClaimId;";


        public const string SAVE_CLAIMDETAIL = @"
INSERT INTO TClaimDetail
(
    CLAIMDETAILID,
    CLAIMID,
    SLNO,
    CLAIMDETAILDATE,
    CLAIMHEADID,
    EXPENSEACCOUNTID,
    CREDITACCOUNTID,
    UOMID,
    CURRENCYID,
    QUANTITY,
    RATE,
    CONVERSIONRATE,
    AMOUNT,
    ELIGIBLEAMOUNT,
    BILLAMOUNT,
    BILLREFERENCE,
    CLAIMAMOUNT,
    APPROVEDAMOUNT,
    REMARKS,
    DISTANCE,
    FROMCITYID,
    TOCITYID,
    FROMLOCATIONID,
    TOLOCATIONID,
    MODEID,
    MMDETAILID,
    EMPLOYEEID,
    RECOVERYTYPE,
    RECOVERYPERCENTAGE,
    RECOVERYVALUE
)
VALUES
(
    @ClaimDetailId,
    @ClaimId,
    @ClaimDetailSlNo,
    @ClaimDetailClaimDetailDate,
    @ClaimHeadId,
    @ExpenseAccountId,
    @CreditAccountId,
    @UOMId,
    @CurrencyId,
    @ClaimDetailQuantity,
    @ClaimDetailRate,
    @ClaimDetailConversionRate,
    @ClaimDetailAmount,
    @ClaimDetailEligibleAmount,
    @ClaimDetailBillAmount,
    @ClaimDetailBillReference,
    @ClaimDetailClaimAmount,
    @ClaimDetailApprovedAmount,
    @ClaimDetailRemarks,
    @ClaimDetailDistance,
    @FromCityId,
    @ToCityId,
    @FromLocationId,
    @ToLocationId,
    @ModeId,
    @MMDetailId,
    @EmployeeId,
    @ClaimDetailRecoveryType,
    @ClaimDetailRecoveryPercentage,
    @ClaimDetailRecoveryValue
);";

        public const string UPDATE_CLAIMDETAIL = @"UPDATE TClaimDetail
SET
    CLAIMID                  = @ClaimId,
    SLNO                     = @ClaimDetailSlNo,
    CLAIMDETAILDATE          = @ClaimDetailClaimDetailDate,
    CLAIMHEADID              = @ClaimHeadId,
    EXPENSEACCOUNTID         = @ExpenseAccountId,
    CREDITACCOUNTID          = @CreditAccountId,
    UOMID                    = @UOMId,
    CURRENCYID               = @CurrencyId,
    QUANTITY                 = @ClaimDetailQuantity,
    RATE                     = @ClaimDetailRate,
    CONVERSIONRATE           = @ClaimDetailConversionRate,
    AMOUNT                   = @ClaimDetailAmount,
    ELIGIBLEAMOUNT           = @ClaimDetailEligibleAmount,
    BILLAMOUNT               = @ClaimDetailBillAmount,
    BILLREFERENCE            = @ClaimDetailBillReference,
    CLAIMAMOUNT              = @ClaimDetailClaimAmount,
    APPROVEDAMOUNT           = @ClaimDetailApprovedAmount,
    REMARKS                  = @ClaimDetailRemarks,
    DISTANCE                 = @ClaimDetailDistance,
    FROMCITYID               = @FromCityId,
    TOCITYID                 = @ToCityId,
    FROMLOCATIONID           = @FromLocationId,
    TOLOCATIONID             = @ToLocationId,
    MODEID                   = @ModeId,
    MMDETAILID               = @MMDetailId,
    EMPLOYEEID               = @EmployeeId,
    RECOVERYTYPE             = @ClaimDetailRecoveryType,
    RECOVERYPERCENTAGE       = @ClaimDetailRecoveryPercentage,
    RECOVERYVALUE            = @ClaimDetailRecoveryValue
WHERE
    CLAIMDETAILID = @ClaimDetailId;";
        public const string DELETE_CLAIMDETAIL = @"
DELETE FROM TClaimDetail
WHERE CLAIMID = @ClaimId;";

        // Business-rule guards/lookups — confirmed against the real TCLAIM/MPERIOD/MBIZTRANSACTIONTYPE
        // columns already used elsewhere in this file / in AdminDAL's PeriodQB and BIZTransactionTypeQB.
        public const string GET_CLAIM_ALREADY_PROCESSED = @"
SELECT COUNT(1) FROM TClaim WHERE CLAIMID = @ClaimId AND POSTEDPAYPERIODID <> -1;";

        public const string GET_PERIOD_WINDOW = @"
SELECT FROMDATE AS FromDate, TODATE AS ToDate, STATUS AS Status
FROM MPERIOD WHERE PERIODID = @PeriodId;";

        public const string GET_BIZTRANSACTIONTYPE_FOR_CLASS = @"
SELECT TOP 1 BIZTRANSACTIONTYPEID
FROM MBIZTRANSACTIONTYPE
WHERE BIZTRANSACTIONCLASSID = @BizTransactionClassId
AND   PERIODID = @PeriodId
AND   OUID = @OUId;";
    }
}