from fastapi import FastAPI, HTTPException, Depends, status
from fastapi.middleware.cors import CORSMiddleware
from sqlalchemy import create_engine, Column, Integer, String, Date, DateTime, ForeignKey, DECIMAL, TIMESTAMP, text
from sqlalchemy.orm import declarative_base, sessionmaker, Session, relationship
from pydantic import BaseModel, ConfigDict
from typing import List, Optional
from datetime import date, datetime

# Configuration de la base de données
DATABASE_URL = "mysql+pymysql://root:root@localhost:3306/Comptapro"
engine = create_engine(DATABASE_URL, echo=True)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

# Modèles SQLAlchemy
class User(Base):
    __tablename__ = "User"
    user_id = Column(Integer, primary_key=True, autoincrement=True)
    nom = Column(String(25), nullable=False)
    email = Column(String(100), nullable=False)
    mot_de_passe = Column(String(20), nullable=False)
    telephone = Column(Integer, nullable=False)
    
    # Relations
    pmes = relationship("PME", back_populates="user")
    entrees = relationship("Entree", back_populates="user")
    depenses = relationship("Depense", back_populates="user")
    bilans = relationship("Bilan", back_populates="user")

class PME(Base):
    __tablename__ = "PME"
    pme_id = Column(Integer, primary_key=True, autoincrement=True)
    nom = Column(String(30), nullable=False)
    ville = Column(String(30), nullable=False)
    user_id = Column(Integer, ForeignKey("User.user_id"))
    num_registre = Column(Integer, nullable=False)
    email = Column(String(30), nullable=False)
    tel = Column(Integer, nullable=False)
    type_dabonnement = Column(String(30), nullable=False)
    
    # Relations
    user = relationship("User", back_populates="pmes")
    entrees = relationship("Entree", back_populates="pme")
    depenses = relationship("Depense", back_populates="pme")
    bilans = relationship("Bilan", back_populates="pme")

class Entree(Base):
    __tablename__ = "ENTREE"
    entree_id = Column(Integer, primary_key=True, autoincrement=True)
    montant_entree = Column(String(250), nullable=False)
    date = Column(DateTime, nullable=False)
    User_id = Column(Integer, ForeignKey("User.user_id"))
    categorie_entree = Column(String(250), nullable=False)
    update_date = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
    statut_entree = Column(String(20), nullable=False)
    description_entree = Column(String(250), nullable=False)
    pme_id_entree = Column(Integer, ForeignKey("PME.pme_id"))
    
    # Relations
    user = relationship("User", back_populates="entrees")
    pme = relationship("PME", back_populates="entrees")

class Depense(Base):
    __tablename__ = "Depense_ID"
    depense_id_depense = Column(Integer, primary_key=True, autoincrement=True)
    montant_depense = Column(Integer, nullable=False)
    create_date = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    user_id_depense = Column(Integer, ForeignKey("User.user_id"))
    update_date = Column(TIMESTAMP, nullable=False, server_default=text("'0000-00-00 00:00:00'"))
    categorie_depense = Column(String(250), nullable=False)
    statut_depense = Column(String(20), nullable=False)
    description_depense = Column(String(250), nullable=False)
    pme_id_depense = Column(Integer, ForeignKey("PME.pme_id"))
    
    # Relations
    user = relationship("User", back_populates="depenses")
    pme = relationship("PME", back_populates="depenses")

class Bilan(Base):
    __tablename__ = "BILAN"
    bilan_id = Column(Integer, primary_key=True, autoincrement=True)
    create_date = Column(Date, nullable=False)
    user_id = Column(Integer, ForeignKey("User.user_id"))
    pme_id = Column(Integer, ForeignKey("PME.pme_id"))
    Periode = Column(Integer, nullable=False)  # YEAR(4)
    total_entrees = Column(Integer, nullable=False)
    total_depenses = Column(Integer, nullable=False)
    
    # Relations
    user = relationship("User", back_populates="bilans")
    pme = relationship("PME", back_populates="bilans")

# Schémas Pydantic
class UserBase(BaseModel):
    nom: str
    email: str
    mot_de_passe: str
    telephone: int

class UserCreate(UserBase):
    pass

class UserResponse(UserBase):
    user_id: int
    model_config = ConfigDict(from_attributes=True)

class PMEBase(BaseModel):
    nom: str
    ville: str
    user_id: int
    num_registre: int
    email: str
    tel: int
    type_dabonnement: str

class PMECreate(PMEBase):
    pass

