from flask import Flask, render_template, request, redirect, url_for, flash, jsonify, send_file, abort
from flask_login import LoginManager, login_user, logout_user, login_required, current_user
from werkzeug.middleware.proxy_fix import ProxyFix
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
from sqlalchemy import or_, func, and_
from sqlalchemy.orm import aliased
from models import db, User, Artwork, Artist, create_gallery_client_table
from config import Config
from flask_migrate import Migrate
from datetime import datetime
import os
import json
import logging
import io
import csv
from flask import Response
import google.generativeai as genai
from pptx import Presentation
from pptx.util import Inches, Pt

app = Flask(__name__)
app.wsgi_app = ProxyFix(app.wsgi_app, x_prefix=1, x_for=1)
app.config.from_object(Config)

COUNTRY_LIST = ["France", "USA", "UK", "Germany", "Japan", "China", "Brazil", "UAE", "Other"]

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

db.init_app(app)
migrate = Migrate(app, db)
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'

gallery_clients = {}
with app.app_context():
    for i in range(1, 6):
        gallery_clients[i] = create_gallery_client_table(i)

if app.config['GEMINI_API_KEY']:
    genai.configure(api_key=app.config['GEMINI_API_KEY'])

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in {'jpg', 'jpeg'}

def get_instructions(type='SCORING'):
    try:
        with open(os.path.join(app.static_folder, 'gemini_instructions.txt'), 'r', encoding='utf-8') as f:
            content = f.read()
        start_marker = f'[{type.upper()} INSTRUCTIONS]'
        end_marker = '\n['
        start_index = content.find(start_marker)
        if start_index == -1: return ""
        end_index = content.find(end_marker, start_index + len(start_marker))
        return content[start_index:] if end_index == -1 else content[start_index:end_index]
    except FileNotFoundError:
        logger.error("Fichier gemini_instructions.txt introuvable.")
        return ""

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        user = User.query.filter_by(username=request.form['username']).first()
        if user and check_password_hash(user.password, request.form['password']):
            login_user(user)
            if user.role == 'admin':
                return redirect(url_for('admin'))
            elif user.role in ['manager', 'commercial']:
                return redirect(url_for('gallery_hub', gallery_id=user.gallery_id))
        flash('Nom d\'utilisateur ou mot de passe invalide')
    return render_template('login.html')

@app.route('/logout')
@login_required
def logout():
    logout_user()
    return redirect(url_for('index'))

# ==============================================================================
# ADMIN ROUTES
# ==============================================================================

@app.route('/admin')
@login_required
def admin():
    if current_user.role != 'admin': abort(403)
    return render_template('admin_hub.html')

@app.route('/dossiers_artistes')
@login_required
def dossiers_artistes():
    if current_user.role != 'admin': abort(403)
    artists_from_db = Artist.query.order_by(Artist.name).all()
    return render_template('dossiers_artistes.html', artists=artists_from_db)

@app.route('/instructions', methods=['GET', 'POST'])
@login_required
def instructions():
    if current_user.role != 'admin': abort(403)
    instructions_path = os.path.join(app.static_folder, 'gemini_instructions.txt')
    if request.method == 'POST':
        scoring_content = request.form.get('scoring_instructions', '')
        email_content = request.form.get('email_instructions', '')
        full_content = (
            f"[SCORING INSTRUCTIONS]\n{scoring_content.strip()}\n\n"
            f"[EMAIL INSTRUCTIONS]\n{email_content.strip()}"
        )
        with open(instructions_path, 'w', encoding='utf-8') as f:
            f.write(full_content)
        flash('Instructions Gemini sauvegardées.')
        return redirect(url_for('instructions'))
    try:
        with open(instructions_path, 'r', encoding='utf-8') as f:
            full_content = f.read()
        scoring_marker = '[SCORING INSTRUCTIONS]'
        email_marker = '[EMAIL INSTRUCTIONS]'
        scoring_part_start = full_content.find(scoring_marker)
        email_part_start = full_content.find(email_marker)
        if scoring_part_start != -1 and email_part_start != -1:
            scoring_instructions = full_content[scoring_part_start + len(scoring_marker):email_part_start].strip()
            email_instructions = full_content[email_part_start + len(email_marker):].strip()
        else:
            scoring_instructions = "Marqueur [SCORING INSTRUCTIONS] introuvable."
            email_instructions = "Marqueur [EMAIL INSTRUCTIONS] introuvable."
    except FileNotFoundError:
        scoring_instructions = ""
        email_instructions = ""
    return render_template('instructions.html', scoring_instructions=scoring_instructions, email_instructions=email_instructions)

@app.route('/admin/add_artwork', methods=['GET', 'POST'])
@login_required
def add_artwork():
    if current_user.role != 'admin': abort(403)
    if request.method == 'POST':
        file = request.files.get('image')
        if not file or file.filename == '':
            flash('Aucun fichier image sélectionné.')
            return redirect(request.url)
        if allowed_file(file.filename):
            filename = secure_filename(file.filename)
            filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
            os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
            file.save(filepath)
            artist_name = request.form['artist_name']
            existing_artist = Artist.query.filter(func.lower(Artist.name) == func.lower(artist_name.strip())).first()
            if not existing_artist:
                new_artist = Artist(name=artist_name.strip())
                db.session.add(new_artist)
                flash(f'Nouvel artiste "{artist_name}" ajouté.')
            cegid = request.form['cegid']
            existing_artwork = Artwork.query.filter_by(cegid=cegid).first()
            if existing_artwork:
                flash(f'Le CEGID {cegid} est déjà utilisé.')
                return redirect(request.url)
            artwork = Artwork(
                cegid=request.form['cegid'], title=request.form['title'],
                image_path=os.path.join('static/uploads', filename), artist_name=artist_name,
                type=request.form.get('type'), price_range=request.form.get('price_range'),
                width_cm=request.form.get('width_cm', type=int) or None,
                height_cm=request.form.get('height_cm', type=int) or None,
                depth_cm=request.form.get('depth_cm', type=int) or None,
                commentary=request.form['commentary']
            )
            artwork.status = 'ok' if artwork.commentary and artwork.commentary.strip() else 'pending'
            artwork.is_available = 'is_available' in request.form
            db.session.add(artwork)
            db.session.commit()
            flash('Oeuvre ajoutée avec succès !')
            return redirect(url_for('list_artworks'))
    artists_from_db = Artist.query.order_by(Artist.name).all()
    artist_names = [artist.name for artist in artists_from_db]
    return render_template('add_artwork_form.html', artists=artist_names)

