
    jw                        d Z ddlZddlZddlZddlZddlZddlmZ d Zd Z	ed        Z
ed        Zd Zd	ed
efdZd
efdZded
efdZded
efdZd
efdZd
efdZded
efdZi dddddddddddd d!d"d#d$d%d&d'd(d)d*d+d,d-d.d/d0d1d2d3d4d5d6i d7d8d9d:d;d<d=d>d?d@dAdBdCdDdEdFdGdHdIdJdKdLdMdNdOdPdQdRdSdTdUdVdWdXi dYdZd[d\d]d^d_d`dadbdcdddedfdgdhdidjdkdldmdndodpdqdrdsdtdudvdwdxdydzd{d|d}d~ddddddddZded
efdZdeded
efdZdddddddddd	ZddddddddZdddddddddd	ZdddddddddZdddddddZdededededededefdZ d	ed
efdZ!ded
efdZ"dedefdZ#ddede$fdZ%dededefdZ&defdZ'd	ed
efdZ(d	ed
efdZ)d	eded
efdZ*d	ed
efdZ+d	edefdÄZ,y)aP  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)
    N)contextmanagerc                     t         j                  j                  dd      t        t         j                  j                  dd            t         j                  j                  dd      t         j                  j                  dd      t         j                  j                  dd      d	t        j
                  j                  d
S )NDB_HOST DB_PORT  DB_USERNAMEDB_PASSWORDDB_DATABASEquitsureutf8mb4hostportuserpassworddatabasecharsetcursorclassosenvirongetintpymysqlcursors
DictCursor     1/var/www/python/quitsure-chatbot-onboarding/db.py_get_db_configr!      s{    

y"-BJJNN9d34

}b1JJNN="5JJNN=*=11 r   c                     t         j                  j                  dd      t        t         j                  j                  dd            t         j                  j                  dd      t         j                  j                  dd      t         j                  j                  dd      d	t        j
                  j                  d
S )NDB_QSUSERINFO_HOSTr   DB_QSUSERINFO_PORTr   DB_QSUSERINFO_USERNAMEDB_QSUSERINFO_PASSWORDDB_QSUSERINFO_DATABASEquitsureUsersr   r   r   r   r   r    _get_pii_db_configr)       s    

3R8BJJNN#7>?

7<JJNN#;R@JJNN#;_M11 r   c               #      K   t        j                  di t               } 	 |  | j                          y # | j                          w xY wwNr   )r   connectr!   closeconns    r    get_dbr0   ,   s6     ??.^-.D




   A6 AAAc               #      K   t        j                  di t               } 	 |  | j                          y # | j                          w xY wwr+   )r   r,   r)   r-   r.   s    r    
get_pii_dbr3   5   s7     ??2/12D




r1   c                  R    t        t        j                  j                  d            S )z'Check if DB credentials are configured.r   )boolr   r   r   r   r   r    
db_enabledr6   >   s    

y)**r   emailreturnc                     t        j                  | j                         j                         j	                               j                         S )zFSHA256 hash of lowercase trimmed email. Must match V3/Razorpay format.)hashlibsha256lowerstripencode	hexdigestr7   s    r    
hash_emailrA   E   s2    >>%++---/6689CCEEr   c                      dj                  t        d      D  cg c]!  } t        t        j                  dd            # c}       S c c} w )z816 random digits. Matches V3 PHP: generateAccessToken().r      r   	   )joinrangestrrandomrandint)_s    r    generate_access_tokenrK   J   s3    77uRyA!Cq!,-ABBAs   &Auser_idc                     t        t        j                         dz        }d}|}|dkD  rd|dz     |z   }|dz  }|dkD  r| dk\  rt        |       dd n
t        |       }| d	| S )
zMbase36(timestamp_ms)-last2digits(userId). Matches V3 PHP: generateVEUserID().i  r   r   $0123456789abcdefghijklmnopqrstuvwxyz$   
   N-)r   timerG   )rL   timestamp_msbase36nsuffixs        r    generate_ve_user_idrX   O   s|    tyy{T)*LFA
