from datetime import datetime
from app.models.employees import Employee
from app.models.generated_bultins import GeneratedBultins
from sqlalchemy.orm import Session
from app.database.database import SessionLocal
from app.schemas import bultins
from sqlalchemy.orm import Session
from app.models.bultins import Bultin
from sqlalchemy import text
import shutil
from pathlib import Path
from pathlib import Path
import fitz  # PyMuPDF
import re
from sqlalchemy.orm import Session
from pathlib import Path

# from fastapi import APIRouter, Depends, HTTPException
# from fastapi import  File, UploadFile

# router = APIRouter()
from flask import  request, jsonify, Blueprint
from werkzeug.utils import secure_filename  # Utilisé pour sécuriser le nom du fichier

router = Blueprint('bultins', __name__)
# router = Flask(__name__)

# Dépendance pour la base de données
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()




# def reset_bultin_id_sequence(db: Session):
#     # Obtenir le maximum des IDs dans la table
#     result = db.execute(text("SELECT MAX(id) FROM bultins"))
#     max_id = result.scalar()

#     # Si la table est vide, on démarre la séquence à 1
#     new_seq_value = (max_id + 1) if max_id is not None else 1

#     # Mettre à jour la séquence
#     db.execute(text(f"SELECT setval('bultins_id_seq', {new_seq_value}, false)"))
#     db.commit()
from sqlalchemy import text
from sqlalchemy.orm import Session

def reset_bultin_id_sequence(db: Session):
    # Obtenir le maximum des IDs dans la table
    result = db.execute(text("SELECT MAX(id) FROM bultins"))
    max_id = result.scalar()

    # Si la table est vide, on démarre la séquence à 1
    new_seq_value = (max_id + 1) if max_id is not None else 1

    # Réinitialiser la valeur de l'auto-incrément (MySQL)
    db.execute(text(f"ALTER TABLE bultins AUTO_INCREMENT = {new_seq_value}"))
    db.commit()
# def reset_generated_bultins_id_sequence(db: Session):
#         # Get the maximum ID from the table
#         result = db.execute(text("SELECT MAX(id) FROM generated_bultins"))
#         max_id = result.scalar()  # Get the scalar result from the query
#         # Set the sequence value to max(id) + 1        
#         db.execute(text(f"SELECT setval('generated_bultins_id_seq', {max_id + 1})"))
#         db.commit()
# def reset_generated_bultins_id_sequence(db: Session):
#     # Obtenir le maximum des IDs dans la table
#     result = db.execute(text("SELECT MAX(id) FROM generated_bultins"))
#     max_id = result.scalar()

#     # Si la table est vide, on démarre la séquence à 1
#     new_seq_value = (max_id + 1) if max_id is not None else 1

#     # Mettre à jour la séquence
#     db.execute(text(f"SELECT setval('generated_bultins_id_seq', {new_seq_value}, false)"))
#     db.commit()
def reset_generated_bultins_id_sequence(db: Session):
    # Obtenir le maximum des IDs dans la table
    result = db.execute(text("SELECT MAX(id) FROM generated_bultins"))
    max_id = result.scalar()

    # Si la table est vide, on démarre la séquence à 1
    new_seq_value = (max_id + 1) if max_id is not None else 1

    # Réinitialiser la valeur de l'auto-incrément pour MySQL
    db.execute(text(f"ALTER TABLE generated_bultins AUTO_INCREMENT = {new_seq_value}"))
    db.commit()
def add_bultin(db: Session, bultin_data):
    #reset_bultin_id_sequence(db)  # Reset the sequence to avoid duplicate ID

    b = Bultin(
        name=bultin_data["name"],
        mois=bultin_data["mois"],
        file_name=bultin_data["file_name"],
        path=bultin_data["file_name"]+'.pdf',
        is_active=bultin_data["is_active"],  # Ajoute `is_active=True` par défaut,
        created_at=datetime.utcnow()
    )
    db.add(b)
    db.commit()
    db.refresh(b)
    new_bultin={
        "id": b.id,
        "name": b.name,
        "mois": b.mois,
        "file_name": b.file_name,
        "path": b.path,
        "is_active": b.is_active,
        "created_at": b.created_at
    }
    return new_bultin
def add_GeneratedBultins(db: Session, file_name,link):
   # reset_bultin_id_sequence(db)  # Reset the sequence to avoid duplicate ID

    new_bultin = GeneratedBultins(
        file_name=file_name,
        link=link,
        generated_by='Admin',
        generated_at=datetime.utcnow()
    )
    db.add(new_bultin)
    db.commit()
    db.refresh(new_bultin)
    return new_bultin
