
    ~j                    8   d Z ddlmZ ddlZddlZddlmZ ddlmZ ddl	m
Z
mZ ddlZddlmZ ddlmZ dd	lmZ dd
lmZ  ej        e          Z ee                                          j        j        dz  ZddZddZ G d d          Z e            ZdS )u'  PostgreSQL access for city_briefs_ops.

Mirrors meta-ads-mcp/agent/db.py — psycopg (v3) AsyncConnectionPool with a
checksum-tracked migration runner. Two tables only: `city_briefs` (one row per
show) and `city_briefs_runs` (per-run stage-status rows). This service never
touches any other DB.
    )annotationsN)asynccontextmanager)Path)AnyAsyncIterator)AsyncConnection)Jsonb)AsyncConnectionPool)settings
migrationsvaluer   returnc                \    t          | t          t          f          rt          |           S | S )z<Wrap JSON-compatible values so psycopg stores them as jsonb.)
isinstancedictlistr	   )r   s    -/workspace/show-city-briefing-agent/app/db.pyadaptr      s)    %$&& U||L    paramstuple[Any, ...] | Nonec                <    | d S t          d | D                       S )Nc              3  4   K   | ]}t          |          V  d S N)r   ).0vs     r   	<genexpr>zadapt_params.<locals>.<genexpr>'   s(      **aq******r   )tuple)r   s    r   adapt_paramsr   $   s)    ~t**6******r   c                      e Zd ZdZd#d$dZd%dZd%dZed&d            Zd'd(dZ	d'd)dZ
d'd*dZd'd+dZed,d            Zd'd-d"ZdS ).Databasez:One pool per database url. Test-friendly via injected url.N      url
str | Nonemin_sizeintmax_sizer   Nonec                V    |pt           j        | _        || _        || _        d | _        d S r   )r   DATABASE_URL_url	_min_size	_max_size_pool)selfr$   r&   r(   s       r   __init__zDatabase.__init__-   s*    080	!!15


r   c                   K   | j         Mt          | j        | j        | j        dddi          | _         | j                             d           d {V  d S d S )NF
autocommit)r&   r(   openkwargsT)wait)r/   r
   r,   r-   r.   r4   r0   s    r   r4   zDatabase.open3   sv      :,	$e,  DJ *//t/,,,,,,,,,,, r   c                h   K   | j         (| j                                          d {V  d | _         d S d S r   )r/   closer7   s    r   r9   zDatabase.close>   sG      :!*""$$$$$$$$$DJJJ "!r   AsyncIterator[AsyncConnection]c                  K   | j         |                                  d {V  | j         J | j                                         4 d {V }|W V  d d d           d {V  d S # 1 d {V swxY w Y   d S r   )r/   r4   
