namespace EnablementDAL.Query.Show;
public static class ShowRuleQB
{
    public const string GET_SHOW_RULE = @"
SELECT
    R.SHOWRULEID AS ShowRuleId,
    ISNULL(R.SHOWID, -1) AS ShowId,
    --ISNULL(R.CHANNELID, -1) AS ChannelId,
    --ISNULL(R.SPACEID, -1) AS SpaceId,
    S.SHOWNAME AS ShowName,
    R.TENANTID AS TenantId,
    C.CLIENTNAME AS TenantName,
    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.VERSION AS Version,
    R.STATUS AS Status,
    R.SORTORDER AS SortOrder,
    R.CREATEDBYID AS CreatedById,
    CU.USERNAME AS CreatedBy,
    R.CREATEDON AS CreatedOn,
    R.MODIFIEDBYID AS ModifiedById,
    MU.USERNAME AS ModifiedBy,
    R.MODIFIEDON AS ModifiedOn,
    R.SOURCETYPE AS SourceType
FROM TSHOWRULE R
LEFT JOIN TSHOW S ON S.SHOWID = R.SHOWID
LEFT JOIN MCLIENT C ON C.CLIENTID = R.TENANTID
LEFT JOIN MUSER CU ON CU.USERID = R.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = R.MODIFIEDBYID
WHERE R.SHOWRULEID = @ShowRuleId";

    public const string GET_SHOW_RULE_LIST = @"
SELECT
    R.SHOWRULEID    AS ShowRuleId,
    ISNULL(R.SHOWID, -1)    AS ShowId,
    --ISNULL(R.CHANNELID, -1) AS ChannelId,
    --ISNULL(R.SPACEID, -1)   AS SpaceId,
    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.VERSION       AS Version,
    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.STATUS <> 0
ORDER BY R.RULECATEGORY, R.RULEGROUP, R.SORTORDER;";

    public const string GET_SHOW_RULE_LIST_COUNT = @"
        SELECT COUNT(1)
        FROM   TSHOWRULE R
        WHERE  R.SHOWID   = @ShowId
        AND    R.STATUS   <> 0";

    // Channel-owned rules — same table, WHERE CHANNELID instead of SHOWID. Backs
    // ShowChannelSubscriptionBLL's live audience-eligibility check (reuses
    // ShowRuleBLL.Evaluate() unchanged — see V005 migration header comment).
    public const string GET_SHOW_RULE_LIST_FOR_CHANNEL = @"
SELECT
    R.SHOWRULEID    AS ShowRuleId,
    R.CHANNELID     AS ChannelId,
    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.VERSION       AS Version,
    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.CHANNELID = @ChannelId
AND   R.TENANTID  = @TenantId
AND   R.STATUS    <> 0
ORDER BY R.RULECATEGORY, R.RULEGROUP, R.SORTORDER;";

    // Space-owned rules — same table, WHERE SPACEID instead of SHOWID/CHANNELID. Backs
    // ShowSpaceSubscriptionBLL's live audience-eligibility check (reuses
    // ShowRuleBLL.Evaluate() unchanged — see V008 migration header comment). A plain
    // GET_SHOW_RULE_LIST can't be reused here — it ignores any owner filter and would
    // return Show/Channel rows mixed in with Space rows.
    public const string GET_SHOW_RULE_LIST_FOR_SPACE = @"
SELECT
    R.SHOWRULEID    AS ShowRuleId,
    R.SPACEID       AS SpaceId,
    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.VERSION       AS Version,
    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.SPACEID   = @SpaceId
AND   R.TENANTID  = @TenantId
AND   R.STATUS    <> 0
ORDER BY R.RULECATEGORY, R.RULEGROUP, R.SORTORDER;";

    public const string INSERT_SHOW_RULE = @"
INSERT INTO TSHOWRULE
(
    SHOWID,
    --CHANNELID,
    --SPACEID,
    TENANTID,
    RULECATEGORY,
    RULEGROUP,
    RULEKEY,
    RULEOPERATOR,
    RULEVALUE,
    ISACTIVE,
    REMARKS,
    VERSION,
    STATUS,
    SORTORDER,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE
)
VALUES
(
    @ShowId,
    --@ChannelId,
    --@SpaceId,
    @TenantId,
    @RuleCategory,
    @RuleGroup,
    @RuleKey,
    @RuleOperator,
    @RuleValue,
    @IsActive,
    @Remarks,
    0,
    1,
    @SortOrder,
    @CreatedById,
    GETUTCDATE(),
    @ModifiedById,
    GETUTCDATE(),
    @SourceType
);

SELECT CAST(SCOPE_IDENTITY() AS INT);";

    public const string UPDATE_SHOW_RULE = @"
        UPDATE TSHOWRULE
        SET    RULECATEGORY = @RuleCategory,
               RULEGROUP    = @RuleGroup,
               RULEKEY      = @RuleKey,
               RULEOPERATOR = @RuleOperator,
               RULEVALUE    = @RuleValue,
               ISACTIVE     = @IsActive,
               REMARKS      = @Remarks,
               SORTORDER    = @SortOrder,
               VERSION      = VERSION + 1,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  SHOWRULEID = @ShowRuleId";

    // Soft delete — same convention as TSHOWSCHEDULE/TSHOWCONTENTLINK (STATUS = 0).
    public const string DELETE_SHOW_RULE = @"
        UPDATE TSHOWRULE
        SET    STATUS       = 0,
               ISACTIVE     = 0,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  SHOWRULEID = @ShowRuleId";
}