# retoor <retoor@molodetz.nl>

from __future__ import annotations

from datetime import datetime, timezone

from devplacepy.database import db, get_table
from devplacepy.utils import generate_uid

from . import naming

TUNNELS_TABLE = "tunnels"

STATUS_PENDING = "pending"
STATUS_PROVISIONING = "provisioning"
STATUS_ACTIVE = "active"
STATUS_FAILED = "failed"
STATUS_SUSPENDED = "suspended"

SERVING_STATUSES = (STATUS_PROVISIONING, STATUS_ACTIVE)


def _now() -> str:
    return datetime.now(timezone.utc).isoformat()


def _table():
    return get_table(TUNNELS_TABLE)


def list_for_instance(instance_uid: str) -> list[dict]:
    return list(
        _table().find(
            instance_uid=instance_uid, deleted_at=None, order_by=["created_at"]
        )
    )


def list_for_user(user_uid: str) -> list[dict]:
    return list(
        _table().find(user_uid=user_uid, deleted_at=None, order_by=["-created_at"])
    )


def get(uid: str) -> dict | None:
    return _table().find_one(uid=uid, deleted_at=None)


def by_hostname(hostname: str) -> dict | None:
    if not hostname:
        return None
    bare = hostname.split(":", 1)[0].lower().rstrip(".")
    return _table().find_one(hostname=bare, deleted_at=None)


def count_for_instance(instance_uid: str) -> int:
    return _table().count(instance_uid=instance_uid, deleted_at=None)


def keeps_certificate(row: dict) -> bool:
    return row.get("deleted_at") is None and row.get("status") == STATUS_ACTIVE


def create(
    instance: dict, label: str, container_port: int, user_uid: str
) -> dict | None:
    name = instance.get("tunnel_name", "")
    if not name or container_port <= 0:
        return None
    hostname = naming.hostname_for(name, container_port)
    table = _table()
    stamp = _now()
    with db:
        db.query(
            """
            INSERT INTO tunnels (
                uid, instance_uid, project_uid, user_uid, hostname, label,
                container_port, desired_state, status, cert_status,
                cert_checked_at, request_count, bytes_out, last_request_at,
                last_error, last_synced_at, created_at, updated_at,
                deleted_at, deleted_by
            ) VALUES (
                :uid, :instance_uid, :project_uid, :user_uid, :hostname, :label,
                :container_port, 'present', :status_pending, '', '', 0, 0, '', '',
                '', :stamp, :stamp, NULL, NULL
            )
            ON CONFLICT(hostname) DO UPDATE SET
                instance_uid = excluded.instance_uid,
                project_uid = excluded.project_uid,
                user_uid = excluded.user_uid,
                label = excluded.label,
                container_port = excluded.container_port,
                desired_state = 'present',
                updated_at = excluded.updated_at,
                deleted_at = NULL,
                deleted_by = NULL,
                status = CASE
                    WHEN tunnels.deleted_at IS NULL AND tunnels.status = :status_active
                    THEN tunnels.status ELSE :status_pending END,
                last_error = CASE
                    WHEN tunnels.deleted_at IS NULL AND tunnels.status = :status_active
                    THEN tunnels.last_error ELSE '' END
            """,
            uid=generate_uid(),
            instance_uid=instance["uid"],
            project_uid=instance.get("project_uid", ""),
            user_uid=user_uid,
            hostname=hostname,
            label=label or f"port {container_port}",
            container_port=container_port,
            stamp=stamp,
            status_pending=STATUS_PENDING,
            status_active=STATUS_ACTIVE,
        )
    return table.find_one(hostname=hostname)


def awaiting_certificate() -> list[dict]:
    return list(
        _table().find(
            status=STATUS_PENDING,
            desired_state="present",
            deleted_at=None,
            order_by=["created_at"],
        )
    )


def update(uid: str, changes: dict) -> None:
    _table().update({"uid": uid, "updated_at": _now(), **changes}, ["uid"])


def mark_absent(uid: str, actor_uid: str = "system") -> None:
    update(uid, {"desired_state": "absent"})


def soft_delete(uid: str, actor_uid: str = "system") -> None:
    _table().update(
        {"uid": uid, "deleted_at": _now(), "deleted_by": actor_uid}, ["uid"]
    )


def suspend_for_instance(instance_uid: str) -> None:
    for row in list_for_instance(instance_uid):
        update(row["uid"], {"status": STATUS_SUSPENDED})


def resume_for_instance(instance_uid: str) -> None:
    for row in list_for_instance(instance_uid):
        if row.get("status") == STATUS_SUSPENDED:
            update(row["uid"], {"status": STATUS_PENDING})


def record_hit(uid: str, byte_count: int) -> None:
    from sqlalchemy import text

    sql = (
        "UPDATE tunnels SET request_count = COALESCE(request_count, 0) + 1, "
        "bytes_out = COALESCE(bytes_out, 0) + :bytes, last_request_at = :seen "
        "WHERE uid = :uid AND deleted_at IS NULL"
    )
    with db:
        db.executable.execute(
            text(sql),
            {"bytes": max(0, byte_count), "seen": _now(), "uid": uid},
        )
