namespace EnablementDAL.Query.Show;

public static class ShowResolverQB
{
    // -------------------------------------------------------------------------
    // Candidate shows: active, approved, within date window, matching startType.
    // Parameters: @TenantId, @StartType
    // -------------------------------------------------------------------------
    // -------------------------------------------------------------------------
    // Candidate shows: active, approved, within date window, matching startType.
    // Parameters: @TenantId, @StartType
    // -------------------------------------------------------------------------
    public const string GET_CANDIDATE_SHOWS = @"
    SELECT S.SHOWID           AS ShowId,
           S.SHOWNAME         AS ShowName,
           S.DISPLAYNAME      AS DisplayName,
           S.DESCRIPTION      AS Description,   
           S.SHOWNATURE       AS ShowNature,
           S.SHOWTYPE         AS ShowType,
           S.STARTTYPE        AS StartType,
           S.PRIORITY         AS Priority,
           S.THEME            AS Theme,
           S.DISPLAYPOSITION  AS DisplayPosition,
           S.DISPLAYDURATION  AS DisplayDuration,
           S.STARTDATE        AS StartDate,
           S.EXPIRYDATE       AS ExpiryDate,
           S.TENANTID         AS TenantId,
           S.STATUS           AS Status,
           S.APPROVALSTATUS   AS ApprovalStatus,
           S.CONDITIONTYPE    AS ConditionType,
           S.SHOWCONDITIONS   AS ShowConditions,
           S.MODULEID         AS ModuleId,
           S.CONTENTCATEGORYID AS ContentCategoryId
    FROM TSHOW S
    WHERE S.TENANTID       = @TenantId
      AND S.STATUS         = 1
      AND S.APPROVALSTATUS = 2
      AND S.STARTDATE      <= GETUTCDATE()
      AND S.EXPIRYDATE     >= GETUTCDATE()
      AND S.STARTTYPE      = @StartType";
    public const string GET_STEP_CONTENT = @"
    SELECT 
           SHOWID,
           CONTENT,
           STEPORDER
    FROM   TSHOWSTEP
    WHERE  SHOWID IN @ShowIds
    AND    STEPORDER = (
        SELECT MIN(STEPORDER) 
        FROM TSHOWSTEP T2 
        WHERE T2.SHOWID = TSHOWSTEP.SHOWID 
        AND T2.ISACTIVE = 1
    )
    AND    ISACTIVE = 1";
    // -------------------------------------------------------------------------
    // Candidate shows for the MATCH resolver (ResolveShowMatches / "System A" —
    // pull/context resolver, e.g. Help Centre, Command Palette, Action Panel).
    // Same base filter as GET_CANDIDATE_SHOWS but no STARTTYPE — this surface is
    // continuous/context-driven, not tied to a single trigger's start type — and
    // additionally selects DEPLOYMENTMODE/EXTERNALREF (V003 migration).
    // Parameters: @TenantId
    // -------------------------------------------------------------------------
    public const string GET_CANDIDATE_SHOWS_FOR_MATCH = @"
    SELECT S.SHOWID           AS ShowId,
           S.SHOWNAME         AS ShowName,
           S.DISPLAYNAME      AS DisplayName,
           S.SHOWNATURE       AS ShowNature,
           S.SHOWTYPE         AS ShowType,
           S.STARTTYPE        AS StartType,
           S.PRIORITY         AS Priority,
           S.THEME            AS Theme,
           S.DISPLAYPOSITION  AS DisplayPosition,
           S.DISPLAYDURATION  AS DisplayDuration,
           S.STARTDATE        AS StartDate,
           S.EXPIRYDATE       AS ExpiryDate,
           S.TENANTID         AS TenantId,
           S.STATUS           AS Status,
           S.APPROVALSTATUS   AS ApprovalStatus,
           S.CONDITIONTYPE    AS ConditionType,
           S.SHOWCONDITIONS   AS ShowConditions,
           S.DEPLOYMENTMODE   AS DeploymentMode,
           S.EXTERNALREF      AS ExternalRef
    FROM TSHOW S
    WHERE S.TENANTID       = @TenantId
      AND S.STATUS         = 1
      AND S.APPROVALSTATUS = 2
      AND S.STARTDATE      <= GETUTCDATE()
      AND S.EXPIRYDATE     >= GETUTCDATE()";

