"""Database integration for QuitSure Chatbot Onboarding.

Handles all MySQL writes:
- Clickstream tracking (tbl_ChatbotClickstream)
- User creation (tbl_Users, tbl_UserData, tbl_UserConfig, tbl_UserProfile, tbl_UserAppActivity)
- Attribution (tbl_UserSources)
- PII storage (quitsureUsers.tbl_UserInfo)
- Quiz data (tbl_ChatbotOnboarding)
"""
import os
import hashlib
import time
import random
import pymysql
from contextlib import contextmanager


# ── Connection helpers ──

def _get_db_config():
    return {
        "host": os.environ.get("DB_HOST", ""),
        "port": int(os.environ.get("DB_PORT", 3306)),
        "user": os.environ.get("DB_USERNAME", ""),
        "password": os.environ.get("DB_PASSWORD", ""),
        "database": os.environ.get("DB_DATABASE", "quitsure"),
        "charset": "utf8mb4",
        "cursorclass": pymysql.cursors.DictCursor,
    }


def _get_pii_db_config():
    return {
        "host": os.environ.get("DB_QSUSERINFO_HOST", ""),
        "port": int(os.environ.get("DB_QSUSERINFO_PORT", 3306)),
        "user": os.environ.get("DB_QSUSERINFO_USERNAME", ""),
        "password": os.environ.get("DB_QSUSERINFO_PASSWORD", ""),
        "database": os.environ.get("DB_QSUSERINFO_DATABASE", "quitsureUsers"),
        "charset": "utf8mb4",
        "cursorclass": pymysql.cursors.DictCursor,
    }


@contextmanager
def get_db():
    conn = pymysql.connect(**_get_db_config())
    try:
        yield conn
    finally:
        conn.close()


@contextmanager
def get_pii_db():
    conn = pymysql.connect(**_get_pii_db_config())
    try:
        yield conn
    finally:
        conn.close()


def db_enabled():
    """Check if DB credentials are configured."""
    return bool(os.environ.get("DB_HOST"))


# ── Helpers ──

def hash_email(email: str) -> str:
    """SHA256 hash of lowercase trimmed email. Must match V3/Razorpay format."""
    return hashlib.sha256(email.lower().strip().encode()).hexdigest()


def generate_access_token() -> str:
    """16 random digits. Matches V3 PHP: generateAccessToken()."""
    return ''.join([str(random.randint(0, 9)) for _ in range(16)])


def generate_ve_user_id(user_id: int) -> str:
    """base36(timestamp_ms)-last2digits(userId). Matches V3 PHP: generateVEUserID()."""
    timestamp_ms = int(time.time() * 1000)
    base36 = ''
    n = timestamp_ms
    while n > 0:
        base36 = '0123456789abcdefghijklmnopqrstuvwxyz'[n % 36] + base36
        n //= 36
    suffix = str(user_id)[-2:] if user_id >= 10 else str(user_id)
    return f"{base36}-{suffix}"


def age_to_range(age: int) -> str:
    """Convert exact age to range string matching tbl_UserData.vAge format."""
    if age < 18:
        return '15-17'
    elif age <= 24:
        return '18-24'
    elif age <= 34:
        return '25-34'
    elif age <= 50:
        return '35-50'
    else:
        return '50+'


def map_time_after_wake(minutes) -> str:
    """Convert first_cig_minutes to tbl_UserData.vTimeAfterWake format."""
    if minutes is None:
        return None
    m = int(minutes)
    if m <= 5:
        return 'within-5'
    elif m <= 30:
        return 'within-30'
    elif m <= 60:
        return 'within-60'
    elif m <= 120:
        return 'after-breakfast'
    else:
        return 'later'


def map_past_attempts(value) -> str:
    """Convert past_attempts_count number to V3 label."""
    if value is None or value == 0:
        return 'Never'
    v = float(value)
    if v <= 2:
        return '1-2 times'
    elif v <= 5:
        return '3-5 times'
    elif v <= 10:
        return '4-10 times'
    else:
        return 'Lost count'


def map_currency_to_code(symbol: str) -> str:
    """Convert currency symbol to ISO code matching V3 tbl_UserProfile.vCurrencyCode."""
    mapping = {
        '₹': 'INR', '$': 'USD', '£': 'GBP', '€': 'EUR',
        'A$': 'AUD', 'C$': 'CAD', 'NZ$': 'NZD', 'S$': 'SGD',
        'AED ': 'AED', 'AED': 'AED', 'R': 'ZAR',
    }
    return mapping.get(symbol, symbol)


