# retoor <retoor@molodetz.nl>

from .core import db, logger
from .users import get_users_by_uids
from .pagination import build_pagination
from .content import resolve_object_url


def get_attachments(resource_type: str, resource_uid: str) -> list:
    if "attachments" not in db.tables:
        return []
    return list(
        db["attachments"].find(
            resource_type=resource_type,
            resource_uid=resource_uid,
            deleted_at=None,
            order_by=["created_at"],
        )
    )


def get_attachments_by_type(resource_type: str, resource_uids: list) -> dict:
    if not resource_uids or "attachments" not in db.tables:
        return {}
    rows = list(
        db["attachments"].find(
            db["attachments"].table.columns.resource_uid.in_(resource_uids),
            db["attachments"].table.columns.deleted_at.is_(None),
            resource_type=resource_type,
        )
    )
    result = {}
    for a in rows:
        key = a["resource_uid"]
        if key not in result:
            result[key] = []
        result[key].append(a)
    return result


def get_news_images_by_uids(news_uids: list) -> dict:
    if not news_uids or "news_images" not in db.tables:
        return {}
    images_table = db["news_images"]
    if not images_table.has_column("news_uid"):
        return {}
    rows = images_table.find(
        images_table.table.columns.news_uid.in_(news_uids),
        images_table.table.columns.deleted_at.is_(None),
        order_by=["uid"],
    )
    result = {}
    for r in rows:
        result.setdefault(r["news_uid"], r["url"])
    return result


def delete_attachment_record(uid: str) -> None:
    if "attachments" not in db.tables:
        return
    att = db["attachments"].find_one(uid=uid)
    if att:
        _delete_attachment_file(att)
        db["attachments"].delete(id=att["id"])


def delete_attachments(resource_type: str, resource_uid: str) -> None:
    if "attachments" not in db.tables:
        return
    for a in db["attachments"].find(
        resource_type=resource_type, resource_uid=resource_uid
    ):
        _delete_attachment_file(a)
    db["attachments"].delete(resource_type=resource_type, resource_uid=resource_uid)


def _delete_attachment_file(att: dict) -> None:
    from devplacepy.config import ATTACHMENTS_DIR

    directory = att.get("directory", "")
    stored_name = att.get("stored_name", "")
    if not (directory and stored_name):
        return
    file_path = ATTACHMENTS_DIR / directory / stored_name
    try:
        file_path.unlink(missing_ok=True)
        parent = file_path.parent
        if parent.exists() and not any(parent.iterdir()):
            parent.rmdir()
        grandparent = parent.parent
        if grandparent.exists() and not any(grandparent.iterdir()):
            grandparent.rmdir()
    except Exception as e:
        logger.warning(f"Failed to delete attachment file {stored_name}: {e}")


def get_user_media(user_uid: str, page: int = 1, per_page: int = 24) -> tuple:
    if "attachments" not in db.tables:
        return [], build_pagination(page, 0, per_page)
    from devplacepy.attachments import _row_to_attachment

    total = list(
        db.query(
            "SELECT COUNT(*) AS n FROM attachments "
            "WHERE user_uid=:u AND target_type != '' AND deleted_at IS NULL",
            u=user_uid,
        )
    )[0]["n"]
    pagination = build_pagination(page, total, per_page)
    offset = (pagination["page"] - 1) * pagination["per_page"]
    rows = db.query(
        "SELECT * FROM attachments "
        "WHERE user_uid=:u AND target_type != '' AND deleted_at IS NULL "
        "ORDER BY created_at DESC LIMIT :limit OFFSET :offset",
        u=user_uid,
        limit=pagination["per_page"],
        offset=offset,
    )
    items = []
    for row in rows:
        item = _row_to_attachment(row)
        item["target_url"] = resolve_object_url(item["target_type"], item["target_uid"])
        items.append(item)
    return items, pagination


def _decorate_attachment(row: dict) -> dict:
    from devplacepy.attachments import _row_to_attachment

    item = _row_to_attachment(row)
    item["linked"] = bool(item.get("target_type"))
    item["target_url"] = (
        resolve_object_url(item["target_type"], item["target_uid"])
        if item["linked"]
        else None
    )
    return item


def get_user_attachments(
    user_uid: str, page: int = 1, per_page: int = 24, linked=None
) -> tuple:
    if "attachments" not in db.tables:
        return [], build_pagination(page, 0, per_page)
    clause = "user_uid=:u AND deleted_at IS NULL"
    if linked is True:
        clause += " AND target_type != ''"
    elif linked is False:
        clause += " AND target_type = ''"
    total = list(
        db.query(f"SELECT COUNT(*) AS n FROM attachments WHERE {clause}", u=user_uid)
    )[0]["n"]
    pagination = build_pagination(page, total, per_page)
    offset = (pagination["page"] - 1) * pagination["per_page"]
    rows = db.query(
        f"SELECT * FROM attachments WHERE {clause} "
        "ORDER BY created_at DESC LIMIT :limit OFFSET :offset",
        u=user_uid,
        limit=pagination["per_page"],
        offset=offset,
    )
    return [_decorate_attachment(row) for row in rows], pagination


def get_user_attachment(uid: str) -> dict | None:
    if "attachments" not in db.tables:
        return None
    row = db["attachments"].find_one(uid=uid, deleted_at=None)
    if not row:
        return None
    return _decorate_attachment(row)


def get_deleted_media(page: int = 1, per_page: int = 24) -> tuple:
    if "attachments" not in db.tables:
        return [], build_pagination(page, 0, per_page)
    from devplacepy.attachments import _row_to_attachment

    total = list(
        db.query("SELECT COUNT(*) AS n FROM attachments WHERE deleted_at IS NOT NULL")
    )[0]["n"]
    pagination = build_pagination(page, total, per_page)
    offset = (pagination["page"] - 1) * pagination["per_page"]
    rows = db.query(
        "SELECT * FROM attachments WHERE deleted_at IS NOT NULL "
        "ORDER BY deleted_at DESC LIMIT :limit OFFSET :offset",
        limit=pagination["per_page"],
        offset=offset,
    )
    rows = list(rows)
    uploaders = get_users_by_uids([row.get("user_uid") for row in rows])
    items = []
    for row in rows:
        item = _row_to_attachment(row)
        item["target_url"] = resolve_object_url(item["target_type"], item["target_uid"])
        item["deleted_at"] = row.get("deleted_at", "")
        uploader = uploaders.get(row.get("user_uid"))
        item["uploader"] = uploader["username"] if uploader else "unknown"
        items.append(item)
    return items, pagination
