namespace AMPDAL.Query.API
{
    public static class APIQB
    {
        // =========================
        // GET SINGLE API
        // =========================
        // =========================================================
        // GET SINGLE API
        // =========================================================
        public const string GET_API = @"
SELECT APIID            AS ApiId,
       TENANTID         AS TenantId,
       APIKEY           AS ApiKey,
       APINAME          AS ApiName,
       DESCRIPTION      AS Description,
       APIKIND          AS ApiKind,
       APITYPE          AS ApiType,
       CRITICALITY      AS Criticality,
       LIFECYCLESTATUS  AS LifecycleStatus,
       VISIBILITYLEVEL  AS VisibilityLevel,
       APIPROVIDERTYPE  AS ApiProviderType,
       EXPOSURETYPE     AS ExposureType,
       ISHEADLESSENABLED AS IsHeadlessEnabled,
       OWNERTEAM        AS OwnerTeam,
       BUSINESSOWNER    AS BusinessOwner,
       RUNTIMETYPE      AS RuntimeType,
       TECHNOLOGY       AS Technology,
       REPOURL          AS RepoUrl,
       PIPELINEREF      AS PipelineRef,
       PROVIDERID       AS ProviderId,
       CATEGORYID       AS CategoryId,
       APIDOMAINID      AS ApiDomainId,
       GEOGRAPHY        AS Geography,
       REGULATORYIMPACT AS RegulatoryImpact,
       RISKRATING       AS RiskRating,
       SLAREFERENCE     AS SlaReference,
       REMARKS          AS Remarks,

       ABOUTAPI         AS AboutApi,
       KEYFEATURES      AS KeyFeatures,
       USECASES         AS UseCases,
       QUICKSTART       AS QuickStart,

       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   MAPI
WHERE  APIID = @ApiId
AND    TENANTID = @TenantId";

        // =========================================================
        // GET LIST
        // =========================================================
        public const string GET_API_LIST = @"
SELECT APIID AS ApiId,
       TENANTID AS TenantId,
       APIKEY AS ApiKey,
       APINAME AS ApiName,
       DESCRIPTION AS Description,
       APIKIND AS ApiKind,
       APITYPE AS ApiType,
       CRITICALITY AS Criticality,
       LIFECYCLESTATUS AS LifecycleStatus,
       VISIBILITYLEVEL AS VisibilityLevel,
       APIPROVIDERTYPE AS ApiProviderType,
       EXPOSURETYPE AS ExposureType,
       ISHEADLESSENABLED AS IsHeadlessEnabled,
       OWNERTEAM AS OwnerTeam,
       BUSINESSOWNER AS BusinessOwner,
       RUNTIMETYPE AS RuntimeType,
       TECHNOLOGY AS Technology,
       REPOURL AS RepoUrl,
       PIPELINEREF AS PipelineRef,
       PROVIDERID AS ProviderId,
       CATEGORYID AS CategoryId,
       APIDOMAINID AS ApiDomainId,
       GEOGRAPHY AS Geography,
       REGULATORYIMPACT AS RegulatoryImpact,
       RISKRATING AS RiskRating,
       SLAREFERENCE AS SlaReference,
       REMARKS AS Remarks,
       ABOUTAPI AS AboutApi,
       KEYFEATURES AS KeyFeatures,
       USECASES AS UseCases,
       QUICKSTART AS QuickStart,
       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 MAPI
WHERE TENANTID = @TenantId
ORDER BY APINAME";

        public const string GET_API_SELECT_LIST = @"SELECT APIID AS ApiId,
       TENANTID AS TenantId,
       APINAME AS ApiName,
       DESCRIPTION AS Description,
       APIKIND AS ApiKind,
       APITYPE AS ApiType
FROM MAPI
where TENANTID = @TenantId";

