
    G>j[                     H   d Z ddlZddlZddlZddlmZ ddlmZmZmZ ddl	m
Z
 ddlmZ ddlZddlZddlmZ ddlmZmZ dd	lmZ dd
lmZmZ ddlmZ ddlmZ ddlmZmZm Z m!Z! ddl"m#Z# ddl$m%Z% ddl&m'Z' ddl(m)Z) ddl*m+Z+  e         ejX                  ejZ                  d        ej\                  d      Z/ej`                  jc                  dd      ej`                  jc                  dd      ej`                  jc                  dd      ej`                  jc                  dd      ddddZ2ej`                  jc                  d d      Z3ej`                  jc                  d!d      Z4 e#e'"      Z5 G d# d$e)      Z6 ed%ddd&      Z7e5e7jp                  _5        e7js                  e%d'        e7ju                  e6       e7ju                  eej`                  jc                  d(d)      gd*d+gd,g-       e7jw                  d. ed/0      d/1        ed20      Z<ejz                  j}                  ejz                  j                  e@      d3d4      ZA ej                  ejz                  j                  eA      d56       d7eCfd8ZDd9eEd7dfd:ZF G d; d<eEe
      ZG G d= d>eEe
      ZH G d? d@eEe
      ZI G dA dBe      ZJedC        ZKdD ZLdEeEd7eEfdFZMdG ZNdHdIiZOdsd9eEdJePd7eQfdKZRdLdMdNZSdOdPdNZTdQeQd7eQfdRZUdSeJdQeQdTeQd7eQfdUZVdSeJdQeQd7eEfdVZWej`                  jc                  dWe4      ZXdXZYdYZZdZZ[d[eEd7e\fd\Z]e7jc                  d]e^      d_ed7efd`       Z^e7jc                  da      d7eQfdb       Z_e7jc                  dc      e5j                  dd      dtd_edeeEdfeEd7efdg              Zae7j                  dh      e5j                  di      d_ed7efdj              Zceddkk(  rXddleZe eej                  dldm ePej`                  jc                  dndo            ej`                  jc                  dpdq      dqk7  r       yy)uz
QuitSure Refund Eligibility Form - Backend
FastAPI app that serves the form, checks DB eligibility, and logs to Google Sheets.
    N)contextmanager)datedatetime	timedelta)Enum)Optional)load_dotenv)FastAPIRequest)CORSMiddleware)HTMLResponseJSONResponse)StaticFiles)Jinja2Templates)	BaseModelEmailStrField	validator)Limiter)RateLimitExceeded)get_remote_address)BaseHTTPMiddleware)Responsez1%(asctime)s [%(levelname)s] %(name)s: %(message)s)levelformatzrefund-formDB_HOST DB_USERDB_PASSWORDDB_NAMEQuitSure_Productionutf8mb4
      )hostuserpassworddatabasecharsetconnect_timeoutread_timeoutGOOGLE_SHEET_WEBHOOK_URLGOOGLE_SHEET_SECRET)key_funcc                       e Zd ZdedefdZy)SecurityHeadersMiddlewarerequestreturnc                    K    ||       d {   }d|j                   d<   d|j                   d<   d|j                   d<   d|j                   d<   d	|j                   d
<   |S 7 Qw)NnosniffzX-Content-Type-OptionsDENYzX-Frame-Optionsz1; mode=blockzX-XSS-Protectionzstrict-origin-when-cross-originzReferrer-Policyzdefault-src 'self'; style-src 'self' 'unsafe-inline' https://fonts.googleapis.com; font-src 'self' https://fonts.gstatic.com; img-src 'self' data:; script-src 'self' 'unsafe-inline'; connect-src 'self';zContent-Security-Policy)headers)selfr1   	call_nextresponses       +/var/www/python/quitsure-refund-form/app.pydispatchz"SecurityHeadersMiddleware.dispatch:   sy     "7++5>12.4*+/>+,.O*+" 	23  ,s   A"A AA"N)__name__
__module____qualname__r   r   r;        r:   r0   r0   9   s    g X r@   r0   zQuitSure Refund Form)titledocs_url	redoc_urlopenapi_urlc                      t        dddi      S )Ni  detailz*Too many requests. Please try again later.status_codecontent)r   )reqexcs     r:   <lambda>rL   T   s    lCD? r@   ALLOWED_ORIGIN*GETPOSTzContent-Type)allow_originsallow_methodsallow_headersz/staticstatic)	directory)name	templatesdatazsubmitted_emails.txtT)exist_okr2   c                      	 t        t        d      5 } | D ch c]#  }|j                         s|j                         % c}cd d d        S c c}w # 1 sw Y   y xY w# t        $ r t	               cY S w xY w)Nr)openSUBMISSIONS_FILEstripFileNotFoundErrorset)flines     r:   _load_submitted_hashesrc   h   sg    "C( 	@A-.?T$**,DJJL?	@ 	@?	@ 	@ usC   A AAAA	A AAA A A21A2
email_hashc                 v    t        t        d      5 }|j                  | dz          d d d        y # 1 sw Y   y xY w)Na
)r\   r]   write)rd   ra   s     r:   _save_submitted_hashri   p   s5    		$ #	
T!"# # #s   /8c                       e Zd ZdZdZdZy)PlatformEnumAndroidiOSWebN)r<   r=   r>   androidioswebr?   r@   r:   rk   rk   v   s    G
C
Cr@   rk   c                       e Zd ZdZdZy)ProgramEnumoriginalrelaxedN)r<   r=   r>   rt   ru   r?   r@   r:   rs   rs   |   s    HGr@   rs   c                       e Zd ZdZdZy)YesNoyesnoN)r<   r=   r>   rx   ry   r?   r@   r:   rw   rw      s    
C	Br@   rw   c                   P   e Zd ZU eed<    edd      Zeed<   eed<   e	ed<    ed	      Z
eed
<   eed<   eed<   eed<    edd      Zeed<    edd      Zeed<    edd      Zeed<   eed<    ee      Zeed<    ed
      ed               Z ed      ed               Zy)RefundFormDataemail      )
