# Chatbot Onboarding — Implementation Spec

## For BE Team Review & Validation
## Everything below is what WE (chatbot team) will implement. We need your confirmation that the approach is correct and doesn't conflict with existing systems.

---

## How This Document Works

We've written down exactly what we plan to do — every table we'll create, every existing table we'll write to, every field we'll populate. 

**Please review and comment:**
- ✅ = Correct, go ahead
- ❌ = Wrong, here's what it should be
- ⚠️ = Needs discussion

---

## The Flow — What Happens Step by Step

### Step 1: User lands on chatbot page

User comes from a Google/Meta ad. URL looks like:
```
https://tweb.quitsure.app/chatbot-onboarding?utm_source=google&utm_medium=cpc&utm_campaign=india_may2026
```

**What we do:** We capture the UTM params from the URL and store them in the browser's memory. We also generate a unique session ID (random string like `cs_1715234567_abc123`) that ties all this user's events together.

We INSERT one row into our new `tbl_ChatbotClickstream` table:

| Field | Value | Why |
|-------|-------|-----|
| vSessionId | `cs_1715234567_abc123` | Unique per quiz session. This is how we link all events from the same user together. |
| vStep | `page_load` | They landed on the page but haven't started yet |
| vAnswer | NULL | No answer at this point |
| iTimeSpentSec | 0 | |
| vDeviceType | `web-chatbot-android-browser` | Detected from browser user agent. See "Device Detection" section below. |
| vCountry | `IN` | Detected from browser timezone (Asia/Kolkata → IN) |
| vSource | `google` | From utm_source in URL |
| vMedium | `cpc` | From utm_medium in URL |
| vCampaign | `india_may2026` | From utm_campaign in URL |

**BE team: Is this okay?** We're creating a new table for this. Schema below.

---

### Step 2: User clicks "Start My Assessment"

Another row goes into `tbl_ChatbotClickstream`:

| Field | Value |
|-------|-------|
| vSessionId | Same session ID as above |
| vStep | `quiz_start` |

Now we can measure: how many people landed vs how many actually started.

---

### Step 3: User answers quiz questions (Q1 through Q5)

Each answer is stored in the browser's memory (JavaScript array). We do NOT write to the database after every single question — that would be too many API calls.

Instead, we collect answers locally:
```
[
  { step: "q1_name", answer: "Neel", timeSpent: 8 },
  { step: "q2_status", answer: "regular", timeSpent: 3 },
  { step: "q3_age", answer: "26", timeSpent: 5 },
  { step: "q5_values", answer: "family,health", timeSpent: 12 },
]
```

**After Q5 (the values question), we FLUSH this batch to the database.** One API call inserts ~5 rows into `tbl_ChatbotClickstream`, all with the same `vSessionId`.

**Why Q5?** If someone drops off before Q5, they weren't serious (less than 1 minute in). After Q5, they've invested 2+ minutes and answered meaningful questions — worth tracking.

**If user closes the tab before Q5:** We lose Q1-Q5 data. We only have the `page_load` and `quiz_start` rows. We know they started but not what they answered.

---

### Step 4: User continues quiz (Q6 through email)

Same pattern — answers collected locally in the browser. Questions include:
- Q6: How long they've been smoking
- Q8: What they like about smoking
- Q11: First cigarette after waking
- Q12: How many cigarettes in last 24 hours
- Q13: Triggers
- Q14: Cost per cigarette
- Q15: Denial patterns
- Q16: Past quit attempts
- Q17: Methods tried

Plus several "reveal" moments where Quira shows them insights (cost card, denial card, method comparison).

**After the email gate, we FLUSH this entire batch.** That's ~15 rows inserted into `tbl_ChatbotClickstream` with the same `vSessionId`.

---

### Step 5: User enters their email — THIS IS THE BIG MOMENT

This is where we create the user in QuitSure's system. We're mirroring what V3 web onboarding does after OTP verification, except we're skipping OTP (see "OTP Decision" section below).

**We make the following database writes:**

#### 5a. INSERT into `tbl_Users` (new user row)

