namespace ECPDAL.Communication.QueryBuilders;

public static class CommentThreadQB
{
    // Index: PK (CommentThreadId)
    public const string GET_COMMENT_THREAD = @"
        SELECT
            CT.COMMENTTHREADID       AS CommentThreadId,
            CT.PARENTCOMMENTTHREADID AS ParentCommentThreadId,
            CT.ROOTCOMMENTTHREADID   AS RootCommentThreadId,
            CT.OBJECTTYPEID          AS ObjectTypeId,
            CT.OBJECTID              AS ObjectId,
            CT.USERID                AS UserId,
            U.USERCODE               AS UserCode,
            U.USERNAME               AS UserName,
            CT.COMMENTTEXT           AS CommentText,
            CT.COMMENTSTATUS         AS CommentStatus,
            CT.ACCESSTYPE            AS AccessType,
            CT.PRIVATEUSERGROUPID    AS PrivateUserGroupId,
            CT.SECURITYMARKID        AS SecurityMarkId,
            CT.BIZTRANSACTIONID      AS BizTransactionId,
            CT.OUID                  AS OuId,
            CT.TENANTID              AS TenantId,
            CT.DATABASENAME          AS DatabaseName,
            CT.DATABASETYPE          AS DatabaseType,
            CT.VERSION               AS Version,
            CT.STATUS                AS Status,
            CT.CREATEDBYID           AS CreatedById,
            CT.CREATEDON             AS CreatedOn,
            CT.MODIFIEDBYID          AS ModifiedById,
            CT.MODIFIEDON            AS ModifiedOn
        FROM   TCOMMENTTHREAD CT
        LEFT JOIN MUSER U ON CT.USERID = U.USERID
        WHERE  CT.COMMENTTHREADID = @CommentThreadId
          AND  CT.DATABASENAME    = @DatabaseName";

    // Index: IX_TCOMMENTTHREAD_ROOTCOMMENTTHREADID — flat fetch of an entire tree in one round
    // trip; CommentThreadBLL assembles parent/child nesting in memory via ParentCommentThreadId,
    // avoiding any recursive-CTE cross-engine dialect risk.
    public const string GET_COMMENT_THREAD_TREE = @"
        SELECT
            CT.COMMENTTHREADID       AS CommentThreadId,
            CT.PARENTCOMMENTTHREADID AS ParentCommentThreadId,
            CT.ROOTCOMMENTTHREADID   AS RootCommentThreadId,
            CT.OBJECTTYPEID          AS ObjectTypeId,
            CT.OBJECTID              AS ObjectId,
            CT.USERID                AS UserId,
            U.USERCODE               AS UserCode,
            U.USERNAME               AS UserName,
            CT.COMMENTTEXT           AS CommentText,
            CT.COMMENTSTATUS         AS CommentStatus,
            CT.ACCESSTYPE            AS AccessType,
            CT.PRIVATEUSERGROUPID    AS PrivateUserGroupId,
            CT.SECURITYMARKID        AS SecurityMarkId,
            CT.BIZTRANSACTIONID      AS BizTransactionId,
            CT.OUID                  AS OuId,
            CT.TENANTID              AS TenantId,
            CT.DATABASENAME          AS DatabaseName,
            CT.DATABASETYPE          AS DatabaseType,
            CT.VERSION               AS Version,
            CT.STATUS                AS Status,
            CT.CREATEDBYID           AS CreatedById,
            CT.CREATEDON             AS CreatedOn,
            CT.MODIFIEDBYID          AS ModifiedById,
            CT.MODIFIEDON            AS ModifiedOn
        FROM   TCOMMENTTHREAD CT
        LEFT JOIN MUSER U ON CT.USERID = U.USERID
        WHERE  CT.ROOTCOMMENTTHREADID = @RootCommentThreadId
          AND  CT.DATABASENAME        = @DatabaseName
          AND  CT.STATUS              = 1
        ORDER BY CT.CREATEDON ASC";

    // Index: IX_TCOMMENTTHREAD_OBJECTTYPEID_OBJECTID — top-level (root) threads for an object;
    // callers fetch each root's tree separately via GET_COMMENT_THREAD_TREE.
    public const string GET_COMMENT_THREAD_LIST_BY_OBJECT = @"
        SELECT
            CT.COMMENTTHREADID       AS CommentThreadId,
            CT.PARENTCOMMENTTHREADID AS ParentCommentThreadId,
            CT.ROOTCOMMENTTHREADID   AS RootCommentThreadId,
            CT.OBJECTTYPEID          AS ObjectTypeId,
            CT.OBJECTID              AS ObjectId,
            CT.USERID                AS UserId,
            U.USERCODE               AS UserCode,
            U.USERNAME               AS UserName,
            CT.COMMENTTEXT           AS CommentText,
            CT.COMMENTSTATUS         AS CommentStatus,
            CT.ACCESSTYPE            AS AccessType,
            CT.PRIVATEUSERGROUPID    AS PrivateUserGroupId,
            CT.SECURITYMARKID        AS SecurityMarkId,
            CT.BIZTRANSACTIONID      AS BizTransactionId,
            CT.OUID                  AS OuId,
            CT.TENANTID              AS TenantId,
            CT.DATABASENAME          AS DatabaseName,
            CT.DATABASETYPE          AS DatabaseType,
            CT.VERSION               AS Version,
            CT.STATUS                AS Status,
            CT.CREATEDBYID           AS CreatedById,
            CT.CREATEDON             AS CreatedOn,
            CT.MODIFIEDBYID          AS ModifiedById,
            CT.MODIFIEDON            AS ModifiedOn
        FROM   TCOMMENTTHREAD CT
        LEFT JOIN MUSER U ON CT.USERID = U.USERID
        WHERE  CT.OBJECTTYPEID         = @ObjectTypeId
          AND  CT.OBJECTID             = @ObjectId
          AND  CT.PARENTCOMMENTTHREADID = -1
          AND  CT.DATABASENAME         = @DatabaseName
          AND  CT.STATUS               = 1
        ORDER BY CT.CREATEDON ASC";

