from sqlalchemy.orm import Session
from sqlalchemy import or_, select

from . import models


class UserRepository:
    def __init__(self, db: Session):
        self.db = db

    def get_by_id(self, user_id: int):
        return self.db.get(models.User, user_id)

    def get_by_username(self, username: str):
        stmt = select(models.User).where(models.User.username == username)
        return self.db.execute(stmt).scalar_one_or_none()

    def get_by_email(self, email: str):
        stmt = select(models.User).where(models.User.email == email)
        return self.db.execute(stmt).scalar_one_or_none()

    def list_all(self):
        stmt = select(models.User).order_by(models.User.username)
        return self.db.execute(stmt).scalars().all()

    def list_admins(self):
        stmt = select(models.User).where(models.User.role == "admin").order_by(models.User.username)
        return self.db.execute(stmt).scalars().all()

    def create(self, user: models.User):
        self.db.add(user)
        self.db.commit()
        self.db.refresh(user)
        return user

    def update(self, user: models.User):
        self.db.add(user)
        self.db.commit()
        self.db.refresh(user)
        return user


class ProjectRepository:
    def __init__(self, db: Session):
        self.db = db

    def get(self, project_id: int):
        return self.db.get(models.Project, project_id)

    def list_all(self):
        stmt = select(models.Project).order_by(models.Project.created_at.desc())
        return self.db.execute(stmt).scalars().all()

    def list_for_user(self, user_id: int):
        stmt = (
            select(models.Project)
            .outerjoin(models.ProjectMember, models.ProjectMember.project_id == models.Project.id)
            .outerjoin(models.Task, models.Task.project_id == models.Project.id)
            .where(
                or_(
                    models.ProjectMember.user_id == user_id,
                    models.Task.assigned_user_id == user_id,
                )
            )
            .distinct()
            .order_by(models.Project.created_at.desc())
        )
        return self.db.execute(stmt).scalars().all()

    def create(self, project: models.Project):
        self.db.add(project)
        self.db.commit()
        self.db.refresh(project)
        return project


class ProjectMemberRepository:
    def __init__(self, db: Session):
        self.db = db

    def add_member(self, project_id: int, user_id: int, role: str = "member"):
        member = models.ProjectMember(project_id=project_id, user_id=user_id, role=role)
        self.db.add(member)
        self.db.commit()
        self.db.refresh(member)
        return member

    def get_member(self, project_id: int, user_id: int):
        stmt = select(models.ProjectMember).where(
            models.ProjectMember.project_id == project_id,
            models.ProjectMember.user_id == user_id,
        )
        return self.db.execute(stmt).scalar_one_or_none()

    def is_member(self, project_id: int, user_id: int):
        stmt = select(models.ProjectMember).where(
            models.ProjectMember.project_id == project_id,
            models.ProjectMember.user_id == user_id,
        )
        return self.db.execute(stmt).scalar_one_or_none()


class TaskRepository:
    def __init__(self, db: Session):
        self.db = db

    def get(self, task_id: int):
        return self.db.get(models.Task, task_id)

    def list_by_project(self, project_id: int):
        stmt = select(models.Task).where(models.Task.project_id == project_id)
        return self.db.execute(stmt).scalars().all()

    def list_for_user(self, user_id: int):
        stmt = select(models.Task).where(models.Task.assigned_user_id == user_id)
        return self.db.execute(stmt).scalars().all()

    def create(self, task: models.Task):
        self.db.add(task)
        self.db.commit()
        self.db.refresh(task)
        return task

    def update(self, task: models.Task):
        self.db.add(task)
        self.db.commit()
        self.db.refresh(task)
        return task

    def delete(self, task: models.Task):
        self.db.delete(task)
        self.db.commit()


class TaskRequestRepository:
    def __init__(self, db: Session):
        self.db = db

    def get(self, request_id: int):
        return self.db.get(models.TaskRequest, request_id)

    def list_pending_for_user(self, user_id: int):
        stmt = select(models.TaskRequest).where(
            models.TaskRequest.target_user_id == user_id,
            models.TaskRequest.status == "pending",
        )
        return self.db.execute(stmt).scalars().all()

    def create(self, request: models.TaskRequest):
        self.db.add(request)
        self.db.commit()
        self.db.refresh(request)
        return request

    def update(self, request: models.TaskRequest):
        self.db.add(request)
        self.db.commit()
        self.db.refresh(request)
        return request