namespace AMPDAL.Query.ApiRequestTimeline
{
    public static class ApiRequestTimelineQB
    {
        // Required index:
        // IX_MAPIREQUESTTIMELINE_APIREQUESTID on (APIREQUESTID, ACTIONTAKENON)

        // =========================================================
        // GET TIMELINE (ordered ASC for audit trail)
        // =========================================================
        public const string GET_TIMELINE = @"
SELECT TIMELINEID       AS TimelineId,
       TENANTID         AS TenantId,
       APIREQUESTID     AS ApiRequestId,
       FROMSTATUS       AS FromStatus,
       TOSTATUS         AS ToStatus,
       ACTIONTAKENBYID  AS ActionTakenById,
       ACTIONTAKENON    AS ActionTakenOn,
       COMMENTS         AS Comments,
       ATTACHMENTURL    AS AttachmentUrl
FROM   MAPIREQUESTTIMELINE
WHERE  APIREQUESTID = @ApiRequestId
AND    TENANTID     = @TenantId
ORDER  BY ACTIONTAKENON ASC";

        // =========================================================
        // INSERT TIMELINE ENTRY (immutable — no UPDATE or DELETE)
        // =========================================================
        public const string INSERT_TIMELINE = @"
INSERT INTO MAPIREQUESTTIMELINE (
    TIMELINEID, TENANTID, APIREQUESTID,
    FROMSTATUS, TOSTATUS,
    ACTIONTAKENBYID, ACTIONTAKENON,
    COMMENTS, ATTACHMENTURL
) VALUES (
    @TimelineId, @TenantId, @ApiRequestId,
    @FromStatus, @ToStatus,
    @ActionTakenById, GETDATE(),
    @Comments, @AttachmentUrl
);
SELECT @TimelineId";

        // =========================================================
        // PostgreSQL Variants
        // =========================================================
        public const string PG_GET_TIMELINE = @"
SELECT ""TIMELINEID""      AS TimelineId,
       ""TENANTID""        AS TenantId,
       ""APIREQUESTID""    AS ApiRequestId,
       ""FROMSTATUS""      AS FromStatus,
       ""TOSTATUS""        AS ToStatus,
       ""ACTIONTAKENBYID"" AS ActionTakenById,
       ""ACTIONTAKENON""   AS ActionTakenOn,
       ""COMMENTS""        AS Comments,
       ""ATTACHMENTURL""   AS AttachmentUrl
FROM   ""MAPIREQUESTTIMELINE""
WHERE  ""APIREQUESTID"" = @ApiRequestId
AND    ""TENANTID""     = @TenantId
ORDER  BY ""ACTIONTAKENON"" ASC";

        public const string PG_INSERT_TIMELINE = @"
INSERT INTO ""MAPIREQUESTTIMELINE"" (
    ""TIMELINEID"", ""TENANTID"", ""APIREQUESTID"",
    ""FROMSTATUS"", ""TOSTATUS"",
    ""ACTIONTAKENBYID"", ""ACTIONTAKENON"",
    ""COMMENTS"", ""ATTACHMENTURL""
) VALUES (
    @TimelineId, @TenantId, @ApiRequestId,
    @FromStatus, @ToStatus,
    @ActionTakenById, NOW(),
    @Comments, @AttachmentUrl
)
RETURNING ""TIMELINEID""";
    }
}