import os
def chercher_fichiers_similaires(UPLOAD_FOLDER, file_name):
    # On enlève l'extension du fichier donné
    nom_sans_extension = os.path.splitext(file_name)[0]

    fichiers_trouves = []
    for f in os.listdir(UPLOAD_FOLDER):
        if f.startswith(nom_sans_extension):
            fichiers_trouves.append(f)
    
    return fichiers_trouves
async def delete_file(db: Session,file_name: str):    
    file_path = UPLOAD_FOLDER / file_name 
    resultats = chercher_fichiers_similaires(OUTPUT_FOLDER, file_name)

    print(file_path)
    # Vérifier si le fichier existe
    if file_path.exists():
        # Supprimer le fichier de manière asynchrone
        for f in resultats:
            file_path_autre = OUTPUT_FOLDER / f 
            if file_path.exists():
                os.remove(file_path_autre)
            g_bultins = db.query(GeneratedBultins).filter(GeneratedBultins.file_name == f).first()
            if g_bultins:
                db.delete(g_bultins)
                db.commit()
        os.remove(file_path)
        return True # Retour d'une réponse sous forme de dictionnaire
    else:
        return False

# SERVICE
async def delete_bultin(db: Session, bultin_id: int):
    b = db.query(Bultin).filter(Bultin.id == bultin_id).first()
    
    if not b:
        return None  # ❌ PAS jsonify ici
    gb = db.query(GeneratedBultins).filter(GeneratedBultins.file_name.like(f"%{b.mois}%")).all()
    await delete_file(db, b.path)
    for g in gb:
        db.delete(g)
        db.commit()
    db.delete(b)
    db.commit()
    db.refresh(b)
    bultin = {
        "id": b.id,
        "name": b.name,
        "mois": b.mois,
        "file_name": b.file_name,
        "path": b.path,
        "is_active": b.is_active,
        "created_at": b.created_at
    }

    return bultin  # ✅ dictionnaire pur

UPLOAD_FOLDER = Path("assets/upload_file")
UPLOAD_FOLDER.mkdir(parents=True, exist_ok=True)
OUTPUT_FOLDER = UPLOAD_FOLDER / "files"
OUTPUT_FOLDER.mkdir(parents=True, exist_ok=True)
if not OUTPUT_FOLDER.exists():
    print(f"❌ Dossier introuvable : {UPLOAD_FOLDER.resolve()}")
else:
    print(f"✅ Le dossier existe : {UPLOAD_FOLDER.resolve()}")
def generatedBultins( file_name: str, db: Session):


    pdf_path = UPLOAD_FOLDER / file_name
    print(pdf_path)
    if not pdf_path.exists():
        return False
    
    matricules_files = split_pdf_by_page(pdf_path)
    print(matricules_files)
    for matricule, file_path in matricules_files.items():
        print(matricule)
        if matricule!="unknown":
            add_GeneratedBultins(db, matricules_files.get(matricule, "").split("\\")[-1] ,file_path)
    return True
from flask import Flask, send_from_directory,abort
import os

@router.route('/files/<path:filename>', methods=['GET'])
def serve_file(filename):
    base_dir = os.path.abspath("assets/upload_file/files")
    file_path = os.path.join(base_dir, filename)
    if not os.path.exists(file_path):
        abort(404)
    return send_from_directory(base_dir, filename)

@router.post("/upload/file")
async def upload_file():
    # Vérifier si le fichier est dans la requête
    file = request.files.get('file')
    if not file:
        return jsonify({"message": "Aucun fichier téléchargé."}), 400

    # Sécuriser le nom du fichier et définir le chemin où il sera enregistré
    file_name = secure_filename(file.filename)
    file_path = UPLOAD_FOLDER / file_name

    # Enregistrer le fichier sur le serveur
    with open(file_path, "wb") as buffer:
        shutil.copyfileobj(file.stream, buffer)

    # Simuler la récupération de la session DB
    db = next(get_db())


    # Réinitialiser la séquence des ID et traiter le fichier
    #reset_generated_bultins_id_sequence(db)
    generatedBultins(file.filename, db)

    # Extraire des informations du nom du fichier
    file_name_without_ext = file.filename.replace('.pdf', '')
    name = file_name_without_ext.split('_')[0]
    mois = name[-6:]
    path = str(file_path)

    # Informations sur le fichier
    file_info = {
        "name": name,
        "mois": mois,
        "file_name": file_name_without_ext,
        "path": path
    }

    return jsonify(file_info)
@router.route('', methods=['GET'])
async def get_bultins():
    is_active = 1
    db = next(get_db())

    bultins_list = db.query(Bultin).filter(Bultin.is_active == is_active).all()

    # Ajouter category_name manuellement
    response = []
    for bul in bultins_list:
        if (datetime.utcnow() - bul.created_at).days > 90:
            b = await delete_bultin(db, bul.id)
        response.append({
            "id": bul.id,
            "name": bul.name,
            "mois": bul.mois,
            "file_name": bul.file_name,
            "path": bul.path,
            "is_active": bul.is_active,
            "created_at": bul.created_at
        })
    return jsonify(response)
