# retoor <retoor@molodetz.nl>

from .core import _in_clause, _now_iso, db, get_table


SEO_META_TYPES = ("post", "project", "gist", "news", "issue", "quiz")


def get_seo_metadata(target_type: str, target_uid: str) -> dict | None:
    if not target_type or not target_uid or "seo_metadata" not in db.tables:
        return None
    row = get_table("seo_metadata").find_one(
        target_type=target_type,
        target_uid=str(target_uid),
        status="ready",
        deleted_at=None,
    )
    return dict(row) if row else None


def get_seo_metadata_batch(target_type: str, uids: list) -> dict:
    uids = [str(uid) for uid in (uids or []) if uid]
    if not uids or "seo_metadata" not in db.tables:
        return {}
    placeholders, params = _in_clause(uids)
    params["tt"] = target_type
    rows = db.query(
        "SELECT * FROM seo_metadata WHERE target_type = :tt AND status = 'ready' "
        f"AND deleted_at IS NULL AND target_uid IN ({placeholders})",
        **params,
    )
    return {row["target_uid"]: dict(row) for row in rows}


def has_fresh_seo_metadata(target_type: str, target_uid: str) -> bool:
    return get_seo_metadata(target_type, target_uid) is not None


def upsert_seo_metadata(
    target_type: str,
    target_uid: str,
    seo_title: str,
    seo_description: str,
    seo_keywords: str,
    status: str,
    source: str,
) -> None:
    if not target_type or not target_uid:
        return
    from devplacepy.utils import generate_uid

    table = get_table("seo_metadata")
    now = _now_iso()
    generated_at = now if status == "ready" else ""
    existing = table.find_one(target_type=target_type, target_uid=str(target_uid))
    payload = {
        "target_type": target_type,
        "target_uid": str(target_uid),
        "seo_title": seo_title,
        "seo_description": seo_description,
        "seo_keywords": seo_keywords,
        "status": status,
        "source": source,
        "generated_at": generated_at,
        "updated_at": now,
        "deleted_at": None,
        "deleted_by": None,
    }
    if existing:
        payload["id"] = existing["id"]
        table.update(payload, ["id"])
    else:
        payload["uid"] = generate_uid()
        payload["created_at"] = now
        table.insert(payload)


def mark_seo_metadata_stale(target_type: str, target_uid: str) -> None:
    if not target_type or not target_uid or "seo_metadata" not in db.tables:
        return
    table = get_table("seo_metadata")
    existing = table.find_one(target_type=target_type, target_uid=str(target_uid))
    if existing:
        table.update(
            {"id": existing["id"], "status": "pending", "updated_at": _now_iso()},
            ["id"],
        )
