from werkzeug.middleware.proxy_fix import ProxyFix
from flask import Flask, render_template, request, redirect, url_for, flash, jsonify, send_file
from flask_login import LoginManager, login_user, logout_user, login_required, current_user
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
from sqlalchemy import or_, func
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.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.is_admin:
                return redirect(url_for('admin'))
            elif user.gallery_id:
                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'))

@app.route('/admin')
@login_required
def admin():
    if not current_user.is_admin: return redirect(url_for('index'))
    return render_template('admin_hub.html')

@app.route('/dossiers_artistes')
@login_required
def dossiers_artistes():
    if not current_user.is_admin:
        return redirect(url_for('index'))
    
    # Récupère la liste des artistes pour l'afficher dans le formulaire de modification
    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 not current_user.is_admin:
        return redirect(url_for('index'))

    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', '')

        # Reconstitue le fichier complet avec les séparateurs
        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()
        
        # Sépare le contenu en deux parties pour les textareas
        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: # Si les marqueurs ne sont pas trouvés, on évite une erreur
            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 not current_user.is_admin:
        return redirect(url_for('index'))
    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é à la base de données.')
            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']
            )
            if artwork.commentary and artwork.commentary.strip():
                artwork.status = 'ok'
            else:
                artwork.status = 'pending'
            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 not current_user.is_admin: return redirect(url_for('index'))
    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 not current_user.is_admin: return redirect(url_for('index'))
    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é à la base de données.')
        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']
        if artwork.commentary and artwork.commentary.strip():
            artwork.status = 'ok'
        else:
            artwork.status = 'pending'
        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 delete_artwork(artwork_id):
    if not current_user.is_admin: return redirect(url_for('index'))
    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('/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:
        return redirect(url_for('index'))

    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_list_artworks', gallery_id=gallery_id))

@app.route('/resources', methods=['GET', 'POST'])
@login_required
def resources():
    if not current_user.is_admin: return redirect(url_for('index'))
    instructions_path = os.path.join(app.static_folder, 'gemini_instructions.txt')
    if request.method == 'POST':
        content = request.form.get('gemini_instructions')
        with open(instructions_path, 'w', encoding='utf-8') as f:
            f.write(content)
        flash('Instructions Gemini sauvegardées.')
        return redirect(url_for('resources'))
    try:
        with open(instructions_path, 'r', encoding='utf-8') as f:
            instructions_content = f.read()
    except FileNotFoundError:
        instructions_content = "Veuillez créer et remplir le fichier gemini_instructions.txt dans le dossier static."
    artists_from_db = Artist.query.order_by(Artist.name).all()
    return render_template('resources.html', artists=artists_from_db, instructions=instructions_content)

@app.route('/api/artist/create', methods=['POST'])
@login_required
def create_artist():
    name = request.form.get('name')
    description_fr = request.form.get('description_fr', '')
    description_en = request.form.get('description_en', '')
    image = request.files.get('image')

    if not name or not name.strip():
        flash("Le nom de l'artiste ne peut pas être vide.", 'danger')
        return redirect(url_for('resources'))

    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('resources'))

    image_path = None
    if image and allowed_file(image.filename):
        filename = secure_filename(image.filename)
        # Créer un sous-dossier pour les images d'artistes
        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=description_fr,
        description_en=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')
    return redirect(url_for('resources'))

@app.route('/api/artist/<int:artist_id>')
@login_required
def get_artist_details(artist_id):
    artist = Artist.query.get_or_404(artist_id)
    return jsonify({
        'description_fr': artist.description or '',
        'description_en': artist.description_en or ''
    })

@app.route('/api/artist/<int:artist_id>', methods=['POST'])
@login_required
def update_artist_details(artist_id):
    artist = Artist.query.get_or_404(artist_id)
    data = request.json
    artist.description = data.get('description_fr', '')
    artist.description_en = data.get('description_en', '')
    db.session.commit()
    return jsonify({'message': f'Description de {artist.name} mise à jour.'})