| Field | Value | Notes |
|-------|-------|-------|
| iUserID | AUTO_INCREMENT | Generated by MySQL |
| vEUserID | UUID we generate | **BE TEAM: What format do you use for this? Is it a standard UUID or a custom format? We need to match exactly.** |
| iActiveDayID | ? | **BE TEAM: What is the ACTIVE3DAYID constant for Program 3? We need this value.** |
| bActive | 1 | User is active immediately |
| bDeleted | 0 | |
| dLastLogin | NOW() | |
| SmokingStatus | 'Smoker' | Default. 'Free' if they selected "I quit recently" |
| iProgramID | 3 | 6-day program |
| vDeviceType | 'web' | Same as V3 web flow |
| dDateCreated | NOW() | |
| vHashedEmail | SHA256 of lowercase email | **BE TEAM: Is SHA256(lowercase(trim(email))) the correct hashing? The Razorpay success page looks up users by vHashedEmail — if our hash doesn't match, it will create a DUPLICATE user. This is critical to verify.** |
| bTestUser | 0 | |

#### 5b. INSERT into `tbl_UserData` (onboarding answers)

| Field | Value | Where it comes from in our chatbot |
|-------|-------|-----------------------------------|
| iUserId | from tbl_Users | |
| vAge | '15-24' or '25-34' or '35-50' or '50+' | We convert the exact age to a range. User enters "26", we store "25-34". This matches how V3 stores it. |
| vGoal | 'quit' | Mapped from smoker_status. If 'quit_recent' then goal = 'stay-free' |
| vPastQuitAttempts | '0' / '1-2' / '3-5' / '6+' / 'lost-count' | From Q16 (past attempts question) |
| setPastQuitTechniques | '["willpower","nrt_patches"]' | JSON array from Q17 (methods tried) |
| setPastRelapseReasons | '["stress_event","alcohol"]' | JSON array from Q16b (what made them go back) |
| setSmokingDeceptions | '["can_quit_anytime","handles_stress"]' | JSON array from Q15 (denial patterns) |
| setQuittingConcerns | '["withdrawal","weight"]' | JSON array from Q19 (fears about quitting) |
| setSmokingTriggers | '["morning","coffee","stress"]' | JSON array from Q13 (triggers) |
| vTimeAfterWake | 'within-5' / 'within-30' / 'within-60' / 'after-breakfast' / 'later' | From Q11. Mapped from chip values. |
| vOnBoardLang | 'ENG' | English only for now |
| vOnBoardPlatform | 'web-chatbot-android-browser' (example) | See Device Detection section |

**Fields we leave NULL:** vGender, setHealthIssues, vQuitDecision, vHeardAboutUs, iResistGoingToPlaces, iSmokeWhenSick, vFaveCigarette, iHideSmoking, iAddictionScore, iPersonalityTypeId, vColdTurkey, iGuilt, setHesitations, vExtendedLastUrl, vRelationship, vEnvironment, vRegret, vInterferes, vEscape, vSeek, vExceed

**BE TEAM: Are we missing any required fields? Is it okay to leave these as NULL?**

#### 5c. INSERT into `tbl_UserConfig`

| Field | Value | Notes |
|-------|-------|-------|
| iUserID | from tbl_Users | |
| vCountry | 'IN' (or detected) | From browser timezone |
| bOnBoardComplete | 0 | We set this to 1 LATER, after results are shown (Step 6) |
| dDownloadDate | NOW() | Treating chatbot entry as "download" equivalent |
| bEmailSubscribed | 1 | They gave us their email willingly |
| bVerified | 0 | No OTP verification done |
| IsSocialLogin | 0 | Not a social login |
| dUTCOffset | Browser's UTC offset | e.g. '+05:30' for India |

**Fields we leave NULL/default:** vDeviceID, vFreshChatToken, vAppVersion, vOnBoardLocation, dDateUpdated, bHasUnusedVoucher (0), bHasRated (0), bNotification, bIosFbTrack, SocialLoginType, vRecordID, bTransactSubscribed (1), vExtendedUrl, bExtendedComplete (0), dExtendedCompleteTime, dProgramStartedOn (set later), bDiscourseAccount (0), vDeviceDetails, bSubBypass (0), bUninstalled (0), vDeviceModel, vDeviceOS, iDeviceCategory, vSubIntent

