namespace AMPDAL.Query.ApiRequest
{
    public static class ApiRequestQB
    {
        // Required indexes:
        // IX_MAPIREQUEST_TENANTID_STATUS on (TENANTID, REQUESTSTATUS)
        // IX_MAPIREQUEST_REQUESTEDBYID
        // IX_MAPIREQUEST_LINKEDAPIID

        // =========================================================
        // GET SINGLE REQUEST
        // =========================================================
        public const string GET_REQUEST = @"
SELECT APIREQUESTID        AS ApiRequestId,
       TENANTID            AS TenantId,
       REQUESTCODE         AS RequestCode,
       REQUESTTITLE        AS RequestTitle,
       REQUESTDESCRIPTION  AS RequestDescription,
       BUSINESSJUSTIFICATION AS BusinessJustification,
       USECASES            AS UseCases,
       INTENTKEYWORDS      AS IntentKeywords,
       REQUESTEDBYID       AS RequestedById,
       REQUESTEDON         AS RequestedOn,
       TARGETDOMAINID      AS TargetDomainId,
       PROPOSEDAPITYPE     AS ProposedApiType,
       PROPOSEDAPIKIND     AS ProposedApiKind,
       PRIORITY            AS Priority,
       REQUESTSTATUS       AS RequestStatus,
       ASSIGNEDTOID        AS AssignedToId,
       LINKEDAPIID         AS LinkedApiId,
       LINKEDAPIVERSIONID  AS LinkedApiVersionId,
       TARGETDELIVERYDATE  AS TargetDeliveryDate,
       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   MAPIREQUEST
WHERE  APIREQUESTID = @ApiRequestId
AND    TENANTID     = @TenantId";

        // =========================================================
        // GET LIST (ALL)
        // =========================================================
        public const string GET_REQUEST_LIST = @"
SELECT APIREQUESTID        AS ApiRequestId,
       TENANTID            AS TenantId,
       REQUESTCODE         AS RequestCode,
       REQUESTTITLE        AS RequestTitle,
       REQUESTDESCRIPTION  AS RequestDescription,
       BUSINESSJUSTIFICATION AS BusinessJustification,
       USECASES            AS UseCases,
       INTENTKEYWORDS      AS IntentKeywords,
       REQUESTEDBYID       AS RequestedById,
       REQUESTEDON         AS RequestedOn,
       TARGETDOMAINID      AS TargetDomainId,
       PROPOSEDAPITYPE     AS ProposedApiType,
       PROPOSEDAPIKIND     AS ProposedApiKind,
       PRIORITY            AS Priority,
       REQUESTSTATUS       AS RequestStatus,
       ASSIGNEDTOID        AS AssignedToId,
       LINKEDAPIID         AS LinkedApiId,
       LINKEDAPIVERSIONID  AS LinkedApiVersionId,
       TARGETDELIVERYDATE  AS TargetDeliveryDate,
       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   MAPIREQUEST
WHERE  TENANTID = @TenantId
ORDER  BY REQUESTEDON DESC";

        // =========================================================
        // GET LIST PAGED
        // =========================================================
        public const string GET_REQUEST_LIST_PAGED = @"
SELECT APIREQUESTID        AS ApiRequestId,
       TENANTID            AS TenantId,
       REQUESTCODE         AS RequestCode,
       REQUESTTITLE        AS RequestTitle,
       REQUESTDESCRIPTION  AS RequestDescription,
       BUSINESSJUSTIFICATION AS BusinessJustification,
       USECASES            AS UseCases,
       INTENTKEYWORDS      AS IntentKeywords,
       REQUESTEDBYID       AS RequestedById,
       REQUESTEDON         AS RequestedOn,
       TARGETDOMAINID      AS TargetDomainId,
       PROPOSEDAPITYPE     AS ProposedApiType,
       PROPOSEDAPIKIND     AS ProposedApiKind,
       PRIORITY            AS Priority,
       REQUESTSTATUS       AS RequestStatus,
       ASSIGNEDTOID        AS AssignedToId,
       LINKEDAPIID         AS LinkedApiId,
       LINKEDAPIVERSIONID  AS LinkedApiVersionId,
       TARGETDELIVERYDATE  AS TargetDeliveryDate,
       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   MAPIREQUEST
WHERE  TENANTID = @TenantId
ORDER  BY REQUESTEDON DESC
OFFSET (@FirstNumber - 1) ROWS
FETCH  NEXT @MaxResult ROWS ONLY";

