from app.crud import categories
from sqlalchemy.orm import Session
from app.database.database import SessionLocal
from app.schemas import categories
from sqlalchemy.orm import Session
from app.models.categories import  Category
from typing import List
from sqlalchemy import text
from datetime import datetime
from flask_cors import CORS
from flask import  request, jsonify, Blueprint
from werkzeug.utils import secure_filename  # Utilisé pour sécuriser le nom du fichier

router = Blueprint('categories', __name__)
#CORS(router)
# Dépendance pour la base de données
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
# def reset_category_id_sequence(db: Session):
#     # Get the maximum ID from the table
#     result = db.execute(text("SELECT MAX(id) FROM categories"))
#     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('categories_id_seq', new_seq_value, false)"))
#     db.commit()
from sqlalchemy import text
from sqlalchemy.orm import Session

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

def get_category(db: Session, category_id: int):
    return db.query(Category).filter(Category.id == category_id).first()
def add_category(db: Session, category_data):
    #reset_category_id_sequence(db)  # Reset the sequence to avoid duplicate ID

    new_category= Category(
        category_name=category_data.category_name,
        category_desc=category_data.category_desc,
        is_active=category_data.is_active,  # Ajoute `is_active=True` par défaut,
        created_at=datetime.utcnow(),
    )
    db.add(new_category)
    db.commit()
    db.refresh(new_category)
    return new_category
def update_category(db: Session, category_id: int, category_data: categories.CategorieUpdate):
    # Recherche de la catégorie à mettre à jour dans la base de données
    category = db.query(Category).filter(Category.id == category_id).first()

    if not category:
        return {"detail": "Category non trouvé"}, 404

    # Mise à jour des attributs de la catégorie à partir des données reçues
    for key, value in category_data.dict(exclude_unset=True).items():
        setattr(category, key, value)

    # Enregistrement des modifications dans la base de données
    db.commit()
    db.refresh(category)

    # Retourner l'objet mis à jour sous forme de dictionnaire
    return category.as_dict()  # ou 
@router.route('/',strict_slashes=False, methods=['GET'])
def get_categories():
    try:
        db = next(get_db())
        skip = int(request.args.get('skip', 0))
        limit = int(request.args.get('limit', 10))
        categories = db.query(Category).offset(skip).limit(limit).all()
        categories_dict = [Category.category_to_dict(cat) for cat in categories]
        return jsonify(categories_dict)
    except Exception as e:
        return jsonify({"error": str(e)}), 500
def get_categorie_name(db: Session, categorie_name: str):
    return db.query(Category).filter(Category.category_name == categorie_name).first()
@router.get("/count")
def get_categorie_count():
    db = next(get_db())
    count = db.query(Category).count()
    print(count)
    return {"count": count}

@router.route("", methods=["POST"])
def create_categorie():
    db: Session = next(get_db())
    data = request.get_json()

    # Création de la catégorie
    new_category = Category(**data)
    db.add(new_category)
    db.commit()
    db.refresh(new_category)

    # Sérialisation manuelle (à remplacer par new_category.as_dict() si défini)
    return jsonify({
        "id": new_category.id,
        "category_name": new_category.category_name,
        "category_desc": new_category.category_desc,
        "is_active": new_category.is_active
    }), 201

@router.route('<string:categorie_name>', methods=['GET'])
def read_categorie_name(categorie_name):
    db = next(get_db())
    categorie = get_categorie_name(db, categorie_name)
    if categorie is None:
        return jsonify({"detail": "Category non trouvé"}), 404
    return jsonify({"id": categorie.id}), 200
@router.route('/id/<int:category_id>', methods=['GET'])
def read_category(category_id):
    db = next(get_db())
    category = get_category(db, category_id)
    if category is None:
        return jsonify({"detail": "Category non trouvé"}), 404
    
    # Conversion manuelle de l'objet SQLAlchemy en dict
    return jsonify({
        "id": category.id,
        "category_name": category.category_name,
        "category_desc": category.category_desc,
        "is_active": category.is_active
    })
# @router.get("/", response_model=list[categories.CategorieOut])
# def read_categories(skip: int = 0, limit: int = 10, db: Session = Depends(get_db)):
#     return categories.get_categories(db, skip, limit)
@router.get("/")
def read_categories_all():
    db = next(get_db())
    print("Toutes les catégories")
    return db.query(Category).all()  # Récupère toutes les catégories sans pagination


@router.route('/<int:categorie_id>', methods=['PUT'])
def update_categorie(categorie_id):
    db: Session = next(get_db())
    data = request.get_json()  # ← Récupère le body JSON

    category = db.query(Category).filter(Category.id == categorie_id).first()
    if not category:
        return jsonify({"detail": "Category non trouvé"}), 404

    # Mise à jour des champs
    for key, value in data.items():
        if hasattr(category, key):
            if key == "is_active":
                if value == "true":
                    value = 1
                elif value == "false":
                    value = 0
            setattr(category, key, value)

    db.commit()
    db.refresh(category)

    # Sérialiser l'objet Category pour la réponse
    return jsonify({
        "id": category.id,
        "category_name": category.category_name,
        "category_desc": category.category_desc,
        "is_active": category.is_active
    }), 200

@router.delete("/{categorie_id}")
def delete_categorie(categorie_id: int):
    db = next(get_db())
    deleted_categorie = categories.delete_categorie(db, categorie_id)
    if deleted_categorie is None:
        return jsonify({"detail": "Category non trouvé"}), 404
    return deleted_categorie