namespace GoodBooks.PAY.PAYDAL.Query.Gateway
{
    /// <summary>
    /// SQL constants for MPAYGATEWAYCONFIG.
    /// All queries MUST filter by TENANTID — per-tenant gateway configurations.
    /// </summary>
    public static class PayGatewayConfigQB
    {
        // INDEX NOTES:
        // MPAYGATEWAYCONFIG: IX_MPAYGATEWAYCONFIG_TENANTID_PAYGATEWAYID
        //   — (TENANTID, PAYGATEWAYID) INCLUDE (MERCHANTID, GATEWAYMODE, ISDEFAULT, STATUS)
        //   → Used by GET_PAY_GATEWAY_CONFIG_BY_GATEWAY_ID and GET_PAY_GATEWAY_CONFIG_FOR_WEBHOOK
        // MPAYGATEWAYCONFIG: PK_MPAYGATEWAYCONFIG — clustered on PAYGATEWAYCONFID
        //   → Used by GET_PAY_GATEWAY_CONFIG
        // MPAYGATEWAYCONFIG: IX_MPAYGATEWAYCONFIG_TENANTID_GATEWAYCODE
        //   — (TENANTID) INCLUDE (APIKEYIDVAULTKEY, APISECRETVAULTKEY, WEBHOOKSECRETVAULTKEY)
        //   → Used by GET_PAY_GATEWAY_CONFIG_FOR_WEBHOOK (joined to MPAYGATEWAY on PAYGATEWAYCODE)

        public const string GET_PAY_GATEWAY_CONFIG = @"
            SELECT
                C.PAYGATEWAYCONFID          AS PayGatewayConfId,
                C.PAYGATEWAYID              AS PayGatewayId,
                G.PAYGATEWAYCODE            AS PayGatewayCode,
                G.PAYGATEWAYNAME            AS PayGatewayName,
                C.MERCHANTID                AS MerchantId,
                C.WEBHOOKURL                AS WebhookUrl,
                C.APIKEYIDVAULTKEY          AS ApiKeyIdVaultKey,
                C.APISECRETVAULTKEY         AS ApiSecretVaultKey,
                C.WEBHOOKSECRETVAULTKEY     AS WebhookSecretVaultKey,
                C.GATEWAYMODE               AS GatewayMode,
                C.ISDEFAULT                 AS IsDefault,
                C.SUPPORTEDCURRENCY         AS SupportedCurrency,
                C.REMARKS                   AS Remarks,
                C.VERSION                   AS Version,
                C.STATUS                    AS Status,
                C.SORTORDER                 AS SortOrder,
                C.CREATEDBYID               AS CreatedById,
                C.CREATEDON                 AS CreatedOn,
                C.MODIFIEDBYID              AS ModifiedById,
                C.MODIFIEDON                AS ModifiedOn,
                C.SOURCETYPE                AS SourceType,
                C.TENANTID                  AS TenantId
            FROM MPAYGATEWAYCONFIG C
            INNER JOIN MPAYGATEWAY G ON G.PAYGATEWAYID = C.PAYGATEWAYID
            WHERE C.PAYGATEWAYCONFID = @PayGatewayConfId
              AND C.TENANTID = @TenantId";

        public const string GET_PAY_GATEWAY_CONFIG_BY_GATEWAY_ID = @"
            SELECT
                C.PAYGATEWAYCONFID          AS PayGatewayConfId,
                C.PAYGATEWAYID              AS PayGatewayId,
                G.PAYGATEWAYCODE            AS PayGatewayCode,
                G.PAYGATEWAYNAME            AS PayGatewayName,
                C.MERCHANTID                AS MerchantId,
                C.WEBHOOKURL                AS WebhookUrl,
                C.APIKEYIDVAULTKEY          AS ApiKeyIdVaultKey,
                C.APISECRETVAULTKEY         AS ApiSecretVaultKey,
                C.WEBHOOKSECRETVAULTKEY     AS WebhookSecretVaultKey,
                C.GATEWAYMODE               AS GatewayMode,
                C.ISDEFAULT                 AS IsDefault,
                C.SUPPORTEDCURRENCY         AS SupportedCurrency,
                C.REMARKS                   AS Remarks,
                C.VERSION                   AS Version,
                C.STATUS                    AS Status,
                C.SORTORDER                 AS SortOrder,
                C.CREATEDBYID               AS CreatedById,
                C.CREATEDON                 AS CreatedOn,
                C.MODIFIEDBYID              AS ModifiedById,
                C.MODIFIEDON                AS ModifiedOn,
                C.SOURCETYPE                AS SourceType,
                C.TENANTID                  AS TenantId
            FROM MPAYGATEWAYCONFIG C
            INNER JOIN MPAYGATEWAY G ON G.PAYGATEWAYID = C.PAYGATEWAYID
            WHERE C.PAYGATEWAYID = @PayGatewayId
              AND C.TENANTID = @TenantId
              AND C.STATUS = 1
            ORDER BY C.ISDEFAULT DESC, C.SORTORDER";

