namespace IceImportDAL.Query.IceImportRun;

// SQL constants for ICEIMPORT.TICEIMPORTRUN / TICEIMPORTRUNLOG / TICEIMPORTRUNARTIFACT /
// TICEIMPORTRUNROWRESULT — column names verified against
// DB/Migrations/20260708_IceImport_Phase0_Schema.sql, not inferred from DTO property names.
//
// RUNID/ARTIFACTID are BIGINT IDENTITY. IQueryExecutor.ExecuteIdentityAsync returns Task<int>
// (SCOPE_IDENTITY() cast to int) — unsuitable for a BIGINT identity that can exceed Int32 range.
// Every INSERT here instead ends with "SELECT CAST(SCOPE_IDENTITY() AS BIGINT);" and is called via
// QuerySingleAsync<long>, mirroring AutomationDAL.Query.Run.RunQB.INSERT_EXECUTION/INSERT_ARTIFACT
// exactly (AUTO.TRUNEXECUTION.RUNID is also BIGINT).
//
// Only TICEIMPORTRUN carries TENANTID — TICEIMPORTRUNLOG/ARTIFACT/ROWRESULT do not (per the
// migration). Tenant boundary for those child tables is enforced by IIceImportRunDAL callers first
// verifying the parent RunId belongs to the caller's tenant via GET_BY_ID before ever touching a
// child table — same discipline AutomationBLL.Run.RunBLL.AppendLog/SaveArtifact use for AUTO.TRUNLOG
// (also TENANTID-less). Statements that mutate TICEIMPORTRUN itself (UPDATE_STAGE/COMPLETE_RUN/
// CANCEL_RUN) filter TENANTID directly in their own WHERE clause instead — no separate check needed.
public static class IceImportRunQB
{
    // Enqueued as 'Queued' with no STARTEDON/CURRENTSTAGE — UPDATE_STAGE's first call (stage=
    // "Parse") flips STATUS to 'Running' and stamps STARTEDON the moment the background pipeline
    // actually begins, mirroring AUTO.TRUNEXECUTION's Queued->Running-on-claim convention.
    // Covering index: IX_TICEIMPORTRUN_ICEMAPID_CREATEDON (ICEMAPID, CREATEDON DESC) INCLUDE (STATUS, TENANTID)
    public const string INSERT_RUN = @"
        INSERT INTO ICEIMPORT.TICEIMPORTRUN
               (ICEMAPID, STATUS, TRIGGEREDBY, SOURCEPROFILEID, RETRYOFRUNID,
                TENANTID, CREATEDBYID, CREATEDON)
        VALUES (@IceMapId, 'Queued', @TriggeredBy, @SourceProfileId, @RetryOfRunId,
                @TenantId, @CreatedById, SYSUTCDATETIME());
        SELECT CAST(SCOPE_IDENTITY() AS BIGINT);";

    public const string UPDATE_SOURCE_FILE_ARTIFACT = @"
        UPDATE ICEIMPORT.TICEIMPORTRUN
        SET    SOURCEFILEARTIFACTID = @ArtifactId
        WHERE  RUNID = @RunId AND TENANTID = @TenantId";

    // First call (stage="Parse") atomically flips Queued->Running and stamps STARTEDON; subsequent
    // calls just advance CURRENTSTAGE. WHERE STATUS IN ('Queued','Running') is a safety net beyond
    // the in-process CancellationTokenSource: if CancelImportRun already flipped STATUS to
    // 'Cancelled' concurrently (possibly from a different instance than the one running the
    // background task), this UPDATE becomes a no-op — the run's row stops advancing even if the
    // background task's own cancellation token somehow wasn't observed in time.
    public const string UPDATE_STAGE = @"
        UPDATE ICEIMPORT.TICEIMPORTRUN
        SET    CURRENTSTAGE = @CurrentStage,
               STATUS        = CASE WHEN STATUS = 'Queued' THEN 'Running' ELSE STATUS END,
               STARTEDON     = CASE WHEN STARTEDON IS NULL THEN SYSUTCDATETIME() ELSE STARTEDON END
        WHERE  RUNID = @RunId AND TENANTID = @TenantId AND STATUS IN ('Queued', 'Running')";

    // Status must be a terminal value allowed by CK_TICEIMPORTRUN_STATUS: Success | Failed | Cancelled.
    public const string COMPLETE_RUN = @"
        UPDATE ICEIMPORT.TICEIMPORTRUN
        SET    STATUS       = @Status,
               COMPLETEDON  = SYSUTCDATETIME(),
               ROWTOTALCOUNT = @RowTotalCount,
               SUCCESSCOUNT = @SuccessCount,
               FAILEDCOUNT  = @FailedCount,
               ERRORMESSAGE = @ErrorMessage
        WHERE  RUNID    = @RunId
        AND    TENANTID = @TenantId
        AND    STATUS   IN ('Queued', 'Running')";

    public const string CANCEL_RUN = @"
        UPDATE ICEIMPORT.TICEIMPORTRUN
        SET    STATUS      = 'Cancelled',
               COMPLETEDON = SYSUTCDATETIME()
        WHERE  RUNID    = @RunId
        AND    TENANTID = @TenantId
        AND    STATUS   IN ('Queued', 'Running')";

