
    
Lj              	           d Z ddlZddlZddlZd ZdZd Zd Zd Zd Z	d%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ddddddddddddd d!d"Zd# Zd$ Zy)&a  DB access for QuitSure Discovery personalization.

Reuses the chatbot-onboarding pattern: PyMySQL, DictCursor, a fresh connection
per call, env-based creds (DB_HOST / DB_PORT / DB_DATABASE / DB_USERNAME /
DB_PASSWORD, loaded from .env by app.py). Read-only.

get_profile(hashed_email) -> dict | None, used by GET /discovery/api/me so the
remarketing link ?u=<vHashedEmail> personalizes the funnel from screen 1.
    Nc            
      B   t        j                  t        j                  d   t	        t        j                  j                  dd            t        j                  d   t        j                  d   t        j                  d   t         j                  j                  dd      S )	NDB_HOSTDB_PORTi  DB_USERNAMEDB_PASSWORDDB_DATABASE   )hostportuserpassworddatabasecursorclassconnect_timeoutread_timeout)pymysqlconnectosenvironintgetcursors
DictCursor     (/var/www/python/quitsure-discovery/db.py_connr      sm    ??ZZ	"	401ZZ&M*M*OO..	 	r   as  
SELECT u.iUserID, u.iActiveDayID,
       p.vName, p.decSmokePerDay, p.vCurrencyCode,
       d.vGender, d.vAge, c.vCountry
FROM tbl_Users u
LEFT JOIN tbl_UserProfile p ON p.iUserID = u.iUserID
LEFT JOIN tbl_UserData   d ON d.iUserID = u.iUserID
LEFT JOIN tbl_UserConfig c ON c.iUserID = u.iUserID
WHERE u.vHashedEmail = %s AND u.bDeleted = 0 AND u.bTestUser = 0
LIMIT 1;
c                     | r| j                         sy | j                         j                         d   j                         S )Nr   )stripsplit
capitalize)vs    r   _first_namer#   +   s1    AGGI779??Q**,,r   c                     | xs dj                         j                         }|j                  d      ry|j                  d      ryy )N malefemale)r   lower
startswith)r"   ss     r   _genderr+   1   s<    	
b!A||F||Hr   c                 z    	 t        |       }d|cxk  rdk  rn nt        |      S dS # t        t        f$ r Y yw xY w)u@   Sanity-bound: only trust 1–60/day (per personalization rules).   <   N)floatround	TypeError
ValueError)r"   xs     r   _cigsr4   :   s@    !H<R<uQx1T1z" s   #( ( ::c                 $    t        |       xr d| v S )Nz< 18)bool)ages    r   	_under_18r8   C   s    9&3&r   c                    | r|sy|xs i }|dd D cg c]  }| t        |j                  dd            dd t        |j                  dd      xs d      |j                  d      d	vrt        |d         dd
 ndt        |j                  dd      xs d      |j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs df
 }}d}	 t               5 }|j	                         5 }|j                  ||       ddd       |j                          ddd       t        |      S c c}w # 1 sw Y   1xY w# 1 sw Y   %xY w# t        $ r Y yw xY w)zBatch-insert step events into tbl_DiscoveryClickstream. events: list of
    {step, stepNumber, answer, timeSpentSec}. Returns inserted count (0 on error).r   Nd   stepr%      
stepNumberanswerNr%   i  timeSpentSec
deviceTypecountrysourcemediumcampaignzINSERT INTO tbl_DiscoveryClickstream (vSessionId, vStep, iStepNumber, vAnswer, iTimeSpentSec, vDeviceType, vCountry, vSource, vMedium, vCampaign) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s))	strr   r   r   cursorexecutemanycommitlen	Exception)
session_ideventsmetaerowssqlconncurs           r   insert_clickstreamrT   G   s    V:2D   fb!"3B'lA&+!,()hz(IS8et$tna(-A.XXl#+tXXi (DXXh'4XXh'4XXj!)T	
D  	1 
W 	 +#T*+KKM	 4y3,+ +	 	
  sH   C,E
