Présentation de la base de connaissance :

"""
Nom du script : Base de Connaissance.py
Auteur : Neil ANDRE
Date : 27/08/2025
Version : 1.0
Script Python : Base de Connaissance avec Extraction, Indexation et Recherche
Fonctionnalités :
- Extraction de texte (.txt, .docx, .pptx, .pdf)
- Indexation dans MySQL (MariaDB)
- Interface web sécurisée (upload + recherche)
- Authentification pour l'upload
- Pagination des résultats
- Optimisation des requêtes MySQL
- Surlignage des mots-clés dans les résultats
- Ouverture/téléchargement des fichiers
- Page intermédiaire après upload pour choisir entre ouvrir ou enregistrer le fichier
- Enregistrement des fichiers dans un répertoire dédié (saved_files/)
- Statistiques par type de fichier
"""

✅ Indexation automatique sans doublons
✅ Mise à jour des fichiers modifiés
✅ Surlignage des mots-clés
✅ Ouverture/téléchargement des fichiers
✅ Page intermédiaire après upload
✅ Authentification sécurisée

# I - Prérequis

# pip install flask mysql-connector-python python-docx python-pptx pdfplumber werkzeug flask-login

# II - Structure de la Base MySQL (MariaDB)

# Commandes exécutées dans le SGBD :
# CREATE DATABASE knowledge_base;
# USE knowledge_base;
# CREATE TABLE documents (
#     id INT AUTO_INCREMENT PRIMARY KEY,
#     title VARCHAR(255),
#     content LONGTEXT,
#     file_type VARCHAR(10),
#     import_date DATETIME DEFAULT CURRENT_TIMESTAMP,
#     FULLTEXT(content),  
#     INDEX(file_type)    
# );
# CREATE TABLE users (
#     id INT AUTO_INCREMENT PRIMARY KEY,
#     username VARCHAR(50) UNIQUE NOT NULL,
#     password VARCHAR(255) NOT NULL
# );
# INSERT INTO users (username, password) VALUES ('admin', '$5K7lDMYIH4DR4jAt$42a6512e29cce828a6e84359d40dcc037cf872bb20d2405e711844337bbd61856a79cb8a65bf3432f6d32d7b9d90997a97b23d1b07cf06408460aab31d1a0aa2');  -- Mot de passe : admin

# III - Script Python

import os
import re
import shutil
import pdfplumber
from docx import Document
from pptx import Presentation
import mysql.connector
from flask import Flask, request, redirect, render_template_string, send_from_directory, flash, url_for
from werkzeug.utils import secure_filename
from werkzeug.security import generate_password_hash, check_password_hash
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user

app = Flask(__name__)
app.secret_key = 'votre_cle_secrete_ici'  # À changer en production !
UPLOAD_FOLDER = 'uploads'
SAVED_FOLDER = 'saved_files'  # Répertoire pour enregistrer les fichiers
DOCUMENTS_DIR = 'documents'
ALLOWED_EXTENSIONS = {'txt', 'docx', 'pptx', 'pdf'}
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

# Configuration Flask-Login
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'

# Modèle utilisateur
class User(UserMixin):
    def __init__(self, id, username):
        self.id = id
        self.username = username

# Connexion à la base de données MySQL
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="Password@2025",
    database="knowledge_base"
)
cursor = db.cursor()

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

def save_file(file):
    filename = secure_filename(file.filename)
    path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
    file.save(path)
    return filename, path

def save_file_permanently(filename):
    """Enregistre le fichier dans le répertoire saved_files/."""
    src = os.path.join(UPLOAD_FOLDER, filename)
    dst = os.path.join(SAVED_FOLDER, filename)
    shutil.copy2(src, dst)
    return dst

def extract_content(path, ext):
    try:
        if ext == "txt":
            with open(path, 'r', encoding='utf-8', errors='ignore') as f:
                return f.read()
        elif ext == "docx":
            doc = Document(path)
            return "\n".join([p.text for p in doc.paragraphs])
        elif ext == "pptx":
            prs = Presentation(path)
            return "\n".join([shape.text for slide in prs.slides for shape in slide.shapes if hasattr(shape, "text")])
        elif ext == "pdf":
            text = ""
            with pdfplumber.open(path) as pdf:
                for page in pdf.pages:
                    text += page.extract_text() + "\n"
            return text
    except Exception as e:
        print(f"[ERREUR] Extraction du fichier {path}: {e}")
    return ""