        // =========================================================
        // GET SELECT LIST (DROPDOWN)
        // =========================================================
        public const string GET_REQUEST_SELECT_LIST = @"
SELECT APIREQUESTID  AS ApiRequestId,
       TENANTID      AS TenantId,
       REQUESTCODE   AS RequestCode,
       REQUESTTITLE  AS RequestTitle,
       REQUESTSTATUS AS RequestStatus,
       PRIORITY      AS Priority
FROM   MAPIREQUEST
WHERE  TENANTID = @TenantId
AND    STATUS   <> 5
ORDER  BY REQUESTTITLE";

        // =========================================================
        // SAVE (INSERT)
        // =========================================================
        public const string SAVE_REQUEST = @"
INSERT INTO MAPIREQUEST (
    APIREQUESTID, TENANTID, REQUESTCODE, REQUESTTITLE,
    REQUESTDESCRIPTION, BUSINESSJUSTIFICATION, USECASES, INTENTKEYWORDS,
    REQUESTEDBYID, REQUESTEDON, TARGETDOMAINID,
    PROPOSEDAPITYPE, PROPOSEDAPIKIND, PRIORITY, REQUESTSTATUS,
    ASSIGNEDTOID, LINKEDAPIID, LINKEDAPIVERSIONID,
    TARGETDELIVERYDATE, REMARKS,
    SORTORDER, STATUS, VERSION, SOURCETYPE,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
) VALUES (
    @ApiRequestId, @TenantId, @RequestCode, @RequestTitle,
    @RequestDescription, @BusinessJustification, @UseCases, @IntentKeywords,
    @RequestedById, @RequestedOn, @TargetDomainId,
    @ProposedApiType, @ProposedApiKind, @Priority, @RequestStatus,
    @AssignedToId, @LinkedApiId, @LinkedApiVersionId,
    @TargetDeliveryDate, @Remarks,
    @SortOrder, @Status, @Version, @SourceType,
    @CreatedById, GETDATE(), @ModifiedById, GETDATE()
);
SELECT @ApiRequestId";

        // =========================================================
        // UPDATE
        // =========================================================
        public const string UPDATE_REQUEST = @"
UPDATE MAPIREQUEST SET
    REQUESTTITLE          = @RequestTitle,
    REQUESTDESCRIPTION    = @RequestDescription,
    BUSINESSJUSTIFICATION = @BusinessJustification,
    USECASES              = @UseCases,
    INTENTKEYWORDS        = @IntentKeywords,
    TARGETDOMAINID        = @TargetDomainId,
    PROPOSEDAPITYPE       = @ProposedApiType,
    PROPOSEDAPIKIND       = @ProposedApiKind,
    PRIORITY              = @Priority,
    ASSIGNEDTOID          = @AssignedToId,
    TARGETDELIVERYDATE    = @TargetDeliveryDate,
    REMARKS               = @Remarks,
    SORTORDER             = @SortOrder,
    STATUS                = @Status,
    MODIFIEDBYID          = @ModifiedById,
    MODIFIEDON            = GETDATE()
WHERE APIREQUESTID = @ApiRequestId
AND   TENANTID     = @TenantId";

        // =========================================================
        // UPDATE STATUS ONLY
        // =========================================================
        public const string UPDATE_REQUEST_STATUS = @"
UPDATE MAPIREQUEST SET
    REQUESTSTATUS = @RequestStatus,
    MODIFIEDBYID  = @ModifiedById,
    MODIFIEDON    = GETDATE()
WHERE APIREQUESTID = @ApiRequestId
AND   TENANTID     = @TenantId";

