namespace CollabDAL.Query.CollabSession;

public static class CollabSessionQB
{
    // Index: IX_CollabSession_DB_Status
    public const string GET_ACTIVE_SESSIONS = @"
        SELECT   s.SessionId, s.SessionCode, s.Title,
                 s.Status, s.Mode,
                 s.ContextModuleCode, s.ContextRecordLabel,
                 s.ScheduledAt, s.StartedAt,
                 s.DatabaseName,
                 COUNT(p.ParticipantId) AS ActiveParticipantCount
        FROM     collab.CollabSession      s
        LEFT JOIN collab.CollabParticipant p
               ON p.SessionId  = s.SessionId
              AND p.IsActive   = 1
        WHERE    s.DatabaseName = @DatabaseName
        AND      s.Status       = 'Active'
        GROUP BY s.SessionId, s.SessionCode, s.Title,
                 s.Status, s.Mode,
                 s.ContextModuleCode, s.ContextRecordLabel,
                 s.ScheduledAt, s.StartedAt,
                 s.DatabaseName";

    // Index: IX_CollabSession_DB_Status + IX_CollabSession_ScheduledAt
    public const string GET_SCHEDULED_SESSIONS = @"
        SELECT   s.SessionId, s.SessionCode, s.Title,
                 s.Status, s.Mode,
                 s.ContextModuleCode, s.ContextRecordLabel,
                 s.ScheduledAt, s.StartedAt,
                 s.DatabaseName,
                 0 AS ActiveParticipantCount
        FROM     collab.CollabSession s
        WHERE    s.DatabaseName = @DatabaseName
        AND      s.Status       IN ('Pending', 'Active')
        AND      s.ScheduledAt  IS NOT NULL
        AND      s.ScheduledAt  >= GETUTCDATE()
        ORDER BY s.ScheduledAt ASC";

    // Index: PK (SessionId) + DatabaseName filter
    public const string GET_SESSION_BY_ID = @"
        SELECT s.SessionId, s.SessionCode, s.Title,
               s.SessionType, s.Status, s.Mode,
               s.ContextModuleCode, s.ContextRecordId,
               s.ContextRecordLabel, s.ContextRouteUrl,
               s.ScheduledAt, s.ScheduledDurationMinutes,
               s.StartedAt, s.EndedAt,
               s.MaxParticipants, s.CreatedBy, s.CreatedDate,
               s.DatabaseName
        FROM   collab.CollabSession s
        WHERE  s.SessionId    = @SessionId
        AND    s.DatabaseName = @DatabaseName";

    // Index: UX_CollabSession_Code_DB
    public const string GET_SESSION_BY_CODE = @"
        SELECT s.SessionId, s.SessionCode, s.Title,
               s.SessionType, s.Status, s.Mode,
               s.ContextModuleCode, s.ContextRecordId,
               s.ContextRecordLabel, s.ContextRouteUrl,
               s.ScheduledAt, s.ScheduledDurationMinutes,
               s.StartedAt, s.EndedAt,
               s.MaxParticipants, s.CreatedBy, s.CreatedDate,
               s.DatabaseName
        FROM   collab.CollabSession s
        WHERE  s.SessionCode   = @SessionCode
        AND    s.DatabaseName  = @DatabaseName";

    // Index: IX_CollabSession_DB_Status — for cleanup background service
    // NOTE: No DatabaseName filter — queries across all tenants for background cleanup
    public const string GET_ALL_ABANDONED_CANDIDATES = @"
        SELECT s.SessionId, s.DatabaseName
        FROM   collab.CollabSession s
        WHERE  s.Status          = 'Active'
        AND    (
                   s.LastActivityAt < DATEADD(MINUTE, -@TimeoutMinutes, GETUTCDATE())
                OR (s.LastActivityAt IS NULL AND s.StartedAt < DATEADD(MINUTE, -@TimeoutMinutes, GETUTCDATE()))
               )";

    // Returns new SessionId via OUTPUT INSERTED
    public const string CREATE_SESSION = @"
        INSERT INTO collab.CollabSession
               (SessionCode, Title, SessionType, Status, Mode,
                ContextModuleCode, ContextRecordId, ContextRecordLabel, ContextRouteUrl,
                ScheduledAt, ScheduledDurationMinutes,
                MaxParticipants, AllowAnonymousJoin,
                CreatedBy, CreatedDate, DatabaseName)
        OUTPUT INSERTED.SessionId
        VALUES (@SessionCode, @Title, @SessionType, 'Pending', @Mode,
                @ContextModuleCode, @ContextRecordId, @ContextRecordLabel, @ContextRouteUrl,
                @ScheduledAt, @ScheduledDurationMinutes,
                @MaxParticipants, 0,
                @CreatedBy, GETUTCDATE(), @DatabaseName)";

    public const string UPDATE_SESSION_STATUS = @"
        UPDATE collab.CollabSession
        SET    Status         = @NewStatus,
               StartedAt      = CASE WHEN @NewStatus = 'Active'                  THEN GETUTCDATE() ELSE StartedAt END,
               EndedAt        = CASE WHEN @NewStatus IN ('Ended','Abandoned')     THEN GETUTCDATE() ELSE EndedAt   END,
               LastActivityAt = GETUTCDATE()
        WHERE  SessionId      = @SessionId
        AND    DatabaseName   = @DatabaseName";

    public const string UPDATE_LAST_ACTIVITY = @"
        UPDATE collab.CollabSession
        SET    LastActivityAt = GETUTCDATE()
        WHERE  SessionId      = @SessionId
        AND    DatabaseName   = @DatabaseName";

    public const string UPDATE_SESSION_CONTEXT = @"
        UPDATE collab.CollabSession
        SET    ContextRouteUrl      = @ContextRouteUrl,
               ContextRecordId      = @ContextRecordId,
               ContextRecordLabel   = @ContextRecordLabel,
               LastActivityAt       = GETUTCDATE()
        WHERE  SessionId            = @SessionId
        AND    DatabaseName         = @DatabaseName";
}