def insert_into_db(title, content, file_type):
    try:
        cursor.execute("INSERT INTO documents (title, content, file_type) VALUES (%s, %s, %s)", (title, content, file_type))
        db.commit()
    except Exception as e:
        print(f"[ERREUR] Insertion dans la base : {e}")

def process_directory(directory):
    for filename in os.listdir(directory):
        path = os.path.join(directory, filename)
        if os.path.isfile(path) and allowed_file(filename):
            ext = filename.rsplit('.', 1)[1].lower()
            content = extract_content(path, ext)
            if content:
                insert_into_db(filename, content, ext)
                print(f"[DEBUG] Fichier indexé : {filename}")

@login_manager.user_loader
def load_user(user_id):
    cursor.execute("SELECT id, username FROM users WHERE id = %s", (user_id,))
    user_data = cursor.fetchone()
    if user_data:
        return User(id=user_data[0], username=user_data[1])
    return None

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        cursor.execute("SELECT id, username, password FROM users WHERE username = %s", (username,))
        user_data = cursor.fetchone()
        if user_data and check_password_hash(user_data[2], password):
            user = User(id=user_data[0], username=user_data[1])
            login_user(user)
            return redirect(url_for('search'))
        flash('Identifiants incorrects', 'error')
    return render_template_string('''
        <!doctype html>
        <title>Login</title>
        <style>
            body { font-family: Arial, sans-serif; margin: 20px; }
            .alert { color: red; }
            form { margin-bottom: 20px; }
            input, button { margin: 5px 0; }
        </style>
        <h1>Connexion</h1>
        {% with messages = get_flashed_messages(with_categories=true) %}
            {% if messages %}
                {% for category, message in messages %}
                    <div class="alert alert-{{ category }}">{{ message }}</div>
                {% endfor %}
            {% endif %}
        {% endwith %}
        <form method="post">
            <label>Utilisateur:</label><br>
            <input type="text" name="username" required><br>
            <label>Mot de passe:</label><br>
            <input type="password" name="password" required><br>
            <button type="submit">Se connecter</button>
        </form>
    ''')

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

@app.route('/uploads/<filename>')
def uploaded_file(filename):
    upload_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
    saved_path = os.path.join(SAVED_FOLDER, filename)
    documents_path = os.path.join(DOCUMENTS_DIR, filename)

    path = None
    if os.path.exists(upload_path):
        path = upload_path
    elif os.path.exists(saved_path):
        path = saved_path
    elif os.path.exists(documents_path):
        path = documents_path
    else:
        return "Fichier non trouvé", 404

    if filename.endswith('.pdf'):
        mimetype = 'application/pdf'
    elif filename.endswith('.docx'):
        mimetype = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
    elif filename.endswith('.pptx'):
        mimetype = 'application/vnd.openxmlformats-officedocument.presentationml.presentation'
    else:
        mimetype = 'text/plain'

    if os.path.dirname(path) == app.config['UPLOAD_FOLDER']:
        return send_from_directory(app.config['UPLOAD_FOLDER'], filename, mimetype=mimetype, as_attachment=False)
    elif os.path.dirname(path) == SAVED_FOLDER:
        return send_from_directory(SAVED_FOLDER, filename, mimetype=mimetype, as_attachment=False)
    else:
        return send_from_directory(DOCUMENTS_DIR, filename, mimetype=mimetype, as_attachment=False)

def highlight_text(text, keyword):
    if not keyword:
        return text
    pattern = re.compile(re.escape(keyword), re.IGNORECASE)
    return pattern.sub(f'<span class="highlight">{keyword}</span>', text)

@app.route("/upload", methods=["GET", "POST"])
@login_required
def upload_file():
    if request.method == "POST":
        file = request.files["file"]
        if file and allowed_file(file.filename):
            filename, path = save_file(file)
            ext = filename.rsplit('.', 1)[1].lower()
            content = extract_content(path, ext)
            if content:
                insert_into_db(filename, content, ext)
                return redirect(url_for('post_upload', filename=filename))
            else:
                flash('Impossible d\'extraire le contenu du fichier.', 'error')
                return redirect(url_for('search'))
    return render_template_string('''
        <!doctype html>
        <title>Uploader un fichier</title>
        <style>
            body { font-family: Arial, sans-serif; margin: 20px; }
            .alert { padding: 10px; margin: 10px 0; border-radius: 4px; }
            .alert-error { background-color: #f2dede; color: #a94442; }
            a { display: block; margin-top: 10px; }
        </style>
        <h1>Uploader un nouveau fichier</h1>
        {% with messages = get_flashed_messages(with_categories=true) %}
            {% if messages %}
                {% for category, message in messages %}
                    <div class="alert alert-{{ category }}">{{ message }}</div>
                {% endfor %}
            {% endif %}
        {% endwith %}
        <form method=post enctype=multipart/form-data>
            <input type=file name=file required>
            <input type=submit value=Uploader>
        </form>
        <a href="{{ url_for('search') }}">Retour à la recherche</a>
    ''')

