from sqlalchemy import select, func
from sqlalchemy.ext.asyncio import AsyncSession

from backend.models.subreddit import MonitoredSubreddit
from backend.models.post import Post


async def list_subreddits(db: AsyncSession) -> list[dict]:
    stmt = (
        select(
            MonitoredSubreddit,
            func.count(Post.id).label("post_count"),
        )
        .outerjoin(Post, Post.subreddit_id == MonitoredSubreddit.id)
        .group_by(MonitoredSubreddit.id)
        .order_by(MonitoredSubreddit.name)
    )
    result = await db.execute(stmt)
    rows = result.all()
    out = []
    for sub, post_count in rows:
        data = {c.name: getattr(sub, c.name) for c in sub.__table__.columns}
        data["post_count"] = post_count
        out.append(data)
    return out


async def get_subreddit(db: AsyncSession, subreddit_id: int) -> dict | None:
    stmt = (
        select(
            MonitoredSubreddit,
            func.count(Post.id).label("post_count"),
        )
        .outerjoin(Post, Post.subreddit_id == MonitoredSubreddit.id)
        .where(MonitoredSubreddit.id == subreddit_id)
        .group_by(MonitoredSubreddit.id)
    )
    result = await db.execute(stmt)
    row = result.first()
    if not row:
        return None
    sub, post_count = row
    data = {c.name: getattr(sub, c.name) for c in sub.__table__.columns}
    data["post_count"] = post_count
    return data


async def create_subreddit(db: AsyncSession, name: str) -> MonitoredSubreddit:
    sub = MonitoredSubreddit(name=name.lower().strip())
    db.add(sub)
    await db.commit()
    await db.refresh(sub)
    return sub


async def update_subreddit(
    db: AsyncSession, subreddit_id: int, is_active: bool | None = None
) -> MonitoredSubreddit | None:
    sub = await db.get(MonitoredSubreddit, subreddit_id)
    if not sub:
        return None
    if is_active is not None:
        sub.is_active = is_active
    await db.commit()
    await db.refresh(sub)
    return sub


async def delete_subreddit(db: AsyncSession, subreddit_id: int) -> bool:
    sub = await db.get(MonitoredSubreddit, subreddit_id)
    if not sub:
        return False
    sub.is_active = False
    await db.commit()
    return True