from datetime import datetime
from app.models.login import Login
from app.schemas.empCat import EmployeeSchema
from app.schemas.login import CreateUser, LoginUpdate
from sqlalchemy import or_, select
from app.crud import employees
from sqlalchemy.orm import Session
from app.database.database import SessionLocal
from app.schemas import employees
from sqlalchemy.orm import Session
from app.models.employees import Category, Employee
from typing import List

from flask import  request, jsonify, Blueprint

router = Blueprint('employees', __name__)


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
from sqlalchemy import text

# def reset_employee_id_sequence(db: Session):
#     # Get the maximum ID from the table
#     result = db.execute(text("SELECT MAX(id) FROM employees"))
#     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('employees_id_seq', {new_seq_value}, false)"))
#     db.commit()
# def reset_user_id_sequence(db: Session):
#     # Get the maximum ID from the table
#     result = db.execute(text("SELECT MAX(id) FROM users"))
#     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('users_id_seq', {new_seq_value}, false)"))
#     db.commit()
from sqlalchemy import text
from sqlalchemy.orm import Session

def reset_employee_id_sequence(db: Session):
    # Obtenir le maximum des IDs dans la table employees
    result = db.execute(text("SELECT MAX(id) FROM employees"))
    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 employees AUTO_INCREMENT = {new_seq_value}"))
    db.commit()

def reset_user_id_sequence(db: Session):
    # Obtenir le maximum des IDs dans la table users
    result = db.execute(text("SELECT MAX(id) FROM users"))
    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 users AUTO_INCREMENT = {new_seq_value}"))
    db.commit()

import bcrypt
import random
import string
def generate_random_password(length=10):
    characters = string.ascii_letters + string.digits + "!@#$%^&*()"
    return ''.join(random.choices(characters, k=length))
def hash_password(password: str) -> str:
    return bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
def verify_password(plain_password: str, hashed_password: str) -> bool:
    return bcrypt.checkpw(plain_password.encode('utf-8'), hashed_password.encode('utf-8'))
def add_employee(db: Session, employee_data):
    existing_employee = db.query(Employee).filter(
        (Employee.matricule == employee_data["matricule"]) 
    ).first()

    if existing_employee:
        return "1"
    #reset_employee_id_sequence(db)  # Reset the sequence to avoid duplicate ID
    #reset_user_id_sequence(db)
    # print(employee_data["first_name"])
    # print(employee_data["last_name"])
    # print(employee_data["matricule"])
    # print(employee_data["mail"])
    # print(employee_data["category_id"])
    # print(employee_data["is_active"])

    new_employee = Employee(
        first_name=employee_data["first_name"],
        last_name=employee_data["last_name"],
        matricule=employee_data["matricule"],
        mail=employee_data["mail"],
        category_id=employee_data["category_id"],  # Utilisation de employee_data["category_id"] au lieu de employee_data.category_id,
        is_active=1,  # Ajoute `is_active=True` par défaut,
        created_at=datetime.utcnow(),
        last_update=datetime.utcnow()
    )
    # Génération des infos d'identification
    generated_password = generate_random_password()
    hashed_password = hash_password(generated_password)

    # Création du user_name (ex: jdoe)
    user_name = employee_data["first_name"][0].lower() + employee_data["last_name"].lower()

    new_user = Login(
        first_name=employee_data["first_name"],
        last_name=employee_data["last_name"],
        matricule=employee_data["matricule"],
        creation_date=datetime.utcnow(),
        password_changed=0,
        user_name=user_name,
        password=generated_password,
        password_hash=hashed_password,
        is_active=1,
        role="user"
    )
    db.add(new_user)
    db.commit()
    db.add(new_employee)
    db.commit()
    db.refresh(new_employee)
    return new_employee