@app.route("/post_upload/<filename>")
@login_required
def post_upload(filename):
    return render_template_string('''
        <!doctype html>
        <title>Fichier uploadé</title>
        <style>
            body { font-family: Arial, sans-serif; margin: 20px; text-align: center; }
            .container { max-width: 600px; margin: 0 auto; padding: 20px; border: 1px solid #ddd; border-radius: 5px; background-color: #f9f9f9; }
            h1 { color: #333; }
            .buttons { margin: 20px 0; }
            .btn { display: inline-block; padding: 10px 20px; margin: 0 10px; background-color: #4CAF50; color: white; text-decoration: none; border-radius: 5px; }
            .btn-open { background-color: #2196F3; }
            .btn-save { background-color: #4CAF50; }
            .btn-back { background-color: #f44336; }
            .btn:hover { opacity: 0.8; }
        </style>
        <div class="container">
            <h1>Fichier "{{ filename }}" uploadé avec succès !</h1>
            <p>Que souhaitez-vous faire ?</p>
            <div class="buttons">
                <a href="{{ url_for('uploaded_file', filename=filename) }}" target="_blank" class="btn btn-open">Ouvrir le fichier</a>
                <a href="{{ url_for('save_file_route', filename=filename) }}" class="btn btn-save">Enregistrer le fichier</a>
            </div>
            <a href="{{ url_for('search') }}" class="btn btn-back">Retour à la recherche</a>
        </div>
    ''', filename=filename)

@app.route("/save_file/<filename>")
@login_required
def save_file_route(filename):
    try:
        save_file_permanently(filename)
        flash(f'Fichier "{filename}" enregistré dans le répertoire "saved_files/".', 'success')
    except Exception as e:
        flash(f'Erreur lors de l\'enregistrement du fichier : {e}', 'error')
    return redirect(url_for('search'))

