app/db/models.py

from sqlalchemy import Column, Integer, String, Boolean, DateTime, Float, ForeignKey, ARRAY, JSON, Table, Enum
from sqlalchemy.orm import relationship, mapped_column, Mapped
from sqlalchemy.dialects.postgresql import JSONB
from datetime import datetime
from typing import List, Optional
from .database import Base, async_engine
import enum

# Association tables for many-to-many relationships
user_roles = Table(
    'user_roles',
    Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('role_id', Integer, ForeignKey('roles.id'))
)

# Role model
class Role(Base):
    __tablename__ = "roles"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, unique=True, index=True)
    description: Mapped[str] = mapped_column(String)
    permissions: Mapped[List[str]] = mapped_column(ARRAY(String), default=list)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    users = relationship("User", secondary=user_roles, back_populates="roles")

# Branch model
class Branch(Base):
    __tablename__ = "branches"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, unique=True, index=True)
    address: Mapped[str] = mapped_column(String)
    phone: Mapped[str] = mapped_column(String)
    email: Mapped[str] = mapped_column(String)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    users = relationship("User", back_populates="branch")
    products = relationship("Product", back_populates="branch")
    orders = relationship("Order", back_populates="branch")
    staff_activities = relationship("StaffActivity", back_populates="branch")
    staff_metrics = relationship("PerformanceMetric", back_populates="branch")

async def ensure_tables():
    """Ensure all tables exist in the database"""
    async with async_engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String, unique=True, index=True)
    username: Mapped[str] = mapped_column(String, unique=True, index=True)
    full_name: Mapped[str] = mapped_column(String)
    hashed_password: Mapped[str] = mapped_column(String)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    is_superuser: Mapped[bool] = mapped_column(Boolean, default=False)
    branch_id: Mapped[Optional[int]] = mapped_column(ForeignKey("branches.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    # Update roles relationship to use many-to-many
    roles = relationship("Role", secondary=user_roles, back_populates="users")
    
    # Other relationships
    branch = relationship("Branch", back_populates="users")
    products = relationship("Product", back_populates="seller")
    orders = relationship("Order", back_populates="customer")
    notifications = relationship("Notification", back_populates="user")
    sessions = relationship("Session", back_populates="user", cascade="all, delete-orphan")
    activities = relationship("StaffActivity", back_populates="user")
    performance_metrics = relationship("PerformanceMetric", back_populates="user")

class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, index=True)
    description: Mapped[str] = mapped_column(String)
    price: Mapped[float] = mapped_column(Float)
    category: Mapped[str] = mapped_column(String, index=True)
    inventory_count: Mapped[int] = mapped_column(Integer)
    seller_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"))
    brand_id: Mapped[Optional[int]] = mapped_column(ForeignKey("brands.id"), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, 
        default=datetime.utcnow, 
        onupdate=datetime.utcnow
    )

    # Relationships
    seller = relationship("User", back_populates="products")
    branch = relationship("Branch", back_populates="products")
    brand = relationship("Brand", back_populates="products")
    order_items = relationship("OrderItem", back_populates="product")

class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"))
    total_amount: Mapped[float] = mapped_column(Float)
    status: Mapped[str] = mapped_column(String)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, 
        default=datetime.utcnow, 
        onupdate=datetime.utcnow
    )

    # Relationships
    customer = relationship("User", back_populates="orders")
    branch = relationship("Branch", back_populates="orders")
    items = relationship("OrderItem", back_populates="order", cascade="all, delete-orphan")

class OrderItem(Base):
    __tablename__ = "order_items"

    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
    product_id: Mapped[int] = mapped_column(ForeignKey("products.id"))
    quantity: Mapped[int] = mapped_column(Integer)
    price: Mapped[float] = mapped_column(Float)

    # Relationships
    order = relationship("Order", back_populates="items")
    product = relationship("Product", back_populates="order_items")