a%7B?&H	b a% #*R-S\"#S\FXQvhr   agec                 4    | dk  ry| dk  ry| dk  ry| dk  ryy	)
zDConvert exact age to range string matching tbl_UserData.vAge format.   z15-17   z18-24"   z25-342   z35-50z50+r   )rY   s    r    age_to_ranger_   [   s-    
Rx			r   c                 P    | yt        |       }|dk  ry|dk  ry|dk  ry|dk  ry	y
)z@Convert first_cig_minutes to tbl_UserData.vTimeAfterWake format.N   zwithin-5   z	within-30<   z	within-60x   zafter-breakfastlater)r   )minutesms     r    map_time_after_wakerh   i   s>    GAAv	
b	
b	
c r   c                 N    | | dk(  ryt        |       }|dk  ry|dk  ry|dk  ryy	)
z/Convert past_attempts_count number to V3 label.r   Never   z	1-2 timesra   z	3-5 timesrP   z
4-10 timesz
Lost count)float)valuevs     r    map_past_attemptsro   z   s:    }
eAAv	
a	
br   symbolc                 B    ddddddddd	d	d
d}|j                  | |       S )zNConvert currency symbol to ISO code matching V3 tbl_UserProfile.vCurrencyCode.INRUSDGBPEURAUDCADNZDSGDAEDZAR)   ₹$   £u   €zA$zC$zNZ$zS$zAED rz   R)r   )rp   mappings     r    map_currency_to_coder      s7     5e5ee%G
 ;;vv&&r   INIndiaUSzUnited StatesGBzUnited KingdomCACanadaAU	AustraliaNZzNew ZealandSG	SingaporeAEzUnited Arab EmiratesZAz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CZz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KRzSouth KoreaCNChinaTWTaiwanHKz	Hong KongTHThailandMYMalaysiaID	IndonesiaPHPhilippinesVNVietnamBD
BangladeshPKPakistanLKz	Sri LankaNPNepalzSaudi ArabiaQatarKuwaitBahrainOmanEgyptNigeriaKenyaGhanaIsraelEurope)SAQAKWBHOMEGNGKEGHILEUcodec                 p    | sy| j                         j                         } t        j                  | |       S )zJConvert ISO country code to full name matching V3 tbl_UserConfig.vCountry.N)upperr=   COUNTRY_NAMESr   )r   s    r    country_code_to_namer      s/    ::<DT4((r   values	label_mapc                     | syg }| D ]8  }||v r||   }||vs|j                  |       #d|vs(|j                  d       : |rdj                  |      S dS )zConvert 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']
    NOther,)appendrE   )r   r   labelsrn   mappeds        r    	to_v3_csvr     sp    
 F '	>q\FV#f% f$g&'  &388F/4/r   zFirst thing in the morningzWith coffee/teazAfter mealszDuring work breakszIn the evenings, to unwindzWhen drinking alcoholzWhile socializingr   )	morningcoffeemealsworkeveningalcoholsocialstresschainzTo relaxzTo have fun / socializezTo remove boredomzTo help concentrate)relaxr  breakfocusenjoyrewardnothingNozNicotine Gums/Patches (NRTs)zVarenicline, wellbutrin, etczNaturopathy/homeopathy/ayurveda)		willpowernrt_gumsnrt_patchesvaping
medicationhypnosisreducingreducing_nicnonez*Scared of the withdrawal symptoms/cravingszWorried about gaining weightz*Concerned about my mental health worseningz'Worried about changing my daily routinezAfraid of losing friendszExcited about regaining control)
withdrawalweightr	  
fail_againidentitysocial_miss	not_readyreadyzStress/emotional triggerzSocial pressureAlcoholzWithdrawal was too hardzThought just one would be finezLost focus/got busy)stress_eventsocial_pressurer  r  just_one
lost_focus
session_ideventsdevice_typecountrysourcemediumcampaignc                 V   t               r|sy	 t               5 }|j                         5 }|D ]  }	|j                  d| |	j	                  dd      |	j	                  dd      t        |	j	                  dd            dd |	j	                  d	d      |||r|dd nd|r|dd
 nd|r|dd ndf
        	 ddd       |j                          ddd       t        dt        |       d| dd  d       y# 1 sw Y   @xY w# 1 sw Y   4xY w# t        $ r}
t        d|
        Y d}
~
yd}
~
ww xY w)z>Write a batch of clickstream events to tbl_ChatbotClickstream.Na6  
                        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)
                    stepr   
