database.py

from sqlalchemy import create_engine, Column, Integer, String, Float, Boolean, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime

Base = declarative_base()

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    price = Column(Float, nullable=False)
    deposit = Column(Float, default=0)
    stock = Column(Integer, default=0)
    category = Column(String(50))
    is_returnable = Column(Boolean, default=True)
    image = Column(String(500), default="")
    created_at = Column(DateTime, default=datetime.now)

class Sale(Base):
    __tablename__ = 'sales'
    id = Column(Integer, primary_key=True)
    product_id = Column(Integer)
    product_name = Column(String(100))
    quantity = Column(Integer)
    price = Column(Float)
    total = Column(Float)
    sale_type = Column(String(20))
    customer_name = Column(String(100))
    is_return = Column(Boolean, default=False)
    date = Column(DateTime, default=datetime.now)

class Debt(Base):
    __tablename__ = 'debts'
    id = Column(Integer, primary_key=True)
    customer_name = Column(String(100))
    product_name = Column(String(100))
    amount = Column(Float)
    paid = Column(Float, default=0)
    remaining = Column(Float)
    due_date = Column(DateTime, default=lambda: datetime.now())
    status = Column(String(20), default="active")
    date = Column(DateTime, default=datetime.now)

class StockMovement(Base):
    __tablename__ = 'stock_movements'
    id = Column(Integer, primary_key=True)
    product_id = Column(Integer)
    product_name = Column(String(100))
    quantity = Column(Integer)
    movement_type = Column(String(10))
    reason = Column(String(50))
    date = Column(DateTime, default=datetime.now)

engine = create_engine('sqlite:///mendepos.db', connect_args={'check_same_thread': False})
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

# Add sample products
session = Session()
if session.query(Product).count() == 0:
    products = [
        Product(name="Coca Cola 500ml", price=1000, deposit=500, stock=100, category="Soda"),
        Product(name="Fanta Orange", price=1000, deposit=500, stock=80, category="Soda"),
        Product(name="Pampers Size 3", price=5000, deposit=0, stock=50, category="Baby"),
        Product(name="Unga wa Mahindi 1kg", price=1500, deposit=0, stock=200, category="Food"),
        Product(name="Sprite 500ml", price=1000, deposit=500, stock=60, category="Soda"),
        Product(name="Mchele 1kg", price=2500, deposit=0, stock=100, category="Food"),
    ]
    session.add_all(products)
    session.commit()
session.close()