    // -------------------------------------------------------------------------
    // All active resolver rules for this tenant.
    // Parameters: @TenantId
    // -------------------------------------------------------------------------
    public const string GET_SHOW_RULES = @"
        SELECT R.SHOWRULEID    AS ShowRuleId,
               R.SHOWID        AS ShowId,
               R.TENANTID      AS TenantId,
               R.RULECATEGORY  AS RuleCategory,
               R.RULEGROUP     AS RuleGroup,
               R.RULEKEY       AS RuleKey,
               R.RULEOPERATOR  AS RuleOperator,
               R.RULEVALUE     AS RuleValue,
               R.ISACTIVE      AS IsActive,
               R.REMARKS       AS Remarks,
               R.STATUS        AS Status,
               R.SORTORDER     AS SortOrder,
               R.CREATEDBYID   AS CreatedById,
               R.CREATEDON     AS CreatedOn,
               R.MODIFIEDBYID  AS ModifiedById,
               R.MODIFIEDON    AS ModifiedOn,
               R.SOURCETYPE    AS SourceType
        FROM   TSHOWRULE R
        WHERE  R.TENANTID = @TenantId
        AND    R.ISACTIVE = 1
        AND    R.STATUS   = 1";

    // -------------------------------------------------------------------------
    // Active schedules for all active shows in this tenant.
    // Parameters: @TenantId
    // -------------------------------------------------------------------------
    public const string GET_SHOW_SCHEDULES = @"
    SELECT SC.TSHOWSCHEDULEID  AS ShowScheduleId,
           SC.SHOWID           AS ShowId,
           SC.SCHEDULETYPE     AS ScheduleType,
           SC.SCHEDULEMODE     AS ScheduleMode,
           SC.STARTDATE        AS StartDate,
           SC.ENDDATE          AS EndDate,
           SC.SCHEDULETIME     AS ScheduleTime,
           SC.TIMEZONE         AS Timezone,
           SC.RECURRENCEJSON   AS RecurrenceJson,
           SC.ISACTIVE         AS IsActive,
           SC.REMARKS          AS Remarks,
           SC.STATUS           AS Status,
           SC.SORTORDER        AS SortOrder,
           SC.CREATEDBYID      AS CreatedById,
           SC.CREATEDON        AS CreatedOn,
           SC.MODIFIEDBYID     AS ModifiedById,
           SC.MODIFIEDON       AS ModifiedOn,
           SC.SOURCETYPE       AS SourceType,
           SC.TENANTID         AS TenantId
    FROM   TSHOWSCHEDULE SC
    WHERE  SC.TENANTID = @TenantId
    AND    SC.ISACTIVE = 1
    AND    SC.STATUS   = 1
    AND    SC.SHOWID IN (
               SELECT S.SHOWID
               FROM   TSHOW S
               WHERE  S.TENANTID = @TenantId
               AND    S.STATUS   = 1)";

    // -------------------------------------------------------------------------
    // ShowIDs already completed or dismissed by this user (OneTime exclusion).
    // Parameters: @UserId, @TenantId
    // -------------------------------------------------------------------------
    public const string GET_USER_COMPLETED_RUNS = @"
        SELECT DISTINCT R.SHOWID
        FROM   TSHOWRUN R
        WHERE  R.USERID   = @UserId
        AND    R.TENANTID = @TenantId
        AND    R.RUNSTATE IN (1, 2)";

    // -------------------------------------------------------------------------
    // Count of all runs ever started by this user (0 = first login ever).
    // Parameters: @UserId, @TenantId
    // -------------------------------------------------------------------------
    public const string CHECK_FIRST_LOGIN = @"
        SELECT COUNT(1)
        FROM   TSHOWRUN R
        WHERE  R.USERID   = @UserId
        AND    R.TENANTID = @TenantId";
}