models.py

from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Boolean, Text
from sqlalchemy.orm import relationship
from database import Base
from datetime import datetime

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    company_name = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    clients = relationship("Client", back_populates="user", cascade="all, delete-orphan")
    employees = relationship("Employee", back_populates="user", cascade="all, delete-orphan")
    invoices = relationship("Invoice", back_populates="user", cascade="all, delete-orphan")
    bank_accounts = relationship("BankAccount", back_populates="user", cascade="all, delete-orphan")
    revenues = relationship("RevenueEntry", back_populates="user", cascade="all, delete-orphan")
    expenses = relationship("ExpenseEntry", back_populates="user", cascade="all, delete-orphan")
    payroll_records = relationship("Payroll", back_populates="user", cascade="all, delete-orphan")
    personal_expenses = relationship("PersonalExpense", back_populates="user", cascade="all, delete-orphan")

class Client(Base):
    __tablename__ = "clients"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    email = Column(String, index=True)
    phone = Column(String)
    company = Column(String)
    address = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    description = Column(Text, nullable=True)
    is_active = Column(Boolean, default=True)
    advance_payment = Column(Float, default=0.0)
    remaining_payment = Column(Float, default=0.0)
    joining_date = Column(DateTime, default=datetime.utcnow)
    
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), default=1)
    user = relationship("User", back_populates="clients")
    invoices = relationship("Invoice", back_populates="client", cascade="all, delete-orphan")
    revenues = relationship("RevenueEntry", back_populates="client")

class Employee(Base):
    __tablename__ = "employees"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    email = Column(String, index=True)
    phone = Column(String)
    role = Column(String)
    base_salary = Column(Float)
    is_active = Column(Boolean, default=True)
    joining_date = Column(DateTime, default=datetime.utcnow)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    bank_account_number = Column(String(255), nullable=True)
    bank_name = Column(String(255), nullable=True)
    account_title = Column(String(255), nullable=True)
    starting_salary = Column(Float, default=0.0)
    
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), default=1)
    user = relationship("User", back_populates="employees")
    payroll_records = relationship("Payroll", back_populates="employee", cascade="all, delete-orphan")
    increments = relationship("SalaryIncrement", back_populates="employee", cascade="all, delete-orphan")

class SalaryIncrement(Base):
    __tablename__ = "salary_increments"
    
    id = Column(Integer, primary_key=True, index=True)
    employee_id = Column(Integer, ForeignKey("employees.id", ondelete="CASCADE"))
    old_salary = Column(Float)
    new_salary = Column(Float)
    increment_amount = Column(Float)
    date = Column(DateTime, default=datetime.utcnow)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), default=1)
    
    employee = relationship("Employee", back_populates="increments")

class BankAccount(Base):
    __tablename__ = "bank_accounts"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    account_number = Column(String, unique=True)
    balance = Column(Float, default=0.0)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), default=1)
    user = relationship("User", back_populates="bank_accounts")
    transactions = relationship("Transaction", back_populates="bank_account", cascade="all, delete-orphan")
    revenues = relationship("RevenueEntry", back_populates="bank_account", cascade="all, delete-orphan")
    expenses = relationship("ExpenseEntry", back_populates="bank_account", cascade="all, delete-orphan")

class Transaction(Base):
    __tablename__ = "transactions"
    
    id = Column(Integer, primary_key=True, index=True)
    bank_account_id = Column(Integer, ForeignKey("bank_accounts.id", ondelete="CASCADE"))
    amount = Column(Float)
    transaction_type = Column(String)  # "DEPOSIT" or "WITHDRAWAL"
    category = Column(String)
    description = Column(Text)
    date = Column(DateTime, default=datetime.utcnow)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # Linked tracking keys
    invoice_id = Column(Integer, ForeignKey("invoices.id", ondelete="SET NULL"), nullable=True)
    payroll_id = Column(Integer, ForeignKey("payroll.id", ondelete="SET NULL"), nullable=True)
    expense_id = Column(Integer, ForeignKey("expenses.id", ondelete="SET NULL"), nullable=True)
    revenue_id = Column(Integer, ForeignKey("revenues.id", ondelete="SET NULL"), nullable=True)
    
    bank_account = relationship("BankAccount", back_populates="transactions")

class Invoice(Base):
    __tablename__ = "invoices"
    
    id = Column(Integer, primary_key=True, index=True)
    invoice_number = Column(String, unique=True, index=True)
    client_id = Column(Integer, ForeignKey("clients.id", ondelete="CASCADE"))
    client_name = Column(String)
    amount = Column(Float)
    status = Column(String, default="Unpaid")  # "Paid", "Unpaid", "Overdue"
    issue_date = Column(DateTime, default=datetime.utcnow)
    due_date = Column(DateTime)
    payment_date = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), default=1)
    user = relationship("User", back_populates="invoices")
    client = relationship("Client", back_populates="invoices")

class Payroll(Base):
    __tablename__ = "payroll"
    
    id = Column(Integer, primary_key=True, index=True)
    employee_id = Column(Integer, ForeignKey("employees.id", ondelete="CASCADE"))
    employee_name = Column(String)
    month = Column(String)
    year = Column(Integer)
    base_salary = Column(Float)
    bonus = Column(Float, default=0.0)
    deductions = Column(Float, default=0.0)
    net_salary = Column(Float)
    is_paid = Column(Boolean, default=False)
    payment_date = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), default=1)
    user = relationship("User", back_populates="payroll_records")
    employee = relationship("Employee", back_populates="payroll_records")

class RevenueEntry(Base):
    __tablename__ = "revenues"
    
    id = Column(Integer, primary_key=True, index=True)
    client_id = Column(Integer, ForeignKey("clients.id", ondelete="SET NULL"), nullable=True)
    bank_account_id = Column(Integer, ForeignKey("bank_accounts.id", ondelete="CASCADE"))
    amount = Column(Float)
    description = Column(Text)
    date = Column(DateTime, default=datetime.utcnow)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), default=1)
    user = relationship("User", back_populates="revenues")
    client = relationship("Client", back_populates="revenues")
    bank_account = relationship("BankAccount", back_populates="revenues")

class ExpenseEntry(Base):
    __tablename__ = "expenses"
    
    id = Column(Integer, primary_key=True, index=True)
    bank_account_id = Column(Integer, ForeignKey("bank_accounts.id", ondelete="CASCADE"))
    amount = Column(Float)
    category = Column(String)
    description = Column(Text)
    date = Column(DateTime, default=datetime.utcnow)
    created_at = Column(DateTime, default=datetime.utcnow)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), default=1)
    employee_id = Column(Integer, ForeignKey("employees.id", ondelete="SET NULL"), nullable=True)
    payroll_id = Column(Integer, ForeignKey("payroll.id", ondelete="SET NULL"), nullable=True)
    target_month = Column(String, nullable=True)
    target_year = Column(Integer, nullable=True)
    
    user = relationship("User", back_populates="expenses")
    bank_account = relationship("BankAccount", back_populates="expenses")

class SecurityConfig(Base):
    __tablename__ = "security_config"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), default=1)
    hashed_pin = Column(String, nullable=True)
    is_biometric_enabled = Column(Boolean, default=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class PersonalExpense(Base):
    __tablename__ = "personal_expenses"
    
    id = Column(Integer, primary_key=True, index=True)
    amount = Column(Float, nullable=False)
    category = Column(String, nullable=False)
    description = Column(Text, nullable=True)
    payment_method = Column(String, nullable=True, default="Cash")
    date = Column(DateTime, default=datetime.utcnow)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), default=1)
    user = relationship("User", back_populates="personal_expenses")