class PMEResponse(PMEBase):
    pme_id: int
    model_config = ConfigDict(from_attributes=True)

class EntreeBase(BaseModel):
    montant_entree: str
    date: datetime
    User_id: int
    categorie_entree: str
    statut_entree: str
    description_entree: str
    pme_id_entree: int

class EntreeCreate(EntreeBase):
    pass

class EntreeResponse(EntreeBase):
    entree_id: int
    update_date: datetime
    model_config = ConfigDict(from_attributes=True)

class DepenseBase(BaseModel):
    montant_depense: int
    user_id_depense: int
    categorie_depense: str
    statut_depense: str
    description_depense: str
    pme_id_depense: int

class DepenseCreate(DepenseBase):
    pass

class DepenseResponse(DepenseBase):
    depense_id_depense: int
    create_date: datetime
    update_date: datetime
    model_config = ConfigDict(from_attributes=True)

class BilanBase(BaseModel):
    create_date: date
    user_id: int
    pme_id: int
    Periode: int
    total_entrees: int
    total_depenses: int

class BilanCreate(BilanBase):
    pass

class BilanResponse(BilanBase):
    bilan_id: int
    model_config = ConfigDict(from_attributes=True)

# Création de l'application FastAPI
app = FastAPI(title="API Comptapro", version="1.0.0")

# Ajout CORS
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# Dépendance pour obtenir la session DB
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# ==================== ROUTES USERS ====================
@app.get("/users", response_model=List[UserResponse])
def get_users(db: Session = Depends(get_db)):
    return db.query(User).all()

