namespace GoodBooks.PAY.PAYDAL.Query.Order
{
    /// <summary>
    /// SQL constants for TPAYTRANSACTION.
    /// All queries MUST filter by TENANTID.
    /// </summary>
    public static class PayTransactionQB
    {
        // INDEX NOTES:
        // TPAYTRANSACTION: PK_TPAYTRANSACTION — clustered on PAYTRANSACTIONID
        //   → Used by GET_PAY_TRANSACTION
        // TPAYTRANSACTION: IX_TPAYTRANSACTION_PAYORDERID_TENANTID
        //   — (PAYORDERID, TENANTID) INCLUDE (TXNSTATUS, TXNINITIATEDON, TXNCOMPLETEDON)
        //   → Used by GET_PAY_TRANSACTIONS_BY_ORDER
        // TPAYTRANSACTION: IX_TPAYTRANSACTION_TENANTID_GATEWAYTXNID
        //   — (TENANTID, GATEWAYTXNID)
        //   → Supports deduplication checks on inbound webhook gateway transaction IDs

        public const string INSERT_PAY_TRANSACTION = @"
            INSERT INTO TPAYTRANSACTION (
                PAYORDERID,
                GATEWAYTXNID,
                PAYMENTMETHOD,
                PAYMENTMETHODDETAIL,
                TXNCURRENCY,
                TXNAMT,
                TXNSTATUS,
                GATEWAYRESPONSECODE,
                GATEWAYRESPONSEMSG,
                GATEWAYRAWRESPONSE,
                TXNINITIATEDON,
                TXNCOMPLETEDON,
                PARENTTXNID,
                REMARKS,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE,
                TENANTID
            ) VALUES (
                @PayOrderId,
                @GatewayTxnId,
                @PaymentMethod,
                @PaymentMethodDetail,
                @TxnCurrency,
                @TxnAmt,
                @TxnStatus,
                @GatewayResponseCode,
                @GatewayResponseMsg,
                @GatewayRawResponse,
                @TxnInitiatedOn,
                @TxnCompletedOn,
                @ParentTxnId,
                @Remarks,
                @Version,
                @Status,
                @SortOrder,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn,
                @SourceType,
                @TenantId
            )";

        public const string GET_PAY_TRANSACTION = @"
            SELECT
                T.PAYTRANSACTIONID      AS PayTransactionId,
                T.PAYORDERID            AS PayOrderId,
                T.GATEWAYTXNID          AS GatewayTxnId,
                T.PAYMENTMETHOD         AS PaymentMethod,
                T.PAYMENTMETHODDETAIL   AS PaymentMethodDetail,
                T.TXNCURRENCY           AS TxnCurrency,
                T.TXNAMT                AS TxnAmt,
                T.TXNSTATUS             AS TxnStatus,
                T.GATEWAYRESPONSECODE   AS GatewayResponseCode,
                T.GATEWAYRESPONSEMSG    AS GatewayResponseMsg,
                T.GATEWAYRAWRESPONSE    AS GatewayRawResponse,
                T.TXNINITIATEDON        AS TxnInitiatedOn,
                T.TXNCOMPLETEDON        AS TxnCompletedOn,
                T.PARENTTXNID           AS ParentTxnId,
                T.REMARKS               AS Remarks,
                T.VERSION               AS Version,
                T.STATUS                AS Status,
                T.SORTORDER             AS SortOrder,
                T.CREATEDBYID           AS CreatedById,
                T.CREATEDON             AS CreatedOn,
                T.MODIFIEDBYID          AS ModifiedById,
                T.MODIFIEDON            AS ModifiedOn,
                T.SOURCETYPE            AS SourceType,
                T.TENANTID              AS TenantId
            FROM TPAYTRANSACTION T
            WHERE T.PAYTRANSACTIONID = @PayTransactionId
              AND T.TENANTID = @TenantId";

        public const string GET_PAY_TRANSACTIONS_BY_ORDER = @"
            SELECT
                T.PAYTRANSACTIONID      AS PayTransactionId,
                T.PAYORDERID            AS PayOrderId,
                T.GATEWAYTXNID          AS GatewayTxnId,
                T.PAYMENTMETHOD         AS PaymentMethod,
                T.PAYMENTMETHODDETAIL   AS PaymentMethodDetail,
                T.TXNCURRENCY           AS TxnCurrency,
                T.TXNAMT                AS TxnAmt,
                T.TXNSTATUS             AS TxnStatus,
                T.GATEWAYRESPONSECODE   AS GatewayResponseCode,
                T.GATEWAYRESPONSEMSG    AS GatewayResponseMsg,
                T.GATEWAYRAWRESPONSE    AS GatewayRawResponse,
                T.TXNINITIATEDON        AS TxnInitiatedOn,
                T.TXNCOMPLETEDON        AS TxnCompletedOn,
                T.PARENTTXNID           AS ParentTxnId,
                T.REMARKS               AS Remarks,
                T.VERSION               AS Version,
                T.STATUS                AS Status,
                T.SORTORDER             AS SortOrder,
                T.CREATEDBYID           AS CreatedById,
                T.CREATEDON             AS CreatedOn,
                T.MODIFIEDBYID          AS ModifiedById,
                T.MODIFIEDON            AS ModifiedOn,
                T.SOURCETYPE            AS SourceType,
                T.TENANTID              AS TenantId
            FROM TPAYTRANSACTION T
            WHERE T.PAYORDERID = @PayOrderId
              AND T.TENANTID = @TenantId
            ORDER BY T.TXNINITIATEDON DESC";
    }
}