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

# Association table for User-Role (Many-to-Many for cumulative RBAC)
user_roles = Table(
    'user_roles',
    Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id', ondelete='CASCADE'), primary_key=True),
    Column('role_id', Integer, ForeignKey('roles.id', ondelete='CASCADE'), primary_key=True)
)

# Association table for Project Members (User-Project)
project_members = Table(
    'project_members',
    Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id', ondelete='CASCADE'), primary_key=True),
    Column('project_id', Integer, ForeignKey('projects.id', ondelete='CASCADE'), primary_key=True)
)

# Association table for Project Viewers (Read-only User-Project)
project_viewers = Table(
    'project_viewers',
    Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id', ondelete='CASCADE'), primary_key=True),
    Column('project_id', Integer, ForeignKey('projects.id', ondelete='CASCADE'), primary_key=True)
)

# Association table for Task-Tag
task_tags = Table(
    'task_tags',
    Base.metadata,
    Column('task_id', Integer, ForeignKey('tasks.id', ondelete='CASCADE'), primary_key=True),
    Column('tag_id', Integer, ForeignKey('tags.id', ondelete='CASCADE'), primary_key=True)
)

# Association table for Team Members
team_members = Table(
    'team_members',
    Base.metadata,
    Column('team_id', Integer, ForeignKey('teams.id', ondelete='CASCADE'), primary_key=True),
    Column('user_id', Integer, ForeignKey('users.id', ondelete='CASCADE'), primary_key=True)
)

class Company(Base):
    __tablename__ = 'companies'
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), unique=True, index=True)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    users = relationship("User", back_populates="company")

class Role(Base):
    __tablename__ = 'roles'
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), unique=True, index=True) # e.g. 'Administrador', 'Project Manager', 'Miembro'
    description = Column(String(200))
    
    users = relationship("User", secondary=user_roles, back_populates="roles")

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, index=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=True)
    email = Column(String(100), unique=True, index=True)
    hashed_password = Column(String(200))
    is_active = Column(Boolean, default=True)
    
    # MFA fields
    mfa_code = Column(String(100), nullable=True)
    mfa_code_expires = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    last_active = Column(DateTime, nullable=True)

    company = relationship("Company", back_populates="users")
    roles = relationship("Role", secondary=user_roles, back_populates="users", lazy="selectin")
    projects = relationship("Project", secondary=project_members, back_populates="members")
    teams = relationship("Team", secondary=team_members, back_populates="members")
    tasks_assigned = relationship("Task", foreign_keys='Task.assignee_id', back_populates="assignee")
    viewable_projects = relationship("Project", secondary=project_viewers, back_populates="viewers")

    @property
    def is_admin(self):
        return self.username == 'admin' or any(role.name in ["Administrador", "Super Administrador"] for role in self.roles)

class Team(Base):
    __tablename__ = 'teams'
    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=True)
    name = Column(String(100), index=True)
    description = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    members = relationship("User", secondary=team_members, back_populates="teams", lazy="selectin")
    projects = relationship("Project", back_populates="team")
    tasks = relationship("Task", back_populates="team")