min_length
max_lengthrV   platformprogramr#   )r   purchaseDate	completedfirstSubaccountActiver   i  )defaultr   reasond   countrypaypalEmailformEligible)default_factoryformRejectReasonsc                     	 t        j                  |d      j                         }|t        j                         kD  rt        d      |t        ddd      k  rt        d      |S # t        $ r t        d      w xY w)N%Y-%m-%dz(Invalid date format, expected YYYY-MM-DDz%Purchase date cannot be in the futurei     z$Purchase date is too far in the past)r   strptimer   
ValueErrortoday)clsvds      r:   validate_purchase_datez%RefundFormData.validate_purchase_date   s    	I!!!Z0557A tzz|DEEtD!QCDD  	IGHH	Is   $A% %A:c                 `    |j                         }t        d |D              st        d      |S )Nc              3   <   K   | ]  }|j                           y w)N)isalpha).0cs     r:   	<genexpr>z/RefundFormData.validate_name.<locals>.<genexpr>   s     11199;1s   z%Name must contain at least one letter)r^   anyr   )r   r   strippeds      r:   validate_namezRefundFormData.validate_name   s.     779111DEEr@   N)r<   r=   r>   r   __annotations__r   rV   strrk   rs   r   rw   r   r   r   boollistr   r   classmethodr   r   r?   r@   r:   r{   r{      s    Os3D#3,L#,Ot4FC44GS4RC8K8#D9t9~	  	 v  r@   r{   c               #      K   d} 	 t        j                  di t        dt         j                  j                  i} |  | r| j                          yy# | r| j                          w w xY ww)z?Context manager for safe DB connections with automatic cleanup.Ncursorclassr?   )pymysqlconnect	DB_CONFIGcursors
DictCursorclose)conns    r:   get_db_connectionr      sY      DSS8R8RS
JJL 4JJL s   A+9A A+A((A+c                     ddl m} | yt        | |      rt        |       S t        | t        t
        f      r| j                         S | S )zJConvert MySQL types (Decimal, datetime, date) to JSON-serializable values.r   )Decimalr   )decimalr   
isinstancefloatr   r   	isoformat)valr   s     r:   
_json_safer      sA    
{#wSz#$'(}}Jr@   textc                     t        j                  | j                         j                         j	                               j                         S )z&Hash email the same way QuitSure does.)hashlibsha256lowerr^   encode	hexdigest)r   s    r:   sha256_hashr      s2    >>$**,,,.5578BBDDr@   c                     | yt        | t              rt        | t              s| S t        | t              r| j                         S t        | t              r't        j                  | dd d      j                         S y)z8Parse a date value from MySQL into a Python date object.Nr#   r   )r   r   r   r   r   )r   s    r:   _to_dater      sg    
{#tZX%>
#x xxz#s  Sb:6;;==r@   	      user_idc                    dddddddddddd}	 t               5 }|j                         5 }||j                  d|f       n|j                  d| f       |j                         }|s|cddd       cddd       S d|d<   |d   |d	<   t	        |d
         |d<   |j                  d      xs dj                         }d|v rd|d<   nd|v sd|v rd|d<   nd|d<   |d   }|j                  d|f       |j                         }d|d<   |D ]3  }|j                  d      }	|	st        |	      dk7  s&d|d<   |d   |d<    n |D 
cg c]  }
|
j                  d      s|
 }}
|r|d   nd}|r|d   }||d<   |dk(  rdnd |d!<   |j                  d"      sd|d#<   d$|d%<   n|d&   }|j                  d'|f       |j                         }g }|D ]  }|j                  d(      }|j                  d)      xs dj                         }|j                  d*      xs dj                         }d+|v xs
 d,|v xs d-|v }|t        |      dk(  r{|r||j                  |        |sd|d#<   d.|d%<   nt        |      d/k\  rd|d0<   t        |      |d1<   |d2   }t        |d3         }|r|j                  d*      xs dj                         d4k(  r:t        j                  |d5      }|t        |6      z   }d7j                  |      |d%<   n|}d8|d%<   |j!                         |d9<   t#        j$                         |z
  j&                  |d:<   |j                  d(      t        |d(         |d;<   |j                  d<      xs d|d=<   |j                  d)      xs |j                  d*      xs d}||d><   |j                  d?|fd@z         |j                         }|r|dA   ndxs dB}||dC<   dDdEdFdGj                  |dE      }t        |j                  dH            }t        |d3         }|r|r||z
  j&                  } | |dIz  kD  |dJ<   nd|dJ<   |d   }|r#dK|cxk  rdLk  rn ndMj                  |      }!dNj                  |      }"|dk(  rdOndP}#|j                  dQj                  |"      |#f       |j                         D cg c]  }|dR   	 }$}|$rdSj)                  dTgt        |$      z        }%|j                  dUj                  |!|"|%      |g|$       |j                         }&|&r|&dV   nd}'|'|#k\  |dW<   |'|dV<   |&r7|&dX   r2|&dX   }(|&dY   xs d})|)|dY<   |(|dX<   |&dZ   |d[<   |(d@k\  xr |)|(z  d\kD  |d]<   |j                  d^|f       |j                         }*|*r|*j                  d_d      nd}+|+rt        |+      d`k\  r||+dak7  rw|+dbk7  rr|j                  dc|+|f       |j                         },t        t+        dd |,D                    |de<   |,D ]+  }-|-j                  d      }	|	st        |	      dk7  s&d|df<    n ddd       ddd       |S c c}