@app.get("/users/{user_id}", response_model=UserResponse)
def get_user(user_id: int, db: Session = Depends(get_db)):
    user = db.query(User).filter(User.user_id == user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")
    return user

@app.post("/users", response_model=UserResponse, status_code=201)
def create_user(user: UserCreate, db: Session = Depends(get_db)):
    db_user = User(**user.model_dump())
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

@app.put("/users/{user_id}", response_model=UserResponse)
def update_user(user_id: int, user: UserCreate, db: Session = Depends(get_db)):
    db_user = db.query(User).filter(User.user_id == user_id).first()
    if not db_user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")
    
    for key, value in user.model_dump().items():
        setattr(db_user, key, value)
    
    db.commit()
    db.refresh(db_user)
    return db_user

@app.delete("/users/{user_id}", status_code=204)
def delete_user(user_id: int, db: Session = Depends(get_db)):
    db_user = db.query(User).filter(User.user_id == user_id).first()
    if not db_user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")
    
    db.delete(db_user)
    db.commit()
    return None

# ==================== ROUTES PME ====================
@app.get("/pmes", response_model=List[PMEResponse])
def get_pmes(db: Session = Depends(get_db)):
    return db.query(PME).all()

@app.get("/pmes/{pme_id}", response_model=PMEResponse)
def get_pme(pme_id: int, db: Session = Depends(get_db)):
    pme = db.query(PME).filter(PME.pme_id == pme_id).first()
    if not pme:
        raise HTTPException(status_code=404, detail="PME non trouvée")
    return pme

@app.get("/pme/reg/{num_registre}", response_model=PMEResponse)
def get_pme_by_registre(num_registre: int, db: Session = Depends(get_db)):
    pme = db.query(PME).filter(PME.num_registre == num_registre).first()
    if not pme:
        raise HTTPException(status_code=404, detail="PME non trouvée")
    return pme

@app.post("/pmes/{pme_id}", response_model=PMEResponse, status_code=201)
def create_pme(pme: PMECreate, db: Session = Depends(get_db)):
    # Vérifier que l'utilisateur existe
    user = db.query(User).filter(User.user_id == pme.user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")
    
    db_pme = PME(**pme.model_dump())
    db.add(db_pme)
    db.commit()
    db.refresh(db_pme)
    return db_pme

@app.put("/pmes/{pme_id}", response_model=PMEResponse)
def update_pme(pme_id: int, pme: PMECreate, db: Session = Depends(get_db)):
    db_pme = db.query(PME).filter(PME.pme_id == pme_id).first()
    if not db_pme:
        raise HTTPException(status_code=404, detail="PME non trouvée")
    
    for key, value in pme.model_dump().items():
        setattr(db_pme, key, value)
    
    db.commit()
    db.refresh(db_pme)
    return db_pme

@app.delete("/pmes/{pme_id}", status_code=204)
def delete_pme(pme_id: int, db: Session = Depends(get_db)):
    db_pme = db.query(PME).filter(PME.pme_id == pme_id).first()
    if not db_pme:
        raise HTTPException(status_code=404, detail="PME non trouvée")
    
    db.delete(db_pme)
    db.commit()
    return None

# ==================== ROUTES ENTREES ====================
@app.get("/entrees", response_model=List[EntreeResponse])
def get_entrees(db: Session = Depends(get_db)):
    return db.query(Entree).all()

@app.get("/entrees/{entree_id}", response_model=EntreeResponse)
def get_entree(entree_id: int, db: Session = Depends(get_db)):
    entree = db.query(Entree).filter(Entree.entree_id == entree_id).first()
    if not entree:
        raise HTTPException(status_code=404, detail="Entrée non trouvée")
    return entree

@app.post("/entrees", response_model=EntreeResponse, status_code=201)
def create_entree(entree: EntreeCreate, db: Session = Depends(get_db)):
    # Vérifier que l'utilisateur et la PME existent
    user = db.query(User).filter(User.user_id == entree.User_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")
    
    pme = db.query(PME).filter(PME.pme_id == entree.pme_id_entree).first()
    if not pme:
        raise HTTPException(status_code=404, detail="PME non trouvée")
    
    db_entree = Entree(**entree.model_dump())
    db.add(db_entree)
    db.commit()
    db.refresh(db_entree)
    return db_entree

@app.put("/entrees/{entree_id}", response_model=EntreeResponse)
def update_entree(entree_id: int, entree: EntreeCreate, db: Session = Depends(get_db)):
    db_entree = db.query(Entree).filter(Entree.entree_id == entree_id).first()
    if not db_entree:
        raise HTTPException(status_code=404, detail="Entrée non trouvée")
    
    for key, value in entree.model_dump().items():
        setattr(db_entree, key, value)
    
    db.commit()
    db.refresh(db_entree)
    return db_entree

@app.delete("/entrees/{entree_id}", status_code=204)
def delete_entree(entree_id: int, db: Session = Depends(get_db)):
    db_entree = db.query(Entree).filter(Entree.entree_id == entree_id).first()
    if not db_entree:
        raise HTTPException(status_code=404, detail="Entrée non trouvée")
    
    db.delete(db_entree)
    db.commit()
    return None

# ==================== ROUTES DEPENSES ====================
@app.get("/depenses", response_model=List[DepenseResponse])
def get_depenses(db: Session = Depends(get_db)):
    return db.query(Depense).all()

@app.get("/depenses/{depense_id}", response_model=DepenseResponse)
def get_depense(depense_id: int, db: Session = Depends(get_db)):
    depense = db.query(Depense).filter(Depense.depense_id_depense == depense_id).first()
    if not depense:
        raise HTTPException(status_code=404, detail="Dépense non trouvée")
    return depense

@app.post("/depenses", response_model=DepenseResponse, status_code=201)
def create_depense(depense: DepenseCreate, db: Session = Depends(get_db)):
    # Vérifier que l'utilisateur et la PME existent
    user = db.query(User).filter(User.user_id == depense.user_id_depense).first()
    if not user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")
    
    pme = db.query(PME).filter(PME.pme_id == depense.pme_id_depense).first()
    if not pme:
        raise HTTPException(status_code=404, detail="PME non trouvée")
    
    db_depense = Depense(**depense.model_dump())
    db.add(db_depense)
    db.commit()
    db.refresh(db_depense)
    return db_depense

@app.put("/depenses/{depense_id}", response_model=DepenseResponse)
def update_depense(depense_id: int, depense: DepenseCreate, db: Session = Depends(get_db)):
    db_depense = db.query(Depense).filter(Depense.depense_id_depense == depense_id).first()
    if not db_depense:
        raise HTTPException(status_code=404, detail="Dépense non trouvée")
    
    for key, value in depense.model_dump().items():
        setattr(db_depense, key, value)
    
    db.commit()
    db.refresh(db_depense)
    return db_depense

@app.delete("/depenses/{depense_id}", status_code=204)
def delete_depense(depense_id: int, db: Session = Depends(get_db)):
    db_depense = db.query(Depense).filter(Depense.depense_id_depense == depense_id).first()
    if not db_depense:
        raise HTTPException(status_code=404, detail="Dépense non trouvée")
    
    db.delete(db_depense)
    db.commit()
    return None

# ==================== ROUTES BILANS ====================
@app.get("/bilans", response_model=List[BilanResponse])
def get_bilans(db: Session = Depends(get_db)):
    return db.query(Bilan).all()

@app.get("/bilans/{bilan_id}", response_model=BilanResponse)
def get_bilan(bilan_id: int, db: Session = Depends(get_db)):
    bilan = db.query(Bilan).filter(Bilan.bilan_id == bilan_id).first()
    if not bilan:
        raise HTTPException(status_code=404, detail="Bilan non trouvé")
    return bilan

@app.post("/bilans", response_model=BilanResponse, status_code=201)
def create_bilan(bilan: BilanCreate, db: Session = Depends(get_db)):
    # Vérifier que l'utilisateur et la PME existent
    user = db.query(User).filter(User.user_id == bilan.user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")
    
    pme = db.query(PME).filter(PME.pme_id == bilan.pme_id).first()
    if not pme:
        raise HTTPException(status_code=404, detail="PME non trouvée")
    
    db_bilan = Bilan(**bilan.model_dump())
    db.add(db_bilan)
    db.commit()
    db.refresh(db_bilan)
    return db_bilan

@app.put("/bilans/{bilan_id}", response_model=BilanResponse)
def update_bilan(bilan_id: int, bilan: BilanCreate, db: Session = Depends(get_db)):
    db_bilan = db.query(Bilan).filter(Bilan.bilan_id == bilan_id).first()
    if not db_bilan:
        raise HTTPException(status_code=404, detail="Bilan non trouvé")
    
    for key, value in bilan.model_dump().items():
        setattr(db_bilan, key, value)
    
    db.commit()
    db.refresh(db_bilan)
    return db_bilan

@app.delete("/bilans/{bilan_id}", status_code=204)
def delete_bilan(bilan_id: int, db: Session = Depends(get_db)):
    db_bilan = db.query(Bilan).filter(Bilan.bilan_id == bilan_id).first()
    if not db_bilan:
        raise HTTPException(status_code=404, detail="Bilan non trouvé")
    
    db.delete(db_bilan)
    db.commit()
    return None

# ==================== ROUTES SPÉCIALES ====================
@app.get("/users/{user_id}/pmes", response_model=List[PMEResponse])
def get_user_pmes(user_id: int, db: Session = Depends(get_db)):
    pmes = db.query(PME).filter(PME.user_id == user_id).all()
    return pmes

@app.get("/pmes/{pme_id}/entrees", response_model=List[EntreeResponse])
def get_pme_entrees(pme_id: int, db: Session = Depends(get_db)):
    entrees = db.query(Entree).filter(Entree.pme_id_entree == pme_id).all()
    return entrees

@app.get("/pmes/{pme_id}/depenses", response_model=List[DepenseResponse])
def get_pme_depenses(pme_id: int, db: Session = Depends(get_db)):
    depenses = db.query(Depense).filter(Depense.pme_id_depense == pme_id).all()
    return depenses

@app.get("/pmes/{pme_id}/bilans", response_model=List[BilanResponse])
def get_pme_bilans(pme_id: int, db: Session = Depends(get_db)):
    bilans = db.query(Bilan).filter(Bilan.pme_id == pme_id).all()
    return bilans

@app.get("/statistiques/pme/{pme_id}/annee/{annee}")
def get_statistiques_pme(pme_id: int, annee: int, db: Session = Depends(get_db)):
    # Calculer le total des entrées pour l'année
    total_entrees = db.query(Entree).filter(
        Entree.pme_id_entree == pme_id,
        Entree.date >= datetime(annee, 1, 1),
        Entree.date <= datetime(annee, 12, 31)
    ).all()
    
    total_entrees_sum = sum(float(e.montant_entree) for e in total_entrees)
    
    # Calculer le total des dépenses pour l'année
    total_depenses = db.query(Depense).filter(
        Depense.pme_id_depense == pme_id,
        Depense.create_date >= datetime(annee, 1, 1),
        Depense.create_date <= datetime(annee, 12, 31)
    ).all()
    
    total_depenses_sum = sum(d.montant_depense for d in total_depenses)
    
    return {
        "pme_id": pme_id,
        "annee": annee,
        "total_entrees": total_entrees_sum,
        "total_depenses": total_depenses_sum,
        "benefice": total_entrees_sum - total_depenses_sum
    }

# Route racine
@app.get("/")
def root():
    return {
        "message": "API Comptapro - Gestion des PME",
        "version": "1.0.0",
        "endpoints": {
            "users": "/users",
            "pmes": "/pmes",
            "entrees": "/entrees",
            "depenses": "/depenses",
            "bilans": "/bilans"
        }
    }

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="127.0.0.1", port=8000)