routes.py

# -*- coding: utf-8 -*-
 
# routes are defined here, then imported in __init__.py
 
from flask import render_template, flash, redirect, session, request, jsonify, make_response, url_for, Response, Flask
from flask_limiter import Limiter
from flask_limiter.util import get_remote_address
import markupsafe
 
import sqlalchemy
from sqlalchemy.orm.session import make_transient
from sqlalchemy.orm.attributes import flag_modified
 
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func
#import requests
 
from bs4 import BeautifulSoup
import lxml
 
from app import app, socketio, db
 
from app.forms import BuildRegisterForm, BuildLoginForm, Build2FAForm, BuildNewProjectForm, BuildAllProjectSessionForm, BuildOldProjectForm, BuildNewSessionForm, BuildTilesSetForm, BuildEditsessionform, BuildOldTileSetForm, BuildConfigSessionForm, BuildConnectionsForm, BuildRetreiveSessionForm, BuildAdminForm
 
# Import email utilities
from app.email_utils import generate_verification_token, send_verification_email, verify_token, delete_sent_email, generate_verification_code, send_2FAcode_email, send_new_register_email
 
import app.models as models # DB management
import json, os, pprint
#import distutils
#import shutil
# import gzip,base64
import socket
 
import logging
import code
 
from flask_socketio import emit, join_room, rooms
from flask_cors import CORS
from flask_cors import cross_origin
 
#import werkzeug.exceptions
from werkzeug.utils import secure_filename
 
import sys,re,traceback
import datetime,time
import random
 
import tempfile, filecmp
import tarfile
import configparser
 
import pandas as pd
 
sys.path.append(os.path.abspath('../TVDatabase'))
from TVDb import tvdb
tvdb.session=db.session
 
logging.debug("Flask Route start app config : "+str(app.config))
 
# - PCA -
# importing the module analysis-and-treatment-of-data
sys.path.append(os.path.abspath('app'))
from Anatreada import anatreada
 
 
# Read config file
TVrunDir='/.tiledviz'
TVconf=TVrunDir+"/tiledviz.conf"
configExist=False
if (os.path.isdir(TVrunDir)):
    if (os.path.isfile(TVconf)):
        configExist=True
else:
    os.mkdir(TVrunDir)
    mode = os.stat(TVrunDir).st_mode
    mode -= (mode & (stat.S_IRWXG | stat.S_IRWXO))
    os.chmod(TVrunDir,mode)
 
if (configExist):
    TVconfig = configparser.ConfigParser()
    TVconfig.optionxform = str
    TVconfig.read(TVconf)
 
    # default activation for 2FA
    is2FaActivated=json.loads(TVconfig['TVWeb']['is2FaActivated'].lower())
 
    # defaut access to HPC frontend ('ssh' for direct ssh or 'rebound' to use one or more gateways)
    authchoice=json.loads(TVconfig['TVWeb']['AuthChoice'])
 
    # Time sleep for waiting loops in seconds :
    timeAlive=int(TVconfig['TVWeb']['timeAlive'])
 
    # Time for create and start connections in seconds
    TimeConnection=int(TVconfig['TVWeb']['TimeConnection'])
 
    # Boolean for admin page :
    really_delete=json.loads(TVconfig['TVWeb']['really_delete'].lower())
 
    # Link expired duree in seconds
    LinkExpiredAfterSec=int(TVconfig['TVWeb']['LinkExpiredAfterSec'])
 
    # Global rate limit for all pages
    GlobalRateLimit=json.loads(TVconfig['TVWeb']['GlobalRateLimit'])
 
    # More vulnerable pages rate limit (register, login)
    VulnerableRateLimit=json.loads(TVconfig['TVWeb']['VulnerableRateLimit'])
 
else:
    # default activation for 2FA
    is2FaActivated=True
 
    # defaut access to HPC frontend ('ssh' for direct ssh or 'rebound' to use one or more gateways)
    authchoice='ssh'
 
    # Time sleep for waiting loops in seconds :
    timeAlive=5
 
    # Time for create and start connections in seconds
    TimeConnection=30
 
    # Boolean for admin page :
    really_delete=True
 
    # Link expired duree in seconds
    LinkExpiredAfterSec=240
 
    # Global rate limit for all pages
    GlobalRateLimit=["200 per day", "50 per hour"]
 
    # More vulnerable pages rate limit (register, login)
    VulnerableRateLimit="15/hour;5/minute;1/second"
 
 
# Rate Limit
limiter = Limiter(
    get_remote_address,
    app=app,
    default_limits=GlobalRateLimit,
    storage_uri="memory://",
)
 
# A global list for roles in projects
valid_roles = ['owner', 'editor', 'viewer']
valid_manage_members=["owner"]
valid_manage_project=['owner', 'editor']
 
#Separation character for invite links:
linkChar='*'
 
# Logging
logFormatter = logging.Formatter("%(asctime)s - %(threadName)s - %(levelname)s: %(message)s ")
outHandler = logging.StreamHandler(sys.stdout)
outHandler.setLevel(logging.WARNING)
# outHandler.setLevel(logging.DEBUG)
outHandler.setFormatter(logFormatter)
 
import errno,posix
logFIFO="/tmp/logfifo"
if (not os.path.exists(logFIFO)):
    try:
        os.mkfifo(logFIFO)
    except:
        pass
initLOGLEV="WARNING"
logfifo=posix.open(logFIFO, posix.O_NONBLOCK)
LOGLEV=initLOGLEV
def logfun(mystring):
    global LOGLEV
    try:
        nLOGLEV=re.sub(r'\n',r'',posix.read(logfifo,8).decode('utf-8'))
    except Exception as e:
        print("Erreur LOGLEV")
        pass
    if (len(nLOGLEV)>2):
        LOGLEV=nLOGLEV
    if (LOGLEV=="WARNING"):
        logging.warning(mystring)
    elif (LOGLEV=="INFO"):
        logging.info(mystring)
    elif (LOGLEV=="DEBUG"):
        logging.debug(mystring)
    elif (LOGLEV=="ERROR"):
        logging.error(mystring)
    else:
        logging.warning(mystring)
        print("LOGLEV "+LOGLEV)
 
# Shared variables for threads in server
clients = [] # Array to store clients
room_dict = {} # Dict to store rooms (and sub-arrays for clients in each room)
cookie_persistence = False # Opt-in (TODO: better in config?)
config = {}
 
tiles_data={}
tiles_data["nodes"]=[]
 
jsontransfert={}
 
# For display in form list, specify length of elements
usernamel=str(min(20,models.Users.name.type.length))
sessionl=str(min(60,models.Sessions.name.type.length))
tilesetl=str(min(80,models.TileSets.name.type.length))
projectl=str(min(15,models.Projects.name.type.length))
connectionh=str(min(40,models.Connections.host_address.type.length))
datel=str(19)
descrl=str(min(62,models.Projects.description.type.length))
 
# 2FAcode
codes2FA={}
 
class FormUser():
    __slots__=["data","iseditor"]
    def __init__(self,data,iseditor):
        self.data=data
        self.iseditor=iseditor
 
# Global functions : creation, copy and delete DB elements
 
def myflush():
    outHandler.flush()
    sys.stdout.flush()
 
# Test login user :
def get_user_id(fun,username):
    try:
        user=db.session.query(models.Users.id).filter_by(name=session["username"]).one()
    except:
        flash(fun+" requested : User must login !")
        return redirect(url_for("login"))
    return user[0]
 
# Project Member Management Utility Functions
 
def get_user_projects(user_id):
    """
    Get all projects for a user with their roles
    Returns a list of tuples: (project, role_type)
    """
    try:
        projects_query = db.session.query(
            models.Projects,
            models.ProjectMembers.role_type
        ).join(
            models.ProjectMembers,
            models.Projects.id == models.ProjectMembers.project_id
        ).filter(
            models.ProjectMembers.user_id == user_id
        )
 
        return projects_query.all()
    except Exception as e:
        logging.error(f"Error getting user projects: {str(e)}")
        return []
 
def is_project_owner(project_id, user_id):
    """
    Check if user is owner of the project
    """
    try:
        membership = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=user_id,
            role_type='owner'
        ).first()
        return membership is not None
    except Exception as e:
        logging.error(f"Error checking project ownership: {str(e)}")
        return False
 
def can_manage_project(project_id, user_id):
    """
    Check if user can manage the project (owner or editor)
    """
    try:
        membership = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=user_id
        ).first()
        return membership and membership.role_type in valid_manage_project
    except Exception as e:
        logging.error(f"Error checking project management permissions: {str(e)}")
        return False
 
 
def can_edit_project(project_id, user_id):
    """
    Alias helper for edit-level permissions on a project.
    Editing is allowed for roles: owner, admin, editor.
    """
    return can_manage_project(project_id, user_id)
def can_access_project(project_id, user_id):
    """
    Check if user can access the project (any role including viewer and guest)
    """
    try:
        membership = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=user_id
        ).first()
        return membership is not None
    except Exception as e:
        logging.error(f"Error checking project access permissions: {str(e)}")
        return False
 
def get_project_members(project_id):
    """
    Get all members of a project with their details
    """
    try:
        from sqlalchemy.orm import joinedload
 
        members = db.session.query(models.ProjectMembers).options(
            joinedload(models.ProjectMembers.user)
        ).filter(
            models.ProjectMembers.project_id == project_id
        ).order_by(
            models.ProjectMembers.role_type,
            models.ProjectMembers.user_id
        ).all()
 
        return members
    except Exception as e:
        logging.error(f"Error getting project members: {str(e)}")
        return []
 
def add_project_member(project_id, user_id, role_type='viewer'):
    """
    Add a user as member to a project
    """
    try:
        # Check if user is already a member
        existing_member = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=user_id
        ).first()
 
        if existing_member:
            return False, "User is already a member of this project"
 
        # Validate role type
        if role_type not in valid_roles:
            return False, f"Invalid role type. Must be one of: {', '.join(valid_roles)}"
 
        # Create new member
        new_member = models.ProjectMembers(
            project_id=project_id,
            user_id=user_id,
            role_type=role_type
        )
 
        db.session.add(new_member)
        db.session.commit()
 
        return True, "Member added successfully"
 
    except Exception as e:
        db.session.rollback()
        logging.error(f"Error adding project member: {str(e)}")
        return False, f"Error adding member: {str(e)}"
 
def remove_project_member(project_id, user_id):
    """
    Remove a user from a project
    """
    try:
        member = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=user_id
        ).first()
 
        if not member:
            return False, "User is not a member of this project"
 
        # Prevent removal of the last owner
        if member.role_type == 'owner':
            owner_count = db.session.query(models.ProjectMembers).filter_by(
                project_id=project_id,
                role_type='owner'
            ).count()
 
            if owner_count <= 1:
                return False, "Cannot remove the last owner of a project"
 
        db.session.delete(member)
        db.session.commit()
 
        return True, "Member removed successfully"
 
    except Exception as e:
        db.session.rollback()
        logging.error(f"Error removing project member: {str(e)}")
        return False, f"Error removing member: {str(e)}"
 
def update_member_role(project_id, user_id, new_role):
    """
    Update a member's role in a project
    """
    try:
        member = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=user_id
        ).first()
 
        if not member:
            return False, "User is not a member of this project"
 
        # Validate role type
        if new_role not in valid_roles:
            return False, f"Invalid role type. Must be one of: {', '.join(valid_roles)}"
 
        # Prevent changing role of the last owner
        if member.role_type == 'owner' and new_role != 'owner':
            owner_count = db.session.query(models.ProjectMembers).filter_by(
                project_id=project_id,
                role_type='owner'
            ).count()
 
            if owner_count <= 1:
                return False, "Cannot change role of the last owner"
 
        member.role_type = new_role
        db.session.commit()
 
        return True, "Member role updated successfully"
 
    except Exception as e:
        db.session.rollback()
        logging.error(f"Error updating member role: {str(e)}")
        return False, f"Error updating role: {str(e)}"
 
def transfer_project_ownership(project_id, current_user_id, new_owner_id):
    """
    Transfer project ownership to another user
    """
    try:
        # Validate current user is owner
        if not is_project_owner(project_id, current_user_id):
            return False, "Only the project owner can transfer ownership"
 
        # Validate new owner exists
        new_owner = db.session.query(models.Users).filter_by(id=new_owner_id).first()
        if not new_owner:
            return False, "New owner user not found"
 
        # Validate new owner is already a member
        new_owner_membership = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=new_owner_id
        ).first()
 
        if not new_owner_membership:
            return False, "The new owner must be a member of the project first"
 
        # Prevent self-transfer
        if current_user_id == new_owner_id:
            return False, "Cannot transfer ownership to yourself"
 
        # Prevent duplicate owners
        if new_owner_membership.role_type == 'owner':
            return False, "The selected user is already an owner of this project"
 
        # Perform ownership transfer
        current_user_membership = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=current_user_id
        ).first()
 
        new_owner_membership.role_type = 'owner'     # Promote new owner
 
        # Update the project's id_users field to reflect the new owner
        project = db.session.query(models.Projects).filter_by(id=project_id).first()
        if project:
            project.id_users = new_owner_id
 
        db.session.commit()
 
        return True, "Ownership transferred successfully"
 
    except Exception as e:
        db.session.rollback()
        logging.error(f"Error transferring ownership: {str(e)}")
        return False, f"Error transferring ownership: {str(e)}"
 
def get_available_users_for_project(project_id):
    """
    Get users who are not yet members of the project
    """
    try:
        # Get current member IDs
        current_member_ids = db.session.query(models.ProjectMembers.user_id).filter_by(
            project_id=project_id
        ).subquery()
 
        # Get available users
        # TODO
        #SAWarning: Coercing Subquery object into a select() for use in IN();
        # please pass a select() construct explicitly
        #  ~models.Users.id.in_(current_member_ids)
        available_users = db.session.query(models.Users).filter(
            ~models.Users.id.in_(current_member_ids)
        ).order_by(models.Users.name).all()
 
        return available_users
    except Exception as e:
        logging.error(f"Error getting available users: {str(e)}")
        return []
 
def sync_user_to_project_and_session(user_id, project_id, session_id, role_type='guest'):
    """
    Synchronize user membership between project and session
    - Add user to project_members if not already a member
    - Add user to many_users_has_many_sessions if not already in session
    """
    try:
        # Check if user is already a project member
        existing_member = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=user_id
        ).first()
 
        if not existing_member:
            # Add user as project member with specified role
            new_member = models.ProjectMembers(
                project_id=project_id,
                user_id=user_id,
                role_type=role_type
            )
            db.session.add(new_member)
            logging.info(f"Added user {user_id} as {role_type} to project {project_id}")
 
        # Check if user is already in session
        existing_session_user = db.session.query(models.t_many_users_has_many_sessions).filter_by(
            id_users=user_id,
            id_sessions=session_id
        ).first()
 
        if not existing_session_user:
            # Add user to session
            session_user = models.t_many_users_has_many_sessions.insert().values(
                id_users=user_id,
                id_sessions=session_id
            )
            db.session.execute(session_user)
            logging.info(f"Added user {user_id} to session {session_id}")
 
        db.session.commit()
        return True, "User synchronized successfully"
 
    except Exception as e:
        db.session.rollback()
        logging.error(f"Error synchronizing user to project and session: {str(e)}")
        return False, f"Error synchronizing user: {str(e)}"
 
def validate_session_project_consistency(session_id):
    """
    Validate that all users in a session are members of the project
    Returns list of inconsistencies found
    """
    try:
        # Get the session and its project
        session_obj = db.session.query(models.Sessions).filter_by(id=session_id).first()
        if not session_obj:
            return [f"Session {session_id} not found"]
 
        project_id = session_obj.id_projects
        if not project_id:
            return [f"Session {session_id} has no associated project"]
 
        # Get all users in the session
        session_users = db.session.query(models.t_many_users_has_many_sessions).filter_by(
            id_sessions=session_id
        ).all()
 
        inconsistencies = []
 
        for session_user in session_users:
            user_id = session_user.id_users
 
            # Check if user is a project member
            is_project_member = db.session.query(models.ProjectMembers).filter_by(
                project_id=project_id,
                user_id=user_id
            ).first()
 
            if not is_project_member:
                user = db.session.query(models.Users).filter_by(id=user_id).first()
                user_name = user.name if user else f"User ID {user_id}"
                inconsistencies.append(f"User {user_name} (ID: {user_id}) is in session but not a project member")
 
        return inconsistencies
 
    except Exception as e:
        logging.error(f"Error validating session-project consistency: {str(e)}")
        return [f"Error during validation: {str(e)}"]
 
def fix_session_project_inconsistencies(session_id, default_role='guest'):
    """
    Fix inconsistencies by adding session users as project members with default role
    """
    try:
        inconsistencies = validate_session_project_consistency(session_id)
        if not inconsistencies:
            return True, "No inconsistencies found"
 
        # Get the session and its project
        session_obj = db.session.query(models.Sessions).filter_by(id=session_id).first()
        project_id = session_obj.id_projects
 
        fixed_count = 0
        for inconsistency in inconsistencies:
            if "is in session but not a project member" in inconsistency:
                # Extract user ID from the inconsistency message
                import re
                match = re.search(r'User .+ \‍(ID: (\d+)\‍)', inconsistency)
                if match:
                    user_id = int(match.group(1))
 
                    # Add user as project member
                    new_member = models.ProjectMembers(
                        project_id=project_id,
                        user_id=user_id,
                        role_type=default_role
                    )
                    db.session.add(new_member)
                    fixed_count += 1
 
        if fixed_count > 0:
            db.session.commit()
            return True, f"Fixed {fixed_count} inconsistencies by adding users as project members"
        else:
            return False, "No inconsistencies could be automatically fixed"
 
    except Exception as e:
        db.session.rollback()
        logging.error(f"Error fixing session-project inconsistencies: {str(e)}")
        return False, f"Error fixing inconsistencies: {str(e)}"
 
def role_project(oldproject,user_obj):
    membership = db.session.query(models.ProjectMembers).filter_by(
        project_id=oldproject.id, user_id=user_obj.id
    ).first() if user_obj and oldproject else None
    role_type = membership.role_type if membership else None
    can_manage_members = role_type in valid_manage_members
    can_edit_session = role_type in valid_manage_project
    return can_edit_session, can_manage_members, role_type, membership
 
def role_session(oldsession,user_obj):
    membership = db.session.query(models.ProjectMembers).filter_by(
        project_id=oldsession.id_projects, user_id=user_obj.id
    ).first() if user_obj and oldsession else None
    role_type = membership.role_type if membership else None
    can_manage_members = role_type in valid_manage_members
    can_edit_session = role_type in valid_manage_project
    return can_edit_session, can_manage_members, role_type, membership
 
def can_create_invite_links(user_id, project_id):
    """
    Check if a user can create invitation links for a project
    Returns (can_create, reason)
    """
    try:
        # Check if user is a project member
        membership = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=user_id
        ).first()
 
        if not membership:
            return False, "User is not a project member"
 
        # Check role permissions (restrict to owner)
        if membership.role_type in valid_manage_members:
            return True, f"User has {membership.role_type} role"
        else:
            return False, f"Role '{membership.role_type}' cannot create invitation links"
 
    except Exception as e:
        logging.error(f"Error checking invite permissions: {str(e)}")
        return False, f"Error checking permissions: {str(e)}"
 
# copy users in Session
def copy_users_session(newsession,oldusers):
    if ( len(oldusers) > 0 ):
        # register only known oldusers
        for newuser in oldusers:
            thisuserq=db.session.query(models.Users).filter_by(name=newuser.data)
            userexists=db.session.query(thisuserq.exists())
            if userexists.first():
                user=thisuserq.first()
                # don't register a user two times
                if (user not in newsession.users):
                    logging.debug("This user not in the list : {}. We add her.him.".format(user.name))
                    newsession.users.append(user)
                    db.session.commit()
 
                iseditor=newuser.iseditor
                projectsession=newsession.projects
                projectmembers=db.session.query(models.ProjectMembers).filter_by(project_id=projectsession.id).all()
                a_member=db.session.query(models.ProjectMembers).filter_by(project_id=projectsession.id,user_id=user.id)
                is_already_a_member=a_member.count()
                if (is_already_a_member == 1 and iseditor):
                    hisrole=a_member[0].role_type
                    if (not (hisrole=='owner' or hisrole=='editor')):
                        logging.debug("For project %s %d user %s's role %s will be upgrading for editor."
                                        % (projectsession.name,projectsession.id,user.name,hisrole))
                        a_member[0].role_type="editor"
                        db.session.commit()
                elif (is_already_a_member == 0):
                    if (iseditor):
                        hisrole="editor"
                    else:
                        hisrole="viewer"
                    new_member = models.ProjectMembers(
                        project_id=projectsession.id,
                        user_id=user.id,
                        role_type=hisrole
                    )
                    logging.warning("For project %s %d user %s will be added as project member with role %s"
                                        % (projectsession.name,projectsession.id,user.name,hisrole))
                    db.session.add(new_member)
                    db.session.commit()
            else:
                errorstr="This user doesn't exists : {}. We can't add him.".format(newuser.data)
                flash(errorstr)
                logging.warning(errorstr)
 
# Define new session
def create_newsession(sessionname, description, projectid, oldusers):
    creation_date=datetime.datetime.now()
    newsession = models.Sessions(name=str(sessionname),
                                description=str(description),
                                id_projects=projectid,
                                creation_date=creation_date)
 
    exist=db.session.query(models.Sessions.id).filter(models.Sessions.name.like(sessionname)).first() is not None
 
    if (not exist):
        lastsession=db.session.query(models.Sessions.id).order_by(models.Sessions.id.desc()).first()
        if ( lastsession ):
            newsession.id=lastsession.id+1
        else:
            newsession.id=1
        db.session.commit()
        copy_users_session(newsession,oldusers)
        db.session.commit()
 
        # Validate consistency after session creation
        inconsistencies = validate_session_project_consistency(newsession.id)
        if inconsistencies:
            logging.warning(f"Found inconsistencies in newly created session {newsession.name}: {inconsistencies}")
            # Try to fix inconsistencies automatically
            fix_success, fix_message = fix_session_project_inconsistencies(newsession.id, default_role='viewer')
            if fix_success:
                logging.info(f"Fixed session inconsistencies: {fix_message}")
            else:
                logging.error(f"Failed to fix session inconsistencies: {fix_message}")
    else:
        newsession=db.session.query(models.Sessions).filter(models.Sessions.name.like(sessionname)).one()
 
    session["sessionname"]=str(sessionname)
 
    return newsession,exist
 
# Define new TileSet
def create_newtileset(tilesetname, thesession, type_of_tiles, datapath, creation_date):
    newtileset = models.TileSets(name=tilesetname,
                                type_of_tiles = type_of_tiles,
                                Dataset_path = datapath,
                                creation_date=creation_date)
 
    exist=db.session.query(models.TileSets.id).filter_by(name=tilesetname).scalar() is not None
 
    if (not exist):
        # Last TileSet id +1
        lasttileset=db.session.query(models.TileSets.id).order_by(models.TileSets.id.desc()).first()
        if ( lasttileset ):
            newtileset.id=lasttileset.id+1
        else:
            newtileset.id=1
        db.session.commit()
        thesession.tile_sets.append(newtileset)
        db.session.commit()
    else:
        newtileset=db.session.query(models.TileSets).filter_by(name=tilesetname).one()
    return newtileset,exist
 
# Convert Tile fron json file structure to database object
def convertTile(Mynode,tilesetname,connectionbool,urlbool,datapath):
    try:
        url=Mynode["url"]
    except Keyerror as e:
        traceback.print_exc(file=sys.stderr)
        raise(e)
 
    ConnectionPort=0
    if (urlbool and len(datapath) > 0):
        # Detect if path is already in tiles source url
        searchPath=re.search(r''+datapath,url)
        # if no add Dataset_path in url
        if (not searchPath):
            url=datapath+Mynode["url"]
    elif (connectionbool):
        searchPort=re.search(r'port=\d+',url)
        if (searchPort):
            ConnectionPort=int(searchPort.group().replace('port=',''))
    try:
        title=Mynode["title"]
    except :
        title=Mynode["name"]
    try:
        name=Mynode["name"]
    except:
        name=Mynode["title"]
 
    comment=""
    try:
        comment=Mynode["comment"]
    except:
        pass
 
    tags=[]
    try:
        searchtsn=re.search(r''+tilesetname,str(Mynode["tags"]))
        if (not searchtsn):
            if (type(Mynode["tags"]) == 'str'):
                tags=[tilesetname,Mynode["tags"]]
            else:
                tags=[tilesetname]+Mynode["tags"]
        else :
            tags=Mynode["tags"]
    except:
        #traceback.print_exc(file=sys.stderr)
        tags=[tilesetname]
        pass
 
    variable=""
    try:
        variable=Mynode["variable"]
    except:
        pass
 
    pos_px_x=-1
    pos_px_y=-1
    try:
        pos_px_x=Mynode["pos_px_x"]
        pos_px_y=Mynode["pos_px_y"]
    except:
        pass
 
    IdLocation=-1
    try:
        IdLocation=Mynode["IdLocation"]
    except:
        pass
 
    return title,name,comment,tags,variable,pos_px_x,pos_px_y,IdLocation,url,ConnectionPort
 
# Copy a connection when copy a TileSet
# => copy connection + config files +     vnctransfert=json.loads(session["connection"+str(idconnection)]) + session["connection"+str(idconnection)]
# TODO message to connection user owner grid : "Are you OK to copy your connection for tileset.name ?"
def copy_connection(oldtileset,newtileset,newsessionname):
    oldconnection=oldtileset.connections
    user_id=get_user_id("copy_connection",session["username"])
 
    message = '{"oldtilesetid":'+str(newtileset.id)+',"oldsessionname":"'+session["sessionname"]+'"}'
 
    #TODO : session from/to right user for connection
    if (oldconnection.id == None ):
        return
    else:
        idconnection=oldconnection.id
    if ( not "connection"+str(idconnection) in session):
        flash("You must use createconnection button after have given job script file and configuration files or tarball.")
        logging.error("You (user "+str(user_id)+") don't have connection information in your personal cookie for this connection : "+str(idconnection))
        return
 
    #TODO : vncpassword from/to right user for connection
    if  (user_id != oldconnection.id_users) :
        flash("You can not access to this connection. You are not its owner.")
        owner=db.session.query(models.Users).filter_by(id=oldconnection.id_users).one().name
        you=db.session.query(models.Users).filter_by(id=user_id).one().name
        logging.error("You (user "+you+") can not access to this connection owned by user "+owner)
        return redirect(url_for(".edittileset",message=message))
 
    creation_date=datetime.datetime.now()
    newconnection = models.Connections(host_address=oldconnection.host_address,
                                      auth_type=oldconnection.auth_type,
                                      container=oldconnection.container,
                                      scheduler=oldconnection.scheduler,
                                      scheduler_file=oldconnection.scheduler_file,
                                      id_users=user_id,
                                      creation_date= creation_date)
 
    lastconnection=db.session.query(models.Connections.id).order_by(models.Connections.id.desc()).first()
    if ( lastconnection ):
        newconnection.id=lastconnection.id+1
    else:
        newconnection.id=1
    db.session.add(newconnection)
    db.session.commit()
 
    user_path=os.path.join("/TiledViz/TVFiles",str(user_id))
    olddir=os.path.join(user_path,str(idconnection))
    newdir=os.path.join(user_path,str(newconnection.id))
    os.system("mkdir "+newdir)
 
    config_files={}
    for key in oldconnection.config_files:
        filetmp=oldconnection.config_files[key]
        newtmp=filetmp.replace(olddir,newdir)
        os.system("cp "+filetmp+" "+newtmp)
        config_files[key]=newtmp
 
    newconnection.config_files=config_files
    flag_modified(newconnection,"config_files")
    newconnection.scheduler_file=oldconnection.scheduler_file
    newtileset.id_connections=newconnection.id
    db.session.commit()
 
    vnctransfert=json.loads(session["connection"+str(idconnection)])
    vncpassword=vnctransfert["vncpassword"]
    session["connection"+str(newconnection.id)]=' {"callfunction":"edittileset",'+'"args":{"oldsessionname":"'+newsessionname+'","oldtilesetid":"'+str(newtileset.id)+'"}, "vncpassword":"'+vncpassword+'"}'
 
    return
 
# Copy a mirror connection and tileset files
def copy_tileset_connection(tileset,tileset1,sessionname ):
    oldconnection=tileset.connections
    if (oldconnection):
        copy_connection(tileset,tileset1,sessionname)
    user_id=get_user_id("copy_tileset_connection",session["username"])
    user_path=os.path.join("/TiledViz/TVFiles",str(user_id))
    olddir=os.path.join(user_path,str(tileset.id_connections))
    newdir=os.path.join(user_path,str(tileset1.id_connections))
 
    config_files={}
    for key in tileset.config_files:
        filetmp=tileset.config_files[key]
        newtmp=filetmp.replace(olddir,newdir)
        os.system("cp "+filetmp+" "+newtmp)
        config_files[key]=newtmp
 
    tileset1.config_files=config_files
    tileset1.launch_file=tileset.launch_file
 
 
