week 10 database2

from sqlalchemy import create_engine, Column, Integer, String, Text, ForeignKey, DateTime, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
engine_user = create_engine('sqlite:///user_database.db')  # 用户数据库
engine_product = create_engine('sqlite:///product_database.db')  # 商品数据库
SessionUser = sessionmaker(bind=engine_user)
session_user = SessionUser()
SessionProduct = sessionmaker(bind=engine_product)
session_product = SessionProduct()
Base = declarative_base()

# 定义订单表
class Order(Base):
    __tablename__ = 'orders'
    order_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.user_id'), nullable=False)
    product_id = Column(Integer, nullable=False)
    order_date = Column(DateTime, default=datetime.utcnow)
    quantity = Column(Integer, default=1)  # 新增订单数量字段
    total_price = Column(Float, nullable=False)  # 新增总价字段
    user = relationship("User", back_populates="orders")
# 在用户数据库中创建表
Base.metadata.create_all(engine_user)  

# 在database1的UserBrowsingHistory中添加用户浏览的内容
def add_browsing_history(user_id, product_id):
    new_history = UserBrowsingHistory(user_id=user_id, product_id=product_id)
    session_user.add(new_history)
    session_user.commit()

# 添加用户订单
def add_order(user_id, product_id, quantity=1, total_price=0):
    new_order = Order(user_id=user_id, product_id=product_id, quantity=quantity, total_price=total_price)
    session_user.add(new_order)
    session_user.commit()

# 定义商品表
class Product(Base):
    __tablename__ = 'products'
    product_id = Column(Integer, primary_key=True, autoincrement=True)
    product_name = Column(String, nullable=False)
    product_image = Column(String)
    product_description = Column(Text)
    category = Column(String)
    store = Column(String)
    click_count = Column(Integer, default=0)
    favorite_count = Column(Integer, default=0)
    price = Column(Float)
    positive_feedback = Column(Integer, default=0)
    negative_feedback = Column(Integer, default=0)
    positive_feedback_content = Column(Text)
    negative_feedback_content = Column(Text)
# 在商品数据库中创建表
Base.metadata.create_all(engine_product)

# 添加新商品
def add_product(name, image, description, category, store, price, positive_feedback=0, negative_feedback=0, positive_feedback_content=None, negative_feedback_content=None):
    new_product = Product(
        product_name=name,
        product_image=image,
        product_description=description,
        category=category,
        store=store,
        price=price,
        positive_feedback=positive_feedback,
        negative_feedback=negative_feedback,
        positive_feedback_content=positive_feedback_content,
        negative_feedback_content=negative_feedback_content
    )
    session_product.add(new_product)
    session_product.commit()

# 删除商品
def delete_product(product_id):
    product = session_product.query(Product).filter(Product.product_id == product_id).one()
    session_product.delete(product)
    session_product.commit()
    
# 增加商品点击量
def increment_click_count(product_id):
    product = session_product.query(Product).filter(Product.product_id == product_id).one()
    product.click_count += 1
    session_product.commit()

# 增加商品收藏量
def increment_favorite_count(product_id):
    product = session_product.query(Product).filter(Product.product_id == product_id).one()
    product.favorite_count += 1
    session_product.commit()  
   
# 控制面板
def menu():
    while True:
        print("\n菜单:")
        print("1. 添加用户")
        print("2. 删除用户")
        print("3. 添加收藏商品")
        print("4. 删除收藏商品")
        print("5. 添加浏览历史")
        print("6. 添加订单")
        print("7. 添加商品")
        print("8. 更新商品")
        print("9. 删除商品")
        print("10. 增加商品点击量")
        print("11. 增加商品收藏量")
        print("12. 增加商品好评")
        print("13. 增加商品差评")
        print("0. 退出")
        choice = input("请选择操作: ")

        if choice == '1':
            username = input("用户名: ")
            password = input("密码: ")
            address = input("地址: ")
            phone = input("手机号: ")
            add_user(username, password, address, phone)
        elif choice == '2':
            user_id = int(input("用户ID: "))
            delete_user(user_id)
        elif choice == '3':
            user_id = int(input("用户ID: "))
            product_id = int(input("商品ID: "))
            add_favorite(user_id, product_id)
        elif choice == '4':
            user_id = int(input("用户ID: "))
            product_id = int(input("商品ID: "))
            delete_favorite(user_id, product_id)
        elif choice == '5':
            user_id = int(input("用户ID: "))
            product_id = int(input("商品ID: "))
            add_browsing_history(user_id, product_id)
        elif choice == '6':
            user_id = int(input("用户ID: "))
            product_id = int(input("商品ID: "))
            quantity = int(input("数量: "))
            total_price = float(input("总价: "))
            add_order(user_id, product_id, quantity, total_price)