        // =========================================================
        // PAGED LIST (MISSING FIXED)
        // =========================================================
        public const string GET_API_LIST_PAGED = @"
SELECT APIID AS ApiId,
       TENANTID AS TenantId,
       APIKEY AS ApiKey,
       APINAME AS ApiName,
       DESCRIPTION AS Description,
       APIKIND AS ApiKind,
       APITYPE AS ApiType,
       CRITICALITY AS Criticality,
       LIFECYCLESTATUS AS LifecycleStatus,
       VISIBILITYLEVEL AS VisibilityLevel,
       APIPROVIDERTYPE AS ApiProviderType,
       EXPOSURETYPE AS ExposureType,
       ISHEADLESSENABLED AS IsHeadlessEnabled,
       OWNERTEAM AS OwnerTeam,
       BUSINESSOWNER AS BusinessOwner,
       RUNTIMETYPE AS RuntimeType,
       TECHNOLOGY AS Technology,
       REPOURL AS RepoUrl,
       PIPELINEREF AS PipelineRef,
       PROVIDERID AS ProviderId,
       CATEGORYID AS CategoryId,
       APIDOMAINID AS ApiDomainId,
       GEOGRAPHY AS Geography,
       REGULATORYIMPACT AS RegulatoryImpact,
       RISKRATING AS RiskRating,
       SLAREFERENCE AS SlaReference,
       REMARKS AS Remarks,
       ABOUTAPI AS AboutApi,
       KEYFEATURES AS KeyFeatures,
       USECASES AS UseCases,
       QUICKSTART AS QuickStart,
       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 MAPI
WHERE TENANTID = @TenantId
ORDER BY APINAME
OFFSET (@FirstNumber - 1) ROWS
FETCH NEXT @MaxResult ROWS ONLY";

        // =========================================================
        // GET ALL LIST (MISSING FIXED)
        // =========================================================
        public const string GET_API_LIST_ALL = @"
SELECT APIID AS ApiId,
       TENANTID AS TenantId,
       APIKEY AS ApiKey,
       APINAME AS ApiName,
       DESCRIPTION AS Description,
       APIKIND AS ApiKind,
       APITYPE AS ApiType,
       CRITICALITY AS Criticality,
       LIFECYCLESTATUS AS LifecycleStatus,
       VISIBILITYLEVEL AS VisibilityLevel,
       APIPROVIDERTYPE AS ApiProviderType,
       EXPOSURETYPE AS ExposureType,
       ISHEADLESSENABLED AS IsHeadlessEnabled,
       OWNERTEAM AS OwnerTeam,
       BUSINESSOWNER AS BusinessOwner,
       RUNTIMETYPE AS RuntimeType,
       TECHNOLOGY AS Technology,
       REPOURL AS RepoUrl,
       PIPELINEREF AS PipelineRef,
       PROVIDERID AS ProviderId,
       CATEGORYID AS CategoryId,
       APIDOMAINID AS ApiDomainId,
       GEOGRAPHY AS Geography,
       REGULATORYIMPACT AS RegulatoryImpact,
       RISKRATING AS RiskRating,
       SLAREFERENCE AS SlaReference,
       REMARKS AS Remarks,
       ABOUTAPI AS AboutApi,
       KEYFEATURES AS KeyFeatures,
       USECASES AS UseCases,
       QUICKSTART AS QuickStart,
       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 MAPI
WHERE TENANTID = @TenantId
ORDER BY APINAME";

        // =========================
        // POSTGRES VERSION
        // =========================
        public const string PG_GET_API = @"
        SELECT apiid            AS ""ApiId"",
               tenantid         AS ""TenantId"",
               apikey           AS ""ApiKey"",
               apiname          AS ""ApiName"",
               description      AS ""Description"",
               apikind          AS ""ApiKind"",
               apitype          AS ""ApiType"",
               criticality      AS ""Criticality"",
               lifecyclestatus  AS ""LifecycleStatus"",
               visibilitylevel  AS ""VisibilityLevel"",
               apiprovidertype  AS ""ApiProviderType"",
               exposuretype     AS ""ExposureType"",
               isheadlessenabled AS ""IsHeadlessEnabled"",
               ownerteam        AS ""OwnerTeam"",
               businessowner    AS ""BusinessOwner"",
               runtimetype      AS ""RuntimeType"",
               technology       AS ""Technology"",
               repourl          AS ""RepoUrl"",
               pipelineref      AS ""PipelineRef"",
               providerid       AS ""ProviderId"",
               categoryid       AS ""CategoryId"",
               geography        AS ""Geography"",
               regulatoryimpact AS ""RegulatoryImpact"",
               riskrating       AS ""RiskRating"",
               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   mapi
        WHERE  apiid = @ApiId";

