using System.Text;
using SwDAL.DTO.ChangeRequest;

namespace SwDAL.Query.ChangeRequest;

public static class ChangeRequestQB
{
    public static readonly HashSet<string> AllowedSortColumns = new(StringComparer.OrdinalIgnoreCase)
    {
        "title", "createdon", "crstatus", "querytype", "category"
    };

    public const string GET_BY_ID = @"
        SELECT  cr.CHANGEREQUESTID   AS ChangeRequestId,
                cr.TITLE             AS Title,
                cr.DESCRIPTION       AS Description,
                cr.SQL               AS Sql,
                cr.CLIENTDATABASEID  AS ClientDatabaseId,
                cd.CLIENTDBCODE      AS ClientDatabaseCode,
                cd.CLIENTDBNAME      AS ClientDatabaseName,
                cr.CRSTATUS          AS CrStatus,
                cr.EXECUTIONSTATUS   AS ExecutionStatus,
                cr.QUERYTYPE         AS QueryType,
                cr.CATEGORY          AS Category,
                cr.TICKETREFERENCE   AS TicketReference,
                cr.ROLLBACKPLAN      AS RollbackPlan,
                cr.IMPACTANALYSIS    AS ImpactAnalysis,
                cr.PROVISIONINGMODE         AS ProvisioningMode,
                cr.TEMPLATEBACKUPREF        AS TemplateBackupRef,
                cr.BASELINEUPGRADEPACKAGEID AS BaselineUpgradePackageId,
                cr.REVIEWERBYID      AS ReviewerById,
                cr.REVIEWEDON        AS ReviewedOn,
                cr.REVIEWCOMMENT     AS ReviewComment,
                cr.EXECUTEDBYID      AS ExecutedById,
                cr.EXECUTEDON        AS ExecutedOn,
                cr.TENANTID          AS TenantId,
                cr.VERSION           AS Version,
                cr.STATUS            AS Status,
                cr.CREATEDBYID       AS CreatedById,
                cr.CREATEDON         AS CreatedOn,
                cr.MODIFIEDBYID      AS ModifiedById,
                cr.MODIFIEDON        AS ModifiedOn
        FROM    SW.MSWCHANGEREQUEST cr
        LEFT JOIN SW.MSWCLIENTDATABASE cd ON cr.CLIENTDATABASEID = cd.CLIENTDBID
        WHERE   cr.CHANGEREQUESTID = @ChangeRequestId
        AND     cr.STATUS          = 1";

    public const string GET_TIMELINE = @"
        SELECT  tl.TIMELINEID       AS TimelineId,
                tl.CHANGEREQUESTID  AS ChangeRequestId,
                tl.EVENTTYPE        AS EventType,
                tl.EVENTCOMMENT     AS EventComment,
                tl.PERFORMEDBYID    AS PerformedById,
                tl.PERFORMEDON      AS PerformedOn
        FROM    SW.LSWCHANGEREQUESTTIMELINE tl
        WHERE   tl.CHANGEREQUESTID = @ChangeRequestId
        ORDER BY tl.PERFORMEDON ASC";

    public const string GET_SELECT_LIST = @"
        SELECT  cr.CHANGEREQUESTID  AS ChangeRequestId,
                cr.TITLE            AS Title,
                cr.CRSTATUS         AS CrStatus,
                cr.QUERYTYPE        AS QueryType,
                cr.CATEGORY         AS Category,
                cr.TICKETREFERENCE  AS TicketReference,
                cr.CLIENTDATABASEID AS ClientDatabaseId,
                cd.CLIENTDBCODE      AS ClientDatabaseCode,
                cd.CLIENTDBNAME      AS ClientDatabaseName,
                cr.CREATEDON        AS CreatedOn
        FROM    SW.MSWCHANGEREQUEST cr
        LEFT JOIN SW.MSWCLIENTDATABASE cd ON cr.CLIENTDATABASEID = cd.CLIENTDBID
        WHERE    cr.STATUS   = 1
        ORDER BY cr.CREATEDON DESC
        OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY";

