# retoor <retoor@molodetz.nl>

from devplacepy.database import CONSENTS_TABLE, db, get_setting, get_table

from .agreements import Agreement

DEFAULT_VERSION = "1"

LIVE_ACCOUNT_CLAUSES: tuple[tuple[str, str], ...] = (
    ("deletion_requested_at", "COALESCE(u.deletion_requested_at, '') = ''"),
    ("is_active", "COALESCE(u.is_active, 1) != 0"),
    ("deleted_at", "u.deleted_at IS NULL"),
)


def latest_consent(column: str, user_ref: str) -> str:
    return (
        f"COALESCE((SELECT c.{column} FROM {CONSENTS_TABLE} c "
        f"WHERE c.owner_kind = 'user' AND c.owner_id = {user_ref} "
        "AND c.kind = :kind AND c.deleted_at IS NULL "
        "ORDER BY c.created_at DESC, c.id DESC LIMIT 1), '')"
    )


def not_withdrawn(user_ref: str) -> str:
    return f"{latest_consent('state', user_ref)} != 'withdrawn'"


def has_gate_column(agreement: Agreement) -> bool:
    if not agreement.gate_column:
        return False
    if "users" not in db.tables:
        return False
    return get_table("users").has_column(agreement.gate_column)


def satisfied_clause(agreement: Agreement, user_ref: str, column_prefix: str) -> str:
    if has_gate_column(agreement):
        return f"COALESCE({column_prefix}{agreement.gate_column}, '') != :version"
    if agreement.version_setting:
        return f"{latest_consent('version', user_ref)} != :version"
    return f"{latest_consent('state', user_ref)} != 'granted'"


def live_account_clauses() -> list[str]:
    users = get_table("users")
    return [
        clause for column, clause in LIVE_ACCOUNT_CLAUSES if users.has_column(column)
    ]


def current_version(agreement: Agreement) -> str:
    if not agreement.version_setting:
        return DEFAULT_VERSION
    return get_setting(agreement.version_setting, DEFAULT_VERSION) or DEFAULT_VERSION


def pending(agreement: Agreement, limit: int) -> list[dict]:
    if "users" not in db.tables or CONSENTS_TABLE not in db.tables:
        return []
    bound = max(1, int(limit))
    clauses = [
        *live_account_clauses(),
        not_withdrawn("u.uid"),
        satisfied_clause(agreement, "u.uid", "u."),
    ]
    sql = (
        "SELECT u.uid, u.username FROM users u "
        f"WHERE {' AND '.join(clauses)} "
        "ORDER BY u.id LIMIT :limit"
    )
    rows = db.query(
        sql,
        kind=agreement.kind,
        version=current_version(agreement),
        limit=bound,
    )
    return [dict(row) for row in rows]