w c c}w # 1 sw Y   xY w# 1 sw Y   |S xY w# t,        j.                  $ r!}.t0        j3                  dg|.       Y d}.~.|S d}.~.wt4        $ r!}.t0        j3                  dh|.       Y d}.~.|S d}.~.ww xY w)izLLook up user and return eligibility data. Key by hashed email OR by user_id.FN)
user_foundr   r   r   
program_idfirst_sub_datedays_since_purchaseaccount_deletedhas_prior_refundprogram_completeddays_completeda  
                        SELECT /*+ MAX_EXECUTION_TIME(15000) */
                            iUserID, vDeviceType, bDeleted, bTestUser
                        FROM tbl_Users
                        WHERE iUserID = %s
                        LIMIT 1
                    a]  
                        SELECT /*+ MAX_EXECUTION_TIME(15000) */
                            iUserID, vDeviceType, bDeleted, bTestUser
                        FROM tbl_Users
                        WHERE vHashedEmail = %s
                        ORDER BY bTestUser ASC, bDeleted ASC, iUserID DESC
                        LIMIT 1
                    Tr   iUserIDr   bDeletedr   vDeviceTyper   ro   rl   r   rp   iphonerm   UnknownaU  
                    SELECT /*+ MAX_EXECUTION_TIME(15000) */
                        iUserProgramID, iProgramId, dFirstSubDate,
                        bPreviouslySubscribed, bSubscribed, bRefund
                    FROM tbl_UserPrograms
                    WHERE iUserID = %s
                    ORDER BY dFirstSubDate DESC
                r   bRefund0
iProgramIdrefund_programdFirstSubDater   r   r   RelaxedOriginalr   bPreviouslySubscribedno_payment_foundz/bPreviouslySubscribed=0 (payment not completed)date_sourceiUserProgramIDa  
                            SELECT /*+ MAX_EXECUTION_TIME(15000) */
                                dStartDate, dExpiryDate, decPrice, vCurrency,
                                vProductId, vLabel, iRenewed, bRestore
                            FROM UserSubHistory
                            WHERE iUserProgramID = %s
                            ORDER BY dStartDate ASC
                        decPrice
vProductIdvLabelftfreetrialzNo paid rows in SubHistoryr}   is_repeat_subscribertotal_subscription_periods
dStartDatez
free-trial   )daysz SubHistory +{}d trial adjustmentzSubHistory paymentr   r   payment_amount	vCurrencypayment_currency
product_ida  
                                SELECT /*+ MAX_EXECUTION_TIME(15000) */
                                    vDuration FROM tbl_ProductCodes
                                WHERE bActive = 1
                                  AND (vIosSubId = %s OR vAndroidSubId = %s
                                       OR vAndroidProdId = %s OR vWebId = %s
                                       OR vRazorId = %s)
                                LIMIT 1
                               	vDurationmosub_durationr         )wkr   6modExpiryDateg      ?