#### 5d. INSERT into `tbl_UserProfile`

| Field | Value | Where from |
|-------|-------|-----------|
| iUserID | from tbl_Users | |
| vName | 'Neel' | From Q1 |
| iStartedSmoking | 16 (calculated: age - years_smoking) | If age=26 and years=10, started at 16 |
| iSmokingCigarettes | 18 | From Q12 (last 24h count) |
| vMode | 'day' | |
| decSmokePerDay | 18.0 | Same as iSmokingCigarettes |
| vCurrencyCode | 'INR' | From Q14 currency selection |
| bAlreadyQuit | 0 | 1 if smoker_status = 'quit_recent' |
| setNicotineTypes | 'cigarettes' or 'vaping' | From Q2 |

#### 5e. INSERT into `tbl_UserAppActivity`

| Field | Value |
|-------|-------|
| iUserID | from tbl_Users |
| (all other fields) | Default / NULL |

**BE TEAM: What are the required fields for tbl_UserAppActivity? V3 doc says "create with default values" but doesn't specify which columns exist.**

#### 5f. INSERT into `tbl_UserSources` (attribution)

| Field | Value | Notes |
|-------|-------|-------|
| iUserId | from tbl_Users | |
| dDateCreated | NOW() | |
| vSourceUrl | Full chatbot URL with all params | The original URL they came from |
| vSource | From utm_source, or 'chatbot-onboarding' if no UTM | |
| vMedium | From utm_medium, or 'web' | |
| vCampaign | From utm_campaign, or 'chatbot' | |
| bSignup | 1 | |

#### 5g. INSERT into `quitsureUsers.tbl_UserInfo` (PII database)

| Field | Value |
|-------|-------|
| iUserID | from tbl_Users |
| vEmail | Plaintext email (lowercase, trimmed) |
| vName | User's name |

**BE TEAM: Does tbl_UserInfo have any other required fields we need to populate?**

#### 5h. Edge case: Email already exists

Before creating a new user, we check:
```sql
SELECT iUserID FROM tbl_Users WHERE vHashedEmail = ? AND bDeleted = 0
```

If a user with this email already exists:
- We do NOT create a duplicate
- We UPDATE their existing row: `dLastLogin = NOW(), bActive = 1`
- We still INSERT a new `tbl_UserSources` row (new attribution entry)
- We still INSERT `tbl_ChatbotOnboarding` linked to their existing iUserID
- We proceed with the rest of the flow using their existing iUserID

**BE TEAM: Is this the right approach for existing users? Or should we do something different?**

---

### Step 6: User sees results page

After email capture, the chatbot shows personalized results (smoker type, assessment letter, program recommendation, etc.).

**We make these UPDATES:**

```sql
UPDATE tbl_UserConfig 
SET bOnBoardComplete = 1, 
    dOnBoardCompleteTime = NOW(),
    dProgramStartedOn = NOW()
WHERE iUserID = ?
```

```sql
UPDATE tbl_UserProfile 
SET decCostPerMonth = ?  -- calculated from Q14 (price × cigs/day × 30)
WHERE iUserID = ?
```

We also INSERT the full quiz data into our new `tbl_ChatbotOnboarding` table (schema below), linked to the iUserID we created in Step 5.

---

### Step 7: User clicks "Unlock My Personalized Plan" (CTA)

**What happens:**
1. Final clickstream batch flushed to `tbl_ChatbotClickstream` (step = 'cta_clicked')
2. UPDATE `tbl_ChatbotOnboarding SET bCompleted = 1` for this session
3. Browser redirects to: `https://program.quitsure.app/v3/eng/direct?link-source=chatbot-onboarding&program=3`

**We do NOT handle payment.** The existing Razorpay "direct" page handles everything from here.

---

### Step 8: Razorpay payment (existing system — NOT us)

The user lands on the existing V3 Razorpay page. When they pay:

The success page should:
1. Look up user by hashed email → **find the user WE created in Step 5**
2. Update subscription: `bSubscribed = 1, bPreviouslySubscribed = 1` in `tbl_UserPrograms`
3. Create `tbl_UserSubHistory` row
4. Create SSO token + Branch deep link
5. User opens app, auto-logged in