class Notification(Base):
    __tablename__ = "notifications"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    title: Mapped[str] = mapped_column(String)
    message: Mapped[str] = mapped_column(String)
    type: Mapped[str] = mapped_column(String)
    data: Mapped[Optional[dict]] = mapped_column(JSONB)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    read: Mapped[bool] = mapped_column(Boolean, default=False)

    # Relationship
    user = relationship("User", back_populates="notifications")

class Event(Base):
    __tablename__ = "events"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    title: Mapped[str] = mapped_column(String)
    description: Mapped[str] = mapped_column(String)
    start_time: Mapped[datetime] = mapped_column(DateTime)
    end_time: Mapped[datetime] = mapped_column(DateTime)
    attendees: Mapped[List[str]] = mapped_column(ARRAY(String), default=list)
    is_all_day: Mapped[bool] = mapped_column(Boolean, default=False)
    reminder_minutes: Mapped[int] = mapped_column(Integer)
    status: Mapped[str] = mapped_column(String)
    attendee_responses: Mapped[dict] = mapped_column(JSONB, default=dict)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, 
        default=datetime.utcnow, 
        onupdate=datetime.utcnow
    )
    # Fields for recurring events
    is_recurring: Mapped[bool] = mapped_column(Boolean, default=False)
    recurrence_pattern: Mapped[Optional[str]] = mapped_column(String)
    recurrence_group: Mapped[Optional[str]] = mapped_column(String)
    recurrence_end_date: Mapped[Optional[datetime]] = mapped_column(DateTime)
    parent_event_id: Mapped[Optional[int]] = mapped_column(Integer)
    sequence_number: Mapped[Optional[int]] = mapped_column(Integer)
    reminder_sent: Mapped[bool] = mapped_column(Boolean, default=False)

    # Relationship
    user = relationship("User")

class Session(Base):
    __tablename__ = "sessions"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    session_token: Mapped[str] = mapped_column(String, unique=True, index=True)
    last_activity: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    expires_at: Mapped[datetime] = mapped_column(DateTime)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    iteration_active: Mapped[bool] = mapped_column(Boolean, default=True)

    # Relationship
    user = relationship("User", back_populates="sessions")

class ActivityType(str, enum.Enum):
    LOGIN = "login"
    LOGOUT = "logout"
    SALE = "sale"
    VOID = "void"
    REFUND = "refund"
    INVENTORY = "inventory"
    CUSTOMER_SERVICE = "customer_service"

class StaffActivity(Base):
    __tablename__ = "staff_activities"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"))
    activity_type: Mapped[str] = mapped_column(String)
    details: Mapped[dict] = mapped_column(JSON)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    duration: Mapped[Optional[float]] = mapped_column(Float, nullable=True)  # Duration in minutes if applicable
    performance_score: Mapped[Optional[float]] = mapped_column(Float, nullable=True)  # Score based on activity type

    # Relationships
    user = relationship("User", back_populates="activities")
    branch = relationship("Branch", back_populates="staff_activities")

class PerformanceMetric(Base):
    __tablename__ = "performance_metrics"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"))
    metric_date: Mapped[datetime] = mapped_column(DateTime)
    total_sales: Mapped[float] = mapped_column(Float, default=0)
    transaction_count: Mapped[int] = mapped_column(Integer, default=0)
    average_transaction_value: Mapped[float] = mapped_column(Float, default=0)
    void_count: Mapped[int] = mapped_column(Integer, default=0)
    customer_interaction_count: Mapped[int] = mapped_column(Integer, default=0)
    login_time: Mapped[float] = mapped_column(Float, default=0)  # Total minutes logged in
    efficiency_score: Mapped[float] = mapped_column(Float, default=0)  # Calculated score based on metrics

    # Relationships
    user = relationship("User", back_populates="performance_metrics")
    branch = relationship("Branch", back_populates="staff_metrics")

class Brand(Base):
    __tablename__ = "brands"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, unique=True, index=True)
    category: Mapped[str] = mapped_column(String)
    established_date: Mapped[datetime] = mapped_column(DateTime)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    products = relationship("Product", back_populates="brand")