    public const string INSERT_LOG = @"
        INSERT INTO ICEIMPORT.TICEIMPORTRUNLOG (RUNID, LOGLEVEL, MESSAGE, LOGGEDON)
        VALUES (@RunId, @LogLevel, @Message, SYSUTCDATETIME());";

    public const string INSERT_ARTIFACT = @"
        INSERT INTO ICEIMPORT.TICEIMPORTRUNARTIFACT (RUNID, ARTIFACTTYPE, STORAGEKEY, CREATEDON)
        VALUES (@RunId, @ArtifactType, @StorageKey, SYSUTCDATETIME());
        SELECT CAST(SCOPE_IDENTITY() AS BIGINT);";

    public const string INSERT_ROW_RESULT = @"
        INSERT INTO ICEIMPORT.TICEIMPORTRUNROWRESULT
               (RUNID, ROWNUMBER, ENTITYCODE, STATUS, TARGETENTITYID, POSTDATA, ERRORMESSAGE)
        VALUES (@RunId, @RowNumber, @EntityCode, @Status, @TargetEntityId, @PostData, @ErrorMessage);";

    public const string GET_BY_ID = @"
        SELECT RUNID, ICEMAPID, STATUS, TRIGGEREDBY, SOURCEFILEARTIFACTID, SOURCEPROFILEID,
               CURRENTSTAGE, ROWTOTALCOUNT, SUCCESSCOUNT, FAILEDCOUNT, STARTEDON, COMPLETEDON,
               ERRORMESSAGE, RETRYOFRUNID, TENANTID, CREATEDBYID, CREATEDON
        FROM   ICEIMPORT.TICEIMPORTRUN
        WHERE  RUNID = @RunId AND TENANTID = @TenantId";

    // QueryPagedAsync wraps the full SQL in "SELECT COUNT(*) FROM (sql)" which breaks once the
    // inner SQL already has OFFSET/FETCH (it counts only the page, not the total) — same bug
    // MMDAL.CustomCode.Production.ProductionDAL documents. IceImportRunDAL runs GET_HISTORY_PAGE and
    // GET_HISTORY_COUNT separately instead and assembles PagedResult<T> itself.
    // Covering index: IX_TICEIMPORTRUN_ICEMAPID_CREATEDON
    public const string GET_HISTORY_PAGE = @"
        SELECT RUNID, ICEMAPID, STATUS, TRIGGEREDBY, CURRENTSTAGE, ROWTOTALCOUNT, SUCCESSCOUNT,
               FAILEDCOUNT, STARTEDON, COMPLETEDON, RETRYOFRUNID, CREATEDON
        FROM   ICEIMPORT.TICEIMPORTRUN
        WHERE  ICEMAPID = @IceMapId AND TENANTID = @TenantId
        ORDER BY CREATEDON DESC, RUNID DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string GET_HISTORY_COUNT = @"
        SELECT COUNT(*)
        FROM   ICEIMPORT.TICEIMPORTRUN
        WHERE  ICEMAPID = @IceMapId AND TENANTID = @TenantId";

    public const string GET_LOGS_BY_RUN = @"
        SELECT LOGID, RUNID, LOGLEVEL, MESSAGE, LOGGEDON
        FROM   ICEIMPORT.TICEIMPORTRUNLOG
        WHERE  RUNID = @RunId
        ORDER BY LOGGEDON, LOGID";

    // Index note: no dedicated index needed — RUNID lookups on this small child table are cheap
    // via the clustered PK scan at this table's expected volume (a handful of artifacts per run).
    public const string GET_ARTIFACTS_BY_RUN = @"
        SELECT ARTIFACTID, RUNID, ARTIFACTTYPE, STORAGEKEY, CREATEDON
        FROM   ICEIMPORT.TICEIMPORTRUNARTIFACT
        WHERE  RUNID = @RunId
        ORDER BY ARTIFACTID";

    public const string GET_LATEST_ARTIFACT_BY_TYPE = @"
        SELECT TOP (1) ARTIFACTID, RUNID, ARTIFACTTYPE, STORAGEKEY, CREATEDON
        FROM   ICEIMPORT.TICEIMPORTRUNARTIFACT
        WHERE  RUNID = @RunId AND ARTIFACTTYPE = @ArtifactType
        ORDER BY ARTIFACTID DESC";

    // Covering index: IX_TICEIMPORTRUNROWRESULT_RUNID_STATUS (RUNID, STATUS)
    public const string GET_ROW_ERRORS_PAGE = @"
        SELECT ROWRESULTID, RUNID, ROWNUMBER, ENTITYCODE, STATUS, TARGETENTITYID, POSTDATA, ERRORMESSAGE
        FROM   ICEIMPORT.TICEIMPORTRUNROWRESULT
        WHERE  RUNID = @RunId
        AND    (@Status IS NULL OR STATUS = @Status)
        ORDER BY ROWNUMBER
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string GET_ROW_ERRORS_COUNT = @"
        SELECT COUNT(*)
        FROM   ICEIMPORT.TICEIMPORTRUNROWRESULT
        WHERE  RUNID = @RunId
        AND    (@Status IS NULL OR STATUS = @Status)";
}