from datetime import datetime, timezone

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

from app.users.models import BlockedUser, User, UserContact


async def create_user(db: AsyncSession, *, email: str, name: str, username: str, password_hash: str) -> User:
    user = User(email=email, name=name, username=username, password_hash=password_hash, email_verified=False)
    db.add(user)
    await db.flush()
    return user


async def get_user_by_id(db: AsyncSession, user_id: int) -> User | None:
    result = await db.execute(select(User).where(User.id == user_id))
    return result.scalar_one_or_none()


async def get_user_by_email(db: AsyncSession, email: str) -> User | None:
    result = await db.execute(select(User).where(User.email == email))
    return result.scalar_one_or_none()


async def get_user_by_username(db: AsyncSession, username: str) -> User | None:
    result = await db.execute(select(User).where(User.username == username))
    return result.scalar_one_or_none()


async def list_users_by_ids(db: AsyncSession, user_ids: list[int]) -> list[User]:
    if not user_ids:
        return []
    result = await db.execute(select(User).where(User.id.in_(user_ids)))
    return list(result.scalars().all())


async def search_users_by_username(
    db: AsyncSession,
    *,
    query: str,
    limit: int = 20,
    exclude_user_id: int | None = None,
) -> list[User]:
    normalized = query.lower().strip().lstrip("@")
    stmt = select(User).where(
        or_(
            func.lower(User.username).like(f"%{normalized}%"),
            func.lower(User.email).like(f"%{normalized}%"),
        )
    )
    if exclude_user_id is not None:
        stmt = stmt.where(User.id != exclude_user_id)
    stmt = stmt.order_by(User.username.asc()).limit(limit)
    result = await db.execute(stmt)
    return list(result.scalars().all())


async def update_user_last_seen_now(db: AsyncSession, *, user_id: int) -> User | None:
    user = await get_user_by_id(db, user_id)
    if not user:
        return None
    user.last_seen_at = datetime.now(timezone.utc)
    await db.flush()
    return user


async def block_user(db: AsyncSession, *, user_id: int, blocked_user_id: int) -> BlockedUser:
    existing = await get_block_relation(db, user_id=user_id, blocked_user_id=blocked_user_id)
    if existing:
        return existing
    relation = BlockedUser(user_id=user_id, blocked_user_id=blocked_user_id)
    db.add(relation)
    await db.flush()
    return relation


async def unblock_user(db: AsyncSession, *, user_id: int, blocked_user_id: int) -> None:
    relation = await get_block_relation(db, user_id=user_id, blocked_user_id=blocked_user_id)
    if relation:
        await db.delete(relation)


async def get_block_relation(db: AsyncSession, *, user_id: int, blocked_user_id: int) -> BlockedUser | None:
    result = await db.execute(
        select(BlockedUser).where(
            BlockedUser.user_id == user_id,
            BlockedUser.blocked_user_id == blocked_user_id,
        )
    )
    return result.scalar_one_or_none()


async def has_block_relation_between_users(db: AsyncSession, *, user_a_id: int, user_b_id: int) -> bool:
    result = await db.execute(
        select(BlockedUser.id).where(
            or_(
                and_(BlockedUser.user_id == user_a_id, BlockedUser.blocked_user_id == user_b_id),
                and_(BlockedUser.user_id == user_b_id, BlockedUser.blocked_user_id == user_a_id),
            )
        ).limit(1)
    )
    return result.scalar_one_or_none() is not None


async def list_blocked_users(db: AsyncSession, *, user_id: int) -> list[User]:
    stmt = (
        select(User)
        .join(BlockedUser, BlockedUser.blocked_user_id == User.id)
        .where(BlockedUser.user_id == user_id)
        .order_by(User.username.asc())
    )
    result = await db.execute(stmt)
    return list(result.scalars().all())


async def add_contact(db: AsyncSession, *, user_id: int, contact_user_id: int) -> UserContact:
    existing = await get_contact_relation(db, user_id=user_id, contact_user_id=contact_user_id)
    if existing:
        return existing
    relation = UserContact(user_id=user_id, contact_user_id=contact_user_id)
    db.add(relation)
    await db.flush()
    return relation


async def remove_contact(db: AsyncSession, *, user_id: int, contact_user_id: int) -> None:
    relation = await get_contact_relation(db, user_id=user_id, contact_user_id=contact_user_id)
    if relation:
        await db.delete(relation)


async def get_contact_relation(db: AsyncSession, *, user_id: int, contact_user_id: int) -> UserContact | None:
    result = await db.execute(
        select(UserContact).where(
            UserContact.user_id == user_id,
            UserContact.contact_user_id == contact_user_id,
        )
    )
    return result.scalar_one_or_none()


async def is_user_in_contacts(db: AsyncSession, *, owner_user_id: int, candidate_user_id: int) -> bool:
    result = await db.execute(
        select(UserContact.id).where(
            UserContact.user_id == owner_user_id,
            UserContact.contact_user_id == candidate_user_id,
        ).limit(1)
    )
    return result.scalar_one_or_none() is not None


async def list_contacts(db: AsyncSession, *, user_id: int) -> list[User]:
    stmt = (
        select(User)
        .join(UserContact, UserContact.contact_user_id == User.id)
        .where(UserContact.user_id == user_id)
        .order_by(User.username.asc())
    )
    result = await db.execute(stmt)
    return list(result.scalars().all())