namespace ECPDAL.Collaboration.QueryBuilders;

public static class CollaborationSpaceQB
{
    // Index: PK (CollaborationSpaceId)
    public const string GET_COLLABORATION_SPACE = @"
        SELECT
            CS.COLLABORATIONSPACEID AS CollaborationSpaceId,
            CS.NAME                 AS Name,
            CS.DESCRIPTION          AS Description,
            CS.OWNERUSERID          AS OwnerUserId,
            U.USERCODE              AS OwnerUserCode,
            U.USERNAME              AS OwnerUserName,
            CS.STATUS               AS Status,
            CS.OUID                 AS OuId,
            CS.TENANTID             AS TenantId,
            CS.DATABASENAME         AS DatabaseName,
            CS.DATABASETYPE         AS DatabaseType,
            CS.VERSION              AS Version,
            CS.CREATEDBYID          AS CreatedById,
            CS.CREATEDON            AS CreatedOn,
            CS.MODIFIEDBYID         AS ModifiedById,
            CS.MODIFIEDON           AS ModifiedOn
        FROM   TCOLLABORATIONSPACE CS
        LEFT JOIN MUSER U ON CS.OWNERUSERID = U.USERID
        WHERE  CS.COLLABORATIONSPACEID = @CollaborationSpaceId
          AND  CS.DATABASENAME         = @DatabaseName";

    // Index: IX_TCOLLABORATIONSPACE_OWNERUSERID
    public const string GET_COLLABORATION_SPACE_LIST_BY_OWNER = @"
        SELECT
            CS.COLLABORATIONSPACEID AS CollaborationSpaceId,
            CS.NAME                 AS Name,
            CS.DESCRIPTION          AS Description,
            CS.OWNERUSERID          AS OwnerUserId,
            U.USERCODE              AS OwnerUserCode,
            U.USERNAME              AS OwnerUserName,
            CS.STATUS               AS Status,
            CS.OUID                 AS OuId,
            CS.TENANTID             AS TenantId,
            CS.DATABASENAME         AS DatabaseName,
            CS.DATABASETYPE         AS DatabaseType,
            CS.VERSION              AS Version,
            CS.CREATEDBYID          AS CreatedById,
            CS.CREATEDON            AS CreatedOn,
            CS.MODIFIEDBYID         AS ModifiedById,
            CS.MODIFIEDON           AS ModifiedOn
        FROM   TCOLLABORATIONSPACE CS
        LEFT JOIN MUSER U ON CS.OWNERUSERID = U.USERID
        WHERE  CS.OWNERUSERID  = @OwnerUserId
          AND  CS.DATABASENAME = @DatabaseName
        ORDER BY CS.CREATEDON DESC";

    // Index: TCOLLABORATIONSPACEMEMBER.USERID (join) — spaces the given user belongs to as a
    // member (union of owned + member-of is resolved by CollaborationSpaceBLL, not this query).
    public const string GET_COLLABORATION_SPACE_LIST_BY_MEMBER = @"
        SELECT
            CS.COLLABORATIONSPACEID AS CollaborationSpaceId,
            CS.NAME                 AS Name,
            CS.DESCRIPTION          AS Description,
            CS.OWNERUSERID          AS OwnerUserId,
            U.USERCODE              AS OwnerUserCode,
            U.USERNAME              AS OwnerUserName,
            CS.STATUS               AS Status,
            CS.OUID                 AS OuId,
            CS.TENANTID             AS TenantId,
            CS.DATABASENAME         AS DatabaseName,
            CS.DATABASETYPE         AS DatabaseType,
            CS.VERSION              AS Version,
            CS.CREATEDBYID          AS CreatedById,
            CS.CREATEDON            AS CreatedOn,
            CS.MODIFIEDBYID         AS ModifiedById,
            CS.MODIFIEDON           AS ModifiedOn
        FROM   TCOLLABORATIONSPACE CS
        JOIN   TCOLLABORATIONSPACEMEMBER M ON M.COLLABORATIONSPACEID = CS.COLLABORATIONSPACEID
                                           AND M.STATUS = 1
        LEFT JOIN MUSER U ON CS.OWNERUSERID = U.USERID
        WHERE  M.USERID        = @UserId
          AND  CS.DATABASENAME = @DatabaseName
        ORDER BY CS.CREATEDON DESC";

    // Same visibility rule as GetCollaborationSpaceList (owned UNION member-of), collapsed to the
    // picker's minimal shape. Index: IX_TCOLLABORATIONSPACE_OWNERUSERID / IX_TCOLLABORATIONSPACEMEMBER_USERID.
    public const string GET_SELECTLIST_COLLABORATION_SPACE = @"
        SELECT CS.COLLABORATIONSPACEID AS CollaborationSpaceId, CS.NAME AS Name
        FROM   TCOLLABORATIONSPACE CS
        WHERE  CS.STATUS = 1
          AND  CS.DATABASENAME = @DatabaseName
          AND  (CS.OWNERUSERID = @UserId
                OR EXISTS (SELECT 1 FROM TCOLLABORATIONSPACEMEMBER M
                           WHERE M.COLLABORATIONSPACEID = CS.COLLABORATIONSPACEID
                             AND M.USERID = @UserId AND M.STATUS = 1))
        ORDER BY CS.NAME ASC";