def add_employee_list(db: Session, employees_data):
    news_employees = []
    for employee_data in employees_data:
        employee_data["category_id"]=employee_data["category_id"]["id"]
        emp=add_employee(db, employee_data)
        if emp is None:
            return jsonify({"error": "Employé non ajouté"}), 500
        if emp =="1":
            return jsonify({"error": "Un employé avec ce matricule ou cette adresse mail existe déjà."}), 400
        new_employee={
            "id": emp.id,
            "first_name": emp.first_name,
            "last_name": emp.last_name,
            "matricule": emp.matricule,
            "mail": emp.mail,
            "category_id": emp.category_id,
            "is_active": emp.is_active,
            "created_at": emp.created_at,
            "last_update": emp.last_update
        }
        # reset_employee_id_sequence(db)
        # reset_user_id_sequence(db)
        
        # new_employee = Employee(
        #     first_name=employee_data["first_name"],
        #     last_name=employee_data["last_name"],
        #     matricule=employee_data["matricule"],
        #     mail=employee_data["mail"],
        #     category_id=employee_data["category_id"],
        #     is_active=1,
        #     created_at=datetime.utcnow(),
        #     last_update=datetime.utcnow()
        # )

        # generated_password = generate_random_password()
        # hashed_password = hash_password(generated_password)
        # user_name = employee_data["first_name"][0].lower() + employee_data["last_name"].lower()

        # new_user = Login(
        #     first_name=employee_data["first_name"],
        #     last_name=employee_data["last_name"],
        #     matricule=employee_data["matricule"],
        #     creation_date=datetime.utcnow(),
        #     password_changed=0,
        #     user_name=user_name,
        #     password=generated_password,
        #     password_hash=hashed_password,
        #     role="user"
        # )

        # db.add(new_user)
        # db.commit()
        # db.add(new_employee)
        # db.commit()
        # # db.refresh(new_employee)
        # print(new_user)
        # print(new_employee)
        news_employees.append(new_employee)
        print(news_employees)
    return news_employees

def get_employee(db: Session, employee_id: int):
    return db.query(Employee).filter(Employee.id == employee_id).first()
def get_employeeByMatricule(db: Session, employee_id: str):
    return db.query(Employee).filter(Employee.matricule == employee_id).first()
def get_userByMatricule(db: Session, employee_id: str):
    return db.query(Login).filter(Login.matricule == employee_id).first()