**BE TEAM: Will the existing Razorpay success page correctly find users created by our chatbot? The key is that `vHashedEmail` in `tbl_Users` matches what Razorpay/Stripe sends. Please confirm the hashing algorithm.**

**BE TEAM: The success page code has this logic:**
```
IF user with this email ALREADY exists:
    Update their subscription
ELSIF $StripeEmail is not null:
    CREATE NEW USER
```
**Since we already created the user at email capture (Step 5), the success page should take the FIRST path (user exists). It should NOT create a duplicate. Please confirm this will work correctly.**

---

## New Tables We Need Created

**We have INSERT/UPDATE/DELETE permissions but NOT CREATE TABLE. BE team needs to run these CREATE statements.**

### Table 1: `tbl_ChatbotClickstream`

This tracks every quiz step. Multiple rows per user session, linked by `vSessionId`.

```sql
CREATE TABLE tbl_ChatbotClickstream (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  vSessionId VARCHAR(50) NOT NULL,     -- links all events from same user
  vStep VARCHAR(30) NOT NULL,          -- e.g. 'page_load', 'q1_name', 'email_captured'
  iStepNumber INT DEFAULT 0,          -- sequential order (0, 1, 2...)
  vAnswer TEXT,                        -- what they answered (could be JSON for multi-select)
  iTimeSpentSec INT DEFAULT 0,        -- how long they spent on this step
  vDeviceType VARCHAR(50),            -- e.g. 'web-chatbot-android-browser'
  vCountry VARCHAR(50),               -- e.g. 'IN'
  vSource VARCHAR(500),               -- utm_source or 'chatbot-onboarding'
  vMedium VARCHAR(100),               -- utm_medium or 'web'
  vCampaign VARCHAR(200),             -- utm_campaign
  dDateCreated DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_session (vSessionId),
  INDEX idx_step (vStep),
  INDEX idx_created (dDateCreated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

**How to read this table:**

To see one user's complete journey:
```sql
SELECT vStep, vAnswer, iTimeSpentSec 
FROM tbl_ChatbotClickstream 
WHERE vSessionId = 'cs_1715234567_abc123' 
ORDER BY iStepNumber;
```
Result:
```
page_load       | NULL           | 0s
quiz_start      | NULL           | 15s (time on landing page)
q1_name         | Neel           | 8s
q2_status       | regular        | 3s
q3_age          | 26             | 5s
q5_values       | family,health  | 12s
q6_years        | 10-20 years    | 4s
...
email_captured  | n***@g***.com  | 20s
results         | NULL           | 45s (time reading results)
cta_clicked     | NULL           | 5s
```

**UTM/device/country only stored on the first row (page_load).** Subsequent rows leave these NULL to save space. To get a session's UTM data, join on `vSessionId` where `vStep = 'page_load'`.

**Expected volume:**
- ~20 rows per completed user
- ~5 rows per early drop-off  
- At 1,000 users/day ≈ 10,000-20,000 rows/day
- At 10,000 users/day ≈ 100,000-200,000 rows/day
- Monthly cleanup: archive sessions older than 90 days

### Table 2: `tbl_ChatbotOnboarding`

One row per user who reaches the results page. Contains the full quiz as a single record (easier to query than the clickstream for analytics).

```sql
CREATE TABLE tbl_ChatbotOnboarding (
  id INT AUTO_INCREMENT PRIMARY KEY,
  iUserID INT NULL,                    -- from tbl_Users (set at email capture)
  vSessionId VARCHAR(50),              -- links to tbl_ChatbotClickstream
  vSmokerStatus VARCHAR(20),           -- regular/vaper/occasional/quit_recent
  bIsVaper TINYINT DEFAULT 0,
  vAgeRange VARCHAR(10),               -- '15-24', '25-34', '35-50', '50+'
  vCoreValues TEXT,                    -- JSON: ["family","health"]
  vYearsSmokingLabel VARCHAR(50),
  iCigsPerDay INT,
  vLikesAboutSmoking TEXT,             -- JSON
  vDislikesAboutSmoking TEXT,          -- JSON
  vTriggers TEXT,                      -- JSON
  iFirstCigMinutes INT,
  vCurrency VARCHAR(10),
  decPricePerCig DECIMAL(10,2),
  decMonthlySavings DECIMAL(10,2),
  vDenialPatterns TEXT,                -- JSON
  iDenialCount INT,
  iPastAttempts INT,
  vRelapseTriggers TEXT,               -- JSON
  vMethodsTried TEXT,                  -- JSON
  vDeeperWhys TEXT,                    -- JSON
  vBiggestFear VARCHAR(50),
  vFutureVision TEXT,
  vCommitment VARCHAR(20),
  iReadiness INT,
  vSmokerType VARCHAR(20),            -- stress/social/habitual/emotional/reward/identity
  iSuccessProbability INT,
  vProgram VARCHAR(20),
  vPersonalLetter TEXT,               -- AI-generated assessment letter
  vOnBoardPlatform VARCHAR(50),       -- same as tbl_UserData.vOnBoardPlatform
  vCountry VARCHAR(50),
  vSource VARCHAR(500),
  vMedium VARCHAR(100),
  vCampaign VARCHAR(200),
  dDateCreated DATETIME DEFAULT CURRENT_TIMESTAMP,
  bCompleted TINYINT DEFAULT 0,       -- 1 = clicked CTA, 0 = saw results but didn't click
  INDEX idx_created (dDateCreated),
  INDEX idx_type (vSmokerType),
  INDEX idx_user (iUserID),
  INDEX idx_session (vSessionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

---

## Device Detection

We detect the device from the browser's `navigator.userAgent`:

| What we detect | vOnBoardPlatform value | vDeviceType (tbl_Users) |
|---------------|----------------------|------------------------|
| Android phone browser | `web-chatbot-android-browser` | `web` |
| iPhone browser | `web-chatbot-ios-browser` | `web` |
| Desktop browser | `web-chatbot-desktop-browser` | `web` |
| Instagram/Facebook/TikTok in-app browser | `web-chatbot-android-instagram` (or ios, or facebook, etc.) | `web` |

This follows the same pattern as V3 web onboarding but with `chatbot` instead of `view`, so chatbot users are distinguishable in analytics from regular V3 web users.

`tbl_Users.vDeviceType` is always `'web'` (same as V3).
`tbl_UserData.vOnBoardPlatform` has the detailed platform string.

**BE TEAM: Does this naming convention work? V3 uses patterns like `web-view-android-browser`, `web-mobile-android-browser`. We're proposing `web-chatbot-android-browser`. Any conflicts?**

---

## OTP Decision: We Are SKIPPING OTP

V3 web onboarding sends an OTP to the user's email, they enter it, and only then the user is created. We are NOT doing this.

**Why:**
1. The chatbot is a conversational flow. Asking someone to go to their email inbox, find a 6-digit code, come back and type it — this completely breaks the chat experience and would lose 20-30% of users.
2. We set `tbl_UserConfig.bVerified = 0` for chatbot users so they can be distinguished from OTP-verified V3 users.
3. The Razorpay payment acts as email verification — if they pay, the email is real.
4. For users who enter email but don't pay, we can run a cleanup cron later (delete unverified users with no subscription after 30 days).

**BE TEAM: Is skipping OTP acceptable? Any security or data quality concerns we should know about?**

---

## What the Razorpay Page Handles (NOT us)

The existing `program.quitsure.app/v3/eng/direct` page handles:
- Showing pricing (monthly + semi-annual plans)
- Processing Razorpay payment
- On success: creating/updating `tbl_UserPrograms` (subscription)
- Creating `tbl_UserSubHistory` row
- Creating `tbl_UserSubscriptions` row
- Creating SSO token in `tbl_UserSSO`
- Generating Branch deep link for auto-login in app
- Sending welcome email

**We pass `link-source=chatbot-onboarding` in the URL.** The direct page uses this to look up `tbl_WebLoginDiscount` for discount configuration.

**BE TEAM: Does a row exist in `tbl_WebLoginDiscount` for `vLinkSource = 'chatbot-onboarding'`? If not, it needs to be created with the discount/product configuration you want chatbot users to see.**

---

## Summary of ALL Database Writes

| When | Table | Operation | Who does it |
|------|-------|-----------|-------------|
| Page load | tbl_ChatbotClickstream | INSERT (page_load) | Us |
| Quiz start | tbl_ChatbotClickstream | INSERT (quiz_start) | Us |
| After Q5 | tbl_ChatbotClickstream | INSERT batch (~5 rows) | Us |
| Email capture | tbl_ChatbotClickstream | INSERT batch (~15 rows) | Us |
| Email capture | tbl_Users | INSERT new user | Us |
| Email capture | tbl_UserData | INSERT | Us |
| Email capture | tbl_UserConfig | INSERT | Us |
| Email capture | tbl_UserProfile | INSERT | Us |
| Email capture | tbl_UserAppActivity | INSERT | Us |
| Email capture | tbl_UserSources | INSERT | Us |
| Email capture | quitsureUsers.tbl_UserInfo | INSERT (PII) | Us |
| Results shown | tbl_UserConfig | UPDATE (bOnBoardComplete=1) | Us |
| Results shown | tbl_ChatbotOnboarding | INSERT full quiz data | Us |
| CTA clicked | tbl_ChatbotClickstream | INSERT batch (~3 rows) | Us |
| CTA clicked | tbl_ChatbotOnboarding | UPDATE (bCompleted=1) | Us |
| Payment success | tbl_UserPrograms | INSERT/UPDATE subscription | Razorpay page (existing) |
| Payment success | tbl_UserSubHistory | INSERT | Razorpay page (existing) |
| Payment success | tbl_UserSSO | INSERT (SSO token) | Razorpay page (existing) |

---

## Questions for BE Team

1. **vEUserID format** — How is this generated? Standard UUID? Custom format? We need to generate it the same way.

2. **ACTIVE3DAYID for Program 3** — What value should `iActiveDayID` be set to for a new Program 3 user?

3. **Email hashing** — Is `SHA256(lowercase(trim(email)))` correct? The Razorpay success page looks up users by `vHashedEmail`. If our hash doesn't match, it creates a duplicate user.

4. **tbl_UserAppActivity required fields** — What columns exist and which are required?

5. **tbl_WebLoginDiscount entry** — Does a row exist for `vLinkSource = 'chatbot-onboarding'`? If not, what discount/product should be configured?

6. **Existing user handling** — If someone already has a QuitSure account with the same email, we plan to update their `dLastLogin` and add a new `tbl_UserSources` row, but NOT create a duplicate. Is this correct?

7. **bVerified = 0** — We skip OTP and set `bVerified = 0`. Does anything in the system break if this is 0? Any cron jobs or features that check this?

8. **tbl_UserInfo fields** — Does `quitsureUsers.tbl_UserInfo` have columns beyond `iUserID`, `vEmail`, `vName`? Do we need to populate anything else?

9. **AccessToken** — V3 generates an `vAccessToken` after user creation. Do we need to generate one? The chatbot doesn't use it, but does any other part of the system expect it to exist?

10. **vOnBoardPlatform naming** — We propose `web-chatbot-android-browser` / `web-chatbot-ios-browser` / `web-chatbot-desktop-browser`. Any conflicts with existing naming?

---

## Environment Configuration

Our `.env` file follows the same structure as other projects:

```
# Gemini (LLM)
GEMINI_API_KEY=xxxxx

# Main DB
DB_HOST=qs-test-dec2025.cromn2cmgjti.ap-south-1.rds.amazonaws.com
DB_PORT=3306
DB_DATABASE=quitsure
DB_USERNAME=qs-neel
DB_PASSWORD=xxxxx

# PII DB
DB_QSUSERINFO_HOST=qs-test-dec2025.cromn2cmgjti.ap-south-1.rds.amazonaws.com
DB_QSUSERINFO_PORT=3306
DB_QSUSERINFO_DATABASE=quitsureUsers
DB_QSUSERINFO_USERNAME=qs-neel
DB_QSUSERINFO_PASSWORD=xxxxx

# Environment
ENV=test
```

Production will have different host/credentials (to be provided by BE team when ready).