        /// <summary>
        /// Returns only the vault key paths and TenantId for webhook signature verification.
        /// Used by webhook BLL before a full LoginDTO is constructed — intentionally lightweight.
        /// Filters by PAYGATEWAYCODE (from MPAYGATEWAY) and TENANTID.
        /// </summary>
        public const string GET_PAY_GATEWAY_CONFIG_FOR_WEBHOOK = @"
            SELECT
                C.PAYGATEWAYCONFID          AS PayGatewayConfId,
                C.PAYGATEWAYID              AS PayGatewayId,
                G.PAYGATEWAYCODE            AS PayGatewayCode,
                G.PAYGATEWAYNAME            AS PayGatewayName,
                C.MERCHANTID                AS MerchantId,
                C.WEBHOOKURL                AS WebhookUrl,
                C.APIKEYIDVAULTKEY          AS ApiKeyIdVaultKey,
                C.APISECRETVAULTKEY         AS ApiSecretVaultKey,
                C.WEBHOOKSECRETVAULTKEY     AS WebhookSecretVaultKey,
                C.GATEWAYMODE               AS GatewayMode,
                C.ISDEFAULT                 AS IsDefault,
                C.SUPPORTEDCURRENCY         AS SupportedCurrency,
                C.REMARKS                   AS Remarks,
                C.VERSION                   AS Version,
                C.STATUS                    AS Status,
                C.SORTORDER                 AS SortOrder,
                C.CREATEDBYID               AS CreatedById,
                C.CREATEDON                 AS CreatedOn,
                C.MODIFIEDBYID              AS ModifiedById,
                C.MODIFIEDON                AS ModifiedOn,
                C.SOURCETYPE                AS SourceType,
                C.TENANTID                  AS TenantId
            FROM MPAYGATEWAYCONFIG C
            INNER JOIN MPAYGATEWAY G ON G.PAYGATEWAYID = C.PAYGATEWAYID
            WHERE G.PAYGATEWAYCODE = @GatewayCode
              AND C.TENANTID = @TenantId
              AND C.STATUS = 1";

        /// <summary>
        /// Returns the DatabaseName for a given TenantId — used by the webhook BLL to
        /// construct a system-level LoginDTO before processing the inbound webhook payload.
        /// Queries MCLIENT which is a platform-level table (no TENANTID filter on MCLIENT itself).
        /// </summary>
        public const string GET_DATABASE_NAME_FOR_TENANT = @"
            SELECT
                CL.DATABASENAME AS DatabaseName
            FROM MCLIENT CL
            WHERE CL.TENANTID = @TenantId
              AND CL.STATUS = 1";

        public const string SAVE_PAY_GATEWAY_CONFIG = @"
            INSERT INTO MPAYGATEWAYCONFIG (
                PAYGATEWAYCONFID,
                PAYGATEWAYID,
                MERCHANTID,
                WEBHOOKURL,
                APIKEYIDVAULTKEY,
                APISECRETVAULTKEY,
                WEBHOOKSECRETVAULTKEY,
                GATEWAYMODE,
                ISDEFAULT,
                SUPPORTEDCURRENCY,
                REMARKS,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE,
                TENANTID
            ) VALUES (
                @PayGatewayConfId,
                @PayGatewayId,
                @MerchantId,
                @WebhookUrl,
                @ApiKeyIdVaultKey,
                @ApiSecretVaultKey,
                @WebhookSecretVaultKey,
                @GatewayMode,
                @IsDefault,
                @SupportedCurrency,
                @Remarks,
                @Version,
                @Status,
                @SortOrder,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn,
                @SourceType,
                @TenantId
            )";

        public const string UPDATE_PAY_GATEWAY_CONFIG = @"
            UPDATE MPAYGATEWAYCONFIG SET
                PAYGATEWAYID            = @PayGatewayId,
                MERCHANTID              = @MerchantId,
                WEBHOOKURL              = @WebhookUrl,
                APIKEYIDVAULTKEY        = @ApiKeyIdVaultKey,
                APISECRETVAULTKEY       = @ApiSecretVaultKey,
                WEBHOOKSECRETVAULTKEY   = @WebhookSecretVaultKey,
                GATEWAYMODE             = @GatewayMode,
                ISDEFAULT               = @IsDefault,
                SUPPORTEDCURRENCY       = @SupportedCurrency,
                REMARKS                 = @Remarks,
                VERSION                 = @Version,
                STATUS                  = @Status,
                SORTORDER               = @SortOrder,
                MODIFIEDBYID            = @ModifiedById,
                MODIFIEDON              = @ModifiedOn,
                SOURCETYPE              = @SourceType
            WHERE PAYGATEWAYCONFID = @PayGatewayConfId
              AND TENANTID = @TenantId";
    }
}