namespace AccountsDAL.Query.Payment
{
    public static class PaymentQB
    {
        // Ported from GB4 PaymentBLL.GetSelectListPayment / PaymentDAL.GetSelectListPaymentFromDetail,
        // which ran as HQL over the Payment/PaymentDetail NHibernate entities rather than raw SQL.
        // Column names translated 1:1 from D:\GB4 Service\DAL\AccountsDAL\HibernateMapFile\
        // Payment.hbm.xml (Payment -> TPAYMENT) and PaymentDetail.hbm.xml (PaymentDetail -> TPAYMENTDETAIL) —
        // no live GB5_SCHEMA_DB access was available in this session to re-verify against the running schema.
        // Default variant only returns payments with at least one outstanding (unpaid) detail line,
        // matching legacy's "(AmountRequired-AmountPaid)>0" join filter (translated from an inner join +
        // DISTINCT to an EXISTS, since only TPAYMENT columns are ever selected).
        public const string GET_SELECTLIST_PAYMENT = @"
WITH PagedPayment AS
(
    SELECT
        P.PAYMENTID AS Id,
        P.PAYMENTNUMBER AS Number,
        P.REFERENCENUMBER AS ReferenceNumber,
        P.PAYMENTDATE AS Date,
        P.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
        ROW_NUMBER() OVER (ORDER BY P.PAYMENTNUMBER DESC) AS RowNum
    FROM TPAYMENT P
    WHERE EXISTS (
        SELECT 1 FROM TPAYMENTDETAIL PD
        WHERE PD.PAYMENTID = P.PAYMENTID
          AND (PD.AMOUNTREQUIRED - PD.AMOUNTPAID) > 0
    )
      AND (@Id IS NULL OR P.PAYMENTID = @Id)
      AND (@Status IS NULL OR P.STATUS = @Status)
      AND (@BizTransactionTypeId IS NULL OR P.BIZTRANSACTIONTYPEID = @BizTransactionTypeId)
      AND (@OUId IS NULL OR P.OUID = @OUId)
      AND (@Number IS NULL OR @Number = ''
           OR P.PAYMENTNUMBER LIKE '%' + @Number + '%'
           OR P.REFERENCENUMBER LIKE '%' + @Number + '%')
)
SELECT Id, Number, ReferenceNumber, Date, BizTransactionTypeId
FROM PagedPayment
WHERE RowNum BETWEEN @FirstNumber AND (@FirstNumber + @MaxResult - 1)
ORDER BY RowNum;";

        // Selected when criteria field "IsFromScreen" = 0 — same filters, without the
        // outstanding-balance EXISTS check (legacy: GET_PAYMENT_AND_DETAIL_FROM_SCREEN).
        public const string GET_SELECTLIST_PAYMENT_FROM_SCREEN = @"
WITH PagedPayment AS
(
    SELECT
        P.PAYMENTID AS Id,
        P.PAYMENTNUMBER AS Number,
        P.REFERENCENUMBER AS ReferenceNumber,
        P.PAYMENTDATE AS Date,
        P.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
        ROW_NUMBER() OVER (ORDER BY P.PAYMENTNUMBER DESC) AS RowNum
    FROM TPAYMENT P
    WHERE (@Id IS NULL OR P.PAYMENTID = @Id)
      AND (@Status IS NULL OR P.STATUS = @Status)
      AND (@BizTransactionTypeId IS NULL OR P.BIZTRANSACTIONTYPEID = @BizTransactionTypeId)
      AND (@OUId IS NULL OR P.OUID = @OUId)
      AND (@Number IS NULL OR @Number = ''
           OR P.PAYMENTNUMBER LIKE '%' + @Number + '%'
           OR P.REFERENCENUMBER LIKE '%' + @Number + '%')
)
SELECT Id, Number, ReferenceNumber, Date, BizTransactionTypeId
FROM PagedPayment
WHERE RowNum BETWEEN @FirstNumber AND (@FirstNumber + @MaxResult - 1)
ORDER BY RowNum;";

        // ── PaymentToVoucher (read-only preview transform) ──────────────────────
        // Ported from GB4 PaymentBLL.PaymentToVoucher (Payment.svc's /PaymentToVoucher/?PaymentId=
        // operation, GET method) — a pure read-only "derive a proposed Voucher shape from an
        // existing Payment" operation. It never inserts into TVOUCHER/TVOUCHERDETAIL/
        // TBILLALLOCATION; it only builds and returns an in-memory VoucherDTO. Not to be confused
        // with the separate, mutating PaymentToVoucherPosting operation (not this endpoint).
        // All tables confirmed live via INFORMATION_SCHEMA.COLUMNS (2026-07-25): TPAYMENT,
        // TPAYMENTDETAIL, TCOSTCENTERALLOCATION, MCOSTCATEGORYCONDITION, MCURRENCY. No TENANTID on
        // either T-table (same per-tenant-database pattern already confirmed for Voucher/Bill
        // Allocation tables); MCOSTCATEGORYCONDITION/MCURRENCY do have TENANTID (master tables).
        public const string GET_PAYMENT_HEADER_FOR_VOUCHER = @"
            SELECT
                P.PAYMENTID AS PaymentId,
                P.REFERENCENUMBER AS PaymentReferenceNumber,
                P.REFERENCEDATE AS PaymentReferenceDate,
                P.PAYMENTDATE AS PaymentDate,
                P.REMARKS AS PaymentRemarks
            FROM TPAYMENT P
            WHERE P.PAYMENTID = @PaymentId;";

