namespace GoodBooks.PAY.PAYDAL.Query.Order
{
    /// <summary>
    /// SQL constants for TPAYORDER.
    /// All queries MUST filter by TENANTID.
    /// NOTE: TPAYORDER does NOT have a DATABASENAME column — filter by TENANTID only.
    /// </summary>
    public static class PayOrderQB
    {
        // INDEX NOTES:
        // TPAYORDER: PK_TPAYORDER — clustered on PAYORDERID
        //   → Used by GET_PAY_ORDER, UPDATE_PAY_ORDER_STATUS, UPDATE_PAY_ORDER_VOUCHERID
        // TPAYORDER: IX_TPAYORDER_TENANTID_ORDERSTATUS
        //   — (TENANTID, ORDERSTATUS) INCLUDE (PAYORDERNO, CUSTOMERNAME, NETPAYABLEAMT, CREATEDON)
        //   → Used by GET_PAY_ORDER_LIST paged query
        // TPAYORDER: IX_TPAYORDER_GATEWAYPAYORDERID_TENANTID
        //   — (GATEWAYPAYORDERID, TENANTID)
        //   → Used by GET_PAY_ORDER_BY_GATEWAY_ORDER_ID (webhook callback lookup)
        // TPAYORDER: IX_TPAYORDER_TENANTID_SOURCEDOCTYPE_SOURCEDOCID
        //   — (TENANTID, SOURCEDOCTYPE, SOURCEDOCID) INCLUDE (ORDERSTATUS)
        //   → Used by COUNT_ACTIVE_ORDERS_FOR_SOURCEDOC (duplicate order guard)

        public const string GET_PAY_ORDER = @"
            SELECT
                O.PAYORDERID            AS PayOrderId,
                O.PAYORDERNO            AS PayOrderNo,
                O.SOURCEDOCTYPE         AS SourceDocType,
                O.SOURCEDOCID           AS SourceDocId,
                O.CUSTOMERID            AS CustomerId,
                O.CUSTOMERNAME          AS CustomerName,
                O.CUSTOMEREMAIL         AS CustomerEmail,
                O.CUSTOMERPHONE         AS CustomerPhone,
                O.ORDERCURRENCY         AS OrderCurrency,
                O.ORDERAMT              AS OrderAmt,
                O.BASECURRENCY          AS BaseCurrency,
                O.BASEAMT               AS BaseAmt,
                O.EXCHANGERATE          AS ExchangeRate,
                O.COUPONDISCAMT         AS CouponDiscAmt,
                O.LOYALTYDISCAMT        AS LoyaltyDiscAmt,
                O.NETPAYABLEAMT         AS NetPayableAmt,
                O.PAYGATEWAYCONFID      AS PayGatewayConfId,
                O.GATEWAYPAYORDERID     AS GatewayPayOrderId,
                O.ORDERSTATUS           AS OrderStatus,
                O.PAIDAMT               AS PaidAmt,
                O.EXPIRESON             AS ExpiresOn,
                O.VOUCHERID             AS VoucherId,
                O.REMARKS               AS Remarks,
                G.PAYGATEWAYCODE        AS GatewayCode,
                G.PAYGATEWAYNAME        AS GatewayName,
                O.VERSION               AS Version,
                O.STATUS                AS Status,
                O.SORTORDER             AS SortOrder,
                O.CREATEDBYID           AS CreatedById,
                O.CREATEDON             AS CreatedOn,
                O.MODIFIEDBYID          AS ModifiedById,
                O.MODIFIEDON            AS ModifiedOn,
                O.SOURCETYPE            AS SourceType,
                O.TENANTID              AS TenantId
            FROM TPAYORDER O
            LEFT JOIN MPAYGATEWAYCONFIG C ON C.PAYGATEWAYCONFID = O.PAYGATEWAYCONFID
            LEFT JOIN MPAYGATEWAY G ON G.PAYGATEWAYID = C.PAYGATEWAYID
            WHERE O.PAYORDERID = @PayOrderId
              AND O.TENANTID = @TenantId";