class Project(Base):
    __tablename__ = 'projects'
    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=True)
    name = Column(String(100), index=True)
    description = Column(Text)
    is_archived = Column(Boolean, default=False)
    is_public = Column(Boolean, default=True)
    creator_id = Column(Integer, ForeignKey('users.id'), nullable=True)
    assignee_id = Column(Integer, ForeignKey('users.id'), nullable=True)
    team_id = Column(Integer, ForeignKey('teams.id'), nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    roadmap_objectives = Column(Text, nullable=True)
    roadmap_deliverables = Column(Text, nullable=True)
    roadmap_strategy = Column(Text, nullable=True)
    roadmap_resources = Column(Text, nullable=True)
    roadmap_deadlines = Column(Text, nullable=True)
    roadmap_stakeholders = Column(Text, nullable=True)
    
    creator = relationship("User", foreign_keys=[creator_id])
    assignee = relationship("User", foreign_keys=[assignee_id])
    company = relationship("Company")
    team = relationship("Team", back_populates="projects", lazy="selectin")
    members = relationship("User", secondary=project_members, back_populates="projects")
    viewers = relationship("User", secondary=project_viewers, back_populates="viewable_projects", lazy="selectin")
    columns = relationship("KanbanColumn", back_populates="project", cascade="all, delete-orphan")
    tasks = relationship("Task", back_populates="project", cascade="all, delete-orphan")
    comments = relationship("ProjectComment", back_populates="project", cascade="all, delete-orphan")
    tags = relationship("Tag", back_populates="project", cascade="all, delete-orphan")
    roadmap_versions = relationship("ProjectRoadmapVersion", back_populates="project", cascade="all, delete-orphan")
    report_versions = relationship("ProjectReportVersion", back_populates="project", cascade="all, delete-orphan")

class KanbanColumn(Base):
    __tablename__ = 'kanban_columns'
    id = Column(Integer, primary_key=True, index=True)
    project_id = Column(Integer, ForeignKey('projects.id'))
    name = Column(String(50)) # 'To Do', 'In Progress', 'Done'
    order = Column(Integer)
    
    project = relationship("Project", back_populates="columns")
    tasks = relationship("Task", back_populates="column")

class Task(Base):
    __tablename__ = 'tasks'
    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(200), index=True)
    description = Column(Text)
    story_points = Column(Integer, default=0)
    
    project_id = Column(Integer, ForeignKey('projects.id', ondelete='CASCADE'))
    column_id = Column(Integer, ForeignKey('kanban_columns.id', ondelete='SET NULL'), nullable=True)
    assignee_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    team_id = Column(Integer, ForeignKey('teams.id', ondelete='SET NULL'), nullable=True)
    
    start_date = Column(DateTime, nullable=True)
    due_date = Column(DateTime, nullable=True)
    completed_at = Column(DateTime, nullable=True)
    is_blocked = Column(Boolean, default=False)
    progress = Column(Float, default=0.0)
    priority = Column(String(20), default="low")
    order = Column(Integer, default=0)
    
    # Advanced scheduling fields
    baseline_start_date = Column(DateTime, nullable=True)
    baseline_end_date = Column(DateTime, nullable=True)
    estimated_hours = Column(Float, default=0.0)
    resource_cost = Column(Float, default=0.0)
    
    created_at = Column(DateTime, default=datetime.utcnow)
    
    project = relationship("Project", back_populates="tasks")
    column = relationship("KanbanColumn", back_populates="tasks")
    assignee = relationship("User", foreign_keys=[assignee_id], back_populates="tasks_assigned")
    team = relationship("Team", foreign_keys=[team_id], back_populates="tasks")
    logs = relationship("TaskLog", back_populates="task", cascade="all, delete-orphan")
    attachments = relationship("TaskAttachment", back_populates="task", cascade="all, delete-orphan", lazy="selectin")
    comments = relationship("TaskComment", back_populates="task", cascade="all, delete-orphan")
    tags = relationship("Tag", secondary=task_tags, back_populates="tasks", lazy="selectin")
    subtasks = relationship("Subtask", back_populates="task", cascade="all, delete-orphan", lazy="selectin")

class TaskAttachment(Base):
    __tablename__ = 'task_attachments'
    id = Column(Integer, primary_key=True, index=True)
    task_id = Column(Integer, ForeignKey('tasks.id', ondelete='CASCADE'))
    filename = Column(String(255))
    file_path = Column(String(500))
    uploaded_at = Column(DateTime, default=datetime.utcnow)

    task = relationship("Task", back_populates="attachments")

class TaskLog(Base):
    __tablename__ = 'task_logs'
    id = Column(Integer, primary_key=True, index=True)
    task_id = Column(Integer, ForeignKey('tasks.id', ondelete='CASCADE'))
    user_id = Column(Integer, ForeignKey('users.id'))
    action = Column(String(100)) # e.g. "Moved to Done", "Blocked"
    timestamp = Column(DateTime, default=datetime.utcnow)
    
    task = relationship("Task", back_populates="logs")