# Function to launch connection page
def launch_connection(theTS, theConnect, myhost_address, myauth_type, mycontainer, myscheduler):
    try:
        logging.warning("editconnection: "
                        +str(session["username"])+" ; "
                        +str(myhost_address)+" ; "
                        +str(myauth_type)+" ; "
                        +str(mycontainer)+" ; "
                        +str(myscheduler)+" ; "
                        +str(theTS.id)+" ; "
                        +str(theConnect.id))
        myflush()
 
        #  Wait NbTimeAlive for TVSecure to get VNC view to give connection again.
        NbTimeAlive = 20
        passpath="/home/connect"+str(theConnect.id)+"/vncpassword"
        logging.warning("Go to vnc with path "+passpath)
 
        count=0
        while(True):
            if (count > NbTimeAlive):
                strerror="Connection has never been reach. Go back to TileSet."
                logging.error(strerror)
                flash(strerror)
                message = '{"oldtilesetid": "'+str(theTS.id)+'"}'
                logging.warning(strerror+" message "+message)
                return redirect(url_for(".edittileset",message=message))
            count=count+1
            logging.debug("count = "+str(count))
            # GET VNC password in
            # security problem here if server is attacked ?
            time.sleep(timeAlive)
            #os.system("ls -la "+passpath)
            if (os.path.isfile(passpath)):
                with open(passpath,'r') as f:
                    vncpassword=re.sub(r'\n',r'',f.read())
                f.close()
                logging.debug("and password : "+vncpassword)
            else:
                logging.error("File not found in connection container "+passpath)
                vncpassword=""
 
            message = '{"oldtilesetid":'+str(theTS.id)+',"connectionid":'+str(theConnect.id)+',"sessionname":"'+session["sessionname"]+'"}'
            session["connection"+str(theConnect.id)]=' {"callfunction":"edittileset",'+'"args":{"oldsessionname":"'+str(session["sessionname"])+'","oldtilesetid":"'+str(theTS.id)+'"}, "vncpassword":"'+vncpassword+'"}'
 
            logging.warning("editconnection in session : "+str(session["connection"+str(theConnect.id)])+" message :"+str(message))
            #TODO logging.debug
            myflush()
            return message
    except Exception:
        traceback.print_exc(file=sys.stderr)
 
 
# Define new session
def save_session(oldsessionname, newsuffix, newdescription, alltiles):
    creation_date=datetime.datetime.now()
    oldsession=db.session.query(models.Sessions).filter(models.Sessions.name.like(oldsessionname)).one()
    projectid=oldsession.id_projects
    # TODO : max length of Session.name (=80) ?
    # mais parent with date may be too long
    #=> notion of heritage for session and tilsets in DB
    newsessionname=session["sessionname"]+'_'+newsuffix
    newsession = models.Sessions(name=newsessionname,
                                description=newdescription,
                                id_projects=projectid,
                                creation_date=creation_date)
    lastsession=db.session.query(models.Sessions.id).order_by(models.Sessions.id.desc()).first()
    if ( lastsession ):
        newsession.id=lastsession.id+1
    else:
        newsession.id=1
    db.session.commit()
 
    oldusers=oldsession.users
    for user in oldusers:
        # Use sync function to ensure consistency between project and session
        sync_success, sync_message = sync_user_to_project_and_session(
            user_id=user.id,
            project_id=newsession.id_projects,
            session_id=newsession.id,
            role_type='viewer'  # Default role for users copied to sessions
        )
        if not sync_success:
            logging.warning(f"Failed to sync user {user.name} to copied session {newsession.name}: {sync_message}")
        else:
            logging.info(f"Successfully synced user {user.name} to copied session {newsession.name}")
 
    newsession.config = oldsession.config
    flag_modified(newsession,"config")
    db.session.commit()
 
    # Validate consistency after session copy
    inconsistencies = validate_session_project_consistency(newsession.id)
    if inconsistencies:
        logging.warning(f"Found inconsistencies in copied session {newsession.name}: {inconsistencies}")
        # Try to fix inconsistencies automatically
        fix_success, fix_message = fix_session_project_inconsistencies(newsession.id, default_role='viewer')
        if fix_success:
            logging.info(f"Fixed copied session inconsistencies: {fix_message}")
        else:
            logging.error(f"Failed to fix copied session inconsistencies: {fix_message}")
 
    logging.warning("All tilesets :"+str([ ts.name for ts in oldsession.tile_sets]))
 
    alltilesjson = alltiles["nodes"]
    for tileset in oldsession.tile_sets:
        # copy tilesets
        tilesetname=tileset.name
        logging.debug("copy tileset from :"+tileset.name)
 
        urlbool=False
        connectionbool=False
        if (tileset.type_of_tiles == "URL"):
            urlbool=True
        elif(tileset.type_of_tiles == "CONNECTION"):
            # Creation of the tiles and launch connection to remote machine.
            connectionbool=True
 
        datapath=tileset.Dataset_path
        tileset1,exist=create_newtileset(tilesetname+'_'+newsuffix, newsession,
                                   tileset.type_of_tiles, datapath, creation_date)
 
        if (connectionbool):
            # Copy a mirror connection
            logging.error("copy config data :"+tileset.launch_file+"  "+str(tileset.config_files))
 
            copy_tileset_connection(tileset,tileset1,newsession.name)
            if (tileset1.connection):
                logging.error("copy id connection :  "+str(tileset1.id_connections))
 
            logging.error("copy config data 1 :"+tileset1.launch_file+"  "+str(tileset1.config_files))
 
 
        if (not exist):
            try:
                db.session.add(tileset1)
                db.session.commit()
            except Exception:
                traceback.print_exc(file=sys.stderr)
        logging.warning("add tileset1 :"+tileset1.name)
 
        for tile in tileset.tiles:
            try :
                i=next(i for i, item in enumerate(alltilesjson) if (item["title"] == tile.title))
            except StopIteration:
                i=-1
            logging.debug("new tile :"+tile.title+" i "+str(i))
            if (i > -1):
                Mynode=alltilesjson[i]
                #print("Mynode :",str(Mynode)," type ",str(type(Mynode))," type tags ",str(type(Mynode["tags"])))
                title,name,comment,tags,variable,pos_px_x,pos_px_y,IdLocation,url,ConnectionPort = \
                    convertTile(Mynode,tilesetname,connectionbool,urlbool,datapath)
                tile.tags=tags
                tile.source= {"name" : name,
                              "connection" : ConnectionPort,
                              "url" : url,
                              "variable": variable}
                flag_modified(tile,"source")
                tile.pos_px_x= pos_px_x
                tile.pos_px_y= pos_px_y
                tile.IdLocation=IdLocation
                tileset1.tiles.append(tile)
                db.session.commit()
            else:
                pass # do nothing if tile is not found in oldtileset ?
 
    newsession.config = oldsession.config
    db.session.commit()
    return newsession
 
def delelement(thistable, chosenElement, elementid):
    thiselement=db.session.query(thistable).filter_by(id=elementid)
    logging.warning("Delete this %s %d : %s" % (chosenElement,elementid,str(thiselement)))
    if (really_delete):
        thiselement.delete()
 
def remove_this_session(sessionid):
    thissession=db.session.query(models.Sessions).filter_by(id=sessionid).scalar()
    # Suppress all link with any tileset
    for ts in thissession.tile_sets:
        thissession.tile_sets.remove(ts)
    # Suppress all link with any user
    logging.warning('in remove_this_session All users :'+str(thissession.users))
    logging.warning("in remove_this_session Link "+str(db.session.query(models.t_many_users_has_many_sessions).filter_by(id_sessions=sessionid).all()))
    for us in thissession.users:
        thissession.users.remove(us)
    db.session.commit()
    logging.warning('in remove_this_session All users after commit :'+str(thissession.users))
 
    logging.warning("in remove_this_session Link after commit "+str(db.session.query(models.t_many_users_has_many_sessions).filter_by(id_sessions=sessionid).all()))
    for link in db.session.query(models.t_many_users_has_many_sessions).filter_by(id_sessions=sessionid).all():
        link.delete()
    delelement(models.Sessions, "session", sessionid)
    db.session.commit()
 
 
def remove_this_project(projectid):
    """
    Remove a project and all its associated data
    """
    try:
        thisproject = db.session.query(models.Projects).filter_by(id=projectid).first()
        if not thisproject:
            logging.warning(f"Project {projectid} not found for deletion")
            return False
 
        # Suppress all sessions of this project
        project_sessions = db.session.query(models.Sessions).filter_by(id_projects=projectid).all()
        for thissession in project_sessions:
            remove_this_session(thissession.id)
 
        # Remove all project members
        db.session.query(models.ProjectMembers).filter_by(project_id=projectid).delete()
 
        # Remove the project itself
        delelement(models.Projects, "project", projectid)
        db.session.commit()
 
        logging.info(f"Project {projectid} and all associated data removed successfully")
        return True
 
    except Exception as e:
        db.session.rollback()
        logging.error(f"Error removing project {projectid}: {str(e)}")
        return False
 
def remove_this_user(userid):
    thisuser=db.session.query(models.Users).filter_by(id=userid).one()
 
    # Search connection wich have this user as owner
    user_connections=db.session.query(models.Connections).filter_by(id_users=userid).all()
    for thisconnection in user_connections:
        # TODO : use remove_this_connection(oldtileset,idconnection,userid) to suppress tmp files in TVFiles
        # Possible to recover TileSet from connection.id ?
        flash("Suppress element connection number %d." % (thisconnection.id))
        delelement(models.Connections, "connection", thisconnection.id)
 
    # Search session where this user is present
    user_session=db.session.query(models.Sessions).filter(models.Sessions.users.any(id=userid)).all()
    for thissession in user_session:
        thissession.users.remove(thisuser)
 
    # Search project wich have this user as owner
    # TODO : give the possibility to change owner of the project ?
    user_project=db.session.query(models.Projects).filter_by(id_users=userid).all()
    for thisproject in user_project:
        remove_this_project(thisproject.id)
 
    delelement(models.Users, "user", userid)
 
def remove_this_connection(oldtileset,idconnection,user_id):
    oldconnection=oldtileset.connections
    oldtilesetid=oldtileset.id
    logging.warning("Remove connection of tileset %d" % (oldtilesetid))
 
    if (oldconnection.id != idconnection):
        logging.error("id %d to be removed is not this tileset %d id %d." % (idconnection, oldtilesetid, oldconnection.id))
        return
    # Build connection path
    user_path=os.path.join("/TiledViz/TVFiles",str(user_id))
    connectionpath=os.path.join(user_path,str(idconnection))
 
    for (dirpath, dirname, filelist) in os.walk(connectionpath):
        for filename in filelist:
            strrm="rm -f "+os.path.join(dirpath,filename)
            os.system(strrm)
            logging.warning("Remove file for tileset "+oldtileset.name+" : "+os.path.join(dirpath,filename))
        os.rmdir(dirpath)
        logging.warning("Remove dir for tileset "+oldtileset.name+" : "+dirpath)
 
    oldtileset.id_connections=None
    #oldtileset.type_of_tiles == None
    oldtileset.config_files=""
    flag_modified(oldtileset,"config_files")
    oldconnection.config_files=""
    flag_modified(oldconnection,"config_files")
    logging.warning("removeconnection: "
                    +str(session["username"])+" ; "
                    +str(oldtilesetid)+" ; "
                    +str(idconnection))
    db.session.commit()
    myflush()
 
    session["connection"+str(idconnection)]=""
    del(session["connection"+str(idconnection)])
 
def myrender():
    myargs={}
    myargs["notlogin"]=True
    myargs["username"]=""
    if ("username" in session):
        myargs["username"]=session["username"]
        if (session["username"]!="Anonymous"):
            myargs["notlogin"]=False
 
            User=db.session.query(models.Users).filter_by(name=session["username"]).one_or_none()
            if User is None:
                # User doesn't exist in database anymore (cookie corrupted or user deleted)
                # Clear the session and treat as Anonymous
                logging.warning(f"User '{session['username']}' in session doesn't exist in database. Clearing session.")
                session.pop("username", None)
                session.pop("is_client_active", None)
                myargs["username"]="Anonymous"
                myargs["notlogin"]=True
                userAdmin=False
            else:
                userAdmin=User.is_admin
        else:
            userAdmin=False
    else:
        userAdmin=False
    myargs["isadmin"]=userAdmin
    return myargs
 
# ====================================================================
# Index/home page
@app.route('/', methods=['GET', 'POST']) # decorators for routes ; all these ones will lead to index
@app.route('/index', methods=['GET', 'POST'])
@app.route('/home', methods=['GET', 'POST'])
def index():
    try:
        #logging.warning(str(session))
        try:
            user = {"username" : session["username"] } # Test for cookie?
        except:
            user = {"username" : "Anonymous"}
            session["username"]="Anonymous"
        if (session["username"] != "Anonymous"):
            session["is_client_active"]=True
        if ( "projectname" in session ):
            project = session["projectname"]
        else:
            project = ""
        if ("sessionname" in session ):
            psession = session["sessionname"]
        else:
            psession=""
    except KeyError as e: # If session["username"] does not exist (no cookie yet)
        logging.error("Home error : "+e)
        # If the cookie is not present
        project = "noproject"
        psession = "nosession"
        session["username"]="Anonymous"
        user = {"username" : session["username"] }
        session["projectname"]=project
        session["sessionname"]=psession
        session["is_client_active"]=False
 
    return render_template("main_template.html", **(myrender()), title="TiledViz home", user=user, project=project, session=psession)
 
 
# ====================================================================
# Register
@app.route('/register', methods=["GET", "POST"])
@limiter.limit(VulnerableRateLimit, methods=["POST"])
def register():
    showlogin=False
    showexist=False
    showknown=False
 
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            myform = BuildRegisterForm()()
        else:
            User=db.session.query(models.Users).filter_by(name=session["username"]).one()
            myform = BuildRegisterForm(Username=User.name,
                                       Useremail=User.mail,
                                       Usercomp=User.compagny,
                                       Usermanager=User.manager
                                       )()
    else:
        myform = BuildRegisterForm()()
 
    if myform.validate_on_submit():
        logging.warning("Register new user.")
        myusername = myform.username.data
        # if (showlogin):
        #     flash("Login requested for user {} in project {}, remember_me={}".format(myform.username.data, myform.projectname.data, myform.remember_me.data))
        #     showlogin=False
        #     return render_template("main_login.html", title="TiledViz register", form=myform)
        try:
            exists = db.session.query(models.Users.id).filter_by(name=myusername).scalar() is not None
        except Exception:
            exists=False
        if exists:
            if (showexist):
                flash("Known user {}, remember_me={}".format(myform.username.data, myform.remember_me.data))
                showexist=False
                return render_template("main_login.html", **(myrender()), title="TiledViz register", form=myform)
            logging.warning("username already exists.")
 
            if (myform.newpassword.data):
                if ("username" in session):
                    if (session["username"] == "Anonymous"):
                        flash("You can't change password if you are connected as Anonymous.")
                        logging.warning("You can't change password if you are connected as Anonymous.")
                        return redirect("/login")
                else:
                    flash("You can't change password if you are not connected : User must login !")
                    logging.warning("You can't change password if you are not connected : User must login !")
                    return redirect("/login")
 
                logging.warning("Renew password for user {}.".format(myusername))
                User=db.session.query(models.Users).filter_by(name=myusername).one()
                hashpass, salt=tvdb.passprotected(myform.password.data)
                creation_date=datetime.datetime.now()
                User.creation_date=str(creation_date)
                User.salt=salt
                User.password=hashpass
                User.dateverified=str(creation_date)
                db.session.commit()
                user_id=User.id
            else:
                hashPassword,hashSalt=db.session.query(models.Users.password,models.Users.salt).filter_by(name=myusername)
                testP=tvdb.testpassprotected(models.Users,myusername,myform.password.data,hashPassword,hashSalt)
                if (testP):
                    logging.info("Correct password !")
                    session["username"] = myusername
                    session["is_client_active"]=True
                    user_id=get_user_id("login",session["username"])
                    if (showknown):
                        flash(Markup("Correct Login for user {} remember_me={}".format(myform.username.data, myform.remember_me.data)))
                        showknown=False
                        return render_template("main_login.html", **(myrender()), title="TiledViz register", form=myform)
                    user = {"username" : session["username"] }
                    session["is_client_active"]=True
                else:
                    if (showknown):
                        flash("You have entered an already existing username, but wrong password for user {}".format(myform.username.data))
                        showknown=False
                        return render_template("main_login.html", **(myrender()), title="TiledViz register", form=myform)
 
                    flash("This username {} already exists and passwd is incorrect : ".format(session["username"]))
                    return render_template("main_login.html", **(myrender()),
                                           title="TiledViz register",
                                           form=myform)
        else:
            hashpass, salt=tvdb.passprotected(myform.password.data)
 
            creation_date=datetime.datetime.now()
 
            # Create the user first
            user = models.Users(name=str(myusername),
                               creation_date=str(creation_date),
                               mail=str(myform.email.data),
                               compagny=str(myform.compagny.data),
                               manager=str(myform.manager.data),
                               salt=salt,
                               password=hashpass,
                               dateverified=str(creation_date),
                               is_verified=False,  # User not verified yet
                               is_admin=False)
            db.session.add(user)
            db.session.commit()
            logging.warning("Commit new user.")
 
            # Get real user ID
            user_id = user.id
 
            if is2FaActivated:
                token = generate_verification_token(user_id)
                # Send verification email with correct token
                email_sent = send_verification_email(
                    user_email=myform.email.data,
                    username=myusername,
                    token=token
                )
 
                try:
                    # Query all admins
                    admin_records = db.session.query(models.Users.mail).filter_by(is_admin=True).all()
                    admin_emails = [record.mail for record in admin_records if record.mail]
 
                    if admin_emails:
                        logging.info(f"Sending registration alert to {len(admin_emails)} admins.")
                        send_new_register_email(
                            admin_emails=admin_emails,
                            username=str(myusername),
                            creation_date=str(creation_date),
                            user_email=str(myform.email.data),
                            user_company=str(myform.compagny.data),
                            user_manager=str(myform.manager.data)
                        )
                except Exception as e:
                    logging.error(f"Failed to fetch admins or send admin notification: {e}")
 
                if email_sent:
                    logging.warning("Verification email sent to {}".format(myform.email.data))
                    flash("Registration successful! Please check your email and click the verification link to activate your account.")
                else:
                    # Email failed, remove the created user
                    db.session.delete(user)
                    db.session.commit()
                    logging.error("Failed to send verification email to {}. User deleted.".format(myform.email.data))
                    flash("Registration failed: Unable to send verification email. Please check your email address and try again.")
                    return render_template("main_login.html", **(myrender()),
                        title="TiledViz register",
                        form=myform)
 
                # Set admin if first user
                if (user_id == 1):
                    user.is_admin=True
                    user.is_verified=True  # First user is auto-verified
                    db.session.commit()
                    logging.warning("New user is Admin and auto-verified.")
                    # Auto-login first user
                    session["username"] = myusername
                    session["is_client_active"]=True
                else:
                    # Don't auto-login new users - they need to verify email first
                    logging.info("New user {} needs to verify email before login.".format(myusername))
            else:
                # Bypass verification email and auto-verify all new users when 2FA/verification is disabled
                user.is_verified = True
                if (user_id == 1):
                    user.is_admin = True
                db.session.commit()
                logging.warning(f"New user {myusername} registered and auto-verified without email (is2FaActivated=False).")
                # Auto-login new user immediately
                session["username"] = myusername
                session["is_client_active"] = True
                flash("Registration successful! You are now logged in.")
        cookie_persistence = myform.remember_me.data
        logging.warning("[!] Cookie persistence set to %s" % (str(cookie_persistence)))
 
        # TODO : suppress code below and options because is_verified is not
        logging.debug("Project Choice :"+myform.choice_project.data)
 
        # Check if user is logged in (only first user or existing users)
        if "username" in session and session["username"] != "Anonymous":
            if(myform.choice_project.data == "create"):
                logging.info("Go to create new project for user "+session["username"]+".")
                return redirect("/project")
            else:
                if exists:
                    return redirect("/allsessions")
                else:
                    logging.info("New user "+session["username"]+" : create a new project or ask for invite_link.")
                    flash("New user {} registred : please create a new project or ask another user for an invite_link.".format(session["username"]))
                    if is2FaActivated:
                        return render_template("main_login.html", **(myrender()),
                            title="TiledViz register",
                            form=myform)
                    else:
                        return redirect("/allsessions")
 
        else:
            # New user not logged in - redirect to login
            flash("Please login as {} avec have clicked on received email on {}.".format(myform.username.data,myform.email.data))
            return redirect("/login")
    return render_template("main_login.html", **(myrender()), title="TiledViz register", form=myform)
 
# OR Login
@app.route('/login', methods=["GET", "POST"])
@limiter.limit(VulnerableRateLimit, methods=["POST"])
def login():
    if ("username" not in session):
        session["username"] = "Anonymous"
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            pass  # On laisse passer, on veut afficher le formulaire de login
        else:
            User = db.session.query(models.Users).filter_by(name=session["username"]).first()
            if User:
                exists = User.id is not None
                logging.error(f"User already connected with username {session['username']}.")
 
                if exists:
                    # Check if user email is verified
                    if not User.is_verified:
                        flash("Your email address has not been verified yet. Please check your email and click the verification link.")
                        return render_template("main_login.html", **(myrender()), title="TiledViz login", form=myform)
 
                    hashPassword = User.password
                    hashSalt = User.salt
 
                    myform = BuildLoginForm(session)()
                    myusername = myform.username.data if hasattr(myform, 'username') else session["username"]
                    if (myusername != str(session["username"])):
                        flash(f"User already connected with username {session['username']} but one tried to login as different user {myusername}.\n"+\
                               +"Please logout before login as another user.")
                        logging.error(f"User already connected with username {session['username']} but one tried to login as user {myusername}.")
                        session.pop('username', None)
                        session.pop("is_client_active")
                        return redirect(url_for('index'))
 
 
                    logging.error(f"User {session['username']} already connected.")
                    flash("Your were already connected.")
                    return redirect("/allsessions")
 
    myform = BuildLoginForm(session)()
    if myform.validate_on_submit():
        myusername = myform.username.data
        mypassword = myform.password.data
        user = db.session.query(models.Users).filter_by(name=myusername).first()
        if user:
            # Check if user email is verified
            if not user.is_verified:
                if is2FaActivated:
                    flash("Your email address has not been verified yet. Please check your email and click the verification link.")
                    return render_template("main_login.html", **(myrender()), title="TiledViz login", form=myform)
                else:
                    user.is_verified = True
                    db.session.commit()
 
            if tvdb.testpassprotected(models.Users, myusername, mypassword, user.password, user.salt):
                logging.info('Correct password.')
 
                if is2FaActivated:
                    # Send 2FA email with correct code
                    code = generate_verification_code()
                    email_sent = send_2FAcode_email(
                        user_email=user.mail,
                        username=myusername,
                        code=code
                    )
                    # Create expiration datetime (UTC)
                    expiration_time = datetime.datetime.utcnow() + datetime.timedelta(seconds=360)
                    codes2FA[myusername]={"code":code, "expiration_time":expiration_time}
 
                    strerror=f"We have sent you a security code on your mail. Please check your inbox before UTC {expiration_time}."
                    flash(strerror)
                    message = '{"username": "'+myusername+'", '+\
                        '"newuser": "'+str(myform.newuser.data)+'", "choice_project": "'+myform.choice_project.data+'"}'
                    logging.info(strerror+" message "+message)
                    logging.warning(f"User {myusername} try to connect.")
                    return redirect(url_for(".check2FA",message=message))
                else:
                    session["username"] = myusername
                    session["is_client_active"] = True
 
                    logging.warning(f"User {myusername} has loggined without 2FA.")
 
                    if (str(myform.newuser.data) == "True"):
                        return redirect("/register")
 
                    # Check if there's a pending invite link
                    if "pending_invite_link" in session:
                        link = session.pop("pending_invite_link")
                        return redirect(url_for(".handle_join_with_invite_link", link=link))
 
                    if (myform.choice_project.data == "create"):
                        return redirect("/project")
                    elif (myform.choice_project.data == "connect"):
                        return redirect("/allsessions")
                    else:
                        return redirect("/allsessions")
 
            else:
                flash("Invalid password")
                return redirect("/login")
        else:
            flash("Invalid username")
            return redirect("/login")
 
    return render_template("main_login.html", **(myrender()), title="TiledViz login", form=myform)
 
@app.route('/check2FA', methods=["GET", "POST"])
def check2FA():
    message = json.loads(request.args["message"])
    myusername=message["username"]
    if (myusername in codes2FA):
        code=codes2FA[myusername]["code"]
        expiration_time=codes2FA[myusername]["expiration_time"]
    else:
        flash("User didn't received a 2FA code. Please try to login again.")
        return redirect("/login")
 
    myform = Build2FAForm(session,myusername)()
    if myform.validate_on_submit():
        if (datetime.timedelta(seconds=360) < datetime.datetime.utcnow()-expiration_time):
           flash("Timeout for code verification. Please try to login again.")
           return redirect("/login")
 
        logging.info(f"code created {code} and from form {myform.code.data}")
        if (code == int(myform.code.data)):
            session["username"] = myusername
            session["is_client_active"] = True
 
            logging.info(f"code OK message {message}")
            logging.warning(f"User {myusername} has loggined with 2FA code.")
            codes2FA.pop(myusername)
 
            if (message["newuser"]=="True"): #distutils.util.strtobool(message["newuser"])):
                return redirect("/register")
 
            # Check if there's a pending invite link
            if "pending_invite_link" in session:
                link = session.pop("pending_invite_link")
                return redirect(url_for(".handle_join_with_invite_link", link=link))
 
            if (message["choice_project"] == "create"):
                return redirect("/project")
            elif (message["choice_project"] == "connect"):
                return redirect("/allsessions")
        else:
            flash("Code mismatch. Please try to login again.")
            return redirect("/login")
 
    return render_template("main_login.html", **(myrender()), title="TiledViz login", form=myform)
 
@app.route('/logout')
def logout():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/")
    else:
        flash("User is already not connected !")
        return redirect("/")
    # remove the username from the session if it is there
    session.pop('username', None)
    session.pop("is_client_active")
    return redirect(url_for('index'))
 
@app.route('/test')
def testserver():
    # only test TiledViz server.
    return render_template("test.html")
 
@app.route('/savesession', methods=['GET', 'POST'])
def savesession():
 
    if ("username" in session):
        user_id=get_user_id("Savession",session["username"])
    else:
        flash("You are not connected. You must login before saving a session.")
        return redirect("/login")
 
    if (not "is_client_active" in session):
        flash("You must be connected and not Anonymous to save a session.")
        return redirect("/login")
 
    all_session={"username":session['username'],"is_client_active":session["is_client_active"],
                 "projectname":session["projectname"],"sessionname":session["sessionname"]}
    # logging.error("basic all_session : "+str(all_session))
    for item in session:
        logging.info("item all_session : "+str(item))
        if item in all_session:
            pass
        elif (item == 'csrf_token'):
            pass
        else:
            all_session[item]=session[item]
 
    logging.info("complete all_session : "+str(all_session))
 
    #all_connections=
    # session["connection"+str(idconnection)])
    # session["connection"+str(newconnection.id)]
    json_all_session=json.JSONEncoder().encode(all_session)
 
    if ( request.method == 'POST'):
        flash("Session cookie saved.")
        return redirect("/index")
        #return redirect(url_for('index'))
 
    return render_template("savesession.html", **(myrender()),
                           all_session=json_all_session)
 
@app.route('/retreivesession', methods=["GET", "POST"])
def retreivesession():
 
    if ("username" in session):
        user_id=get_user_id("retreivesession",session["username"])
    else:
        flash("You are not connected. You must login before retreive a session.")
        return redirect("/login")
 
    # if ( session["sessionname"] in  jsontransfert):
    #     if ( "TheJson" in  jsontransfert[session["sessionname"]]):
 
    myform = BuildRetreiveSessionForm()()
 
    if myform.validate_on_submit():
        logging.info("in tileset editor")
 
        if(myform.goback.data):
            logging.warning("go back to home without session.")
            return redirect(url_for(".index"))
 
        if (myform.session_file.data) :
            session_file = myform.session_file.data
            logging.warning("Read session_file :"+myform.session_file.data.filename)
            session_data = json.loads(json.loads(session_file.read().decode('utf-8')))
            logging.warning("Session_data :"+str(session_data))
 
            session["username"]=session_data['username']
            session["is_client_active"]=session_data["is_client_active"]
            session["projectname"]=session_data["projectname"]
            session["sessionname"]=session_data["sessionname"]
            all_session={"username":session['username'],"is_client_active":session["is_client_active"],
                 "projectname":session["projectname"],"sessionname":session["sessionname"]}
 
            logging.info("basic all_session : "+str(all_session))
            for item in session_data:
                if item in all_session:
                    pass
                else:
                    session[item]=session_data[item]
 
            flash("Session cookie restored.")
            return redirect("/index")
 
        # if myform.editjson.data:
        #     # jsontransfert[session["sessionname"]]={"callfunction": '{"function":"edittileset",'+'"args":{"oldtilesetid":"'+str(oldtilesetid)+'"}}',
        #     #                                        "TheJson":json_tiles}
        #     pass
 
    #    return render_template("retreivesession.html")
    return render_template("main_login.html", **(myrender()), title="Retreive saved session for TiledViz", form=myform)
 
 