        // =========================================================
        // LINK API TO REQUEST (sets both LINKEDAPIID and LINKEDAPIVERSIONID)
        // =========================================================
        public const string LINK_API_TO_REQUEST = @"
UPDATE MAPIREQUEST SET
    LINKEDAPIID        = @LinkedApiId,
    LINKEDAPIVERSIONID = @LinkedApiVersionId,
    MODIFIEDBYID       = @ModifiedById,
    MODIFIEDON         = GETDATE()
WHERE APIREQUESTID = @ApiRequestId
AND   TENANTID     = @TenantId";

        // =========================================================
        // DELETE
        // =========================================================
        public const string DELETE_REQUEST = @"
UPDATE MAPIREQUEST SET
    STATUS       = 5,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = GETDATE()
WHERE APIREQUESTID = @ApiRequestId
AND   TENANTID     = @TenantId";

        // =========================================================
        // GATE CHECKS — cross-domain queries used by BLL gates
        // =========================================================

        // Count unresolved duplicates (gate: Accepted)
        public const string GET_PENDING_DUPLICATE_COUNT = @"
SELECT COUNT(1)
FROM   MAPIREQUESTDUPLICATECHECK
WHERE  SOURCEAPIREQUESTID = @ApiRequestId
AND    TENANTID           = @TenantId
AND    REVIEWSTATUS       = 0";

        // Count interfaces for linked API (gate: InDevelopment → DevReview)
        public const string GET_INTERFACE_COUNT = @"
SELECT COUNT(1)
FROM   MAPIINTERFACE
WHERE  APIID     = @ApiId
AND    TENANTID  = @TenantId
AND    STATUS    <> 5";

        // Count unparsed interfaces (gate: DevReview → QCTesting)
        public const string GET_UNPARSED_INTERFACE_COUNT = @"
SELECT COUNT(1)
FROM   MAPIINTERFACE
WHERE  APIID               = @ApiId
AND    TENANTID            = @TenantId
AND    STATUS              <> 5
AND    PARSEDSUCCESSFULLY  <> 1";

        // Count schemas with content (gate: QCTesting → UAT)
        public const string GET_SCHEMA_WITH_CONTENT_COUNT = @"
SELECT COUNT(1)
FROM   MAPISCHEMA
WHERE  APIID         = @ApiId
AND    TENANTID      = @TenantId
AND    REQUESTJSON   IS NOT NULL
AND    RESPONSEJSON  IS NOT NULL";

        // Count parameters without ISREQUIRED set (gate: QCTesting → UAT)
        public const string GET_UNSET_PARAMETER_COUNT = @"
SELECT COUNT(1)
FROM   MAPIPARAMETER
WHERE  APIID      = @ApiId
AND    TENANTID   = @TenantId
AND    ISREQUIRED IS NULL";

        // Check API content completeness (gate: UAT → ReadyToDeploy)
        public const string GET_API_CONTENT_COMPLETENESS = @"
SELECT CAST(
    CASE WHEN ISNULL(ABOUTAPI,'')   = '' THEN 0 ELSE 1 END +
    CASE WHEN ISNULL(KEYFEATURES,'') = '' THEN 0 ELSE 1 END +
    CASE WHEN ISNULL(USECASES,'')   = '' THEN 0 ELSE 1 END
AS INT) AS CompleteCount
FROM   MAPI
WHERE  APIID     = @ApiId
AND    TENANTID  = @TenantId";

        // Count artifacts (gate: UAT → ReadyToDeploy)
        public const string GET_ARTIFACT_COUNT = @"
SELECT COUNT(1)
FROM   MAPIARTIFACT
WHERE  APIID    = @ApiId
AND    TENANTID = @TenantId
AND    STATUS   <> 5";

        // Count exposure policies (gate: ReadyToDeploy → Deployed)
        public const string GET_EXPOSURE_POLICY_COUNT = @"
SELECT COUNT(1)
FROM   MAPIEXPOSUREPOLICY
WHERE  APIID    = @ApiId
AND    TENANTID = @TenantId";

        // Check version status (gate: ReadyToDeploy → Deployed)
        public const string GET_VERSION_STATUS = @"
SELECT VERSIONSTATUS
FROM   MAPIVERSION
WHERE  APIVERSIONID = @ApiVersionId
AND    TENANTID     = @TenantId";

