from datetime import datetime
from app.models.paper import Papers
from app.schemas.paper import PaperBase, PaperOut, PaperResponse
from sqlalchemy import or_, select
from sqlalchemy.orm import Session
from app.database.database import SessionLocal
from sqlalchemy.orm import Session
from typing import List
from sqlalchemy import text
import shutil
from pathlib import Path
import aiofiles.os  
from pathlib import Path
import fitz  # PyMuPDF
import re
from sqlalchemy.orm import Session
from pathlib import Path
from flask import  request, jsonify, Blueprint
from werkzeug.utils import secure_filename  # Utilisé pour safegeniser le nom du fichier
from flask import send_file,  abort
import os
router = Blueprint('paper', __name__)
# Dépendance pour la base de données
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()




# def reset_paper_id_sequence(db: Session):
#     # Obtenir le maximum des IDs dans la table
#     result = db.execute(text("SELECT MAX(id_paper) FROM paper"))
#     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('paper_id_seq', {new_seq_value}, false)"))
#     db.commit()
from sqlalchemy import text
from sqlalchemy.orm import Session

def reset_paper_id_sequence(db: Session):
    # Obtenir le maximum des IDs dans la table paper
    result = db.execute(text("SELECT MAX(id_paper) FROM paper"))
    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 dans MySQL
    db.execute(text(f"ALTER TABLE paper AUTO_INCREMENT = {new_seq_value}"))
    db.commit()

def add_paper(db: Session, paper_data):
    #reset_paper_id_sequence(db)  # Reset the sequence to avoid duplicate ID

    p = Papers(
        generated_by=paper_data["generated_by"],
        id_user=paper_data["id_user"],
        file_name=paper_data["file_name"],
        
link=paper_data["file_name"].replace(' ','_').replace('–','').replace('-','').replace('.','')+'.pdf',
        generated_at=datetime.utcnow()
    )
    db.add(p)
    db.commit()
    db.refresh(p)
    new_paper ={
        "id_paper": p.id_paper,
        "generated_by": p.generated_by,
        "id_user": p.id_user,
        "file_name": p.file_name,
        "link": p.link,
        "generated_at": p.generated_at
    }
    return new_paper
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 
    print(file_path)
    # Vérifier si le fichier existe
    if file_path.exists():
        os.remove(file_path)
        return True # Retour d'une réponse sous forme de dictionnaire
    else:
        return False

async def delete_paper(db: Session, paper_id: int):
    paper = db.query(Papers).filter(Papers.id_paper == paper_id).first()
    print(paper)
    if not paper:
        jsonify({"detail": "Paper non rencontré"}), 404
    res=await delete_file(db,paper.link)
    print(res)
    db.delete(paper)
    db.commit()
    #db.refresh(paper)
    return paper
        
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()}")
@router.route('/upload/file', methods=['POST'])
def upload_file():
    if 'file' not in request.files:
        return jsonify({"error": "Aucun fichier envoyé"}), 400

    file = request.files['file']
    if file.filename == '':
        return jsonify({"error": "Nom de fichier vide"}), 400

    db = SessionLocal()
    #try:
    #    reset_paper_id_sequence(db)
    #finally:
    #    db.close()

    filename = secure_filename(file.filename).replace(' ','_').replace('-','').replace('–','')
    file_path = UPLOAD_FOLDER / filename

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

    file_name = filename.replace('.pdf', '')
    link = str(file_path)

    file_info = {
        "link": link,
        "file_name": file_name
    }

    return jsonify(file_info), 200

# @router.get("/papers/", response_model=list[PaperResponse])

async def get_papers(db,skip: int = 0, limit: int = 5,id_user: int=None):
    print(id_user)
    papers_list = db.query(Papers).filter(Papers.id_user == id_user).offset(skip).limit(limit).all()
    print(papers_list)
    response = []
    tz = timezone("Europe/Paris")
    for paper in papers_list:
        if (datetime.utcnow() - paper.generated_at).days > 90:
            b =await delete_paper(db, paper.id_paper)
        generated_at = paper.generated_at
        if generated_at.tzinfo is None:
            generated_at = tz.localize(generated_at)
        response.append({
            "id_paper": paper.id_paper,
            "link": paper.link,
            "id_user": paper.id_user,
            "file_name": paper.file_name,
            "generated_at":  generated_at,
            "generated_by": paper.generated_by
        })
    return response

@router.route("/count", methods=["GET"])
def get_paper_count():
    id_user=int(request.args.get('id_user', None))
    db = next(get_db())
    count = db.query(Papers).filter(Papers.id_user == id_user).count()
    print(count)
    return {"count": count}
# @router.get("/search/{mois}/{annee}/{service}", response_model=bultins.BultinResponseEmp)
# 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()
#     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 response

# @router.get("/search/emp/{mois}/{annee}/{id}", response_model=bultins.BultinResponseEmp)
# def get_bultins_Emp(mois: str, annee: str, id: int, db: Session = Depends(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 response


@router.route("", methods=["POST"])
def create_paper():
    paper = request.json
    db = next(get_db())
    print(paper)
    return jsonify(add_paper(db, paper))
from datetime import datetime
from pytz import timezone


@router.route("/", methods=["GET"])
async def read_papers():
    skip=int(request.args.get('skip', 0))
    limit=int(request.args.get('limit', 10))
    id_user=int(request.args.get('id_user', None))

    db = next(get_db())
    print(id_user)
    res =await get_papers(db,skip, limit,id_user)
    return jsonify(res)

@router.route('/download', methods=['GET'])
def download_file():
    file_name = request.args.get('file_name')
    print(file_name)
    if not file_name:
        return abort(400, description="Le paramètre 'file_name' est requis.")

    # Construction du chemin absolu vers le fichier
    file_path = os.path.abspath(
        os.path.join(os.path.dirname(__file__), '..', '..', 'assets', 'upload_file', file_name)
    )

    # Vérifier que le fichier existe
    if not os.path.isfile(file_path):
        return abort(404, description="Fichier introuvable.")

    return send_file(
        file_path,
        download_name=file_name,
        mimetype='application/pdf',
        as_attachment=True
    )
@router.delete("/delete/{paper_id}")
async def delete_paperr(paper_id: int):
    db = next(get_db())
    print(paper_id)
    deleted_paper = await delete_paper(db, paper_id)
    if deleted_paper is None:
        jsonify({"detail": "Paper non trouvé"}), 404
    return deleted_paper