class AuditLog(Base):
    __tablename__ = 'audit_logs'
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'))
    username = Column(String(50)) # Guardamos el username en caso de que el usuario se elimine
    action = Column(String(200)) # Ej: "Inicio de sesión", "Creó el proyecto X"
    ip_address = Column(String(50), nullable=True)
    timestamp = Column(DateTime, default=datetime.utcnow)

class SystemSetting(Base):
    __tablename__ = 'system_settings'
    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=True)
    key = Column(String(50), index=True) # e.g. "backups_enabled", "api_enabled"
    value = Column(String(500))

class TaskLink(Base):
    __tablename__ = 'task_links'
    id = Column(Integer, primary_key=True, index=True)
    source_id = Column(String(50), index=True)
    target_id = Column(String(50), index=True)
    type = Column(String(10), default="0")
    project_id = Column(Integer, ForeignKey('projects.id', ondelete='CASCADE'))

class TaskComment(Base):
    __tablename__ = 'task_comments'
    id = Column(Integer, primary_key=True, index=True)
    task_id = Column(Integer, ForeignKey('tasks.id', ondelete='CASCADE'))
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    content = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    task = relationship("Task", back_populates="comments")
    user = relationship("User", lazy="selectin")

class ProjectComment(Base):
    __tablename__ = 'project_comments'
    id = Column(Integer, primary_key=True, index=True)
    project_id = Column(Integer, ForeignKey('projects.id', ondelete='CASCADE'))
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    content = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    project = relationship("Project", back_populates="comments")
    user = relationship("User", lazy="selectin")

class Tag(Base):
    __tablename__ = 'tags'
    id = Column(Integer, primary_key=True, index=True)
    project_id = Column(Integer, ForeignKey('projects.id', ondelete='CASCADE'))
    name = Column(String(50))
    color = Column(String(20), default="#888888")
    
    project = relationship("Project", back_populates="tags")
    tasks = relationship("Task", secondary=task_tags, back_populates="tags")

class TaskProgressHistory(Base):
    __tablename__ = 'task_progress_history'
    id = Column(Integer, primary_key=True, index=True)
    task_id = Column(Integer, ForeignKey('tasks.id', ondelete='CASCADE'))
    project_id = Column(Integer, ForeignKey('projects.id', ondelete='CASCADE'))
    date = Column(DateTime, default=datetime.utcnow)
    progress = Column(Float)

class Subtask(Base):
    __tablename__ = 'subtasks'
    id = Column(Integer, primary_key=True, index=True)
    task_id = Column(Integer, ForeignKey('tasks.id', ondelete='CASCADE'))
    title = Column(String(200))
    is_completed = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    task = relationship("Task", back_populates="subtasks")

class Ticket(Base):
    __tablename__ = 'tickets'
    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=True)
    title = Column(String(200), index=True)
    description = Column(Text)
    status = Column(String(50), default="Abierto", index=True) # Abierto, En Progreso, Resuelto, Rechazado, Cerrado
    priority = Column(String(50), default="Media", index=True) # Crítica, Alta, Media, Baja
    category = Column(String(100), nullable=True)
    company = Column(String(100), nullable=True)
    tags = Column(String(255), nullable=True)
    
    # Can be linked to a registered user or just an email for external
    created_by_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    email_reporter = Column(String(150), nullable=True) 
    
    assignee_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    team_id = Column(Integer, ForeignKey('teams.id', ondelete='SET NULL'), nullable=True)
    
    # Link to task/project if converted
    linked_task_id = Column(Integer, ForeignKey('tasks.id', ondelete='SET NULL'), nullable=True)
    linked_project_id = Column(Integer, ForeignKey('projects.id', ondelete='SET NULL'), nullable=True)
    
    sla_deadline = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    created_by = relationship("User", foreign_keys=[created_by_id])
    assignee = relationship("User", foreign_keys=[assignee_id])
    team = relationship("Team", foreign_keys=[team_id])
    linked_task = relationship("Task", foreign_keys=[linked_task_id])
    linked_project = relationship("Project", foreign_keys=[linked_project_id])
    comments = relationship("TicketComment", back_populates="ticket", cascade="all, delete-orphan")
    attachments = relationship("TicketAttachment", back_populates="ticket", cascade="all, delete-orphan")