stepNumberr   answeri  timeSpentSecd      z[DB] Wrote z clickstream events for    ...z%[DB ERROR] Clickstream write failed: )
r6   r0   cursorexecuter   rG   commitprintlen	Exception)r'  r(  r)  r*  r+  r,  r-  r/   curevtes              r    write_clickstream_batchr@    s?    <v;X 	 #! CKK ! #+a0CGGHb124C82#(.tD(.tD*2#& KKM)	* 	CK=(@BQ@PPSTU) 	 	,  ;5aS9::;sF   
D C;BC/0C;&D /C8	4C;;D D 	D(D##D(c                 ^   t               syt        |       }	 t               5 }|j                         5 }|j	                  d|f       |j                         cddd       cddd       S # 1 sw Y   nxY w	 ddd       y# 1 sw Y   yxY w# t        $ r}t        d|        Y d}~yd}~ww xY w)zHCheck if user with this email already exists. Returns user dict or None.NzOSELECT iUserID, bActive FROM tbl_Users WHERE vHashedEmail = %s AND bDeleted = 0z[DB ERROR] User check failed: )r6   rA   r0   r7  r8  fetchoner<  r:  )r7   hashedr/   r=  r?  s        r    check_user_existsrD  %  s    <F
X 	& &#eI ||~& &	& 	&& & &	& 	& 	&  .qc23sQ   
B A?#A)	A?	B )A2	.A?6B ?BB B 	B,B''B,datac                 	   t               sy| j                  dd      j                         j                         }| j                  dd      }t	        |      }t               }t        j                  |j                               j                         }| j                  dd      xs d}| j                  dd      xs d}| j                  dd      xs d}|r|rt        d	||z
        nd}	t        | j                  d
d            }
| j                  d      dk(  }t        | j                  dd            }| j                  dd      }	 t               5 }|j                         5 }dj                  t!        d      D cg c]!  }t#        t%        j&                  dd            # c}      }|j)                  d||rdnd||f       |j*                  }t-        |      }|j)                  d||f       ddl}|j)                  d||rt1        |      nd|rdndt3        | j                  dd            t5        | j                  dg       t6              t5        | j                  dg       t8              t5        | j                  d      xs | j                  dg       t:              t5        | j                  d g       t<              t5        | j                  d!g       t>              tA        | j                  d"            d#|f       |j)                  d$||f       |j)                  d%|||	||rtC        |      ndtE        | j                  d&      xs | j                  d'd(            |rd	nd|
rd)nd*| j                  d+d,      | j                  d-      f
       |j)                  d.|f       |j)                  d/|| j                  d0      xs d| j                  d1d2      | j                  d3d4      | j                  d5d6      f       ddd       |jG                          ddd       	 tI               5 }|j                         5 }|j)                  d7||f       ddd       |jG                          ddd       tM        d9 d:|dd;  d<       |S c c}w # 1 sw Y   xY w# 1 sw Y   xY w# 1 sw Y   UxY w# 1 sw Y   IxY w# tJ        $ r}tM        d8|        Y d}~hd}~ww xY w# tJ        $ r}tM        d=|        Y d}~yd}~ww xY w)>a  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
    Nr7   r   namerY   r   cigs_per_dayyears_smoking   isVaperFsmoker_statusquit_recentr*  r   onboard_platformweb-chatbot-desktop-chromer5  rD   a  
                    INSERT INTO tbl_Users
                    (vEUserID, bActive, bDeleted, SmokingStatus,
                     vDeviceType, dDateCreated, vHashedEmail, vHashedToken)
                    VALUES (%s, 1, 0, %s, 'web', NOW(), %s, %s)
                FreeSmokerz5UPDATE tbl_Users SET vEUserID = %s WHERE iUserID = %sa  
                    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)
                z	stay-freequitpast_attempts_countmethods_triedrelapse_triggerslikes_about_smokinglikesfearstriggersfirst_cig_minutesEnglishz
                    INSERT INTO tbl_UserConfig
                    (iUserID, vCountry, bOnBoardComplete, bEmailSubscribed,
                     IsSocialLogin, bTransactSubscribed)
                    VALUES (%s, %s, 0, 1, 0, 1)
                an  
                    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)
                currency_codecurrencyrr   r  