@app.route('/admin/artworks')
@login_required
def list_artworks():
    if current_user.role != 'admin': abort(403)
    query = request.args.get('q', '')
    filter_mode = request.args.get('filter', '')
    base_query = Artwork.query
    if filter_mode == 'pending':
        base_query = base_query.filter_by(status='pending')
    if query:
        search_term = f"%{query}%"
        base_query = base_query.filter(or_(Artwork.title.ilike(search_term), Artwork.artist_name.ilike(search_term), Artwork.cegid.ilike(search_term)))
    artworks = base_query.order_by(Artwork.created_at.desc()).all()
    return render_template('list_artworks_editable.html', artworks=artworks, user_type='admin', query=query, filter_mode=filter_mode)

@app.route('/admin/edit_artwork/<int:artwork_id>', methods=['GET', 'POST'])
@login_required
def edit_artwork(artwork_id):
    if current_user.role != 'admin': abort(403)
    artwork = Artwork.query.get_or_404(artwork_id)
    if request.method == 'POST':
        artist_name = request.form['artist_name']
        existing_artist = Artist.query.filter(func.lower(Artist.name) == func.lower(artist_name.strip())).first()
        if not existing_artist:
            new_artist = Artist(name=artist_name.strip())
            db.session.add(new_artist)
            flash(f'Nouvel artiste "{artist_name}" ajouté.')
        artwork.cegid = request.form['cegid']
        artwork.title = request.form['title']
        artwork.artist_name = artist_name
        artwork.type = request.form['type']
        artwork.price_range = request.form.get('price_range')
        artwork.width_cm = request.form.get('width_cm', type=int) or None
        artwork.height_cm = request.form.get('height_cm', type=int) or None
        artwork.depth_cm = request.form.get('depth_cm', type=int) or None
        artwork.commentary = request.form['commentary']
        artwork.status = 'ok' if artwork.commentary and artwork.commentary.strip() else 'pending'
        artwork.is_available = 'is_available' in request.form
        db.session.commit()
        flash('Oeuvre mise à jour avec succès !')
        return redirect(url_for('list_artworks'))
    artists_from_db = Artist.query.order_by(Artist.name).all()
    artist_names = [artist.name for artist in artists_from_db]
    return render_template('edit_artwork_form.html', artwork=artwork, artists=artist_names, user_type='admin')

@app.route('/admin/artwork/<int:artwork_id>/delete', methods=['POST'])
@login_required
def admin_delete_artwork(artwork_id):
    if current_user.role != 'admin': abort(403)
    artwork = Artwork.query.get_or_404(artwork_id)
    if artwork.image_path:
        try:
            full_image_path = os.path.join(app.root_path, artwork.image_path)
            if os.path.exists(full_image_path):
                os.remove(full_image_path)
        except Exception as e:
            logger.error(f"Erreur lors de la suppression du fichier image {artwork.image_path}: {e}")
    db.session.delete(artwork)
    db.session.commit()
    flash('Oeuvre supprimée avec succès !', 'success')
    return redirect(url_for('list_artworks'))

@app.route('/admin/edit_artist/<int:artist_id>', methods=['GET', 'POST'])
@login_required
def edit_artist(artist_id):
    if current_user.role != 'admin': abort(403)
    artist = Artist.query.get_or_404(artist_id)
    if request.method == 'POST':
        artist.name = request.form.get('name', artist.name)
        artist.description = request.form.get('description_fr', artist.description)
        artist.description_en = request.form.get('description_en', artist.description_en)
        image = request.files.get('image')
        if image and allowed_file(image.filename):
            if artist.image_path and os.path.exists(os.path.join(app.root_path, artist.image_path)):
                os.remove(os.path.join(app.root_path, artist.image_path))
            filename = secure_filename(image.filename)
            artist_upload_folder = os.path.join(app.config['UPLOAD_FOLDER'], 'artists')
            os.makedirs(artist_upload_folder, exist_ok=True)
            filepath = os.path.join(artist_upload_folder, filename)
            image.save(filepath)
            artist.image_path = os.path.join('static/uploads/artists', filename)
        db.session.commit()
        flash(f"L'artiste '{artist.name}' a été mis à jour.", 'success')
        return redirect(url_for('dossiers_artistes'))
    return render_template('edit_artist_form.html', artist=artist)

# ==============================================================================
# USER MANAGEMENT ROUTES
# ==============================================================================

@app.route('/admin/management')
@login_required
def management():
    if current_user.role != 'admin': abort(403)
    
    managers = User.query.filter_by(role='manager').order_by(User.gallery_id).all()
    commercials = User.query.filter_by(role='commercial').order_by(User.gallery_id, User.username).all()
    
    # Get a list of gallery IDs that already have a manager
    managed_galleries = {manager.gallery_id for manager in managers}
    available_galleries = [g for g in range(1, 6) if g not in managed_galleries]
    
    return render_template('management.html', managers=managers, commercials=commercials, available_galleries=available_galleries)

@app.route('/admin/user/create', methods=['POST'])
@login_required
def create_commercial():
    if current_user.role != 'admin': abort(403)
    
    username = request.form.get('username')
    password = request.form.get('password')
    gallery_id = request.form.get('gallery_id', type=int)
    
    if not all([username, password, gallery_id]):
        flash("Tous les champs sont requis.", 'danger')
        return redirect(url_for('management'))

    existing_user = User.query.filter_by(username=username).first()
    if existing_user:
        flash("Ce nom d'utilisateur est déjà pris.", 'danger')
        return redirect(url_for('management'))

    new_user = User(
        username=username,
        password=generate_password_hash(password),
        gallery_id=gallery_id,
        role='commercial'
    )
    db.session.add(new_user)
    db.session.commit()
    flash(f"Le commercial '{username}' a été créé avec succès.", 'success')
    return redirect(url_for('management'))

