
    i(0                       U d Z ddlmZ ddlZddlZddlZddlmZmZmZm	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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mZm Z m!Z!  ee"      Z#ddZ$da%de&d<    ejN                         Z(d dZ)d!dZ*d"dZ+d"dZ, ed      Z- ed      Z. ed      Z/ ed      Z0 ed      Z1 ed      Z2 G d d      Z3da4de&d<    ejN                         Z5d#d$dZ6y)%u  
Repository for profiling storage (SQLAlchemy ORM).

Published vs staging
--------------------
* **user_profiling** — what admins read; updated by API/forced runs and by
  :meth:`publish_staging_to_published`.
* **user_profiling_staging** — batch writes only; no tracker updates until publish.
* **publish_staging_to_published** — one transaction: MERGE staging → published,
  MERGE tracker from staging user_ids, TRUNCATE staging. Rollback leaves published unchanged.
    )annotationsN)datedatetime	timedeltatimezone)Decimal)AnyDictOptional)
quote_plus)create_engineselecttext)Engine)Sessionsessionmaker)
get_logger)BaseUserProfilingUserProfilingStagingUserProfilingTrackerc                    | j                  t        t              j                  t        j                  |k(        j                  d            j                         S )N   )scalarsr   r   whereuser_idlimitfirst)sessionr   s     >/var/www/html/userprofiledev.eatanceapp.com/storage/db_repo.py_tracker_row_for_userr!   !   sF    ??#$**+?+G+G7+RSYYZ[\eg    Optional[Engine]_enginec                    t        | t        t        f      r| j                         S t        | t              rt        |       S t        |       S N)
isinstancer   r   	isoformatr   floatstr)objs    r    _json_defaultr,   +   s:    #$'(}}#wSzs8Or"   c                :    t        j                  | t        d      S )NF)defaultensure_ascii)jsondumpsr,   )profile_datas    r    profile_data_to_jsonr3   3   s    ::lMNNr"   c                    t        j                  dd      } t        j                  dd      }t        j                  d      }t        j                  d      }|t        |      j                         st	        d      |t	        d      t        |      j                         }|}t        j                  d	d
      }|j                  d      }t        t        j                  dd            }t        j                  dd      j                         j                         }	|	dv }
d| dd|  dd| dd| dd| ddg}dj                  |      }dt        |       }t        |d|d      S )N	DB_SERVER	localhostDB_NAMEprofiling_dbDB_USERDB_PASSWORDzeDB_USER must be set to a non-empty value (no default credentials). Set it in the environment or .env.zHDB_PASSWORD must be set (no default). Set it in the environment or .env.	DB_DRIVERz{ODBC Driver 18 for SQL Server}z{}DB_POOL_SIZE7DB_TRUST_SERVER_CERTIFICATE )1trueyesonzDriver={z};zServer=;z	Database=zUID=zPWD=zTrustServerCertificate=yes;zmssql+pyodbc:///?odbc_connect=Tr   )pool_pre_ping	pool_sizemax_overflow)
osgetenvr*   strip
ValueErrorintlowerjoinr   r   )serverdatabaseraw_userraw_passwordusernamepassword
driver_rawdriverrF   	trust_raw
trust_cert
odbc_partsodbcurls                 r    _build_enginer\   7   s{   YY{K0FyyN3Hyy#H99]+Ls8}2241
 	
 V
 	
 8}""$HH;(IJJd#FBIInc23I		7<BBDJJLI88J
F83
&
H:Q
xj
xj
%J 77:D*:d+;*<
=C	 r"   c                 f    t         5  t        
t               at        cd d d        S # 1 sw Y   y xY wr&   )_engine_lockr$   r\    r"   r    
get_enginer`   `   s    	?#oG 
s   '0zo
    DELETE FROM dbo.user_profiling
    WHERE user_id IN (SELECT user_id FROM dbo.user_profiling_staging);
    z
    INSERT INTO dbo.user_profiling (user_id, created_at, profile_json, deep_analysis_markdown)
    SELECT user_id, updated_at, profile_json, deep_analysis_markdown
    FROM dbo.user_profiling_staging;
    a[  
    MERGE dbo.user_profiling_tracker AS T
    USING (SELECT DISTINCT user_id FROM dbo.user_profiling_staging) AS S
    ON T.user_id = S.user_id
    WHEN MATCHED THEN
      UPDATE SET last_completed_at = SYSUTCDATETIME()
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (user_id, last_completed_at)
      VALUES (S.user_id, SYSUTCDATETIME());
    z*TRUNCATE TABLE dbo.user_profiling_staging;z8DELETE FROM dbo.user_profiling WHERE user_id = :user_id;z
    INSERT INTO dbo.user_profiling (user_id, created_at, profile_json, deep_analysis_markdown)
    VALUES (:user_id, :created_at, :profile_json, :deep_analysis_markdown);
    c                      e Zd ZdZdddZddZddZ	 d	 	 	 	 	 	 	 ddZ	 d	 	 	 	 	 	 	 ddZddZ	dd	Z
dd
ZddZddZ	 d	 	 	 	 	 	 	 ddZy)ProfilingRepositoryzPublished + staging + tracker.Nc                p    |xs
 t               | _        t        | j                  t        ddd      | _        y )NF)bindclass_expire_on_commit	autoflush