        // Dual PayTo/PayFrom account+currency projection — one row per TPAYMENTDETAIL. GB4 loaded
        // both accounts via lazy NHibernate navigation properties (PayToAccount/PayFromAccount);
        // ported here as two explicit LEFT JOINs against MACCOUNT/MCURRENCY.
        public const string GET_PAYMENT_DETAILS_FOR_VOUCHER = @"
            SELECT
                PD.PAYMENTDETAILID AS PaymentDetailId,
                PD.AMOUNTREQUIRED AS AmountRequired,
                PD.AMOUNTPAID AS AmountPaid,
                PD.REMARKS AS Remarks,

                PD.PAYTOACCOUNTID AS PayToAccountId,
                PTA.ACCOUNTCODE AS PayToAccountCode,
                PTA.ACCOUNTNAME AS PayToAccountName,
                PTA.BILLALLOCATIONTYPE AS PayToAccountBillAllocationType,
                PTA.ACCOUNTTYPE AS PayToAccountType,
                PTA.ACCOUNTGROUPID AS PayToAccountGroupId,
                PTA.CURRENCYID AS PayToCurrencyId,
                PTC.CURRENCYCODE AS PayToCurrencyCode,
                PTC.CURRENCYNAME AS PayToCurrencyName,

                PD.PAYFROMACCOUNTID AS PayFromAccountId,
                PFA.ACCOUNTCODE AS PayFromAccountCode,
                PFA.ACCOUNTNAME AS PayFromAccountName,
                PFA.BILLALLOCATIONTYPE AS PayFromAccountBillAllocationType,
                PFA.ACCOUNTTYPE AS PayFromAccountType,
                PFA.ACCOUNTGROUPID AS PayFromAccountGroupId,
                PFA.CURRENCYID AS PayFromCurrencyId,
                PFC.CURRENCYCODE AS PayFromCurrencyCode,
                PFC.CURRENCYNAME AS PayFromCurrencyName
            FROM TPAYMENTDETAIL PD
            LEFT JOIN MACCOUNT PTA ON PTA.ACCOUNTID = PD.PAYTOACCOUNTID
            LEFT JOIN MCURRENCY PTC ON PTC.CURRENCYID = PTA.CURRENCYID
            LEFT JOIN MACCOUNT PFA ON PFA.ACCOUNTID = PD.PAYFROMACCOUNTID
            LEFT JOIN MCURRENCY PFC ON PFC.CURRENCYID = PFA.CURRENCYID
            WHERE PD.PAYMENTID = @PaymentId;";

        // ObjectHeaderTypeId -1399999514 is GB4's own Entity-type constant identifying "Payment"
        // as the cost-center-allocation object-header type — reused verbatim, not re-derived.
        public const string GET_COST_CENTER_ALLOCATION_FOR_PAYMENT = @"
            SELECT
                CCA.OBJECTID AS CostCenterAllocationObjectId,
                CCA.COSTCATEGORYSLNO AS CostCenterAllocationCostCategorySlno,
                CCA.SLNO AS CostCenterAllocationSlno,
                CCA.ALLOCATEDQUANTITY AS CostCenterAllocationAllocatedQuantity,
                CCA.COSTCATEGORYID AS CostCategoryId,
                CCAT.COSTCATEGORYCODE AS CostCategoryCode,
                CCAT.COSTCATEGORYNAME AS CostCategoryName,
                CCA.COSTCENTERID AS CostCenterId,
                CC.COSTCENTERCODE AS CostCenterCode,
                CC.COSTCENTERNAME AS CostCenterName
            FROM TCOSTCENTERALLOCATION CCA
            LEFT JOIN MCOSTCATEGORY CCAT ON CCAT.COSTCATEGORYID = CCA.COSTCATEGORYID
            LEFT JOIN MCOSTCENTER CC ON CC.COSTCENTERID = CCA.COSTCENTERID
            WHERE CCA.OBJECTHEADERID = @PaymentId
              AND CCA.OBJECTHEADERTYPEID = @ObjectHeaderTypeId;";

        public const string CHECK_COST_CATEGORY_CONDITION_EXISTS_FOR_ACCOUNT_GROUP = @"
            SELECT TOP 1 1
            FROM MCOSTCATEGORYCONDITION
            WHERE ACCOUNTGROUPID = @AccountGroupId
              AND TENANTID = @TenantId;";

        public const string GET_ORGANIZATION_UNIT_FOR_VOUCHER = @"
            SELECT
                OUID AS OUId,
                ORGANIZATIONUNITCODE AS OUCode,
                ORGANIZATIONUNITNAME AS OUName
            FROM MORGANIZATIONUNIT
            WHERE OUID = @OUId;";
    }
}