"""Seats must stop working when the plan no longer includes them.

`get_user_limit` was checked only at INVITE time, so an org that invited members
on a trial kept them all working after dropping to free (`user_limit: 1`). Seats we
no longer charge for stayed live indefinitely — and with 30-day remember-me
cookies, waiting for sessions to lapse is not enforcement.

Rules pinned here:
  * over-limit members are REFUSED, not deleted
  * admins keep their seat before agents, so an org can never lock out everyone
    able to upgrade or remove a member
  * upgrading restores access immediately
"""
import datetime as dt

import pytest
from sqlalchemy import text

from supporthub.app.db import engine, session_scope

SANDBOX_SLUG = "free-sandbox"
ADMIN_EMAIL = "free@local.test"
ADMIN_PASSWORD = "Test123!Free"
EXTRA_EMAIL = "seattest-agent@local.test"
EXTRA_PASSWORD = "Test123!Seat"


def _sandbox():
    with engine.connect() as c:
        return c.execute(text(
            "SELECT id, schema_name FROM public.organizations WHERE slug=:s"),
            {"s": SANDBOX_SLUG}).one_or_none()


pytestmark = pytest.mark.skipif(
    _sandbox() is None,
    reason="free-sandbox org not provisioned (run scripts/create_test_orgs.py)",
)


@pytest.fixture()
def extra_agent():
    """Add a second (agent) member to the sandbox org, then remove it."""
    from supporthub.app.services.auth_service import hash_password
    org = _sandbox()
    with session_scope(schema="public") as s:
        uid = s.execute(text(
            "INSERT INTO users (org_id, email, password_hash, role, email_verified,"
            " created_at) VALUES (:o, :e, :p, 'agent', true, :n) RETURNING id"),
            {"o": org.id, "e": EXTRA_EMAIL, "p": hash_password(EXTRA_PASSWORD),
             "n": dt.datetime.utcnow()}).scalar()
    yield uid
    with session_scope(schema="public") as s:
        s.execute(text("DELETE FROM users WHERE id=:i"), {"i": uid})


@pytest.fixture()
def as_plan():
    org = _sandbox()
    original = {}

    def _set(plan):
        with session_scope(schema="public") as s:
            if "plan" not in original:
                original["plan"] = s.execute(text(
                    "SELECT plan FROM public.organizations WHERE id=:i"),
                    {"i": org.id}).scalar()
            s.execute(text(
                "UPDATE public.organizations SET plan=:p, trial_ends_at=NULL WHERE id=:i"),
                {"p": plan, "i": org.id})
        _clear_seat_cache()

    yield _set

    if original:
        with session_scope(schema="public") as s:
            s.execute(text("UPDATE public.organizations SET plan=:p WHERE id=:i"),
                      {"p": original["plan"], "i": org.id})
        _clear_seat_cache()


def _clear_seat_cache():
    """No-op: the seat verdict is intentionally uncached so a plan change takes
    effect on the very next request. Kept as a named call so the tests document
    that requirement rather than depending on it silently."""
    return


def _login(email, password):
    import re
    from supporthub.app.main import app
    c = app.test_client()
    token = re.search(r'name="csrf_token"[^>]*value="([^"]+)"',
                      c.get("/login").get_data(as_text=True)).group(1)
    c.post("/login", data={"email": email, "password": password, "csrf_token": token})
    return c


def test_extra_member_is_refused_on_free(extra_agent, as_plan):
    as_plan("free")            # user_limit 1
    r = _login(EXTRA_EMAIL, EXTRA_PASSWORD).get("/inbox", follow_redirects=False)
    assert r.status_code == 302, "over-limit member should be bounced off the app"
    assert "/login" in r.headers.get("Location", "")


def test_the_admin_keeps_their_seat_on_free(extra_agent, as_plan):
    """Admins rank first — an org must never lock out everyone who can upgrade."""
    as_plan("free")
    r = _login(ADMIN_EMAIL, ADMIN_PASSWORD).get("/inbox")
    assert r.status_code == 200


def test_extra_member_works_again_after_upgrade(extra_agent, as_plan):
    as_plan("free")
    assert _login(EXTRA_EMAIL, EXTRA_PASSWORD).get(
        "/inbox", follow_redirects=False).status_code == 302
    as_plan("max")             # user_limit None
    assert _login(EXTRA_EMAIL, EXTRA_PASSWORD).get("/inbox").status_code == 200


def test_the_over_limit_member_is_not_deleted(extra_agent, as_plan):
    """Refused, never destroyed — the row must survive the downgrade."""
    as_plan("free")
    _login(EXTRA_EMAIL, EXTRA_PASSWORD).get("/inbox")
    with engine.connect() as c:
        still_there = c.execute(text(
            "SELECT count(*) FROM public.users WHERE id=:i"), {"i": extra_agent}).scalar()
    assert still_there == 1


def test_admin_ranks_above_an_older_agent(extra_agent, as_plan):
    """Ordering is (admins first, then oldest id) — assert it directly, since the
    admin here happens to be the older account and that would hide a regression."""
    from supporthub.app.services.plan_service import get_user_limit
    org = _sandbox()
    with session_scope(schema="public") as s:
        rows = s.execute(text(
            "SELECT id, role FROM public.users WHERE org_id=:o"
            " ORDER BY CASE WHEN role IN ('admin','platform_admin') THEN 0 ELSE 1 END, id"),
            {"o": org.id}).fetchall()
    assert rows[0].role in ("admin", "platform_admin"), (
        f"an agent outranked an admin for the last seat: {rows}")