@router.route("/search/<string:mois>/<string:annee>/<string:service>", methods=['GET']) 
def get_bultins_search(mois, annee, service):
    db = next(get_db())

    bultin = db.query(Bultin).filter(Bultin.mois == mois + annee).filter(Bultin.is_active == True).first()
    if not bultin:
        return {"error": "Aucun bulletin trouvé"}
    
    pdf_path = UPLOAD_FOLDER / bultin.path
    print(pdf_path)
    matricules_files = {}
    if not pdf_path.exists():
        return {"error": "Fichier PDF introuvable"}
    resultats = chercher_fichiers_similaires(OUTPUT_FOLDER, bultin.path)
    for f in resultats:
        matricule = f.split("_")[-1].split(".")[0]
        print(f)
        print(matricule)
        if matricule != "unknown":
            matricules_files[matricule] = f
    # matricules_files = split_pdf_by_page(pdf_path)
    # print(matricules_files)
    employees_info = get_employees_info(matricules_files, db,service,mois + annee)
    
    response = {
        "id": bultin.id,
        "name": bultin.name,
        "mois": bultin.mois,
        "file_name": bultin.file_name,
        "path": str(pdf_path),
        "is_active": bultin.is_active,
        "created_at": bultin.created_at,
        "employees": employees_info
    }
    
    return jsonify(response)
@router.route("/search/emp/id/<id>", methods=['GET'])
def get_bultins_Emp_ID(id):
    db = next(get_db())

    employee = db.query(Employee).filter(Employee.matricule==id).first()
    bultins =db.query(GeneratedBultins).filter(GeneratedBultins.file_name.like(f"%{employee.matricule}%")).all()
    employees_info = []

    if not bultins:
        return {"error": "Aucun bulletin trouvé"}
    
    for bultin in bultins:

        chemin = bultin.file_name
        
        # Expression régulière pour trouver un motif de 6 chiffres après "BP"
        match = re.search(r'BP(\d{6})', chemin)

        if match:
            mois = match.group(1)
            print("Code extrait :", mois)
        else:
            mois=""
            print("Aucun code trouvé")
        b=db.query(Bultin).filter(Bultin.mois.like(f"%{mois}%")).first()
        if not b:
            continue

        employees_info.append({
            "first_name": employee.first_name,
            "last_name": employee.last_name,
            "matricule": employee.matricule,
            "email": employee.mail,
            "file_name": bultin.file_name,
            "mois": mois,
            "selected": False
        })

    
    response = {
        "id": "",
        "name": "",
        "mois": "",
        "file_name": "",
        "path": "",
        "is_active": "",
        "created_at": "",
        "employees": employees_info
    }
    return jsonify(response)
@router.route("/search/emp/<string:mois>/<string:annee>/<int:id>", methods=['GET'])
def get_bultins_Emp(mois, annee, id):
    db = next(get_db())

    bultin = db.query(Bultin).filter(Bultin.mois == mois + annee).filter(Bultin.is_active == True).first()
    employee = db.query(Employee).filter(Employee.id==id).first()

    if not bultin:
        return {"error": "Aucun bulletin trouvé"}
    
    pdf_path = UPLOAD_FOLDER / bultin.path
    print(pdf_path)
    if not pdf_path.exists():
        return {"error": "Fichier PDF introuvable"}
    
    matricules_files = split_pdf_by_page_id(pdf_path,employee.matricule)
    print(matricules_files)
    employees_info = get_employees_info_id(matricules_files, db,mois + annee)
    
    response = {
        "id": bultin.id,
        "name": bultin.name,
        "mois": bultin.mois,
        "file_name": bultin.file_name,
        "path": str(pdf_path),
        "is_active": bultin.is_active,
        "created_at": bultin.created_at,
        "employees": employees_info
    }
    return jsonify(response) 


import re

# def extract_matricule(text):
#     # Nettoyage du texte pour éliminer les espaces et sauts de ligne superflus
#     text = re.sub(r"\s+", " ", text).strip()
#     print(repr(text))

#     # match = re.search(r"Matricule\s*:\s*(\d+)", text)
#     # Recherche du matricule
#     match = re.search(r"Matricule\s*:\s*(\d+)", text)

#     return match.group(1) if match else "unknown"
import re

def extract_matricule(text):
    # Nettoyage du texte pour éliminer les espaces et sauts de ligne superflus
    text = re.sub(r"\s+", " ", text).strip()

    # Recherche du matricule après "Matricule :"
    match = re.search(r"Matricule\s*:\s*(\d+)", text)

    if match:
        return match.group(1)
    
    # Si aucun matricule trouvé, rechercher le premier nombre de 5 chiffres entouré d'espaces
    alt_match = re.search(r"\s(\d{5})\s", text)
    
    return alt_match.group(1) if alt_match else "unknown"