@app.route('/admin/user/update/<int:user_id>', methods=['POST'])
@login_required
def update_commercial(user_id):
    if current_user.role != 'admin': abort(403)

    user = User.query.get_or_404(user_id)
    if user.role != 'commercial':
        flash("Action non autorisée.", 'danger')
        return redirect(url_for('management'))

    new_username = request.form.get('username')
    new_gallery_id = request.form.get('gallery_id', type=int)
    
    # Check if username is being changed and if it's already taken
    if new_username != user.username and User.query.filter_by(username=new_username).first():
        flash("Ce nom d'utilisateur est déjà pris.", 'danger')
        return redirect(url_for('management'))
        
    user.username = new_username
    
    # Handle gallery change
    if new_gallery_id and new_gallery_id != user.gallery_id:
        OldClientTable = gallery_clients[user.gallery_id]
        NewClientTable = gallery_clients[new_gallery_id]
        
        clients_to_move = OldClientTable.query.filter_by(commercial_id=user.id).all()
        
        for old_client in clients_to_move:
            # Create a new client with the same data in the new table
            new_client_data = {c.name: getattr(old_client, c.name) for c in old_client.__table__.columns if c.name not in ['id']}
            new_client = NewClientTable(**new_client_data)
            db.session.add(new_client)
            # Delete the old client
            db.session.delete(old_client)
            
        user.gallery_id = new_gallery_id

    db.session.commit()
    flash(f"Le commercial '{user.username}' a été mis à jour.", 'success')
    return redirect(url_for('management'))

@app.route('/admin/user/delete/<int:user_id>', methods=['POST'])
@login_required
def delete_commercial(user_id):
    if current_user.role != 'admin': abort(403)

    user = User.query.get_or_404(user_id)
    if user.role != 'commercial':
        flash("Action non autorisée.", 'danger')
        return redirect(url_for('management'))
        
    # Find the manager of the gallery
    manager = User.query.filter_by(gallery_id=user.gallery_id, role='manager').first()
    if not manager:
        flash(f"Aucun manager trouvé pour la galerie {user.gallery_id}. Impossible de réattribuer les clients.", 'danger')
        return redirect(url_for('management'))

    ClientTable = gallery_clients[user.gallery_id]
    clients_to_reassign = ClientTable.query.filter_by(commercial_id=user.id).all()

    for client in clients_to_reassign:
        client.commercial_id = manager.id
    
    db.session.delete(user)
    db.session.commit()
    
    flash(f"Le commercial '{user.username}' a été supprimé. Ses clients ont été réattribués au manager.", 'success')
    return redirect(url_for('management'))

@app.route('/admin/manager/update/<int:user_id>', methods=['POST'])
@login_required
def update_manager(user_id):
    if current_user.role != 'admin': abort(403)
    manager = User.query.get_or_404(user_id)
    if manager.role != 'manager': abort(403)
    
    new_username = request.form.get('username')
    if new_username != manager.username and User.query.filter_by(username=new_username).first():
        flash("Ce nom d'utilisateur est déjà pris.", 'danger')
        return redirect(url_for('management'))
        
    manager.username = new_username
    db.session.commit()
    flash(f"Le manager '{manager.username}' a été mis à jour.", 'success')
    return redirect(url_for('management'))

@app.route('/admin/manager/replace/<int:gallery_id>', methods=['POST'])
@login_required
def replace_manager(gallery_id):
    if current_user.role != 'admin': abort(403)
    
    commercial_to_promote_id = request.form.get('commercial_id', type=int)
    if not commercial_to_promote_id:
        flash("Aucun commercial sélectionné pour la promotion.", 'danger')
        return redirect(url_for('management'))
        
    current_manager = User.query.filter_by(gallery_id=gallery_id, role='manager').first()
    commercial_to_promote = User.query.get(commercial_to_promote_id)
    
    if not commercial_to_promote or commercial_to_promote.gallery_id != gallery_id:
        flash("Commercial invalide ou n'appartenant pas à cette galerie.", 'danger')
        return redirect(url_for('management'))

    # Atomically swap roles
    if current_manager:
        current_manager.role = 'commercial'
        
    commercial_to_promote.role = 'manager'
    
    db.session.commit()
    flash(f"'{commercial_to_promote.username}' est le nouveau manager de la galerie {gallery_id}.", 'success')
    return redirect(url_for('management'))

# ==============================================================================
# GALLERY ROUTES
# ==============================================================================

@app.route('/gallery<int:gallery_id>')
@login_required
def gallery_hub(gallery_id):
    if current_user.gallery_id != gallery_id: abort(403)
    return render_template('gallery_hub.html', gallery_id=gallery_id)

@app.route('/gallery<int:gallery_id>/artwork/<int:artwork_id>/delete', methods=['POST'])
@login_required
def gallery_delete_artwork(gallery_id, artwork_id):
    if current_user.gallery_id != gallery_id: abort(403)
    artwork = Artwork.query.get_or_404(artwork_id)
    if artwork.image_path:
        try:
            full_image_path = os.path.join(app.root_path, artwork.image_path)
            if os.path.exists(full_image_path):
                os.remove(full_image_path)
        except Exception as e:
            logger.error(f"Erreur lors de la suppression du fichier image {artwork.image_path}: {e}")
    db.session.delete(artwork)
    db.session.commit()
    flash('Oeuvre supprimée avec succès !', 'success')
    return redirect(url_for('gallery_catalogue', gallery_id=gallery_id))