E: E.E"2E.
E: "E+	'E..E73E: :	FFiUserIDvHashedEmailvTomorrowCountvExcusesiExcuseCountvFeeling	bSmokeNowvPathvVoiceGendervTriggervNameiCigsPerDay	vCurrencyvCountrybReachedOffer
bConvertedvConvertProgramvExitReasonvOnBoardPlatformvDeviceTypevSourcevMedium	vCampaign
bCompleted)rf   rg   rh   ri   rj   rk   rl   c           
         | syt         j                         D ci c]  \  }}||v s||   dvs|||    }}}	 t               5 }|j                         5 }|j	                  d| f       |j                         }g g }	}|j                         D ]+  \  }}
|j                  d| d       |	j                  |
       - |j                  d      r|j                  d       |j                  d      r|j                  d	       |r-|j	                  d
dj                  |       d|	|d   gz          ndgt        |j                               z   }dgt        |      z  }d}|j                  d      r"|j                  d       |j                  d       |j                  d      r"|j                  d       |j                  d       dj                  d |D              }|j	                  d| ddj                  |       d| gt        |j                               z          ddd       |j                          ddd       yc c}}w # 1 sw Y   (xY w# 1 sw Y   yxY w# t        $ r Y yw xY w)zInsert or update one tbl_DiscoveryOnboarding row per session (keyed by vSessionId).
    Sets dConvertedAt/dOfferSeenAt server-side when the matching flag flips on. Best-effort.Fr?   zBSELECT id FROM tbl_DiscoveryOnboarding WHERE vSessionId=%s LIMIT 1`z`=%src   z.`dOfferSeenAt`=COALESCE(`dOfferSeenAt`, NOW())rd   z.`dConvertedAt`=COALESCE(`dConvertedAt`, NOW())z#UPDATE tbl_DiscoveryOnboarding SET z, z WHERE id=%sid
vSessionIdz%sr%   dOfferSeenAtzNOW()dConvertedAtc              3   (   K   | ]
  }d | d   yw)rn   Nr   ).0cs     r   	<genexpr>z!upsert_session.<locals>.<genexpr>   s     &>A1#Qx&>s   z%INSERT INTO tbl_DiscoveryOnboarding (z
) VALUES ()NT)_SESSION_COLSitemsr   rG   executefetchoneappendr   joinlistkeysrJ   valuesrI   rK   )rL   datakcolfieldsrR   rS   existingsetsvalsr"   colsphextracolsqls                  r   upsert_sessionr   w   sB    )6)<)<)>jvq#!t)PTUVPW_iPic47ljFjW 	 F#`cmbop<<>d$lln ?FCKK!C5.A?::o.KK PQ::l+KK PQKK"EdiiPToEVVb ceimuvzm{l|e|}(>D,??D#d)+BEzz/2N3RYYw5Gzz,/N3RYYw5G!YY&>&>>FKK"GxzZ^ZcZcdfZgYhhi j!+tFMMO/D DF+F. KKM1	2 7 kF F	2  sW   I I I 
I IGII7I I	III I 	I*)I*c                    | rt        |       dk7  ryt               5 }|j                         5 }|j                  t        | f       |j                         }ddd       ddd       sy|j                  d      xs dj                         }|j                  d      xs dj                         j                         }|j                         dv xs |dk(  }|rdn|dk(  rdnd}|j                  d	      t        |j                  d
            t        |j                  d            t        |j                  d            |xs d|t        |j                  d             dS # 1 sw Y   xY w# 1 sw Y   xY w)zEReturn personalization dict for a hashed email, or None if not found.@   Nrb   r%   vCurrencyCode)indiainINRUSDrU   r_   decSmokePerDayvGendervAge)rU   name
cigsPerDaygenderrB   currencyeligible)rJ   r   rG   rz   _LOOKUPr{   r   r   upperr(   r#   r4   r+   r8   )hashed_emailrR   rS   rowrB   codeis_indiar   s           r   get_profiler      sE   3|,2	 !D[[] 	!cKK,1,,.C	!! wwz"(b//1GGGO$*11399;D}}/1BTU]H uu4H779%CGGG,-CGG$456#''),-?d!#''&/22 	! 	!! !s"   E-(E E- E*	%E--E7)N)__doc__r   r   pymysql.cursorsr   r   r#   r+   r4   r8   rT   rx   r   r   r   r   r   <module>r      s2   
  

-'!Jy(.:JL\
*N<F
  &w 1? Q[\f W	 $]	 5@	 OYZd	
 _
 '3L
 CTUf !6HYfY[Xd#Lr   