# retoor <retoor@molodetz.nl>

from .core import _drop_index, _in_clause, _index, _now_iso, db
from .pagination import build_pagination


SOFT_DELETE_TABLES = [
    "posts",
    "comments",
    "gists",
    "projects",
    "news",
    "news_images",
    "project_files",
    "attachments",
    "votes",
    "reactions",
    "bookmarks",
    "follows",
    "poll_votes",
    "polls",
    "poll_options",
    "sessions",
    "instances",
    "instance_schedules",
    "tunnels",
    "workspace_flags",
    "workspace_quota_rules",
    "workspace_editor_prefs",
    "backup_schedules",
    "devii_conversations",
    "devii_tasks",
    "devii_lessons",
    "devii_virtual_tools",
    "user_customizations",
    "project_forks",
    "issue_tickets",
    "issue_comment_authors",
    "notification_preferences",
    "deepsearch_sessions",
    "deepsearch_messages",
    "isslop_analyses",
    "devrant_tokens",
    "access_tokens",
    "email_accounts",
    "user_relations",
    "seo_metadata",
    "awards",
    "quizzes",
    "quiz_questions",
    "quiz_options",
    "quiz_attempts",
    "quiz_answers",
    "opinion_wars",
    "opinion_war_fighters",
    "opinion_war_events",
    "content_reports",
    "moderation_actions",
    "content_maturity",
    "user_consents",
]


def ensure_soft_delete_columns(table, *, db_handle=None):
    handle = db_handle or db
    if table not in handle.tables:
        return
    target = handle[table]
    if not target.has_column("deleted_at"):
        target.create_column_by_example("deleted_at", "")
    if not target.has_column("deleted_by"):
        target.create_column_by_example("deleted_by", "")
    _drop_index(handle, f"idx_{table}_deleted")
    _index(
        handle,
        table,
        f"idx_{table}_trash",
        ["deleted_at"],
        where="deleted_at IS NOT NULL",
    )


def soft_delete(table_name, deleted_by, *, stamp=None, **criteria):
    if table_name not in db.tables:
        return 0
    table = db[table_name]
    if not table.has_column("deleted_at"):
        return 0
    rows = list(table.find(deleted_at=None, **criteria))
    if not rows:
        return 0
    stamp = stamp or _now_iso()
    for row in rows:
        table.update(
            {"id": row["id"], "deleted_at": stamp, "deleted_by": deleted_by}, ["id"]
        )
    return len(rows)


def soft_delete_in(table_name, column, uids, deleted_by, *, stamp=None, **extra):
    uids = [uid for uid in (uids or []) if uid]
    if not uids or table_name not in db.tables:
        return 0
    if not db[table_name].has_column("deleted_at"):
        return 0
    placeholders, params = _in_clause(uids)
    params["dat"] = stamp or _now_iso()
    params["dby"] = deleted_by
    extra_sql = ""
    for index, (key, value) in enumerate(extra.items()):
        params[f"x{index}"] = value
        extra_sql += f" AND {key} = :x{index}"
    with db:
        db.query(
            f"UPDATE {table_name} SET deleted_at = :dat, deleted_by = :dby "
            f"WHERE {column} IN ({placeholders}) AND deleted_at IS NULL{extra_sql}",
            **params,
        )
    return len(uids)


def restore(table_name, **criteria):
    if table_name not in db.tables:
        return 0
    table = db[table_name]
    if not table.has_column("deleted_at"):
        return 0
    rows = [row for row in table.find(**criteria) if row.get("deleted_at")]
    for row in rows:
        table.update({"id": row["id"], "deleted_at": None, "deleted_by": None}, ["id"])
    return len(rows)


def purge(table_name, **criteria):
    if table_name not in db.tables:
        return 0
    table = db[table_name]
    count = table.count(**criteria)
    table.delete(**criteria)
    return int(count)


def list_deleted(table_name, page=1, per_page=25):
    if table_name not in db.tables or not db[table_name].has_column("deleted_at"):
        return [], build_pagination(page, 0, per_page)
    table = db[table_name]
    column = table.table.columns.deleted_at
    total = table.count(column.isnot(None))
    pagination = build_pagination(page, total, per_page)
    offset = (pagination["page"] - 1) * pagination["per_page"]
    rows = list(
        table.find(
            column.isnot(None),
            order_by=["-deleted_at"],
            _limit=pagination["per_page"],
            _offset=offset,
        )
    )
    return rows, pagination


def count_deleted(table_name):
    if table_name not in db.tables or not db[table_name].has_column("deleted_at"):
        return 0
    table = db[table_name]
    return int(table.count(table.table.columns.deleted_at.isnot(None)))


def restore_event(stamp):
    if not stamp:
        return 0
    restored = 0
    for table_name in SOFT_DELETE_TABLES:
        if table_name in db.tables and db[table_name].has_column("deleted_at"):
            restored += int(
                db.query(
                    f"SELECT COUNT(*) AS n FROM {table_name} WHERE deleted_at = :s",
                    s=stamp,
                ).__next__()["n"]
            )
            with db:
                db.query(
                    f"UPDATE {table_name} SET deleted_at = NULL, deleted_by = NULL "
                    f"WHERE deleted_at = :s",
                    s=stamp,
                )
    return restored


def purge_event(stamp):
    if not stamp:
        return []
    purged = []
    for table_name in SOFT_DELETE_TABLES:
        if table_name in db.tables and db[table_name].has_column("deleted_at"):
            rows = list(
                db.query(
                    f"SELECT * FROM {table_name} WHERE deleted_at = :s", s=stamp
                )
            )
            if rows:
                purged.append((table_name, rows))
                with db:
                    db.query(
                        f"DELETE FROM {table_name} WHERE deleted_at = :s", s=stamp
                    )
    return purged
