from fastapi import APIRouter, HTTPException, Depends
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, Float, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from typing import List, Optional
from pydantic import BaseModel
import pymysql

# Define SQLAlchemy models
Base = declarative_base()

class Zy(Base):
    __tablename__ = "zy"
    id_zy = Column(Integer, primary_key=True)
    lqzdf = Column(Float)
    yxmc = Column(String)
    zymc = Column(String)


# Database connection
SQLALCHEMY_DATABASE_URL = "mysql+pymysql://root:Duan17001266088@8.140.254.18/chat_report_edu"
engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
metadata = MetaData()

# Create tables
Base.metadata.create_all(bind=engine)

# Pydantic model for database query parameters
class ZyQuery(BaseModel):
    id_zy: Optional[int]
    # Add other fields if needed
    lqzdf: Optional[float]

# Dependency to get the database session
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()