
    mj(                    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                  |    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#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    r7t          j        d          dk    rdddd	d
| j        dd|g	}nNd	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LIANGZHU_PSQL_USE_SUDO1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   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y122c99tZt|UTXZabtT\5$H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)r5   getr   rowss     r   r7   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)r8   ).0rs     r   
<listcomp>z6PostgresRepository.available_dates.<locals>.<listcomp>2   s-    CCC1k0B0BC+CCCr   r5   r9   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
rB   r   rD   rE   s      r   store_summaryz PostgresRepository.store_summary4   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 'rG   z.'::date
GROUP BY stat_date
ORDER BY stat_date
rB   rH   s      r   trendzPostgresRepository.trendD   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 'rG   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 
r5   rN   r   rD   rE   rM   s       r   product_rankzPostgresRepository.product_rankR   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 'rG   zo'::date
GROUP BY store_name, parent_source_name, source_name
ORDER BY SUM(visitor_count) DESC NULLS LAST
LIMIT rP   rQ   rR   s       r   traffic_rankzPostgresRepository.traffic_rankc   sU       %    4<    5zz        	r   c                :    |                      d| d| d          S )uT   Promotion effect by store, using scene report as the non-duplicated spend/GMV口径.a  
SELECT p.store_code,
       p.store_name,
       SUM(COALESCE(p.spend_amount, 0)) AS promotion_spend_amount,
       SUM(COALESCE(p.promoted_transaction_amount, 0)) AS promoted_transaction_amount,
       SUM(COALESCE(p.transaction_count, 0)) AS promoted_transaction_count,
       SUM(COALESCE(p.click_count, 0)) AS promotion_click_count,
       SUM(COALESCE(p.impression_count, 0)) AS promotion_impression_count,
       CASE WHEN SUM(COALESCE(p.impression_count, 0)) = 0 THEN NULL ELSE SUM(COALESCE(p.click_count, 0)) / SUM(COALESCE(p.impression_count, 0)) END AS promotion_ctr,
       CASE WHEN SUM(COALESCE(p.click_count, 0)) = 0 THEN NULL ELSE SUM(COALESCE(p.spend_amount, 0)) / SUM(COALESCE(p.click_count, 0)) END AS promotion_cpc,
       CASE WHEN SUM(COALESCE(p.spend_amount, 0)) = 0 THEN NULL ELSE SUM(COALESCE(p.promoted_transaction_amount, 0)) / SUM(COALESCE(p.spend_amount, 0)) END AS promotion_roi,
       SUM(COALESCE(p.net_payment_amount, 0)) AS net_payment_amount_for_rate,
       CASE WHEN SUM(COALESCE(p.net_payment_amount, 0)) = 0 THEN NULL ELSE SUM(COALESCE(p.spend_amount, 0)) / SUM(COALESCE(p.net_payment_amount, 0)) END AS promotion_spend_rate,
       CASE WHEN SUM(COALESCE(p.net_payment_amount, 0)) = 0 THEN NULL ELSE SUM(COALESCE(p.promoted_transaction_amount, 0)) / SUM(COALESCE(p.net_payment_amount, 0)) END AS promotion_transaction_share
FROM mart.promotion_daily_effect_summary p
WHERE p.stat_date BETWEEN 'rG   zf'::date
GROUP BY p.store_code, p.store_name
ORDER BY SUM(COALESCE(p.spend_amount, 0)) DESC NULLS LAST
rB   rH   s      r   promotion_store_summaryz*PostgresRepository.promotion_store_summarys   s@       '    6>        	r   c                :    |                      d| d| d          S )Na?  
SELECT p.stat_date::text AS stat_date,
       SUM(COALESCE(p.spend_amount, 0)) AS promotion_spend_amount,
       SUM(COALESCE(p.promoted_transaction_amount, 0)) AS promoted_transaction_amount,
       SUM(COALESCE(p.transaction_count, 0)) AS promoted_transaction_count,
       SUM(COALESCE(p.click_count, 0)) AS promotion_click_count,
       SUM(COALESCE(p.impression_count, 0)) AS promotion_impression_count,
       CASE WHEN SUM(COALESCE(p.spend_amount, 0)) = 0 THEN NULL ELSE SUM(COALESCE(p.promoted_transaction_amount, 0)) / SUM(COALESCE(p.spend_amount, 0)) END AS promotion_roi,
       CASE WHEN SUM(COALESCE(p.net_payment_amount, 0)) = 0 THEN NULL ELSE SUM(COALESCE(p.spend_amount, 0)) / SUM(COALESCE(p.net_payment_amount, 0)) END AS promotion_spend_rate
