"""Unit tests for db.py: pure normalization helpers + get_profile / upsert_session
logic (DB mocked, no real MySQL)."""
import db


def test_first_name():
    assert db._first_name("  john smith ") == "John"
    assert db._first_name("MARY") == "Mary"
    assert db._first_name("") is None
    assert db._first_name(None) is None
    assert db._first_name("   ") is None


def test_gender():
    assert db._gender("Male") == "male"
    assert db._gender("female") == "female"
    assert db._gender("Non-binary") is None
    assert db._gender("") is None
    assert db._gender(None) is None


def test_cigs_sanity_bounds():
    assert db._cigs("7") == 7
    assert db._cigs(7.4) == 7
    assert db._cigs(60) == 60
    assert db._cigs(0) is None     # below 1
    assert db._cigs(61) is None    # above 60
    assert db._cigs("abc") is None
    assert db._cigs(None) is None


def test_under_18():
    assert db._under_18("< 18 years") is True
    assert db._under_18("25-34") is False
    assert db._under_18(None) is False
    assert db._under_18("") is False


# ── get_profile: mock the DB connection so no MySQL is needed ──
class _FakeCur:
    def __init__(self, row):
        self._row = row

    def __enter__(self):
        return self

    def __exit__(self, *a):
        return False

    def execute(self, *a, **k):
        pass

    def fetchone(self):
        return self._row


class _FakeConn:
    def __init__(self, row):
        self._row = row

    def __enter__(self):
        return self

    def __exit__(self, *a):
        return False

    def cursor(self):
        return _FakeCur(self._row)


def _mock_row(monkeypatch, row):
    monkeypatch.setattr(db, "_conn", lambda: _FakeConn(row))


def test_get_profile_india(monkeypatch):
    _mock_row(monkeypatch, {
        "iUserID": 1, "vName": "Neel Raut", "decSmokePerDay": "7",
        "vCurrencyCode": "INR", "vGender": "male", "vAge": "25-34", "vCountry": "India",
    })
    p = db.get_profile("a" * 64)
    assert p["iUserID"] == 1
    assert p["name"] == "Neel"
    assert p["cigsPerDay"] == 7
    assert p["currency"] == "INR"
    assert p["gender"] == "male"
    assert p["eligible"] is True


def test_get_profile_usd(monkeypatch):
    _mock_row(monkeypatch, {
        "iUserID": 3, "vName": "Dave", "decSmokePerDay": "10",
        "vCurrencyCode": "USD", "vGender": "female", "vAge": "35-44", "vCountry": "United States",
    })
    p = db.get_profile("d" * 64)
    assert p["currency"] == "USD"
    assert p["gender"] == "female"


def test_get_profile_under18_not_eligible(monkeypatch):
    _mock_row(monkeypatch, {
        "iUserID": 2, "vName": "Kid", "decSmokePerDay": "5",
        "vCurrencyCode": "", "vGender": "male", "vAge": "< 18 years", "vCountry": "India",
    })
    p = db.get_profile("b" * 64)
    assert p["eligible"] is False


def test_get_profile_rejects_bad_hash():
    assert db.get_profile("short") is None
    assert db.get_profile("") is None
    assert db.get_profile(None) is None


def test_get_profile_miss(monkeypatch):
    _mock_row(monkeypatch, None)
    assert db.get_profile("c" * 64) is None


# ── upsert_session: server-side iUserID derivation + best-effort behavior ──
# A recording cursor: SELECT id (existing-row check) returns whatever we seed;
# the userid lookup returns the seeded uid row. It captures every executed SQL.
class _RecCur:
    def __init__(self, uid_row, existing_row):
        self._uid_row = uid_row
        self._existing_row = existing_row
        self._last_sql = ""
        self.executed = []

    def __enter__(self):
        return self

    def __exit__(self, *a):
        return False

    def execute(self, sql, params=None):
        self._last_sql = sql
        self.executed.append((sql, params))

    def fetchone(self):
        if "FROM tbl_Users" in self._last_sql:
            return self._uid_row
        if "FROM tbl_DiscoveryOnboarding" in self._last_sql:
            return self._existing_row
        return None


class _RecConn:
    def __init__(self, cur):
        self._cur = cur
        self.committed = False

    def __enter__(self):
        return self

    def __exit__(self, *a):
        return False

    def cursor(self):
        return self._cur

    def commit(self):
        self.committed = True


def _mock_upsert(monkeypatch, uid_row=None, existing_row=None):
    cur = _RecCur(uid_row, existing_row)
    conn = _RecConn(cur)
    monkeypatch.setattr(db, "_conn", lambda: conn)
    return cur


def test_upsert_session_requires_session_id():
    # No DB touched when session id is empty.
    assert db.upsert_session("", {"vName": "X"}) is False