@app.route('/gallery<int:gallery_id>/ajouter_client', methods=['GET', 'POST'])
@login_required
def ajouter_client(gallery_id):
    if current_user.gallery_id != gallery_id: abort(403)
    if request.method == 'POST':
        GalleryClient = gallery_clients[gallery_id]
        email = request.form.get('email')
        if email and email.strip():
            if GalleryClient.query.filter(func.lower(GalleryClient.email) == func.lower(email.strip())).first():
                flash(f"L'adresse e-mail '{email}' est déjà utilisée.", 'danger')
                return redirect(request.url)
        new_client = GalleryClient(
            first_name=request.form['first_name'], last_name=request.form['last_name'],
            email=email.strip() if email else None,
            phone_number=request.form.get('phone_number'),
            nationality=request.form.get('nationality'), language=request.form.get('language'),
            favorite_artists=request.form.getlist('favorite_artists'),
            commentary=request.form.get('commentary', ''),
            newsletter='newsletter' in request.form,
            potential_haut='potential_haut' in request.form,
            relance_status=request.form.get('relance_status'),
            relance_date=datetime.strptime(request.form['relance_date'], '%Y-%m-%d').date() if request.form.get('relance_date') else None,
            negotiation_status=request.form.get('negotiation_status')
        )
        db.session.add(new_client)
        db.session.commit()
        flash(f'Client {new_client.first_name} {new_client.last_name} ajouté.', 'success')
        return redirect(url_for('edit_client_list', gallery_id=gallery_id))
    artists_from_db = Artist.query.order_by(Artist.name).all()
    artist_names = [artist.name for artist in artists_from_db]
    return render_template('add_client_form.html', gallery_id=gallery_id, artists=artist_names, countries=COUNTRY_LIST)

@app.route('/gallery<int:gallery_id>/clients')
@login_required
def edit_client_list(gallery_id):
    if current_user.gallery_id != gallery_id: abort(403)
    
    GalleryClient = gallery_clients[gallery_id]
    CommercialUser = aliased(User)
    
    base_query = db.session.query(
        GalleryClient, 
        CommercialUser.username
    ).outerjoin(
        CommercialUser, GalleryClient.commercial_id == CommercialUser.id
    )

    if current_user.role == 'commercial':
        base_query = base_query.filter(GalleryClient.commercial_id == current_user.id)
    
    results = base_query.order_by(GalleryClient.updated_at.desc()).all()
    
    clients_with_owner = []
    for client, owner_name in results:
        client.owner_name = owner_name
        clients_with_owner.append(client)
        
    team_commercials = []
    if current_user.role == 'manager':
        team_commercials = User.query.filter(
            User.gallery_id == current_user.gallery_id,
            or_(User.role == 'commercial', User.role == 'manager')
        ).all()

    return render_template('edit_client_list.html', 
                           clients=clients_with_owner, 
                           gallery_id=gallery_id,
                           team_commercials=team_commercials)

@app.route('/gallery<int:gallery_id>/reassign_client/<int:client_id>', methods=['POST'])
@login_required
def reassign_client(gallery_id, client_id):
    if not (current_user.role == 'manager' and current_user.gallery_id == gallery_id):
        abort(403)
        
    GalleryClient = gallery_clients[gallery_id]
    client = GalleryClient.query.get_or_404(client_id)
    new_owner_id = request.form.get('new_owner_id', type=int)
    new_owner = User.query.get_or_404(new_owner_id)

    if new_owner.gallery_id != gallery_id: abort(403)

    client.commercial_id = new_owner_id
    db.session.commit()
    
    flash(f"Client réassigné à {new_owner.username}.", "success")
    return redirect(url_for('edit_client_list', gallery_id=gallery_id))

@app.route('/gallery<int:gallery_id>/edit_client/<int:client_id>', methods=['GET', 'POST'])
@login_required
def edit_client(gallery_id, client_id):
    if current_user.gallery_id != gallery_id: abort(403)
    
    GalleryClient = gallery_clients[gallery_id]
    client = GalleryClient.query.get_or_404(client_id)
    
    if current_user.role == 'commercial' and client.commercial_id != current_user.id:
        abort(403)
        
    if request.method == 'POST':
        # On récupère toutes les données du formulaire
        client.first_name = request.form.get('first_name')
        client.last_name = request.form.get('last_name')
        client.email = request.form.get('email')
        client.phone_number = request.form.get('phone_number')
        client.nationality = request.form.get('nationality')
        client.commercial_name = request.form.get('commercial_name')
        client.negotiation_status = request.form.get('negotiation_status')
        client.language = request.form.get('language')
        client.relance_status = request.form.get('relance_status')
        
        # Traitement spécial pour la date
        relance_date_str = request.form.get('relance_date')
        if relance_date_str:
            client.relance_date = datetime.strptime(relance_date_str, '%Y-%m-%d').date()
        else:
            client.relance_date = None
        
        # Traitement spécial pour les cases à cocher
        client.potential_haut = 'potential_haut' in request.form
        client.newsletter = 'newsletter' in request.form
        
        # Traitement spécial pour la liste des artistes
        client.favorite_artists = request.form.getlist('favorite_artists')
        
        # Le champ qui posait problème
        client.commentary = request.form.get('commentary')

        db.session.commit()
        flash('Client mis à jour avec succès.', 'success')
        return redirect(url_for('edit_client_list', gallery_id=gallery_id))
    
    # On passe les listes nécessaires au template pour l'affichage
    return render_template('edit_client_form.html', 
                           client=client, 
                           gallery_id=gallery_id, 
                           countries=COUNTRY_LIST)

@app.route('/gallery<int:gallery_id>/catalogue')
@login_required
def gallery_catalogue(gallery_id):
    if current_user.gallery_id != gallery_id: abort(403)
    query = request.args.get('q', '')
    filter_mode = request.args.get('filter', '')
    base_query = Artwork.query
    if filter_mode == 'pending':
        base_query = base_query.filter_by(status='pending')
    if query:
        search_term = f"%{query}%"
        base_query = base_query.filter(or_(Artwork.title.ilike(search_term), Artwork.artist_name.ilike(search_term), Artwork.cegid.ilike(search_term)))
    artworks = base_query.order_by(Artwork.created_at.desc()).all()
    return render_template('list_artworks_editable.html', artworks=artworks, user_type='gallery', gallery_id=gallery_id, query=query, filter_mode=filter_mode)