@app.route("/")
def search():
    keyword = request.args.get("search", "")
    file_type = request.args.get("type", "")
    page = int(request.args.get("page", 1))
    per_page = 5

    # Requête pour compter les résultats par type
    type_counts = {}
    query_count = "SELECT file_type, COUNT(*) FROM documents WHERE content LIKE %s"
    params = [f"%{keyword}%"]
    if file_type:
        query_count += " AND file_type = %s"
        params.append(file_type)
    query_count += " GROUP BY file_type"

    cursor.execute(query_count, tuple(params))
    for ft, count in cursor.fetchall():
        type_counts[ft] = count

    # Requête principale
    query = "SELECT * FROM documents WHERE content LIKE %s"
    params = [f"%{keyword}%"]
    if file_type:
        query += " AND file_type = %s"
        params.append(file_type)

    cursor.execute(f"SELECT COUNT(*) FROM documents WHERE content LIKE %s{' AND file_type = %s' if file_type else ''}", tuple(params))
    total = cursor.fetchone()[0]

    offset = (page - 1) * per_page
    query += " LIMIT %s OFFSET %s"
    params.extend([per_page, offset])
    cursor.execute(query, tuple(params))
    documents = cursor.fetchall()

    total_pages = (total + per_page - 1) // per_page

    return render_template_string('''
        <!doctype html>
        <title>Recherche</title>
        <style>
            body { font-family: Arial, sans-serif; margin: 20px; }
            .header { display: flex; justify-content: space-between; align-items: center; margin-bottom: 20px; }
            .stats { background: #f0f0f0; padding: 10px; border-radius: 5px; margin-bottom: 20px; }
            .result { margin-bottom: 20px; border-bottom: 1px solid #ddd; padding-bottom: 10px; }
            .file-link { color: #0066cc; text-decoration: none; margin-right: 10px; }
            .file-link:hover { text-decoration: underline; }
            .pagination { margin-top: 20px; }
            .highlight { background-color: yellow; font-weight: bold; }
            form { margin-bottom: 20px; }
            input, button, select { margin: 0 5px; }
            .type-count { display: inline-block; margin-right: 10px; }
            .alert { padding: 10px; margin: 10px 0; border-radius: 4px; }
            .alert-success { background-color: #dff0d8; color: #3c763d; }
            .alert-error { background-color: #f2dede; color: #a94442; }
        </style>
        <div class="header">
            <h1>Rechercher dans la base de connaissance</h1>
            {% if current_user.is_authenticated %}
                <div>Connecté en tant que {{ current_user.username }} | <a href="{{ url_for('logout') }}">Se déconnecter</a></div>
            {% else %}
                <a href="{{ url_for('login') }}">Se connecter pour uploader</a>
            {% endif %}
        </div>

        {% with messages = get_flashed_messages(with_categories=true) %}
            {% if messages %}
                {% for category, message in messages %}
                    <div class="alert alert-{{ category }}">{{ message }}</div>
                {% endfor %}
            {% endif %}
        {% endwith %}

        <form method=get>
            <input type=text name=search placeholder="Mot-clé" value="{{ keyword }}">
            <select name=type>
                <option value="">Tous les types</option>
                <option value="txt" {% if file_type == 'txt' %}selected{% endif %}>TXT</option>
                <option value="docx" {% if file_type == 'docx' %}selected{% endif %}>DOCX</option>
                <option value="pptx" {% if file_type == 'pptx' %}selected{% endif %}>PPTX</option>
                <option value="pdf" {% if file_type == 'pdf' %}selected{% endif %}>PDF</option>
            </select>
            <button type=submit>Rechercher</button>
        </form>

        {% if keyword %}
            <div class="stats">
                <strong>Résultats pour "{{ keyword }}" :</strong>
                {% for ft, count in type_counts.items() %}
                    <span class="type-count">{{ ft|upper }}: {{ count }}</span>
                {% endfor %}
                | Total: {{ total }}
            </div>
        {% endif %}

        {% if current_user.is_authenticated %}
            <p><a href="{{ url_for('upload_file') }}">Uploader un fichier</a></p>
        {% endif %}

        {% if documents %}
            {% for doc in documents %}
                <div class="result">
                    <h3>
                        <a class="file-link" href="{{ url_for('uploaded_file', filename=doc[1]) }}" target="_blank">{{ doc[1] }}</a>
                        <span style="color: #666;">({{ doc[3] }})</span>
                    </h3>
                    <p>{{ highlight_text(doc[2][:500] + ('...' if doc[2]|length > 500 else ''), keyword)|safe }}</p>
                    <small style="color: #999;">Importé le: {{ doc[4] }}</small>
                </div>
            {% endfor %}

            {% if total_pages > 1 %}
                <div class="pagination">
                    {% if page > 1 %}
                        <a href="?search={{ keyword }}&type={{ file_type }}&page={{ page - 1 }}">Précédent</a>
                    {% endif %}
                    Page {{ page }} sur {{ total_pages }}
                    {% if page < total_pages %}
                        <a href="?search={{ keyword }}&type={{ file_type }}&page={{ page + 1 }}">Suivant</a>
                    {% endif %}
                </div>
            {% endif %}
        {% else %}
            <p>Aucun résultat trouvé pour "{{ keyword }}".</p>
        {% endif %}
    ''',
    keyword=keyword,
    file_type=file_type,
    documents=documents,
    total=total,
    page=page,
    per_page=per_page,
    total_pages=total_pages,
    type_counts=type_counts,
    highlight_text=highlight_text
    )

def create_admin_user():
    admin_username = "admin"
    admin_password = "admin"
    cursor.execute("SELECT COUNT(*) FROM users WHERE username = %s", (admin_username,))
    if cursor.fetchone()[0] == 0:
        hashed_password = generate_password_hash(admin_password)
        cursor.execute("INSERT INTO users (username, password) VALUES (%s, %s)", (admin_username, hashed_password))
        db.commit()
        print("[DEBUG] Utilisateur admin créé.")

if __name__ == "__main__":
    os.makedirs(UPLOAD_FOLDER, exist_ok=True)
    os.makedirs(SAVED_FOLDER, exist_ok=True)
    os.makedirs(DOCUMENTS_DIR, exist_ok=True)
    create_admin_user()

    if os.path.exists(DOCUMENTS_DIR):
        print(f"[DEBUG] Indexation des fichiers du répertoire {DOCUMENTS_DIR}...")
        process_directory(DOCUMENTS_DIR)

    app.run(debug=True)