        // Entitlement↔Payment loose coupling (Docs/Platform-Architecture-Integration-
        // Reference.md §9.1) — every order ever created for one SourceDoc (e.g. one
        // subscription's full payment history: initial purchase + every renewal), newest
        // first. Reuses the same (TENANTID, SOURCEDOCTYPE, SOURCEDOCID) index the duplicate-
        // order guard already relies on — no new index needed.
        public const string GET_PAY_ORDERS_BY_SOURCEDOC = @"
            SELECT
                O.PAYORDERID            AS PayOrderId,
                O.PAYORDERNO            AS PayOrderNo,
                O.SOURCEDOCTYPE         AS SourceDocType,
                O.SOURCEDOCID           AS SourceDocId,
                O.ORDERCURRENCY         AS OrderCurrency,
                O.ORDERAMT              AS OrderAmt,
                O.NETPAYABLEAMT         AS NetPayableAmt,
                O.ORDERSTATUS           AS OrderStatus,
                O.PAIDAMT               AS PaidAmt,
                G.PAYGATEWAYCODE        AS GatewayCode,
                G.PAYGATEWAYNAME        AS GatewayName,
                O.CREATEDON             AS CreatedOn,
                O.TENANTID              AS TenantId
            FROM TPAYORDER O
            LEFT JOIN MPAYGATEWAYCONFIG C ON C.PAYGATEWAYCONFID = O.PAYGATEWAYCONFID
            LEFT JOIN MPAYGATEWAY G ON G.PAYGATEWAYID = C.PAYGATEWAYID
            WHERE O.SOURCEDOCTYPE = @SourceDocType
              AND O.SOURCEDOCID = @SourceDocId
              AND O.TENANTID = @TenantId
            ORDER BY O.CREATEDON DESC";

        public const string GET_PAY_ORDER_BY_GATEWAY_ORDER_ID = @"
            SELECT
                O.PAYORDERID            AS PayOrderId,
                O.PAYORDERNO            AS PayOrderNo,
                O.SOURCEDOCTYPE         AS SourceDocType,
                O.SOURCEDOCID           AS SourceDocId,
                O.CUSTOMERID            AS CustomerId,
                O.CUSTOMERNAME          AS CustomerName,
                O.CUSTOMEREMAIL         AS CustomerEmail,
                O.CUSTOMERPHONE         AS CustomerPhone,
                O.ORDERCURRENCY         AS OrderCurrency,
                O.ORDERAMT              AS OrderAmt,
                O.BASECURRENCY          AS BaseCurrency,
                O.BASEAMT               AS BaseAmt,
                O.EXCHANGERATE          AS ExchangeRate,
                O.COUPONDISCAMT         AS CouponDiscAmt,
                O.LOYALTYDISCAMT        AS LoyaltyDiscAmt,
                O.NETPAYABLEAMT         AS NetPayableAmt,
                O.PAYGATEWAYCONFID      AS PayGatewayConfId,
                O.GATEWAYPAYORDERID     AS GatewayPayOrderId,
                O.ORDERSTATUS           AS OrderStatus,
                O.PAIDAMT               AS PaidAmt,
                O.EXPIRESON             AS ExpiresOn,
                O.VOUCHERID             AS VoucherId,
                O.REMARKS               AS Remarks,
                G.PAYGATEWAYCODE        AS GatewayCode,
                G.PAYGATEWAYNAME        AS GatewayName,
                O.VERSION               AS Version,
                O.STATUS                AS Status,
                O.SORTORDER             AS SortOrder,
                O.CREATEDBYID           AS CreatedById,
                O.CREATEDON             AS CreatedOn,
                O.MODIFIEDBYID          AS ModifiedById,
                O.MODIFIEDON            AS ModifiedOn,
                O.SOURCETYPE            AS SourceType,
                O.TENANTID              AS TenantId
            FROM TPAYORDER O
            LEFT JOIN MPAYGATEWAYCONFIG C ON C.PAYGATEWAYCONFID = O.PAYGATEWAYCONFID
            LEFT JOIN MPAYGATEWAY G ON G.PAYGATEWAYID = C.PAYGATEWAYID
            WHERE O.GATEWAYPAYORDERID = @GatewayPayOrderId
              AND O.TENANTID = @TenantId";