@app.route('/gallery<int:gallery_id>/edit_artwork/<int:artwork_id>', methods=['GET', 'POST'])
@login_required
def gallery_edit_artwork(gallery_id, artwork_id):
    if current_user.gallery_id != gallery_id: abort(403)
    artwork = Artwork.query.get_or_404(artwork_id)
    if request.method == 'POST':
        artist_name = request.form['artist_name']
        existing_artist = Artist.query.filter(func.lower(Artist.name) == func.lower(artist_name.strip())).first()
        if not existing_artist:
            new_artist = Artist(name=artist_name.strip())
            db.session.add(new_artist)
            flash(f'Nouvel artiste "{artist_name}" ajouté.')
        artwork.cegid = request.form['cegid']
        artwork.title = request.form['title']
        artwork.artist_name = artist_name
        artwork.type = request.form['type']
        artwork.price_range = request.form.get('price_range')
        artwork.width_cm = request.form.get('width_cm', type=int) or None
        artwork.height_cm = request.form.get('height_cm', type=int) or None
        artwork.depth_cm = request.form.get('depth_cm', type=int) or None
        artwork.commentary = request.form['commentary']
        artwork.status = 'ok' if artwork.commentary and artwork.commentary.strip() else 'pending'
        artwork.is_available = 'is_available' in request.form
        db.session.commit()
        flash('Oeuvre mise à jour avec succès !')
        return redirect(url_for('gallery_catalogue', gallery_id=gallery_id))
    artists_from_db = Artist.query.order_by(Artist.name).all()
    artist_names = [artist.name for artist in artists_from_db]
    return render_template('edit_artwork_form.html', artwork=artwork, artists=artist_names, gallery_id=gallery_id, user_type='gallery')

@app.route('/api/artist/create', methods=['POST'])
@login_required
def create_artist():
    if current_user.role != 'admin': abort(403)
    name = request.form.get('name')
    if not name or not name.strip():
        flash("Le nom de l'artiste ne peut pas être vide.", 'danger')
        return redirect(url_for('dossiers_artistes'))
    existing_artist = Artist.query.filter(func.lower(Artist.name) == func.lower(name.strip())).first()
    if existing_artist:
        flash(f"L'artiste '{name}' existe déjà.", 'warning')
        return redirect(url_for('dossiers_artistes'))
    try:
        image_path = None
        image = request.files.get('image')
        if image and allowed_file(image.filename):
            filename = secure_filename(image.filename)
            artist_upload_folder = os.path.join(app.config['UPLOAD_FOLDER'], 'artists')
            os.makedirs(artist_upload_folder, exist_ok=True)
            filepath = os.path.join(artist_upload_folder, filename)
            image.save(filepath)
            image_path = os.path.join('static/uploads/artists', filename)
        new_artist = Artist(name=name.strip(), description=request.form.get('description_fr', ''), description_en=request.form.get('description_en', ''), image_path=image_path)
        db.session.add(new_artist)
        db.session.commit()
        flash(f"L'artiste '{name}' a été créé avec succès.", 'success')
    except Exception as e:
        db.session.rollback()
        logger.error(f"Erreur lors de la création de l'artiste: {e}")
        flash('Une erreur est survenue.', 'danger')
    return redirect(url_for('dossiers_artistes'))

@app.route('/gallery<int:gallery_id>/start_match')
@login_required
def start_match(gallery_id):
    if current_user.gallery_id != gallery_id: abort(403)
    
    # On récupère les utilisateurs (commerciaux/managers) de la galerie depuis la table User
    team_members = User.query.filter( # <-- MODIFIÉ
        User.gallery_id == gallery_id, # <-- MODIFIÉ
        or_(User.role == 'commercial', User.role == 'manager') # <-- MODIFIÉ
    ).order_by(User.username).all() # <-- MODIFIÉ
    
    artists_from_db = Artist.query.order_by(Artist.name).all()
    artist_names = [artist.name for artist in artists_from_db]
    
    return render_template('start_match_form.html', 
                           gallery_id=gallery_id, 
                           artists=artist_names, 
                           commercials=team_members)

@app.route('/api/gallery<int:gallery_id>/check_email')
@login_required
def check_email(gallery_id):
    if current_user.gallery_id != gallery_id:
        return jsonify({'error': 'Unauthorized'}), 403
    email = request.args.get('email', '')
    if not email:
        return jsonify({'exists': False})
    GalleryClient = gallery_clients[gallery_id]
    existing_client = GalleryClient.query.filter(func.lower(GalleryClient.email) == func.lower(email)).first()
    return jsonify({'exists': bool(existing_client)})

@app.route('/api/gallery<int:gallery_id>/search_clients')
@login_required
def search_clients(gallery_id):
    query = request.args.get('q', '')
    print(f"\n--- Début de la recherche de client ---")
    print(f"Terme de recherche reçu : '{query}'")

    if len(query) < 2:
        print("Recherche annulée : terme trop court.")
        return jsonify([])
    
    GalleryClient = gallery_clients[gallery_id]
    search_term = f"%{query}%"
    print(f"Terme de recherche SQL (ilike) : '{search_term}'")
    
    try:
        clients = GalleryClient.query.filter(
            or_(
                GalleryClient.first_name.ilike(search_term),
                GalleryClient.last_name.ilike(search_term)
            )
        ).limit(10).all()
        
        print(f"Nombre de clients trouvés dans la base de données : {len(clients)}")

        results = [{'id': c.id, 'name': f'{c.first_name} {c.last_name}'} for c in clients]
        if clients:
            print(f"Résultats qui seront envoyés : {results}")
        
        print("--- Fin de la recherche de client ---\n")
        return jsonify(results)

    except Exception as e:
        print(f"ERREUR PENDANT LA REQUÊTE : {e}")
        return jsonify({"error": "Une erreur s'est produite"}), 500

@app.route('/search_artists')
@login_required
def search_artists():
    query = request.args.get('q', '').lower()
    if len(query) < 2:
        return jsonify([])
    search_term = f"%{query}%"
    artists = Artist.query.filter(Artist.name.ilike(search_term)).limit(10).all()
    results = [artist.name for artist in artists]
    return jsonify(results)
    
@app.route('/gallery<int:gallery_id>/update_commentary', methods=['POST'])
@login_required
def update_commentary(gallery_id):
    if current_user.gallery_id != gallery_id: return jsonify({'error': 'Unauthorized'}), 403
    data = request.json
    client_ids = data.get('client_ids', [])
    match_reason = data.get('match_reason', '')
    GalleryClient = gallery_clients[gallery_id]
    clients = GalleryClient.query.filter(GalleryClient.id.in_(client_ids)).all()
    for client in clients:
        if match_reason:
            timestamp = datetime.now().strftime('%Y-%m-%d')
            client.commentary = (client.commentary or "") + f"\n\n[Match Context - {timestamp}]: {match_reason}"
    db.session.commit()
    return jsonify({'message': f'Commentary updated for {len(clients)} clients.'})