class TicketComment(Base):
    __tablename__ = 'ticket_comments'
    id = Column(Integer, primary_key=True, index=True)
    ticket_id = Column(Integer, ForeignKey('tickets.id', ondelete='CASCADE'))
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    content = Column(Text)
    is_internal = Column(Boolean, default=False) # True if it's an internal note not visible to the client
    created_at = Column(DateTime, default=datetime.utcnow)
    
    ticket = relationship("Ticket", back_populates="comments")
    user = relationship("User", lazy="selectin")
    attachments = relationship("TicketAttachment", back_populates="comment", cascade="all, delete-orphan")

class TicketAttachment(Base):
    __tablename__ = 'ticket_attachments'
    id = Column(Integer, primary_key=True, index=True)
    ticket_id = Column(Integer, ForeignKey('tickets.id', ondelete='CASCADE'))
    comment_id = Column(Integer, ForeignKey('ticket_comments.id', ondelete='CASCADE'), nullable=True)
    filename = Column(String(255))
    file_path = Column(String(500))
    uploaded_at = Column(DateTime, default=datetime.utcnow)

    ticket = relationship("Ticket", back_populates="attachments")
    comment = relationship("TicketComment", back_populates="attachments")

class Notification(Base):
    __tablename__ = 'notifications'
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), index=True)
    message = Column(String(255))
    link = Column(String(255), nullable=True)
    is_read = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    user = relationship("User", foreign_keys=[user_id])

class KbArticle(Base):
    __tablename__ = 'kb_articles'
    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=True)
    title = Column(String(200), index=True)
    content = Column(Text)
    category = Column(String(100), index=True, nullable=True)
    author_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    views = Column(Integer, default=0)
    is_published = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    author = relationship("User", foreign_keys=[author_id])

class TicketRating(Base):
    __tablename__ = 'ticket_ratings'
    id = Column(Integer, primary_key=True, index=True)
    ticket_id = Column(Integer, ForeignKey('tickets.id', ondelete='CASCADE'), unique=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    rating = Column(Integer) # 1 to 5
    comment = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    ticket = relationship("Ticket")
    user = relationship("User")

class TicketAutomation(Base):
    __tablename__ = 'ticket_automations'
    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=True)
    name = Column(String(100))
    conditions_json = Column(Text) # JSON string of conditions
    actions_json = Column(Text) # JSON string of actions
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)

class Holiday(Base):
    __tablename__ = 'holidays'
    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False)
    date = Column(Date, index=True)
    name = Column(String(100))
    
    company = relationship("Company")


class ProjectRoadmapVersion(Base):
    __tablename__ = 'project_roadmap_versions'
    id = Column(Integer, primary_key=True, index=True)
    project_id = Column(Integer, ForeignKey('projects.id', ondelete='CASCADE'), nullable=False)
    version_name = Column(String(100), nullable=False)
    roadmap_objectives = Column(Text, nullable=True)
    roadmap_deliverables = Column(Text, nullable=True)
    roadmap_strategy = Column(Text, nullable=True)
    roadmap_resources = Column(Text, nullable=True)
    roadmap_deadlines = Column(Text, nullable=True)
    roadmap_stakeholders = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    project = relationship("Project", back_populates="roadmap_versions")


class ProjectReportVersion(Base):
    __tablename__ = 'project_report_versions'
    id = Column(Integer, primary_key=True, index=True)
    project_id = Column(Integer, ForeignKey('projects.id', ondelete='CASCADE'), nullable=False)
    version_name = Column(String(100), nullable=False)
    report_html = Column(Text, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    project = relationship("Project", back_populates="report_versions")