def searchEmployees(
    db: Session,
    search_value: str ,
    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_employee(db: Session, employee_id: int, employee_data: employees.EmployeeUpdate):
    employee = db.query(Employee).filter(Employee.id == employee_id).first()

    if not employee:
        return jsonify({"detail": "Employé non trouvé"}), 404

    # for key, value in employee_data.dict(exclude_unset=True).items():
    #     setattr(employee, key, value)
    employee.last_name = employee_data["last_name"]
    employee.first_name = employee_data["first_name"]
    employee.matricule = employee_data["matricule"]
    employee.mail = employee_data["mail"]
    employee.category_id = employee_data["category_id"]
    employee.last_update = datetime.utcnow()
    if employee_data["is_active"]=="true" or employee_data["is_active"]==True :
        employee.is_active = 1
    else:
        employee.is_active = 0
    db.commit()
    db.refresh(employee)
    return employee
def update_employee_password(db: Session, employee_id: int, employee_data: employees.EmployeeUpdate):
    employee = db.query(Employee).filter(Employee.id == employee_id).first()
    user = db.query(Login).filter(Login.matricule == employee.matricule).first()
    if not employee:
        return jsonify({"detail": "Employé non trouvé"}), 404
    if user:
        generated_password = generate_random_password()
        hashed_password = hash_password(generated_password)
        user.password = generated_password
        user.password_hash = hashed_password
        user.password_changed = 0
    # for key, value in employee_data.dict(exclude_unset=True).items():
    #     setattr(employee, key, value)
    employee.last_name = employee_data["last_name"]
    employee.first_name = employee_data["first_name"]
    employee.matricule = employee_data["matricule"]
    employee.mail = employee_data["mail"]
    employee.category_id = employee_data["category_id"]
    employee.last_update = datetime.utcnow()
    if employee_data["is_active"] =='true':
        employee.is_active = 1
    else:
        employee.is_active = 0
    db.commit()
    db.refresh(employee)
    return employee
def update_password(db: Session, employee_id: int, employee_data: CreateUser):
    user = db.query(Login).filter(Login.id == employee_id).first()
    if not user:
        return jsonify({"detail": "User non trouvé"}), 404
    if user:
        hashed_password = hash_password(employee_data.hashed_password)
        user.password_changed = True
        user.creation_date = datetime.utcnow()
        # user.password = employee_data.password
        user.password_hash = hashed_password

    db.commit()
    db.refresh(user)
    return user
def delete_employee(db: Session, employee_id: int):
    employee = db.query(Employee).filter(Employee.id == employee_id).first()
    user = db.query(Login).filter(Login.matricule == employee.matricule).first()
        
    if not employee:
        return jsonify({"detail": "Employé non trouvé"}), 404
    if user:
        user.is_active = False
        db.commit()
    employee.is_active = False
    db.commit()
    db.refresh(employee)
    return employee
        
# @router.get("/employees/", response_model=List[employees.EmployeeOut])
# def get_employees(skip: int = 0, limit: int = 10, db: Session = Depends(get_db)):
#     employees = db.query(Employee).offset(skip).limit(limit).all()
#     return employees 

def get_employees(db:Session,skip: int = 0, limit: int = 5 ,is_active: bool = True):
    employees_list = db.query(Employee).filter(Employee.is_active == is_active).offset(skip).limit(limit).all()

    # Ajouter category_name manuellement
    response = []
    for emp in employees_list:
        response.append({
            "id": emp.id,
            "first_name": emp.first_name,
            "last_name": emp.last_name,
            "matricule": emp.matricule,
            "mail": emp.mail,
            "category_id": emp.category_id,
            "is_active": emp.is_active,
            "created_at": emp.created_at,
            "last_update": emp.last_update,
            "category_name": emp.category.category_name if emp.category else "Unknown"
        })
    return response
@router.get("/count")
def get_employee_count( is_active: bool = True):
    db = next(get_db())
    count = db.query(Employee).filter(Employee.is_active == is_active).count()
    print(count)
    return {"count": count}
@router.route('/', methods=['POST'])
def create_employee():
    db = next(get_db())
    data = request.get_json()
    if not data:
        return jsonify({"error": "Données JSON manquantes"}), 400
    print(data)
    emp = add_employee(db, data)
    if emp is None:
        return jsonify({"error": "Employé non ajouté"}), 500
    if emp =="1":
        return jsonify({"error": "Un employé avec ce matricule ou cette adresse mail existe déjà."}), 400
    
    new_employee = {
        
            "id": emp.id,
            "first_name": emp.first_name,
            "last_name": emp.last_name,
            "matricule": emp.matricule,
            "mail": emp.mail,
            "category_id": emp.category_id,
            "is_active": emp.is_active,
            "created_at": emp.created_at,
            "last_update": emp.last_update,
            "category_name": emp.category.category_name
    }
    return jsonify(new_employee)
@router.route("/add_list", methods=["POST"])
def create_employee_list():
    db = next(get_db())
    employees = request.get_json()
    
    if not isinstance(employees, list):
        return jsonify({"error": "Invalid data format: expected list"}), 400
    
    try:
        result = add_employee_list(db, employees)
        return jsonify(result)
    except Exception as e:
        print("ERROR:", e)
        return jsonify({"error": str(e)}), 500

@router.route('/<int:employee_id>', methods=['GET'])
def read_employee(employee_id):
    db = next(get_db())
    emp = get_employee(db, employee_id)
    if emp is None:
        return jsonify({"detail": "Employé non trouvé"}), 404
    employee = {
            "id": emp.id,
            "first_name": emp.first_name,
            "last_name": emp.last_name,
            "matricule": emp.matricule,
            "mail": emp.mail,
            "category_id": emp.category_id,
            "is_active": emp.is_active,
            "created_at": emp.created_at,
            "last_update": emp.last_update,
            "category_name": emp.category.category_name if emp.category else "Unknown"
        }
    return jsonify(employee) 
@router.route('/matricule/<string:matricule>', methods=['GET'])
def read_employeeByMatricule(matricule):
    db = next(get_db())
    emp = get_employeeByMatricule(db, matricule)
    if emp is None:
        return jsonify({"detail": "Employé non trouvé"}), 404
    employee = {
            "id": emp.id,
            "first_name": emp.first_name,
            "last_name": emp.last_name,
            "matricule": emp.matricule,
            "mail": emp.mail,
            "category_id": emp.category_id,
            "is_active": emp.is_active,
            "created_at": emp.created_at,
            "last_update": emp.last_update,
            "category_name": emp.category.category_name if emp.category else "Unknown"
    }
    return jsonify(employee) 
@router.route('/user/matricule/<string:matricule>', methods=['GET'])
def read_userByMatricule(matricule):
    db = next(get_db())
    emp = get_userByMatricule(db, matricule)
    if emp is None:
        return jsonify({"detail": "User non trouvé"}), 404
    user = {
            "id": emp.id
            
    }
    return jsonify(user) 
@router.route('/search/<string:search_value>', methods=['GET'])
def search_employees(search_value):
    skip = int(request.args.get('skip', 0))
    limit = int(request.args.get('limit', 10))
    is_active_str = request.args.get('is_active', 'true').lower()
    is_active = is_active_str == 'true'
 
    db = next(get_db())
    employees = searchEmployees(db, search_value, skip, limit, is_active)

    if not employees:
        return jsonify({"detail": "Employé non trouvé"}), 404

    result = []
    for e in employees:
        emp_dict = {
            "id": e.id,
            "first_name": e.first_name,
            "last_name": e.last_name,
            "matricule": e.matricule,
            "email": e.mail,
            "is_active": e.is_active,
            "category_name": e.category.category_name 
        }
        
        result.append(emp_dict)
        # print(result)
    return result



@router.route('/search/count/<string:search_value>/<string:is_active>', methods=['GET'])
def count_search_employees(search_value, is_active):
    db = next(get_db())
    
    # Convertir 'true'/'false' string en booléen
    is_active_bool = is_active.lower() == 'true'

    count = countSearchEmployees(db, search_value, is_active_bool)
    print(count)
    if count is None:
        return jsonify({"detail": "Employé non trouvé"}), 404

    return str(count)

@router.route('/', methods=['GET'])
def read_employees():
    try:
        db = next(get_db())
        # Récupération des paramètres query ?skip=...&limit=...&is_active=...
        skip = int(request.args.get('skip', 0))
        limit = int(request.args.get('limit', 10))
        is_active = request.args.get('is_active', 'true').lower() == 'true'

        employees  = get_employees(db,skip, limit, is_active)

        # Conversion en JSON (si nécessaire)
        # response = [item.as_dict() for item in result]

        return jsonify(employees )
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@router.route('/<int:employee_id>', methods=['PUT'])
def update_employee_flask(employee_id):
    db = next(get_db())  # ou utilisez un gestionnaire de contexte selon votre setup

    employee_data = request.get_json()

    emp = update_employee(db, employee_id, employee_data)

    if emp is None:
        return jsonify({"detail": "Employé non trouvé"}), 404
    updated_employee = {
        
            "id": emp.id,
            "first_name": emp.first_name,
            "last_name": emp.last_name,
            "matricule": emp.matricule,
            "mail": emp.mail,
            "category_id": emp.category_id,
            "is_active": emp.is_active,
            "created_at": emp.created_at,
            "last_update": emp.last_update,
            "category_name": emp.category.category_name
    }
    return jsonify(updated_employee)

@router.route("/generate/password", methods=['GET'])
def generatePassword():
    db = next(get_db())
    employees = db.query(Employee).filter(Employee.is_active == 1).all()
    for employee in employees: 
        user = db.query(Login).filter(Login.matricule == employee.matricule).first()
        if user:
            #reset_user_id_sequence(db)
            generated_password = generate_random_password()
            hashed_password = hash_password(generated_password)
           # user = Login(first_name=employee.first_name, last_name=employee.last_name,matricule=employee.matricule, password=generated_password, password_hash=hashed_password, role="user", creation_date=datetime.utcnow(), password_changed=False, user_name=employee.first_name[0].lower() + employee.last_name.lower(), is_active=True)
            user.password = generated_password
            user.password_hash = hashed_password
            user.password_changed = 0
            db.add(user)

            db.commit()
            db.refresh(user)
    return jsonify({'succes':'true'}), 200
@router.route('/password/<int:employee_id>', methods=['PUT'])
def update_employee_password_flask(employee_id):
    db = next(get_db())

    employee_data = request.get_json()

    emp = update_employee_password(db, employee_id, employee_data)

    if emp is None:
        return jsonify({"detail": "Employé non trouvé"}), 404
    updated_employee = {
        
            "id": emp.id,
            "first_name": emp.first_name,
            "last_name": emp.last_name,
            "matricule": emp.matricule,
            "mail": emp.mail,
            "category_id": emp.category_id,
            "is_active": emp.is_active,
            "created_at": emp.created_at,
            "last_update": emp.last_update,
            "category_name": emp.category.category_name
    }
    return jsonify(updated_employee)
@router.route('/password/only/<int:employee_id>', methods=['PUT'])
def update_employee_password_only(employee_id):
    db = next(get_db())
    data = request.get_json()
    
    new_password = data.get('new_password')
    if not new_password:
        return jsonify({"detail": "Le mot de passe est requis"}), 400

    employee = db.query(Login).filter(Login.id == employee_id).first()
    if employee is None:
        return jsonify({"detail": "Erreur lors du changement de mot de passe."}), 404
    is_last_password = verify_password(new_password, employee.password_hash)
    if is_last_password:
        return jsonify({"detail": "Le nouveau mot de passe doit être différent de l’ancien."}), 400
    employee.hashed_password = new_password

    updated_employee = update_password(db, employee_id, employee)
    if updated_employee is None:
        return jsonify({"detail": "Erreur lors du changement de mot de passe."}), 404

    return jsonify(True)
@router.delete("/{employee_id}")
def delete_employeee(employee_id: int):
    db = next(get_db())
    deleted_employee = delete_employee(db, employee_id)
    if deleted_employee is None:
        return jsonify({"detail": "Mot de passe incorrect"}), 404
    return deleted_employee