
    jl                    d    d dl mZ d dlZd dlZd dlZd dlZd dlmZ d dlm	Z	  G d d          Z
dS )    )annotationsN)date)Anyc                  X    e Zd Z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
d d!dZdS )"PostgresRepositorya  Read-only repository for the Liangzhu ecommerce PostgreSQL database.

    Default mode uses SSH to run psql on `wwyl-cloud` so no DB password is stored in
    this project. When deployed on the cloud host later, set
    LIANGZHU_PSQL_MODE=local to call local psql directly.
    Nliangzhu_qianniussh_host
str | Nonedb_namestrc                    |pt          j        dd          | _        || _        t          j        dd          | _        d S )NLIANGZHU_DB_SSH_HOSTz
wwyl-cloudLIANGZHU_PSQL_MODEssh)osgetenvr	   r   mode)selfr	   r   s      u   /Users/bot1/Volumes/root_for_ai/AI工作区/良渚_电商数据看板_前后端分离_20260602_0257/app/repository.py__init__zPostgresRepository.__init__   s:     SBI.Dl$S$SI2E::			    sqlreturnlist[dict[str, Any]]c           	        d|z   dz   }| j         dk    rddddd| j        d	d
|g	}n@dt          j        | j                  z   dz   t          j        |          z   }ddd| j        |g}t          j        |ddd          }|j        r%t          |j	        p|j
        pddd                    t          j        |j
        pd                                pd          S )Nz:SELECT COALESCE(jsonb_agg(to_jsonb(t))::text, '[]') FROM (z) t;localsudoz-upostgrespsqlz-dz-Atz-czsudo -u postgres psql -d z -At -c r   z-ozBatchMode=yesT   )textcapture_outputtimeoutzpsql failedi0z[])r   r   shlexquoter	   
subprocessrun
returncodeRuntimeErrorstderrstdoutjsonloadsstrip)r   r   wrappedcmdremoteprocs         r   
_psql_jsonzPostgresRepository._psql_json   s    NQTTW]]94VT4<PTV]^CC05;t|3L3LLzY\a\gho\p\ppF$GC~cT3OOO? 	V Kt{ KmUVVTUUUz4;.$5577?4@@@r   c                j    |                      d          }|r|d                             d          nd S )NzTSELECT max(stat_date)::text AS latest_date FROM mart.omnichannel_daily_store_summaryr   latest_date)r3   getr   rowss     r   r5   zPostgresRepository.latest_date$   s4    uvv-1;tAw{{=)))t;r   	list[str]c                D    |                      d          }d |D             S )Nzu
SELECT stat_date::text AS stat_date
FROM mart.omnichannel_daily_store_summary
GROUP BY stat_date
ORDER BY stat_date
c                H    g | ]}|                     d           |d           S )	stat_date)r6   ).0rs     r   
<listcomp>z6PostgresRepository.available_dates.<locals>.<listcomp>/   s-    CCC1k0B0BC+CCCr   r3   r7   s     r   available_datesz"PostgresRepository.available_dates(   s1         DCCCCCr   
start_dater   end_datec                :    |                      d| d| d          S )Na  
SELECT source_platform, store_name,
       SUM(visitor_count) AS visitor_count,
       SUM(page_view_count) AS page_view_count,
       SUM(payment_amount) AS payment_amount,
       SUM(refund_amount) AS refund_amount,
       SUM(net_payment_amount) AS net_payment_amount,
       SUM(payment_buyer_count) AS payment_buyer_count,
       SUM(payment_item_count) AS payment_item_count
FROM mart.omnichannel_daily_store_summary
WHERE stat_date BETWEEN ''::date AND 'zR'::date
GROUP BY source_platform, store_name
ORDER BY source_platform, store_name
r@   r   rB   rC   s      r   store_summaryz PostgresRepository.store_summary1   s@       %    4<        	r   c                :    |                      d| d| d          S )Na`  
SELECT stat_date::text AS stat_date,
       SUM(visitor_count) AS visitor_count,
       SUM(page_view_count) AS page_view_count,
       SUM(net_payment_amount) AS net_payment_amount,
       SUM(payment_amount) AS payment_amount,
       SUM(payment_buyer_count) AS payment_buyer_count
FROM mart.omnichannel_daily_store_summary
WHERE stat_date BETWEEN 'rE   z.'::date
GROUP BY stat_date
ORDER BY stat_date
r@   rF   s      r   trendzPostgresRepository.trendA   s@       %    4<        	r      limitintc           
     Z    |                      d| d| dt          |           d          S )Na  
SELECT store_name, product_id, left(product_name, 100) AS product_name,
       SUM(product_visitor_count) AS product_visitor_count,
       SUM(payment_amount) AS payment_amount,
       SUM(refund_amount) AS refund_amount,
       SUM(net_payment_amount) AS net_payment_amount,
       SUM(payment_item_count) AS payment_item_count,
       SUM(payment_buyer_count) AS payment_buyer_count
FROM fact.product_daily_core
WHERE stat_date BETWEEN 'rE   z'::date
  AND COALESCE(net_payment_amount, payment_amount, 0) > 0
GROUP BY store_name, product_id, product_name
ORDER BY SUM(COALESCE(net_payment_amount, payment_amount, 0)) DESC NULLS LAST
LIMIT 
r3   rL   r   rB   rC   rK   s       r   product_rankzPostgresRepository.product_rankO   sU       %    4<    5zz        	r   c           
     Z    |                      d| d| dt          |           d          S )Nu  
SELECT store_name,
       COALESCE(parent_source_name, '') AS parent_source_name,
       COALESCE(source_name, '未标明') AS source_name,
       SUM(visitor_count) AS visitor_count,
       SUM(page_view_count) AS page_view_count,
       SUM(payment_amount) AS payment_amount,
       SUM(payment_buyer_count) AS payment_buyer_count
FROM fact.traffic_daily_store
WHERE stat_date BETWEEN 'rE   zo'::date
GROUP BY store_name, parent_source_name, source_name
ORDER BY SUM(visitor_count) DESC NULLS LAST
LIMIT rN   rO   rP   s       r   traffic_rankzPostgresRepository.traffic_rank`   sU       %    4<    5zz        	r   )Nr   )r	   r
   r   r   )r   r   r   r   )r   r
   )r   r9   )rB   r   rC   r   r   r   )rJ   )rB   r   rC   r   rK   rL   r   r   )__name__
__module____qualname____doc__r   r3   r5   rA   rG   rI   rQ   rS    r   r   r   r      s         ; ; ; ; ;

A 
A 
A 
A< < < <D D D D           "      r   r   )
__future__r   r,   r   r$   r&   datetimer   typingr   r   rX   r   r   <module>r\      s    " " " " " "  				                 c c c c c c c c c cr   