        public const string GET_PAY_ORDER_LIST = @"
            SELECT
                O.PAYORDERID            AS PayOrderId,
                O.PAYORDERNO            AS PayOrderNo,
                O.SOURCEDOCTYPE         AS SourceDocType,
                O.SOURCEDOCID           AS SourceDocId,
                O.CUSTOMERNAME          AS CustomerName,
                O.NETPAYABLEAMT         AS NetPayableAmt,
                O.ORDERSTATUS           AS OrderStatus,
                O.PAIDAMT               AS PaidAmt,
                G.PAYGATEWAYCODE        AS GatewayCode,
                O.CREATEDON             AS CreatedOn,
                O.TENANTID              AS TenantId
            FROM TPAYORDER O
            LEFT JOIN MPAYGATEWAYCONFIG C ON C.PAYGATEWAYCONFID = O.PAYGATEWAYCONFID
            LEFT JOIN MPAYGATEWAY G ON G.PAYGATEWAYID = C.PAYGATEWAYID
            WHERE O.TENANTID = @TenantId
            ORDER BY O.CREATEDON DESC
            OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string COUNT_PAY_ORDER_LIST = @"
            SELECT COUNT(1)
            FROM TPAYORDER O
            WHERE O.TENANTID = @TenantId";

        /// <summary>
        /// Returns count of orders for a given source document that are not expired or cancelled.
        /// OrderStatus: 1=Pending, 2=Processing, 3=Paid — excludes 4=Expired, 5=Cancelled, 6=Failed.
        /// Used as a duplicate-order guard before InitiatePayOrder.
        /// </summary>
        public const string COUNT_ACTIVE_ORDERS_FOR_SOURCEDOC = @"
            SELECT COUNT(1)
            FROM TPAYORDER O
            WHERE O.TENANTID = @TenantId
              AND O.SOURCEDOCTYPE = @SourceDocType
              AND O.SOURCEDOCID = @SourceDocId
              AND O.ORDERSTATUS IN (1, 2, 3)";

        public const string INSERT_PAY_ORDER = @"
            INSERT INTO TPAYORDER (
                PAYORDERNO,
                SOURCEDOCTYPE,
                SOURCEDOCID,
                CUSTOMERID,
                CUSTOMERNAME,
                CUSTOMEREMAIL,
                CUSTOMERPHONE,
                ORDERCURRENCY,
                ORDERAMT,
                BASECURRENCY,
                BASEAMT,
                EXCHANGERATE,
                COUPONDISCAMT,
                LOYALTYDISCAMT,
                NETPAYABLEAMT,
                PAYGATEWAYCONFID,
                GATEWAYPAYORDERID,
                ORDERSTATUS,
                PAIDAMT,
                EXPIRESON,
                VOUCHERID,
                REMARKS,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE,
                TENANTID
            ) VALUES (
                @PayOrderNo,
                @SourceDocType,
                @SourceDocId,
                @CustomerId,
                @CustomerName,
                @CustomerEmail,
                @CustomerPhone,
                @OrderCurrency,
                @OrderAmt,
                @BaseCurrency,
                @BaseAmt,
                @ExchangeRate,
                @CouponDiscAmt,
                @LoyaltyDiscAmt,
                @NetPayableAmt,
                @PayGatewayConfId,
                @GatewayPayOrderId,
                @OrderStatus,
                @PaidAmt,
                @ExpiresOn,
                @VoucherId,
                @Remarks,
                @Version,
                @Status,
                @SortOrder,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn,
                @SourceType,
                @TenantId
            )";

        /// <summary>
        /// Targeted status update — used by webhook callback and payment return handler.
        /// Only updates ORDERSTATUS, GATEWAYPAYORDERID, PAIDAMT, and audit fields.
        /// Does NOT update financial amounts to prevent accidental overwrites.
        /// </summary>
        public const string UPDATE_PAY_ORDER_STATUS = @"
            UPDATE TPAYORDER SET
                ORDERSTATUS         = @OrderStatus,
                GATEWAYPAYORDERID   = @GatewayPayOrderId,
                PAIDAMT             = @PaidAmt,
                MODIFIEDBYID        = @ModifiedById,
                MODIFIEDON          = @ModifiedOn
            WHERE PAYORDERID = @PayOrderId
              AND TENANTID = @TenantId";

        /// <summary>
        /// Updates the VoucherId after the Finance module posts the receipt voucher.
        /// Called from Finance receipt-posting flow after successful payment confirmation.
        /// </summary>
        public const string UPDATE_PAY_ORDER_VOUCHERID = @"
            UPDATE TPAYORDER SET
                VOUCHERID       = @VoucherId,
                MODIFIEDBYID    = @ModifiedById,
                MODIFIEDON      = @ModifiedOn
            WHERE PAYORDERID = @PayOrderId
              AND TENANTID = @TenantId";
    }
}