# Create new project
@app.route('/project', methods=["GET", "POST"])
def project():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
        user_id=get_user_id("Project",session["username"])
        flash("Create new or use an old project for user {}".format(session["username"]))
    else:
        return redirect("/login")
 
    # All projects for user using improved utility function
    printstr="{0:\xa0<"+projectl+"."+projectl+"}|\xa0{2:\xa0<"+datel+"."+datel+"}\xa0|\xa0{1:\xa0<"+descrl+"."+descrl+"}|\xa0{3:\xa0<"+descrl+"}"
 
    # Get all projects where user is a member (any role)
    user_projects = get_user_projects(user_id)
 
    myprojects=[]
    myprojects.append(('NoChoice',printstr.format("Project name","Description","Date and Time","All sessions")))
 
    try:
        for project, role_type in user_projects:
            ListsessionsTheproject=db.session.query(models.Sessions.name).filter_by(id_projects=project.id)
            allsessionsname=[ asessionTheproject.name for asessionTheproject in ListsessionsTheproject ]
            thedate=project.creation_date.isoformat().replace("T"," ") if project.creation_date else "Unknown"
 
            # Add role information to the display
            project_display_name = f"{project.name} ({role_type})"
 
            myprojects.append((str(project.id),
                               printstr.format(
                                     project_display_name,
                                     project.description or "",
                                     thedate,
                                     str(allsessionsname))
                               ))
    except Exception as e:
        logging.error(f"Error loading user projects: {str(e)}")
        pass
 
    myform = BuildNewProjectForm(myprojects)()
 
    # UI filtering: adapt action choices based on selected project role
    try:
        # Determine selected project id (if any) and user's role for it
        selected_val = myform.chosen_project.data
        selected_project_id = None
        try:
            if selected_val and selected_val != "NoChoice":
                selected_project_id = int(selected_val)
        except Exception:
            selected_project_id = None
 
        if selected_project_id:
            role = db.session.query(models.ProjectMembers.role_type).filter_by(
                project_id=selected_project_id,
                user_id=user_id
            ).scalar()
            can_edit_role = role in ["owner", "admin", "editor"]
            if not can_edit_role:
                myform.action_sessions.choices = [("use","Use an existing session for the grid")]
                myform.action_sessions.default = "use"
    except Exception:
        pass
    if myform.validate_on_submit():
        logging.info("in project")
 
        if (myform.chosen_project.data=="NoChoice"):
            if (myform.projectname.data != ""):
                project_id = db.session.query(models.Projects.id).filter_by(name=myform.projectname.data).scalar()
            else:
                logging.warning("You must create a new project or choose an old one.")
                flash("You must create a new project or choose an old one.")
                return redirect("/project")
 
        else:
            project_id = int(myform.chosen_project.data)
 
        exists = project_id is not None
        logging.debug("Project exists "+str(exists)+" id : "+str(project_id))
 
        if exists:
            # Check if user has access to this project
            if not can_access_project(project_id, user_id):
                flash("You don't have permission to access this project!")
                return redirect("/project")
 
            if (myform.chosen_project.data == "NoChoice"):
                session["projectname"]=myform.projectname.data
            else:
                session["projectname"]=db.session.query(models.Projects.name).filter_by(id=project_id).scalar()
 
            oldproject=db.session.query(models.Projects).filter_by(id=project_id).scalar()
            # Permission: only owner may manage members (add users)
            try:
                current_user_obj = db.session.query(models.Users).filter_by(name=session["username"]).first()
                can_edit_session, can_manage_members, role_type, membership = role_project(oldproject,current_user_obj)
            except Exception:
                can_manage_members = False
                can_edit_session = False
 
            logging.debug("Chosen project : "+str(session["projectname"]))
            # Route behavior depends on role: only editors+ can create/modify
            user_can_edit = can_manage_project(project_id, user_id)
            if(myform.action_sessions.data == "create"):
                if (can_edit_session):
                    logging.warning("Create new session ")
                    flash("Create new session for user {}".format(session["username"]))
                    return redirect("/newsession")
                else:
                    sentence="You don't have permission to create new session on this project!"
                    logging.warning(sentence)
                    flash(sentence)
                    return redirect("/project")
            else:
                logging.debug("Use old sessions (view)")
                flash("Use an old session for user {}".format(session["username"]))
                return redirect("/oldsessions")
        elif (myform.chosen_project.data=="NoChoice"):
            creation_date=datetime.datetime.now()
            screation_date=str(creation_date)
            logging.error("create project date "+screation_date)
 
            try:
                project = models.Projects(name=str(myform.projectname.data),
                                         creation_date=screation_date,
                                         id_users=user_id,
                                         role_type="owner",
                                         description=myform.description.data)
                db.session.add(project)
                db.session.flush()  # Get the project ID
 
                # Create the owner membership record using utility function
                success, message = add_project_member(project.id, user_id, 'owner')
                if not success:
                    db.session.rollback()
                    flash(f"Error creating project: {message}")
                    return redirect("/project")
 
                session["projectname"]=myform.projectname.data
                logging.debug("Project created : create new session ")
                return redirect("/newsession")
 
            except Exception as e:
                db.session.rollback()
                logging.error(f"Error creating project: {str(e)}")
                flash(f"Error creating project: {str(e)}")
                return redirect("/project")
        else:
            logging.error("Error for chosen project.")
            return redirect("/project")
 
    return render_template("main_login.html", **(myrender()), title="New project TiledViz", form=myform)
 
# List all my old projects and after all sessions I am in
@app.route('/admin', methods=["GET", "POST"])
def admin():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
        user_id=get_user_id("admin",session["username"])
        logging.warning("Admin page with user {}".format(session["username"]))
        if (really_delete):
            flash("Admin page with user {}\nALERT : click any suppress buttons will remove elements in DB.".format(session["username"]))
        else:
            flash("Admin page with user {}".format(session["username"]))
        logging.warning("User id {}".format(user_id))
    else:
        flash("Admin page : User must login !")
        return redirect("/login")
 
    User=db.session.query(models.Users).filter_by(name=session["username"]).one()
    userAdmin=User.is_admin
    if not userAdmin:
        flash("You must be an administrator to access this page.")
        return redirect("/")
 
    message='{"username": '+session["username"]+'}'
    logging.info("in administration page.")
 
    if (userAdmin):
        allusers = db.session.query(models.Users).all()
        logging.debug("All users :"+str([ theuser.name for theuser in allusers]))
 
        printstr="{0:\xa0<"+usernamel+"."+usernamel+"}|\xa0{1:\xa0<"+datel+"."+datel+"}\xa0|\xa0{2:\xa0<"+descrl+"."+descrl+"}"
        listallusers=[]
        listallusers.append(('NoChoice',printstr.format("User name","Date and Time","Description")))
 
        for thisuser in allusers:
            thedate=thisuser.creation_date.isoformat().replace("T"," ")
            if (thisuser.mail):
                mail=thisuser.mail
            else:
                mail=""
            if (thisuser.compagny):
                compagny=thisuser.compagny
            else:
                compagny=""
            if (thisuser.manager):
                manager=thisuser.manager
            else:
                manager=""
            Desc=mail+"; "+compagny+"; "+manager
            Desc=Desc[:int(descrl)]
            listallusers.append(
                (str(thisuser.id),printstr.
                 format(str(thisuser.name),
                        thedate,Desc)
                )
            )
 
        # Handle admin status changes
        if request.method == 'POST':
            if 'toggle_admin' in request.form:
                user_id = request.form.get('user_id')
                try:
                    user = db.session.query(models.Users).filter_by(id=user_id).one()
                    user.is_admin = not user.is_admin
                    db.session.commit()
                    flash(f"Admin status updated for user {user.name}")
                except Exception as e:
                    flash(f"Error updating admin status: {str(e)}")
                return redirect(url_for('admin'))
 
    allprojects = db.session.query(models.Projects).all()
    logging.debug("All projects :"+str([ theproject.name for theproject in allprojects]))
 
    printstr="{0:\xa0<"+projectl+"."+projectl+"}|\xa0{1:\xa0<"+usernamel+"."+usernamel+"}\xa0|\xa0{2:\xa0<"+datel+"."+datel+"}\xa0|\xa0{3:\xa0<"+descrl+"."+descrl+"}"
    listallprojects=[]
    listallprojects.append(('NoChoice',printstr.format("Project name","Owner","Date and Time","Description")))
 
    for thisproject in allprojects:
        thedate=thisproject.creation_date.isoformat().replace("T"," ")
        Desc=thisproject.description
 
        # Get owner via project_members
        owner_member = db.session.query(models.ProjectMembers).filter(
            models.ProjectMembers.project_id == thisproject.id,
            models.ProjectMembers.role_type == 'owner'
        ).first()
 
        owner_name = 'Unknown'
        if owner_member and owner_member.user:
            owner_name = owner_member.user.name
        elif thisproject.id_users:  # Fallback on old relation
            old_owner = db.session.query(models.Users).filter_by(id=thisproject.id_users).first()
            if old_owner:
                owner_name = old_owner.name
 
        listallprojects.append(
            (str(thisproject.id),printstr.
             format(str(thisproject.name), owner_name, thedate, Desc)
            )
        )
 
 
    allsessions = db.session.query(models.Sessions).all()
    logging.debug("All sessions :"+str([ thesession.name for thesession in allsessions]))
 
    printstr="{0:\xa0<"+sessionl+"."+sessionl+"}|\xa0{1:\xa0<"+datel+"."+datel+"}\xa0|\xa0{2:\xa0<"+descrl+"."+descrl+"}"
    listallsessions=[]
    listallsessions.append(('NoChoice',printstr.format("Session name","Date and Time","Description")))
 
    for thissession in allsessions:
        thedate=thissession.creation_date.isoformat().replace("T"," ")
        Desc=thissession.description
        listallsessions.append(
            (str(thissession.id),printstr.
             format(str(thissession.name),
                    thedate,Desc)
            )
        )
 
    projects = db.session.query(models.Projects).filter_by(id_users=user_id).all()
    logging.debug("My projects :"+str([ theproject.name for theproject in projects]))
 
    printstr="{0:\xa0<"+projectl+"."+projectl+"}|\xa0{1:\xa0<"+datel+"."+datel+"}\xa0|\xa0{2:\xa0<"+descrl+"."+descrl+"}"
    listmyprojects=[]
    listmyprojects.append(('NoChoice',printstr.format("Project name","Date and Time","Description")))
 
    for thisproject in projects:
        thedate=thisproject.creation_date.isoformat().replace("T"," ")
        Desc=thisproject.description
        listmyprojects.append(
                (str(thisproject.id),printstr.
                 format(str(thisproject.name),
                        thedate,Desc)
                )
            )
 
    # All sessions own of those projects
    mysessions=[]
    try:
        for theproject in projects:
            ListsessionsTheproject=db.session.query(models.Sessions.name).filter_by(id_projects=theproject.id).all()
            [ mysessions.append((theproject.name,ListsessionTheproject)) for ListsessionTheproject in ListsessionsTheproject ]
    except:
        pass
    logging.debug("My sessions :"+str(mysessions))
 
    printstr="{1:\xa0<"+sessionl+"."+sessionl+"}|{0:\xa0<"+projectl+"."+projectl+"}|\xa0{2:\xa0<"+datel+"."+datel+"}\xa0|\xa0{3:\xa0<"+descrl+"."+descrl+"}"
    listmyprojectssession=[]
    listmyprojectssession.append(('NoChoice',printstr.format("Project name","Session name","Date and Time","Description")))
    listmysession=[]
    for thissessions in mysessions:
 
        for thissession in thissessions[1]:
            listmysession.append(thissession)
            thedate="1970-01-01"
            try:
                thedate=db.session.query(models.Sessions.creation_date).filter_by(name=str(thissession)).scalar().isoformat().replace("T"," ")
            except:
                pass
            SessDesc=db.session.query(models.Sessions).filter_by(name=thissession).scalar().description
            thissessionid=db.session.query(models.Sessions.id).filter_by(name=str(thissession)).one()
            listmyprojectssession.append(
                (str(thissessionid),printstr.
                 format(str(thissessions[0]),
                        str(thissession),
                        thedate,SessDesc)
                )
            )
 
 
    # # All sessions this user has been invited to
    # listsessions=[]
 
    # invite_sessions = db.session.query(models.Sessions.name).filter(models.Sessions.users.any(id=user_id)).all()
    # printstr="{0:\xa0<"+sessionl+"."+sessionl+"}|\xa0{1:\xa0<"+datel+"."+datel+"}\xa0|\xa0{2:\xa0<"+descrl+"."+descrl+"}"
    # listsessions.append(('NoChoice',printstr.format("Session name","Date and Time","Description")))
    # for thissession in invite_sessions:
    #     logging.debug("Build listsessions for invite_session "+str(thissession.name))
    #     if (thissession.name not in listmysession):
    #         thedate="1970-01-01"
    #         try:
    #             thedate=db.session.query(models.Sessions.creation_date).filter_by(name=thissession.name).scalar().isoformat().replace("T"," ")
    #         except:
    #             pass
    #         SessDesc=db.session.query(models.Sessions).filter_by(name=thissession.name).scalar().description
    #         listsessions.append(
    #             (str(thissession.name),printstr.
    #              format(str(thissession.name),
    #                     thedate, SessDesc)
    #             )
    #         )
 
 
    # list all my Connections
    connections = db.session.query(models.Connections).filter_by(id_users=user_id)
    logging.debug("My connections :"+str([ theconnection.host_address for theconnection in connections]))
 
    printstr="{0:\xa0<"+connectionh+"."+connectionh+"}|\xa0{1:\xa0<"+datel+"."+datel+"}\xa0\xa0\xa0\xa0\xa0|\xa0{2:\xa0<"+datel+"."+datel+"}\xa0\xa0\xa0\xa0|\xa0{3:\xa0<"+datel+"."+datel+"}"
    listmyconnections=[]
    listmyconnections.append(('NoChoice',printstr.format("Host address","Date and Time","id","In session")))
    for thisconnection in connections:
        insession=False
        if ("connection"+str(thisconnection.id) in session):
            insession=True
        listmyconnections.append(
            (str(thisconnection.id),printstr.
             format(str(thisconnection.host_address),
                    str(thisconnection.creation_date),
                    str(thisconnection.id),
                    str(insession))
            )
        )
 
    if (userAdmin):
        listallconnections=[]
        # list all Connections
        allconnections = db.session.query(models.Connections)
        logging.debug("All connections :"+str([ theconnection.host_address for theconnection in allconnections]))
 
        printstr="{0:\xa0<"+usernamel+"."+usernamel+"}|\xa0"+\
            "{1:\xa0<"+connectionh+"."+connectionh+"}|\xa0"+\
            "{2:\xa0<"+datel+"."+datel+"}\xa0\xa0\xa0\xa0\xa0|\xa0"+\
            "{3:\xa0<"+datel+"."+datel+"}\xa0\xa0\xa0\xa0|\xa0"+\
            "{4:\xa0<"+datel+"."+datel+"}"
        listallconnections.append(('NoChoice',printstr.format("Username","Host address","Date and Time","id","In session")))
        for thisconnection in allconnections:
            insession=False
            if ("connection"+str(thisconnection.id) in session):
                insession=True
            theuser=db.session.query(models.Users).filter_by(id=thisconnection.id_users).one().name
            listallconnections.append(
                (str(thisconnection.id),printstr.
                 format(str(theuser),
                        str(thisconnection.host_address),
                        str(thisconnection.creation_date),
                        str(thisconnection.id),
                        str(insession))
                 )
            )
 
    logging.debug("My project sessions :"+str(listmyprojectssession))
    # logging.debug("My invited sessions :"+str(listmysession))
    if (userAdmin):
        myform = BuildAdminForm(listmyprojects,listmyprojectssession,listmyconnections,
                                list_all_users=listallusers,list_all_projects=listallprojects,list_all_sessions=listallsessions,list_all_connections=listallconnections)()
    else:
        myform = BuildAdminForm(listmyprojects,listmyprojectssession,listmyconnections)()
        #,listsessions
    if myform.validate_on_submit():
        objects=[]
        ids=[]
 
        # TODO : GOT TO A NEW PAGE with the list of confirmations of deletations
 
        if (userAdmin):
            if (myform.suprressfreetiles.data):
                flash("All free tiles were suppressed.")
                for freetile in db.session.query(models.t_freetiles).all():
                    delelement(models.Tiles, "tile", freetile[0])
 
            if (myform.suprressUnusedTilesets.data):
                flash("All free tilesets were suppressed.\n You may suppress all free tiles now.")
                for freetilesets in db.session.query(models.t_freetilesets).all():
                    elementid=freetilesets[0]
                    logging.warning("Delete this %s %d : %s" % ("tileset",elementid,str(freetilesets[1])))
                    db.session.query(func.deltileset(freetilesets[0])).all()
                    #delelement(models.TileSets, "tileset", freetilesets[0])
 
        if ( myform.suppressSelected.data):
            if (userAdmin):
                if (myform.all_users.data != "NoChoice"):
                    chosenObject="user"
                    elementid=int(myform.all_users.data)
                    logging.warning("Admin suppress %s %d " % (chosenObject,elementid))
                    flash("Admin suppress element %s number %s." % (chosenObject,elementid))
                    objects.append(chosenObject)
                    ids.append(elementid)
 
                    remove_this_user(elementid)
 
                if (myform.all_projects.data != "NoChoice"):
                    chosenObject="project"
                    elementid=int(myform.all_projects.data)
                    logging.warning("Admin suppress %s %d " % (chosenObject,elementid))
                    flash("Admin suppress element %s number %s." % (chosenObject,elementid))
                    objects.append(chosenObject)
                    ids.append(elementid)
 
                    remove_this_project(elementid)
 
                if (myform.all_sessions.data != "NoChoice"):
                    chosenObject="session"
                    elementid=int(myform.all_sessions.data)
                    logging.warning("Admin suppress %s %d " % (chosenObject,elementid))
                    flash("Suppress element %s number %s." % (chosenObject,elementid))
                    objects.append(chosenObject)
                    ids.append(elementid)
 
                    remove_this_session(elementid)
 
 
 
            if (myform.chosen_project.data != "NoChoice"):
                chosenObject="project"
                elementid=int(re.sub(r'[(),]','',myform.chosen_project.data))
                logging.warning("Chosen my %s %d " % (chosenObject,elementid))
                flash("Suppress element %s number %d." % (chosenObject,elementid))
                objects.append(chosenObject)
                ids.append(elementid)
 
                delelement(models.Projects, chosenObject, elementid)
 
            if (myform.chosen_project_session.data != "NoChoice"):
                chosenObject="mysession"
                elementid=int(re.sub(r'[(),]','',myform.chosen_project_session.data))
                logging.warning("Chosen my %s %d " % (chosenObject,elementid))
                flash("Suppress element %s number %d." % (chosenObject,elementid))
                objects.append(chosenObject)
                ids.append(elementid)
 
                thissession=db.session.query(models.Sessions).filter_by(id=elementid).scalar()
                remove_this_session(elementid)
 
            # if (myform.chosen_session_invited.data != "NoChoice"):
            #     logging.warning("Chosen session invited "+str(myform.chosen_session_invited.data))
            #     chosenObject="invitedsession"
            #     elementid=myform.chosen_session_invited.data
            #     flash("Suppress element %s number %s." % (chosenObject,elementid))
 
            if (myform.chosen_user_connection.data != "NoChoice"):
                chosenObject="connection"
                elementid=int(myform.chosen_user_connection.data)
                logging.warning("Chosen my %s %d " % (chosenObject,elementid))
                flash("Suppress element %s number %d." % (chosenObject,elementid))
                objects.append(chosenObject)
                ids.append(elementid)
 
                try:
                    thisConnection=db.session.query(models.Connections).filter_by(id=elementid).one()
                    user_id=thisConnection.id_users
                    oldtileset=db.session.query(models.TileSets).filter_by(id_connections=elementid).one()
                    if (oldtileset.connections.id==elementid):
                        remove_this_connection(oldtileset,elementid,user_id)
                    else:
                        delelement(models.Connections, chosenObject, elementid)
                except:
                    delelement(models.Connections, chosenObject, elementid)
 
        if (myform.suppressAllMyConnections.data):
            flash("All my connections were suppressed {}".format(session["username"]))
 
            for thisconnection in connections:
                chosenObject="connection"
                objects.append(chosenObject)
                ids.append(thisconnection.id)
                try:
                    user_id=thisconnection.id_users
                    oldtileset=db.session.query(models.TileSets).filter_by(id_connections=thisconnection.id).one()
                    if (oldtileset.connections.id==thisconnection.id):
                        remove_this_connection(oldtileset,thisconnection.id,user_id)
                    else:
                        delelement(models.Connections, chosenObject, thisconnection.id)
                except:
                    delelement(models.Connections, chosenObject, thisconnection.id)
 
 
        if (userAdmin):
 
            if (myform.chosen_connections.data != "NoChoice"):
                chosenObject="connection"
                elementid=int(myform.chosen_connections.data)
                logging.warning("Chosen my %s %d " % (chosenObject,elementid))
                flash("Suppress element %s number %d." % (chosenObject,elementid))
                objects.append(chosenObject)
                ids.append(elementid)
 
                try:
                    thisConnection=db.session.query(models.Connections).filter_by(id=elementid).one()
                    user_id=thisConnection.id_users
                    oldtileset=db.session.query(models.TileSets).filter_by(id_connections=elementid).one()
                    if (oldtileset.connections.id==elementid):
                        remove_this_connection(oldtileset,elementid,user_id)
                    else:
                        delelement(models.Connections, chosenObject, elementid)
                except:
                    delelement(models.Connections, chosenObject, elementid)
 
 
            if (myform.suppressAllConnections.data):
                flash("All connections for all users were suppressed.")
                # TODO : use remove_this_connection(oldtileset,idconnection,user_id) to suppress tmp files in TVFiles
                # Possible to recover TileSet from connection.id ?
                for thisconnection in allconnections:
                    chosenObject="connection"
                    objects.append(chosenObject)
                    ids.append(thisconnection.id)
                    try:
                        user_id=thisconnection.id_users
                        oldtileset=db.session.query(models.TileSets).filter_by(id_connections=thisconnection.id).one()
                        if (oldtileset.connections.id==thisconnection.id):
                            remove_this_connection(oldtileset,thisconnection.id,user_id)
                        else:
                            delelement(models.Connections, chosenObject, thisconnection.id)
                    except:
                        delelement(models.Connections, chosenObject, thisconnection.id)
 
 
        db.session.commit()
 
        return redirect("/admin")
 
    return render_template("main_login.html", **(myrender()), title="Admin for user.", form=myform, message=message)
 
 
# List all my old projects and after all sessions I am in
@app.route('/allsessions', methods=["GET", "POST"])
def allmysessions():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
 
        flash("All projects and sessions for user {}".format(session["username"]))
        logging.warning("All projects and sessions for user {}".format(session["username"]))
        user_id=get_user_id("allsavessions",session["username"])
        logging.warning("User id {}".format(user_id))
    else:
        flash("All projects and sessions : User must login !")
        return redirect("/login")
 
    message='{"username": '+session["username"]+'}'
    logging.info("in allsessions")
 
    # All projects own by user
    projects = db.session.query(models.Projects).filter_by(id_users=user_id)
    logging.debug("My projects :"+str([ theproject.name for theproject in projects]))
 
    # All sessions own of those projects
    mysessions=[]
    try:
        for theproject in projects:
            ListsessionsTheproject=db.session.query(models.Sessions.name).filter_by(id_projects=theproject.id)
            [ mysessions.append((theproject.name,ListsessionTheproject)) for ListsessionTheproject in ListsessionsTheproject ]
    except:
        pass
    logging.debug("My sessions :"+str(mysessions))
 
    printstr="{1:\xa0<"+sessionl+"."+sessionl+"}|{0:\xa0<"+projectl+"."+projectl+"}|\xa0{2:\xa0<"+datel+"."+datel+"}\xa0|\xa0{3:\xa0<"+descrl+"."+descrl+"}"
    listmyprojectssession=[]
    listmyprojectssession.append(('NoChoice',printstr.format("Project name","Session name","Date and Time","Description")))
    listmysession=[]
    for thissessions in mysessions:
        for thissession in thissessions[1]:
            listmysession.append(thissession)
            dbSession=db.session.query(models.Sessions).filter(models.Sessions.name.like(thissession)).first()
            logging.debug(f"Session : {thissession} {dbSession}")
            try:
                logging.debug(f"        :               {dbSession.name}")
                thedate=dbSession.creation_date.isoformat().replace("T"," ")
            except:
                logging.debug(f"Error date session {thissession}")
                thedate="1972-08-05"
                pass
            try:
                SessDesc=dbSession.description
            except:
                logging.debug(f"Error desc session {thissession}")
                SessDesc="Unknown"
                pass
 
            listmyprojectssession.append(
                (str(thissession),printstr.
                 format(str(thissessions[0]),
                        str(thissession),
                        thedate,SessDesc)
                )
            )
 
    # All sessions this user has been invited to
    listsessions=[]
 
    invite_sessions = db.session.query(models.Sessions.name).filter(models.Sessions.users.any(id=user_id)).all()
    printstr="{0:\xa0<"+sessionl+"."+sessionl+"}|\xa0{1:\xa0<"+datel+"."+datel+"}\xa0|\xa0{2:\xa0<"+descrl+"."+descrl+"}"
    listsessions.append(('NoChoice',printstr.format("Session name","Date and Time","Description")))
    for thissession in invite_sessions:
        if (thissession.name not in listmysession):
            dbSession=db.session.query(models.Sessions).filter(models.Sessions.name.like(thissession.name)).first()
            logging.debug(f"iSession : {thissession} {dbSession}")
            try:
                logging.debug(f"        :               {dbSession.name}")
                thedate=dbSession.creation_date.isoformat().replace("T"," ")
            except:
                logging.debug(f"Error date invited session {thissession}")
                thedate="1972-08-05"
                pass
            try:
                SessDesc=dbSession.description
            except:
                logging.debug(f"Error desc invited session {thissession}")
                SessDesc="Unknown"
                pass
            listsessions.append(
                (str(thissession.name),printstr.
                 format(str(thissession.name),
                        thedate, SessDesc)
                )
            )
 
    logging.debug("My project sessions :"+str(listmyprojectssession))
    logging.debug("My invited sessions :"+str(listsessions))
    if (len(listmyprojectssession) == 0 and len(listmysession) == 0 and len(invite_sessions) == 0):
        flash("You are not in any project or session now. Please create your first one or ask somebody to invite you.")
        return redirect(url_for(".index"))
 
    myform = BuildAllProjectSessionForm(listmyprojectssession,listsessions)()
 
    # UI filtering: hide edit button if user has no edit rights on any project
    try:
        has_edit_rights = db.session.query(models.ProjectMembers).filter(
            models.ProjectMembers.user_id == user_id,
            models.ProjectMembers.role_type.in_(["owner","admin","editor"])
        ).count() > 0
        if not has_edit_rights and hasattr(myform, 'edit'):
            try:
                myform.edit.render_kw = {"style": "display:none"}
            except Exception:
                pass
    except Exception:
        pass
    if myform.validate_on_submit():
        if (myform.chosen_project_session.data != "NoChoice"):
            logging.debug("Chosen project session "+str(myform.chosen_project_session.data))
            session["sessionname"]=myform.chosen_project_session.data
        elif (myform.chosen_session_invited.data != "NoChoice"):
            logging.debug("Chosen session invited "+str(myform.chosen_session_invited.data))
            session["sessionname"]=myform.chosen_session_invited.data
        else:
            logging.warning("You must choose a session")
            flash("You didn't select a session or click 'Go' button on search bar.\n"+
                  "You must choose a session in your projects or one you were invited on.")
            return redirect("/allsessions")
 
        logging.warning("Which is session "+str(db.session.query(models.Sessions.id).filter(models.Sessions.name.like(f'{session["sessionname"]}')).first()))
        its_project_id=db.session.query(models.Sessions).filter(models.Sessions.name.like(f'{session["sessionname"]}')).first().id_projects
        session["projectname"]=db.session.query(models.Projects).filter_by(id=its_project_id).scalar().name
        logging.warning("And have project id "+str(its_project_id)+" which is "+str(session["projectname"]))
        session["is_client_active"]=True
        if(myform.edit.data):
            # Back-end guard: only owner/admin/editor may access edit view
            if not can_manage_project(its_project_id, user_id):
                flash("You don't have permission to edit sessions in this project.")
                return redirect("/grid")
            logging.debug("go to edit old session : "+session["sessionname"])
            message = '{"oldsessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".editsession",message=message))
        return redirect("/grid")
 
    return render_template("main_login.html", **(myrender()), title="All projects/sessions TiledViz", form=myform, message=message)
 
 