        // Incomplete checklist items for current stage (gate check)
        public const string GET_INCOMPLETE_CHECKLIST_COUNT = @"
SELECT COUNT(1)
FROM   MAPIREQUESTCHECKLIST
WHERE  APIREQUESTID = @ApiRequestId
AND    TENANTID     = @TenantId
AND    STAGE        = @Stage
AND    ISCOMPLETE   = 0";

        // =========================================================
        // READINESS SCORE — component counts for 0-100 score
        // =========================================================
        public const string GET_READINESS_COMPONENTS = @"
SELECT
    (SELECT COUNT(1) FROM MAPIINTERFACE
     WHERE APIID = m.APIID AND TENANTID = m.TENANTID
     AND PARSEDSUCCESSFULLY = 1 AND STATUS <> 5)     AS ParsedInterfaceCount,

    (SELECT COUNT(1) FROM MAPISCHEMA
     WHERE APIID = m.APIID AND TENANTID = m.TENANTID
     AND REQUESTJSON IS NOT NULL AND RESPONSEJSON IS NOT NULL) AS SchemaWithContentCount,

    (SELECT COUNT(1) FROM MAPIOPERATION
     WHERE APIID = m.APIID AND TENANTID = m.TENANTID) AS OperationCount,

    (SELECT COUNT(1) FROM MAPIPARAMETER p
     INNER JOIN MAPIOPERATION op ON p.APIOPERATIONID = op.APIOPERATIONID
     WHERE op.APIID = m.APIID AND op.TENANTID = m.TENANTID
     AND p.ISREQUIRED IS NULL)                        AS UnsetParamCount,

    CASE WHEN ISNULL(m.ABOUTAPI,'')    <> '' THEN 1 ELSE 0 END +
    CASE WHEN ISNULL(m.KEYFEATURES,'') <> '' THEN 1 ELSE 0 END +
    CASE WHEN ISNULL(m.USECASES,'')    <> '' THEN 1 ELSE 0 END +
    CASE WHEN ISNULL(m.QUICKSTART,'')  <> '' THEN 1 ELSE 0 END AS ContentFieldsComplete,

    (SELECT COUNT(1) FROM MAPIARTIFACT
     WHERE APIID = m.APIID AND TENANTID = m.TENANTID AND STATUS <> 5) AS ArtifactCount,

    (SELECT COUNT(1) FROM MAPIEXPOSUREPOLICY
     WHERE APIID = m.APIID AND TENANTID = m.TENANTID) AS ExposurePolicyCount

FROM MAPI m
WHERE m.APIID    = @ApiId
AND   m.TENANTID = @TenantId";

        // =========================================================
        // PostgreSQL Variants
        // =========================================================
        public const string PG_GET_REQUEST = @"
SELECT ""APIREQUESTID""       AS ApiRequestId,
       ""TENANTID""           AS TenantId,
       ""REQUESTCODE""        AS RequestCode,
       ""REQUESTTITLE""       AS RequestTitle,
       ""REQUESTDESCRIPTION"" AS RequestDescription,
       ""BUSINESSJUSTIFICATION"" AS BusinessJustification,
       ""USECASES""           AS UseCases,
       ""INTENTKEYWORDS""     AS IntentKeywords,
       ""REQUESTEDBYID""      AS RequestedById,
       ""REQUESTEDON""        AS RequestedOn,
       ""TARGETDOMAINID""     AS TargetDomainId,
       ""PROPOSEDAPITYPE""    AS ProposedApiType,
       ""PROPOSEDAPIKIND""    AS ProposedApiKind,
       ""PRIORITY""           AS Priority,
       ""REQUESTSTATUS""      AS RequestStatus,
       ""ASSIGNEDTOID""       AS AssignedToId,
       ""LINKEDAPIID""        AS LinkedApiId,
       ""LINKEDAPIVERSIONID"" AS LinkedApiVersionId,
       ""TARGETDELIVERYDATE"" AS TargetDeliveryDate,
       ""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   ""MAPIREQUEST""
WHERE  ""APIREQUESTID"" = @ApiRequestId
AND    ""TENANTID""     = @TenantId";

