database.py

import os
from sqlalchemy import create_engine, Column, Integer, String, Float, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from dotenv import load_dotenv

load_dotenv()

SQLALCHEMY_DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///./propguard.db")

if SQLALCHEMY_DATABASE_URL.startswith("sqlite"):
    engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})
else:
    engine = create_engine(SQLALCHEMY_DATABASE_URL)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


# --- 2. MULTI-TENANT B2B TABLES ---

class LawFirmDB(Base):
    __tablename__ = "law_firms"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True, index=True)

    # Relationships: A firm has many staff members and many clients
    staff_members = relationship("FirmUserDB", back_populates="firm", cascade="all, delete-orphan")
    clients = relationship("LandlordDB", back_populates="firm", cascade="all, delete-orphan")


class FirmUserDB(Base):
    __tablename__ = "firm_users"
    id = Column(Integer, primary_key=True, index=True)
    full_name = Column(String)
    username = Column(String, unique=True, index=True)  # Used for logging in
    password_hash = Column(String)  # NEVER store raw passwords!

    law_firm_id = Column(Integer, ForeignKey("law_firms.id"))
    firm = relationship("LawFirmDB", back_populates="staff_members")


# --- 3. PROPERTY MANAGEMENT TABLES ---

class LandlordDB(Base):
    __tablename__ = "landlords"  # These are the "Clients"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    commission_percentage = Column(Float, default=10.0)

    # NEW: Link the client to the specific Law Firm
    law_firm_id = Column(Integer, ForeignKey("law_firms.id"))
    firm = relationship("LawFirmDB", back_populates="clients")

    properties = relationship("PropertyDB", back_populates="owner", cascade="all, delete-orphan")


class PropertyDB(Base):
    __tablename__ = "properties"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    address = Column(String)
    caretaker_name = Column(String)
    number_of_flats = Column(Integer, default=1)
    property_type = Column(String, default="Residential")

    landlord_id = Column(Integer, ForeignKey("landlords.id"))
    owner = relationship("LandlordDB", back_populates="properties")
    tenants = relationship("TenantDB", back_populates="property_unit", cascade="all, delete-orphan")


class TenantDB(Base):
    __tablename__ = "tenants"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    phone_number = Column(String, default="")
    flat_number = Column(String)
    annual_rent = Column(Float)
    amount_paid = Column(Float)
    status = Column(String)
    property_id = Column(Integer, ForeignKey("properties.id"))
    property_unit = relationship("PropertyDB", back_populates="tenants")