def split_pdf_by_page(pdf_path: Path):
    doc = fitz.open(str(pdf_path))
    original_filename = pdf_path.stem  # Nom du fichier sans extension
    matricules_files = {}
    
    for i, page in enumerate(doc):
        text = page.get_text("text")
        matricule = extract_matricule(text)
        output_filename = f"{original_filename}_{matricule}.pdf"
        output_path = OUTPUT_FOLDER / output_filename
        
        new_doc = fitz.open()
        new_doc.insert_pdf(doc, from_page=i, to_page=i)
        new_doc.save(str(output_path))
        new_doc.close()
        
        matricules_files[matricule] = str(output_path)
    
    doc.close()
    return matricules_files


def split_pdf_by_page_id(pdf_path: Path,mat:str):
    doc = fitz.open(str(pdf_path))
    original_filename = pdf_path.stem  # Nom du fichier sans extension
    matricules_files = {}
    
    for i, page in enumerate(doc):
        text = page.get_text("text")
        matricule = extract_matricule(text)
        if matricule ==mat:
            output_filename = f"{original_filename}_{matricule}.pdf"
            output_path = OUTPUT_FOLDER / output_filename
        
            new_doc = fitz.open()
            new_doc.insert_pdf(doc, from_page=i, to_page=i)
            new_doc.save(str(output_path))
            new_doc.close()
        
            matricules_files[matricule] = str(output_path)
    
    doc.close()
    return matricules_files
def get_employees_info(matricules_files, db,service,mois):
    employees = db.query(Employee).filter(Employee.matricule.in_(matricules_files.keys())).all()
    employees_info = []
    for emp in employees:
        print(emp.first_name)
        print(emp.category.category_name)
        if emp.category.category_name != service:
            continue
        employees_info.append({
            "first_name": emp.first_name,
            "last_name": emp.last_name,
            "matricule": emp.matricule,
            "email": emp.mail,
            "file_name": matricules_files.get(emp.matricule, "").split("\\")[-1] ,
            "mois": mois,
            "selected": False
        })
    return employees_info
def get_employees_info_id(matricules_files, db,mois):
    employees = db.query(Employee).filter(Employee.matricule.in_(matricules_files.keys())).all()
    
    employees_info = []
    for emp in employees:

        employees_info.append({
            "first_name": emp.first_name,
            "last_name": emp.last_name,
            "matricule": emp.matricule,
            "email": emp.mail,
            "file_name": matricules_files.get(emp.matricule, "").split("\\")[-1] ,
            "mois": mois,
            "selected": False
        })
    return employees_info

# UPLOAD_FOLDER_LOAD = Path("C:/Users/uhp/Documents/FinaShore/ENVOIBP/assets/upload_file/files")

# router.mount("/assets/upload_file/files", StaticFiles(directory=str(UPLOAD_FOLDER_LOAD)), name="files")


# @router.route('/assets/upload_file/files/<filename>')
# def get_pdf(filename):
#     file_path = OUTPUT_FOLDER / filename
#     if not file_path.exists():
#         return {"error": "Fichier introuvable"}, 404
#     return send_from_directory(OUTPUT_FOLDER, filename)

# @router.get("/search/{mois}/{annee}/{service}", response_model=bultins.BultinResponse)
# def get_bultins_search(mois: str,annee: str,service: str,db: Session = Depends(get_db)):
#     bultin = db.query(Bultin).filter( Bultin.mois == mois+annee 
#     ).filter(Bultin.is_active == True).first()

#     # Ajouter category_name manuellement
#     response ={
#             "id": bultin.id,
#             "name": bultin.name,
#             "mois": bultin.mois,
#             "file_name": bultin.file_name,
#             "path": bultin.path,
#             "is_active": bultin.is_active,
#             "created_at": bultin.created_at
#         }
#     return response
@router.route("", methods=["POST"])
def create_bultin():
    bultin= request.json
    db = next(get_db())

    print(bultin)
    return jsonify(add_bultin(db, bultin))
# @router.route("", methods=["GET"])
# def read_bultins( is_active: bool = True):
#     db = next(get_db())

#     return jsonify(get_bultins(db,is_active))



# ROUTE
@router.route("/delete/<int:bultin_id>", methods=["DELETE"])
async def delete_bultinn(bultin_id):
    db = next(get_db())

    b = await delete_bultin(db, bultin_id)

    if b is None:
        return jsonify({"detail": "Bultin non trouvé"}), 404

    deleted_bultin = {
        "id": b["id"],
        "name": b["name"],
        "mois": b["mois"],
        "file_name": b["file_name"],
        "path": b["path"],
        "is_active": b["is_active"],
        "created_at": b["created_at"]
    }

    return jsonify(deleted_bultin)