cigarettespay_currencyr|   price_per_cigz5INSERT INTO tbl_UserAppActivity (iUserID) VALUES (%s)z
                    INSERT INTO tbl_UserSources
                    (iUserId, dDateCreated, vSourceUrl, vSource, vMedium, vCampaign, bSignup, vDeviceType)
                    VALUES (%s, NOW(), %s, %s, %s, %s, 1, 'web')
                
source_url
utm_sourcechatbot-onboarding
utm_mediumwebutm_campaignchatbotz
                        INSERT INTO tbl_UserInfo (iUserID, vEmail, vAPIToken)
                        VALUES (%s, %s, %s)
                    z[DB ERROR] PII write failed: z[DB] Created user z (email=   z***)z![DB ERROR] User creation failed: )'r6   r   r<   r=   rA   rK   r:   r;   r>   r?   maxr5   r   r0   r7  rE   rF   rG   rH   rI   r8  	lastrowidrX   jsonr_   ro   r  METHOD_LABELSRELAPSE_LABELSDECEPTION_LABELSCONCERN_LABELSTRIGGER_LABELSrh   rl   r   r9  r3   r<  r:  )rE  r7   rG  hashed_emailaccess_tokenhashed_tokenrY   cigsyearsstarted_smokingis_vaperalready_quitr*  platformr/   r=  rJ   	random_verL   
ve_user_idjson_modpii_connr?  s                          r    create_userr~  7  s    <HHWb!'')//1E88FBDe$L(*L>>,"5"5"78BBDL
((5!

!C88NA&+!DHH_a(-AE-0Uc!S5[)ODHHY./H88O,=L"488It#<=Gxx*,HIHuX a	 ^#GGa$Q1S1)=%>$QR	  *F  	
 -- 19
K) (  ),L%$#/KV%dhh/Da&HIdhh;]Kdhh'92>Odhh'<=V'SUAVXhidhhw3^Ddhhz26G'1D(EF,      ##'E$KQ(/)B)adhhz[`Fab%A1 (HlHH^X6HH_-( KJ  
 HH\*0bHH\+?@HH\51HH^Y7	
i^@ KKMCa	H		7 "__& 9#KK ! "5,799
 !" 	"7)8E"1I;dCD[ %R^ ^a	 a	L9 9" "  	71!566	7  1!56s   
S R!R:&Q? IR6RS 
R4 !R(2RR( R4 (S ?RR		RRS R%	!R((R1-R4 4	S=SS SS 	S9!S44S9c                    t               sy	 t               5 }|j                         5 }|j                  d| f       |j                  d| |j	                  d      xs d|j	                  dd      |j	                  dd	      |j	                  d
d      f       |j                  d|j	                  d      rt        |j	                  dd            nddt        |j	                  dd            t        |j	                  dg       t              t        |j	                  dg       t              t        |j	                  d      xs |j	                  dg       t              t        |j	                  dg       t              t        |j	                  dg       t              t        |j	                  d            |j	                  dd      | f       |j	                  dd      xs d}|j	                  dd      xs d}|j	                  dd      xs d}|j                  d|j	                  dd      |t        |      t        |j	                  d      xs |j	                  dd             |j	                  d!      xs |j	                  dd"      |j	                  d#      |r|rt!        d$||z
        nd|j	                  d%      rd&nd'|j	                  d(      d)k(  rd$nd| f
       |j                  d*| f       ddd       |j#                          ddd       t%        d+|  d,       y# 1 sw Y   1xY w# 1 sw Y   %xY w# t&        $ r}t%        d-|        Y d}~yd}~ww xY w).uN   Update existing user — last login, source, AND quiz data in existing tables.Nz
                    UPDATE tbl_Users SET dLastLogin = NOW(), bActive = 1, vDeviceType = 'web'
                    WHERE iUserID = %s
                z
                    INSERT INTO tbl_UserSources
                    (iUserId, dDateCreated, vSourceUrl, vSource, vMedium, vCampaign, bSignup, vDeviceType)
                    VALUES (%s, NOW(), %s, %s, %s, %s, 0, 'web')
                ra  r   rb  rc  rd  re  rf  rg  a4  
                    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
                rY   r   rR  rS  rT  rU  rV  rW  rX  rY  rZ  rN  rO  rH  rI  a  
                    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
                rG  r\  r]  rr   r_  r|   r`  rJ  rK  r  r^  rL  rM  z
                    UPDATE tbl_UserConfig SET
                        dDateUpdated = NOW()
                    WHERE iUserID = %s
                z[DB] Updated existing user z (all tables)z[DB ERROR] User update failed: )r6   r0   r7  r8  r   r_   ro   r  rl  rm  rn  ro  rp  rh   rl   r   ri  r9  r:  r<  )rL   rE  r/   r=  rt  rY   ru  r?  s           r    update_existing_userr    s   <Y5X U	 R!#  Z!  
 HH\*0bHH\+?@HH\51HH^Y7	
   9=L%!34T%dhh/Da&HIdhh;]Kdhh'92>Odhh'<=V'SUAVXhidhhw3^Ddhhz26G'1D(EFHH/1MN8 xx27ahhua(-A!49  HHVR($K(/)B)adhhz[`FabHH^,NX0NHH_-+.5C3;'d $ 3H/2mCA4   Z	!]R!h KKMkU	l 	+G9MBCkR! R!U	 U	n  5/s3445sF   