FROM mart.promotion_daily_effect_summary p
WHERE p.stat_date BETWEEN 'rG   z2'::date
GROUP BY p.stat_date
ORDER BY p.stat_date
rB   rH   s      r   promotion_trendz"PostgresRepository.promotion_trend   s@       '    6>        	r   c           
     Z    |                      d| d| dt          |           d          S )Na  
SELECT store_name,
       scene_name,
       SUM(COALESCE(spend_amount, 0)) AS promotion_spend_amount,
       SUM(COALESCE(promoted_transaction_amount, 0)) AS promoted_transaction_amount,
       SUM(COALESCE(transaction_count, 0)) AS promoted_transaction_count,
       SUM(COALESCE(click_count, 0)) AS promotion_click_count,
       SUM(COALESCE(impression_count, 0)) AS promotion_impression_count,
       CASE WHEN SUM(COALESCE(impression_count, 0)) = 0 THEN NULL ELSE SUM(COALESCE(click_count, 0)) / SUM(COALESCE(impression_count, 0)) END AS promotion_ctr,
       CASE WHEN SUM(COALESCE(click_count, 0)) = 0 THEN NULL ELSE SUM(COALESCE(spend_amount, 0)) / SUM(COALESCE(click_count, 0)) END AS promotion_cpc,
       CASE WHEN SUM(COALESCE(spend_amount, 0)) = 0 THEN NULL ELSE SUM(COALESCE(promoted_transaction_amount, 0)) / SUM(COALESCE(spend_amount, 0)) END AS promotion_roi
FROM mart.promotion_daily_effect_summary
WHERE stat_date BETWEEN 'rG   zf'::date
GROUP BY store_name, scene_name
ORDER BY SUM(COALESCE(spend_amount, 0)) DESC NULLS LAST
LIMIT rP   rQ   rR   s       r   promotion_scene_rankz'PostgresRepository.promotion_scene_rank   sU       %    4<    5zz        	r   c           
     Z    |                      d| d| dt          |           d          S )Na  
SELECT store_name,
       scene_name,
       plan_name,
       SUM(COALESCE(spend_amount, 0)) AS promotion_spend_amount,
       SUM(COALESCE(promoted_transaction_amount, 0)) AS promoted_transaction_amount,
       SUM(COALESCE(transaction_count, 0)) AS promoted_transaction_count,
       SUM(COALESCE(click_count, 0)) AS promotion_click_count,
       SUM(COALESCE(impression_count, 0)) AS promotion_impression_count,
       CASE WHEN SUM(COALESCE(impression_count, 0)) = 0 THEN NULL ELSE SUM(COALESCE(click_count, 0)) / SUM(COALESCE(impression_count, 0)) END AS promotion_ctr,
       CASE WHEN SUM(COALESCE(click_count, 0)) = 0 THEN NULL ELSE SUM(COALESCE(spend_amount, 0)) / SUM(COALESCE(click_count, 0)) END AS promotion_cpc,
       CASE WHEN SUM(COALESCE(spend_amount, 0)) = 0 THEN NULL ELSE SUM(COALESCE(promoted_transaction_amount, 0)) / SUM(COALESCE(spend_amount, 0)) END AS promotion_roi
FROM mart.promotion_daily_plan_summary
WHERE stat_date BETWEEN 'rG   zq'::date
GROUP BY store_name, scene_name, plan_name
ORDER BY SUM(COALESCE(spend_amount, 0)) DESC NULLS LAST
LIMIT rP   rQ   rR   s       r   promotion_plan_rankz&PostgresRepository.promotion_plan_rank   sU       %    4<     5zz!        	r   )Nr   )r	   r
   r   r   )r   r   r   r   )r   r
   )r   r;   )rD   r   rE   r   r   r   )rL   )rD   r   rE   r   rM   rN   r   r   )__name__
__module____qualname____doc__r   r5   r7   rC   rI   rK   rS   rU   rW   rY   r[   r]    r   r   r   r      s/        ; ; ; ; ;
A A A A< < < <D D D D           "        ,        &      r   r   )
__future__r   r.   r   r&   r(   datetimer   typingr   r   rb   r   r   <module>rf      s    " " " " " "  				                 s s s s s s s s s sr   