using System.Runtime.Intrinsics.Arm;

namespace AmpDAL.Query.ApiCapability;

public static class ApiCapabilityQB
{
    // =====================================================
    // GET SINGLE CAPABILITY
    // =====================================================

    public const string GET_CAPABILITY = @"
        SELECT APICAPABILITYID AS ApiCapabilityId,
               TENANTID        AS TenantId,
               APIID           AS ApiId,
               APIDOMAINID     AS ApiDomainId,
               CAPABILITYNAME  AS CapabilityName,
               DESCRIPTION     AS Description,
               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   MAPICAPABILITY
        WHERE  APICAPABILITYID = @ApiCapabilityId";


    public const string PG_GET_CAPABILITY = @"
        SELECT apicapabilityid AS ApiCapabilityId,
               tenantid        AS TenantId,
               apiid           AS ApiId,
               apidomainid     AS ApiDomainId,
               capabilityname  AS CapabilityName,
               description     AS Description,
               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   mapicapability
        WHERE  apicapabilityid = @ApiCapabilityId
        AND    tenantid        = @TenantId";


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

    public const string GET_CAPABILITY_LIST = @"
        SELECT APICAPABILITYID AS ApiCapabilityId,
       TENANTID        AS TenantId,
       APIID           AS ApiId,
       APIDOMAINID     AS ApiDomainId,
       CAPABILITYNAME  AS CapabilityName,
       DESCRIPTION     AS Description,
       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   MAPICAPABILITY
WHERE  TENANTID = @TenantId
ORDER BY CAPABILITYNAME";


    public const string PG_GET_CAPABILITY_LIST = @"
        SELECT apicapabilityid AS ApiCapabilityId,
               tenantid        AS TenantId,
               apiid           AS ApiId,
               apidomainid     AS ApiDomainId,
               capabilityname  AS CapabilityName,
               description     AS Description,
               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   mapicapability
        WHERE  tenantid    = @TenantId
        AND    apidomainid = @ApiDomainId
        ORDER BY capabilityname";