L5 L)KL-L)L5 L&	"L))L2.L5 5	M>MMmonthly_costc                    t               sy	 t               5 }|j                         5 }|j                  d| f       |dkD  r|j                  d|| f       ddd       |j	                          ddd       t        d|         y# 1 sw Y   0xY w# 1 sw Y   $xY w# t        $ r}t        d|        Y d}~yd}~ww xY w)z/Set bOnBoardComplete=1 after results are shown.Nz
                    UPDATE tbl_UserConfig
                    SET bOnBoardComplete = 1, dOnBoardCompleteTime = NOW(),
                        dProgramStartedOn = NOW(), vOnBoardLocation = NULL
                    WHERE iUserID = %s
                r   zBUPDATE tbl_UserProfile SET decCostPerMonth = %s WHERE iUserID = %sz&[DB] Marked onboard complete for user z$[DB ERROR] Onboard complete failed: )r6   r0   r7  r8  r9  r:  r<  )rL   r  r/   r=  r?  s        r    mark_onboard_completer  )  s    <:X 	 # 
 Z!  !#KK\%w/ KKM	 	6wi@A 	 	  :4QC899:sE   
B B-BB-B B		BBB 	B=%B88B=c                    t               syddl}	 t               5 }|j                         5 }|j	                  dg || |j                  dd      |j                  d      rdnd|j                  d      rt        |j                  dd            nd|j                  |j                  d	g             |j                  d
d      |j                  d      |j                  |j                  dg             |j                  |j                  dg             |j                  |j                  dg             |j                  d      |j                  d      xs d|j                  d      |j                  di       j                  d      |j                  |j                  dg             |j                  d      |j                  d      |j                  |j                  dg             |j                  |j                  dg             |j                  |j                  dg             |j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      |j                  di       j                  d      xs d|j                  di       j                  d       |j                  di       j                  d!d"      |j                  d#      xs% |j                  di       j                  d$      xs d|j                  d%      xs d|j                  d&      xs i j                  d'      xs d|j                  d&      xs i j                  d(      xs d|j                  d&      xs i j                  d)      xs d|j                  d&      xs i j                  d*      xs d       ddd       |j                          ddd       t        d+| d,| dd-  d.       y# 1 sw Y   7xY w# 1 sw Y   +xY w# t        $ r}t        d/|        Y d}~yd}~ww xY w)0z.Write full quiz data to tbl_ChatbotOnboarding.Nr   aP  
                    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)
                rL  r   rK  rJ  rY   core_valuesyears_smoking_labelrH  rV  dislikes_about_smokingrY  rZ  r]  r`  savingsmonthlydenial_patternsdenial_countrS  rU  rT  deeper_whysbiggest_fearfuture_vision
commitment	readinessprofilesmoker_typesuccess_probabilityprogramsix_day_personalLetter_llm_letter_onboard_platform_utmr*  rb  rd  rf  z'[DB] Wrote chatbot onboarding for user z
, session r5  r6  z$[DB ERROR] Onboarding write failed: )r6   rk  r0   r7  r8  r   r_   dumpsr9  r:  r<  )r'  rL   rE  r|  r/   r=  r?  s          r    write_chatbot_onboardingr  C  sx   <4:X 0	 .# 
### HH_b1# ),A!	#
 9=L%!34T# NN488M2#>?# HH2B7# HH^,# NN488,A2#FG# NN488,Db#IJ# NN488J#;<# HH01# HHZ(0D# HH_-# HHY+//	:#  NN488,=r#BC!#" HH^,##$ HH23%#& NN488,>#CD'#( NN488OR#@A)#* NN488M2#>?+#, HH^,4-#. HH_-5/#0 HH\*2d1#2 HH[)3#4 HHY+//>F$5#6 HHY+//0EF7#8 HHY+//	9E9#: HH./e488Ir3J3N3N}3]eae;#< HH019T=#> XXf%+00;Ct?#@ XXf%+00>F$A#B XXf%+00>F$C#D XXf%+00@HDE#-.^ KKMa0	b 	7y
:VXWX>JZZ]^_a. .0	 0	d  :4QC899:sF   
P1 P%N7P#P%;P1 P"	P%%P.*P1 1	Q:QQc                 2   t               sy	 t               5 }|j                         5 }|j                  d| f       ddd       |j	                          ddd       y# 1 sw Y   "xY w# 1 sw Y   yxY w# t
        $ r}t        d|        Y d}~yd}~ww xY w)z&Set bCompleted=1 when user clicks CTA.NzEUPDATE tbl_ChatbotOnboarding SET bCompleted = 1 WHERE vSessionId = %sz[DB ERROR] CTA mark failed: )r6   r0   r7  r8  r9  r<  r:  )r'  r/   r=  r?  s       r    mark_cta_clickedr    s    <	2X 	 #[M
 KKM	 	 	 	  2,QC0112sJ   
A5 A)AA)A5 A&	"A))A2.A5 2A5 5	B>BBc                     | j                         j                         dk(  ryt        t        j                  dd            S )z@Generate 6-digit OTP. Fixed code for ram@quitsure.app (testing).zram@quitsure.app091081i i?B )r<   r=   rG   rH   rI   r@   s    r    generate_otpr    s3    {{} 22v~~ff-..r   c                 
   | j                         j                         } t        |       }t               st	        d|  d|        |S 	 t               5 }|j                         5 }|j                  d| f       |j                  d| |f       ddd       |j                          ddd       t        | |       t	        d|         |S # 1 sw Y   =xY w# 1 sw Y   1xY w# t        $ r}t	        d|        |cY d}~S d}~ww xY w)zDGenerate OTP, save to tbl_Login, send via Postmark. Returns the OTP.z[OTP] DB disabled, OTP for : zDUPDATE tbl_Login SET bExpired = 1 WHERE vEmail = %s AND bExpired = 0z
                    INSERT INTO tbl_Login (vEmail, vOTP, dDateCreated)
                    VALUES (%s, %s, NOW())
                Nz[OTP] Sent to z[OTP ERROR] Failed: )r<   r=   r  r6   r:  r0   r7  r8  r9  _send_postmark_otpr<  )r7   otpr/   r=  r?  s        r    send_otpr    s   KKM!E
u
C<+E7"SE:;
X 	 	##ZH
   S\#	# KKM	 	5#&ug&'
!	# 	#	 	&  $QC()
sN   
C C#(CC##C C	CCC 	D(C=7D=Duser_otpc                 Z   | j                         j                         } t               sdddS 	 t               5 }|j	                         5 }|j                  d| f       |j                         }|sdddcddd       cddd       S |d   |j                         k7  rdddcddd       cddd       S |j                  d	| f       ddd       |j                          ddd       d
ddS # 1 sw Y   &xY w# 1 sw Y   xY w# t        $ r}t        d|        dddcY d}~S d}~ww xY w)z4Verify OTP. Returns {'valid': bool, 'message': str}.FzDB not available)validmessage
                    SELECT vOTP FROM tbl_Login
                    WHERE vEmail = %s AND bUsed = 0 AND bExpired = 0
                    ORDER BY dDateCreated DESC LIMIT 1
                z9Your verification code has expired. We'll send a new one.NvOTPz!Incorrect code. Please try again.zOUPDATE tbl_Login SET bUsed = 1 WHERE vEmail = %s AND bUsed = 0 AND bExpired = 0TVerifiedz[OTP ERROR] Verify failed: z'Something went wrong. Please try again.)
r<   r=   r6   r0   r7  r8  rB  r9  r<  r:  )r7   r  r/   r=  rowr?  s         r    
verify_otpr    s=   KKM!E<+=>>VX 	 #  X	
 lln%*7rs 	 	 v;(.."22%*7Z[ 	 	" eH!( KKM+	. *55- 	 	2  V+A3/0+TUUVsp   
D C8
*C,4	C8=	D C,!	C8*	D 4C,C8D ,C5	1C88D=D 	D*D%D*%D*c                    | j                         j                         } t               st        |       S 	 t	               5 }|j                         5 }|j                  d| f       |j                         }ddd       ddd       r"t        | |d          t        d|         |d   S t        |       S # 1 sw Y   @xY w# 1 sw Y   DxY w# t        $ r#}t        d|        t        |       cY d}~S d}~ww xY w)u@   Resend OTP — reuse existing if active, otherwise generate new.r  Nr  z[OTP] Resent existing to z[OTP ERROR] Resend failed: )r<   r=   r6   r  r0   r7  r8  rB  r  r:  r  r<  )r7   r/   r=  r  r?  s        r    
resend_otpr    s    KKM!E<E""X 	% %#  X	
 lln%	% uc&k2-eW56v; E?"% %	% 	%$  +A3/0sR   
C B?$B34B?<+C (
C 3B<	8B??CC 	C7C2,C72C7r  c                    ddl }t        j                  j                  dd      }t        j                  j                  dd      }t        j                  j                  dd      }|st	        d	|  d
|        y	 |j                  ddd|d|| |d|idddd      }|j                  dk(  rt	        d|         yt	        d|j                   d
|j                  dd         y# t        $ r}t	        d|        Y d}~yd}~ww xY w)z Send OTP email via Postmark API.r   NPOSTMARK_TOKENr   POSTMARK_FROMzno-reply@quitsure.appPOSTMARK_OTP_TEMPLATEzotp-templatez-[POSTMARK] No token, skipping email. OTP for r  z.https://api.postmarkapp.com/email/withTemplatezapplication/json)AcceptzContent-TypezX-Postmark-Server-Tokenotp_coder  outbound)FromToTemplateAliasTemplateModelTagMessageStreamg      $@)headersrk  timeoutr4  z[POSTMARK] OTP email sent to z[POSTMARK ERROR] Status z[POSTMARK ERROR] )	httpxr   r   r   r:  poststatus_codetextr<  )r7   r  r  token
from_emailtemplate_aliasrespr?  s           r    r  r     s   JJNN+R0E1HIJZZ^^$;^LN=eWBseLM'zz<, 2+0 #!/",c!2!+   
" s"1%9:,T-=-=,>b4C@QRS '!!%&&'s   :?C# :(C# #	D,C??D)r   )-__doc__r   r:   rS   rH   r   
contextlibr   r!   r)   r0   r3   r6   rG   rA   rK   r   rX   r_   rh   ro   r   r   r   listdictr  rp  rn  rl  ro  rm  r@  rD  r~  r  rl   r  r  r  r  r  r  r  r  r   r   r    <module>r     sB   
     %
		    +Fc Fc F
Cs C
	  	  	 c c C " ' ' ''*.0@(+'+] 	+ 3 6:> 	)	 8	 &*7	 59'	
 	-
 y
 +/	
 <@ 	( ( %)) 6:9 	) : (,X 8<=M 	) 9 '+H 7;H 	( ) &*8 6:8 	+ Z *.w 9=f 	'  )-g 8<X 	+ Z *.z <@ 	- y +/ ?CJ 	+ W -;'
)6
7'
#*)s )s )0d 0t 0 0, , +&!
 ' "  .101
 ?,:9-.	 /(+0'; ;T ; ;%(;25;?B;NQ;DS T $Od Os Od]5# ]5T ]5@:3 :e :49: 9:s 9:$ 9:x2 2$/ / / C  C  F#Vc #VS #VT #VLc c >#'c #' #'r   