# List all old sessions for the projectname I am in
@app.route('/oldsessions', methods=["GET", "POST"])
def oldsessions():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Old sessions : User must login !")
        return redirect("/login")
# Old Session page
    if (not "projectname" in session):
        flash("Old sessions : Must define a project first !")
        return redirect("/project")
 
    # Validate user access to the project and restrict actions for viewer/guest
    try:
        Project = db.session.query(models.Projects).filter_by(name=session["projectname"]).first()
        user_id=get_user_id("OldSessionsPerm",session["username"])
        if not Project or not can_access_project(Project.id, user_id):
            flash("You don't have permission to access this project.")
            return redirect("/project")
        user_can_edit = can_manage_project(Project.id, user_id)
    except Exception:
        user_can_edit = False
 
    Project = db.session.query(models.Projects).filter_by(name=session["projectname"]).scalar()
    project_id=Project.id
    project_desc=Project.description
    querysessions = db.session.query(models.Sessions.name).filter_by(id_projects=project_id)
    listsessions=[]
    for thissession in querysessions:
        logging.debug("Build listsessions "+str(thissession[0]))
        listsessions.append((str(thissession[0]),str(thissession[0])))
    oldproject={"name":session["projectname"],
                "description":project_desc}
    logging.debug("Old project : "+str(oldproject["name"])+" list old sessions :"+str(listsessions))
 
    # Permission: only owner may manage members (add users)
    try:
        current_user_obj = db.session.query(models.Users).filter_by(name=session["username"]).first()
        can_edit_session, can_manage_members, role_type, membership = role_project(Project,current_user_obj)
    except Exception:
        can_manage_members = False
        can_edit_session = False
 
    myform = BuildOldProjectForm(oldproject, listsessions, session)()
    if myform.validate_on_submit():
        session["sessionname"]=myform.chosen_session.data
        if(myform.from_session.data=="use"):
            logging.debug("reuse old session : "+myform.chosen_session.data)
            session["is_client_active"]=True
            return redirect("/grid")
        elif(myform.from_session.data=="edit"):
            if not user_can_edit:
                flash("You don't have permission to edit sessions in this project.")
                return redirect("/oldsessions")
            logging.debug("go to edit old session : "+myform.chosen_session.data)
            message = '{"oldsessionname":"'+myform.chosen_session.data+'"}'
            return redirect(url_for(".editsession",message=message))
        elif(myform.from_session.data=="copy"):
            if not user_can_edit:
                flash("You don't have permission to copy sessions in this project.")
                return redirect("/oldsessions")
            logging.debug("go to copy old session : "+myform.chosen_session.data)
            message = '{"oldsessionname":"'+myform.chosen_session.data+'"}'
            return redirect(url_for(".copysession",message=message))
 
    return render_template("main_login.html", **(myrender()), title="Old projects TiledViz", form=myform)
 
# ==> invite an existing user (and connected) with invite_link
# Create new session
@app.route('/newsession', methods=["GET", "POST"])
def newsession():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Create new sessions : User must login !")
        return redirect("/login")
 
    # Permission guard: only owner/admin/editor may create a session in the current project
    try:
        user_id=get_user_id("NewSessionPerm",session["username"])
        Project = db.session.query(models.Projects).filter_by(name=session.get("projectname")).first()
        if not Project or not can_manage_project(Project.id, user_id):
            flash("You don't have permission to create sessions for this project.")
            return redirect("/project")
    except Exception:
        flash("Unable to validate permissions for creating a session.")
        return redirect("/project")
 
    # New or session manager (copy, invite_link a list of connected users ?)
    myform = BuildNewSessionForm()()
    if myform.validate_on_submit():
        if myform.add_users.data:
            myform.users.append_entry()
            flash("New user for user {} in session {}".format(session["username"], myform.sessionname.data))
            return render_template("main_login.html", **(myrender()), title="New session TiledViz", form=myform)
        logging.info("in session")
        id_projects=db.session.query(models.Projects.id).filter_by(name=session["projectname"])
        sessionname=myform.sessionname.data
 
        oldusers=[]
        if ("users" in myform):
            list_users=[]
            i=0
            logging.debug("myform.users : %s" % str(myform.users))
            for newuser in myform.users:
                soup=BeautifulSoup(str(newuser),'lxml')
                outsoup=soup.find_all("input")
                outfind=[ input.get("value") for input in outsoup ]
                outchecked=[ input.get("checked") for input in outsoup ]
                if (len(outfind[0]) > 0):
                    logging.debug("myform.users : %s %s" % (str(outfind[0]),str(outchecked[1] == "")))
                    thisuser=FormUser(data=outfind[0],iseditor=(outchecked[1]==""))
                    list_users.append(thisuser)
 
            ok_list_users="Owner %s add users %s to the session %s" % (session["username"],str(list_users),sessionname)
            logging.debug("ok_list_users : %s " % ok_list_users)
            oldusers=list_users
 
        newsession,exist=create_newsession(sessionname, myform.description.data, id_projects, oldusers)
        if (not exist):
            try:
                db.session.add(newsession)
                db.session.commit()
            except Exception:
                traceback.print_exc(file=sys.stderr)
 
                flash("Session with name {} creation problem.".format(sessionname))
                return render_template("main_login.html", **(myrender()), title="New Session TiledViz", form=myform, message=message)
        else:
            flash("Session with name {} already exists".format(sessionname))
            return render_template("main_login.html", **(myrender()), title="New Session TiledViz", form=myform)
 
        # Create default config for new session
        config_default_file=open("app/static/js/config_default.json",'r')
        json_configs=json.load(config_default_file)
        config_default_file.close()
        newsession.config=json_configs
        db.session.commit()
 
        if myform.Session_config.data:
            message = '{"sessionname":"'+newsession.name+'"}'
            return redirect(url_for(".configsession",message=message))
        else:
            #message='{"username":"'+session["username"]+'","sessionname":"'+session["sessionname"]+'"}'
            #return redirect(url_for(".addtileset",message=message))
            flash("One must validate new session before create a tiledset.")
            message='{"oldsessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".editsession",message=message))
    return render_template("main_login.html", **(myrender()), title="New session TiledViz", form=myform)
 
 
# Copy an old session and edit tilesets
@app.route('/copysession', methods=["GET", "POST"])
def copysession():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Copy session : User must login !")
        return redirect("/login")
 
    if ( not "sessionname" in session ):
        return redirect("/allsessions")
 
    message=json.loads(request.args["message"])
    oldsessionname=message["oldsessionname"]
    oldsession = db.session.query(models.Sessions).filter(models.Sessions.name.like(oldsessionname)).first()
    # Permission: only owner may manage members (add users)
    try:
        current_user_obj = db.session.query(models.Users).filter_by(name=session["username"]).first()
        can_edit_session, can_manage_members, role_type, membership = role_session(oldsession,current_user_obj)
    except Exception:
        can_manage_members = False
        can_edit_session = False
        role_type="viewer"
 
    if (can_edit_session):
        flash("User {} don't have rights to copy session {} with role {}".format(session["username"], myform.sessionname.data, role_type))
        return redirect("/oldsessions")
 
    session["can_manage_members"]=can_manage_members
    session["can_edit_session"]=can_edit_session
    myform = BuildEditsessionform(oldsession,session,edit=False)()
    if myform.validate_on_submit():
        logging.debug("copySessionForm : ")
        # TODO : multiple
        if myform.add_users.data:
            myform.users.append_entry()
            message = '{"oldsessionname":"'+session["sessionname"]+'"}'
            flash("New user avaible for user {} in session {}".format(session["username"], myform.sessionname.data))
            # TODO !! => send invitation to new users ?
            return render_template("main_login.html", **(myrender()), title="Copy session TiledViz", form=myform)
 
        list_users=[]
        i=0
        for newuser in myform.users:
            soup=BeautifulSoup(str(newuser),'lxml')
            outsoup=soup.find_all("input")
            outfind=[ input.get("value") for input in outsoup ]
            outchecked=[ input.get("checked") for input in outsoup ]
            if (len(outfind) > 0):
                logging.debug("myform.users : %s %s" % (str(outfind[0]),str(outchecked[1] == "")))
                thisuser=FormUser(data=outfind[0],iseditor=(outchecked[1]==""))
                list_users.append(thisuser)
 
        newsession,exist=create_newsession(myform.sessionname.data, myform.description.data, oldsession.id_projects, list_users)
 
        if (not exist):
            nbts=len(oldsession.tile_sets)
            for i in range(nbts):
                newsession.tile_sets.append(oldsession.tile_sets[i])
 
            try:
                # if user has not change session.name, it can't be created.
                db.session.add(newsession)
                db.session.commit()
 
                # Validate consistency after session copy
                inconsistencies = validate_session_project_consistency(newsession.id)
                if inconsistencies:
                    logging.warning(f"Found inconsistencies in copied session {newsession.name}: {inconsistencies}")
                    # Try to fix inconsistencies automatically
                    fix_success, fix_message = fix_session_project_inconsistencies(newsession.id, default_role='viewer')
                    if fix_success:
                        logging.info(f"Fixed copied session inconsistencies: {fix_message}")
                    else:
                        logging.error(f"Failed to fix copied session inconsistencies: {fix_message}")
 
            except Exception:
                traceback.print_exc(file=sys.stderr)
 
                message = '{"oldsessionname":"'+oldsessionname+'"}'
                flash("Session with name {} creation problem.".format(newsession.name))
                return render_template("main_login.html", **(myrender()), title="Copy session TiledViz", form=myform, message=message)
        else:
            try:
                flash("You must change session name {} for new session.".format(newsession.data))
            except:
                flash("You must change session name {} for new session.".format(str(newsession)))
            return render_template("main_login.html", **(myrender()), title="Copy Session TiledViz", form=myform)
 
        theaction=myform.tilesetaction.data
 
        # try:
        #     oldtilesetid=int(myform.tilesetchoice.data)
        #     message = '{"oldtilesetid":'+str(tilesetid)+'}'
        # except :
        #     traceback.print_exc(file=sys.stderr)
        #     if ( theaction != "search" and theaction != "copy" ): #and theaction != "useold"
        #             logging.error("You must check a tileset for this action {}".format(theaction))
        #             flash("You must check a tileset for this action {}".format(theaction))
        #             message = '{"oldsessionname":'+newsession.name+'}'
        #             return redirect(url_for(".editsession",message=message))
 
        logging.debug("Action tileset : "+str(message)+" "+theaction)
        if(theaction == "useold"):
            session["is_client_active"]=True
            return redirect("/grid")
        elif myform.Session_config.data:
            message = '{"sessionname":"'+newsession.name+'"}'
            return redirect(url_for(".configsession",message=message))
        elif(theaction == "copy"):
            tilesetid=int(myform.tilesetchoice.data)
            message = '{"oldtilesetid":"'+str(tilesetid)+'"}'
            return redirect(url_for(".copytileset",message=message))
        elif (theaction == "search"):
            message = '{"oldsessionname":"'+newsession.name+'"}'
            return redirect(url_for(".searchtileset",message=message))
    return render_template("main_login.html", **(myrender()), title="Copy session TiledViz", form=myform, message=message)
 
 
# Edit old session
@app.route('/editsession', methods=["GET", "POST"])
def editsession():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Edit session : User must login !")
        return redirect("/login")
 
    message=json.loads(request.args["message"])
    oldsessionname=message["oldsessionname"]
    oldsession = db.session.query(models.Sessions).filter(models.Sessions.name.like(oldsessionname)).first()
    # Permission: only owner may manage members (add users)
    try:
        current_user_obj = db.session.query(models.Users).filter_by(name=session["username"]).first()
        can_edit_session, can_manage_members, role_type, membership = role_session(oldsession,current_user_obj)
    except Exception:
        can_manage_members = False
        can_edit_session = False
 
    session["can_manage_members"]=can_manage_members
    session["can_edit_session"]=can_edit_session
 
    myform = BuildEditsessionform(oldsession,session,edit=True)()
    if myform.validate_on_submit():
        # Redirect to project member management (owner/admin only)
        if hasattr(myform, 'manage_members') and myform.manage_members.data:
            if not can_manage_members:
                message = '{"oldsessionname":"'+oldsessionname+'"}'
                flash("Only owners or admins can manage project members.")
                return render_template("main_login.html", **(myrender()), title="Edit session TiledViz", form=myform, can_manage_members=can_manage_members)
            return redirect(url_for('.project_members', project_id=oldsession.id_projects))
        logging.debug("editSessionForm : ")
 
        if (myform.editusers.data):
            return redirect(url_for('project_members', project_id=oldsession.projects.id))
 
        # - PCA -
        #   |_ my form contains the form the edition tileset
        #   |_ process the anatreada script here ??
        # logging.info("Calling pca_nodes_of_tilesets methode of anatread")
        # logging.info("The user as selected the option : " + myform.has_pca.data)
 
        # - PCA -
        # if has_pca option is True -> make the PCA over all the tisets of the current session
        if myform.has_pca.data == "YES" :
 
            # logging.info("Contruction of nodes of tilesets to process the PCA ...")
 
            # - PCA -
            #   |_ structure of dict_tiles_all_session :
            #
            #       {
            #           "nodes": [
            #               {
            #                   "id": "xxxxxxxxxx",
            #                   "title": "xxxxxxxxxx",
            #                   "tags": [
            #                        "{tagName,minValue,value,maxValue}",
            #                        "{tagName2,minValue,value,maxValue}",
            #                        "tagName3"
            #                    ]
            #                }
            #           ]
            #       }
 
            dict_tiles_all_session = dict()
            dict_tiles_all_session["nodes"] = []
            # ici
            for tileset in oldsession.tile_sets:
                for tile in tileset.tiles:
                    dict_tiles_all_session["nodes"].append( {
                        "id" : tile.id,
                        "title" : tile.title,
                        "tags" : tile.tags
                        } )
 
            # dict_pca_tiles_all_session  : contains a json of dict_tiles_all_session with the group for all the tiles
            # groups_dict                 : contains a json of a dict as this format : {id_tile : group_name}
            json_tiles_text=json.dumps(dict_tiles_all_session)
            logging.error(json_tiles_text)
            myflush()
            dict_pca_tiles_all_session, groups_dict = anatreada.pca_on_multiple_nodes(json_tiles_text)
 
            dict_pca_tiles_all_session = json.loads(dict_pca_tiles_all_session)
            groups_dict = json.loads(groups_dict)
 
            # Update oldession with the new groups
            for i in range(0, len(oldsession.tile_sets)):
                tileset =  oldsession.tile_sets[i]
                for j in range(0, len(tileset.tiles)):
                    try :
                        tile = tileset.tiles[j]
                        tile.tags.append(groups_dict[str(tile.id)])
 
                        flag_modified(tile,"tags")
 
                    except :
                        logging.error("An error occurred : can't find tile id in groups dict ...")
 
            db.session.commit()
 
        if ("add_users" in myform):
            if myform.add_users.data:
                myform.users.append_entry()
                message = '{"oldsessionname":"'+oldsessionname+'"}'
                flash("New user avaible for user {} in session {}".format(session["username"], myform.sessionname.data))
                # TODO !! => send invitation to new users ? => just a mail to give the notif
                return render_template("main_login.html", **(myrender()), title="Edit session TiledViz", form=myform)
 
        if (myform.sessionname.data != oldsessionname):
            message = '{"oldsessionname":"'+oldsessionname+'"}'
            flash("You must NOT change session name to edit session {}".format(oldsessionname))
            return render_template("main_login.html", **(myrender()), title="Edit session TiledViz", form=myform, message=message)
 
        if can_edit_session:
            oldsession.description=str(myform.description.data)
            creation_date=datetime.datetime.now()
            oldsession.creation_date=str(creation_date)
            db.session.commit()
        session["sessionname"]=oldsessionname
 
        if ("users" in myform):
            list_users=[]
            i=0
            logging.debug("myform.users : %s" % str(myform.users))
            for newuser in myform.users:
                soup=BeautifulSoup(str(newuser),'lxml')
                outsoup=soup.find_all("input")
                outfind=[ input.get("value") for input in outsoup ]
                outchecked=[ input.get("checked") for input in outsoup ]
                if (len(outfind[0]) > 0):
                    logging.debug("myform.users : %s %s" % (str(outfind[0]),str(outchecked[1] == "")))
                    thisuser=FormUser(data=outfind[0],iseditor=(outchecked[1]==""))
                    list_users.append(thisuser)
 
            ok_list_users="Owner %s add users %s to the session %s" % (session["username"],str(list_users),oldsessionname)
            logging.debug("ok_list_users : %s " % ok_list_users)
 
            copy_users_session(oldsession,list_users)
            db.session.commit()
 
        if myform.Session_config.data and can_edit_session:
            message = '{"sessionname":"'+oldsessionname+'"}'
            return redirect(url_for(".configsession",message=message))
 
        theaction=myform.tilesetaction.data
 
        ListAllTileSet_ThisSession=[ (str(thistileset.id), thistileset.name) for thistileset in oldsession.tile_sets]
        if (len(ListAllTileSet_ThisSession) > 0):
            try:
                tilesetid=int(myform.tilesetchoice.data)
                message = '{"oldtilesetid":'+str(tilesetid)+'}'
            except Exception:
                #traceback.print_exc(file=sys.stderr)
                if ( theaction not in ["search","copy","create","useold"] and not (hasattr(myform,'edit') and myform.edit.data)):
                    logging.debug("You must check a tileset for this action {}".format(theaction))
                    flash("You must check a tileset for this action {}".format(theaction))
                    message = '{"oldsessionname":"'+oldsessionname+'"}'
                    return redirect(url_for(".editsession",message=message))
            if(hasattr(myform,'edit') and myform.edit.data and can_edit_session):
                logging.debug("message before edittileset "+str(message))
                return redirect(url_for(".edittileset",message=message))
 
        logging.debug("Action tileset : "+str(message)+" "+theaction)
        if(theaction == "useold"):
            session["is_client_active"]=True
            return redirect("/grid")
        elif (theaction == "create") and can_edit_session:
            flash("Tileset requested for user {} in session {}".format(session["username"],session["sessionname"]))
            message='{"username":"'+session["username"]+'","sessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".addtileset",message=message))
        elif(theaction == "copy") and can_edit_session:
            return redirect(url_for(".copytileset",message=message))
        elif(theaction == "search") and can_edit_session:
            message = '{"oldsessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".searchtileset",message=message))
        elif(theaction == "remove") and can_edit_session:
            try:
                thistileset=db.session.query(models.TileSets).filter_by(id=tilesetid).scalar()
                logging.debug("TileSet for remove : "+str(thistileset))
                oldsession.tile_sets.remove(thistileset)
                db.session.commit()
            except Exception:
                traceback.print_exc(file=sys.stderr)
 
                flash("Error remove tileset {}".format(db.session.query(models.TileSets).filter_by(id=tilesetid).scalar().name))
            message = '{"oldsessionname":"'+oldsessionname+'"}'
            return redirect(url_for(".editsession",message=message))
        else:
            # For view-only users trying non-authorized actions
            if not can_edit_session:
                flash("You have view-only permissions on this project.")
                return render_template("main_login.html", **(myrender()), title="Edit session TiledViz", form=myform, message='{"oldsessionname":"'+oldsessionname+'"}')
    return render_template("main_login.html", **(myrender()), title="Edit session TiledViz", form=myform, message=message)
 
# Use json editor for session nodes.json
@app.route('/editnodes', methods=["GET", "POST"])
def editnodes():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Edit nodes : User must login !")
        return redirect("/login")
 
    message=json.loads(request.args["message"])
    oldsessionname=message["oldsessionname"]
    ThisSession = db.session.query(models.Sessions).filter(models.Sessions.name.like(oldsessionnam)).first()
 
    # Permissions: only owner/editor can edit nodes
    try:
        current_user_obj = db.session.query(models.Users).filter_by(name=session["username"]).first()
        membership = db.session.query(models.ProjectMembers).filter_by(
            project_id=ThisSession.id_projects, user_id=current_user_obj.id
        ).first() if current_user_obj and ThisSession else None
        role_type = membership.role_type if membership else None
        can_edit_nodes = role_type in valid_manage_project
    except Exception:
        can_edit_nodes = False
 
    if not can_edit_nodes:
        flash("You do not have permission to edit nodes in this project.")
        return redirect("/grid")
 
    if (type(ThisSession) != type(None)):
            ListAllTileSet_ThisSession=ThisSession.tile_sets
    else:
        logging.warning("You must choose a valid session for editnodes")
        flash("You didn't select a valid session in editnodes.")
        return redirect("/editsession")
 
    session["tilesetnames"]=[ thistileset.name for thistileset in ListAllTileSet_ThisSession ]
    logging.debug("All TileSet for session "+str(session["sessionname"])+" : "+str(session["tilesetnames"]))
    # Main loop to build the grid :
    nbr_of_tiles=0
 
    # build all tiles data vector
    global tiles_data
    tiles_data={}
    tiles_data["nodes"]=[]
    ts=0
    lts=len(ThisSession.tile_sets)
    while (ts < lts):
        thistileset=ThisSession.tile_sets[ts]
        nbtiles=len(thistileset.tiles)
        nbr_of_tiles = nbr_of_tiles + nbtiles
 
        if (thistileset.type_of_tiles == "CONNECTION"):
            oldconnection=thistileset.connections
            out_nodes_json = os.path.join("/TiledViz/TVFiles", str(oldconnection.id_users), str(oldconnection.id),"nodes.json")
            tiledata=[]
            if ( os.path.exists( out_nodes_json ) ):
                try:
                    with open(out_nodes_json) as json_tiles_file:
                        tsjson=json.loads(json_tiles_file.read())
 
                    logging.warning("nodes.json read and OK "+out_nodes_json)
                    tiledata=tsjson["nodes"]
                    for O in tsjson:
                        if ( O != "nodes" ):
                            if ( O in tiles_data ):
                                tiles_data[O]=tiles_data[O]+tsjson[O]
                            else:
                                tiles_data[O]=tsjson[O]
                except Exception as err:
                    traceback.print_exc(file=sys.stderr)
                    strerror="Error from json "+out_nodes_json+" file from connection : "+str(err)
                    logging.error(strerror)
 
        else:
            tiledata=tvdb.encode_tileset(thistileset)
 
        tiles_data["nodes"]=tiles_data["nodes"]+tiledata
        ts=ts+1
    jsontransfert[session["sessionname"]]={"callfunction": '{"function":"show_grid",'+'"args":{"sessionname":"'+oldsessionname+'"}}'}
    jsontransfert[session["sessionname"]]["TheJson"]=tiles_data
 
    return redirect(url_for(".jsoneditor"))
 
 
# List all old tilesets I am in
@app.route('/searchtileset', methods=["GET", "POST"])
def searchtileset():
# Old Tileset page
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Search TileSet : User must login !")
        return redirect("/login")
    try:
        message=json.loads(request.args["message"])
    except json.decoder.JSONDecodeError as e:
        logging.error("message error ! "+str(e))
        logging.error("message : "+str(request.args["message"]))
        traceback.print_exc(file=sys.stderr)
        message=json.loads(request.args["message"].replace("'", '"'))
 
    oldsessionname=message["oldsessionname"]
    oldsession = db.session.query(models.Sessions).filter(models.Sessions.name.like(oldsessionname)).first()
 
 
    # Guard: edit permission required on owning project to add tilesets from search
    try:
        user_id=get_user_id("SearchTileSetPerm",session["username"])
        if not oldsession or not can_manage_project(oldsession.id_projects, user_id):
            flash("You don't have permission to modify tilesets for this project.")
            return redirect("/allsessions")
    except Exception:
        flash("Unable to validate permissions for tileset search.")
        return redirect("/allsessions")
 
    querysessions= db.session.query(models.Sessions).filter(models.Sessions.users.any(name=session["username"])).all()
 
    printstr="{0:\xa0<"+tilesetl+"."+tilesetl+"}|\xa0{1:\xa0<"+datel+"."+datel+"}\xa0|\xa0{2:\xa0<"+descrl+"."+descrl+"}"
 
    listtilesets=[]
    listtilesets.append(('NoChoice',printstr.format("Tileset name","Date and Time","Data Path")))
    for thissession in querysessions:
        #thissession=db.session.query(models.Sessions).filter_by(id=thissessionid[0])
        for tileset in thissession.tile_sets:
            if ( tileset.name not in listtilesets ):
                thedate=db.session.query(models.TileSets.creation_date).filter_by(name=tileset.name).scalar().isoformat().replace("T"," ")
                logging.warning("Compare thedate : %s %s" % (thedate, tileset.creation_date.isoformat().replace("T"," ")))
                listtilesets.append((str(tileset.id),
                                     printstr.format(
                                         str(tileset.name),
                                         thedate,
                                         tileset.Dataset_path)))
    # logging.warning("For user : "+session["username"]+" list old tilesets :"+str(listtilesets).replace("\xa0"," ").replace("('","\n('"))
 
    myform = BuildOldTileSetForm(session["username"], listtilesets)()
    if myform.validate_on_submit():
        if (myform.chosen_tileset.data=="NoChoice"):
            flash("Error : no TileSet Selected.")
            return redirect(url_for(".searchtileset",message=message))
        else:
            logging.warning("Out of forms, add tilesets :"+str(myform.chosen_tileset.data))
            thisTS=db.session.query(models.TileSets).filter_by(id=myform.chosen_tileset.data).scalar()
            logging.warning("For user : "+session["username"]+", add tilesets :"+str(thisTS.name))
            oldsession.tile_sets.append(thisTS)
            db.session.commit()
            message = '{"oldsessionname":"'+oldsessionname+'"}'
            flash("Add tileSet {}.".format(thisTS.name))
            return redirect(url_for(".editsession",message=message))
 
    return render_template("main_login.html", **(myrender()), title="All my Tiledsets ", form=myform)
 
# Config Session : give the json (depend of static/js/config_default.json)
@app.route('/configsession', methods=["GET", "POST"])
def configsession():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Config session : User must login !")
        return redirect("/login")
    if ( not "sessionname" in session ):
        return redirect("/allsessions")
 
    message=json.loads(request.args["message"])
 
    sessionname=message["sessionname"]
    thesession = db.session.query(models.Sessions).filter(models.Sessions.name.like(sessionname)).first()
 
    # Detect how the data of config has been inserted :
    if ( session["sessionname"] in  jsontransfert):
        if ( "TheJson" in  jsontransfert[session["sessionname"]]):
            # if TheJson is already define in message, it has been edited by jsoneditor (call beside)
            json_configs=jsontransfert[session["sessionname"]]["TheJson"]
            jsontransfert[session["sessionname"]].pop("TheJson")
            logging.debug("configsession : come back from jsoneditor")
            # json_gziped=message["TheJson"].replace("b'","").replace("'","")
            # json_unziped=gzip.decompress(base64.b64decode(json_gziped)).decode('utf-8')
            # #print("configsession json_unziped", json_unziped)
            # json_configs=json.loads(json_unziped)
            #print("configsession json_configs", json_configs)
        else:
            logging.debug("configsession : we can't be here !")
            if ( thesession.config == None ):
                logging.debug("configsession : using config_default.json")
                config_default_file=open("app/static/js/config_default.json",'r')
                json_configs=json.load(config_default_file)
                config_default_file.close()
            else:
                logging.debug("configsession : old session config.")
                json_configs=thesession.config
    else:
        if ( thesession.config == None ):
            logging.debug("configsession : using config_default.json")
            config_default_file=open("app/static/js/config_default.json",'r')
            json_configs=json.load(config_default_file)
            config_default_file.close()
        else:
            logging.debug("configsession : old session config.")
            json_configs=thesession.config
 
    json_configs_text=json.JSONEncoder().encode(json_configs)
    #logging.debug("json_configs_text : "+json_configs_text)
    myform = BuildConfigSessionForm(json_configs,json_configs_text)()
 
    if myform.validate_on_submit():
        # if json structure is inserted with text area
        json_configs = myform.json_config_text.data
 
        if myform.editjson.data:
            # json_gziped=base64.b64encode(gzip.compress(json_configs.encode('utf-8')))
            # callfunction = '{"function":"configsession",'+'"args":{"sessionname":"'+sessionname+'"}}'
            jsontransfert[session["sessionname"]]={"callfunction": '{"function":"configsession",'+'"args":{"sessionname":"'+sessionname+'"}}',
                                                   "TheJson":json_configs}
            # return redirect(url_for(".jsoneditor",callfunction=callfunction,TheJson=json_gziped))
            return redirect(url_for(".jsoneditor"))
 
        # Translate json text in structure
        jsonConfigs = json.loads(json_configs)
        #logging.debug("json_configs modify : "+str(jsonConfigs))
 
        # json_configs_file = FileField("File json object for tileset ")
        # json_file = open(json_file_name).read()
 
        thesession.config=jsonConfigs
        flag_modified(thesession,"config")
        db.session.commit()
 
        message = '{"oldsessionname":"'+sessionname+'"}'
        return redirect(url_for(".editsession",message=message))
 
    return render_template("main_login.html", **(myrender()), title="Config session", form=myform, message=message)
 
 