@app.route('/gallery<int:gallery_id>')
@login_required
def gallery_hub(gallery_id):
    if current_user.gallery_id != gallery_id: return redirect(url_for('index'))
    return render_template('gallery_hub.html', 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: return redirect(url_for('index'))
    if request.method == 'POST':
        GalleryClient = gallery_clients[gallery_id]
        email = request.form.get('email')
        if email and email.strip():
            existing_client = GalleryClient.query.filter(func.lower(GalleryClient.email) == func.lower(email.strip())).first()
            if existing_client:
                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'),
            commercial_name=request.form.get('commercial_name'),
            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: return redirect(url_for('index'))
    query = request.args.get('q', '')
    commercial_filter = request.args.get('commercial', '')
    relance_filter = request.args.get('relance_status', '')
    potential_filter = request.args.get('potential', '')
    GalleryClient = gallery_clients[gallery_id]
    base_query = GalleryClient.query
    if commercial_filter:
        base_query = base_query.filter_by(commercial_name=commercial_filter)
    if relance_filter:
        base_query = base_query.filter_by(relance_status=relance_filter)
    if potential_filter == 'haut':
        base_query = base_query.filter_by(potential_haut=True)
    if query:
        search_term = f"%{query}%"
        base_query = base_query.filter(or_(
            GalleryClient.first_name.ilike(search_term),
            GalleryClient.last_name.ilike(search_term)
        ))
    clients = base_query.order_by(GalleryClient.updated_at.desc()).all()
    commercials_query = db.session.query(GalleryClient.commercial_name.distinct()).filter(GalleryClient.commercial_name.isnot(None)).all()
    commercial_names = [c[0] for c in commercials_query]
    return render_template('edit_client_list.html', clients=clients, gallery_id=gallery_id, query=query, commercial_names=commercial_names, commercial_filter=commercial_filter, relance_filter=relance_filter, potential_filter=potential_filter)

@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: return redirect(url_for('index'))
    GalleryClient = gallery_clients[gallery_id]
    client = GalleryClient.query.get_or_404(client_id)
    if request.method == 'POST':
        client.first_name = request.form['first_name']
        client.last_name = request.form['last_name']
        client.email = request.form['email']
        client.phone_number = request.form['phone_number']
        client.commercial_name = request.form['commercial_name']
        client.nationality = request.form['nationality']
        client.language = request.form['language']
        client.favorite_artists = request.form.getlist('favorite_artists')
        client.commentary = request.form.get('commentary', '')
        client.newsletter = 'newsletter' in request.form
        client.potential_haut = 'potential_haut' in request.form
        client.relance_status = request.form.get('relance_status')
        client.relance_date = datetime.strptime(request.form['relance_date'], '%Y-%m-%d').date() if request.form.get('relance_date') else None
        client.negotiation_status = request.form.get('negotiation_status')
        db.session.commit()
        flash(f'Client {client.first_name} {client.last_name} mis à jour.', '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('edit_client_form.html', client=client, gallery_id=gallery_id, artists=artist_names, countries=COUNTRY_LIST)

@app.route('/gallery<int:gallery_id>/catalogue')
@login_required
def gallery_catalogue(gallery_id):
    if current_user.gallery_id != gallery_id: 
        return redirect(url_for('index'))
    
    # 1. Lire les arguments de l'URL
    query = request.args.get('q', '')
    filter_mode = request.args.get('filter', '') # <-- Cette ligne est cruciale
    
    base_query = Artwork.query

    # 2. Appliquer le filtre si 'filter_mode' est 'pending'
    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()
    
    # 3. Renvoyer 'filter_mode' au template pour que l'icône puisse changer
    return render_template('list_artworks_editable.html', 
                           artworks=artworks, 
                           user_type='gallery', 
                           gallery_id=gallery_id, 
                           query=query, 
                           filter_mode=filter_mode) # <-- 'filter_mode' est maintenant passé au template


@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:
        return redirect(url_for('index'))
        
    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é à la base de données.')
            
        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']
        
        if artwork.commentary and artwork.commentary.strip():
            artwork.status = 'ok'
        else:
            artwork.status = 'pending'
            
        db.session.commit()
        flash('Oeuvre mise à jour avec succès !')
        
        # --- LIGNE CORRIGÉE ---
        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('/gallery<int:gallery_id>/start_match')
@login_required
def start_match(gallery_id):
    if current_user.gallery_id != gallery_id: return redirect(url_for('index'))
    GalleryClient = gallery_clients[gallery_id]
    commercials_query = db.session.query(GalleryClient.commercial_name.distinct()).filter(GalleryClient.commercial_name.isnot(None)).order_by(GalleryClient.commercial_name).all()
    commercial_names = [c[0] for c in commercials_query]
    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=commercial_names)

@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]
    
    query = GalleryClient.query

    if criteria == 'specific':
        client_ids = data.get('client_ids', [])
        if not client_ids:
            return jsonify([])
        clients = query.filter(GalleryClient.id.in_(client_ids)).all()
    else:
        commercials = data.get('commercials', [])
        if commercials:
            query = query.filter(GalleryClient.commercial_name.in_(commercials))

        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
    data = request.json
    client_ids = data.get('client_ids', [])
    selected_artists = data.get('preferred_artists', [])
    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))
    artworks = base_artwork_query.all()
    if not artworks:
        return jsonify({}), 400
    scoring_instructions = get_instructions('SCORING')
    if not scoring_instructions:
        return jsonify({'error': "Fichier d'instructions introuvable."}), 500
    model = genai.GenerativeModel('gemini-1.5-pro-latest')
    structured_results = {}
    artwork_details_map = {art.id: art for art in artworks}
    for client in clients:
        client_profile = f"""PROFIL CLIENT:
        - Nom: {client.first_name} {client.last_name}
        - Potentiel: {'Élevé' if client.potential_haut else 'Standard'}
        - Statut de relance: {client.relance_status}
        - Date de relance: {client.relance_date.strftime('%Y-%m-%d') if client.relance_date else 'N/A'}
        - Artistes Favoris: {', '.join(client.favorite_artists or [])}
        - Commentaires et Préférences: {client.commentary}"""
        client_matches = []
        for art in artworks:
            artwork_details = f"""DÉTAILS DE L'ŒUVRE:
            - 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
            - Commentaire de la galerie: {art.commentary}"""
            prompt = f"{scoring_instructions}\n\n{client_profile}\n\n{artwork_details}"
            try:
                response = model.generate_content(prompt)
                json_text = response.text.strip().replace('```json', '').replace('```', '')
                score_data = json.loads(json_text)
                score = float(score_data.get("score", 0.0))
                if score > 0.3:
                    client_matches.append({'artwork_id': art.id, 'score': score})
            except Exception as e:
                logger.error(f"Erreur d'évaluation Gemini pour client {client.id} / œuvre {art.id}: {e}")
        if client_matches:
            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": []
            }
            for match in sorted_matches:
                artwork = artwork_details_map.get(match['artwork_id'])
                if artwork:
                    structured_results[client.id]["matches"].append({
                        "artwork_id": artwork.id,
                        "cegid": artwork.cegid,
                        "artist_name": artwork.artist_name,
                        "image_path": artwork.image_path,
                        "score": round(match['score'], 3)
                    })
    return jsonify(structured_results)