@app.route('/gallery<int:gallery_id>/save_email_commentary', methods=['POST'])
@login_required
def save_email_commentary(gallery_id):
    if current_user.gallery_id != gallery_id:
        return jsonify({'error': 'Unauthorized'}), 403
    
    data = request.json
    client_id = data.get('client_id')
    email_text = data.get('email_text')

    if not client_id or not email_text:
        return jsonify({'error': 'Missing data'}), 400

    GalleryClient = gallery_clients[gallery_id]
    client = GalleryClient.query.get(client_id)

    if not client:
        return jsonify({'error': 'Client not found'}), 404

    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M')
    
    separator = "*" * 30
    update_text = f"\n\n{separator}\n[Email envoyé - {timestamp}]:\n{email_text}\n{separator}"
    
    client.commentary = (client.commentary or "") + update_text
    db.session.commit()
    
    return jsonify({'message': 'Commentary saved successfully.'})

@app.route('/api/gallery<int:gallery_id>/filter_clients', methods=['POST'])
@login_required
def filter_clients(gallery_id):
    if current_user.gallery_id != gallery_id:
        return jsonify({'error': 'Unauthorized'}), 403

    data = request.json
    criteria = data.get('criteria', 'all')
    GalleryClient = gallery_clients[gallery_id]
    
    # 1. Établir la requête de base avec les restrictions de rôle obligatoires
    base_query = GalleryClient.query
    if current_user.role == 'commercial':
        # Un commercial ne PEUT VOIR QUE ses propres clients. C'est un filtre non négociable.
        base_query = base_query.filter(GalleryClient.commercial_id == current_user.id)

    # 2. Appliquer les critères de la demande par-dessus la requête de base sécurisée
    if criteria == 'specific':
        client_ids = data.get('client_ids', [])
        # La requête de base garantit déjà que le commercial ne peut pas sélectionner des clients hors de son périmètre.
        clients = base_query.filter(GalleryClient.id.in_(client_ids)).all() if client_ids else []
    else:
        # On continue à construire la requête à partir de la base
        query = base_query
        
        # Si l'utilisateur est un manager, on lui permet de filtrer par les commerciaux qu'il a sélectionnés.
        # Cette section est ignorée pour un commercial, car sa vue est déjà restreinte.
        if current_user.role == 'manager':
            commercial_ids = data.get('commercial_ids', [])
            if commercial_ids:
                query = query.filter(GalleryClient.commercial_id.in_(commercial_ids))

        # Appliquer les filtres de matching finaux (5 derniers, à relancer, etc.)
        if criteria == 'latest_5':
            clients = query.order_by(GalleryClient.created_at.desc()).limit(5).all()
        elif criteria == 'relance_only':
            clients = query.filter_by(relance_status='À relancer').order_by(GalleryClient.updated_at.desc()).all()
        elif criteria == 'haut_potentiel_only':
            clients = query.filter_by(potential_haut=True).order_by(GalleryClient.updated_at.desc()).all()
        else: # 'all'
            clients = query.order_by(GalleryClient.updated_at.desc()).all()
    
    client_list = [{'id': c.id, 'first_name': c.first_name, 'last_name': c.last_name} for c in clients]
    return jsonify(client_list)

    
@app.route('/gallery<int:gallery_id>/match', methods=['POST'])
@login_required
def match_artworks(gallery_id):
    if current_user.gallery_id != gallery_id:
        return jsonify({'error': 'Unauthorized'}), 403

    # --- Étape 1 : Récupération des données (inchangée) ---
    data = request.json
    client_ids = data.get('client_ids', [])
    selected_artists = data.get('preferred_artists', [])
    required_keywords = data.get('required_keywords', [])

    GalleryClient = gallery_clients[gallery_id]
    clients = GalleryClient.query.filter(GalleryClient.id.in_(client_ids)).all()
    
    base_artwork_query = Artwork.query.filter_by(is_available=True)
    if selected_artists:
        base_artwork_query = base_artwork_query.filter(Artwork.artist_name.in_(selected_artists))
    if required_keywords:
        keyword_conditions = [or_(Artwork.title.ilike(f'%{k}%'), Artwork.commentary.ilike(f'%{k}%')) for k in required_keywords]
        base_artwork_query = base_artwork_query.filter(and_(*keyword_conditions))
    
    artworks = base_artwork_query.all()
    if not artworks:
        return jsonify({}), 200

    scoring_instructions = get_instructions('SCORING')
    if not scoring_instructions:
        return jsonify({'error': "Fichier d'instructions introuvable."}), 500

    # --- Étape 2 : Préparation des données pour un appel unique ---
    # On prépare les détails de TOUTES les œuvres une seule fois
    artworks_details_for_prompt = ""
    for art in artworks:
        artworks_details_for_prompt += f"""
<oeuvre>
CEGID: {art.cegid}
Artiste: {art.artist_name}
Titre: {art.title}
Type: {art.type}
Dimensions: H:{art.height_cm} L:{art.width_cm} P:{art.depth_cm} cm
Commentaire: {art.commentary}
</oeuvre>
"""
    # On prépare les profils de TOUS les clients en une seule fois
    clients_profiles_for_prompt = ""
    for client in clients:
        clean_commentary = (client.commentary or "").split('******************************')[0].strip()
        clients_profiles_for_prompt += f"""
<profil_client>
ID: {client.id}
Nom: {client.first_name} {client.last_name}
Artistes Favoris: {', '.join(client.favorite_artists or [])}
Commentaires et Préférences: {clean_commentary}
</profil_client>
"""

    # --- Étape 3 : Construction d'un prompt unique et massif ---
    # La TÂCHE est modifiée pour demander une analyse de tous les clients en une seule fois
    prompt = f"""
{scoring_instructions}

**TÂCHE :**
Pour CHAQUE profil client listé ci-dessous, évalue CHAQUE œuvre de la liste.
Renvoie TOUS les résultats dans un unique objet JSON. Les clés de cet objet doivent être les IDs des clients, 
et la valeur pour chaque clé doit être un tableau d'objets contenant "cegid" et "score".

**PROFILS DES CLIENTS À ÉVALUER :**
{clients_profiles_for_prompt}

**LISTE DES ŒUVRES À ÉVALUER :**
{artworks_details_for_prompt}
"""

    structured_results = {}
    try:
        # --- Étape 4 : Un seul appel à l'API Gemini ---
        model = genai.GenerativeModel('gemini-2.5-flash') # Utilisation de Flash pour la rapidité
        response = model.generate_content(prompt)
        
        json_text = response.text.strip().replace('```json', '').replace('```', '')
        all_scores_data = json.loads(json_text) # La réponse contient les scores pour TOUS les clients

        # --- Étape 5 : Traitement de la réponse unique ---
        artwork_map = {art.cegid: art for art in artworks}

        for client_id_str, scores_list in all_scores_data.items():
            client_id = int(client_id_str)
            client_matches = []
            
            for score_item in scores_list:
                cegid = score_item.get('cegid')
                score = float(score_item.get('score', 0.0))
                artwork = artwork_map.get(cegid)

                if artwork and score >= 0.75: # Seuil de pertinence
                    client_matches.append({
                        "artwork_id": artwork.id,
                        "title": artwork.title,
                        "cegid": artwork.cegid,
                        "artist_name": artwork.artist_name,
                        "image_path": artwork.image_path,
                        "score": round(score, 3),
                        "height_cm": artwork.height_cm,
                        "width_cm": artwork.width_cm,
                        "depth_cm": artwork.depth_cm
                    })
            
            if client_matches:
                client = next((c for c in clients if c.id == client_id), None)
                if client:
                    sorted_matches = sorted(client_matches, key=lambda x: x['score'], reverse=True)
                    structured_results[client_id] = {
                        "client_name": f"{client.first_name} {client.last_name}",
                        "matches": sorted_matches
                    }

    except Exception as e:
        logger.error(f"Erreur générale lors du matching par lot avec Gemini: {e}")
        return jsonify({'error': 'Erreur pendant le matching.'}), 500
        
    return jsonify(structured_results)