# New TileSet : always create tile even if another (title/comment) exists
@app.route('/addtileset', methods=["GET", "POST"])
def addtileset():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Add TileSet : User must login !")
        return redirect("/login")
    if ( not "sessionname" in session ):
        return redirect("/allsessions")
 
    # Guard: user must be allowed to edit the project owning the session
    try:
        ThisSession = db.session.query(models.Sessions).filter(models.Sessions.name.like(session["sessionname"])).first()
        user_id=get_user_id("AddTileSetPerm",session["username"])
        if not ThisSession or not can_manage_project(ThisSession.id_projects, user_id):
            flash("You don't have permission to add tilesets in this project.")
            return redirect("/allsessions")
    except Exception:
        flash("Unable to validate permissions for tileset creation.")
        return redirect("/allsessions")
 
    myform = BuildTilesSetForm()()
    #print('message=',str(request.args["message"]))
    message=json.loads(request.args["message"])
    if myform.validate_on_submit():
        logging.info("in addtileset")
 
        json_tiles=None;
        # Detect how the data of tiles has been inserted :
        if (myform.json_tiles_file.data) :
            json_tiles_file = myform.json_tiles_file.data
            json_tiles = json_tiles_file.read()
        elif (myform.json_tiles_text.data):
            # if json structure is inserted with text area
            json_tiles = myform.json_tiles_text.data
 
        if (json_tiles):
            # Translate json text in structure
            try:
                jsonTileSet = json.loads(json_tiles)
            except json.decoder.JSONDecodeError as e:
                logging.error("Addtileset : json TileSet error ! "+str(e))
                logging.error("Data : "+str(json_tiles))
                traceback.print_exc(file=sys.stderr)
                json_tiles=json.loads(json_tiles.replace("'", '"'))
                try:
                    jsonTileSet = json.loads(json_tiles)
                except json.decoder.JSONDecodeError as e:
                    logging.error("Addtileset : correction with double quotes not efficient ! "+str(e))
                    flash("Please look on data for TileSet json compliance")
                    return redirect(url_for(".addtileset",message=message))
 
            nbr_of_tiles = len(jsonTileSet["nodes"])
            logging.info("Number of tiles "+str(nbr_of_tiles))
 
        # openports_between_tiles = FieldList(IntegerField("port :",validators=[Optional()]),description="Open port in visualisation network",min_entries=2,max_entries=5)
 
        urlbool=False
        connectionbool=False
        if (myform.type_of_tiles.data == "URL"):
            urlbool=True
        elif(myform.type_of_tiles.data == "CONNECTION"):
            launch_file=myform.script_launch_file.data
            if (not launch_file):
                flash("TileSet with connection must have at least a script to launch your tiles.\n You must add launch_file script.")
                return render_template("main_login.html", **(myrender()), title="New TileSet TiledViz", form=myform, message=message)
            connectionbool=True
 
        #print(session["sessionname"])
        conn_session=db.session.query(models.Sessions).filter(models.Sessions.name.like(session["sessionname"])).first()
        creation_date=datetime.datetime.now()
        tilesetname=myform.name.data
        if ( myform.dataset_path.data ):
            datapath=str(myform.dataset_path.data)
        else:
            if (urlbool):
                datapath="https"
            else:
                datapath=""
 
        newtileset,exist=create_newtileset(tilesetname, conn_session, myform.type_of_tiles.data, datapath, creation_date)
        if (not exist):
            try:
                db.session.add(newtileset)
                db.session.commit()
            except Exception:
                traceback.print_exc(file=sys.stderr)
 
                flash("TileSet with name {} creation problem :".format(tilesetname))
                return render_template("main_login.html", **(myrender()), title="New TileSet TiledViz", form=myform, message=message)
        else:
            flash("TileSet with name {} already exists : Please give another name ".format(tilesetname))
            return render_template("main_login.html", **(myrender()), title="New TileSet TiledViz", form=myform, message=message)
 
        # Register config files in jsontransfert to write them in connection path
        if (connectionbool):
            TStmpName="tileset_"+str(newtileset.id)
            if ( not TStmpName in  jsontransfert):
                jsontransfert[TStmpName]={}
            if (myform.configfiles.data):
                for FileS in myform.configfiles.data:
                    jsontransfert[TStmpName][FileS.filename]=FileS.read()
 
            # Python file to launch case
            jsontransfert["tileset_"+str(newtileset.id)][launch_file.filename]=launch_file.read()
            newtileset.launch_file=launch_file.filename
            db.session.commit()
 
        if (json_tiles):
            # Insert tiles into DB :
            tiles=[]
 
            for i in range(nbr_of_tiles):
                Mynode=jsonTileSet["nodes"][i]
                try:
                    title,name,comment,tags,variable,pos_px_x,pos_px_y,IdLocation,url,ConnectionPort = \
                        convertTile(Mynode,tilesetname,connectionbool,urlbool,datapath)
                except Exception as e:
                    etype, value, tb = sys.exc_info()
                    flash("TileSet with name {} creation problem :".format(tilesetname)+"\n"+
                          ''.join(traceback.format_tb(tb)))
                    logging.error(''.join(traceback.format_tb(tb)))
                    return redirect(url_for(".addtileset",message=message))
 
                newtile = models.Tiles(title=title,
                                      comment=comment,
                                      tags=tags,
                                      source= {"name" : name,
                                               "connection" : ConnectionPort,
                                               "url" : url,
                                               "variable": variable
                                      },
                                      pos_px_x= pos_px_x,
                                      pos_px_y= pos_px_y,
                                      IdLocation=IdLocation,
                                      creation_date= creation_date)
                lasttile=db.session.query(models.Tiles.id).order_by(models.Tiles.id.desc()).first()
                if (lasttile):
                    newtile.id=lasttile.id+1
                else:
                    newtile.id=1
 
                db.session.add(newtile)
                db.session.commit()
                logging.warning(str(i)+" add tile "+str(newtile.id)+" "+str(newtile.title))
 
                newtileset.tiles.append(newtile)
                db.session.commit()
 
 
        session["is_client_active"]=True
 
        if (connectionbool):
            message = '{"oldtilesetid":'+str(newtileset.id)+',"oldsessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".addconnection",message=message))
        else:
            message = '{"oldsessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".editsession",message=message))
 
    return render_template("edittileset.html", **(myrender()), title="New TileSet TiledViz", form=myform, message=message)
 
 
# Copy an old TileSet
# Only copy old tiles in DB
@app.route('/copytileset', methods=["GET", "POST"])
def copytileset():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Copy TileSet : User must login !")
        return redirect("/login")
    if ( not "sessionname" in session ):
        return redirect("/allsessions")
 
    # Guard: edit permission required on owning project
    try:
        ThisSession = db.session.query(models.Sessions).filter(models.Sessions.name(session["sessionname"])).first()
        user_id=get_user_id("CopyTileSetPerm",session["username"])
        if not ThisSession or not can_manage_project(ThisSession.id_projects, user_id):
            flash("You don't have permission to copy tilesets in this project.")
            return redirect("/allsessions")
    except Exception:
        flash("Unable to validate permissions for tileset copy.")
        return redirect("/allsessions")
 
    message=json.loads(request.args["message"])
    logging.warning("copytileset : "+str(message))
    oldtilesetid=message["oldtilesetid"]
    oldtileset=db.session.query(models.TileSets).filter_by(id=oldtilesetid).scalar()
 
    myform = BuildTilesSetForm(oldtileset,onlycopy=True)()
 
    flash("Tileset {} copy for user {} in session {}".format(oldtileset.name,session["username"],session["sessionname"]))
    if myform.validate_on_submit():
        logging.info("in copy tileset")
 
        if (myform.name.data == oldtileset.name):
            message = '{"oldtilsetid":"'+str(oldtilesetid)+'"}'
            flash("You must change tilsetname to copy tileset {}".format(oldtileset.name))
            #return redirect(url_for(".copytileset",message=message))
            return render_template("main_login.html", **(myrender()), title="Copy tileset TiledViz", form=myform, message=message)
 
        nbr_of_tiles = len(oldtileset.tiles)
 
        sessioncopy=db.session.query(models.Sessions).filter_by(models.Sessions.name.like(session["sessionname"])).scalar()
        creation_date=datetime.datetime.now()
        tilesetname=myform.name.data
        newtileset, exist=create_newtileset(myform.name.data, sessioncopy, oldtileset.type_of_tiles, oldtileset.Dataset_path, creation_date)
        if (not exist):
            try:
                db.session.add(newtileset)
                db.session.commit()
            except Exception:
                traceback.print_exc(file=sys.stderr)
 
                flash("TileSet creation with name {} already exist :".format(tilesetname))
                return render_template("main_login.html", **(myrender()), title="New TileSet TiledViz", form=myform, message=message)
 
 
        newtileset.tiles=[]
        for i in range(nbr_of_tiles):
            oldtile=oldtileset.tiles[i]
            newtileset.tiles.append(oldtile)
            db.session.commit()
 
        session["is_client_active"]=True
 
        # TODO : get back old connection if exists
        urlbool=False
        connectionbool=False
        if (oldtileset.type_of_tiles == "URL"):
            urlbool=True
        elif(oldtileset.type_of_tiles == "CONNECTION"):
            connectionbool=True
 
        if (connectionbool):
            # Copy a mirror connection
            copy_tileset_connection(oldtileset,newtileset,session["sessionname"])
            logging.error("copy id connection :  "+str(newtileset.id_connections))
 
            flag_modified(newtileset,"config_files")
            db.session.commit()
 
            message = '{"oldtilesetid":'+str(newtileset.id)+',"oldsessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".edittileset",message=message))
        else:
            message = '{"oldsessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".editsession",message=message))
 
    return render_template("edittileset.html", **(myrender()), title="Copy TileSet TiledViz", form=myform, message=message)
 
def save_tiles(oldtileset,nbr_of_tiles,connectionbool,urlbool,datapath,jsonTileSet,creation_date):
    oldtileset.tiles=[]
    tilesetname=oldtileset.name
 
    for i in range(nbr_of_tiles):
        Mynode=jsonTileSet["nodes"][i]
        #print (str(i)+" "+str(Mynode))
 
        try:
            title,name,comment,tags,variable,pos_px_x,pos_px_y,IdLocation,url,ConnectionPort = \
                convertTile(Mynode,tilesetname,connectionbool,urlbool,datapath)
        except Exception as e:
            etype, value, tb = sys.exc_info()
            flash("TileSet with name {} edition problem :".format(tilesetname)+"\n"+
                  ''.join(traceback.format_tb(tb)))
            message=json.loads(request.args["message"].replace("'", '"'))
            logging.error(''.join(traceback.format_tb(tb)))
            return redirect(url_for(".edittileset",message=message))
 
        # # Insert and create only NEW tiles into TileSet or
        # # TODO: edit OLD tiles from TileSet.tiles list ? (add a suppress old tiles button in form).
        # # search if Tile already exists :
        # try:
        #     # unicity for (title, comment, tags) (url ?)
        #     oldtile=db.session.query(models.Tiles).filter_by(title=title,comment=comment).order_by(models.Tiles.id.desc()).first()
 
        #     # if (type(oldtile) == type(None)):
        #     #     logging.warning(str(i)+" tile type "+str(type(oldtile)))
        #     #     # search with url ? (if comment has changed)
        #     #     try:
        #     #         oldtile=db.session.query(models.Tiles).filter_by(title=title,source={"name":name,"url":url,"connection":ConnectionPort,"variable":variable}).order_by(models.Tiles.id.desc()).first()
        #     # => sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) operator does not exist: json = unknown
        #     # LINE 3: WHERE tiles.title = '001 ' AND tiles.source = '{"name": "001...
        #     #                                                     ^
        #     # HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
        #     #         oldtile=db.session.query(models.Tiles).filter_by(title=title,source=jsonify(name=name,url=url,connection=ConnectionPort,variable=variable)).order_by(models.Tiles.id.desc()).first()
        #     # TypeError: Object of type Response is not JSON serializable
        #     #     except :
        #     #         raise AttributeError
 
        #     logging.debug(str(i)+" tile type "+str(type(oldtile)))
        #     oldtileid=oldtile.id
        #     logging.warning(str(i)+" update old tile "+str(oldtileid))
        #     oldtile.tags=tags
        #     oldtile.source= {"name" : name,
        #                      "connection" : ConnectionPort,
        #                      "url" : url,
        #                      "variable": variable}
        #     flag_modified(oldtile,"source")
        #     oldtile.pos_px_x= pos_px_x
        #     oldtile.pos_px_y= pos_px_y
        #     oldtile.IdLocation=IdLocation
        #     oldtileset.tiles.append(oldtile)
        # except AttributeError:
        # if not : insert at end of oldtileset.tiles ?
        if (True):
            newtile = models.Tiles(title=title,
                                  comment=comment,
                                  tags=tags,
                                  source= {"name" : name,
                                           "connection" : ConnectionPort,
                                           "url" : url,
                                           "variable": variable
                                           },
                                  pos_px_x= pos_px_x,
                                  pos_px_y= pos_px_y,
                                  IdLocation=IdLocation,
                                  creation_date= creation_date)
            lasttile=db.session.query(models.Tiles.id).order_by(models.Tiles.id.desc()).first()
            if ( lasttile ):
                newtile.id=lasttile.id+1
            else:
                newtile.id=1
            db.session.add(newtile)
            oldtileset.tiles.append(newtile)
            logging.warning(str(i)+" add tile "+str(newtile.id))
        # except Exception:
        #     logging.warning(str(i)+" Error tile ")
        #     traceback.print_exc(file=sys.stderr)
 
    db.session.commit()
 
# Edit old new TileSet
@app.route('/edittileset', methods=["GET", "POST"])
def edittileset():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Edit TileSet : User must login !")
        return redirect("/login")
    if ( not "sessionname" in session ):
        return redirect("/allsessions")
 
    try:
        message=json.loads(request.args["message"].replace("'", '"'))
    except json.decoder.JSONDecodeError as e:
        logging.error("message error ! "+str(e))
        logging.error("message : "+str(request.args["message"]))
        traceback.print_exc(file=sys.stderr)
 
    logging.warning("edittileset : "+str(message))
 
    oldtilesetid=message["oldtilesetid"]
    oldtileset=db.session.query(models.TileSets).filter_by(id=oldtilesetid).one()
 
    # TODO : test if user is in a session with this tileset
    # Guard: edit permission required on owning project
    try:
        ThisSession = db.session.query(models.Sessions).filter(models.Sessions.name.like(session["sessionname"])).first()
        user_id=get_user_id("EditTileSetPerm",session["username"])
        if not ThisSession or not can_manage_project(ThisSession.id_projects, user_id):
            flash("You don't have permission to edit tilesets in this project.")
            return redirect("/allsessions")
    except Exception:
        flash("Unable to validate permissions for tileset edition.")
        return redirect("/allsessions")
 
    # Detect how the data of tileset has been inserted :
    buildargs={}
    buildargs["oldtileset"]=oldtileset
 
    # - PCA -
    #   |_ buildargs used to pass arguments to BuildTilesSetForm
    #   |_ there is the json_tiles_text in corresponding to the json of the tileset
 
    if ( session["sessionname"] in  jsontransfert):
        if ( "TheJson" in  jsontransfert[session["sessionname"]]):
            # if TheJson is already define in message, it has been edited by jsoneditor (call beside)
            logging.debug("TheJson is already define in message")
            TheJson=jsontransfert[session["sessionname"]]["TheJson"]
            jsontransfert[session["sessionname"]].pop("TheJson")
            try:
                json_tiles_text=json.JSONEncoder().encode(TheJson)
                #print("edittileset json_tiles_text) ",json_tiles_text)
                buildargs["json_tiles_text"]=json_tiles_text
            except:
                traceback.print_exc(file=sys.stderr)
                flash("Error from json editor. Please try again.")
                return redirect(url_for(".edittileset",message=message))
 
    connectionbool=False
    if(oldtileset.type_of_tiles == "CONNECTION"):
        # Try to get the old connection
        oldConnection_id=-1
        try:
            oldconnection=db.session.query(models.Connections).filter_by(id=oldtileset.id_connections).one()
            oldConnection_id=oldtileset.id_connections
            buildargs["editconnection"]=True
 
        except sqlalchemy.orm.exc.NoResultFound:
            flash("Tileset {} edit for user {} : no connection found ! ".format(oldtileset.name,session["username"]))
            oldConnection_id = 0
            buildargs["editconnection"]=True
        except AttributeError as err:
            #message = '{"oldtilesetid":'+str(oldtileset.id)+',"oldsessionname":"'+session["sessionname"]+'"}'
            #return redirect(url_for(".addconnection",message=message))
            traceback.print_exc(file=sys.stderr)
            logging.error("Error get old connection for tileset %s : %s" % ( oldtileset.name, err ))
            flash("Tileset {} edit for user {} : AttributeError ! ".format(oldtileset.name,session["username"]))
 
        except Exception as err:
            traceback.print_exc(file=sys.stderr)
            logging.error("Error get old connection for tileset %s : %s" % ( oldtileset.name, err ))
            flash("Error get old connection for tileset %s : %s" % ( oldtileset.name, err ))
 
        if ( oldConnection_id < 0):
            message = '{"oldsessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".editsession",message=message))
        connectionbool=True
 
    myform = BuildTilesSetForm(**buildargs)()
 
    if ("username" in session):
        flash("Tileset {} edit for user {} in session {}".format(oldtileset.name,session["username"],session["sessionname"]))
    else:
        flash("You are not connected. You must login before using a connection.")
        return redirect("/login")
 
    if myform.validate_on_submit():
        logging.info("in tileset editor")
 
        if(connectionbool and buildargs["editconnection"]):
            if (myform.editconnection.data):
                message = '{"oldtilesetid":'+str(oldtileset.id)+',"oldsessionname":"'+session["sessionname"]+'"}'
                return redirect(url_for(".editconnection",message=message))
            if (myform.shellconnection.data):
                message = '{"direct":1,"oldtilesetid":'+str(oldtileset.id)+',"oldsessionname":"'+session["sessionname"]+'"}'
                return redirect(url_for(".editconnection",message=message))
 
        if(myform.goback.data):
            logging.debug("go back to edit old session : "+session["sessionname"])
            message = '{"oldsessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".editsession",message=message))
 
        # - PCA -
        #   |_ process the anatreada script
        logging.info("Calling pca_nodes methode of anatread")
        logging.info("The user as selected the option : " + myform.has_pca.data)
 
        # Detect how the data of tiles has been inserted :
        if (myform.json_tiles_file.data) :
            json_tiles_file = myform.json_tiles_file.data
            logging.warning("Read json_tiles_file :"+myform.json_tiles_file.data.filename)
 
            # - PCA -
            # if has_pca option is True -> make the PCA over the file
            if myform.has_pca.data == "YES" :
                json_tiles_text=json_tiles_file.read()
                json_pca_tiles_text = anatreada.pca_on_one_node(json_tiles_text)
                json_tiles = json_pca_tiles_text
            else:
                json_tiles = json_tiles_file.read()
        else:
            # if json structure is inserted with text area
 
            # - PCA -
            # if has_pca option is True -> make the PCA over the text area
            if myform.has_pca.data == "YES" :
                json_pca_tiles_text = anatreada.pca_on_one_node(myform.json_tiles_text.data)
                json_tiles = json_pca_tiles_text
                logging.info("edittileset -> myform.has_pca.data = YES -> json_tiles")
            else:
                json_tiles = myform.json_tiles_text.data
 
        try:
            if myform.editjson.data:
                jsontransfert[session["sessionname"]]={"callfunction": '{"function":"edittileset",'+'"args":{"oldtilesetid":"'+str(oldtilesetid)+'"}}',
                                                   "TheJson":json_tiles}
                # json_gziped=base64.b64encode(gzip.compress(json_tiles.encode('utf-8')))
                #return redirect(url_for(".jsoneditor",callfunction=callfunction,TheJson=json_gziped))
                return redirect(url_for(".jsoneditor"))
        except Exception as err:
            traceback.print_exc(file=sys.stderr)
            logging.error("Error editjson %s : %s" % ( str(myform.editjson), err ))
 
        if(oldtileset.type_of_tiles == "CONNECTION" and myform.manage_connection.data != "reNew" and not myform.createconnection.data):
            user_id=get_user_id("edittileset",session["username"])
            if ( not "connection"+str(oldConnection_id) in session):
                flash("You don't have connection information in your personal cookie for this connection.")
                logging.error("You (user "+str(user_id)+") don't have connection information in your personal cookie for this connection : "+str(oldConnection_id))
                message=request.args["message"]
                return redirect(url_for(".edittileset",message=message))
 
            # Build connection path
            user_path=os.path.join("/TiledViz/TVFiles",str(user_id))
            connectionpath=os.path.join(user_path,str(oldConnection_id))
 
            # Diff config files to write them if needed in connection path
            oldtileset_config_files=oldtileset.config_files
            if (myform.configfiles.data):
                for FileS in myform.configfiles.data:
                    # data from form
                    tf = tempfile.NamedTemporaryFile(mode="w+b",dir=connectionpath,prefix="",delete=False)
                    tf.write(FileS.read())
                    newfilename=tf.name
                    tf.close()
                    if (os.stat(tf.name).st_size > 0):
                        if (FileS.filename in oldtileset_config_files):
                            # Test file modified with same name
                            boolDiff=filecmp.cmp(f1=oldtileset_config_files[FileS.filename],f2=newfilename)
                            logging.warning("Diff with modified config file : "+str(boolDiff))
                            if (not boolDiff):
                                # rm old config files
                                strrm="rm -f "+oldtileset_config_files[FileS.filename]
                                logging.warning("Update old config file "+FileS.filename+" in edittileset.")
                                os.system(strrm)
                                oldtileset.config_files[FileS.filename]=newfilename
                                flag_modified(oldtileset,"config_files")
                                db.session.commit()
                            else:
                                # rm unused config files
                                strrm="rm -f "+newfilename
                                os.system(strrm)
                        else:
                            # new config file
                            oldtileset.config_files[FileS.filename]=newfilename
                            logging.warning("Add new config file "+FileS.filename+" in edittileset.")
                            flag_modified(oldtileset,"config_files")
                            db.session.commit()
                    else:
                        # rm unused config files
                        strrm="rm -f "+newfilename
                        os.system(strrm)
 
            # Python file to launch case
            launch_file=myform.script_launch_file.data
 
            oldtileset_launch_file=oldtileset.launch_file
            FileS=myform.script_launch_file.data
            tf = tempfile.NamedTemporaryFile(mode="w+b",dir=connectionpath,prefix="",delete=False)
            tf.write(FileS.read())
            newfilename=tf.name
            tf.close()
            if (os.stat(tf.name).st_size > 0):
                if (FileS.filename in oldtileset_config_files):
                    # Test launch_file modified with same name
                    boolDiff=filecmp.cmp(f1=oldtileset_config_files[FileS.filename],f2=newfilename)
                    logging.warning("Diff with modified launch case file : "+str(boolDiff))
                    if (not boolDiff):
                        # rm old config files
                        strrm="rm -f "+oldtileset_config_files[FileS.filename]
                        logging.warning("Update old launch file "+FileS.filename+" in edittileset.")
                        os.system(strrm)
                        oldtileset.config_files[FileS.filename]=newfilename
                        flag_modified(oldtileset,"config_files")
                        db.session.commit()
                    else :
                        # rm unused config files
                        strrm="rm -f "+newfilename
                        os.system(strrm)
                else :
                    # New launch_file filename
                    # rm old config files
                    strrm="rm -f "+oldtileset_config_files[oldtileset_launch_file]
                    del(oldtileset_config_files[oldtileset_launch_file])
                    logging.warning("Rename launch file from "+oldtileset_launch_file
                                        +" to "+FileS.filename+" in edittileset.")
                    os.system(strrm)
                    oldtileset.launch_file=FileS.filename
                    oldtileset.config_files[FileS.filename]=newfilename
                    flag_modified(oldtileset,"config_files")
                    db.session.commit()
            else :
                # rm unused config files
                strrm="rm -f "+newfilename
                os.system(strrm)
 
        elif(oldtileset.type_of_tiles == "CONNECTION" and (myform.manage_connection.data == "reNew" or myform.createconnection.data)):
            launch_file=myform.script_launch_file.data
            if (not launch_file):
                flash("TileSet with connection must have at least a script to launch your tiles.\n You must add launch_file script.")
                return render_template("main_login.html", **(myrender()), title="Edit TileSet TiledViz", form=myform, message=message)
            # Register config files in jsontransfert to write them in connection path
            TStmpName="tileset_"+str(oldtileset.id)
            if ( not TStmpName in  jsontransfert):
                jsontransfert[TStmpName]={}
            if (myform.configfiles.data):
                for FileS in myform.configfiles.data:
                    jsontransfert[TStmpName][FileS.filename]=FileS.read()
 
            # Python file to launch case
            jsontransfert["tileset_"+str(oldtileset.id)][launch_file.filename]=launch_file.read()
            oldtileset.launch_file=launch_file.filename
            db.session.commit()
 
 
        # Translate json text in structure
        if (len(json_tiles) > 0):
            jsonTileSet = json.loads(json_tiles)
        else:
            jsonTileSet = {"nodes":[]}
        # else:
        #     json_file_name=secure_filename(myform.json_tiles_file.data)
        #     print("instance_path :",app.instance_path)
        #     myform.save(os.path.join(app.instance_path, filename))
        #     req, resp = app.test_client.post(
        #          '/upload', data={'upload': open(__file__, 'rb')})
        #     assert resp.status == 200
        #     assert resp.text == os.path.basename(__file__)
        #     #json_file = json_file_name.data.read()
        #     print("Read the file ",json_file_name," contains ",json_file)
        #     # get call message and put new json from file
        #     OutJson=json.dumps(json.loads(json_file)).replace("'", '"')
        #     message["TheJson"]=str(base64.b64encode(gzip.compress(OutJson.encode('utf-8'))))
        #     message=json.dumps(message) #.replace("'", '"')
        #     print("Relaunch whith file ",message)
        #     return redirect(url_for(".edittileset",message=message))
 
 
        # - PCA -
        # use this variable as the knee number
        # intialize this varibale with knee number previously calculated  if there is an oldtileset
        try:
            nbr_of_tiles = len(jsonTileSet["nodes"])
            logging.info("Number of tiles "+str(nbr_of_tiles))
        except Exception as e:
            return redirect(url_for(".edittileset",message=message))
 
        # TODO:
        # openports_between_tiles = FieldList(IntegerField("port :",validators=[Optional()]),description="Open port in visualisation network",min_entries=2,max_entries=5)
 
        # TODO
        # TilesSetForm.script_launch_file
 
 
        # Insert and create NEW tiles into TileSet or delete OLD tiles from TileSet.tiles list ?
        old_nbr_of_tiles=len(oldtileset.tiles)
        if (nbr_of_tiles > old_nbr_of_tiles):
            insertnewtiles=True
        elif (nbr_of_tiles < old_nbr_of_tiles):
            deletesometiles=True
 
 
        urlbool=False
        if (myform.type_of_tiles.data == "URL"):
            urlbool=True
 
        creation_date=datetime.datetime.now()
        if (myform.dataset_path.data):
            datapath=str(myform.dataset_path.data)
        else:
            if (urlbool):
                datapath="https"
            else:
                datapath=""
        oldtileset.datapath=datapath
        oldtileset.creation_date=creation_date
        db.session.commit()
 
 
        session["is_client_active"]=True
 
        # Get back old connection if exists
        if (connectionbool and myform.manage_connection.data):
            message = '{"oldtilesetid":'+str(oldtileset.id)+',"oldsessionname":"'+session["sessionname"]+'"}'
 
            if (myform.manage_connection.data == "reNew" or myform.createconnection.data):
                return redirect(url_for(".addconnection",message=message))
            # elif (myform.manage_connection.data == "Edit"):
            #     return redirect(url_for(".editconnection",message=message))
            elif (myform.manage_connection.data == "Quit"):
                return redirect(url_for(".removeconnection",message=message))
            else:
                save_tiles(oldtileset,nbr_of_tiles,connectionbool,urlbool,datapath,jsonTileSet,creation_date)
                return redirect(url_for(".editsession",message=message))
            #TODO:
            # ("New","Create a new one."),
            # ("Save","Save the connection for reuse."),
            # ("Reload","Reload saved connection."),
        else:
            save_tiles(oldtileset,nbr_of_tiles,connectionbool,urlbool,datapath,jsonTileSet,creation_date)
            message = '{"oldsessionname":"'+session["sessionname"]+'"}'
            return redirect(url_for(".editsession",message=message))
 
    return render_template("edittileset.html", **(myrender()), title="Edit TileSet TiledViz", form=myform, message=message)
 