    public const string INSERT = @"
        INSERT INTO SW.MSWCHANGEREQUEST
               (TITLE, DESCRIPTION, SQL, CLIENTDATABASEID, CRSTATUS, QUERYTYPE, CATEGORY,
                TICKETREFERENCE, ROLLBACKPLAN, IMPACTANALYSIS,
                PROVISIONINGMODE, TEMPLATEBACKUPREF, BASELINEUPGRADEPACKAGEID,
                TENANTID, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
        VALUES (@Title, @Description, @Sql, @ClientDatabaseId, 0, @QueryType, @Category,
                @TicketReference, @RollbackPlan, @ImpactAnalysis,
                @ProvisioningMode, @TemplateBackupRef, @BaselineUpgradePackageId,
                @TenantId, 1, 1, @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE());
        SELECT CAST(SCOPE_IDENTITY() AS INT);";

    public const string UPDATE = @"
        UPDATE SW.MSWCHANGEREQUEST
        SET    TITLE            = @Title,
               DESCRIPTION      = @Description,
               SQL              = @Sql,
               CLIENTDATABASEID = @ClientDatabaseId,
               QUERYTYPE        = @QueryType,
               CATEGORY         = @Category,
               TICKETREFERENCE  = @TicketReference,
               ROLLBACKPLAN     = @RollbackPlan,
               IMPACTANALYSIS   = @ImpactAnalysis,
               PROVISIONINGMODE         = @ProvisioningMode,
               TEMPLATEBACKUPREF        = @TemplateBackupRef,
               BASELINEUPGRADEPACKAGEID = @BaselineUpgradePackageId,
               VERSION          = VERSION + 1,
               MODIFIEDBYID     = @ModifiedById,
               MODIFIEDON       = GETUTCDATE()
        WHERE  CHANGEREQUESTID  = @ChangeRequestId
        AND    TENANTID         = @TenantId
        AND    CRSTATUS         = 0
        AND    STATUS           = 1";

    /// <summary>
    /// Transitions CrStatus. Caller must pass ExpectedStatus so the WHERE clause
    /// rejects stale/concurrent updates (guard against wrong-state transitions).
    /// </summary>
    public const string UPDATE_STATUS = @"
        UPDATE SW.MSWCHANGEREQUEST
        SET    CRSTATUS         = @NewStatus,
               REVIEWERBYID     = CASE WHEN @NewStatus IN (3,4) THEN @PerformedById ELSE REVIEWERBYID END,
               REVIEWEDON       = CASE WHEN @NewStatus IN (3,4) THEN GETUTCDATE()   ELSE REVIEWEDON   END,
               REVIEWCOMMENT    = CASE WHEN @NewStatus IN (3,4) THEN @Comment       ELSE REVIEWCOMMENT END,
               EXECUTEDBYID     = CASE WHEN @NewStatus = 5      THEN @PerformedById ELSE EXECUTEDBYID END,
               EXECUTEDON       = CASE WHEN @NewStatus = 5      THEN GETUTCDATE()   ELSE EXECUTEDON   END,
               VERSION          = VERSION + 1,
               MODIFIEDBYID     = @PerformedById,
               MODIFIEDON       = GETUTCDATE()
        WHERE  CHANGEREQUESTID  = @ChangeRequestId
        AND    CRSTATUS         = @ExpectedStatus
        AND    STATUS           = 1";

    public const string INSERT_TIMELINE = @"
        INSERT INTO SW.LSWCHANGEREQUESTTIMELINE
               (CHANGEREQUESTID, EVENTTYPE, EVENTCOMMENT, PERFORMEDBYID, PERFORMEDON, TENANTID)
        VALUES (@ChangeRequestId, @EventType, @EventComment, @PerformedById, GETUTCDATE(), @TenantId)";

    public const string DELETE = @"
        UPDATE SW.MSWCHANGEREQUEST
        SET    STATUS       = 0,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  CHANGEREQUESTID = @ChangeRequestId
        AND    CRSTATUS        = 0
        AND    STATUS          = 1";

    /// <summary>Flips EXECUTIONSTATUS only — never touches CRSTATUS/VERSION, since this is
    /// orthogonal background-execution bookkeeping (tracker §37), not a CR state transition (no
    /// ExpectedStatus guard needed: only QueueExecutionAsync/ProcessQueuedExecutionsAsync ever
    /// call this, both of which already hold the one CR row they're updating).
    /// EXECUTEDBYID is also stamped here, conditionally, when queuing (@NewExecutionStatus = 1
    /// AND @QueuedById is supplied) — recording who QUEUED the request, before it actually runs.
    /// ExecuteCoreAsync reads this back as the acting user for a background-worker execution
    /// (the worker's own LoginDTO is never the right identity for a per-user role-grant check),
    /// then re-stamps EXECUTEDBYID with the same value via UPDATE_STATUS's own @NewStatus=5
    /// branch once execution actually completes — so the column still ends up meaning "who
    /// executed this," just correctly attributed to the human, not the worker service account.</summary>
    public const string UPDATE_EXECUTION_STATUS = @"
        UPDATE SW.MSWCHANGEREQUEST
        SET    EXECUTIONSTATUS = @NewExecutionStatus,
               EXECUTEDBYID    = CASE WHEN @NewExecutionStatus = 1 AND @QueuedById IS NOT NULL
                                       THEN @QueuedById ELSE EXECUTEDBYID END
        WHERE  CHANGEREQUESTID = @ChangeRequestId
        AND    STATUS           = 1";

    /// <summary>Rows a background worker (RolloutCampaignWorkerJob's own Quartz-job precedent,
    /// §39) should pick up and run — queued but not yet Running/Done/Failed.</summary>
    public const string GET_QUEUED_FOR_EXECUTION = @"
        SELECT  cr.CHANGEREQUESTID   AS ChangeRequestId,
                cr.TITLE             AS Title,
                cr.DESCRIPTION       AS Description,
                cr.SQL               AS Sql,
                cr.CLIENTDATABASEID  AS ClientDatabaseId,
                cr.CRSTATUS          AS CrStatus,
                cr.EXECUTIONSTATUS   AS ExecutionStatus,
                cr.QUERYTYPE         AS QueryType,
                cr.CATEGORY          AS Category,
                cr.TICKETREFERENCE   AS TicketReference,
                cr.ROLLBACKPLAN      AS RollbackPlan,
                cr.IMPACTANALYSIS    AS ImpactAnalysis,
                cr.PROVISIONINGMODE         AS ProvisioningMode,
                cr.TEMPLATEBACKUPREF        AS TemplateBackupRef,
                cr.BASELINEUPGRADEPACKAGEID AS BaselineUpgradePackageId,
                cr.REVIEWERBYID      AS ReviewerById,
                cr.REVIEWEDON        AS ReviewedOn,
                cr.REVIEWCOMMENT     AS ReviewComment,
                cr.EXECUTEDBYID      AS ExecutedById,
                cr.EXECUTEDON        AS ExecutedOn,
                cr.TENANTID          AS TenantId,
                cr.VERSION           AS Version,
                cr.STATUS            AS Status,
                cr.CREATEDBYID       AS CreatedById,
                cr.CREATEDON         AS CreatedOn,
                cr.MODIFIEDBYID      AS ModifiedById,
                cr.MODIFIEDON        AS ModifiedOn
        FROM    SW.MSWCHANGEREQUEST cr
        WHERE   cr.EXECUTIONSTATUS = 1
        AND     cr.STATUS          = 1
        AND     cr.TENANTID        = @TenantId
        ORDER BY cr.CHANGEREQUESTID ASC";

    public const string GET_LINKED_APPROVED_DDL = @"
        SELECT  ds.DDLSCRIPTID   AS DdlScriptId,
                ds.SQLSCRIPT     AS SqlScript,
                ds.ROLLBACKSQL   AS RollbackSql,
                ds.SEQUENCE      AS Sequence,
                ds.OBJECTNAME    AS ObjectName,
                ds.SCRIPTSTATUS  AS ScriptStatus,
                ds.DBMODELID     AS DbModelId
        FROM    SW.MSWDDLSCRIPT ds
        WHERE   ds.CHANGEREQUESTID = @ChangeRequestId
        AND     ds.SCRIPTSTATUS    = 2
        AND     ds.STATUS          = 1
        ORDER BY ds.SEQUENCE ASC";

    public static (string sql, string countSql) BuildCrList(ChangeRequestListCriteria criteria)
    {
        var where = new StringBuilder("WHERE cr.TENANTID = @TenantId AND cr.STATUS = 1");
        if (criteria.CrStatus.HasValue)         where.Append(" AND cr.CRSTATUS = @CrStatus");
        if (criteria.QueryType.HasValue)         where.Append(" AND cr.QUERYTYPE = @QueryType");
        if (criteria.Category.HasValue)          where.Append(" AND cr.CATEGORY = @Category");
        if (criteria.ClientDatabaseId.HasValue)  where.Append(" AND cr.CLIENTDATABASEID = @ClientDatabaseId");
        if (!string.IsNullOrWhiteSpace(criteria.SearchText))
            where.Append(" AND (cr.TITLE LIKE @SearchText OR cr.TICKETREFERENCE LIKE @SearchText)");

        string body = $@"
        FROM SW.MSWCHANGEREQUEST cr
        {where}";

        string sql = $@"
        SELECT  cr.CHANGEREQUESTID  AS ChangeRequestId,
                cr.TITLE            AS Title,
                cr.CRSTATUS         AS CrStatus,
                cr.QUERYTYPE        AS QueryType,
                cr.CATEGORY         AS Category,
                cr.TICKETREFERENCE  AS TicketReference,
                cr.CLIENTDATABASEID AS ClientDatabaseId,
                cr.CREATEDON        AS CreatedOn
        {body}
        ORDER BY cr.CREATEDON DESC
        OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        string countSql = $"SELECT COUNT(1) {body}";

        return (sql, countSql);
    }
}