@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)

    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
    model = genai.GenerativeModel('gemini-1.5-pro-latest')
    email_drafts = {}
    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.)"
            }
    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 ---
    # Récupère l'ID du client et la liste des IDs des œuvres depuis l'URL
    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

    # Interroge la base de données pour obtenir les objets Client et Artwork complets
    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 ---
    # Définit les chemins vers le modèle PowerPoint et le logo
    template_path = os.path.join(app.static_folder, 'template.pptx')
    logo_path = os.path.join(app.static_folder, 'logo.png')

    # Ouvre la présentation en utilisant le template
    prs = Presentation(template_path)

    # --- Slide 1 : Page de Titre ---
    # Ajoute une nouvelle diapositive en utilisant une mise en page prédéfinie (ici, la 6ème, souvent "Titre seul")
    slide_titre = prs.slides.add_slide(prs.slide_layouts[5])
    # Ajoute le logo au centre
    slide_titre.shapes.add_picture(logo_path, Inches(2.5), Inches(1.5), width=Inches(5))
    # Ajoute le titre de la présentation
    title_shape = slide_titre.shapes.title
    title_shape.text = f"Proposition pour {client.first_name} {client.last_name}"

    # --- Étape 3 : Slides des Artistes et des Œuvres ---
    # Regroupe les œuvres par nom d'artiste pour créer les sections
    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)

    # Boucle sur chaque artiste trouvé dans la sélection
    for artist_name, arts in artworks_by_artist.items():
        artist = Artist.query.filter_by(name=artist_name).first()
        
        # --- Slide de Présentation de l'Artiste ---
        if artist:
            slide_artiste = prs.slides.add_slide(prs.slide_layouts[1]) # Mise en page "Titre et Contenu"
            slide_artiste.shapes.title.text = artist.name

            # Ajoute l'image de l'artiste si elle existe
            if artist.image_path and os.path.exists(os.path.join(app.root_path, artist.image_path)):
                slide_artiste.shapes.add_picture(os.path.join(app.root_path, artist.image_path), Inches(0.5), Inches(1.5), height=Inches(2))
            
            # Sélectionne la description dans la langue du client
            description = artist.description if client.language == 'Français' else (artist.description_en or artist.description)
            
            # Ajoute le texte de description
            content_shape = slide_artiste.placeholders[1]
            tf = content_shape.text_frame
            tf.text = description or "Aucune description disponible."
            p = tf.paragraphs[0]
            p.font.size = Pt(14)
        
        # --- Slides pour chaque Œuvre de cet artiste ---
        for art in arts:
            slide_oeuvre = prs.slides.add_slide(prs.slide_layouts[5]) # Mise en page "Titre seul"
            slide_oeuvre.shapes.title.text = f'"{art.title}" - {art.artist_name}'
            
            # Ajoute l'image de l'œuvre
            artwork_image_path = os.path.join(app.root_path, art.image_path)
            if os.path.exists(artwork_image_path):
                slide_oeuvre.shapes.add_picture(artwork_image_path, Inches(1), Inches(1.5), height=Inches(5))

            # Ajoute une zone de texte pour les informations de l'œuvre
            textbox = slide_oeuvre.shapes.add_textbox(Inches(1), Inches(6.5), Inches(8), Inches(1))
            tf = textbox.text_frame
            p = tf.add_paragraph()
            p.text = f"CEGID: {art.cegid} | Dimensions: H:{art.height_cm} x L:{art.width_cm} cm"
            p.font.size = Pt(14)

    # --- Slide 4 : Page de Contact ---
    slide_fin = prs.slides.add_slide(prs.slide_layouts[1])
    slide_fin.shapes.title.text = "Contact"
    content_shape = slide_fin.placeholders[1]
    tf = content_shape.text_frame
    tf.text = f"Galerie {gallery_id}\nAdresse de la galerie\ncontact@galerie.com\n+33 1 23 45 67 89"

    # --- Étape 5 : Envoi du fichier ---
    # Crée un flux de données en mémoire pour éviter de sauvegarder le fichier sur le serveur
    file_stream = io.BytesIO()
    prs.save(file_stream)
    file_stream.seek(0)

    # Renvoie le flux de données comme un fichier à télécharger
    return send_file(
        file_stream,
        as_attachment=True,
        download_name=f'proposition_{client.last_name}.pptx',
        mimetype='application/vnd.openxmlformats-officedocument.presentationml.presentation'
    )