# Special random function for link keys
def linkrandom(nbchar):
    ALPHABET = "B6P8VbhZoGp9JYd0uLCsAT4DXF1xqIUSyQMniNgje5_~3crvlHR-7W2f=kEtmazwKO"
    mystring=''.join(random.choice(ALPHABET) for i in range(nbchar)).encode('utf-8')
    return mystring
 
# Build iframe with noVNC (in template/noVNC ?) inside a comeback html script to be abble to go back with new message
# Then kill connection link
@app.route('/vncconnection', methods=['GET', 'POST'])
def vncconnection():
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("VNC connection : User must login !")
        return redirect("/login")
    logging.warning("Enter in connection.")
 
    myflush()
 
    try:
        message=json.loads(request.args["message"])
    except json.decoder.JSONDecodeError as e:
        logging.error("message error ! "+str(e))
        logging.error("message : "+str(request.args["message"]))
        traceback.print_exc(file=sys.stderr)
        message=json.loads(request.args["message"].replace("'", '"'))
 
    idconnection=message["connectionid"]
    idtileset=message["oldtilesetid"]
    try:
        oldconnection=db.session.query(models.Connections).filter_by(id=idconnection).first()
    except:
        flash("This connection doesn't exist.")
        logging.error("This connection doesn't exist.")
        message=request.args["message"]
        return redirect(url_for(".edittileset",message=message))
 
    if ("username" in session):
        user_id=get_user_id("vncconnection",session["username"])
    else:
        flash("You are not connected. You must login before using a connection.")
        return redirect("/login")
    if ( not "connection"+str(idconnection) in session):
        flash("You don't have connection information in your personal cookie for this connection.")
        logging.error("You (user "+str(user_id)+") don't have connection information in your personal cookie for this connection : "+str(idconnection))
        message=request.args["message"]
        return redirect(url_for(".edittileset",message=message))
 
    vnctransfert=json.loads(session["connection"+str(idconnection)])
    logging.debug("With infos :"+str(vnctransfert))
 
    flaskaddr=os.getenv("flaskhost")
    logging.debug("Detected flask address :"+str(flaskaddr))
 
    callfunction=vnctransfert["callfunction"]
    if (oldconnection):
        if  (user_id != oldconnection.id_users) :
            flash("You can not access to this connection. You are not its owner.")
            owner=db.session.query(models.Users).filter_by(id=oldconnection.id_users).one().name
            you=db.session.query(models.Users).filter_by(id=user_id).one().name
            logging.error("You (user "+you+") can not access to this connection owned by user "+owner)
            message=request.args["message"]
            return redirect(url_for(".edittileset",message=message))
 
    # Wait from TVSecure for connection PORT in DB
    db.session.refresh(oldconnection)
    connection_vnc=oldconnection.connection_vnc
    logging.warning("PORT VNC :"+str(connection_vnc))
    if (connection_vnc == 0):
        flash_msg="Error reading PORT VNC :"+str(connection_vnc)
        logging.error(flash_msg)
        flash(flash_msg)
        message=request.args["message"]
        return redirect(url_for(".edittileset",message=message))
    connection_vnc=connection_vnc+32768
 
    if ( request.method == 'POST'):
        logging.warning("in POST")
        myflush()
        message=json.JSONEncoder().encode(vnctransfert["args"])
 
        logging.warning("killconnection: "
                        +str(session["username"])+" ; "
                        +str(idtileset)+" ; "
                        +str(idconnection))
        myflush()
 
        out_nodes_json = os.path.join("/TiledViz/TVFiles", str(user_id), str(idconnection),"nodes.json")
        logging.warning("out_nodes_json after vncconnection.html :"+out_nodes_json)
 
        # Wait NbTimeAlive to get files from connection
        NbTimeAlive=20
 
        if ( not session["sessionname"] in  jsontransfert):
            jsontransfert[session["sessionname"]]={}
 
        count=0
        while True:
            time.sleep(timeAlive)
            if (count > NbTimeAlive):
                if ("TheJson" in jsontransfert[session["sessionname"]]):
                    del(jsontransfert[session["sessionname"]]["TheJson"])
                return redirect(url_for("."+callfunction,message=message))
            if ( os.path.exists( out_nodes_json ) ):
                try:
                    json_tiles_file=open(out_nodes_json)
                    jsontransfert[session["sessionname"]]["TheJson"]=json.loads(json_tiles_file.read())
                    json_tiles_file.close()
                    logging.warning("nodes.json read and OK "+out_nodes_json)
                    return redirect(url_for("."+callfunction,message=message))
                except Exception as err:
                    traceback.print_exc(file=sys.stderr)
                    strerror="Error from json "+out_nodes_json+" file from connection : "+str(err)
                    logging.error(strerror)
            else:
                logging.warning("Wait for "+out_nodes_json)
            count=count+1
 
    return render_template("vncconnection.html", **(myrender()),
                           port=connection_vnc,
                           id=idconnection,
                           tsid=idtileset,
                           vncpassword=vnctransfert["vncpassword"],
                           session=session["sessionname"],
                           flaskaddr=flaskaddr)
 
@app.route('/addconnection', methods=["GET", "POST"])
def addconnection():
    global TimeConnection
 
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Add connection : User must login !")
        return redirect("/login")
 
    User = db.session.query(models.Users).filter_by(name=session["username"]).one()
 
    logging.warning(f"authchoices before {authchoice}")
    myform = BuildConnectionsForm(is_admin=User.is_admin,authchoice=authchoice)()
 
    print('message=',str(request.args["message"]))
    message=json.loads(request.args["message"])
    message["TimeConnection"]=TimeConnection
 
    logging.debug("ConnectionForm built."+str(message))
 
    # Guard: edit permission required on owning project
    try:
        ThisSession = db.session.query(models.Sessions).filter(models.Sessions.name.like(session["sessionname"])).first()
        user_id=get_user_id("AddConnectionPerm",session["username"])
        if not ThisSession or not can_manage_project(ThisSession.id_projects, user_id):
            flash("You don't have permission to manage connections in this project.")
            return redirect("/allsessions")
    except Exception:
        flash("Unable to validate permissions for connection management.")
        return redirect("/allsessions")
 
    if myform.validate_on_submit():
        logging.info("in addconnection")
 
 
        # TODO :
        # test validity of data with s.encode('ascii') and except UnicodeDecodeError:
        #myform.host_address.data myform.auth_type.data myform.container.data ?
 
 
        logging.info(str(session["username"])+" "+str(myform.host_address.data)+"  "+str(myform.auth_type.data)+"  "+str(myform.container.data))
 
        creation_date=datetime.datetime.now()
        user_id=get_user_id("addconnection",session["username"])
 
        # Test if a connection is already attached few seconds ago for the tileset :
        newtileset=db.session.query(models.TileSets).filter_by(id=message["oldtilesetid"]).one()
 
        if (type(newtileset.id_connections) != type(None)):
            logging.warning("New connection created :"+str(newtileset.id_connections))
            try:
                oldConnection=db.session.query(models.Connections).filter_by(id=newtileset.id_connections).one()
                olddate=oldConnection.creation_date
                if ((creation_date-olddate).seconds < 3):
                    return
            except:
                return
 
        if (myform.scheduler_file.data):
            scheduler_filename=myform.scheduler_file.data.filename
        else:
            scheduler_filename=""
 
        newConnection = models.Connections(host_address=myform.host_address.data,
                                          auth_type=myform.auth_type.data,
                                          container=myform.container.data,
                                          scheduler=myform.scheduler.data,
                                          scheduler_file=scheduler_filename,
                                          id_users=user_id,
                                          creation_date= creation_date)
 
        lastconnection=db.session.query(models.Connections.id).order_by(models.Connections.id.desc()).first()
        if ( lastconnection ):
            newConnection.id=lastconnection.id+1
        else:
            newConnection.id=1
        db.session.add(newConnection)
        db.session.commit()
 
        # We must add connection id in the tile_set
        newtileset.id_connections=newConnection.id
        db.session.commit()
 
        # TODO : Add subdir specific ?
        TSConfigjson={}
        ConnConfigjson={}
        # Build connection path
        user_path=os.path.join("/TiledViz/TVFiles",str(user_id))
        connectionpath=os.path.join(user_path,str(newConnection.id))
        # Create connection dir
        try:
            os.mkdir(user_path)
            logging.warning("Creation of connection path for config files : "+user_path)
        except FileExistsError:
            pass
        try:
            os.mkdir(connectionpath)
            logging.warning("Creation of connection path for config files : "+connectionpath)
        except FileExistsError:
            pass
 
        # Save config files from TileSet (placed in jsontransfer) and connection in this connectionpath
        TStmpName="tileset_"+str(newtileset.id)
        if ( TStmpName in jsontransfert):
            for FileS in jsontransfert[TStmpName]:
                tf = tempfile.NamedTemporaryFile(mode="w+b",dir=connectionpath,prefix="",delete=False)
                tf.write(jsontransfert[TStmpName][FileS])
                # TODO : Add dir ?
                TSConfigjson[FileS]=tf.name
                tf.close()
            # for FileS in jsontransfert[TStmpName]:
            #     jsontransfert[TStmpName].pop(FileS)
            jsontransfert.pop(TStmpName)
 
        # Write config files for connection
        if ( myform.configfiles.data and str(myform.configfiles.data) != str([""]) ):
            logging.info("Config files : %s " % (str(myform.configfiles.data)))
            for FileS in myform.configfiles.data:
                logging.warning("Config files File : %s " % (str(FileS)))
                tf = tempfile.NamedTemporaryFile(mode="w+b",dir=connectionpath,prefix="",delete=False)
                tf.write(FileS.read())
                # TODO : Add dir (in JOBPath on HPC machine) info for files ?
                ConnConfigjson[FileS.filename]=tf.name
                tf.close()
 
        # if (myform.configfiles.data):
        #     for FileS in myform.configfiles.data:
        #         logging.error("Config type file : %s " % (str(type(FileS))))
        #         outHandler.flush()
        #         if ( type(FileS) != type("AA") ):
        #             logging.error("Config file : %s " % (FileS))
        #             tf = tempfile.NamedTemporaryFile(mode="w+b",dir=connectionpath,prefix="",delete=False)
        #             tf.write(myform.configfiles.data[FileS].read())
        #             # TODO : Add dir (in JOBPath on HPC machine) info for files ?
        #             ConnConfigjson[FileS]=tf.name
        #             tf.close()
        #         else:
        #             logging.warning("Config file : %s " % (FileS))
        #             outHandler.flush()
 
        # Save scheduler_file in connectionpath and tmp filename in ConnConfigjson
        if (myform.scheduler_file.data):
            logging.warning("Scheduler file : %s " % (str(myform.scheduler_file.data)))
            scheduler_file=myform.scheduler_file.data
            if ( scheduler_file != "" ):
                tf = tempfile.NamedTemporaryFile(mode="w+b",dir=connectionpath,prefix="",delete=False)
                tf.write(scheduler_file.read())
                ConnConfigjson[scheduler_file.filename]=tf.name
                tf.close()
 
        sTSConfigjson=str(TSConfigjson).replace("'", '"')
        logging.warning("Configuration files for TileSet %s : %s " % (newtileset.name,sTSConfigjson) )
        newtileset.config_files=json.loads(sTSConfigjson)
 
        sConnConfigjson=str(ConnConfigjson).replace("'", '"')
        logging.warning("Configuration files for connection %d : %s " % (newConnection.id,sConnConfigjson) )
        newConnection.config_files=json.loads(sConnConfigjson)
        db.session.commit()
 
        deb=(myform.debug.data & 1 | 0)
 
        passpath="/home/connect"+str(newConnection.id)+"/vncpassword"
        logging.warning("Go to vnc with path "+passpath)
 
        config = configparser.ConfigParser()
        config.optionxform = str
 
        if ( "config.tar" in newtileset.config_files ):
            tar_config_file=tarfile.TarFile(name=newtileset.config_files["config.tar"],mode='r')
            tar_config_file.list()
            try:
                with tar_config_file.extractfile("case_config.ini") as case_config_file:
                    config.read_string(case_config_file.read().decode('utf-8'))
                nbtiles=int(config['CASE']['NUM_DOCKERS'])
            except Exception as err:
                traceback.print_exc(file=sys.stderr)
                strerror="Error getting number of tiles from case_config in config.tar before connection : "+str(err)
                logging.error(strerror)
                nbtiles=0
        else:
            try:
                config.read(newtileset.config_files["case_config.ini"])
                nbtiles=int(config['CASE']['NUM_DOCKERS'])
            except Exception as err:
                traceback.print_exc(file=sys.stderr)
                strerror="Error getting number of tiles from case_config.ini before connection : "+str(err)
                logging.error(strerror)
                nbtiles=0
 
        logging.warning("addconnection: "
                        +str(session["username"])+" ; "
                        +str(myform.host_address.data)+" ; "
                        +str(myform.auth_type.data)+" ; "
                        +str(myform.container.data)+" ; "
                        +str(myform.scheduler.data)+" ; "
                        +str(newtileset.id)+" ; "
                        +str(newConnection.id)+" ; "
                        +str(nbtiles)+" ; "
                        +str(deb))
        myflush()
 
        #  Wait NbTimeAlive for TVSecure to get VNC view to put connection datas.
        NbTimeAlive = 40
        count=0
        countTimeConnection=time.time()
        while(True):
            if (count > NbTimeAlive):
                strerror="Connection has never been reach. Go back to TileSet."
                logging.error(strerror)
                flash(strerror)
                message = '{"oldtilesetid": "'+str(newtileset.id)+'"}'
                return redirect(url_for(".edittileset",message=message))
            count=count+1
            #logging.warning("addconnection count : "+str(count))
 
            # GET VNC password in
            # security problem here if server is attacked ?
            time.sleep(timeAlive)
            #os.system("ls -la "+passpath)
            #sys.stdout.flush()
            if (os.path.isfile(passpath)):
                countTimeConnection=int(time.time()-countTimeConnection)
                if (TimeConnection!=countTimeConnection):
                    TimeConnection=countTimeConnection
                with open(passpath,'r') as f:
                    vncpassword=re.sub(r'\n',r'',f.read())
                f.close()
                logging.debug("and password : "+vncpassword)
 
                message = '{"oldtilesetid":'+str(newtileset.id)+',"connectionid":'+str(newConnection.id)+',"sessionname":"'+session["sessionname"]+'"}'
                session["connection"+str(newConnection.id)]=' {"callfunction":"edittileset",'+'"args":{"oldsessionname":"'+str(session["sessionname"])+'","oldtilesetid":"'+str(newtileset.id)+'"}, "vncpassword":"'+vncpassword+'"}'
 
                logging.warning("addconnection in session : "+str(session["connection"+str(newConnection.id)]))
                logging.warning("Go to vncconnection : "+str(url_for(".vncconnection",message=message)))
                #TODO logging.debug
                myflush()
                return redirect(url_for(".vncconnection",message=message))
 
    return render_template("addconnection.html", **(myrender()), title="Add new Connection TiledViz", form=myform, message=message)
 
# Edit old Connection related to a tileset
@app.route('/editconnection', methods=["GET", "POST"])
def editconnection():
    logging.warning('editconnection message='+str(request.args["message"]))
 
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Edit connection : User must login !")
        return redirect("/login")
 
    # Guard: edit permission required on owning project
    try:
        ThisSession = db.session.query(models.Sessions).filter(models.Sessions.name.like(session["sessionname"])).first()
        user_id=get_user_id("EditConnectionPerm",session["username"])
        if not ThisSession or not can_manage_project(ThisSession.id_projects, user_id):
            flash("You don't have permission to edit connections in this project.")
            return redirect("/allsessions")
    except Exception:
        flash("Unable to validate permissions for connection edition.")
        return redirect("/allsessions")
 
    try:
        message=json.loads(request.args["message"])
    except json.decoder.JSONDecodeError as e:
        logging.error("message error ! "+str(e))
        logging.error("message : "+str(request.args["message"]))
        traceback.print_exc(file=sys.stderr)
        message=json.loads(request.args["message"].replace("'", '"'))
 
    oldtilesetid=message["oldtilesetid"]
    oldtileset=db.session.query(models.TileSets).filter_by(id=oldtilesetid).one()
 
    oldconnection=oldtileset.connections
    try:
        idconnection=oldconnection.id
    except:
        flash("This connection doesn't exist.")
        logging.error("This connection doesn't exist.")
        message=request.args["message"]
        return redirect(url_for(".edittileset",message=message))
 
    user_id=get_user_id("editconnection",session["username"])
    # Build connection path
    user_path=os.path.join("/TiledViz/TVFiles",str(user_id))
    connectionpath=os.path.join(user_path,str(oldconnection.id))
 
 
    if ( not "connection"+str(idconnection) in session):
        flash("You don't have connection information in your personal cookie for this connection.")
        logging.error("You (user "+str(user_id)+") don't have connection information in your personal cookie for this connection : "+str(idconnection))
        message=request.args["message"]
        return redirect(url_for(".edittileset",message=message))
 
    if ("direct" in message):
        del message["direct"]
        message=launch_connection(oldtileset,oldconnection,
                          oldconnection.host_address,
                          oldconnection.auth_type,
                          oldconnection.container,
                          oldconnection.scheduler)
        return redirect(url_for(".vncconnection",message=message))
        flash("Error direct connection to shell.")
 
 
    User = db.session.query(models.Users).filter_by(name=session["username"]).one()
 
    myform = BuildConnectionsForm(is_admin=User.is_admin,oldconnection=oldconnection)()
 
    logging.debug("ConnectionForm edit."+str(message))
    if myform.validate_on_submit():
        logging.info("in editconnection")
 
        logging.info(str(myform.host_address.data)+"  "+str(myform.auth_type.data)+"  "+str(myform.container.data))
 
        TSConfigjson={}
 
        # TODO :
        #  Test connection type in launch a form dedicated.
 
        # TODO :
        #        rm old config files not overwitten if exists ?
 
        # detect/diff/update config files
        oldconnection_config_files=oldconnection.config_files
 
        if (myform.configfiles.data):
            for FileS in myform.configfiles.data:
                # data from form
                tf = tempfile.NamedTemporaryFile(mode="w+b",dir=connectionpath,prefix="",delete=False)
                tf.write(FileS.read())
                newfilename=tf.name
                tf.close()
                if (os.stat(tf.name).st_size > 0):
                    if (FileS.filename in oldconnection_config_files):
                        # Test file modified with same name
                        boolDiff=filecmp.cmp(f1=oldconnection_config_files[FileS.filename],f2=newfilename)
                        logging.warning("Diff with modified config file : "+str(boolDiff))
                        if (not boolDiff):
                            # rm old config files
                            strrm="rm -f "+oldconnection_config_files[FileS.filename]
                            logging.warning("Update old config file "+FileS.filename+" in editconnection.")
                            os.system(strrm)
                            oldconnection.config_files[FileS.filename]=newfilename
                            flag_modified(oldconnection,"config_files")
                            db.session.commit()
                        else :
                            # rm unused config files
                            strrm="rm -f "+newfilename
                            os.system(strrm)
                    else:
                        # new config file
                        oldconnection.config_files[FileS.filename]=newfilename
                        logging.warning("Add new config file "+FileS.filename+" in editconnection.")
                        flag_modified(oldconnection,"config_files")
                        db.session.commit()
                else :
                    # rm unused config files
                    strrm="rm -f "+newfilename
                    os.system(strrm)
 
        # detect/diff/update scheduler file
        if (myform.scheduler_file.data):
            oldconnection_scheduler_file=oldconnection.scheduler_file
            FileS=myform.scheduler_file.data
            tf = tempfile.NamedTemporaryFile(mode="w+b",dir=connectionpath,prefix="",delete=False)
            tf.write(FileS.read())
            newfilename=tf.name
            tf.close()
            if (os.stat(tf.name).st_size > 0):
                if (FileS.filename in oldconnection_config_files):
                    # Test scheduler_file modified with same name
                    boolDiff=filecmp.cmp(f1=oldconnection_config_files[FileS.filename],f2=newfilename)
                    logging.warning("Diff with modified scheduler file : "+str(boolDiff))
                    if (not boolDiff):
                        # rm old config files
                        strrm="rm -f "+oldconnection_config_files[FileS.filename]
                        logging.warning("Update old scheduler file "+FileS.filename+" in editconnection.")
                        os.system(strrm)
                        oldconnection.config_files[FileS.filename]=newfilename
                        flag_modified(oldconnection,"config_files")
                        db.session.commit()
                    else :
                        # rm unused config files
                        strrm="rm -f "+newfilename
                        os.system(strrm)
                else:
                    if (oldconnection_scheduler_file):
                        # rm old config files
                        strrm="rm -f "+oldconnection_config_files[oldconnection_scheduler_file]
                        logging.warning("Rename scheduler file from "+oldconnection_scheduler_file
                                        +" to "+FileS.filename+" in editconnection.")
                        os.system(strrm)
                    else:
                        logging.warning("Add scheduler file "+FileS.filename+" in editconnection.")
                    oldconnection.scheduler_file=FileS.filename
                    oldconnection.config_files[FileS.filename]=newfilename
                    flag_modified(oldconnection,"config_files")
                    db.session.commit()
            else :
                # rm unused config files
                strrm="rm -f "+newfilename
                os.system(strrm)
 
        message=launch_connection(oldtileset,oldconnection,
                                  myform.host_address.data,
                                  myform.auth_type.data,
                                  myform.container.data,
                                  myform.scheduler.data)
        return redirect(url_for(".vncconnection",message=message))
 
    return render_template("main_login.html", **(myrender()), title="Edit Connection TiledViz", form=myform, message=message)
 
 
# Remove old Connection related to a tileset
@app.route('/removeconnection', methods=["GET", "POST"])
def removeconnection():
    logging.warning('removeconnection message='+str(request.args["message"]))
 
    if ("username" in session):
        if (session["username"] == "Anonymous"):
            return redirect("/login")
    else:
        flash("Remove connection : User must login !")
        return redirect("/login")
 
    try:
        message=json.loads(request.args["message"])
    except json.decoder.JSONDecodeError as e:
        logging.error("message error ! "+str(e))
        logging.error("message : "+str(request.args["message"]))
        traceback.print_exc(file=sys.stderr)
        message=json.loads(request.args["message"].replace("'", '"'))
 
    oldtilesetid=message["oldtilesetid"]
    oldtileset=db.session.query(models.TileSets).filter_by(id=oldtilesetid).one()
 
    oldconnection=oldtileset.connections
    try:
        idconnection=oldconnection.id
    except:
        flash("This connection doesn't exist.")
        logging.error("This connection doesn't exist.")
        message=request.args["message"]
        return redirect(url_for(".edittileset",message=message))
 
    user_id=get_user_id("removeconnection",session["username"])
    if ( not "connection"+str(idconnection) in session):
        flash("You don't have connection information in your personal cookie for this connection.")
        logging.error("You (user "+str(user_id)+") don't have connection information in your personal cookie for this connection : "+str(idconnection))
        message=request.args["message"]
        return redirect(url_for(".edittileset",message=message))
 
    remove_this_connection(oldtileset,idconnection,user_id)
 
    flash("Connection "+str(idconnection)+" for tileset "+oldtileset.name+" has been removed.")
    message=request.args["message"]
    return redirect(url_for(".edittileset",message=message))
 
 
# Call json editor on a structure
# TODO : no more GET method to test ?
@app.route('/jsoneditor', methods=['GET', 'POST'])
def jsoneditor():
    #print("jsoneditor args : ",request.args)
    #     json_gziped=request.args["TheJson"]
    #     #print("type json_gziped :",type(json_gziped))
    #     TheJson=gzip.decompress(base64.b64decode(json_gziped)).decode('utf-8')
    #     callfunction=json.loads(request.args["callfunction"])
    try:
        TheJson=jsontransfert[session["sessionname"]]["TheJson"]
    except Exception as e:
        message = '{"oldsessionname":"'+session["sessionname"]+'"}'
        return redirect(url_for(".editsession",message=message))
 
    callfunction=json.loads(jsontransfert[session["sessionname"]]["callfunction"])
 
    logging.debug("jsoneditor : "+str(callfunction))
    if ( request.method == 'POST'):
        message=callfunction["args"]
        TheJson=json.loads(request.form.get("submit"))
        OutJson=json.dumps(TheJson).replace("'", '"')
        #logging.debug("jsoneditor OutJson :"+OutJson)
        jsontransfert[session["sessionname"]]={"TheJson":TheJson}
        # message["TheJson"]=str(base64.b64encode(gzip.compress(OutJson.encode('utf-8'))))
        message=json.dumps(message) #.replace("'", '"')
        #logging.debug("jsoneditor message :"+str(message))
 
        return redirect(url_for("."+callfunction["function"],message=message))
    return render_template("jsoneditor.html", **(myrender()), TheJson=TheJson)
 
