"""
SQLAlchemy ORM models for TripTalley.
"""

from datetime import datetime
from decimal import Decimal
from typing import Optional
from sqlalchemy import (
    Column, Integer, String, DateTime, Date, Numeric, ForeignKey, Table, Enum, UniqueConstraint
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


# Association table for many-to-many relationships
user_group = Table(
    'user_group',
    Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id'), primary_key=True),
    Column('group_id', Integer, ForeignKey('groups.id'), primary_key=True)
)

user_trip = Table(
    'user_trip',
    Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id'), primary_key=True),
    Column('trip_id', Integer, ForeignKey('trips.id'), primary_key=True)
)


class User(Base):
    """
    User account model.
    
    Users can belong to multiple groups and trips.
    Each trip has a settlement currency preference.
    """
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True, nullable=False, index=True)
    hashed_password = Column(String(255), nullable=False)
    display_name = Column(String(255), nullable=False)
    home_currency = Column(String(3), default='USD')  # User's preferred currency
    
    # Timestamps
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, 
                        nullable=False)
    
    # Relationships
    groups = relationship('Group', secondary=user_group, back_populates='users')
    trips = relationship('Trip', secondary=user_trip, back_populates='users')
    payments_made = relationship('Payment', foreign_keys='Payment.from_user_id')
    payments_received = relationship('Payment', foreign_keys='Payment.to_user_id')
    expenses_paid = relationship('Expense', back_populates='payer')
    expense_participants = relationship('ExpenseParticipant', back_populates='user')
    
    def __repr__(self) -> str:
        return f"<User(id={self.id}, email='{self.email}', name='{self.display_name}')>"


class Group(Base):
    """
    Group model for organizing trips.
    
    Groups can have members and multiple trips.
    """
    __tablename__ = 'groups'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    description = Column(String(1000))
    
    # Timestamps
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, 
                        nullable=False)
    
    # Relationships
    users = relationship('User', secondary=user_group, back_populates='groups')
    trips = relationship('Trip', back_populates='group')
    
    def __repr__(self) -> str:
        return f"<Group(id={self.id}, name='{self.name}')>"


class Trip(Base):
    """
    Trip model for tracking expenses.
    
    Each trip has:
    - A settlement currency (e.g., the local currency of the destination)
    - A start/end date
    - Participants
    - Expenses
    """
    __tablename__ = 'trips'
    
    id = Column(Integer, primary_key=True)
    group_id = Column(Integer, ForeignKey('groups.id'))
    name = Column(String(255), nullable=False)
    description = Column(String(1000))
    
    # Dates
    start_date = Column(Date, nullable=False)
    end_date = Column(Date, nullable=False)
    
    # Settlement currency for this trip (e.g., EUR for a trip to France)
    settlement_currency = Column(String(3), default='USD')
    
    # Timestamps
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, 
                        nullable=False)
    
    # Relationships
    group = relationship('Group', back_populates='trips')
    users = relationship('User', secondary=user_trip, back_populates='trips')
    expenses = relationship('Expense', back_populates='trip')
    
    def __repr__(self) -> str:
        return f"<Trip(id={self.id}, name='{self.name}', currency='{self.settlement_currency}')>"


class Expense(Base):
    """
    Expense model for tracking individual expenses.
    
    Each expense has:
    - A payer (who paid initially)
    - A total amount in original currency
    - A date spent
    - Participants and their shares
    """
    __tablename__ = 'expenses'
    
    id = Column(Integer, primary_key=True)
    trip_id = Column(Integer, ForeignKey('trips.id'), nullable=False)
    payer_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    
    # Amount details (stored as integer minor units in the database)
    original_amount_minor = Column(Integer, nullable=False)  # e.g., 1000 = $10.00
    original_currency = Column(String(3), nullable=False)
    
    # Metadata
    spent_on = Column(Date, nullable=False)  # Date expense was incurred
    category = Column(String(100))  # e.g., "Food", "Transport", "Accommodation"
    description = Column(String(500))
    
    # Timestamps
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, 
                        nullable=False)
    
    # Relationships
    trip = relationship('Trip', back_populates='expenses')
    payer = relationship('User', back_populates='expenses_paid')
    participants = relationship('ExpenseParticipant', back_populates='expense', 
                               cascade='all, delete-orphan')
    
    def __repr__(self) -> str:
        return (f"<Expense(id={self.id}, trip_id={self.trip_id}, "
                f"amount={self.original_amount_minor}{self.original_currency})>")
    
    @property
    def amount_decimal(self) -> Decimal:
        """Get amount as Decimal for display."""
        from app.core.money import from_minor
        return from_minor(self.original_amount_minor, self.original_currency)