@app.route('/get_artwork_by_cegid')
@login_required
def get_artwork_by_cegid():
    cegid = request.args.get('cegid', '')
    if not cegid:
        return jsonify({"error": "CEGID manquant"}), 400

    artwork = Artwork.query.filter_by(cegid=cegid).first()

    if not artwork:
        return jsonify({"error": "Oeuvre non trouvée"}), 404

    result = {
        "id": artwork.id,
        "title": artwork.title,
        "artist_name": artwork.artist_name,
        "image_path": artwork.image_path,
        "height_cm": artwork.height_cm,
        "width_cm": artwork.width_cm,
        "depth_cm": artwork.depth_cm,
        "cegid": artwork.cegid
    }
    return jsonify(result)

@app.route('/gallery<int:gallery_id>/export_clients.csv')
@login_required
def export_clients_csv(gallery_id):
    if current_user.gallery_id != gallery_id:
        return redirect(url_for('index'))

    GalleryClient = gallery_clients[gallery_id]
    
    all_clients = GalleryClient.query.all()
    sorted_clients = sorted(all_clients, key=lambda c: (c.commercial_name or "", c.last_name or ""))

    output = io.StringIO()
    writer = csv.writer(output, delimiter=';')

    header = ['Commercial', 'Nom', 'Prénom', 'Adresse mail', 'Téléphone', 'Haut Potentiel', 'Statut Négociation', 'Langue', 'Nationalité']
    writer.writerow(header)

    current_commercial = None
    for client in sorted_clients:
        if client.commercial_name != current_commercial and current_commercial is not None:
            writer.writerow([])
        
        current_commercial = client.commercial_name

        # CORRECTION 2: Formate le numéro de téléphone pour forcer Excel à le lire comme du texte
        phone_number_formatted = f'="{client.phone_number}"' if client.phone_number else ''

        row = [
            client.commercial_name or '',
            client.last_name or '',
            client.first_name or '',
            client.email or '',
            phone_number_formatted,
            'Oui' if client.potential_haut else 'Non',
            client.negotiation_status or '',
            client.language or '',
            client.nationality or ''
        ]
        writer.writerow(row)

    # CORRECTION 1: Encode la sortie en 'utf-8-sig' pour inclure le BOM pour Excel
    csv_data = output.getvalue().encode('utf-8-sig')
    
    return Response(
        csv_data,
        mimetype="text/csv",
        headers={"Content-Disposition": f"attachment;filename=export_clients_gallery_{gallery_id}.csv"}
    )


@app.route('/gallery<int:gallery_id>/generate_materials', methods=['POST'])
@login_required
def generate_materials(gallery_id):
    selected_matches = request.json.get('matches', [])
    GalleryClient = gallery_clients[gallery_id]
    clients_to_artworks = {}
    for match in selected_matches:
        client_id = int(match['client_id'])
        artwork_id = int(match['artwork_id'])
        if client_id not in clients_to_artworks:
            clients_to_artworks[client_id] = []
        clients_to_artworks[client_id].append(artwork_id)
    email_instructions = get_instructions('EMAIL')
    if not email_instructions:
        return jsonify({'error': "Fichier d'instructions introuvable."}), 500

    email_drafts = {}
    try:
        model = genai.GenerativeModel('gemini-2.5-flash')  # Modèle gratuit et disponible (fixe les 404)
        for client_id, artwork_ids in clients_to_artworks.items():
            client = GalleryClient.query.get(client_id)
            artworks = Artwork.query.filter(Artwork.id.in_(artwork_ids)).all()
            if not client or not artworks: continue
            client_profile = f"""PROFIL CLIENT:
            - Nom: {client.first_name} {client.last_name}
            - Langue: {client.language}
            - Statut de relance: {client.relance_status}
            - Commentaires et Préférences: {client.commentary}"""
            artworks_details = "\n".join([
                f"""ŒUVRE {i+1}:
                - Artiste: {art.artist_name}
                - Titre: {art.title}
                - CEGID: {art.cegid}
                - Type: {art.type}
                - Dimensions: H:{art.height_cm} L:{art.width_cm} P:{art.depth_cm} cm
                """ for i, art in enumerate(artworks)
            ])
            prompt = f"{email_instructions}\n\n{client_profile}\n\n{artworks_details}"
            try:
                response = model.generate_content(prompt)
                draft = response.text
                email_drafts[client.id] = { "client_name": f"{client.first_name} {client.last_name}", "email_draft": draft.strip() }
            except Exception as e:
                logger.error(f"Erreur de génération d'e-mail Gemini pour le client {client.id}: {e}")
                email_drafts[client.id] = {
                    "client_name": f"{client.first_name} {client.last_name}",
                    "email_draft": f"Sujet : Suggestion d'œuvres\n\nCher {client.first_name},\n\n(Erreur lors de la génération automatique. Veuillez compléter cet e-mail manuellement.)"
                }
    except Exception as e:
        logger.error(f"Erreur lors de l'initialisation du modèle Gemini: {e}")
        return jsonify({'error': 'Erreur lors de l\'initialisation du modèle Gemini'}), 500
    return jsonify(email_drafts)