        // =========================
        // GET LIST
        // =========================

        // =========================
        // POSTGRES LIST
        // =========================
        public const string PG_GET_API_LIST = @"
        SELECT apiid            AS ""ApiId"",
               tenantid         AS ""TenantId"",
               apikey           AS ""ApiKey"",
               apiname          AS ""ApiName"",
               description      AS ""Description"",
               apikind          AS ""ApiKind"",
               apitype          AS ""ApiType"",
               criticality      AS ""Criticality"",
               lifecyclestatus  AS ""LifecycleStatus"",
               visibilitylevel  AS ""VisibilityLevel"",
               apiprovidertype  AS ""ApiProviderType"",
               exposuretype     AS ""ExposureType"",
               isheadlessenabled AS ""IsHeadlessEnabled"",
               ownerteam        AS ""OwnerTeam"",
               businessowner    AS ""BusinessOwner"",
               runtimetype      AS ""RuntimeType"",
               technology       AS ""Technology"",
               repourl          AS ""RepoUrl"",
               pipelineref      AS ""PipelineRef"",
               providerid       AS ""ProviderId"",
               categoryid       AS ""CategoryId"",
               geography        AS ""Geography"",
               regulatoryimpact AS ""RegulatoryImpact"",
               riskrating       AS ""RiskRating"",
               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   mapi
        WHERE  tenantid = @TenantId
        ORDER BY apiname";

