namespace AmpDAL.Query.ApiExposurePolicy
{
    public static class ApiExposurePolicyQB
    {
        // =====================================================
        // GET EXPOSURE POLICY
        // =====================================================
        public const string GET_EXPOSURE_POLICY = @"
            SELECT APIEXPOSUREPOLICYID AS ApiExposurePolicyId,
                   TENANTID            AS TenantId,
                   APIID               AS ApiId,
                   ALLOWPARTNERACCESS  AS AllowPartnerAccess,
                   ALLOWPUBLICACCESS   AS AllowPublicAccess,
                   BILLINGCATEGORY     AS BillingCategory,
                   SLAREFERENCE        AS SLAReference,
                   REMARKS             AS Remarks,
                   SORTORDER           AS SortOrder,
                   STATUS              AS Status,
                   VERSION             AS Version,
                   SOURCETYPE          AS SourceType,
                   CREATEDBYID         AS CreatedById,
                   CREATEDON           AS CreatedOn,
                   MODIFIEDBYID        AS ModifiedById,
                   MODIFIEDON          AS ModifiedOn
            FROM   MAPIEXPOSUREPOLICY
            WHERE  APIID = @ApiId";

        public const string PG_GET_EXPOSURE_POLICY = @"
            SELECT apiexposurepolicyid AS ApiExposurePolicyId,
                   tenantid            AS TenantId,
                   apiid               AS ApiId,
                   allowpartneraccess  AS AllowPartnerAccess,
                   allowpublicaccess   AS AllowPublicAccess,
                   billingcategory     AS BillingCategory,
                   slareference        AS SLAReference,
                   remarks             AS Remarks,
                   sortorder           AS SortOrder,
                   status              AS Status,
                   version             AS Version,
                   sourcetype          AS SourceType,
                   createdbyid         AS CreatedById,
                   createdon           AS CreatedOn,
                   modifiedbyid        AS ModifiedById,
                   modifiedon          AS ModifiedOn
            FROM   mapiexposurepolicy
            WHERE  apiid = @ApiId";

        // =====================================================
        // UPSERT (SQL SERVER WITH MANUAL PK)
        // =====================================================
        public const string SAVE_EXPOSURE_POLICY = @"
            ;MERGE MAPIEXPOSUREPOLICY AS target
            USING (SELECT @ApiId AS ApiId, @TenantId AS TenantId) AS source
            ON target.APIID = source.ApiId AND target.TENANTID = source.TenantId

            WHEN MATCHED THEN
                UPDATE SET
                    ALLOWPARTNERACCESS = @AllowPartnerAccess,
                    ALLOWPUBLICACCESS  = @AllowPublicAccess,
                    BILLINGCATEGORY    = @BillingCategory,
                    SLAREFERENCE       = @SLAReference,
                    REMARKS            = @Remarks,
                    SORTORDER          = @SortOrder,
                    [STATUS]           = @Status,
                    [VERSION]          = @Version,
                    SOURCETYPE         = @SourceType,
                    MODIFIEDBYID       = @ModifiedById,
                    MODIFIEDON         = @ModifiedOn

            WHEN NOT MATCHED THEN
                INSERT (
                    APIEXPOSUREPOLICYID,  -- include manual PK
                    TENANTID,
                    APIID,
                    ALLOWPARTNERACCESS,
                    ALLOWPUBLICACCESS,
                    BILLINGCATEGORY,
                    SLAREFERENCE,
                    REMARKS,
                    SORTORDER,
                    [STATUS],
                    [VERSION],
                    SOURCETYPE,
                    CREATEDBYID,
                    CREATEDON,
                    MODIFIEDBYID,
                    MODIFIEDON
                )
                VALUES (
                    @ApiExposurePolicyId, -- manual PK passed from BLL
                    @TenantId,
                    @ApiId,
                    @AllowPartnerAccess,
                    @AllowPublicAccess,
                    @BillingCategory,
                    @SLAReference,
                    @Remarks,
                    @SortOrder,
                    @Status,
                    @Version,
                    @SourceType,
                    @CreatedById,
                    @CreatedOn,
                    @ModifiedById,
                    @ModifiedOn
                )
            OUTPUT inserted.APIEXPOSUREPOLICYID;";