    public const string SAVE_COLLABORATION_SPACE = @"
        INSERT INTO TCOLLABORATIONSPACE (
            COLLABORATIONSPACEID, NAME, DESCRIPTION, OWNERUSERID, STATUS, OUID,
            TENANTID, DATABASENAME, DATABASETYPE, VERSION,
            CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @CollaborationSpaceId, @Name, @Description, @OwnerUserId, @Status, @OuId,
            @TenantId, @DatabaseName, @DatabaseType, @Version,
            @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
        )";

    public const string UPDATE_COLLABORATION_SPACE = @"
        UPDATE TCOLLABORATIONSPACE
        SET    NAME         = @Name,
               DESCRIPTION  = @Description,
               OUID         = @OuId,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = @ModifiedOn,
               VERSION      = VERSION + 1
        WHERE  COLLABORATIONSPACEID = @CollaborationSpaceId
          AND  DATABASENAME         = @DatabaseName";

    // Delete = archive (STATUS=2) — TCOLLABORATIONSPACE has no separate row-level status column,
    // per the plan's two-state model (1=active/2=archived).
    public const string ARCHIVE_COLLABORATION_SPACE = @"
        UPDATE TCOLLABORATIONSPACE
        SET    STATUS       = 2,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = @ModifiedOn,
               VERSION      = VERSION + 1
        WHERE  COLLABORATIONSPACEID = @CollaborationSpaceId
          AND  DATABASENAME         = @DatabaseName";

    // Index: IX_TCOLLABORATIONSPACEMEMBER_USERID
    public const string GET_MEMBERS_FOR_SPACE = @"
        SELECT
            M.COLLABORATIONSPACEMEMBERID AS CollaborationSpaceMemberId,
            M.COLLABORATIONSPACEID       AS CollaborationSpaceId,
            M.USERID                     AS UserId,
            U.USERCODE                   AS UserCode,
            U.USERNAME                   AS UserName,
            M.ROLE                       AS Role,
            M.TENANTID                   AS TenantId,
            M.DATABASENAME               AS DatabaseName,
            M.DATABASETYPE               AS DatabaseType,
            M.STATUS                     AS Status,
            M.CREATEDBYID                AS CreatedById,
            M.CREATEDON                  AS CreatedOn,
            M.MODIFIEDBYID               AS ModifiedById,
            M.MODIFIEDON                 AS ModifiedOn
        FROM   TCOLLABORATIONSPACEMEMBER M
        LEFT JOIN MUSER U ON M.USERID = U.USERID
        WHERE  M.COLLABORATIONSPACEID = @CollaborationSpaceId
          AND  M.STATUS               = 1
          AND  M.DATABASENAME         = @DatabaseName
        ORDER BY M.ROLE ASC, M.CREATEDON ASC";

    // UX_TCOLLABORATIONSPACEMEMBER_SPACE_USER — checked in BLL before insert to surface a clear
    // "already a member" error rather than relying solely on the DB constraint violation.
    public const string GET_MEMBER = @"
        SELECT
            M.COLLABORATIONSPACEMEMBERID AS CollaborationSpaceMemberId,
            M.COLLABORATIONSPACEID       AS CollaborationSpaceId,
            M.USERID                     AS UserId,
            U.USERCODE                   AS UserCode,
            U.USERNAME                   AS UserName,
            M.ROLE                       AS Role,
            M.TENANTID                   AS TenantId,
            M.DATABASENAME               AS DatabaseName,
            M.DATABASETYPE               AS DatabaseType,
            M.STATUS                     AS Status,
            M.CREATEDBYID                AS CreatedById,
            M.CREATEDON                  AS CreatedOn,
            M.MODIFIEDBYID               AS ModifiedById,
            M.MODIFIEDON                 AS ModifiedOn
        FROM   TCOLLABORATIONSPACEMEMBER M
        LEFT JOIN MUSER U ON M.USERID = U.USERID
        WHERE  M.COLLABORATIONSPACEID = @CollaborationSpaceId
          AND  M.USERID               = @UserId
          AND  M.STATUS               = 1
          AND  M.DATABASENAME         = @DatabaseName";

    // "Add Member" picker — active MUSER rows not already an active member of this space.
    public const string GET_SELECTLIST_MEMBER = @"
        SELECT U.USERID AS UserId, U.USERCODE AS UserCode, U.USERNAME AS UserName
        FROM   MUSER U
        WHERE  U.DATABASENAME = @DatabaseName
          AND  NOT EXISTS (SELECT 1 FROM TCOLLABORATIONSPACEMEMBER M
                            WHERE M.COLLABORATIONSPACEID = @CollaborationSpaceId
                              AND M.USERID = U.USERID AND M.STATUS = 1)
        ORDER BY U.USERNAME ASC";

    public const string ADD_MEMBER = @"
        INSERT INTO TCOLLABORATIONSPACEMEMBER (
            COLLABORATIONSPACEMEMBERID, COLLABORATIONSPACEID, USERID, ROLE,
            TENANTID, DATABASENAME, DATABASETYPE, STATUS,
            CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @CollaborationSpaceMemberId, @CollaborationSpaceId, @UserId, @Role,
            @TenantId, @DatabaseName, @DatabaseType, @Status,
            @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
        )";

    // Hard delete — same precedent as TCOMMENTREACTION's DeleteCommentReaction, so a removed
    // member can be re-added later without the unique key blocking the insert.
    public const string REMOVE_MEMBER = @"
        DELETE FROM TCOLLABORATIONSPACEMEMBER
        WHERE  COLLABORATIONSPACEID = @CollaborationSpaceId
          AND  USERID               = @UserId
          AND  DATABASENAME         = @DatabaseName";
}