from datetime import datetime
from app.models.login import Login
from app.models.message import Messages
from app.schemas.message import MessageBase, MessageCreate, MessageOut, MessageResponse, MessageResponse1
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 flask import  request, jsonify, Blueprint

router = Blueprint('message', __name__)


# Dépendance pour la base de données
def get_db():
    
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
from sqlalchemy import text
	
# def reset_message_id_sequence(db: Session):
#     # Get the maximum ID from the table
#     result = db.execute(text("SELECT MAX(id_message) FROM message"))
#     max_id = result.scalar()  # Get the scalar result from the query
#     new_seq_value = (max_id + 1) if max_id is not None else 1

#     # Set the sequence value to max(id) + 1
#     db.execute(text(f"SELECT setval('message_id_seq', {new_seq_value}, false)"))
#     db.commit()
from sqlalchemy import text
from sqlalchemy.orm import Session

def reset_message_id_sequence(db: Session):
    # Obtenir le maximum des IDs dans la table message
    result = db.execute(text("SELECT MAX(id_message) FROM message"))
    max_id = result.scalar()  # Récupérer le résultat scalaire de la requête

    # Calculer la nouvelle valeur de la séquence (max(id) + 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 message AUTO_INCREMENT = {new_seq_value}"))
    db.commit()

from fastapi_mail import FastMail, MessageSchema, ConnectionConfig
from app.routes.smtp import read_smtp

infos = read_smtp()
conf = ConnectionConfig(
    MAIL_USERNAME=infos[0].get('username'),
    MAIL_PASSWORD=infos[0].get('password'),
    MAIL_FROM=infos[0].get('sender_email_address'),
    MAIL_PORT=infos[0].get('port'),
    MAIL_SERVER=infos[0].get('hostname'),
    MAIL_STARTTLS=True,
    MAIL_SSL_TLS=False,
    USE_CREDENTIALS=True
)
def send_email_task(email_data):
    m = 'support_rh@finashore.ma'
    message = MessageSchema(
        subject=email_data['subject'],
        recipients=[email_data['email']],
        # cc=[m],
        body=email_data['body'],
        subtype="html"
    )

    fm = FastMail(conf)
    try:
        import asyncio
        loop = asyncio.new_event_loop()
        asyncio.set_event_loop(loop)
        loop.run_until_complete(fm.send_message(message))
    except Exception as e:
        print("Erreur lors de l'envoi de l'email:", e)
    finally:
        loop.close()
def add_message(db: Session, message_data,id_user):
    #reset_message_id_sequence(db)  # Reset the sequence to avoid duplicate ID
    m = Messages(
        sujet=message_data["sujet"],
        message=message_data["message"],
        id_source=id_user,
        id_dest=message_data["id_dest"],
        generated_at=datetime.utcnow()
    )
    db.add(m)
    db.commit()
    db.refresh(m)
    email_data = {
            'subject': 'Notification : Nouvelle demande RH assignée',
            'email': 'hanane.aboussolh@finashore.com',
            'body': f'''
                <p>Bonjour Mme Hanane,</p>
                <p>Une nouvelle demande vous a été assignée dans l'interface INTRANET Finashore :</p>
                <p>Vous pouvez consulter depuis le lien suivant:</p>
                <p>https://finadocs.finashore.ma</p>
                <p>Cordialement</p>
                '''
    }
    try:
            send_email_task(email_data)
    except Exception as e:
            print("Erreur lors de l'envoi de l'email:", e)
            return jsonify(False), 500
    new_message={
        "id_message": m.id_message,
        "sujet": m.sujet,
        "message": m.message,
        "generated_at": m.generated_at,
        "id_source": m.id_source,
        "id_dest": m.id_dest

    }
    return new_message
def get_message(db: Session, id_message: int):
    return db.query(Messages).filter(Messages.id_message == id_message).first()