class ExpenseParticipant(Base):
    """
    Join table for expense participants and their shares.
    
    Each expense can have multiple participants, each with a specified share.
    """
    __tablename__ = 'expense_participants'
    
    expense_id = Column(Integer, ForeignKey('expenses.id'), primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    share_minor = Column(Integer, nullable=False)  # Share in original currency
    
    # Timestamps
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    
    # Relationships
    expense = relationship('Expense', back_populates='participants')
    user = relationship('User', back_populates='expense_participants')
    
    def __repr__(self) -> str:
        return (f"<ExpenseParticipant(expense_id={self.expense_id}, "
                f"user_id={self.user_id}, share={self.share_minor})>")


class FXRate(Base):
    """
    Cached FX rate for historical lookups.
    
    Stores rates by date, from_currency, to_currency to avoid repeated API calls.
    """
    __tablename__ = 'fx_rates'
    
    id = Column(Integer, primary_key=True)
    
    date = Column(Date, nullable=False, index=True)
    from_currency = Column(String(3), nullable=False)
    to_currency = Column(String(3), nullable=False)
    
    # Rate stored as integer (rate * 1,000,000) for precision
    rate_major = Column(Integer, nullable=False)  # Integer part
    rate_minor = Column(Integer, nullable=False)  # Fractional part (micros)
    
    # Metadata
    source = Column(String(50))  # e.g., "frankfurter"
    fetched_at = Column(DateTime, default=datetime.utcnow)
    
    __table_args__ = (
        UniqueConstraint('date', 'from_currency', 'to_currency', 
                        name='uq_fx_rate_lookup'),
    )
    
    def __repr__(self) -> str:
        return (f"<FXRate(date={self.date}, {self.from_currency}→{self.to_currency}="
                f"{self.rate_major}.{self.rate_minor:06d})>")
    
    @property
    def rate_decimal(self) -> Decimal:
        """Get rate as Decimal."""
        return Decimal(self.rate_major) + Decimal(self.rate_minor) / Decimal(1000000)


class Payment(Base):
    """
    Settlement payment record.
    
    Stores actual transfers made to settle trip balances.
    """
    __tablename__ = 'payments'
    
    id = Column(Integer, primary_key=True)
    trip_id = Column(Integer, ForeignKey('trips.id'), nullable=False)
    from_user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    to_user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    
    # Payment amount
    amount_minor = Column(Integer, nullable=False)
    currency = Column(String(3), nullable=False)
    
    # Metadata
    status = Column(String(20), default='pending')  # pending, completed, cancelled
    reference = Column(String(255))  # e.g., Venmo/Zelle reference
    
    # Timestamps
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    completed_at = Column(DateTime)
    
    # Relationships
    trip = relationship('Trip')
    from_user = relationship('User', foreign_keys=[from_user_id])
    to_user = relationship('User', foreign_keys=[to_user_id])
    
    def __repr__(self) -> str:
        return (f"<Payment(id={self.id}, trip_id={self.trip_id}, "
                f"{self.from_user_id}→{self.to_user_id}={self.amount_minor}{self.currency})>")


class TripInvite(Base):
    """
    Trip invitation with signed link.
    
    Enables secure invitation sharing via expiring links.
    """
    __tablename__ = 'trip_invites'
    
    id = Column(Integer, primary_key=True)
    trip_id = Column(Integer, ForeignKey('trips.id'), nullable=False)
    created_by_user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    
    # Invite token (UUID-based, signed)
    token = Column(String(36), unique=True, nullable=False)
    
    # Expiration
    expires_at = Column(DateTime, nullable=False)
    
    # Usage tracking
    accepted_at = Column(DateTime)
    accepted_by_user_id = Column(Integer, ForeignKey('users.id'))
    
    # Timestamps
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    
    # Relationships
    trip = relationship('Trip')
    created_by = relationship('User', foreign_keys=[created_by_user_id])
    accepted_by = relationship('User', foreign_keys=[accepted_by_user_id])
    
    def __repr__(self) -> str:
        return f"<TripInvite(id={self.id}, trip_id={self.trip_id}, token='{self.token}')>"