        public const string PG_SAVE_REQUEST = @"
INSERT INTO ""MAPIREQUEST"" (
    ""APIREQUESTID"", ""TENANTID"", ""REQUESTCODE"", ""REQUESTTITLE"",
    ""REQUESTDESCRIPTION"", ""BUSINESSJUSTIFICATION"", ""USECASES"", ""INTENTKEYWORDS"",
    ""REQUESTEDBYID"", ""REQUESTEDON"", ""TARGETDOMAINID"",
    ""PROPOSEDAPITYPE"", ""PROPOSEDAPIKIND"", ""PRIORITY"", ""REQUESTSTATUS"",
    ""ASSIGNEDTOID"", ""LINKEDAPIID"", ""LINKEDAPIVERSIONID"",
    ""TARGETDELIVERYDATE"", ""REMARKS"",
    ""SORTORDER"", ""STATUS"", ""VERSION"", ""SOURCETYPE"",
    ""CREATEDBYID"", ""CREATEDON"", ""MODIFIEDBYID"", ""MODIFIEDON""
) VALUES (
    @ApiRequestId, @TenantId, @RequestCode, @RequestTitle,
    @RequestDescription, @BusinessJustification, @UseCases, @IntentKeywords,
    @RequestedById, @RequestedOn, @TargetDomainId,
    @ProposedApiType, @ProposedApiKind, @Priority, @RequestStatus,
    @AssignedToId, @LinkedApiId, @LinkedApiVersionId,
    @TargetDeliveryDate, @Remarks,
    @SortOrder, @Status, @Version, @SourceType,
    @CreatedById, NOW(), @ModifiedById, NOW()
)
RETURNING ""APIREQUESTID""";

        public const string PG_UPDATE_REQUEST = @"
UPDATE ""MAPIREQUEST"" SET
    ""REQUESTTITLE""          = @RequestTitle,
    ""REQUESTDESCRIPTION""    = @RequestDescription,
    ""BUSINESSJUSTIFICATION"" = @BusinessJustification,
    ""USECASES""              = @UseCases,
    ""INTENTKEYWORDS""        = @IntentKeywords,
    ""TARGETDOMAINID""        = @TargetDomainId,
    ""PROPOSEDAPITYPE""       = @ProposedApiType,
    ""PROPOSEDAPIKIND""       = @ProposedApiKind,
    ""PRIORITY""              = @Priority,
    ""ASSIGNEDTOID""          = @AssignedToId,
    ""TARGETDELIVERYDATE""    = @TargetDeliveryDate,
    ""REMARKS""               = @Remarks,
    ""SORTORDER""             = @SortOrder,
    ""STATUS""                = @Status,
    ""MODIFIEDBYID""          = @ModifiedById,
    ""MODIFIEDON""            = NOW()
WHERE ""APIREQUESTID"" = @ApiRequestId
AND   ""TENANTID""     = @TenantId";

        public const string PG_UPDATE_REQUEST_STATUS = @"
UPDATE ""MAPIREQUEST"" SET
    ""REQUESTSTATUS"" = @RequestStatus,
    ""MODIFIEDBYID""  = @ModifiedById,
    ""MODIFIEDON""    = NOW()
WHERE ""APIREQUESTID"" = @ApiRequestId
AND   ""TENANTID""     = @TenantId";

        public const string PG_LINK_API_TO_REQUEST = @"
UPDATE ""MAPIREQUEST"" SET
    ""LINKEDAPIID""        = @LinkedApiId,
    ""LINKEDAPIVERSIONID"" = @LinkedApiVersionId,
    ""MODIFIEDBYID""       = @ModifiedById,
    ""MODIFIEDON""         = NOW()
WHERE ""APIREQUESTID"" = @ApiRequestId
AND   ""TENANTID""     = @TenantId";

        public const string PG_DELETE_REQUEST = @"
UPDATE ""MAPIREQUEST"" SET
    ""STATUS""       = 5,
    ""MODIFIEDBYID"" = @ModifiedById,
    ""MODIFIEDON""   = NOW()
WHERE ""APIREQUESTID"" = @ApiRequestId
AND   ""TENANTID""     = @TenantId";
    }
}