# def searchMessages(
#     db: Session = Depends(get_db),
#     search_value: str = Query(..., description="Valeur de recherche"),
#     skip: int = 0,
#     limit: int = 10,
#     is_active: bool = True
# ):
#     return db.query(Employee).join(Category, Employee.category_id == Category.id).filter(
#         or_(
#             Employee.first_name.ilike(f"%{search_value}%"),
#             Employee.last_name.ilike(f"%{search_value}%"),
#             Employee.matricule.ilike(f"%{search_value}%"),
#             Category.category_name.ilike(f"%{search_value}%")
#         ),
#         Employee.is_active == is_active
#     ).offset(skip).limit(limit).all()

# def countSearchEmployees(db: Session, search_value: str, is_active: bool = True):
#     return db.query(Employee).join(Category, Employee.category_id == Category.id).filter(
#         or_(
#             Employee.first_name.ilike(f"%{search_value}%"),
#             Employee.last_name.ilike(f"%{search_value}%"),
#             Employee.matricule.ilike(f"%{search_value}%"),
#             Category.category_name.ilike(f"%{search_value}%")
#         ),
#         Employee.is_active == is_active

#     ).count()

# def update_message(db: Session, employee_id: int, employee_data: employees.EmployeeUpdate):
#     employee = db.query(Employee).filter(Employee.id == employee_id).first()

#     if not employee:
#         raise HTTPException(status_code=404, detail="Employé non trouvé")

#     for key, value in employee_data.dict(exclude_unset=True).items():
#         setattr(employee, key, value)

#     db.commit()
#     db.refresh(employee)
#     return employee

def delete_message(db: Session, id_message: int):
    message = db.query(Messages).filter(Messages.id_message == id_message).first() 
    if not message:
        return jsonify({"detail": "Message non trouvé"}), 404
    db.delete(message)
    db.commit()
    return message
# @router.get("/messages/", response_model=list[employees.EmployeeResponse])
from sqlalchemy.sql import exists
from sqlalchemy.orm import aliased
def get_messages(db,skip: int = 0, limit: int = 5,id: int = None, is_dest: bool = False):
    
    if is_dest:
        Messages2 = aliased(Messages)
        print(Messages.id_message)
        messages_list = (
            db.query(Messages, Login)
            .join(Login, Messages.id_source == Login.id)
            .filter(Messages.id_dest == 0)
            .filter(
                ~db.query(Messages2)
                .filter(Messages.id_message == Messages2.id_source)
                .exists()
            )
            .order_by(Messages.id_message.asc())
            .offset(skip)
            .limit(limit)
            .all()
        )

        # messages_list = db.query(Messages,Login).join(Login, Messages.id_source == Login.id).filter(Login.role == "user").order_by(Messages.id_message.asc()).offset(skip).limit(limit).all()
    else:
        # messages_list = db.query(Messages,Login).join(Login, Messages.id_source == Login.id).order_by(Messages.id_message.asc()).filter(Messages.id_source == id).offset(skip).limit(limit).all()
        Messages2 = aliased(Messages)
        print(Messages.id_message)
        messages_list = (
            db.query(Messages, Login)
            .join(Login, Messages.id_source == Login.id)
            .filter(Messages.id_source == id)
            .filter(
                ~db.query(Messages2)
                .filter(Messages.id_message == Messages2.id_source)
                .exists()
            )
            .order_by(Messages.id_message.asc())
            .offset(skip)
            .limit(limit)
            .all()
        )
    print(messages_list)
    response = []
    for m,l in messages_list:
        #if (datetime.utcnow() - m.generated_at).days > 90:
            #message = db.query(Messages).filter(Messages.id_message == m.id_message).first()
            #db.delete(message)
            #db.commit()
        response.append({
            "id_message": m.id_message,
            "id_source": m.id_source,
            "id_dest": m.id_dest,
            "sujet": m.sujet,
            "message": m.message,
            "generated_at": m.generated_at,
            "first_name": l.first_name ,
            "last_name": l.last_name ,
            "matricule": l.matricule ,
            "role": l.role 
           
        })
    return response