COUNTRY_NAMES = {
    'IN': 'India', 'US': 'United States', 'GB': 'United Kingdom',
    'CA': 'Canada', 'AU': 'Australia', 'NZ': 'New Zealand',
    'SG': 'Singapore', 'AE': 'United Arab Emirates', 'ZA': 'South Africa',
    'DE': 'Germany', 'FR': 'France', 'ES': 'Spain', 'IT': 'Italy',
    'NL': 'Netherlands', 'BE': 'Belgium', 'AT': 'Austria', 'CH': 'Switzerland',
    'SE': 'Sweden', 'NO': 'Norway', 'DK': 'Denmark', 'FI': 'Finland',
    'IE': 'Ireland', 'PT': 'Portugal', 'PL': 'Poland', 'CZ': 'Czech Republic',
    'HU': 'Hungary', 'RO': 'Romania', 'GR': 'Greece', 'TR': 'Turkey',
    'RU': 'Russia', 'UA': 'Ukraine', 'BR': 'Brazil', 'MX': 'Mexico',
    'AR': 'Argentina', 'CO': 'Colombia', 'CL': 'Chile', 'PE': 'Peru',
    'JP': 'Japan', 'KR': 'South Korea', 'CN': 'China', 'TW': 'Taiwan',
    'HK': 'Hong Kong', 'TH': 'Thailand', 'MY': 'Malaysia', 'ID': 'Indonesia',
    'PH': 'Philippines', 'VN': 'Vietnam', 'BD': 'Bangladesh', 'PK': 'Pakistan',
    'LK': 'Sri Lanka', 'NP': 'Nepal', 'SA': 'Saudi Arabia', 'QA': 'Qatar',
    'KW': 'Kuwait', 'BH': 'Bahrain', 'OM': 'Oman', 'EG': 'Egypt',
    'NG': 'Nigeria', 'KE': 'Kenya', 'GH': 'Ghana', 'IL': 'Israel',
    'EU': 'Europe',
}


def country_code_to_name(code: str) -> str:
    """Convert ISO country code to full name matching V3 tbl_UserConfig.vCountry."""
    if not code:
        return None
    code = code.upper().strip()
    return COUNTRY_NAMES.get(code, code)


def to_v3_csv(values: list, label_map: dict) -> str:
    """Convert our chip values to V3 comma-separated human-readable labels.
    V3 stores: 'First thing in the morning,After meals,With coffee/tea'
    We store: ['morning', 'meals', 'coffee']
    """
    if not values:
        return None
    labels = []
    for v in values:
        if v in label_map:
            mapped = label_map[v]
            if mapped not in labels:  # dedup
                labels.append(mapped)
        else:
            # Free text or unmapped value → "Other"
            if 'Other' not in labels:
                labels.append('Other')
    return ','.join(labels) if labels else None


# V3 label maps — match exactly what prod tbl_UserData stores
TRIGGER_LABELS = {
    'morning': 'First thing in the morning',
    'coffee': 'With coffee/tea',
    'meals': 'After meals',
    'work': 'During work breaks',
    'evening': 'In the evenings, to unwind',
    'alcohol': 'When drinking alcohol',
    'social': 'While socializing',
    'stress': 'Other',
    'chain': 'Other',
}

# setSmokingDeceptions in V3 = "why they smoke" (emotional reasons)
# Maps from our Q8 likes_about_smoking chip values
DECEPTION_LABELS = {
    'relax': 'To relax',
    'social': 'To have fun / socialize',
    'break': 'To remove boredom',
    'focus': 'To help concentrate',
    'enjoy': 'Other',
    'reward': 'Other',
    'nothing': 'Other',
}

# Our denial patterns don't map to V3's setSmokingDeceptions
# They're stored in tbl_ChatbotOnboarding.vDenialPatterns instead

METHOD_LABELS = {
    'willpower': 'No',
    'nrt_gums': 'Nicotine Gums/Patches (NRTs)',
    'nrt_patches': 'Nicotine Gums/Patches (NRTs)',
    'vaping': 'Other',
    'medication': 'Varenicline, wellbutrin, etc',
    'hypnosis': 'Naturopathy/homeopathy/ayurveda',
    'reducing': 'No',
    'reducing_nic': 'Other',
    'none': 'No',
}