    // =====================================================
    // SAVE CAPABILITY
    // =====================================================

public const string SAVE_CAPABILITY = @"
INSERT INTO MAPICAPABILITY
(
    APICAPABILITYID,   -- Auto-number provided from code
    TENANTID,
    APIID,
    APIDOMAINID,
    CAPABILITYNAME,
    DESCRIPTION,
    REMARKS,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
OUTPUT INSERTED.APICAPABILITYID
VALUES
(
    @ApiCapabilityId,  -- Auto-number
    @TenantId,
    @ApiId,
    @ApiDomainId,
    @CapabilityName,
    @Description,
    @Remarks,
    @SortOrder,
    @Status,
    @Version,
    @SourceType,
    @CreatedById,
    GETDATE(),
    @ModifiedById,
    GETDATE()
)";

public const string PG_SAVE_CAPABILITY = @"
INSERT INTO mapicapability
(
    apicapabilityid,   -- Auto-number provided from code
    tenantid,
    apiid,
    apidomainid,
    capabilityname,
    description,
    remarks,
    sortorder,
    status,
    version,
    sourcetype,
    createdbyid,
    createdon,
    modifiedbyid,
    modifiedon
)
VALUES
(
    @ApiCapabilityId,
    @TenantId,
    @ApiId,
    @ApiDomainId,
    @CapabilityName,
    @Description,
    @Remarks,
    @SortOrder,
    @Status,
    @Version,
    @SourceType,
    @CreatedById,
    NOW(),
    @ModifiedById,
    NOW()
)
RETURNING apicapabilityid;
";

    public const string UPDATE_CAPABILITY = @"
UPDATE MAPICAPABILITY
SET
    APIID          = @ApiId,
    APIDOMAINID    = @ApiDomainId,
    CAPABILITYNAME = @CapabilityName,
    DESCRIPTION    = @Description,
    REMARKS        = @Remarks,
    SORTORDER      = @SortOrder,
    STATUS         = @Status,
    VERSION        = @Version,
    SOURCETYPE     = @SourceType,
    MODIFIEDBYID   = @ModifiedById,
    MODIFIEDON     = GETDATE()
WHERE APICAPABILITYID = @ApiCapabilityId;

SELECT @ApiCapabilityId AS APICAPABILITYID;
";


public const string PG_UPDATE_CAPABILITY = @"
UPDATE mapicapability
SET
    apiid          = @ApiId,
    apidomainid    = @ApiDomainId,
    capabilityname = @CapabilityName,
    description    = @Description,
    remarks        = @Remarks,
    sortorder      = @SortOrder,
    status         = @Status,
    version        = @Version,
    sourcetype     = @SourceType,
    modifiedbyid   = @ModifiedById,
    modifiedon     = NOW()
WHERE apicapabilityid = @ApiCapabilityId
RETURNING apicapabilityid;
";

    // =====================================================
    // SAVE CAPABILITY LINK
    // =====================================================

    public const string SAVE_CAPABILITY_LINK = @"
IF NOT EXISTS
(
    SELECT 1
    FROM   MAPICAPABILITYLINK
    WHERE  APIID           = @ApiId
      AND  APICAPABILITYID = @ApiCapabilityId
)
BEGIN
    INSERT INTO MAPICAPABILITYLINK
    (
        APICAPABILITYLINKID,  -- Auto-number from code
        TENANTID,
        APIID,
        APICAPABILITYID,
        SORTORDER,
        STATUS,
        VERSION,
        SOURCETYPE,
        CREATEDBYID,
        CREATEDON,
        MODIFIEDBYID,
        MODIFIEDON
    )
    VALUES
    (
        @ApiCapabilityLinkId,
        @TenantId,
        @ApiId,
        @ApiCapabilityId,
        @SortOrder,
        @Status,
        @Version,
        @SourceType,
        @CreatedById,
        GETDATE(),
        @ModifiedById,
        GETDATE()
    )
END
";


    public const string PG_SAVE_CAPABILITY_LINK = @"
INSERT INTO mapicapabilitylink
(
    apicapabilitylinkid,  -- Auto-number from code
    tenantid,
    apiid,
    apicapabilityid,
    sortorder,
    status,
    version,
    sourcetype,
    createdbyid,
    createdon,
    modifiedbyid,
    modifiedon
)
VALUES
(
    @ApiCapabilityLinkId,
    @TenantId,
    @ApiId,
    @ApiCapabilityId,
    @SortOrder,
    @Status,
    @Version,
    @SourceType,
    @CreatedById,
    NOW(),
    @ModifiedById,
    NOW()
)
ON CONFLICT (apiid, apicapabilityid) DO NOTHING
RETURNING apicapabilitylinkid;
";

    // SQL Server UPDATE
    public const string UPDATE_CAPABILITY_LINK = @"
UPDATE MAPICAPABILITYLINK
SET
    SortOrder      = @SortOrder,
    Status         = @Status,
    Version        = @Version,
    SourceType     = @SourceType,
    ModifiedById   = @ModifiedById,
    ModifiedOn     = GETDATE()
WHERE ApiCapabilityLinkId = @ApiCapabilityLinkId;
";

    // PostgreSQL UPDATE
    public const string PG_UPDATE_CAPABILITY_LINK = @"
UPDATE mapicapabilitylink
SET
    sortorder      = @SortOrder,
    status         = @Status,
    version        = @Version,
    sourcetype     = @SourceType,
    modifiedbyid   = @ModifiedById,
    modifiedon     = NOW()
WHERE apicapabilitylinkid = @ApiCapabilityLinkId
RETURNING apicapabilitylinkid;
";

    public const string GET_CAPABILITY_SELECT_LIST = @"
        SELECT APICAPABILITYID AS ApiCapabilityId,
               TENANTID        AS TenantId,
               APIID           AS ApiId,
               CAPABILITYNAME  AS CapabilityName,
               STATUS          AS Status
        FROM   MAPICAPABILITY
        WHERE  TENANTID = @TenantId
        ORDER BY CAPABILITYNAME";

    public const string PG_GET_CAPABILITY_SELECT_LIST = @"
        SELECT apicapabilityid AS ApiCapabilityId,
               tenantid        AS TenantId,
               apiid           AS ApiId,
               capabilityname  AS CapabilityName,
               status          AS Status
        FROM   mapicapability
        WHERE  tenantid = @TenantId
        ORDER BY capabilityname";

    public const string GET_CAPABILITY_LIST_PAGED = @"
        SELECT APICAPABILITYID AS ApiCapabilityId,
               TENANTID        AS TenantId,
               APIID           AS ApiId,
               APIDOMAINID     AS ApiDomainId,
               CAPABILITYNAME  AS CapabilityName,
               DESCRIPTION     AS Description,
               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   MAPICAPABILITY
        WHERE  TENANTID = @TenantId
        ORDER BY CAPABILITYNAME
        OFFSET (@FirstNumber - 1) ROWS
        FETCH NEXT @MaxResult ROWS ONLY";

    public const string DELETE_CAPABILITY = @"
        DELETE FROM MAPICAPABILITY
        WHERE APICAPABILITYID = @ApiCapabilityId
        AND   TENANTID        = @TenantId";

    public const string PG_DELETE_CAPABILITY = @"
        DELETE FROM mapicapability
        WHERE apicapabilityid = @ApiCapabilityId
        AND   tenantid        = @TenantId";
}