SUST/migrations/sqlalchemy_models.py

from sqlalchemy import Column, String, Integer, DateTime, Text, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()

# OPTION 1: Correct relationship setup
class MemberStates(Base):
    __tablename__ = 'member_states'
    
    state_code = Column(String(1), primary_key=True)
    state_name = Column(String(20), nullable=False)
    description = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # Backref will create 'members' attribute on MemberStates
    # members = relationship('Members', back_populates='current_state')

class Members(Base):
    __tablename__ = 'members'
    
    id = Column(Integer, primary_key=True)  # Assuming 'id' is your primary key
    # ... other member fields ...
    
    # Add foreign key constraint
    current_state_code = Column(String(1), ForeignKey('member_states.state_code'), index=True)
    current_state_changed_at = Column(DateTime)
    
    # Correct relationship definition
    current_state = relationship('MemberStates', back_populates='members')

# Update MemberStates to complete the bidirectional relationship
MemberStates.members = relationship('Members', back_populates='current_state')

class MemberStateLog(Base):
    __tablename__ = 'member_state_log'
    
    log_id = Column(Integer, primary_key=True)
    member_id = Column(Integer, ForeignKey('members.id'), nullable=False)
    state_code = Column(String(1), ForeignKey('member_states.state_code'), nullable=False)
    changed_at = Column(DateTime, default=datetime.utcnow)
    changed_by = Column(Integer)  # Optional: user ID who made the change
    reason = Column(Text)
    
    # Relationships
    member = relationship('Members', backref='state_history')
    state = relationship('MemberStates')

# OPTION 2: Alternative approach with primaryjoin (if you can't add foreign key)
class MembersAlternative(Base):
    __tablename__ = 'members'
    
    id = Column(Integer, primary_key=True)
    current_state_code = Column(String(1), index=True)  # No foreign key
    current_state_changed_at = Column(DateTime)
    
    # Use primaryjoin to specify the join condition
    current_state = relationship(
        'MemberStates',
        primaryjoin='Members.current_state_code == MemberStates.state_code',
        foreign_keys=[current_state_code]
    )

# USAGE EXAMPLES:

# Example 1: Query members with their current state
def get_members_with_state():
    from sqlalchemy.orm import sessionmaker
    Session = sessionmaker()
    session = Session()
    
    members = session.query(Members).join(Members.current_state).all()
    
    for member in members:
        print(f"Member: {member.name}")
        print(f"State: {member.current_state.state_name}")
        print(f"Description: {member.current_state.description}")
    
    session.close()

# Example 2: Get member state name directly
def get_member_state_name(member):
    if member.current_state:
        return member.current_state.state_name
    return "Unknown"

# Example 3: Filter members by state
def get_active_members():
    from sqlalchemy.orm import sessionmaker
    Session = sessionmaker()
    session = Session()
    
    active_members = session.query(Members).filter(
        Members.current_state_code == 'A'
    ).all()
    
    session.close()
    return active_members

# Example 4: Count members by state
def count_members_by_state():
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy import func
    Session = sessionmaker()
    session = Session()
    
    counts = session.query(
        MemberStates.state_name,
        func.count(Members.id).label('count')
    ).outerjoin(Members).group_by(
        MemberStates.state_code, 
        MemberStates.state_name
    ).all()
    
    session.close()
    return counts

# Example 5: Update member state (with logging)
def change_member_state(session, member_id, new_state_code, changed_by=None, reason=None):
    # Get the member
    member = session.query(Members).get(member_id)
    if not member:
        raise ValueError(f"Member {member_id} not found")
    
    # Validate state exists
    state = session.query(MemberStates).get(new_state_code)
    if not state:
        raise ValueError(f"Invalid state code: {new_state_code}")
    
    # Log the state change
    log_entry = MemberStateLog(
        member_id=member_id,
        state_code=new_state_code,
        changed_by=changed_by,
        reason=reason
    )
    session.add(log_entry)
    
    # Update member's current state
    member.current_state_code = new_state_code
    member.current_state_changed_at = datetime.utcnow()
    
    session.commit()

# Example 6: Serialization for API responses
def serialize_member_with_state(member):
    return {
        'id': member.id,
        'name': member.name,  # Assuming you have a name field
        'current_state_code': member.current_state_code,
        'current_state_name': member.current_state.state_name if member.current_state else None,
        'current_state_description': member.current_state.description if member.current_state else None,
        'current_state_changed_at': member.current_state_changed_at.isoformat() if member.current_state_changed_at else None
    }

# Example 7: Query with eager loading to avoid N+1 queries
def get_all_members_with_states_optimized():
    from sqlalchemy.orm import sessionmaker, joinedload
    Session = sessionmaker()
    session = Session()
    
    members = session.query(Members).options(
        joinedload(Members.current_state)
    ).all()
    
    # Now you can access member.current_state.state_name without additional queries
    result = [serialize_member_with_state(member) for member in members]
    
    session.close()
    return result

# Example 8: Custom property for easier access
class MembersWithProperty(Base):
    __tablename__ = 'members'
    
    id = Column(Integer, primary_key=True)
    current_state_code = Column(String(1), ForeignKey('member_states.state_code'), index=True)
    current_state_changed_at = Column(DateTime)
    
    current_state = relationship('MemberStates')
    
    @property
    def state_name(self):
        return self.current_state.state_name if self.current_state else "Unknown"
    
    @property
    def is_active(self):
        return self.current_state_code == 'A'
    
    @property
    def is_inactive(self):
        return self.current_state_code == 'I'
    
    @property
    def is_deactivated(self):
        return self.current_state_code == 'D'