autocommit)r`   r$   r   r   _sf)selfengines     r    __init__zProfilingRepository.__init__   s/    -"
r"   c                    t         j                  j                  | j                         t        j                  d       y )NzGEnsured ORM metadata (user_profiling, user_profiling_staging, tracker).)r   metadata
create_allr$   loggerinfo)rj   s    r    ensure_tablesz!ProfilingRepository.ensure_tables   s%      .]^r"   c                    | j                         5 }|j                         5  |j                  t               ddd       ddd       t        j                  d       y# 1 sw Y   'xY w# 1 sw Y   +xY w)zOEmpty staging before a new full batch (call only when starting from user id 0).Nz%Truncated dbo.user_profiling_staging.)ri   beginexecute_TRUNCATE_STAGINGrp   rq   rj   r   s     r    truncate_stagingz$ProfilingRepository.truncate_staging   sL    XXZ7 12 !  	;< ! Zs!   A*AA*A'	#A**A3c                   t        |      }|d}d}n|j                         dk(  rd}d}n|}d}t        j                  t        j
                        j                  d      }| j                         5 }|j                         5  |r|j                  t        t              j                  t        j                  |k(        j                  t        j                  j!                               j#                  d            j%                         }	|	=|	j&                  r1t)        |	j&                        j                         }
|
r|	j&                  }||||d}|j+                  t,        d|i       |j+                  t.        |       t1        ||      }|||_        n|j5                  t7        ||	             ddd       ddd       t8        j;                  d
|       y# 1 sw Y   (xY w# 1 sw Y   ,xY w)a  
        API / forced path: upsert **published** row + tracker in one transaction.

        ``deep_analysis_markdown=None`` means omit: existing stored markdown is kept.
        Pass ``""`` (or whitespace-only, normalized to empty) to clear the column.
        NTr?   Ftzinfor   )r   
created_atprofile_jsondeep_analysis_markdownr   r   last_completed_atz,Upserted published user_profiling user_id=%s)r3   rJ   r   nowr   utcreplaceri   rt   r   r   r   r   r   order_byiddescr   r   r~   r*   ru   _DELETE_PUBLISHED_FOR_USER_INSERT_PUBLISHED_ROWr!   r   addr   rp   rq   )rj   r   r2   r~   payloaddeep_valpreserve_markdownr   r   prevsinsert_paramstrs                r    upsert_publishedz$ProfilingRepository.upsert_published   s    '|4!)&*H $#))+r1H %-H %ll8<<(000=XXZ7$"??}-}44?@!-"2"2"7"7"9:q	
 eg  'D,G,G ; ;<BBD'+'B'BH  '"%$+.6	!  :Y<PQ 5}E*7G<>+.B(KK 4WX[ \]5 ! : 	BGL9 ! Zs%   2G%D/G2G%G"	G%%G.c                   t        |      }||nd}||j                         dk(  rd}t        j                  t        j
                        j                  d      }| j                         5 }|j                         5  |j                  t        |      }|t        |      }|j                  |       ||_        ||_        ||_        ddd       ddd       t        j!                  d|       y# 1 sw Y   (xY w# 1 sw Y   ,xY w)za
        Batch path: upsert **staging** only (no tracker). Published/API data unchanged.
        Nr?   rz   )r   zUpserted staging user_id=%s)r3   rJ   r   r   r   r   r   ri   rt   getr   r   r}   r~   
updated_atrp   debug)	rj   r   r2   r~   r   r   r   r   rows	            r    upsert_stagingz"ProfilingRepository.upsert_staging   s     '|4-C-O)UYHNN$4$:Hll8<<(000=XXZ7kk"6@;.w?CKK$#* -5*!$ !  	2G< ! Zs%   ,C;=AC/C;/C8	4C;;Dc                j   | j                         5 }|j                         5  |j                  t               |j                  t               |j                  t
               |j                  t               ddd       ddd       t        j                  d       y# 1 sw Y   'xY w# 1 sw Y   +xY w)z
        Promote all staging rows to published + refresh tracker for those users + clear staging.
        Single transaction; on error nothing visible changes for readers.
        Nu;   Published staging → user_profiling and truncated staging.)	ri   rt   ru   #_DELETE_PUBLISHED_FOR_STAGING_USERS_INSERT_PUBLISHED_FROM_STAGING_MERGE_TRACKER_FROM_STAGINGrv   rp   rq   rw   s     r    publish_staging_to_publishedz0ProfilingRepository.publish_staging_to_published   su    
 XXZ7 CD >? ;< 12	 !  	QR ! Zs#   B)AB7B)B&	"B))B2c                   |dk  ryt        j                  t        j                        j	                  d       t        |      z
  }| j                         5 }t        t        j                        j                  t        j                  |k(        j                  t        j                  |k\        j                  d      }|j                  |      d ucd d d        S # 1 sw Y   y xY w)Nr   Frz   )daysr   )r   r   r   r   r   r   ri   r   r   r   r   r   r   scalar)rj   r   recency_dayscutoffr   stmts         r    should_skip_scheduled_runz-ProfilingRepository.should_skip_scheduled_run
  s    1hll+3343@9R^C__XXZ7+334+33w>?+==GHq	  >>$'t3 ZZs   A;CC%c                b   t        j                  t        j                        j	                  d       }| j                         5 }|j                         5  t        ||      }|||_        n|j                  t        ||             d d d        d d d        y # 1 sw Y   xY w# 1 sw Y   y xY w)Nrz   r   )r   r   r   r   r   ri   rt   r!   r   r   r   )rj   r   r   r   r   s        r    touch_trackerz!ProfilingRepository.touch_tracker  s~    ll8<<(000=XXZ7*7G<>+.B(KK 4WX[ \] ! Z  Zs$   B%3BB%B"	B%%B.c                    | j                         5 }t        ||      }||j                  |       |cd d d        S # 1 sw Y   y xY wr&   )ri   r!   expungerj   r   r   r   s       r    get_tracker_rowz#ProfilingRepository.get_tracker_row!  s7    XXZ7'9C$	 ZZs	   !<Ac                   | j                         5 }|j                  t        t              j	                  t        j
                  |k(        j                  t        j                  j                               j                  d            j                         }||j                  |       |cddd       S # 1 sw Y   yxY w)z3Latest published row for user_id (admin read path).r   N)ri   r   r   r   r   r   r   r   r   r   r   r   r   s       r    get_snapshotz ProfilingRepository.get_snapshot(  s    XXZ7//}%},,78-**//12q	
 eg  $ ZZs   BB66B?c                *    | j                  |||       y r&   )r   )rj   r   r2   r~   s       r    insert_snapshotz#ProfilingRepository.insert_snapshot6  s     	g|5KLr"   r&   )rk   r#   )returnNone)r   rL   r2   Dict[str, Any]r~   zOptional[str]r   r   )r   rL   r   rL   r   bool)r   rL   r   r   )r   rL   r   Optional[UserProfilingTracker])r   rL   r   zOptional[UserProfiling])__name__
__module____qualname____doc__rl   rr   rx   r   r   r   r   r   r   r   r   r_   r"   r    rb   rb      s    (
_= 15	6M6M %6M !.	6M
 
6Mx 15	== %= !.	=
 
=6S4^$ 15	MM %M !.	M
 
Mr"   rb   zOptional[ProfilingRepository]_repo_singletonc                    | t        |       S t        5  t        
t               at        cddd       S # 1 sw Y   yxY w)z
    Default singleton uses env-driven :func:`get_engine`.
    Pass a SQLAlchemy :class:`~sqlalchemy.engine.Engine` for tests or an alternate database.
    Nrk   )rb   
_repo_lockr   r   s    r    get_profiling_repositoryr   C  s3    
 "&11	"13O 
s   5>)r   r   r   rL   r   r   )r+   r	   r   r	   )r2   r   r   r*   )r   r   r&   )rk   r#   r   rb   )7r   
__future__r   r0   rH   	threadingr   r   r   r   decimalr   typingr	   r
   r   urllib.parser   
sqlalchemyr   r   r   sqlalchemy.enginer   sqlalchemy.ormr   r   core.logger_configr   storage.db_modelr   r   r   r   r   rp   r!   r$   __annotations__Lockr^   r,   r3   r\   r`   r   r   r   rv   r   r   rb   r   r   r   r_   r"   r    <module>r      s  
 #  	  8 8  & & # 2 2 $ 0 ) \ \	H	 !	  y~~O&R '+' # "&"  #	  EF  ""\]  jM jMZ 26. 5Y^^
r"   