        // =========================
        // INSERT
        // =========================
        public const string SAVE_API = @"
INSERT INTO MAPI
(
 APIID, TENANTID, APIKEY, APINAME, DESCRIPTION, APIKIND, APITYPE,
 CRITICALITY, LIFECYCLESTATUS, VISIBILITYLEVEL, APIPROVIDERTYPE,
 EXPOSURETYPE, ISHEADLESSENABLED, OWNERTEAM, BUSINESSOWNER,
 RUNTIMETYPE, TECHNOLOGY, REPOURL, PIPELINEREF, PROVIDERID,
 CATEGORYID, APIDOMAINID,
 GEOGRAPHY, REGULATORYIMPACT, RISKRATING, SLAREFERENCE,
 REMARKS, ABOUTAPI, KEYFEATURES, USECASES, QUICKSTART,
 SORTORDER, STATUS, VERSION, SOURCETYPE,
 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
OUTPUT INSERTED.APIID
VALUES
(
 @ApiId, @TenantId, @ApiKey, @ApiName, @Description, @ApiKind, @ApiType,
 @Criticality, @LifecycleStatus, @VisibilityLevel, @ApiProviderType,
 @ExposureType, @IsHeadlessEnabled, @OwnerTeam, @BusinessOwner,
 @RuntimeType, @Technology, @RepoUrl, @PipelineRef, @ProviderId,
 @CategoryId, @ApiDomainId,
 @Geography, @RegulatoryImpact, @RiskRating, @SlaReference,
 @Remarks, @AboutApi, @KeyFeatures, @UseCases, @QuickStart,
 @SortOrder, @Status, @Version, 5,
 @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE()
)";

        // =========================
        // POSTGRES INSERT
        // =========================
        public const string PG_SAVE_API = @"
INSERT INTO mapi
       (apiid, tenantid, apikey, apiname, description, apikind, apitype,
        criticality, lifecyclestatus, visibilitylevel, apiprovidertype,
        exposuretype, isheadlessenabled, ownerteam, businessowner,
        runtimetype, technology, repourl, pipelineref, providerid,
        categoryid, geography, regulatoryimpact, riskrating, slareference,
        remarks, sortorder, status, version, sourcetype,
        createdbyid, createdon, modifiedbyid, modifiedon)
VALUES (@ApiId, @TenantId, @ApiKey, @ApiName, @Description, @ApiKind, @ApiType,
        @Criticality, @LifecycleStatus, @VisibilityLevel, @ApiProviderType,
        @ExposureType, @IsHeadlessEnabled, @OwnerTeam, @BusinessOwner,
        @RuntimeType, @Technology, @RepoUrl, @PipelineRef, @ProviderId,
        @CategoryId, @Geography, @RegulatoryImpact, @RiskRating, @SlaReference,
        @Remarks, @SortOrder, @Status, @Version, @SourceType,
        @CreatedById, NOW(), @ModifiedById, NOW())
RETURNING apiid";

        public const string UPDATE_API = @"
UPDATE MAPI
SET
    APIKEY = @ApiKey,
    APINAME = @ApiName,
    DESCRIPTION = @Description,
    APIKIND = @ApiKind,
    APITYPE = @ApiType,
APIDOMAINID = @ApiDomainId,
ABOUTAPI = @AboutApi,
KEYFEATURES = @KeyFeatures,
USECASES = @UseCases,
QUICKSTART = @QuickStart,
    CRITICALITY = @Criticality,
    LIFECYCLESTATUS = @LifecycleStatus,
    VISIBILITYLEVEL = @VisibilityLevel,
    APIPROVIDERTYPE = @ApiProviderType,
    EXPOSURETYPE = @ExposureType,
    ISHEADLESSENABLED = @IsHeadlessEnabled,
    OWNERTEAM = @OwnerTeam,
    BUSINESSOWNER = @BusinessOwner,
    RUNTIMETYPE = @RuntimeType,
    TECHNOLOGY = @Technology,
    REPOURL = @RepoUrl,
    PIPELINEREF = @PipelineRef,
    PROVIDERID = @ProviderId,
    CATEGORYID = @CategoryId,
    GEOGRAPHY = @Geography,
    REGULATORYIMPACT = @RegulatoryImpact,
    RISKRATING = @RiskRating,
    SLAREFERENCE = @SlaReference,
    REMARKS = @Remarks,
    SORTORDER = @SortOrder,
    STATUS = @Status,
    VERSION = @Version,
    SOURCETYPE = 5,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON = GETUTCDATE()
WHERE
    APIID = @ApiId
AND TENANTID = @TenantId";

        public const string PG_UPDATE_API = @"
UPDATE mapi
SET
    apikey = @ApiKey,
    apiname = @ApiName,
    description = @Description,
    apikind = @ApiKind,
    apitype = @ApiType,
    criticality = @Criticality,
    lifecyclestatus = @LifecycleStatus,
    visibilitylevel = @VisibilityLevel,
    apiprovidertype = @ApiProviderType,
    exposuretype = @ExposureType,
    isheadlessenabled = @IsHeadlessEnabled,
    ownerteam = @OwnerTeam,
    businessowner = @BusinessOwner,
    runtimetype = @RuntimeType,
    technology = @Technology,
    repourl = @RepoUrl,
    pipelineref = @PipelineRef,
    providerid = @ProviderId,
    categoryid = @CategoryId,
    geography = @Geography,
    regulatoryimpact = @RegulatoryImpact,
    riskrating = @RiskRating,
    slareference = @SlaReference,
    remarks = @Remarks,
    sortorder = @SortOrder,
    status = @Status,
    version = @Version,
    sourcetype = @SourceType,
    modifiedbyid = @ModifiedById,
    modifiedon = NOW()
WHERE
    apiid = @ApiId
AND tenantid = @TenantId";


        // =========================
        // UPDATE LIFECYCLE
        // =========================
        public const string UPDATE_LIFECYCLE = @"
        UPDATE MAPI
        SET    LIFECYCLESTATUS = @LifecycleStatus,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON = GETUTCDATE()
        WHERE  APIID = @ApiId
        AND    TENANTID = @TenantId";

        public const string PG_UPDATE_LIFECYCLE = @"
        UPDATE mapi
        SET    lifecyclestatus = @LifecycleStatus,
               modifiedbyid = @ModifiedById,
               modifiedon = NOW()
        WHERE  apiid = @ApiId
        AND    tenantid = @TenantId";

        public const string GET_API_COUNT = @"
SELECT COUNT(1)
FROM MAPI
WHERE TENANTID = @TenantId";

        public const string DELETE_API = @"
DELETE FROM MAPI
WHERE APIID = @ApiId
AND TENANTID = @TenantId";
    }
}