is_renewalr   r#   ztbl_User{}Chaptersztbl_{}Chapters*      z
                        SELECT /*+ MAX_EXECUTION_TIME(15000) */
                            DISTINCT iDayId FROM {} WHERE bDeleted = 0
                        ORDER BY iDayId ASC LIMIT %s
                    iDayId,z%sa  
                            SELECT /*+ MAX_EXECUTION_TIME(15000) */
                                COUNT(DISTINCT ch.iDayId) as days_completed,
                                COUNT(*) as total_chapters,
                                SUM(CASE WHEN TIMESTAMPDIFF(SECOND, uc.dDateCreated, uc.dDateUpdated) < 10
                                    THEN 1 ELSE 0 END) as speed_chapters,
                                ROUND(AVG(TIMESTAMPDIFF(SECOND, uc.dDateCreated, uc.dDateUpdated))) as avg_seconds
                            FROM {} uc
                            JOIN {} ch ON uc.iChapterID = ch.iChapterId
                            WHERE uc.iUserID = %s AND uc.bCompleted = 1
                              AND ch.iDayId IN ({})
                        r   r   total_chaptersspeed_chaptersavg_secondsavg_seconds_per_chapterg      ?suspected_speed_completionz
                    SELECT /*+ MAX_EXECUTION_TIME(15000) */
                        vUDID FROM tbl_Users WHERE iUserID = %s
                vUDID   -0000000000000000a  
                        SELECT /*+ MAX_EXECUTION_TIME(15000) */
                            u.iUserID, upr.bRefund
                        FROM tbl_Users u
                        JOIN tbl_UserPrograms upr ON u.iUserID = upr.iUserID
                        WHERE u.vUDID = %s AND u.iUserID != %s
                          AND u.bTestUser = 0
                          AND upr.bPreviouslySubscribed = 1
                    c              3   &   K   | ]	  }|d      yw)r   Nr?   )r   r[   s     r:   r   zlookup_user.<locals>.<genexpr>  s     =QAiL=s   other_accounts_same_deviceother_account_refundedz%Database error during user lookup: %sz'Unexpected error during user lookup: %s)r   cursorexecutefetchoner   getr   fetchallr   r   appendlenr   FREE_TRIAL_DAYSr   r   r   r   r   r   joinr`   r   Errorloggererror	Exception)/rd   r   resultr   r	  r&   deviceall_programsprogbpsubs_with_datesubpiduser_program_idall_sub_rowspaid_periodsrowpriceproductlabelis_free_productlateststart
trial_dayspay_dater   dur_rowdurationduration_daysexpiry	raw_start
sub_lengthuser_tbl
master_tblrequired	core_daysplaceholdersprogress	days_donetotalspeedudid_rowudid
paid_dupesr   es/                                                  r:   lookup_userr>     s;    # !F@C  z	"D y"&&NN $ "% NN $ %( (!-y" y"z	" z	"2 (,|$$(Oy!,0j1A,B()((=17R>>@&)2F:&f_F(:).F:&)2F:&y/    Z!  &0 .3)*( D+ASVs]5912373E/0 .:!TQUU?=S!!T!T+9nQ'tl+C+.F<(58AX	:F9% 77#:;59120a}- +..>*? ( ./1 (.'8 (*#/ 5C$'GGJ$7E'*ww|'<'B&I&I&KG%(WWX%6%<"$C$C$EE $ !46U? !4#*e#3 ,  %0U5\Q5F (.5= ( )//4#5&  ,9=F#564PF=1  #<0A5AE'= >GJ<GX'C D &2"%5F %-VL-A$BE$$*JJx$8$>B#E#E#G<#W1@1D1DS!1LJ/4yj7Q/QH(J(Q(QR\(] %+=$9 05H<PF=$9;C;M;M;O'7 8$(JJL8$;"&$ !''< =  &zz*5A;>vj?Q;R'7 89?K9P9VTVF#56 !'

< 8 !>#)::h#7!>;= ' 4>F<0"NN ,  #-!24 '-oo&7G9@!5d !(#' % 6>F>2&'r#-!c(B/ *
 &.fjj.G%HF(01E(FI%).4y.@-F-F
$.1D$D !'| 4 8=| 4 \*1>r>3::3?H!1!8!8!=J%(AXr1HNN $ z*XK	9
 ;A//:K L3X LI L '*xxY0G'H ( #F8ZF$1y13 $*??#4BJH-=$>PQ	6?86K233</0#1A(B$,-=$>E$,-=$>$C!E7<F#347<F#34@H@WF#<= %
 D/D ##?@
    Z! "??,4<x||GR0"SY"_ CKD4F,FNN $ * "(!2J;>=*==<F78 ( "EE),Q3?CF#;<!	"ky"z	"B MK "UH !My" y"z	"B M == A<a@@ M  C>BBMCs   
Y% Y=Y(	Y1	Y% ;B#YY.YYYMY7YEYY(Y0Y8Y% 
YY	YY"Y% "Y% %[8Z[ Z<<[   <   )r   r   r   r   db_datac           	         g }| d   sddgdS | j                  d      r|j                  d       | j                  d      r'| j                  dd	      }|j                  d
| d       | j                  d      r|j                  d       | j                  d      r/| j                  d      }|rd| dnd}|j                  d|        | j                  d      r|j                  d       | d   <t        j                  | d   d      }| d   |kD  r|j                  d| d| d    d       | j                  d      durC| j                  dd      }t        j                  | d   d       }|j                  d!| d"| d#       | j                  d$      r*| j                  d%      xs d&}|j                  d'| d(       | j                  d)d      dkD  r| d)   }	|j                  d*|	 d+       | j                  d,      rQ| j                  d-d      }
| j                  d.d      }| j                  d/d      }|j                  d0|
 d"| d1| d2       t	        |      dk(  |dS )3z'Calculate eligibility based on DB data.r   NzUser not found in database)eligiblereasonsr   uF   No payment record found — user may be on free trial or B2B provisionr   r   r}   zRepeat subscriber: zY subscription periods found. Money-back guarantee applies only to the first subscription.r   zAccount is deleted in databaser   r   z
 (Program )r   z!User has a prior refund on recordr  z<Another account on the same device already received a refundr   r   r?  z'The refund request must be made within z2 days of purchasing the subscription. It has been z days since purchase.r   Tr   r   r   zProgram not completed: /
 days doner   r   unknownz0User is past first billing cycle (subscription: z(). Renewals are not eligible for refund.r  zFound uV    other paid account(s) on the same device. Possible duplicate — coach should verify.r  r   r   r   zSuspected speed-completion: z chapters done in <10 sec (avg z$s per chapter). Coach should verify.)r  r  REFUND_WINDOWREQUIRED_DAYSr  )rA  rD  nr  extramax_daysr7  r3  r,  countr9  r8  avgs                r:   calculate_db_eligibilityrP    s   G<  .J-KLL{{%&_`{{)*KK4a8!! %K L	

 {{$%78{{%&{{+,(,*TF!$":5'BC{{+,J	
 $%1 $$WY%7<()H4NN9( D&'<=>>SU
 {{&'t3KK 0!4	 $$WY%7;%i[(:F	
 {{< ;;~.;)>xj I4 5	

 {{/3a745UG : ;	

 {{/0,a0,a0kk3Q7*5'5' :5<>	

 G)g>>r@   	form_datadb_eligibilityc                   K   t         st        j                  d       dddS i dt        dt	        j
                         j                         d| j                  d| j                  d	| j                  j                  d
| j                  j                  d| j                  d| j                  j                  d| j                  j                  d| j                  j                  d| j                   d| j"                  d| j$                  d| j&                  ddj)                  | j*                        d|j-                  dd      d|j-                  dd      i d|j-                  dd      d|j-                  dd      d|j-                  dd      d |j-                  d!d      d"|j-                  d#d      d$|j-                  d%d      d&|j-                  d'd      d(|j-                  d)d      d*|j-                  d+d,      d-|j-                  d.d      d/|j-                  d0d      d1|j-                  d2d      d3|j-                  d4d      d5|j-                  d6d      d7|j-                  d8d,      d9|j-                  d:d,      d;|j-                  d<d      |j-                  d=d,      |j-                  d>d?      |j-                  d@d      |j-                  dAd      |j-                  dBd      |j-                  dCd,      |j-                  dDd      dj)                  |j-                  dEg             t/        | |      dF	}|j1                         D ci c]  \  }}|t3        |       }}}	 t5        j6                  dGH      4 dI{   }|j9                  t         |dJK       dI{   }dIdIdI      dI{    t        j;                  dLj<                         dM|j<                  dNS c c}}w 7 i7 K7 =# 1 dI{  7  sw Y   MxY w# t4        j>                  $ r t        jA                  dO       dPdQdcY S tB        $ r%}t        jA                  dR|       dPdSdcY dI}~S dI}~ww xY ww)Tz:Send data to Google Sheet via Apps Script webhook (async).z1Google Sheet webhook URL not configured, skippingskippedzNo webhook URL configured)statusr   secret	timestampr|   rV   platform_reportedprogram_reportedpurchase_date_reportedcompleted_reportedfirst_sub_reportedaccount_active_reportedr   r   paypal_emailform_eligibleform_reject_reasons; db_user_foundr   r   
db_user_idr   db_platformr   
db_programr   db_first_sub_dater   db_days_since_purchaser   db_program_completedr   db_days_completedr   db_prior_refundr   db_account_deletedr   db_no_paymentr   Fdb_payment_amountr   db_payment_currencyr   db_date_sourcer   db_product_idr   db_sub_durationr   db_is_renewalr   db_is_repeat_subscriberr   db_total_sub_periodsr   r  r  r   r   r   r   r  rC  rD  )	db_other_account_refundeddb_other_accounts_same_devicedb_speed_chaptersdb_total_chaptersdb_avg_sec_per_chapterdb_suspected_speeddb_eligibledb_reject_reasons	user_liedT)follow_redirectsNg      .@)jsontimeoutzGoogle Sheet write: status=%dsuccess)rU  codezGoogle Sheet webhook timed outr  zWebhook timeoutzGoogle Sheet webhook error: %szWebhook failed)"r,   r  warningr-   r   nowr   r|   rV   r   valuer   r   r   r   r   r   r   r   r   r  r   r  _check_discrepanciesitemsr   httpxAsyncClientpostinforH   TimeoutExceptionr  r  )	rQ  rA  rR  payloadkr   clientrespr=  s	            r:   write_to_google_sheetr  ;  s     $JK#/JKK/%/X\\^--// 		/
 		/ 	Y//55/ 	I--33/ 	!)"8"8/ 	i1177/ 	i0066/ 	"9#:#:#@#@/ 	)""/ 	9$$/ 		--/ 	///  	tyy)D)DE!/$ 	\26%/& 	gkk)R0'/( 	w{{:r2)/* 	gkk)R0+/, 	W[[)92>-/. 	!'++.CR"H//0 	,? D1/2 	W[[)92>3/4 	7;;'92>5/6 	gkk*;R@7/8 	%7?9/: 	W[[)92>;/< 	w{{+=rB=/> 	'++mR8?/@ 	\26A/B 	7;;~r:C/D 	\59E/F 	"7;;/Eu#MG/H 	,H" MI/J &-[[1I5%Q)05QST)U$[[)92>$[[)92>")++.G"L%kk*FN%))*b9!YY~'9'9)R'HI))W=]/Gd -4MMO<DAqq*Q-<G<?$$d; 	 	v(w %  D	 	 	3T5E5EF#T-=-=>> =		 	 	 	 !! @56!->?? ?5q9!-=>>?s   MQ4O42Q46P O:P P 0O<1P 5P  O>2P 3Q4:P <P >P  PP	PP -Q1Q4Q1Q,&Q1'Q4,Q11Q4c                    g }|j                  d      sy| j                  j                  j                         }|j                  d      xs dj                         }||k7  r?|dk7  r:|j	                  d| j                  j                   d|j                  d              | j
                  j                  }|j                  d      xs dj                         }|d	k7  r+||k7  r&|j	                  d
| d|j                  d              | j                  t        j                  k(  r8|j                  d      du r%|j	                  d|j                  dd       d       | j                  t        j                  k(  r"|j                  d      r|j	                  d       | j                  t        j                  k(  rA|j                  d      r0|j	                  dj                  |j                  dd                   | j                  t        j                  k(  r"|j                  d      r|j	                  d       |rdj                  |      S dS )z9Compare user-reported vs DB values. Return discrepancies.r   zUser not found in DBr   r   rH  zPlatform: said z
, DB says r   unsurezProgram: said r   Fz$Completion: said completed, DB says r   r   rG  r   z*First sub: said yes, DB shows prior refundr   z5First sub: said yes, DB shows {} subscription periodsr   z2+r   z&Account: said active, DB shows deletedra  zNo discrepancies)r  r   r  r   r  r   r   rw   rx   r   r   r   r  )rQ  rA  discrepanciesform_platformrd  form_programre  s          r:   r  r    s   M;;|$% &&,,224M;;z*0b779K#y(@i00667z'++jBYAZ[	

 $$**L++i(.B557JxLJ$>\N*W[[5K4LM	

 eii'GKK8K,LPU,U27;;?OQR3S2TT^_	

 UYY&7;;7I+JIJUYY&7;;7M+NCJJ8$?	
 %))+<M0NEF'4499]#L:LLr@   ELIGIBLE_SECRETz
2026-06-24$   a	  
SELECT /*+ MAX_EXECUTION_TIME(15000) */
    u.iUserID                                          AS user_id,
    upf.vName                                          AS name,
    CASE WHEN LOWER(u.vDeviceType) LIKE '%%android%%' THEN 'Android'
         WHEN LOWER(u.vDeviceType) LIKE '%%ios%%' OR LOWER(u.vDeviceType) LIKE '%%iphone%%' THEN 'iOS'
         ELSE 'Other' END                              AS platform,
    uc.vSubIntent                                      AS sub_intent,
    upr.dFirstSubDate                                  AS purchase_date,
    comp.last_completion                               AS completed_at,
    ROUND(TIMESTAMPDIFF(HOUR, upr.dFirstSubDate, comp.last_completion)) AS hrs_to_complete,
    pay.vSubscriptionId                                AS payment_ref
FROM tbl_Users u
JOIN tbl_UserConfig    uc  ON uc.iUserID  = u.iUserID
LEFT JOIN tbl_UserProfile upf ON upf.iUserID = u.iUserID
JOIN tbl_UserPrograms  upr ON upr.iUserID = u.iUserID AND upr.iProgramId = 3
-- completion gate: all core chapters (Days 1-6, even-variant) completed
JOIN (
    SELECT uc3.iUserID,
           MAX(uc3.dDateUpdated)          AS last_completion,
           COUNT(DISTINCT uc3.iChapterID) AS done
    FROM tbl_User3Chapters uc3
    JOIN tbl_3Chapters c3
      ON c3.iChapterId = uc3.iChapterID
     AND c3.iDayId BETWEEN 1 AND 6
     AND c3.bDeleted = 0 AND c3.bActive = 1 AND c3.iABTest IN (0,1)
    WHERE uc3.bCompleted = 1
    GROUP BY uc3.iUserID
    HAVING done = %s
) comp ON comp.iUserID = u.iUserID
-- paid gate: real payment exists (excludes free-trial-only); pulls amount + payment reference
JOIN (
    SELECT iUserProgramID, vSubscriptionId, decPrice, vCurrency,
           ROW_NUMBER() OVER (PARTITION BY iUserProgramID ORDER BY dStartDate DESC) rn
    FROM UserSubHistory
    WHERE decPrice > 0 AND (bRestore = 0 OR bRestore IS NULL)
) pay ON pay.iUserProgramID = upr.iUserProgramID AND pay.rn = 1
WHERE u.iProgramID = 3
  AND u.bDeleted = 0 AND u.bTestUser = 0
  AND u.iUserID %% 2 = 0                                   -- even users
  AND uc.vSubIntent IN ('high','medium-high')             -- high intent
  AND upr.bPreviouslySubscribed = 1                       -- paid (gate); prior refund is now an info column
  AND comp.last_completion >= upr.dFirstSubDate           -- finished after buying
  AND comp.last_completion <= upr.dFirstSubDate + INTERVAL 6 DAY   -- within 6x24h
  AND upr.dFirstSubDate >= %s                             -- on/after offer launch
ORDER BY comp.last_completion ASC

since_datec                    t               5 }|j                         5 }|j                  t        t        | f       |j                         }ddd       ddd       g }D ]  }|j                         D ci c]  \  }}|t        |       }}}t        |d         }	|j                  i d|	j                  dd      d|	j                  dd      d|	j                  dd      d	|	j                  d	d      d
|	j                  d
d      d|	j                  dd      d|	j                  dd      d|	j                  dd      d|	j                  dd      d|	j                  dd      d|	j                  dd      d|	j                  dd      d|	j                  dd      d|	j                  dd      d|	j                  dd      d|	j                  dd      d|	j                  dd      |	j                  dd      |	j                  dd      d       |j                  |j                         D ci c]  \  }}|t        |       c}}        |S # 1 sw Y   xY w# 1 sw Y   xY wc c}}w c c}}w )u  Return all users eligible for the automatic refund since `since_date`.

    Gate = paid + completed 6 days within 6 days (ELIGIBLE_SQL). Every row is then
    enriched with the SAME informational checks the refund form computes (via
    lookup_user), so the sheet's columns always match the form. These checks are
    informational only — they never remove anyone from the list.
    Nr   )r   r   Fr   r   r   r   r   r   r   r   r   r  r  r   r   r   r   r   r  r   r   r   )r   r   )r   r	  r
  ELIGIBLE_SQLCORE_CHAPTER_COUNTr  r  r   r>  updater  r  )
r  r   r	  rowsoutr[   r  r   r"  r  s
             r:   get_auto_eligibler    s    
	 %[[] 	%fNN<*<j)IJ??$D	%%
 C ?,-GGI6DAqq*Q-661Y<0

 
2De)L
2BB)G
 2Db)I
 ,)C	

 .")E
 #2H%)P
 )$((3OQS*T
 2De)L
 2BB)G
 %2JE)R
 )$((3OQR*S
 2CU)K
 "2G)L
 2BB)G
 ,)F
  )$((3OQV*W!
" 2BB)G#
$ *.2BB)G)-2KR)P'
 	* 	

=AAz!}$=>1?2 J=	% 	%% % 7. >s.   I#-I	I#4I03I6I 	I##I-rF  )response_classr1   c                 :   K   t         j                  dd| i      S w)z"Serve the refund eligibility form.z
index.htmlr1   )rW   TemplateResponse)r1   s    r:   
serve_formr    s       %%lY4HIIs   z/healthc                     K   d} 	 t               5 }|j                         5 }|j                  d       d} ddd       ddd       | rd| dS d| dS # 1 sw Y   xY w# 1 sw Y   !xY w# t        $ r Y /w xY ww)z8Health check endpoint for load balancers and monitoring.FzSELECT 1TNhealthydegraded)rU  db)r   r	  r
  r  )db_okr   r	  s      r:   health_checkr    s      E  	D &z*	 $)iFFjFF 	 	  sT   A8
A) AAAA) A8A	AA&"A) )	A52A84A55A8z	/eligiblez	30/minuterV  sincec                 ~  K   |t         k7  rt        dddd      S |xs t        }	 t        j                  |d       	 t        |      }t        dt        |      ||d      S # t
        $ r t        dddd      cY S w xY w# t        $ r0}t        j                  d	|       t        d
ddd      cY d}~S d}~ww xY ww)zReturn all users eligible for the automatic refund (proactive daily list).

    Called by the Google Sheet button. De-duplication happens in the Sheet, so this
    always returns the FULL eligible set since `since` (default = offer launch date).
    i  r  UnauthorizedrU  rF   rG   r     zsince must be YYYY-MM-DDzAuto-eligible query failed: %si  zQuery failedNr  )rU  rN  r  users)r  r   OFFER_LAUNCH_DATEr   r   r   r  r  r  r  r  )r1   rV  r  r  r  r=  s         r:   auto_eligibler  -  s       Sa5bcc++Jp*j1d!*-
 9s5zJafghh  pSm5noop
  d5q9Sa5bccdsQ   $B=A" B 	B="A>;B==A>>B=	B:
%B5/B:0B=5B::B=z/submit-refundz5/minutec                 B  K   	 | j                          d{   }t        di |}t        |j                        }t        j                  d|dd	        |t               v rt        d
ddd      S t        |      }t        |      }t        |||       d{   }t        |       t        d|j                  |j                  d      |j                  d      d      S 7 # t        $ r0}t        j	                  d|       t        dddd      cY d}~S d}~ww xY w7 w)zFProcess refund form submission with DB verification and Sheet logging.NzInvalid form submission: %sr  r  zInvalid form datar  rG   z-Refund submission received for email hash: %s   i  	duplicatezA refund request has already been submitted for this email. Only the first submission will be considered. If you need to update your request, please email hello@quitsure.app.r  rC  rU  )rU  r_  r{  sheet_statusr?   )r  r{   r  r  r  r   r   r|   r  rc   r>  rP  r  ri   r   r  )r1   bodyrQ  r=  rd   rA  rR  sheet_results           r:   submit_refundr  G  s(    
\\^#"*T*	 Y__-J
KK?CRQ +--%[

 
	
 *%G-g6N /y'>RRL $"//%))*5$((2	  I $ 
4a8&2EF
 	

: SsQ   DC! CC! A0DDADC! !	D*%DDDDD__main__zapp:appz0.0.0.0PORTi@  ENV
production)r%   portreload)NN)r   r   )g__doc__r   loggingos
contextlibr   r   r   r   enumr   typingr   r  r   dotenvr	   fastapir
   r   fastapi.middleware.corsr   fastapi.responsesr   r   fastapi.staticfilesr   fastapi.templatingr   pydanticr   r   r   r   slowapir   slowapi.errorsr   slowapi.utilr   starlette.middleware.baser   starlette.responsesr   basicConfigINFO	getLoggerr  environr  r   r,   r-   limiterr0   appstateadd_exception_handleradd_middlewaremountrW   pathr  dirname__file__r]   makedirsr`   rc   r   ri   rk   rs   rw   r{   r   r   r   r   r  intdictr>  rI  rJ  rP  r  r  r  r  r  r  r   r  r  r  limitr  r  r  r<   uvicornrunr?   r@   r:   <module>r     s  
   	 % . .      $ 2 8 + . : :  , + 8 (    
,,> 
		=	) JJNN9b)JJNN9b)

}b1

y*?@	 ::>>*DbI jjnn%:B?  -
. 2 & 
 	 		    + .    , -   ::>>"2C89&/!"	   		)[848	 Dk2	 77<< 96CYZ  BGGOO,- = #S #T #3 #t 
C 
"Y "L  	Ec Ec E

 a&RC R R Rl  B/2.D?d D?t D?PH?H?(,H?>BH?	H?V+MN +MT +Mc +Mf **..!24GH   .b'# '$ 'V \*Jg J, J +J
 
GD 
G 
G {i i# i3 iP\ i  i0 
z, ,\ ,  ,` zGKK-.zz~~e\2lB	 r@   