app/models/lyrics.py

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

lyric_groups = Table(
    "lyric_groups",
    Base.metadata,
    Column("lyric_id", Integer, ForeignKey("lyrics.id", ondelete="CASCADE"), primary_key=True),
    Column("group_id", Integer, ForeignKey("groups.id", ondelete="CASCADE"), primary_key=True),
)

task_groups = Table(
    "task_groups",
    Base.metadata,
    Column("task_id", Integer, ForeignKey("tasks.id", ondelete="CASCADE"), primary_key=True),
    Column("group_id", Integer, ForeignKey("groups.id", ondelete="CASCADE"), primary_key=True),
)

class Group(Base):
    __tablename__ = "groups"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    codigo = Column(String, unique=True, index=True, nullable=True)
    foto_base64 = Column(String, nullable=True)
    requiere_aprobacion = Column(Boolean, default=False)
    is_suspended = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    members = relationship("UserGroup", back_populates="group")
    lyrics = relationship("Lyric", back_populates="group")
    repertorios = relationship("Repertorio", back_populates="group")
    lyrics_shared = relationship("Lyric", secondary=lyric_groups, back_populates="groups_shared")
    tasks_shared = relationship("Task", secondary=task_groups, back_populates="groups_shared")

class GroupRol(Base):
    """Roles personalizados y granulares del grupo de alabanza (RBAC)"""
    __tablename__ = "group_roles"

    id = Column(Integer, primary_key=True, index=True)
    grupo_id = Column(Integer, ForeignKey("groups.id", ondelete="CASCADE"))
    nombre = Column(String)  # Director, Pianista, etc.
    descripcion = Column(String, nullable=True)
    permisos = Column(String)  # JSON string con los 8 booleanos
    is_system = Column(Boolean, default=False)

    grupo = relationship("Group")

class UserGroup(Base):
    __tablename__ = "user_groups"

    user_id = Column(Integer, ForeignKey("users.id"), primary_key=True)
    group_id = Column(Integer, ForeignKey("groups.id"), primary_key=True)
    role = Column(String, default="member") # admin, member
    estado = Column(String, default="activo") # activo, pendiente, expulsado
    joined_at = Column(DateTime, default=datetime.utcnow)
    
    # Rol personalizado
    rol_id = Column(Integer, ForeignKey("group_roles.id", ondelete="SET NULL"), nullable=True)
    rol_obj = relationship("GroupRol")
    
    # Permisos granulares
    perm_compartir_pistas = Column(Boolean, default=True)
    perm_compartir_letras = Column(Boolean, default=True)
    perm_crud_repertorios = Column(Boolean, default=False)
    perm_crud_letras = Column(Boolean, default=True)
    perm_ver_pistas = Column(Boolean, default=True)
    perm_ver_letras = Column(Boolean, default=True)
    perm_ver_repertorios = Column(Boolean, default=True)
    perm_gestionar_solicitudes = Column(Boolean, default=False)

    # Registro de última lectura para notificaciones
    last_read_pistas = Column(DateTime, nullable=True, default=None)
    last_read_letras = Column(DateTime, nullable=True, default=None)
    last_read_repertorios = Column(DateTime, nullable=True, default=None)

    user = relationship("User", back_populates="groups")
    group = relationship("Group", back_populates="members")

class Lyric(Base):
    __tablename__ = "lyrics"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    artist = Column(String, index=True)
    content = Column(String) # ChordPro format
    key = Column(String) # Original key (e.g., "C")
    bpm = Column(Integer, nullable=True)
    group_id = Column(Integer, ForeignKey("groups.id"))
    created_by = Column(Integer, ForeignKey("users.id"))
    transpose_offset = Column(Integer, default=0, nullable=True)
    is_repertoire_only = Column(Boolean, default=False, nullable=True)
    parent_lyric_id = Column(Integer, ForeignKey("lyrics.id", ondelete="SET NULL"), nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    group = relationship("Group", back_populates="lyrics")
    creator = relationship("User")
    groups_shared = relationship("Group", secondary=lyric_groups, back_populates="lyrics_shared")

    @property
    def group_ids(self):
        return [g.id for g in self.groups_shared]

class Repertorio(Base):
    __tablename__ = "repertorios"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    group_id = Column(Integer, ForeignKey("groups.id"))
    created_by = Column(Integer, ForeignKey("users.id"))
    color = Column(String, nullable=True, default='#448AFF')
    created_at = Column(DateTime, default=datetime.utcnow)

    group = relationship("Group", back_populates="repertorios")
    creator = relationship("User")
    lyrics = relationship("RepertorioLyric", back_populates="repertorio")

class RepertorioLyric(Base):
    __tablename__ = "repertorio_lyrics"

    repertorio_id = Column(Integer, ForeignKey("repertorios.id"), primary_key=True)
    lyric_id = Column(Integer, ForeignKey("lyrics.id"), primary_key=True)
    order = Column(Integer) # Order in the setlist

    repertorio = relationship("Repertorio", back_populates="lyrics")
    lyric = relationship("Lyric")

class Invitation(Base):
    __tablename__ = "invitations"

    id = Column(Integer, primary_key=True, index=True)
    code = Column(String, unique=True, index=True) # Unique code or link token
    group_id = Column(Integer, ForeignKey("groups.id"))
    created_by = Column(Integer, ForeignKey("users.id"))
    created_at = Column(DateTime, default=datetime.utcnow)
    expires_at = Column(DateTime)
    used_at = Column(DateTime) # Nullable, if used

    group = relationship("Group")
    creator = relationship("User")