def test_upsert_iuserid_is_server_side_not_from_body(monkeypatch):
    # Client tries to spoof iUserID=999 in the body; the hash resolves to 7.
    # The INSERT must carry 7, never 999, and iUserID must NOT come from the body.
    valid_hash = "a" * 64
    cur = _mock_upsert(monkeypatch, uid_row={"iUserID": 7}, existing_row=None)
    ok = db.upsert_session("s1", {"iUserID": 999, "vHashedEmail": valid_hash, "vName": "Spoofer"})
    assert ok is True
    insert = [e for e in cur.executed if e[0].startswith("INSERT INTO tbl_DiscoveryOnboarding")]
    assert len(insert) == 1
    sql, params = insert[0]
    assert "`iUserID`" in sql
    assert 7 in params            # authoritative uid captured
    assert 999 not in params      # spoofed client value ignored


def test_upsert_first_write_captures_userid(monkeypatch):
    # First write (no existing row) inserts and captures the resolved uid.
    cur = _mock_upsert(monkeypatch, uid_row={"iUserID": 42}, existing_row=None)
    ok = db.upsert_session("s2", {"vHashedEmail": "b" * 64, "bReachedOffer": 1})
    assert ok is True
    insert = [e for e in cur.executed if e[0].startswith("INSERT INTO tbl_DiscoveryOnboarding")]
    assert insert and 42 in insert[0][1]


def test_upsert_no_hash_omits_userid(monkeypatch):
    # Ad traffic has no hash: uid lookup returns None, no iUserID written.
    cur = _mock_upsert(monkeypatch, uid_row=None, existing_row=None)
    ok = db.upsert_session("s3", {"vName": "Anon"})
    assert ok is True
    insert = [e for e in cur.executed if e[0].startswith("INSERT INTO tbl_DiscoveryOnboarding")]
    assert insert and "`iUserID`" not in insert[0][0]


def test_upsert_existing_row_updates(monkeypatch):
    # An existing session id triggers UPDATE, not INSERT.
    cur = _mock_upsert(monkeypatch, uid_row={"iUserID": 5}, existing_row={"id": 11})
    ok = db.upsert_session("s4", {"vHashedEmail": "c" * 64, "bConverted": 1})
    assert ok is True
    assert any(e[0].startswith("UPDATE tbl_DiscoveryOnboarding") for e in cur.executed)
    assert not any(e[0].startswith("INSERT INTO tbl_DiscoveryOnboarding") for e in cur.executed)


def test_upsert_fails_open_on_db_error(monkeypatch):
    def boom():
        raise RuntimeError("db down")
    monkeypatch.setattr(db, "_conn", boom)
    # Never raises; returns False so the funnel keeps working.
    assert db.upsert_session("s5", {"vName": "X"}) is False


def test_authoritative_user_id_rejects_bad_hash():
    # Bad-length hash: never runs a query, returns None.
    calls = []

    class _C:
        def execute(self, *a, **k):
            calls.append(a)

        def fetchone(self):
            return {"iUserID": 1}

    assert db._authoritative_user_id(_C(), "short") is None
    assert db._authoritative_user_id(_C(), None) is None
    assert calls == []


def test_authoritative_user_id_returns_uid_for_valid_hash():
    class _C:
        def execute(self, *a, **k):
            pass

        def fetchone(self):
            return {"iUserID": 88}

    assert db._authoritative_user_id(_C(), "a" * 64) == 88


# ── insert_clickstream: guard rails + fail-open ──
def test_insert_clickstream_empty_is_noop():
    assert db.insert_clickstream("", [{"step": "x"}]) == 0
    assert db.insert_clickstream("s", []) == 0


def test_insert_clickstream_fails_open_on_db_error(monkeypatch):
    def boom():
        raise RuntimeError("table missing")
    monkeypatch.setattr(db, "_conn", boom)
    assert db.insert_clickstream("s", [{"step": "hook", "stepNumber": 0}]) == 0


def test_insert_clickstream_inserts(monkeypatch):
    cur = _mock_upsert(monkeypatch)

    def fake_executemany(sql, rows):
        cur.executed.append(("executemany", rows))
    cur.executemany = fake_executemany
    n = db.insert_clickstream("s", [{"step": "hook", "stepNumber": 0, "timeSpentSec": 2}])
    assert n == 1


# ── ping: reachability ──
def test_ping_true(monkeypatch):
    _mock_upsert(monkeypatch)
    assert db.ping() is True


def test_ping_false_on_error(monkeypatch):
    def boom():
        raise RuntimeError("unreachable")
    monkeypatch.setattr(db, "_conn", boom)
    assert db.ping() is False