# ====================================================================
# Grid/main page
#cors = CORS(app, resources={r"/grid/*": {"origins": "*"}})
@app.route('/grid', methods=['GET', 'POST'])
@cross_origin()
def show_grid():
 
    logging.warning("session in grid : "+str(session))
    #logging.debug("Enter in show_grid: with session "+str(session))
    if (not 'is_client_active' in session):
        flash("You are not connected. You must login before using a grid.")
        logging.error("You are not connected. You must login before using a grid."+str(session))
        return redirect("/login")
 
    if request.method == 'POST' and "new room" in request.form :
        psession = request.form['new room']
        # TODO: properly close the old socket, or remove it from the room
        # (usefulness? it's only a testing tool at this point')
        logging.info("[!] Change session room to " + psession)
        session["sessionname"]=psession
        thesession = db.session.query(models.Sessions).filter_by(name=psession).scalar()
        if (type(thesession) != type(None)):
 
            project = session["projectname"] = thesession.projects.name
 
        else:
            logging.warning("You must choose a valid session")
            flash("You didn't select a valid session in grid.")
            return redirect("/allsessions")
    else: # GET
        if (session["is_client_active"]):
            try:
                cookieuser = {"username" : session["username"] }
            except:
                return redirect("/login")
 
 
        if (not "sessionname" in session or
            not "projectname" in session):
            flash("No project or session defined yet. Please chose one.")
            return redirect("/allsessions")
 
        psession = session["sessionname"]
        project = session["projectname"]
        logging.info("Into '/grid' with GET for session room to " + psession)
 
    try:
        part_nbr = str(len(room_dict[psession]))
        logging.debug(str(room_dict[psession]))
    except KeyError as e: # If the grid is the first to join the room, the "room_dict[session]" doesn't exist yet
        part_nbr = 0
        logging.info("first to join the room "+session["username"])
 
    try:
        logging.debug("Grid with session :"+str(session["projectname"])+" "+str(session["sessionname"])+" "+str(session["username"]))
    except :
        pass
 
    # Build Session
    ThisSession=db.session.query(models.Sessions).filter(models.Sessions.name.like(session["sessionname"])).first()
 
    # Test if session is empty
    if (len(ThisSession.tile_sets) == 0):
        flash("No TileSet in this session : You must define tileset before going to grid.")
        message = '{"oldsessionname":"'+session["sessionname"]+'"}'
        return redirect(url_for(".editsession",message=message))
 
 
    # session["tilesetnames"]=[]
    # if (db.session.query(func.count(ThisSession.tile_sets)).scalar() > 0):
    if (type(ThisSession) != type(None)):
            ListAllTileSet_ThisSession=ThisSession.tile_sets
    else:
        logging.warning("You must choose a valid session")
        flash("You didn't select a valid session in grid.")
        return redirect("/allsessions")
    session["tilesetnames"]=[ thistileset.name for thistileset in ListAllTileSet_ThisSession ]
    logging.warning("All TileSet for session "+str(session["sessionname"])+" : "+str(session["tilesetnames"]))
 
    # JsonSession={"info": {"SessionName" : sessionNAME,
    #               "ProjectName" : ThisSession.projects.name,
    #               "Users" : list(set([ThisSession.projects.users.name]+
    #                                   [SessionUser.name for SessionUser in ThisSession.users]))},
    #              "tilesets": [ {"name":thistileset.name,
    #                     "Dataset_path":thistileset.Dataset_path,
    #                     "tiles": [ {"id" : tile.id,
    #                                 "title" : tile.title,
    #                                 "comment": tile.comment,
    #                                 "source": tile.source,
    #                                 "tags": tile.tags
    #                     } for tile in thistileset.tiles ] }
    #                    for thistileset in ListAllTileSet_ThisSession ]
    # }
    # Need for saving session in a file ?
    # textSession=json.JSONEncoder().encode(JsonSession)
    #logging.debug(textSession)
 
    # Main loop to build the grid :
    nbr_of_tiles=0
 
    # If connection ? Test tileset connection ok ??
 
    # (Temporary ?) build all tiles data vector
    global tiles_data
    tiles_data={}
    tiles_data["nodes"]=[]
    ts=0
    lts=len(ThisSession.tile_sets)
    while (ts < lts):
        thistileset=ThisSession.tile_sets[ts]
        nbtiles=len(thistileset.tiles)
        nbr_of_tiles = nbr_of_tiles + nbtiles
        if (nbtiles < 1):
            flash("Before grid, ERROR IN SESSION '%s' with TileSet '%s' : no tiles." % (ThisSession.name,thistileset.name))
            return redirect("/allsessions")
 
        tiledata=tvdb.encode_tileset(thistileset)
        tiles_data["nodes"]=tiles_data["nodes"]+tiledata
        ts=ts+1
 
    #logging.debug(str(tiles_data))
    session["nbr_of_tiles"]=nbr_of_tiles
    logging.info("nbr_of_tiles="+str(session["nbr_of_tiles"]))
 
    config["nbr_of_tiles"] = nbr_of_tiles
 
    # Global tags search
    Tags={}
    Tags["globalTags"]=[]
    Tags["FloatingTags"]={}
 
    # df_nodes_normalized : DataFrame()
    # df_nodes_normalized contains the DataFrame of tiles_data["nodes"]
    df_nodes_normalized = pd.json_normalize(tiles_data["nodes"])
 
    # df_column_tag_normalized : DataFrame()
    # df_column_tag_normalized contains the DataFrame of tags column of df_nodes_normalized DataFrame
    df_column_tag_normalized = df_nodes_normalized["tags"]
    print(df_column_tag_normalized)
    # tags_normalized : DataFrame()
    # tags_normalized contains the DataFrame of node/tile tags
    df_tags_normalized = pd.DataFrame()
 
    # tag_lines : list(dict())
    # tag_lines contains tags information in a dictionary format
    tag_lines = []
    floating_tag={}
 
    #i : int
    for i in range(0, len(df_column_tag_normalized)):
        # dict_line : dict()
        dict_line = dict()
        tags_node = []
 
        # j : int
        for j in range(0, len(df_column_tag_normalized[i])):
 
            newline = df_column_tag_normalized[i][j]
            newline = newline.replace("{", "")
            newline = newline.replace("}", "")
 
            # list_line : []
            # list_line contains the list of elements of le string line
            list_line = newline.split(',')
 
            # tag_name : str
            tag_name = list_line[0]
            tags_node.append(tag_name)
 
            # if it's a variable tag
            if len(list_line) > 1:
                value_min =     list_line[1]
                value =         list_line[2]
                value_max =     list_line[3]
                dict_line[tag_name] = float(value)
                floating_tag[tag_name]={'m':value_min,'M':value_max}
 
            # if the tag is the last of the node/tile
            if j == len(df_column_tag_normalized[i]) -1 :
                tag_lines.append(dict_line)
                dict_line = {}
 
        for tag in tags_node:
            if (not tag in Tags["globalTags"]):
                Tags["globalTags"].append(tag)
                if (tag in floating_tag):
                    Tags["FloatingTags"][tag]=floating_tag[tag]
 
 
    tiles_data["config"] = config
    psgeom={}
    if (not session["is_client_active"]):
        try:
            psgeom=json.loads(session["geometry"])
            logging.warning("geom for passive client :"+str(psgeom)+" "+str(type(psgeom)))
        except:
            traceback.print_exc(file=sys.stderr)
 
    TheConfig=ThisSession.config;
    if (ThisSession.config is None):
        flash("Session {} does not have a valid configuration for grid.".format(session["sessionname"]))
        message = '{"sessionname":"'+session["sessionname"]+'"}'
        return redirect(url_for(".configsession",message=message))
    # if (TheConfig==""):
    #     config_default_file=open("app/static/js/config_default.json",'r')
    #     json_configs=json.load(config_default_file)
    #     config_default_file.close()
    #     TheConfig=json.JSONEncoder().encode(json_configs)
    logging.debug("config : "+str(TheConfig))
 
    try:
        lang=TheConfig["language"];
    except:
        lang="EN";
 
    #get actions files for each TS/connection
    lts=len(ThisSession.tile_sets)
    tiles_actions={}
    ts=0
    while (ts < lts):
 
        thistileset=ThisSession.tile_sets[ts]
        # Connection for this TS
        tsconnection=thistileset.connections
 
        if (type(tsconnection) != type(None) and
            len(thistileset.config_files) > 0):
            if ("connection"+str(tsconnection.id) in session):
                asaction=False
                if ( "config.tar" in thistileset.config_files ):
                    tar_config_file=tarfile.TarFile(name=thistileset.config_files["config.tar"],mode='r')
                    tar_config_file.list()
                    try:
                        actions_file=tar_config_file.extractfile("actions.json")
                        tiles_actions[thistileset.name]=json.loads(actions_file.read().decode('utf-8'))
                        asaction=True
                    except:
                        pass
 
                if ( "actions.json" in thistileset.config_files ):
                    actions_file=open(thistileset.config_files["actions.json"],'r')
                    tiles_actions[thistileset.name]=json.load(actions_file)
                    asaction=True
                if (asaction):
                    tiles_actions[thistileset.name]["action0"]=["get_new_nodes","system_update_alt"]
                    # Search kill_all_containers action to register it in session cookie for this connection:
                    if (session["is_client_active"]):
                        rekillid=re.compile(r'"killid"')
                        for actionid in tiles_actions[thistileset.name]:
                            if (tiles_actions[thistileset.name][actionid][0] == "kill_all_containers" and
                                not re.search(rekillid,session["connection"+str(tsconnection.id)])):
                                session["connection"+str(tsconnection.id)]=session["connection"+str(tsconnection.id)].replace(', "vncpassword"',', "killid":"'+actionid.replace("action","")+'", "vncpassword"')
                else:
                    tiles_actions[thistileset.name]={}
        else:
            tiles_actions[thistileset.name]={}
        ts=ts+1
 
    if (len(tiles_actions)>0):
        logging.warning("Global tile actions : "+str(tiles_actions))
 
    if ("colorTheme" in TheConfig["colors"]):
        colorTheme=TheConfig["colors"]["colorTheme"]
        logging.info("Color : "+str(colorTheme))
    else:
        colorTheme="dark"
        logging.info("Color always "+str(colorTheme))
    help_path="doc/user_doc_" + lang + "_" + colorTheme + ".html";
    logging.info("helpPath ="+str(help_path))
 
    # Compute project role for template (owner only see invite button)
    try:
        current_user_obj = db.session.query(models.Users).filter_by(name=session["username"]).first()
        membership = None
        if current_user_obj and ThisSession:
            membership = db.session.query(models.ProjectMembers).filter_by(
                project_id=ThisSession.id_projects, user_id=current_user_obj.id
            ).first()
        project_role = membership.role_type if membership else None
    except Exception:
        project_role = None
 
    return render_template("grid_template.html",
                           user=session["username"],
                           title="TiledViz on "+project,
                           project=project,
                           session=psession,
                           description=str(ThisSession.description),
                           json_geom=psgeom,
                           participants=part_nbr,
                           is_client_active=session["is_client_active"],
                           project_role=project_role,
                           json_data=tiles_data,
                           json_actions=tiles_actions,
                           json_config=TheConfig,
                           json_Tags=Tags,
                           helpPath = help_path)
 
@socketio.on("save_Session")
def saveSession(cdata):
    croom=cdata["room"]
    logging.warning("[->] saveSession \"" + str(cdata["NewSuffix"]) + "\" with description \"" + str(cdata["NewDescription"]) + "\" in room " + str(croom))
    alltilesjson=json.loads(cdata["Session"].replace("'", '"'));
    save_session(str(session["sessionname"]),str(cdata["NewSuffix"]),str(cdata["NewDescription"]),alltilesjson)
 
@socketio.on("share_Selection")
def shareSelection(cdata):
    croom=cdata["room"]
    listSelectionIds=json.loads(cdata["Selection"]);
    logfun("[->] shareSelection " + str(len(listSelectionIds)) + " nodes in room " + str(croom))
    sdata = {"Selection":cdata["Selection"]}
    socketio.emit('receive_deploy_Selection', sdata,room=croom)
 
@socketio.on("deploy_Session")
def deploySession(cdata):
    croom=cdata["room"] # room is old session
    logging.warning("[->] deploySession NEW ROOM : '" + str(cdata["NewRoom"]) + "' from room " + str(croom))
    sdata = {"NewRoom":cdata["NewRoom"]}
    session["sessionname"]=cdata["NewRoom"];
    socketio.emit('receive_deploy_Session', sdata,room=croom) # change room for new session ?
 
@socketio.on("share_Config")
def shareConfig(cdata):
    try:
        croom=cdata["room"]
        logging.warning("[->] shareConfig in room " + str(croom))
        configJson=json.loads(cdata["Config"].replace("'", '"'));
        logging.info("Config: "+str(configJson))
        oldsessionname=session["sessionname"]
        ThisSession = db.session.query(models.Sessions).filter(models.Sessions.name.like(oldsessionname)).first()
        oldconfig=ThisSession.config
        # Update modified config
        for section in configJson:
            thisConfigSection=configJson[section]
            for key in thisConfigSection:
                ThisSession.config[section][key]=thisConfigSection[key]
        flag_modified(ThisSession,"config")
        db.session.commit()
        sdata = {"Config":configJson};
        socketio.emit('receive_deploy_Config', sdata,room=croom)
    except Exception as e:
        logging.error("err : "+str(e))
        logging.error("cData : "+str(cdata))
        traceback.print_exc(file=sys.stderr)
 
@socketio.on('move_tile')
def handle_click_event(cdata):
    global tiles_data
    croom = cdata["room"]
    logfun("[->] Click on tile " + str(cdata["id"]) + " in room " + str(croom))
    logging.info("[+] Position: (" + str(cdata["posX"]) + ", " + str(cdata["posY"])+ ")")
 
    session_id = request.sid
    sdata = {"id":cdata["id"],"posX":cdata["posX"], "posY":cdata["posY"], "session_id":session_id}
 
    socketio.emit('receive_move', sdata, room=croom )
    tileid=tiles_data["nodes"][int(cdata["id"])]["dbid"]
    logging.debug("move id = "+cdata["id"]+" db id = "+str(tileid))
    tile=db.session.query(models.Tiles).filter_by(id=tileid).one()
    logging.debug("title = "+tile.title)
    logging.debug("old pos  = (%d,%d)" % (int(tile.pos_px_x),int(tile.pos_px_y)))
    tile.pos_px_x=int(cdata["posX"])
    tile.pos_px_y=int(cdata["posY"])
    logging.debug("new pos  = (%d,%d)" % (int(tile.pos_px_x),int(tile.pos_px_y)))
    db.session.commit()
 
    logging.info("[+] New position for tile " + cdata["id"] + " transmitted to " + str(len(room_dict[croom])) + " sockets")
    return [cdata["id"], 2, croom]
 
 
@socketio.on("click_Menu")
def MenuShare(cdata):
    croom=cdata["room"]
    logging.info("MenuShare :"+str(cdata))
    logfun("[->] Click on menu " + str(cdata["Menu"]) + " option icon " + str(cdata["optionButton"]) + " in room " + str(croom))
 
    sdata = {"Menu":cdata["Menu"], "optionNumber":cdata["optionNumber"], "optionButton":cdata["optionButton"]}
    socketio.emit('receive_Menu_click', sdata,room=croom)
 
@socketio.on("click")
def ClickShare(cdata):
    croom=cdata["room"]
    action=cdata["action"]
    logging.info(action+"Share :"+str(cdata))
    logfun("[->] Click on "+action+ " "+ str(cdata["id"]) + " in room " + str(croom))
    sdata = {"action":action,"id":cdata["id"]}
    socketio.emit('receive_click', sdata,room=croom)
 
@socketio.on("click_val")
def ClickShareVal(cdata):
    croom=cdata["room"]
    action=cdata["action"]
    logging.info(action+"Share with val :"+str(cdata))
    logfun("[->] Click on "+action+ " "+ str(cdata["id"]) + " with val " + str(cdata["val"]) + " in room " + str(croom))
    sdata = {"action":action,"id":cdata["id"],"val":cdata["val"]}
    socketio.emit('receive_click_val', sdata,room=croom)
 
@socketio.on("change_Opacity")
def changeOpacityShare(cdata):
    croom=cdata["room"]
    logging.info("changeOpacityShare :"+str(cdata))
    logfun("[->] Click on an opacity slider " + str(cdata["Id"]) + " in room " + str(croom))
 
    sdata = {"Id":cdata["Id"],"Opacity":cdata["Opacity"]}
    socketio.emit('receive_Force_Opacity', sdata,room=croom)
 
@socketio.on("add_Tag")
def addNewTagShare(cdata):
    croom=cdata["room"]
    logging.info("addNewTagShare :"+str(cdata))
    logfun("[->] Add a new tag " + str(cdata["NewTag"]) + " in room " + str(croom) + " from button " + str(cdata["option"]))
 
    sdata = {"NewTag":cdata["NewTag"],"option":cdata["option"]}
    socketio.emit('receive_Add_Tag', sdata,room=croom)
 
@socketio.on("switch_MultipleTag")
def switchMultipleTagShare(cdata):
    croom=cdata["room"]
    logging.info("MultipleTagsSelection :"+str(cdata))
    logfun("[->] Switch a multiple tags selection " + str(cdata["SelTags"]) + " in room " + str(croom) + " on/off " + str(cdata["bool"]))
 
    sdata = {"SelTags":cdata["SelTags"],"bool":cdata["bool"]}
    socketio.emit('receive_multiple_Tags', sdata,room=croom)
 
 
@socketio.on("color_Tag")
def colorTagShare(cdata):
    croom=cdata["room"]
    logging.info("changeColorTagShare :"+str(cdata))
    logfun("[->] Change color for the tag " + str(cdata["OldTag"]) + " for " + str(cdata["TagColor"]) +" in room " + str(croom))
 
    sdata = {"OldTag":cdata["OldTag"],"TagColor":cdata["TagColor"]}
    socketio.emit('receive_Color_Tag', sdata,room=croom)
 
@socketio.on("action_click")
def ClickAction(cdata):
    if (session["is_client_active"]):
        croom=cdata["room"]
        action=cdata["action"]
        TS=cdata["TileSet"]
        selections=cdata["selections"]
        logging.info(action+" for TileSet "+TS)
        logfun("[->] Click on action "+action+ " "+ str(cdata["id"]) + " in room " + str(croom) + " for selection "+ str(selections))
 
        actionid=int(action.replace("action", ""))
        command=str(actionid)+","+selections
 
        oldtileset=db.session.query(models.TileSets).filter_by(name=TS).one()
        oldconnection=oldtileset.connections
        user_id=get_user_id("action_click",session["username"])
 
        if (not oldconnection):
            logging.error("[->] NO Connection : "+str(oldconnection)+" on tileset "+str(oldtileset)+" for user "+str(user_id))
            return
 
        if  (user_id != oldconnection.id_users or not session["is_client_active"]):
            logging.error("[->] Connection id : "+str(oldconnection.id_users)+" for user "+str(user_id)+"  and session active "+str(session["is_client_active"]))
            myflush()
            return
 
        logfun("actionid %d" % (actionid))
        myflush()
        if (actionid == 0):
            ThisSession=db.session.query(models.Sessions).filter(models.Sessions.name.like(session["sessionname"])).first()
            # save old nodes.json before get new
            out_nodes_json = os.path.join("/TiledViz/TVFiles", str(oldconnection.id_users), str(oldconnection.id),"nodes.json")
            mvDATE=datetime.datetime.now().isoformat().replace(":","-")
            save_nodes_json=out_nodes_json+"_"+mvDATE
            logging.warning("action 0 : Save old nodes.json in %s" % (save_nodes_json))
            myflush()
            os.system("mv "+out_nodes_json+" "+save_nodes_json)
            #shutil.copyfile(out_nodes_json,save_nodes_json)
            # selection must be all tileset
            command=str(actionid)+","+","
        logfun("action command %s" % (command))
        myflush()
 
        searchKillid=re.search(r'"killid":"\d+"',session["connection"+str(oldconnection.id)])
        killid=-1
        if (searchKillid):
            killid=int(searchKillid.group().replace('"killid":','').replace('"',''))
            if (actionid==killid):
                logging.warning("action %d : Find kill_all_containers action." % (actionid))
                command=str(actionid)+","+","
 
        logging.warning("action: "
                        +str(session["username"])+" ; "
                        +str(oldtileset.id)+" ; "
                        +str(oldconnection.id)+" ; "
                        +str(command))
        myflush()
 
        if (searchKillid):
            if (actionid==killid):
                time.sleep(timeAlive)
                logging.warning("action %d : Remove connection %d ." % (actionid,oldconnection.id))
                remove_this_connection(oldtileset,oldconnection.id,user_id)
 
        if (actionid == 0):
            try:
                logging.warning("Update nodes for session %s" % (session["sessionname"]))
                myflush()
                ThisSession=db.session.query(models.Sessions).filter(models.Sessions.name.like(session["sessionname"])).first()
                # action0 == get new nodes.json file
                time.sleep(timeAlive)
 
                # copy old nodes.json
                out_nodes_json = os.path.join("/TiledViz/TVFiles", str(oldconnection.id_users), str(oldconnection.id),"nodes.json")
                #diff save_nodes_json out_nodes_json ?
 
                # Old tile set data
                with open(save_nodes_json) as save_json_tiles_file:
                    tiledata1=json.loads(save_json_tiles_file.read())
                    save_json_tiles_file.close()
 
                # Add old index => For oldtiledset.tiles ??
                itiledata1={"nodes":[]};
                for idx, tile in enumerate(tiledata1["nodes"]):
                    itiledata1["nodes"].append({"i":idx, "title":tile["title"]})
 
                # New tile set data
                count_exist_new_nodes=0
                not_loaded=True
                while(not_loaded):
                    try:
                        time.sleep(timeAlive)
                        with open(out_nodes_json) as json_tiles_file:
                            tiledata2=json.loads(json_tiles_file.read())
                            json_tiles_file.close()
                        not_loaded=False
                    except Exception as err:
                        count_exist_new_nodes=count_exist_new_nodes+1
                        NbIter=10
                        if ( count_exist_new_nodes > NbIter):
                            traceback.print_exc(file=sys.stderr)
                            strerror=str(err)
                            logging.error("After %d x %d s we still have this error :\n %s" % (NbIter, timeAlive,strerror))
                            return
 
                # sort Tiles data for old and new version of tileset
                sortdata1 = sorted(tiledata1["nodes"], key=lambda v: v["title"])
                isortdata1 = sorted(itiledata1["nodes"], key=lambda v: v["title"])
                sortdata2 = sorted(tiledata2["nodes"], key=lambda v: v["title"])
 
                connectionbool=True
                urlbool=False
                datapath=""
                creation_date=datetime.datetime.now()
 
                # DIFF sorted tiledata1 and tildedata2 and modify them in oldtiledset.tiles
                logging.warning("DIFF sorted tiledata1 and tildedata2 ")
                myflush()
                modtiles_data=[]
                for idx1, tile1 in enumerate(sortdata1):
                    found=False
                    for idx2, tile2 in enumerate(sortdata2):
                        if (tile1 == tile2):
                            del sortdata2[idx2]
                            logging.debug("OK equal tiles %d %d " % (idx1, idx2))
                            found=True
                            break
                        elif (tile1["url"] == tile2["url"]):
                            logging.debug("equal url modify tiles %d %d " % (idx1, idx2))
                            i=isortdata1[idx1]["i"]
 
                            title,name,comment,tags,variable,pos_px_x,pos_px_y,IdLocation,url,ConnectionPort = \
                                convertTile(tile2,oldtileset.name,connectionbool,urlbool,datapath)
 
                            oldtileset.tiles[i].tags=tags
                            oldtileset.tiles[i].source= {"name" : name,
                                                         "connection" : ConnectionPort,
                                                         "url" : url,
                                                         "variable": variable}
                            flag_modified(oldtileset.tiles[i],"source")
 
                            oldtileset.tiles[i].pos_px_x= pos_px_x
                            oldtileset.tiles[i].pos_px_y= pos_px_y
                            oldtileset.tiles[i].IdLocation=IdLocation
                            db.session.commit()
                            logging.debug("OK equal url modify tile")
 
                            modtiles_data.append((i,tile2))
 
                            del sortdata2[idx2]
                            found=True
                            break
 
                # emit receive_deploy_nodes
                sdata = {"id":cdata["id"], "modtiles_data":modtiles_data}
                logging.debug("receive_deploy_nodes data : "+str(sdata))
                myflush()
 
                socketio.emit('receive_deploy_nodes', sdata,room=croom)
            except Exception as err:
                traceback.print_exc(file=sys.stderr)
                strerror=str(err)
                logging.error(strerror)
 
# Draw
sidDraw=""
@socketio.on("drawBlob")
def drawBlobShare(cdata):
    sidDraw = request.sid
    croom=cdata["room"]
    logging.info("drawBlobShare :"+str(cdata))
    logfun("[->] Share image from draws with blob " + str(cdata["nodeId"]) + " in room " + str(croom))
    nbsend=cdata["nbsend"]
    # start send draw to all clients
    for csid in room_dict[croom]:
        if ( not csid == sidDraw ):
            socketio.emit('receive_draw_img', cdata,room=croom+str(csid))
            logging.info("Send draw_img signal to client "+croom+str(csid))
 
@socketio.on("uploadDraw")
def uploadDraw(cdata):
    croom=cdata["room"]
    #logging.info("Receive updloadDraw from client "+str(request.sid))
    for csid in room_dict[croom]:
        if ( not csid == sidDraw ):
            socketio.emit('receive_draw_part', cdata,room=croom+str(csid))
            #logging.info("Send draw part to client "+croom+str(csid)+" from "+str(cdata["offset"])+" to "+str(cdata["offsetEnd"]))
 
@socketio.on("modif_draws")
def ModifDraws(cdata):
    croom=cdata["room"]
    action=cdata["action"]
    logging.debug(action+" Share :"+str(cdata))
    logfun("[->] Modification of draw from "+str(cdata["nodeId"])+ " with "+action + " in room " + str(croom))
    sdata = {"nodeId":cdata["nodeId"]}
    socketio.emit('receive_'+action, sdata,room=croom)
 
# Connection
@socketio.on('connected_grid')#, namespace='/grid')
def config_client(cdata):
    logging.warning("[->] Socket connected to a grid")
    logging.info("[+] Project : " + str(cdata['project']))
    logging.info("[+] Session : " + str(cdata['session']))
 
    logging.warning("Connect "+str(cdata["user"])+str(cdata["project"])+str(cdata["session"]))
    croom = cdata['session'] # c[lient]room
    session["projectname"]=cdata['project']
    session["sessionname"]=cdata['session']
    join_room(croom)
    clients.append(request.sid)
    logging.debug(rooms()) # rooms() list the rooms for the socket
    #if not(croom in room_dict):
     #   room_dict.append(croom)
    # if room_dict.has_key(croom): # has_key is deprecated in python3
    if croom in room_dict:
        room_dict[croom].append(request.sid)
        logging.info("[+] " + croom + " : Now " + str(len(room_dict[croom])) + " sockets connected to the room")
    else:
        room_dict[croom] = [request.sid]
        if ("username" in session):
            logging.info("[+] " + croom + " : First client to join the room "+session["username"])
        else:
            logging.info("[+] " + croom + " : Anonymous client to join the room.")
    croomsid = cdata['session']+str(request.sid) # c[lient]room
    join_room(croomsid)
    room_dict[croomsid] = [request.sid]
    if ("username" in session):
        logging.info("[+] " + croomsid + " : Individual room "+session["username"])
    else:
        logging.info("[+] " + croomsid + " : Individual room for Anonymous client.")
 
    clean_rooms()
    logging.info("room_dict are : "+ str(room_dict) + " my rooms "+ str(croom)+ " and "+str(croomsid))
 
    try:
        sdata = {"part_nbr_update": str(len(room_dict[croom])-1)}
        socketio.emit("new_client", sdata, room=croom)
    except:
        traceback.print_exc(file=sys.stderr)
        logging.error("Unknown room %s id %d in  room_dict %s " % (croom,croomsid,str(room_dict)))
    return {"room":croom, "session_id":request.sid}
 
@socketio.on("disconnect")
def disconnect_socket():
    logging.debug("[<-] Socket disconnected")
    tmp_sid = request.sid
    for key in room_dict:
        if tmp_sid in room_dict[key]:
            room_dict[key].remove(tmp_sid)
            logging.info ("[-] " + key + " : Socket " + tmp_sid + " disconnected")
            sdata = {"part_nbr_update": str(len(room_dict[key])-1)}
            socketio.emit("new_client", sdata, room=key)
    return room_dict
 
def clean_rooms():
    try:
        for key in room_dict:
            if (not room_dict[key]):
                room_dict.pop(key)
    except:
        time.sleep(2)
        clean_rooms()
 
@socketio.on("get_link")
def handle_invite_link_request(cdata):
    croom = cdata["session"]
    is_new_client_active = cdata["type"]
    max_uses = cdata.get("max_uses", 1)
    invitee_name = cdata.get("invitee_name", "")
 
    # SECURITY CHECK: Verify user is logged in and has permission to create invites
    if "username" not in session or session["username"] == "Anonymous":
        socketio.emit("get_link_back", {"error": "You must be logged in to create invitation links"}, room=croom)
        return
 
    # SECURITY CHECK: Verify user is a member of the project
    if "projectname" not in session or "sessionname" not in session:
        socketio.emit("get_link_back", {"error": "Invalid session context"}, room=croom)
        return
 
    # Get current user
    current_user = db.session.query(models.Users).filter_by(name=session["username"]).first()
    if not current_user:
        socketio.emit("get_link_back", {"error": "User not found"}, room=croom)
        return
 
    # Get session and project
    session_obj = db.session.query(models.Sessions).filter(models.Sessions.name.like(session["sessionname"])).first()
    if not session_obj:
        socketio.emit("get_link_back", {"error": "Session not found"}, room=croom)
        return
 
    project_id = session_obj.id_projects
    if not project_id:
        socketio.emit("get_link_back", {"error": "Session has no associated project"}, room=croom)
        return
 
    # SECURITY CHECK: Verify user is a project member with appropriate permissions
    user_membership = db.session.query(models.ProjectMembers).filter_by(
        project_id=project_id,
        user_id=current_user.id
    ).first()
 
    if not user_membership:
        socketio.emit("get_link_back", {"error": "You must be a project member to create invitation links"}, room=croom)
        return
 
    # SECURITY CHECK: Only owners can create invites
    if user_membership.role_type not in valid_manage_members:
        socketio.emit("get_link_back", {"error": f"Insufficient permissions. Your role '{user_membership.role_type}' cannot create invitation links"}, room=croom)
        return
 
    # Validate max_uses
    try:
        max_uses = int(max_uses)
        if max_uses < 1:
            socketio.emit("get_link_back", {"error": "Maximum uses must be a positive number"}, room=croom)
            return
    except (ValueError, TypeError):
        socketio.emit("get_link_back", {"error": "Invalid maximum uses value"}, room=croom)
        return
 
    # For active links, validate that the invitee exists
    if is_new_client_active:
        if not invitee_name:
            socketio.emit("get_link_back", {"error": "Invitee name is required for active links"}, room=croom)
            return
 
        # Check if the invitee exists in the database
        invitee = db.session.query(models.Users).filter_by(name=invitee_name).first()
        if not invitee:
            socketio.emit("get_link_back", {"error": f"User {invitee_name} does not exist"}, room=croom)
            return
 
    if (is_new_client_active):
        client_type = "active"
    else:
        client_type = "passive"
 
    try:
        DEFAULT_URL=os.getenv("SERVER_NAME")+"."+os.getenv("DOMAIN")
        print(DEFAULT_URL)
    except:
        DEFAULT_URL = "0.0.0.0:5000"
 
    creation_date = datetime.datetime.now().isoformat()
 
    # Generate a unique key for the invitation
    key = linkrandom(32)
    try:
        key = key.decode('utf-8')
    except AttributeError:
        pass  # key est déjà une str
 
    # Créer la clé du lien (à stocker dans la base)
    if (client_type == "active"):
        link_key = f"{session['sessionname']}{linkChar}{client_type}{linkChar}{invitee_name}{linkChar}{creation_date}{linkChar}{key}"
    else:
        link_key = f"{session['sessionname']}{linkChar}{client_type}{linkChar}Anonymous{linkChar}{creation_date}{linkChar}{key}"
 
    # Générer le lien complet à afficher à l'utilisateur
    sdata = {
        "link": f"https://{DEFAULT_URL}/join/{link_key}",
        "max_uses": max_uses
    }
 
    # Store ONLY the key in the database
    try:
        ErrLink="Full link %s with session %s " % (sdata["link"],session["sessionname"])
        flash(ErrLink)
        logging.warning(ErrLink)
        invite_link = models.InviteLinks(
            link=link_key,
            host_user=session["username"],
            host_project=session["projectname"],
            type=is_new_client_active,
            creation_date=datetime.datetime.now(),
            max_uses=max_uses,
            use_count=0,
            id_sessions=db.session.query(models.Sessions.id).filter(models.Sessions.name.like(session["sessionname"])).scalar(),
            id_users=invitee.id if is_new_client_active else None
        )
        db.session.add(invite_link)
        db.session.commit()
    except Exception as e:
        logging.error(f"Error storing invite link: {str(e)}")
        db.session.rollback()
        socketio.emit("get_link_back", {"error": "Failed to create invitation link"}, room=croom)
        return
 
    socketio.emit("get_link_back" ,sdata,room=croom)
 