CONCERN_LABELS = {
    'withdrawal': 'Scared of the withdrawal symptoms/cravings',
    'weight': 'Worried about gaining weight',
    'stress': 'Concerned about my mental health worsening',
    'fail_again': 'Other',
    'identity': 'Worried about changing my daily routine',
    'social_miss': 'Afraid of losing friends',
    'not_ready': 'Other',
    'ready': 'Excited about regaining control',
}

RELAPSE_LABELS = {
    'stress_event': 'Stress/emotional trigger',
    'social_pressure': 'Social pressure',
    'alcohol': 'Alcohol',
    'withdrawal': 'Withdrawal was too hard',
    'just_one': 'Thought just one would be fine',
    'lost_focus': 'Lost focus/got busy',
}


# ── Clickstream ──

def write_clickstream_batch(session_id: str, events: list, device_type: str,
                            country: str, source: str, medium: str, campaign: str):
    """Write a batch of clickstream events to tbl_ChatbotClickstream."""
    if not db_enabled() or not events:
        return
    try:
        with get_db() as conn:
            with conn.cursor() as cur:
                for evt in events:
                    cur.execute("""
                        INSERT INTO tbl_ChatbotClickstream
                        (vSessionId, vStep, iStepNumber, vAnswer, iTimeSpentSec,
                         vDeviceType, vCountry, vSource, vMedium, vCampaign)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """, (
                        session_id,
                        evt.get("step", ""),
                        evt.get("stepNumber", 0),
                        str(evt.get("answer", ""))[:500],
                        evt.get("timeSpentSec", 0),
                        device_type,
                        country,
                        source[:500] if source else None,
                        medium[:100] if medium else None,
                        campaign[:200] if campaign else None,
                    ))
            conn.commit()
        print(f"[DB] Wrote {len(events)} clickstream events for {session_id[:8]}...")
    except Exception as e:
        print(f"[DB ERROR] Clickstream write failed: {e}")


# ── User Creation ──

def check_user_exists(email: str) -> dict:
    """Check if user with this email already exists. Returns user dict or None."""
    if not db_enabled():
        return None
    hashed = hash_email(email)
    try:
        with get_db() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT iUserID, bActive FROM tbl_Users WHERE vHashedEmail = %s AND bDeleted = 0",
                    (hashed,)
                )
                return cur.fetchone()
    except Exception as e:
        print(f"[DB ERROR] User check failed: {e}")
        return None