def get_reponses(db,skip: int = 0, limit: int = 5,id: int = None, is_dest: bool = False):

    messages_list = db.query(Messages,Login).join(Login, Messages.id_dest == Login.id).order_by(Messages.id_message.asc()).filter(Messages.id_dest == id).offset(skip).limit(limit).all()
    print(messages_list)
    response = []
    for m,l in messages_list:
        if (datetime.utcnow() - m.generated_at).days > 90:
            message = db.query(Messages).filter(Messages.id_message == m.id_message).first()
            db.delete(message)
            db.commit()
        response.append({
            "id_message": m.id_message,
            "id_source": m.id_source,
            "id_dest": m.id_dest,
            "sujet": m.sujet,
            "message": m.message,
            "generated_at": m.generated_at,
            "first_name": l.first_name ,
            "last_name": l.last_name ,
            "matricule": l.matricule ,
            "role": l.role 
           
        })
    print(response)
    return response


@router.get("/count")
def get_message_count(id: int = None, is_dest: bool = False):
    db = next(get_db())
    if is_dest:
        count = db.query(Messages).filter(Login.role == "user").count()
    else:
        count = db.query(Messages,Login).join(Login, Messages.id_source == Login.id).filter(Messages.id_source == id).count()
    print(count)
    return {"count": count}
@router.route("/reponses/count")
def get_reponse_count():
    id=int(request.args.get('id', '0'))
    is_dest=bool(request.args.get('is_dest', False))
    db = next(get_db())
    count = db.query(Messages,Login).join(Login, Messages.id_dest == Login.id).filter(Messages.id_dest == id).count()
    print(count)
    return {"count": count}
@router.route("", methods=["POST"])
def create_message():
    id_user = int(request.args.get('id_user', None))
    message = request.get_json()
    db = next(get_db())
    print(message)
    return jsonify(add_message(db, message,id_user))

@router.get("/{id_message}")
def read_message(id_message: int):
    db = next(get_db())
    message = get_message(db, id_message)
    if message is None:
        return jsonify({"detail": "Message non trouvé"}), 404
    return message 

# @router.get("/search/{search_value}", response_model=List[EmployeeSchema])
# def searchEmployeess(search_value: str,skip: int = 0, limit: int = 10, is_active: bool = True ,db: Session = Depends(get_db)):
#     print(search_value)
#     employee = searchEmployees(db, search_value,skip, limit,is_active)
#     if employee is None:
#         raise HTTPException(status_code=404, detail="Employé non trouvé")
#     return employee
# @router.get("/search/count/{search_value}/{is_active}", response_model=int)
# def countSearchEmployeess(search_value: str,is_active: bool = True , db: Session = Depends(get_db)):
#     countEmp = countSearchEmployees(db, search_value,is_active)
#     if countEmp is None:
#         raise HTTPException(status_code=404, detail="Employé non trouvé")
#     return countEmp
@router.route("/", methods=["GET"])
def read_messages():
    skip=int(request.args.get('skip', 0))
    limit=int(request.args.get('limit', 10))
    id_user=int(request.args.get('id_user', None))
    is_dest=request.args.get('is_dest', False)
    if is_dest == 'true':
        is_dest=True
    else:
        is_dest=False
    print(skip,limit,id_user,is_dest)
    db = next(get_db())
    return jsonify(get_messages(db,skip, limit,id_user,is_dest))
@router.route("/reponses/messages", methods=["GET"])
def read_reponses():
    skip = int(request.args.get('skip', 0))
    limit = int(request.args.get('limit', 10))
    id_user = int(request.args.get('id_user', None))
    is_dest=bool(request.args.get('is_dest', False))
    db = next(get_db())
    return jsonify(get_reponses(db,skip, limit,id_user,is_dest))
@router.put("/{id_message}")
def update_message(id_message: int, message: MessageCreate):
    db = next(get_db())
    updated_message =update_message(db, id_message, message)
    if updated_message is None:
        return jsonify({"detail": "Message non trouvé"}), 404
    return updated_message
@router.delete("/{id_message}")
def delete_message(id_message: int):
    db = next(get_db())
    deleted_message = delete_message(db, id_message)
    if deleted_message is None:
        return jsonify({"detail": "Message non trouvé"}), 404
    return deleted_message