connection)r0   conns     r   r<   zDatabase.connectionC   s     :))++z%%%:((** 	 	 	 	 	 	 	dJJJJ	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	s   A%%
A/2A/querystrr   r   
list[dict]c                  K   |                                  4 d {V }|                    t          j        j                  4 d {V }|                    |t          |                     d {V  |                                 d {V cd d d           d {V  cd d d           d {V  S # 1 d {V swxY w Y   	 d d d           d {V  d S # 1 d {V swxY w Y   d S )Nrow_factory)r<   cursorpsycopgrowsdict_rowexecuter   fetchallr0   r>   r   r=   curs        r   fetchzDatabase.fetchK   s,     ??$$ 	, 	, 	, 	, 	, 	, 	,{{w|/D{EE , , , , , , ,kk%f)=)=>>>>>>>>> \\^^++++++, , , , , , , , , , , , ,	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	,, , , , , , , , , , , , , , ,	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	,s6   ,C	AB1C1
B;	;C>B;	?C
C #C dict | Nonec                V   K   |                      ||           d {V }|r|d         nd S Nr   )rL   )r0   r>   r   rF   s       r   fetchonezDatabase.fetchoneQ   s>      ZZv........(tAwwD(r   c                V  K   |                                  4 d {V }|                                4 d {V }|                    |t          |                     d {V  d d d           d {V  n# 1 d {V swxY w Y   d d d           d {V  d S # 1 d {V swxY w Y   d S r   )r<   rD   rH   r   rJ   s        r   rH   zDatabase.executeU   s     ??$$ 	? 	? 	? 	? 	? 	? 	?{{}} ? ? ? ? ? ? ?kk%f)=)=>>>>>>>>>? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	? 	?s4   B*A4"B4
A>	>BA>	B
B"%B"r   c                  K   |                                  4 d {V }|                                4 d {V }|                    |t          |                     d {V  |                                 d {V }||d         nd cd d d           d {V  cd d d           d {V  S # 1 d {V swxY w Y   	 d d d           d {V  d S # 1 d {V swxY w Y   d S rO   )r<   rD   rH   r   rP   )r0   r>   r   r=   rK   rows         r   fetchvalzDatabase.fetchvalZ   s2     ??$$ 	; 	; 	; 	; 	; 	; 	;{{}} ; ; ; ; ; ; ;kk%f)=)=>>>>>>>>>LLNN******!$s1vvd; ; ; ; ; ; ; ; ; ; ; ; ;	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	;; ; ; ; ; ; ; ; ; ; ; ; ; ; ;	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	; 	;s5   CAB,C,
B6	6C9B6	:C
CCr=   r   c                @   K   |                      d           d {V  d S )Nz
            CREATE TABLE IF NOT EXISTS schema_migrations (
                version    TEXT PRIMARY KEY,
                applied_at TIMESTAMPTZ NOT NULL DEFAULT now(),
                checksum   TEXT NOT NULL DEFAULT ''
            )
            )rH   )r=   s    r   _ensure_migrations_tablez!Database._ensure_migrations_tablea   sI      ll
 
 	
 	
 	
 	
 	
 	
 	
 	
 	
r   migrations_dirPath | None	list[str]c                z  K   |pt           }g }|                                 4 d {V }|                     |           d {V  |                    t          j        j                  4 d {V }|                    d           d {V  d |                                 d {V D             }d d d           d {V  n# 1 d {V swxY w Y   t          |
                    d                    D ]!}|j        }|                                }t          j        |                                                                          }	||v r ||         |	k    rt#          d| d          zt$                              d|           |                                4 d {V  |                    |           d {V  |                    d||	f           d {V  d d d           d {V  n# 1 d {V swxY w Y   |                    |           #	 d d d           d {V  n# 1 d {V swxY w Y   |S )	NrB   z/SELECT version, checksum FROM schema_migrationsc                ,    i | ]}|d          |d         S )versionchecksum )r   rS   s     r   
<dictcomp>z$Database.migrate.<locals>.<dictcomp>t   s"    XXXCIJXXXr   z*.sqlz
Migration zE is already applied but its checksum does not match the file on disk.zapplying migration %szAINSERT INTO schema_migrations (version, checksum) VALUES (%s, %s))MIGRATIONS_DIRr<   rV   rD   rE   rF   rG   rH   rI   sortedglobstem	read_texthashlibsha256encode	hexdigestRuntimeErrorloggerinfotransactionappend)
r0   rW   appliedr=   rK   seenpathr\   sqlr]   s
             r   migratezDatabase.migratem   s5     '9>??$$ 	( 	( 	( 	( 	( 	( 	(//555555555{{w|/D{EE Y Y Y Y Y Y Ykk"STTTTTTTTTXXCWCWCWCWCWCWXXXY Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y ~227;;<< ( ()nn&&">#**,,77AACCd??G}00*I I I I   3W===++--        ,,s+++++++++,,[ (+                                   w''''%(	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	(2 s\   AH*/A C/H*
C	H*C	CH*#:G/H*/
G99H*<G9=H**
H47H4)Nr"   r#   )r$   r%   r&   r'   r(   r'   r   r)   )r   r)   )r   r:   r   )r>   r?   r   r   r   r@   )r>   r?   r   r   r   rM   )r>   r?   r   r   r   r)   )r>   r?   r   r   r   r   )r=   r   r   r)   )rW   rX   r   rY   )__name__
__module____qualname____doc__r1   r4   r9   r   r<   rL   rP   rH   rT   staticmethodrV   rr   r^   r   r   r!   r!   *   s       DD6 6 6 6 6	- 	- 	- 	-   
    , , , , ,) ) ) ) )? ? ? ? ?
; ; ; ; ; 	
 	
 	
 \	
      r   r!   )r   r   r   r   )r   r   r   r   )rv   
__future__r   re   logging
contextlibr   pathlibr   typingr   r   rE   r   psycopg.types.jsonr	   psycopg_poolr
   
app.configr   	getLoggerrs   rj   __file__resolveparentr`   r   r   r!   dbr^   r   r   <module>r      sb    # " " " " "   * * * * * *       % % % % % % % %  # # # # # # $ $ $ $ $ $ , , , , , ,      		8	$	$h''))07,F   + + + +_ _ _ _ _ _ _ _F XZZr   