@app.route('/gallery/<int:gallery_id>/export_pptx')
@login_required
def export_pptx(gallery_id):
    # --- Étape 1 : Récupération des données (inchangée) ---
    client_id = request.args.get('client_id', type=int)
    artwork_ids_str = request.args.get('artwork_ids', '')
    artwork_ids = [int(id) for id in artwork_ids_str.split(',') if id.isdigit()]

    if not client_id or not artwork_ids:
        return "Données manquantes", 400

    GalleryClient = gallery_clients[gallery_id]
    client = GalleryClient.query.get(client_id)
    artworks = Artwork.query.filter(Artwork.id.in_(artwork_ids)).all()
    
    if not client or not artworks:
        return "Client ou œuvres introuvables", 404

    # --- Étape 2 : Préparation de la présentation ---
    template_path = os.path.join(app.static_folder, 'template.pptx')
    prs = Presentation(template_path)
    initial_slide_count = len(prs.slides)
    if initial_slide_count < 4:
        return "Erreur: Le template doit contenir au moins 4 diapositives.", 500

    # Définir les dimensions de la diapositive pour les calculs de positionnement
    slide_width = prs.slide_width
    slide_height = prs.slide_height

    # --- Étape 3 : Génération des nouvelles diapositives avec la mise en page personnalisée ---
    artworks_by_artist = {}
    for art in artworks:
        if art.artist_name not in artworks_by_artist:
            artworks_by_artist[art.artist_name] = []
        artworks_by_artist[art.artist_name].append(art)

    for artist_name, arts in artworks_by_artist.items():
        artist = Artist.query.filter_by(name=artist_name).first()
        
        # --- Diapositive Artiste ---
        if artist:
            # On utilise une mise en page "Titre et Contenu"
            slide_artiste = prs.slides.add_slide(prs.slide_layouts[1]) 
            
            # Nom de l'artiste en haut
            slide_artiste.shapes.title.text = artist.name
            
            # On supprime le corps de texte par défaut pour le remplacer par nos éléments
            body_shape = slide_artiste.placeholders[1]
            if body_shape.has_text_frame:
                sp = body_shape.element
                sp.getparent().remove(sp)

            # Ajout de l'image de l'artiste en dessous du titre
            if artist.image_path and os.path.exists(os.path.join(app.root_path, artist.image_path)):
                img_height = Inches(2.5)
                img_width = Inches(2.5)
                # Positionnement centré horizontalement, sous le titre
                left = (slide_width - img_width) / 2
                top = Inches(1.5)
                slide_artiste.shapes.add_picture(os.path.join(app.root_path, artist.image_path), left, top, height=img_height)
                # La description commencera sous l'image
                description_top = top + img_height + Inches(0.3)
            else:
                description_top = Inches(1.5)

            # Ajout de la description en dessous de l'image
            description = artist.description if client.language == 'Français' else (artist.description_en or artist.description)
            left = Inches(1)
            width = slide_width - Inches(2)
            height = slide_height - description_top - Inches(0.5)
            textbox = slide_artiste.shapes.add_textbox(left, description_top, width, height)
            tf = textbox.text_frame
            tf.word_wrap = True
            p = tf.add_paragraph()
            p.text = description or "Aucune description disponible."
            p.font.size = Pt(14)
        
        # --- Diapositives Œuvres ---
        for art in arts:
            # On utilise une mise en page "Titre seul"
            slide_oeuvre = prs.slides.add_slide(prs.slide_layouts[5])
            
            # Titre de l'œuvre en haut
            slide_oeuvre.shapes.title.text = f'"{art.title}"'
            
            # Ajout de l'image de l'œuvre, centrée
            artwork_image_path = os.path.join(app.root_path, art.image_path)
            if os.path.exists(artwork_image_path):
                img_height = Inches(4.5)
                left = (slide_width - Inches(6)) / 2 # Suppose une largeur d'image approx.
                top = Inches(1.5)
                pic = slide_oeuvre.shapes.add_picture(artwork_image_path, left, top, height=img_height)
                info_top = pic.top + pic.height + Inches(0.2)
            else:
                info_top = Inches(1.5)

            # Ajout des informations de l'œuvre en dessous (SANS CEGID)
            left = Inches(1)
            width = slide_width - Inches(2)
            height = Inches(1)
            textbox = slide_oeuvre.shapes.add_textbox(left, info_top, width, height)
            tf = textbox.text_frame
            p = tf.add_paragraph()
            p.text = f"Dimensions: H:{art.height_cm} x L:{art.width_cm} cm"
            p.font.size = Pt(14)


    # --- Étape 4 : Réorganisation des diapositives (nouvelle logique) ---
    slides = prs.slides._sldIdLst
    all_slide_ids = list(slides)
    generated_slides = all_slide_ids[initial_slide_count:]
    template_slides_to_move = all_slide_ids[3:initial_slide_count]
    
    del slides[3:]
    
    for slide_id in generated_slides:
        slides.append(slide_id)
        
    for slide_id in template_slides_to_move:
        slides.append(slide_id)

    # --- Étape 5 : Envoi du fichier (inchangée) ---
    file_stream = io.BytesIO()
    prs.save(file_stream)
    file_stream.seek(0)

    return send_file(
        file_stream,
        as_attachment=True,
        download_name=f'proposition_{client.last_name}.pptx',
        mimetype='application/vnd.openxmlformats-officedocument.presentationml.presentation'
    )