@app.route("/join/<link>")
def handle_join_with_invite_link(link):
    logging.warning("Handle join with invite link : " + link)
 
    # Parse the link components
    link_parts = link.split(linkChar)
    if len(link_parts) < 5:
        flash("Invalid invitation link : wrong parts number %d in your link." % (len(link_parts)))
        return redirect(url_for(".index"))
 
    session_name = link_parts[0]
    new_client_type = link_parts[1]
    username = link_parts[2]
    creation_date = link_parts[3]
    key = link_parts[4]
    hasgeom=False
    if len(link_parts) == 6:
        geom = link_parts[5]
        hasgeom=True
        logging.warning("Link with geom : %s " % (geom))
        link=session_name+linkChar+new_client_type+linkChar+username+linkChar+creation_date+linkChar+key
 
    # Check if the invitation exists and is valid
    invite_link = db.session.query(models.InviteLinks).filter_by(link=link).first()
    if not invite_link:
        flash("Invalid invitation link : not found in DB")
        return redirect(url_for(".index"))
 
    # Check if the invitation has reached its maximum number of uses
    if invite_link.use_count >= invite_link.max_uses:
        flash("This invitation has reached its maximum number of uses")
        delelement(models.InviteLinks, "link "+link, invite_link.id)
        db.session.commit()
        return redirect(url_for(".index"))
 
    if (datetime.datetime.now() > invite_link.creation_date+datetime.timedelta(seconds=LinkExpiredAfterSec)):
        #datetime.datetime.strptime(invite_link.creation_date,'%Y-%m-%d %H:%M:%S.%f')
        flash("This invitation created at %s is older than expiration time after %d minutes" % (invite_link.creation_date,LinkExpiredAfterSec/60) )
        delelement(models.InviteLinks, "link "+link, invite_link.id)
        db.session.commit()
        return redirect(url_for(".index"))
 
    # For active links, check if the user is logged in and matches the invitee
    user = None
    if new_client_type == "active":
        if "username" not in session:
            # Store the link in session for after login
            session["pending_invite_link"] = link
            flash("You must be logged in to use this invitation link")
            return redirect(url_for(".login"))
 
        # Verify that the logged-in user matches the invitee
        if session["username"] != username:
            flash("This invitation link is for a different user")
            return redirect(url_for(".index"))
 
        # Verify that the user exists in the database
        user = db.session.query(models.Users).filter_by(name=username).first()
        if not user:
            flash("Invalid user account : user not found")
            return redirect(url_for(".index"))
 
    # Increment the use count
    invite_link.use_count += 1
    db.session.commit()
 
    # Set session variables
    session["sessionname"] = session_name
    session["is_client_active"] = (new_client_type == "active")
    session["username"] = username
 
    # Get session details
    ThisSession = db.session.query(models.Sessions).filter_by(name=session_name).first()
    if not ThisSession:
        flash_mess="Anonymous connection with unknown session : "+str(session["sessionname"])
        logging.error(flash_mess)
        flash(flash_mess+". Please check your command line")
        return redirect(url_for(".index"))
 
    try:
        session["projectname"]=ThisSession.projects.name
        project_id = ThisSession.projects.id
    except:
        ErrLink="Error in link %s with project of session %s " % (link,ThisSession)
        flash(ErrLink)
        logging.error(ErrLink)
        return redirect(url_for(".index"))
 
    # Synchronize user to project and session if it's an active link
    if new_client_type == "active" and user:
        sync_success, sync_message = sync_user_to_project_and_session(
            user_id=user.id,
            project_id=project_id,
            session_id=ThisSession.id,
            role_type='guest'  # Default role for invited users
        )
        if not sync_success:
            logging.warning(f"Failed to synchronize user {user.name}: {sync_message}")
            flash(f"Warning: {sync_message}")
        else:
            logging.info(f"Successfully synchronized user {user.name} to project and session")
 
    session["geometry"]='{}'
    if "passive" in link:
        if (hasgeom):
            my_geom=geom.replace('{','{"').replace(',',',"').replace('=','":')
            logging.warning("str json my_geom "+str(my_geom))
            session["geometry"]=my_geom
 
    # Check if the invitation has reached its maximum number of uses and will be deleted
    if invite_link.use_count >= invite_link.max_uses:
        delelement(models.InviteLinks, "link "+link, invite_link.id)
        db.session.commit()
    logging.warning("session after join before grid : "+str(session))
    return redirect('/grid')
    #return(redirect("/grid", project=room))
    #return ("pouet")
 
# ====================================================================
# Error management
@app.errorhandler(400)
def bad_request(e):
    logging.error("Bad request or cookie expired for user "+str(session["username"]))
    return render_template('error_pages/400.html'), 400
 
@app.errorhandler(502)
def bad_req(e):
    logging.error("Cookie expired for user "+str(session["username"]))
    return render_template('error_pages/502.html'), 502
 
@app.errorhandler(504)
def bad_connect(e):
    logging.error("Error of TVSecure for user "+str(session["username"]))
    return render_template('error_pages/504.html'), 504
 
 
@app.errorhandler(404)
def not_found(e):
    return render_template('error_pages/404.html'), 404
 
@app.errorhandler(429)
def not_found(e):
    return render_template('error_pages/429.html'), 429
 
@app.errorhandler(500)
def TVerror(e):
    logging.error("Error with TiledViz for user "+str(session["username"]))
    return render_template('error_pages/500.html'), 500
 
# # Proxy VNC
# # Thank's to https://stackoverflow.com/posts/36601467/revisions
# @app.route('/<path:dummy>')
# def routevnc(path=None,dummy=None):
#     is_noVNC=re.search(r''+"noVNC",dummy)
#     is_images=re.search(r''+"(images|favicon.ico)",dummy)
#     if ( is_images ):
#         logging.warning("is_images : \n "+str(is_images))
#         #dummy == "favicon.ico"
#         logging.warning("proxy favicon : \n "+str(request.host_url))
#         resp = requests.request(
#             method=request.method,
#             url=request.host_url,
#             headers={key: value for (key, value) in request.headers if key != 'Host'},
#             data=request.get_data(),
#             cookies=request.cookies,
#             allow_redirects=False)
#     elif (is_noVNC) :
#         #logging.debug("request : \n "+str(request))
#         logging.debug("proxy noVNC path : \n "+str(path)+":"+str(dummy))
#         logging.debug("routevnc : \n url "+str(request.host_url))
#         # logging.error("routevnc : \n url "+str(request.host_url)+"\n method "+str(request.method).replace("\r","")+"\n header"+str(request.headers).replace("\r","")+"\n args :"+str(request.args).replace("\r",""))
 
#         VNCurl="https://"+app.config["SERVER_NAME"]
#         logfun("Connect with url : "+VNCurl+dummy)
#         newurl=request.url.replace(request.host_url, VNCurl)
#         logfun("Replace url : "+newurl)
 
#         logging.debug(dummy+" header :"+str(request.headers))
#         resp = requests.request(
#             method=request.method,
#             url=newurl,
#             headers={key: value for (key, value) in request.headers if key != 'Host'},
#             data=request.get_data(),
#             cookies=request.cookies,
#             allow_redirects=False)
#     # elif (is_images) :
#     #     logging.warning("images request : \n "+str(request))
#     #     logging.warning("proxy images path : \n "+str(path)+":"+str(dummy))
#     #     logging.warning("host_url : \n url "+str(request.host_url))
#     #     # logging.error("routevnc : \n url "+str(request.host_url)+"\n method "+str(request.method).replace("\r","")+"\n header"+str(request.headers).replace("\r","")+"\n args :"+str(request.args).replace("\r",""))
 
#     #     LocalUrl="https://localhost:5000/"
#     #     logfun("Connect with url : "+LocalUrl+dummy)
#     #     newurl=request.url.replace(request.host_url, LocalUrl)
#     #     logfun("Replace url : "+newurl)
 
#     #     logging.warning(dummy+" header :"+str(request.headers))
#     #     resp = requests.request(
#     #         method=request.method,
#     #         url=newurl,
#     #         headers={key: value for (key, value) in request.headers if key != 'Host'},
#     #         data=request.get_data(),
#     #         cookies=request.cookies,
#     #         allow_redirects=False)
#     else:
#         logging.warning("proxy unknwon path : \n "+str(path)+":"+str(dummy)+"  "+str(request.host_url))
#         resp = requests.request(
#             method=request.method,
#             url=request.host_url,
#             headers={key: value for (key, value) in request.headers if key != 'Host'},
#             data=request.get_data(),
#             cookies=request.cookies,
#             allow_redirects=False)
 
#     excluded_headers = ['content-encoding', 'content-length', 'transfer-encoding', 'connection']
#     headers = [(name, value) for (name, value) in resp.raw.headers.items()
#                if name.lower() not in excluded_headers]
 
#     response = Response(resp.content, resp.status_code, headers)
#     return response
 
@app.route("/revoke_invite/<int:invite_id>", methods=["POST"])
def revoke_invite(invite_id):
    if "username" not in session:
        return jsonify({"error": "User not logged in"}), 401
 
    invite_link = db.session.query(models.InviteLinks).filter_by(id=invite_id).first()
    if not invite_link:
        return jsonify({"error": "Invitation not found"}), 404
 
    # Check if the user has permission to revoke this invitation
    if invite_link.host_user != session["username"]:
        return jsonify({"error": "Not authorized to revoke this invitation"}), 403
 
    invite_link.is_revoked = True
    db.session.commit()
 
    return jsonify({"message": "Invitation revoked successfully"})
 
# Manage project members
@app.route('/project_members/<int:project_id>/members', methods=["GET", "POST"])
def project_members(project_id):
    if ("username" not in session or session["username"] == "Anonymous"):
        return redirect("/login")
 
    user_id = get_user_id("Project Members", session["username"])
 
    # Check if project exists
    project = db.session.query(models.Projects).filter_by(id=project_id).first()
    if not project:
        flash("Project not found!")
        return redirect("/project")
 
    # Check if user has permission to manage this project (owner)
    if not can_manage_project(project_id, user_id):
        flash("You don't have permission to manage members for this project!")
        return redirect("/project")
 
    # Get all current project members using utility function
    current_members = get_project_members(project_id)
 
    # Get all available users for adding using utility function
    available_users = get_available_users_for_project(project_id)
 
    # Handle form submissions
    if request.method == "POST":
        if 'add_member' in request.form:
            new_user_id = request.form.get('user_id')
            new_role = request.form.get('role_type')
 
            if new_user_id and new_role:
                success, message = add_project_member(project_id, int(new_user_id), new_role)
                flash(message)
                if success:
                    # Refresh the page to show updated member list
                    return redirect(url_for('project_members', project_id=project_id))
 
        elif 'remove_member' in request.form:
            member_id = request.form.get('member_id')
            if member_id:
                success, message = remove_project_member(project_id, int(member_id))
                flash(message)
                if success:
                    # Refresh the page to show updated member list
                    return redirect(url_for('project_members', project_id=project_id))
 
        elif 'change_role' in request.form:
            member_id = request.form.get('member_id')
            new_role = request.form.get('new_role')
 
            if member_id and new_role:
                success, message = update_member_role(project_id, int(member_id), new_role)
                flash(message)
                if success:
                    # Refresh the page to show updated member list
                    return redirect(url_for('project_members', project_id=project_id))
 
        elif 'transfer_ownership' in request.form:
            new_owner_id = request.form.get('new_owner_id')
            if new_owner_id:
                success, message = transfer_project_ownership(project_id, user_id, int(new_owner_id))
                flash(message)
                if success:
                    # Refresh the page to show updated member list
                    return redirect(url_for('project_members', project_id=project_id))
 
        # Redirect to refresh the page
        return redirect(url_for('project_members', project_id=project_id))
 
    # Get updated member list for display
    current_members = get_project_members(project_id)
 
    return render_template(
        "project_members.html",
        project=project,
        username=session["username"],
        current_members=current_members,
        available_users=available_users,
        title=f"Manage Members - {project.name}"
    )
 
# API endpoint for ownership transfer with comprehensive validation
@app.route('/api/project/<int:project_id>/transfer-ownership', methods=['POST'])
def transfer_ownership_api(project_id):
    """
    API endpoint for transferring project ownership with comprehensive validation
    """
    if "username" not in session or session["username"] == "Anonymous":
        return jsonify({"error": "Authentication required"}), 401
 
    user_id = get_user_id("Transfer Ownership", session["username"])
 
    try:
        data = request.get_json()
        if not data or 'new_owner_id' not in data:
            return jsonify({"error": "new_owner_id is required"}), 400
 
        new_owner_id = data['new_owner_id']
 
        # Use utility function for ownership transfer
        success, message = transfer_project_ownership(project_id, user_id, new_owner_id)
 
        if success:
            # Get new owner details for response
            new_owner = db.session.query(models.Users).filter_by(id=new_owner_id).first()
            return jsonify({
                "message": message,
                "new_owner": {
                    "id": new_owner.id,
                    "name": new_owner.name
                },
                "previous_owner": {
                    "id": user_id,
                    "name": session["username"]
                }
            }), 200
        else:
            return jsonify({"error": message}), 400
 
    except Exception as e:
        db.session.rollback()
        logging.error(f"Error in ownership transfer API: {str(e)}")
        return jsonify({"error": "Internal server error during ownership transfer"}), 500
 
# API endpoint to check ownership constraints
@app.route('/api/project/<int:project_id>/ownership-constraints', methods=['GET'])
def check_ownership_constraints(project_id):
    """
    API endpoint to check ownership constraints and business rules
    """
    if "username" not in session or session["username"] == "Anonymous":
        return jsonify({"error": "Authentication required"}), 401
 
    user_id = get_user_id("Check Ownership Constraints", session["username"])
 
    try:
        # Validate project exists
        project = db.session.query(models.Projects).filter_by(id=project_id).first()
        if not project:
            return jsonify({"error": "Project not found"}), 404
 
        # Get current user's membership
        user_membership = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            user_id=user_id
        ).first()
 
        if not user_membership:
            return jsonify({"error": "User is not a member of this project"}), 403
 
        # Count owners
        owner_count = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id,
            role_type='owner'
        ).count()
 
        # Get all members for ownership transfer options
        all_members = db.session.query(models.ProjectMembers).filter_by(
            project_id=project_id
        ).all()
 
        # Filter members who can receive ownership (not already owners)
        eligible_members = []
        for member in all_members:
            if member.role_type != 'owner' and member.user_id != user_id:
                user_info = db.session.query(models.Users).filter_by(id=member.user_id).first()
                if user_info:
                    eligible_members.append({
                        "id": member.user_id,
                        "name": user_info.name,
                        "role": member.role_type
                    })
 
        return jsonify({
            "is_owner": user_membership.role_type == 'owner',
            "owner_count": owner_count,
            "is_last_owner": owner_count <= 1 and user_membership.role_type == 'owner',
            "can_transfer_ownership": user_membership.role_type == 'owner' and len(eligible_members) > 0,
            "eligible_for_ownership_transfer": eligible_members,
            "constraints": {
                "cannot_remove_last_owner": owner_count <= 1,
                "must_be_member_to_receive_ownership": True,
                "cannot_transfer_to_self": True,
                "cannot_transfer_to_existing_owner": True
            }
        }), 200
 
    except Exception as e:
        logging.error(f"Error checking ownership constraints: {str(e)}")
        return jsonify({"error": "Internal server error"}), 500
 
# Display user projects with management options
@app.route('/my-projects', methods=["GET"])
def my_projects():
    if ("username" not in session or session["username"] == "Anonymous"):
        return redirect("/login")
 
    user_id = get_user_id("My Projects", session["username"])
 
    # Get all user projects using utility function
    user_projects = get_user_projects(user_id)
 
    # Organize projects by role
    projects_by_role = {
        'owned': [],
        'member': []
    }
 
    # Categorize projects by role
    for project, role_type in user_projects:
        project_data = {
            'project': project,
            'role': role_type,
            'can_manage': role_type in valid_manage_members
        }
 
        if role_type == 'owner':
            projects_by_role['owned'].append(project_data)
        else:
            projects_by_role['member'].append(project_data)
 
    return render_template(
        "my_projects.html",
        projects_by_role=projects_by_role,
        username=session["username"],
        title="My Projects"
    )
 
 
# Migration management routes
@app.route('/admin/migration', methods=['GET', 'POST'])
def migration_admin():
    """Admin interface for managing database migrations"""
    if ("username" not in session or session["username"] == "Anonymous"):
        return redirect("/login")
 
    user_id = get_user_id("Migration Admin", session["username"])
    User = db.session.query(models.Users).filter_by(name=session["username"]).one()
 
    if not User.is_admin:
        flash("You must be an administrator to access this page.")
        return redirect("/")
 
    from app.migration import migrate_project_owners, check_migration_status, validate_migration, rollback_migration, get_orphaned_projects
 
    # Handle migration actions
    if request.method == "POST":
        action = request.form.get('action')
 
        if action == 'run_migration':
            if migrate_project_owners():
                flash("Migration completed successfully!")
            else:
                flash("Migration failed! Check logs for details.")
 
        elif action == 'validate_migration':
            validation = validate_migration()
            if validation and validation['is_valid']:
                flash("Migration validation passed - data is consistent!")
            else:
                flash(f"Migration validation failed: {validation}")
 
        elif action == 'rollback_migration':
            if rollback_migration():
                flash("Migration rollback completed!")
            else:
                flash("Migration rollback failed! Check logs for details.")
 
    # Get current status
    status = check_migration_status()
    validation = validate_migration()
    orphaned_projects = get_orphaned_projects()
 
    return render_template(
        "migration_admin.html",
        status=status,
        validation=validation,
        orphaned_projects=orphaned_projects,
        title="Migration Management"
    )
 
@app.route('/admin/consistency', methods=['GET', 'POST'])
def consistency_admin():
    """
    Admin interface for session-project consistency management
    """
    if ("username" not in session or session["username"] == "Anonymous"):
        return redirect("/login")
 
    user_id = get_user_id("Consistency Admin", session["username"])
    User = db.session.query(models.Users).filter_by(name=session["username"]).one()
 
    if not User.is_admin:
        flash("You must be an administrator to access this page.")
        return redirect("/")
 
    sessions_data = []
    if request.method == 'GET':
        # Get all sessions with their consistency status
        sessions = db.session.query(models.Sessions).all()
        for session_obj in sessions:
            inconsistencies = validate_session_project_consistency(session_obj.id)
            sessions_data.append({
                'id': session_obj.id,
                'name': session_obj.name,
                'project_name': session_obj.projects.name if session_obj.projects else 'No Project',
                'inconsistencies': inconsistencies,
                'is_consistent': len(inconsistencies) == 0
            })
 
    if request.method == 'POST':
        action = request.form.get('action')
        session_id = request.form.get('session_id')
 
        if action == 'validate_session' and session_id:
            inconsistencies = validate_session_project_consistency(int(session_id))
            if inconsistencies:
                flash(f"Found {len(inconsistencies)} inconsistencies in session {session_id}")
                for inc in inconsistencies:
                    flash(inc)
            else:
                flash(f"Session {session_id} is consistent with its project")
 
        elif action == 'fix_session' and session_id:
            success, message = fix_session_project_inconsistencies(int(session_id))
            if success:
                flash(message)
            else:
                flash(f"Error: {message}")
 
        elif action == 'validate_all':
            # Validate all sessions
            sessions = db.session.query(models.Sessions).all()
            total_inconsistencies = 0
            inconsistent_sessions = 0
 
            for session_obj in sessions:
                inconsistencies = validate_session_project_consistency(session_obj.id)
                if inconsistencies:
                    inconsistent_sessions += 1
                    total_inconsistencies += len(inconsistencies)
                    flash(f"Session '{session_obj.name}' (ID: {session_obj.id}): {len(inconsistencies)} inconsistencies")
                    for inc in inconsistencies:
                        flash(f"  - {inc}")
 
            if total_inconsistencies == 0:
                flash("All sessions are consistent with their projects!")
            else:
                flash(f"Found {total_inconsistencies} inconsistencies across {inconsistent_sessions} sessions")
 
        elif action == 'fix_all':
            # Fix all inconsistencies
            sessions = db.session.query(models.Sessions).all()
            total_fixed = 0
            fixed_sessions = 0
 
            for session_obj in sessions:
                success, message = fix_session_project_inconsistencies(session_obj.id)
                if success and "Fixed" in message:
                    # Extract number from message
                    import re
                    match = re.search(r'Fixed (\d+)', message)
                    if match:
                        fixed_count = int(match.group(1))
                        total_fixed += fixed_count
                        if fixed_count > 0:
                            fixed_sessions += 1
                            flash(f"Session '{session_obj.name}': {message}")
 
            if total_fixed > 0:
                flash(f"Successfully fixed {total_fixed} inconsistencies across {fixed_sessions} sessions")
            else:
                flash("No inconsistencies found to fix")
 
    return render_template('admin_consistency.html',
                         title="Consistency Admin",
                         sessions=sessions_data,
                         **myrender())
 
 
@app.route('/project/<int:project_id>/invite', methods=['GET', 'POST'])
def project_invite(project_id):
    """
    Project invitation management interface
    """
    if ("username" not in session or session["username"] == "Anonymous"):
        return redirect("/login")
 
    # Get current user
    current_user = db.session.query(models.Users).filter_by(name=session["username"]).first()
    if not current_user:
        flash("User not found")
        return redirect("/")
 
    # Check if user can create invites for this project (owner only)
    can_create, reason = can_create_invite_links(current_user.id, project_id)
    if not can_create:
        flash(f"Access denied: {reason}")
        return redirect("/")
 
    # Get project details
    project = db.session.query(models.Projects).filter_by(id=project_id).first()
    if not project:
        flash("Project not found")
        return redirect("/")
 
    # Get project sessions
    sessions = db.session.query(models.Sessions).filter_by(id_projects=project_id).all()
 
    # Get existing invite links for this project
    invite_links = db.session.query(models.InviteLinks).filter_by(
        host_project=project.name
    ).order_by(models.InviteLinks.creation_date.desc()).all()
 
    if request.method == 'POST':
        action = request.form.get('action')
 
        if action == 'create_invite':
            session_id = request.form.get('session_id')
            invitee_name = request.form.get('invitee_name', '').strip()
            max_uses = request.form.get('max_uses', 1)
            invite_type = request.form.get('invite_type', 'passive')
 
            try:
                max_uses = int(max_uses)
                if max_uses < 1:
                    flash("Maximum uses must be a positive number")
                    return redirect(url_for('.project_invite', project_id=project_id))
            except (ValueError, TypeError):
                flash("Invalid maximum uses value")
                return redirect(url_for('.project_invite', project_id=project_id))
 
            # Validate session belongs to project
            session_obj = db.session.query(models.Sessions).filter_by(
                id=session_id,
                id_projects=project_id
            ).first()
            if not session_obj:
                flash("Invalid session for this project")
                return redirect(url_for('.project_invite', project_id=project_id))
 
            # For active invites, validate invitee exists
            if invite_type == 'active':
                if not invitee_name:
                    flash("Invitee name is required for active invitations")
                    return redirect(url_for('.project_invite', project_id=project_id))
 
                invitee = db.session.query(models.Users).filter_by(name=invitee_name).first()
                if not invitee:
                    flash(f"User '{invitee_name}' does not exist")
                    return redirect(url_for('.project_invite', project_id=project_id))
            else:
                invitee_name = "Anonymous"
 
            # Create invitation link
            creation_date = datetime.datetime.now().isoformat()
            key = linkrandom(32)
            try:
                key = key.decode('utf-8')
            except AttributeError:
                pass
 
            link_key = f"{session_obj.name}{linkChar}{invite_type}{linkChar}{invitee_name}{linkChar}{creation_date}{linkChar}{key}"
 
            try:
                invite_link = models.InviteLinks(
                    link=link_key,
                    host_user=current_user.name,
                    host_project=project.name,
                    type=(invite_type == 'active'),
                    creation_date=datetime.datetime.now(),
                    max_uses=max_uses,
                    use_count=0,
                    id_sessions=session_id,
                    id_users=invitee.id if invite_type == 'active' else None
                )
                db.session.add(invite_link)
                db.session.commit()
 
                full_link = f"https://{DEFAULT_URL}/join/{link_key}"
                flash(f"Invitation link created successfully: {full_link}")
 
            except Exception as e:
                db.session.rollback()
                logging.error(f"Error creating invite link: {str(e)}")
                flash("Failed to create invitation link")
 
        elif action == 'revoke_invite':
            invite_id = request.form.get('invite_id')
            invite_link = db.session.query(models.InviteLinks).filter_by(
                id=invite_id,
                host_project=project.name
            ).first()
 
            if invite_link:
                invite_link.is_revoked = True
                db.session.commit()
                flash("Invitation link revoked successfully")
            else:
                flash("Invitation link not found")
 
        return redirect(url_for('.project_invite', project_id=project_id))
 
    return render_template('project_invite.html',
                         title=f"Invite Users - {project.name}",
                         project=project,
                         sessions=sessions,
                         invite_links=invite_links,
                         **myrender())
 
# Email Verification Routes
@app.route('/verify-email/<token>')
def verify_email(token):
    """Verify user email with JWT token"""
    try:
        # Verify the token
        data = verify_token(token, app.secret_key)
        if data is None:
            flash('Invalid or expired verification link.', 'error')
            return redirect('/register')
 
        user_id = data.get('confirm_id')
        if not user_id:
            flash('Invalid verification link.', 'error')
            return redirect('/register')
 
        # Find user and verify email
        user = db.session.query(models.Users).filter_by(id=user_id).first()
        if not user:
            flash('User not found.', 'error')
            return redirect('/register')
 
        if user.is_verified:
            flash('User already verified. You can login.',"success")
            return redirect('/login')
 
        # Mark user as verified
        user.is_verified = True
        user.dateverified = datetime.datetime.now()
        db.session.commit()
 
        # Delete the sent email from IMAP (only if user has email)
        if user.mail:
            delete_sent_email("TiledViz - Email Verification", user.mail)
 
        flash('Email verified successfully! You can now log in.', 'success')
        return redirect('/login')
 
    except Exception as e:
        logging.error(f"Error verifying email: {e}")
        flash('An error occurred during verification. Please try again.', 'error')
        return redirect('/register')
 
@app.route('/resend-verification', methods=['GET', 'POST'])
def resend_verification():
    """Resend verification email"""
    if request.method == 'POST':
        email = request.form.get('email')
        if not email:
            flash('Please enter your email address.', 'error')
            return render_template('resend_verification.html', **myrender())
 
        # Find user
        user = db.session.query(models.Users).filter_by(mail=email).first()
        if not user:
            flash('No account found with this email address.', 'error')
            return render_template('resend_verification.html', **myrender())
 
        if user.is_verified:
            flash('This email is already verified. You can log in.', 'info')
            return redirect('/login')
 
        # Generate new token and send email
        token = generate_verification_token(user.id)
        email_sent = send_verification_email(
            user_email=user.mail,
            username=user.name,
            token=token
        )
 
        if email_sent:
            flash('Verification email sent! Please check your inbox.', 'success')
        else:
            flash('Failed to send verification email. Please try again later.', 'error')
 
        return redirect('/login')
 
    return render_template('resend_verification.html', **myrender())