    public const string SAVE_COMMENT_THREAD = @"
        INSERT INTO TCOMMENTTHREAD (
            COMMENTTHREADID, PARENTCOMMENTTHREADID, ROOTCOMMENTTHREADID, OBJECTTYPEID, OBJECTID,
            USERID, COMMENTTEXT, COMMENTSTATUS, ACCESSTYPE, PRIVATEUSERGROUPID, SECURITYMARKID,
            BIZTRANSACTIONID, OUID, TENANTID, DATABASENAME, DATABASETYPE,
            VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @CommentThreadId, @ParentCommentThreadId, @RootCommentThreadId, @ObjectTypeId, @ObjectId,
            @UserId, @CommentText, @CommentStatus, @AccessType, @PrivateUserGroupId, @SecurityMarkId,
            @BizTransactionId, @OuId, @TenantId, @DatabaseName, @DatabaseType,
            @Version, @Status, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
        )";

    public const string UPDATE_COMMENT_THREAD_STATUS = @"
        UPDATE TCOMMENTTHREAD
        SET    COMMENTSTATUS = @NewStatus,
               MODIFIEDBYID  = @ModifiedById,
               MODIFIEDON    = @ModifiedOn,
               VERSION       = VERSION + 1
        WHERE  COMMENTTHREADID = @CommentThreadId
          AND  DATABASENAME     = @DatabaseName";

    // Soft delete only — never a hard DELETE, so replies/mentions/reactions keep referential
    // integrity and audit history.
    public const string DELETE_COMMENT_THREAD = @"
        UPDATE TCOMMENTTHREAD
        SET    STATUS       = 2,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = @ModifiedOn,
               VERSION      = VERSION + 1
        WHERE  COMMENTTHREADID = @CommentThreadId
          AND  DATABASENAME     = @DatabaseName";

    // Index: IX_TCOMMENTTHREAD_ROOTCOMMENTTHREADID — distinct prior participants in a thread,
    // one of the three notification-audience sources (plan's "Notification audience" section).
    public const string GET_THREAD_PARTICIPANT_USERIDS = @"
        SELECT DISTINCT CT.USERID
        FROM   TCOMMENTTHREAD CT
        WHERE  CT.ROOTCOMMENTTHREADID = @RootCommentThreadId
          AND  CT.DATABASENAME        = @DatabaseName";

    // Security-level check (plan's "Access, security level & notification routing", thread level):
    // grants a match if the viewer (by UserId, RoleId, or UserGroup membership via
    // MUSERGROUPDETAIL) holds an MECMRIGHTS/MECMRIGHTSDETAIL rule scoped to the Communication
    // module's ObjectTypeId, whose granted SecurityMark's HIERERACHYLEVEL is >= the thread's
    // mark's HIERERACHYLEVEL, within the same SecurityGroup. This is the first real enforcement
    // logic written against these tables anywhere in the repo (see plan) — verify column names
    // against the live schema before relying on this in production.
    public const string GET_SECURITY_RIGHTS_CHECK = @"
        SELECT COUNT(1)
        FROM   MECMRIGHTS ER
        JOIN   MECMRIGHTSDETAIL RD          ON ER.ECMRIGHTSID = RD.ECMRIGHTSID
        JOIN   MSECURITYGROUPDETAIL GRANTED ON RD.SECURITYMARKID = GRANTED.SECURITYGROUPDETAILID
        JOIN   MSECURITYGROUPDETAIL THREAD  ON THREAD.SECURITYGROUPDETAILID = @ThreadSecurityMarkId
                                            AND THREAD.SECURITYGROUPID = RD.SECURITYGROUPID
        WHERE  ER.OBJECTTYPEID = @ObjectTypeId
          AND  ER.STATUS       = 1
          AND  GRANTED.HIERERACHYLEVEL >= THREAD.HIERERACHYLEVEL
          AND  (
                   ER.USERID = @UserId
                OR ER.ROLEID = @RoleId
                OR EXISTS (
                       SELECT 1 FROM MUSERGROUPDETAIL UGD
                       WHERE  UGD.USERGROUPID = ER.USERGROUPID
                         AND  UGD.USERID      = @UserId
                   )
               )";

    // Thread-level private-group membership check (ACCESSTYPE=2/private) — reuses the same
    // MUSERGROUPDETAIL membership table as the ECMRights UserGroup check above.
    public const string GET_USER_IN_GROUP_CHECK = @"
        SELECT COUNT(1)
        FROM   MUSERGROUPDETAIL UGD
        WHERE  UGD.USERGROUPID = @UserGroupId
          AND  UGD.USERID      = @UserId";

    // Flat fetch of the OU-group hierarchy detail rows — CommentThreadBLL walks
    // ORGANISATIONGROUPPARENTID in a bounded C# loop to resolve descendant OUs (plan's OU-level
    // check), rather than a recursive CTE (avoids cross-engine WITH RECURSIVE dialect risk).
    public const string GET_ORGANIZATION_GROUP_DETAILS = @"
        SELECT
            OGD.ORGANIZATIONGROUPID       AS OrganizationGroupId,
            OGD.OUID                      AS OuId,
            OGD.ORGANISATIONGROUPPARENTID AS OrganizationGroupParentId
        FROM MORGANIZATIONGROUPDETAIL OGD";
}