        // =====================================================
        // UPSERT (POSTGRESQL)
        // =====================================================
        public const string PG_SAVE_EXPOSURE_POLICY = @"
            INSERT INTO mapiexposurepolicy
            (
                apiexposurepolicyid,
                tenantid,
                apiid,
                allowpartneraccess,
                allowpublicaccess,
                billingcategory,
                slareference,
                remarks,
                sortorder,
                status,
                version,
                sourcetype,
                createdbyid,
                createdon,
                modifiedbyid,
                modifiedon
            )
            VALUES
            (
                @ApiExposurePolicyId,
                @TenantId,
                @ApiId,
                @AllowPartnerAccess,
                @AllowPublicAccess,
                @BillingCategory,
                @SLAReference,
                @Remarks,
                @SortOrder,
                @Status,
                @Version,
                @SourceType,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn
            )
            ON CONFLICT (apiid, tenantid)
            DO UPDATE SET
                allowpartneraccess = EXCLUDED.allowpartneraccess,
                allowpublicaccess  = EXCLUDED.allowpublicaccess,
                billingcategory    = EXCLUDED.billingcategory,
                slareference       = EXCLUDED.slareference,
                remarks            = EXCLUDED.remarks,
                sortorder          = EXCLUDED.sortorder,
                status             = EXCLUDED.status,
                version            = EXCLUDED.version,
                sourcetype         = EXCLUDED.sourcetype,
                modifiedbyid       = EXCLUDED.modifiedbyid,
                modifiedon         = EXCLUDED.modifiedon
            RETURNING apiexposurepolicyid;";

        // =====================================================
        // UPDATE QUERIES
        // =====================================================
        public const string UPDATE_EXPOSURE_POLICY = @"
            UPDATE MAPIEXPOSUREPOLICY
SET
    ALLOWPARTNERACCESS = @AllowPartnerAccess,
    ALLOWPUBLICACCESS  = @AllowPublicAccess,
    BILLINGCATEGORY    = @BillingCategory,
    SLAREFERENCE       = @SLAReference,
    REMARKS            = @Remarks,
    SORTORDER          = @SortOrder,
    [STATUS]           = @Status,
    [VERSION]          = @Version,
    SOURCETYPE         = @SourceType,
    MODIFIEDBYID       = @ModifiedById,
    MODIFIEDON         = @ModifiedOn
WHERE APIEXPOSUREPOLICYID = @ApiExposurePolicyId;

SELECT @ApiExposurePolicyId AS ApiExposurePolicyId;";

        public const string PG_UPDATE_EXPOSURE_POLICY = @"
            UPDATE mapiexposurepolicy
SET
    allowpartneraccess = @AllowPartnerAccess,
    allowpublicaccess  = @AllowPublicAccess,
    billingcategory    = @BillingCategory,
    slareference       = @SLAReference,
    remarks            = @Remarks,
    sortorder          = @SortOrder,
    status             = @Status,
    version            = @Version,
    sourcetype         = @SourceType,
    modifiedbyid       = @ModifiedById,
    modifiedon         = @ModifiedOn
WHERE apiexposurepolicyid = @ApiExposurePolicyId
RETURNING apiexposurepolicyid;";

        public const string GET_EXPOSURE_POLICY_SELECT_LIST = @"
            SELECT APIEXPOSUREPOLICYID AS ApiExposurePolicyId,
                   TENANTID            AS TenantId,
                   APIID               AS ApiId,
                   ALLOWPARTNERACCESS  AS AllowPartnerAccess,
                   ALLOWPUBLICACCESS   AS AllowPublicAccess
            FROM   MAPIEXPOSUREPOLICY
            WHERE  TENANTID = @TenantId
            ORDER BY APIID";

        public const string PG_GET_EXPOSURE_POLICY_SELECT_LIST = @"
            SELECT apiexposurepolicyid AS ApiExposurePolicyId,
                   tenantid            AS TenantId,
                   apiid               AS ApiId,
                   allowpartneraccess  AS AllowPartnerAccess,
                   allowpublicaccess   AS AllowPublicAccess
            FROM   mapiexposurepolicy
            WHERE  tenantid = @TenantId
            ORDER BY apiid";

        public const string GET_EXPOSURE_POLICY_LIST = @"
            SELECT APIEXPOSUREPOLICYID AS ApiExposurePolicyId,
                   TENANTID            AS TenantId,
                   APIID               AS ApiId,
                   ALLOWPARTNERACCESS  AS AllowPartnerAccess,
                   ALLOWPUBLICACCESS   AS AllowPublicAccess,
                   BILLINGCATEGORY     AS BillingCategory,
                   SLAREFERENCE        AS SLAReference,
                   REMARKS             AS Remarks,
                   SORTORDER           AS SortOrder,
                   STATUS              AS Status,
                   VERSION             AS Version,
                   SOURCETYPE          AS SourceType,
                   CREATEDBYID         AS CreatedById,
                   CREATEDON           AS CreatedOn,
                   MODIFIEDBYID        AS ModifiedById,
                   MODIFIEDON          AS ModifiedOn
            FROM   MAPIEXPOSUREPOLICY
            WHERE  TENANTID = @TenantId
            ORDER BY APIID";

        public const string DELETE_EXPOSURE_POLICY = @"
            DELETE FROM MAPIEXPOSUREPOLICY
            WHERE APIEXPOSUREPOLICYID = @ApiExposurePolicyId
            AND   TENANTID            = @TenantId";

        public const string PG_DELETE_EXPOSURE_POLICY = @"
            DELETE FROM mapiexposurepolicy
            WHERE apiexposurepolicyid = @ApiExposurePolicyId
            AND   tenantid            = @TenantId";
    }
}