def create_user(data: dict) -> int:
    """Create a new user across 5 tables + PII DB. Returns iUserID or None.

    data should contain: name, email, age, smoker_status, isVaper, cigs_per_day,
    years_smoking, first_cig_minutes, triggers, likes, dislikes, denial_patterns,
    past_attempts, methods_tried, relapse_triggers, fears, deeper_whys,
    currency, price_per_cig, country, onboard_platform, utm_source, utm_medium, utm_campaign
    """
    if not db_enabled():
        return None

    email = data.get("email", "").lower().strip()
    name = data.get("name", "")
    hashed_email = hash_email(email)
    access_token = generate_access_token()
    hashed_token = hashlib.sha256(access_token.encode()).hexdigest()

    age = data.get("age", 0) or 0
    cigs = data.get("cigs_per_day", 0) or 0
    years = data.get("years_smoking", 0) or 0
    started_smoking = max(1, age - years) if age and years else None
    is_vaper = bool(data.get("isVaper", False))
    already_quit = data.get("smoker_status") == "quit_recent"
    country = country_code_to_name(data.get("country", "IN"))
    platform = data.get("onboard_platform", "web-chatbot-desktop-chrome")

    try:
        with get_db() as conn:
            with conn.cursor() as cur:
                # 1. tbl_Users
                random_ve = ''.join([str(random.randint(0, 9)) for _ in range(8)])
                cur.execute("""
                    INSERT INTO tbl_Users
                    (vEUserID, bActive, bDeleted, SmokingStatus,
                     vDeviceType, dDateCreated, vHashedEmail, vHashedToken)
                    VALUES (%s, 1, 0, %s, 'web', NOW(), %s, %s)
                """, (
                    random_ve,                    
                    'Free' if already_quit else 'Smoker',
                    hashed_email,
                    hashed_token,
                ))
                user_id = cur.lastrowid

                # Update vEUserID with proper format now that we have userId
                ve_user_id = generate_ve_user_id(user_id)
                cur.execute(
                    "UPDATE tbl_Users SET vEUserID = %s WHERE iUserID = %s",
                    (ve_user_id, user_id)
                )

                # 2. tbl_UserData
                import json as json_mod
                cur.execute("""
                    INSERT INTO tbl_UserData
                    (iUserId, vAge, vGoal, vPastQuitAttempts, setPastQuitTechniques,
                     setPastRelapseReasons, setSmokingDeceptions, setQuittingConcerns,
                     setSmokingTriggers, vTimeAfterWake, vOnBoardLang, vOnBoardPlatform)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, (
                    user_id,
                    age_to_range(age) if age else None,
                    'stay-free' if already_quit else 'quit',
                    map_past_attempts(data.get("past_attempts_count", 0)),
                    to_v3_csv(data.get("methods_tried", []), METHOD_LABELS),
                    to_v3_csv(data.get("relapse_triggers", []), RELAPSE_LABELS),
                    to_v3_csv(data.get("likes_about_smoking") or data.get("likes", []), DECEPTION_LABELS),
                    to_v3_csv(data.get("fears", []), CONCERN_LABELS),
                    to_v3_csv(data.get("triggers", []), TRIGGER_LABELS),
                    map_time_after_wake(data.get("first_cig_minutes")),
                    'English',
                    platform,
                ))

                # 3. tbl_UserConfig
                cur.execute("""
                    INSERT INTO tbl_UserConfig
                    (iUserID, vCountry, bOnBoardComplete, bEmailSubscribed,
                     IsSocialLogin, bTransactSubscribed)
                    VALUES (%s, %s, 0, 1, 0, 1)
                """, (
                    user_id,
                    country,
                ))

                # 4. tbl_UserProfile
                cur.execute("""
                    INSERT INTO tbl_UserProfile
                    (iUserID, vName, iStartedSmoking, iSmokingCigarettes, vMode,
                     decSmokePerDay, vCurrencyCode, bAlreadyQuit, setNicotineTypes,
                     iPayMode, vPayCurrency, decPayPrice)
                    VALUES (%s, %s, %s, %s, 'day', %s, %s, %s, %s, 1, %s, %s)
                """, (
                    user_id,
                    name,
                    started_smoking,
                    cigs,
                    float(cigs) if cigs else 0,
                    map_currency_to_code(data.get("currency_code") or data.get("currency", "INR")),
                    1 if already_quit else 0,
                    'vaping' if is_vaper else 'cigarettes',
                    data.get("pay_currency", "\u20B9"),
                    data.get("price_per_cig"),
                ))

                # 5. tbl_UserAppActivity
                cur.execute(
                    "INSERT INTO tbl_UserAppActivity (iUserID) VALUES (%s)",
                    (user_id,)
                )

                # 6. tbl_UserSources
                cur.execute("""
                    INSERT INTO tbl_UserSources
                    (iUserId, dDateCreated, vSourceUrl, vSource, vMedium, vCampaign, bSignup, vDeviceType)
                    VALUES (%s, NOW(), %s, %s, %s, %s, 1, 'web')
                """, (
                    user_id,
                    data.get("source_url") or "",
                    data.get("utm_source", "chatbot-onboarding"),
                    data.get("utm_medium", "web"),
                    data.get("utm_campaign", "chatbot"),
                ))

            conn.commit()

        # 7. PII DB — tbl_UserInfo
        try:
            with get_pii_db() as pii_conn:
                with pii_conn.cursor() as cur:
                    cur.execute("""
                        INSERT INTO tbl_UserInfo (iUserID, vEmail, vAPIToken)
                        VALUES (%s, %s, %s)
                    """, (user_id, email, access_token))
                pii_conn.commit()
        except Exception as e:
            print(f"[DB ERROR] PII write failed: {e}")

        print(f"[DB] Created user {user_id} (email={email[:3]}***)")
        return user_id

    except Exception as e:
        print(f"[DB ERROR] User creation failed: {e}")
        return None


def update_existing_user(user_id: int, data: dict):
    """Update existing user — last login, source, AND quiz data in existing tables."""
    if not db_enabled():
        return
    try:
        with get_db() as conn:
            with conn.cursor() as cur:
                # 1. tbl_Users — update login + program
                cur.execute("""
                    UPDATE tbl_Users SET dLastLogin = NOW(), bActive = 1, vDeviceType = 'web'
                    WHERE iUserID = %s
                """, (user_id,))

                # 2. tbl_UserSources — new attribution row
                cur.execute("""
                    INSERT INTO tbl_UserSources
                    (iUserId, dDateCreated, vSourceUrl, vSource, vMedium, vCampaign, bSignup, vDeviceType)
                    VALUES (%s, NOW(), %s, %s, %s, %s, 0, 'web')
                """, (
                    user_id,
                    data.get("source_url") or "",
                    data.get("utm_source", "chatbot-onboarding"),
                    data.get("utm_medium", "web"),
                    data.get("utm_campaign", "chatbot"),
                ))

                # 3. tbl_UserData — update quiz answers
                cur.execute("""
                    UPDATE tbl_UserData SET
                        vAge = %s,
                        vGoal = %s,
                        vPastQuitAttempts = %s,
                        setPastQuitTechniques = %s,
                        setPastRelapseReasons = %s,
                        setSmokingDeceptions = %s,
                        setQuittingConcerns = %s,
                        setSmokingTriggers = %s,
                        vTimeAfterWake = %s,
                        vOnBoardPlatform = %s
                    WHERE iUserId = %s
                """, (
                    age_to_range(data.get("age", 0)) if data.get("age") else None,
                    'quit',
                    map_past_attempts(data.get("past_attempts_count", 0)),
                    to_v3_csv(data.get("methods_tried", []), METHOD_LABELS),
                    to_v3_csv(data.get("relapse_triggers", []), RELAPSE_LABELS),
                    to_v3_csv(data.get("likes_about_smoking") or data.get("likes", []), DECEPTION_LABELS),
                    to_v3_csv(data.get("fears", []), CONCERN_LABELS),
                    to_v3_csv(data.get("triggers", []), TRIGGER_LABELS),
                    map_time_after_wake(data.get("first_cig_minutes")),
                    data.get("onboard_platform", "web-chatbot-desktop-chrome"),
                    user_id,
                ))

                # 4. tbl_UserProfile — update smoking details
                cigs = data.get("cigs_per_day", 0) or 0
                age = data.get("age", 0) or 0
                years = data.get("years_smoking", 0) or 0
                cur.execute("""
                    UPDATE tbl_UserProfile SET
                        vName = %s,
                        iSmokingCigarettes = %s,
                        decSmokePerDay = %s,
                        vCurrencyCode = %s,
                        vPayCurrency = %s,
                        decPayPrice = %s,
                        iStartedSmoking = %s,
                        setNicotineTypes = %s,
                        bAlreadyQuit = %s
                    WHERE iUserID = %s
                """, (
                    data.get("name", ""),
                    cigs,
                    float(cigs),
                    map_currency_to_code(data.get("currency_code") or data.get("currency", "INR")),
                    data.get("pay_currency") or data.get("currency", "\u20B9"),
                    data.get("price_per_cig"),
                    max(1, age - years) if age and years else None,
                    'vaping' if data.get("isVaper") else 'cigarettes',
                    1 if data.get("smoker_status") == "quit_recent" else 0,
                    user_id,
                ))

                # 5. tbl_UserConfig — update onboard platform
                cur.execute("""
                    UPDATE tbl_UserConfig SET
                        dDateUpdated = NOW()
                    WHERE iUserID = %s
                """, (user_id,))

            conn.commit()
        print(f"[DB] Updated existing user {user_id} (all tables)")
    except Exception as e:
        print(f"[DB ERROR] User update failed: {e}")


def mark_onboard_complete(user_id: int, monthly_cost: float = 0):
    """Set bOnBoardComplete=1 after results are shown."""
    if not db_enabled():
        return
    try:
        with get_db() as conn:
            with conn.cursor() as cur:
                cur.execute("""
                    UPDATE tbl_UserConfig
                    SET bOnBoardComplete = 1, dOnBoardCompleteTime = NOW(),
                        dProgramStartedOn = NOW(), vOnBoardLocation = NULL
                    WHERE iUserID = %s
                """, (user_id,))
                if monthly_cost > 0:
                    cur.execute(
                        "UPDATE tbl_UserProfile SET decCostPerMonth = %s WHERE iUserID = %s",
                        (monthly_cost, user_id)
                    )
            conn.commit()
        print(f"[DB] Marked onboard complete for user {user_id}")
    except Exception as e:
        print(f"[DB ERROR] Onboard complete failed: {e}")


# ── Quiz Data ──

def write_chatbot_onboarding(session_id: str, user_id: int, data: dict):
    """Write full quiz data to tbl_ChatbotOnboarding."""
    if not db_enabled():
        return
    import json as json_mod
    try:
        with get_db() as conn:
            with conn.cursor() as cur:
                cur.execute("""
                    INSERT INTO tbl_ChatbotOnboarding
                    (iUserID, vSessionId, vSmokerStatus, bIsVaper, vAgeRange, vCoreValues,
                     vYearsSmokingLabel, iCigsPerDay, vLikesAboutSmoking, vDislikesAboutSmoking,
                     vTriggers, iFirstCigMinutes, vCurrency, decPricePerCig, decMonthlySavings,
                     vDenialPatterns, iDenialCount, iPastAttempts, vRelapseTriggers, vMethodsTried,
                     vDeeperWhys, vBiggestFear, vFutureVision, vCommitment, iReadiness,
                     vSmokerType, iSuccessProbability, vProgram, vPersonalLetter, vOnBoardPlatform,
                     vCountry, vSource, vMedium, vCampaign, bCompleted)
                    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,0)
                """, (
                    user_id,
                    session_id,
                    data.get("smoker_status", ""),
                    1 if data.get("isVaper") else 0,
                    age_to_range(data.get("age", 0)) if data.get("age") else None,
                    json_mod.dumps(data.get("core_values", [])),
                    data.get("years_smoking_label", ""),
                    data.get("cigs_per_day"),
                    json_mod.dumps(data.get("likes_about_smoking", [])),
                    json_mod.dumps(data.get("dislikes_about_smoking", [])),
                    json_mod.dumps(data.get("triggers", [])),
                    data.get("first_cig_minutes"),
                    data.get("currency") or None,
                    data.get("price_per_cig"),
                    data.get("savings", {}).get("monthly"),
                    json_mod.dumps(data.get("denial_patterns", [])),
                    data.get("denial_count"),
                    data.get("past_attempts_count"),
                    json_mod.dumps(data.get("relapse_triggers", [])),
                    json_mod.dumps(data.get("methods_tried", [])),
                    json_mod.dumps(data.get("deeper_whys", [])),
                    data.get("biggest_fear") or None,
                    data.get("future_vision") or None,
                    data.get("commitment") or None,
                    data.get("readiness"),
                    data.get("profile", {}).get("smoker_type") or None,
                    data.get("profile", {}).get("success_probability"),
                    data.get("profile", {}).get("program", "six_day"),
                    data.get("_personalLetter") or data.get("profile", {}).get("_llm_letter") or None,
                    data.get("_onboard_platform") or None,
                    (data.get("_utm") or {}).get("country") or None,
                    (data.get("_utm") or {}).get("utm_source") or None,
                    (data.get("_utm") or {}).get("utm_medium") or None,
                    (data.get("_utm") or {}).get("utm_campaign") or None,
                ))
            conn.commit()
        print(f"[DB] Wrote chatbot onboarding for user {user_id}, session {session_id[:8]}...")
    except Exception as e:
        print(f"[DB ERROR] Onboarding write failed: {e}")


def mark_cta_clicked(session_id: str):
    """Set bCompleted=1 when user clicks CTA."""
    if not db_enabled():
        return
    try:
        with get_db() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "UPDATE tbl_ChatbotOnboarding SET bCompleted = 1 WHERE vSessionId = %s",
                    (session_id,)
                )
            conn.commit()
    except Exception as e:
        print(f"[DB ERROR] CTA mark failed: {e}")


# ── OTP for Existing Users ──

def generate_otp(email: str) -> str:
    """Generate 6-digit OTP. Fixed code for ram@quitsure.app (testing)."""
    if email.lower().strip() == "ram@quitsure.app":
        return "091081"
    return str(random.randint(100000, 999999))


def send_otp(email: str) -> str:
    """Generate OTP, save to tbl_Login, send via Postmark. Returns the OTP."""
    email = email.lower().strip()
    otp = generate_otp(email)

    if not db_enabled():
        print(f"[OTP] DB disabled, OTP for {email}: {otp}")
        return otp

    try:
        # 1. Expire previous OTPs for this email
        with get_db() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "UPDATE tbl_Login SET bExpired = 1 WHERE vEmail = %s AND bExpired = 0",
                    (email,)
                )
                # 2. Insert new OTP
                cur.execute("""
                    INSERT INTO tbl_Login (vEmail, vOTP, dDateCreated)
                    VALUES (%s, %s, NOW())
                """, (email, otp))
            conn.commit()

        # 3. Send via Postmark
        _send_postmark_otp(email, otp)

        print(f"[OTP] Sent to {email}")
        return otp

    except Exception as e:
        print(f"[OTP ERROR] Failed: {e}")
        return otp  # still return OTP so flow doesn't break


def verify_otp(email: str, user_otp: str) -> dict:
    """Verify OTP. Returns {'valid': bool, 'message': str}."""
    email = email.lower().strip()

    if not db_enabled():
        return {"valid": False, "message": "DB not available"}

    try:
        with get_db() as conn:
            with conn.cursor() as cur:
                # Find active OTP
                cur.execute("""
                    SELECT vOTP FROM tbl_Login
                    WHERE vEmail = %s AND bUsed = 0 AND bExpired = 0
                    ORDER BY dDateCreated DESC LIMIT 1
                """, (email,))
                row = cur.fetchone()

                if not row:
                    return {"valid": False, "message": "Your verification code has expired. We'll send a new one."}

                if row["vOTP"] != user_otp.strip():
                    return {"valid": False, "message": "Incorrect code. Please try again."}

                # Mark as used
                cur.execute(
                    "UPDATE tbl_Login SET bUsed = 1 WHERE vEmail = %s AND bUsed = 0 AND bExpired = 0",
                    (email,)
                )
            conn.commit()

        return {"valid": True, "message": "Verified"}

    except Exception as e:
        print(f"[OTP ERROR] Verify failed: {e}")
        return {"valid": False, "message": "Something went wrong. Please try again."}


def resend_otp(email: str) -> str:
    """Resend OTP — reuse existing if active, otherwise generate new."""
    email = email.lower().strip()

    if not db_enabled():
        return generate_otp(email)

    try:
        with get_db() as conn:
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT vOTP FROM tbl_Login
                    WHERE vEmail = %s AND bUsed = 0 AND bExpired = 0
                    ORDER BY dDateCreated DESC LIMIT 1
                """, (email,))
                row = cur.fetchone()

        if row:
            # Active OTP exists — resend same code
            _send_postmark_otp(email, row["vOTP"])
            print(f"[OTP] Resent existing to {email}")
            return row["vOTP"]
        else:
            # No active OTP — generate new
            return send_otp(email)

    except Exception as e:
        print(f"[OTP ERROR] Resend failed: {e}")
        return send_otp(email)


def _send_postmark_otp(email: str, otp: str):
    """Send OTP email via Postmark API."""
    import httpx

    token = os.environ.get("POSTMARK_TOKEN", "")
    from_email = os.environ.get("POSTMARK_FROM", "no-reply@quitsure.app")
    template_alias = os.environ.get("POSTMARK_OTP_TEMPLATE", "otp-template")

    if not token:
        print(f"[POSTMARK] No token, skipping email. OTP for {email}: {otp}")
        return

    try:
        resp = httpx.post(
            "https://api.postmarkapp.com/email/withTemplate",
            headers={
                "Accept": "application/json",
                "Content-Type": "application/json",
                "X-Postmark-Server-Token": token,
            },
            json={
                "From": from_email,
                "To": email,
                "TemplateAlias": template_alias,
                "TemplateModel": {"otp_code": otp},
                "Tag": "otp",
                "MessageStream": "outbound",
            },
            timeout=10.0,
        )
        if resp.status_code == 200:
            print(f"[POSTMARK] OTP email sent to {email}")
        else:
            print(f"[POSTMARK ERROR] Status {resp.status_code}: {resp.text[:200]}")
    except Exception as e:
        print(f"[POSTMARK ERROR] {e}")