
    >j              
        	   d Z ddlZddl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	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 ddlZej                            d	          Zej                            d
d          Zej                            dd          Zej                            dd          Z eej                            dd                    Z eej                            d e d                              Z!ej                            dd          Z"ej                            dd          Z#g dZ$ddddddddd d!ddd"d#d$dd%Z%d&hZ&d' Z'd(ej(        d)e d*e)fd+Z*d(ej(        fd,Z+d(ej(        fd-Z,d(ej(        d*e-fd.Z.d(ej(        fd/Z/ed0efd1            Z0 ed2e03          Z1e12                    ed4gd4gd4g5           d6efd7Z3d6ed8e4d9ej(        fd:Z5d9ej(        d;e)d*e)fd<Z6e1                    d=          d>             Z7e1                    d?          d@             Z8e1                    dA          dB             Z9e1:                    dC          d6efdD            Z;e1:                    dE          d6efdF            Z<e1                    dG          dyd6edIe dJe dKefdL            Z=e1                    dM          dzd6edOedJe fdP            Z>e1                    dQ          dzd6edOedJe fdR            Z?e1                    dS          d{d6edTe dJe fdU            Z@e1:                    dV          d{d6edJe fdW            ZAd*e)fdXZBe#fdYe)dZe d[e d*e)fd\ZCdYe)dZe d*e dz  fd]ZDdJe dIe dYe)d^e dz  d*e f
d_ZEdYe)dJe dIe d^e dz  d*e)f
d`ZFdae d*efdbZGdce d*e	fddZHd|dfeIe)         dge d*e)dz  fdhZJdYe)d*e)fdiZKdYe)d*e fdjZLdYe)d*e-fdkZMdfeIe)         d*eNeIe)         eIe)         f         fdlZOd9ej(        dJe d^e d*eIe)         fdmZPd}dYe)dne)dz  doe dz  d*e)fdpZQdfeIe)         d*eNe)e)f         fdqZRd9ej(        dJe d^e fdrZSd9ej(        dJe d^e d*e)fdsZTeUdtk    r9 eej                            dudv                    ZV ejW        e1dweVx           dS dS )~u  Health Bridge — private Health Connect ingestion API for Pipo.

Receives Pixel Health Connect batches over Tailscale, stores raw records
idempotently, and rebuilds local-day summaries per subject.

Design goals:
- raw_data preserves source records with provenance
- daily_summary is derived, idempotent, dashboard-friendly
- /debug/day explains every number by source/classification
    N)asynccontextmanager)datetimetimezone)ZoneInfo)FastAPIHTTPExceptionRequest)CORSMiddleware)FileResponsez~/health-bridge/health.dbHEALTH_BRIDGE_TOKENchangeme"HEALTH_BRIDGE_SIGNED_INGEST_SECRET "HEALTH_BRIDGE_SIGNED_INGEST_DEVICEzpixel9a-chicho,HEALTH_BRIDGE_SIGNED_INGEST_MAX_SKEW_SECONDS300*HEALTH_BRIDGE_SIGNED_INGEST_MAX_BODY_BYTESi    HEALTH_BRIDGE_TIMEZONEzAmerica/Argentina/Buenos_AiresHEALTH_BRIDGE_DEFAULT_SUBJECTchicho)"subjectdatesteps
distance_mfloorsactive_minutesexercise_minutesworkout_minutesauto_activity_minutescaloriesactive_kcal
total_kcalnutrition_kcalheart_rate_avgheart_rate_minheart_rate_maxsleep_minutessleep_duration_minutessleep_awake_minutessleep_light_minutessleep_deep_minutessleep_rem_minutessleep_efficiencyworkoutsworkout_countauto_activity_count	weight_kgbody_fat_percentageheight_m	protein_gcarbs_gfat_ghydration_ml
updated_atexplicit_workoutHevyhighclasslabeltrustauto_activityz
Google Fitmediumwearable_activityFitbitlegacy_unknownzHealth Connect legacylow)com.hevycom.google.android.apps.fitnesszcom.fitbit.FitbitMobilehealth_connect70c                      t          j        t                    } |                     d           |                     d           t          |            |                                  | S )NzPRAGMA journal_mode=WALa	  
        CREATE TABLE IF NOT EXISTS raw_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            received_at TEXT NOT NULL DEFAULT (datetime('now')),
            subject TEXT NOT NULL DEFAULT 'chicho',
            data_type TEXT NOT NULL,
            record_key TEXT,
            local_date TEXT,
            source TEXT,
            data_json TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_raw_data_type ON raw_data(data_type);
        CREATE INDEX IF NOT EXISTS idx_raw_data_received ON raw_data(received_at);

        CREATE TABLE IF NOT EXISTS ingest_nonces (
            device TEXT NOT NULL,
            nonce TEXT NOT NULL,
            timestamp INTEGER NOT NULL,
            received_at TEXT NOT NULL DEFAULT (datetime('now')),
            PRIMARY KEY (device, nonce)
        );
        CREATE INDEX IF NOT EXISTS idx_ingest_nonces_received ON ingest_nonces(received_at);

        CREATE TABLE IF NOT EXISTS daily_summary (
            subject TEXT NOT NULL DEFAULT 'chicho',
            date TEXT NOT NULL,
            steps INTEGER DEFAULT 0,
            distance_m REAL DEFAULT 0,
            floors REAL DEFAULT 0,
            active_minutes INTEGER DEFAULT 0,
            exercise_minutes INTEGER DEFAULT 0,
            workout_minutes INTEGER DEFAULT 0,
            auto_activity_minutes INTEGER DEFAULT 0,
            calories REAL DEFAULT 0,
            active_kcal REAL DEFAULT 0,
            total_kcal REAL DEFAULT 0,
            nutrition_kcal REAL DEFAULT 0,
            heart_rate_avg REAL,
            heart_rate_min REAL,
            heart_rate_max REAL,
            sleep_minutes INTEGER DEFAULT 0,
            sleep_duration_minutes INTEGER DEFAULT 0,
            sleep_awake_minutes INTEGER DEFAULT 0,
            sleep_light_minutes INTEGER DEFAULT 0,
            sleep_deep_minutes INTEGER DEFAULT 0,
            sleep_rem_minutes INTEGER DEFAULT 0,
            sleep_efficiency REAL,
            workouts INTEGER DEFAULT 0,
            workout_count INTEGER DEFAULT 0,
            auto_activity_count INTEGER DEFAULT 0,
            weight_kg REAL,
            body_fat_percentage REAL,
            height_m REAL,
            protein_g REAL DEFAULT 0,
            carbs_g REAL DEFAULT 0,
            fat_g REAL DEFAULT 0,
            hydration_ml REAL DEFAULT 0,
            updated_at TEXT NOT NULL DEFAULT (datetime('now')),
            PRIMARY KEY (subject, date)
        );
    )sqlite3connectDB_PATHexecuteexecutescript_migrate_schemacommitconns    $/home/ubuntu/health-bridge/server.pyinit_dbrU   8   sd    ?7##DLL*+++ < <	 <	 <	z DKKMMMK    rS   tablereturnc                 l    d |                      d| d                                          D             S )Nc                      i | ]}|d          |S     .0rs     rT   
<dictcomp>z"_table_columns.<locals>.<dictcomp>~   s    TTTAaD!TTTrV   zPRAGMA table_info()rN   fetchall)rS   rW   s     rT   _table_columnsre   }   s:    TTT\\*Gu*G*G*GHHQQSSTTTTrV   c                 8   t          | d          }ddddd                                D ]\  }}||vr|                     |           t          | d          }d|vst          |           s|                     d	           t	          |            t          | d
          }g }t
          D ]i}||v r|                    |           |dk    r|                    d           8|dk    r|                    d           T|                    d           j|                     dd                    t
                     dd                    |           d           |                     d           nt          | d          }i ddddddddddddd d!d"d#d$d%d&d'd(d)d*d+d,d-d.d/d0d1d2d3d4d5d6d7d8d9d:d;d<                                D ]\  }}||vr|                     |           |                     d=                                          }|r3d>|d?         pd@	                                v r|                     dA           t          |            |                     dB           |                     dC           |                     dD           d S )ENraw_datazFALTER TABLE raw_data ADD COLUMN subject TEXT NOT NULL DEFAULT 'chicho'z/ALTER TABLE raw_data ADD COLUMN record_key TEXTz/ALTER TABLE raw_data ADD COLUMN local_date TEXTz+ALTER TABLE raw_data ADD COLUMN source TEXT)r   
record_key
local_datesourcedaily_summaryr   z8ALTER TABLE daily_summary RENAME TO daily_summary_legacydaily_summary_legacyz'chicho'r8   zdatetime('now')0z&INSERT OR REPLACE INTO daily_summary (, z	) SELECT z FROM daily_summary_legacyzDROP TABLE daily_summary_legacyr   z>ALTER TABLE daily_summary ADD COLUMN distance_m REAL DEFAULT 0r   z:ALTER TABLE daily_summary ADD COLUMN floors REAL DEFAULT 0r   zGALTER TABLE daily_summary ADD COLUMN exercise_minutes INTEGER DEFAULT 0r   zFALTER TABLE daily_summary ADD COLUMN workout_minutes INTEGER DEFAULT 0r   zLALTER TABLE daily_summary ADD COLUMN auto_activity_minutes INTEGER DEFAULT 0r!   z?ALTER TABLE daily_summary ADD COLUMN active_kcal REAL DEFAULT 0r"   z>ALTER TABLE daily_summary ADD COLUMN total_kcal REAL DEFAULT 0r#   zBALTER TABLE daily_summary ADD COLUMN nutrition_kcal REAL DEFAULT 0r.   z?ALTER TABLE daily_summary ADD COLUMN workouts INTEGER DEFAULT 0r/   zDALTER TABLE daily_summary ADD COLUMN workout_count INTEGER DEFAULT 0r0   zJALTER TABLE daily_summary ADD COLUMN auto_activity_count INTEGER DEFAULT 0r2   z=ALTER TABLE daily_summary ADD COLUMN body_fat_percentage REALr3   z2ALTER TABLE daily_summary ADD COLUMN height_m REALr4   z=ALTER TABLE daily_summary ADD COLUMN protein_g REAL DEFAULT 0r5   z;ALTER TABLE daily_summary ADD COLUMN carbs_g REAL DEFAULT 0r6   z9ALTER TABLE daily_summary ADD COLUMN fat_g REAL DEFAULT 0r7   z@ALTER TABLE daily_summary ADD COLUMN hydration_ml REAL DEFAULT 0zMALTER TABLE daily_summary ADD COLUMN sleep_duration_minutes INTEGER DEFAULT 0zJALTER TABLE daily_summary ADD COLUMN sleep_awake_minutes INTEGER DEFAULT 0zJALTER TABLE daily_summary ADD COLUMN sleep_light_minutes INTEGER DEFAULT 0zIALTER TABLE daily_summary ADD COLUMN sleep_deep_minutes INTEGER DEFAULT 0zHALTER TABLE daily_summary ADD COLUMN sleep_rem_minutes INTEGER DEFAULT 0z:ALTER TABLE daily_summary ADD COLUMN sleep_efficiency REAL)r(   r)   r*   r+   r,   r-   zWSELECT sql FROM sqlite_master WHERE type = 'index' AND name = 'idx_raw_data_record_key'WHEREr   r   z"DROP INDEX idx_raw_data_record_keyzUCREATE INDEX IF NOT EXISTS idx_raw_data_subject_date ON raw_data(subject, local_date)zYCREATE INDEX IF NOT EXISTS idx_daily_summary_subject_date ON daily_summary(subject, date)zQCREATE UNIQUE INDEX IF NOT EXISTS idx_raw_data_record_key ON raw_data(record_key))re   itemsrN   _daily_summary_has_subject_pk_create_daily_summary_tableSUMMARY_COLUMNSappendjoinfetchoneupper_backfill_legacy_raw_metadata)rS   raw_colscolddlsummary_colslegacy_colsselect_exprs	index_sqls           rT   rP   rP      s   dJ//H[GG?	 
 egg S hLL!$88L$$,I$,O,O$OPPP#D)))$T+ABB" 	) 	)Ck!!##C((((	!!##J////$$##$56666##C((((JTYY5O5O J Jii--J J J	
 	
 	
 	67777%dO<<
Z
R
  i
 g	

 $%s
 \
 Z
 b
 Y
 c
 "#o
 "#b
 L
 X
 T
  P!
" ^#
$ 'v#o#o"m!k \/
 
 
0 %''1	" 	"HC2 ,&&S!!!a hjj   ;W1!3 : : < <<<9:::!$'''LLhiiiLLlmmmLLdeeeeerV   c                 0    |                      d           d S )Na  
        CREATE TABLE daily_summary (
            subject TEXT NOT NULL DEFAULT 'chicho',
            date TEXT NOT NULL,
            steps INTEGER DEFAULT 0,
            distance_m REAL DEFAULT 0,
            floors REAL DEFAULT 0,
            active_minutes INTEGER DEFAULT 0,
            exercise_minutes INTEGER DEFAULT 0,
            workout_minutes INTEGER DEFAULT 0,
            auto_activity_minutes INTEGER DEFAULT 0,
            calories REAL DEFAULT 0,
            active_kcal REAL DEFAULT 0,
            total_kcal REAL DEFAULT 0,
            nutrition_kcal REAL DEFAULT 0,
            heart_rate_avg REAL,
            heart_rate_min REAL,
            heart_rate_max REAL,
            sleep_minutes INTEGER DEFAULT 0,
            sleep_duration_minutes INTEGER DEFAULT 0,
            sleep_awake_minutes INTEGER DEFAULT 0,
            sleep_light_minutes INTEGER DEFAULT 0,
            sleep_deep_minutes INTEGER DEFAULT 0,
            sleep_rem_minutes INTEGER DEFAULT 0,
            sleep_efficiency REAL,
            workouts INTEGER DEFAULT 0,
            workout_count INTEGER DEFAULT 0,
            auto_activity_count INTEGER DEFAULT 0,
            weight_kg REAL,
            body_fat_percentage REAL,
            height_m REAL,
            protein_g REAL DEFAULT 0,
            carbs_g REAL DEFAULT 0,
            fat_g REAL DEFAULT 0,
            hydration_ml REAL DEFAULT 0,
            updated_at TEXT NOT NULL DEFAULT (datetime('now')),
            PRIMARY KEY (subject, date)
        );
    )rO   rR   s    rT   rr   rr      s)     & &	 &	 &	 &	 &	rV   c                     |                      d                                          }d t          d |D             d           D             }|ddgk    S )Nz PRAGMA table_info(daily_summary)c                     g | ]
}|d          S r[   r]   r^   s     rT   
<listcomp>z1_daily_summary_has_subject_pk.<locals>.<listcomp>   s    SSSqtSSSrV   c                 "    g | ]}|d          
|S )   r]   r^   s     rT   r   z1_daily_summary_has_subject_pk.<locals>.<listcomp>   s!    $=$=$=1!$=Q$=$=$=rV   c                     | d         S )Nr   r]   )r`   s    rT   <lambda>z/_daily_summary_has_subject_pk.<locals>.<lambda>   s
    QqT rV   keyr   r   )rN   rd   sorted)rS   colspk_colss      rT   rq   rq      sa    <<:;;DDFFDSSV$=$=$=$=$=>>RRRSSSGy&)))rV   c                    |                      d                                          }|D ]\  }}}	 t          j        |          }n# t          $ r Y (w xY wt          |t          t                    }|d         }|                      d||f                                          }|rd| d| }||d         d<   |                      d|d         |d	         p|||d
         |d         t          j	        |d         dd          |f           d S )NzSELECT id, data_type, data_json FROM raw_data WHERE record_key IS NULL OR local_date IS NULL OR source IS NULL OR subject IS NULLrh   z8SELECT id FROM raw_data WHERE record_key = ? AND id != ?zlegacy::recordz
            UPDATE raw_data
            SET subject = ?, data_type = ?, record_key = ?, local_date = ?, source = ?, data_json = ?
            WHERE id = ?
            r   	data_typeri   rj   ,r   T
separators	sort_keys)
rN   rd   jsonloads	Exception_normalize_recordDEFAULT_TIMEZONEDEFAULT_SUBJECTrv   dumps)	rS   rowsrow_idr   	data_jsonr   
normalizedrh   existss	            rT   rx   rx      sW   << 	L hjj 	 )- 
 
$	9	Z	**FF 	 	 	H	&v/?QQ
-
X[egmZnooxxzz 	<8688J88J1;Jx . 9%z+'>'K)Z<(*X*>
:h/JRVWWWY_	
 	
 	
 	

 
s   A
AAappc                   K   t                      | j        _        d W V  | j        j                                         d S N)rU   statedbclose)r   s    rT   lifespanr     s:      99CIL	EEEEILrV   zHealth Bridge)titler   *)allow_originsallow_methodsallow_headersrequestc                    K   | j                             dd                              dd          }|t          k    rt	          dd          |S )NAuthorizationr   zBearer   zInvalid tokenstatus_codedetail)headersgetreplace
AUTH_TOKENr   )r   tokens     rT   check_tokenr   "  sP      O44<<YKKE
ODDDDLrV   raw_bodyr   c                    t           st          dd          t          |          t          k    rt          dd          | j                            dd          }| j                            dd          }| j                            d	d          }| j                            d
d          }|t          k    s|r|r|st          dd          	 t          |          }n# t          $ r t          dd          w xY wt          t          t          j
                              |z
            t          k    rt          dd          t          j        |                                          }| d| d| d|                     d          }	t!          j        t                               d          |	t          j                                                  }
t!          j        |
|          st          dd          	 |                    d|||f           n$# t(          j        $ r t          dd          w xY w|                    d           d S )Ni  zSigned ingest is not configuredr   i  zPayload too largezX-HB-Devicer   z
X-HB-NoncezX-HB-TimestampzX-HB-Signaturer   z(Missing or invalid signed ingest headerszInvalid timestampz Timestamp outside allowed window
zutf-8zInvalid signaturezcINSERT INTO ingest_nonces (device, nonce, timestamp, received_at) VALUES (?, ?, ?, datetime('now'))i  zReplay noncezGDELETE FROM ingest_nonces WHERE received_at < datetime('now', '-1 day'))SIGNED_INGEST_SECRETr   lenSIGNED_INGEST_MAX_BODY_BYTESr   r   SIGNED_INGEST_DEVICEint
ValueErrorabstimeSIGNED_INGEST_MAX_SKEW_SECONDShashlibsha256	hexdigestencodehmacnewcompare_digestrN   rK   IntegrityError)r   r   r   devicenoncetimestamp_raw	signature	timestamp	body_hashsigned_payloadexpecteds              rT   _verify_signed_ingestr   )  sy    W4UVVVV
8}}3334GHHHH_  33FOb11EO''(8"==M##$4b99I%%%U%-%y%4^____I&&		 I I I4GHHHHI
3ty{{i'((+III4VWWWWx((2244ICC)CCuCC	CCJJ7SSNx,33G<<ngn]]ggiiHx33 I4GHHHHD


qUI&	
 	
 	
 	
 ! D D DNCCCCDJJXYYYYYs   C! !C=H !H<bodyc                    |                     dg           }|                     d          pt          }|                     d          pt          }|st          dd          t	                      }d}|D ]}t          |||          }|                     d|d         |d	         |d
         |d         |d         t          j        |d         dd          f           |d         r#|	                    |d         |d         f           |dz  }| 
                                 g }	t          |          D ].\  }
}t          | |
|           |	                    |
|d           /d||	dS )Nrecordsr   r     zNo records providedr   r   a  
            INSERT INTO raw_data (subject, data_type, record_key, local_date, source, data_json, received_at)
            VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
            ON CONFLICT(record_key) DO UPDATE SET
                subject = excluded.subject,
                data_type = excluded.data_type,
                local_date = excluded.local_date,
                source = excluded.source,
                data_json = excluded.data_json,
                received_at = datetime('now')
            r   rh   ri   rj   r   r   Tr   r\   r   r   ok)statusr   dates_rebuilt)r   r   r   r   setr   rN   r   r   addrQ   r   _rebuild_daily_summaryrt   )r   r   r   	client_tzr   affectedcountr   r   rebuiltsubjri   s               rT   _ingest_bodyr   M  s   hhy"%%G$$8(8Ihhy!!4_G K4IJJJJuuHE  &vy'BB




 9%z+'>
<@X<(*X*>
:h/JRVWWW	
 	
 	
$ l# 	LLL*Y/L1IJKKK
IIKKKG"8,, > >jr44444<<====uwGGGrV   z/healthc                  l   K   dt          j        t          j                                                  dS )Nr   )r   r   )r   nowr   utc	isoformatr]   rV   rT   healthr   v  s,      HL$>$>$H$H$J$JKKKrV   /c                     K   dddS )Nr   zhealth-bridge)r   servicer]   r]   rV   rT   rootr   {  s      777rV   z	/downloadc                     K   t           j                            d          } t           j                            |           st	          dd          t          | dd          S )Nz(~/health-bridge/static/health-bridge.apki  zAPK not found. Build it first.r   z'application/vnd.android.package-archivezhealth-bridge.apk)
media_typefilename)ospath
expanduserr   r   r   )apk_paths    rT   download_apkr     s^      w!!"LMMH7>>(## V4TUUUU-VatuuuurV   z/ingestc                    K   t          |            d {V  |                                  d {V }t          j        j        }t          ||          S r   )r   r   r   r   r   r   )r   r   r   s      rT   ingestr     s^      
g

D Y\BD!!!rV   z/ingest_signedc                   K   |                                   d {V }t          j        j        }t	          | ||           	 t          j        |          }n$# t
          j        $ r t          dd          w xY wt          ||          S )Nr   zInvalid JSONr   )
r   r   r   r   r   r   r   JSONDecodeErrorr   r   )r   r   r   r   s       rT   ingest_signedr     s      \\^^######H Y\B'8R000Dz(## D D DNCCCCDD!!!s    A !A6z/datad   r   r   limitc                 N  K   t          |            d {V  t          j        j        }|pt          }|r,|                    d|||f                                          }n*|                    d||f                                          }|t          |          d |D             dS )NzhSELECT data_json, received_at FROM raw_data WHERE subject = ? AND data_type = ? ORDER BY id DESC LIMIT ?zVSELECT data_json, received_at FROM raw_data WHERE subject = ? ORDER BY id DESC LIMIT ?c                 T    g | ]%}t          j        |d                    |d         d&S )r   r\   )r   received_atr   r   r^   s     rT   r   zget_data.<locals>.<listcomp>  s9    <}<}<}st
STUVSWHXHXijklim=n=n<}<}<}rV   )r   r   data)r   r   r   r   r   rN   rd   r   )r   r   r   r   r   r   s         rT   get_datar    s      
g

 Y\B(G 	zzvi'
 
 (** 	
 zzde
 
 (** 	 T<}<}x|<}<}<}~~~rV   z/summary   daysc                   K   t          |            d {V  t          j        j        }|pt          }|                    dd                    t                     d||f                                          }|d |D             dS )NSELECT rn   @ FROM daily_summary WHERE subject = ? ORDER BY date DESC LIMIT ?c                 ,    g | ]}t          |          S r]   _summary_dictr^   s     rT   r   zget_summary.<locals>.<listcomp>  s     (H(H(Haq)9)9(H(H(HrV   )r   r  )	r   r   r   r   r   rN   ru   rs   rd   )r   r  r   r   r   s        rT   get_summaryr    s      
g

 Y\B(G::n$))O,,nnn	$  hjj 	 (H(H4(H(H(HIIIrV   z	/overviewc           
        K   t          |            d {V  t          j        j        }|pt          }|                    dd                    t                     d||f                                          }d |D             }|                    d|f          	                                }|t          |d         |d         |d         |d	         d
|r|d         nd |ddddddS )Nr
  rn   r  c                 ,    g | ]}t          |          S r]   r  r^   s     rT   r   z get_overview.<locals>.<listcomp>  s     000aq!!000rV   z
        SELECT MAX(received_at), COUNT(*), COUNT(DISTINCT local_date), COUNT(DISTINCT source)
        FROM raw_data WHERE subject = ?
        r   r\         )last_sync_atraw_recordscovered_dayssourcesz2explicit workout sessions, primarily Hevy/strengthz;auto-detected activity, usually Google Fit walking/movementz'workout_minutes + auto_activity_minutesz.burned calories only; nutrition_kcal is intake)r   r   r   r    )r   r   	freshnesstodayr  legend)r   r   r   r   r   rN   ru   rs   rd   rv   r   )r   r  r   r   r   	summariesr  s          rT   get_overviewr    s%     
g

 Y\B(G::n$))O,,nnn	$  hjj 	 104000I

	 

  hjj  $%aL$Q<%aL |	
 
 "+41S%b IH	
 
  rV   z
/debug/dayr   c                    K   t          |            d {V  t          j        j        }|pt          }t          |||          S r   )r   r   r   r   r   
_debug_day)r   r   r   r   s       rT   	debug_dayr    sK      
g

 Y\B(Gb'4(((rV   z/rebuild-summaryc                 Z  K   t          |            d {V  t          j        j        }g }d}|r|dz  }|                    |           |                    d| d|                                          }g }|D ].\  }}t          |||           |                    ||d           /d|dS )NzWHERE local_date IS NOT NULLz AND subject = ?z2SELECT DISTINCT subject, local_date FROM raw_data z ORDER BY subject, local_dater   r   )r   r   )r   r   r   r   rt   rN   rd   r   )	r   r   r   paramswherepairsr   r   ri   s	            rT   rebuild_summaryr$    s      
g

 Y\BF*E ##gJJaUaaa  hjj 
 G! > >jr44444<<====W555rV   c                 F    t          t          t          |                     S r   )dictziprs   )rows    rT   r  r    s    OS))***rV   r   r   default_subjectc                    |                      dd          }|                      d          p|pt          }|                      d          p$|                      d          pt          | |          }t          |           }||d<   |r||d<   |                     d          p+|                     d          p|                     d          pd	}||d<   t	          |          d
         |d<   |                     d          p|                     d          }|r,t          |                              | d          s| d| }|st          ||||          }||d<   t          |          |d<   ||||||dS )Ntypeunknownr   ri   r   rj   r   package_namerH   r=   source_classrh   idr   fingerprint)r   r   rh   ri   rj   r   )	r   r   _local_date_for_recordr&  classify_recordstr
startswith_stable_record_keycanonical_fingerprint)	r   r   r)  r   r   ri   r   rj   rh   s	            rT   r   r     s   

69--Ijj##II/GL))lVZZ-?-?lCYZ`bkClClJfJ#Jy .#-
< ^^H%%t)>)>t*..Q_B`B`tdtF!Jx!0!<!<W!EJ~--E1E1EJ /#j//44]]]CC /..*..
 T'J
SS
)J| 5j A AJ}    rV   c                    t          |          }dD ]r}|                     |          }|rY	 t          |                              |                                                                          c S # t          $ r Y nw xY wsd S )N)r   startperiod_start)_zoner   _parse_instant
astimezoner   r   r   )r   r   zonefieldvalues        rT   r1  r1    s    D2  

5!! 	%e,,77==BBDDNNPPPPP   	
 4s   AA44
B Bri   c           
          |dv r|r
|  d| d| S t          j        t          j        t	          || ||          dd                                                                                    d d         }|  d| d| S )N   
aggregated
heart_ratedaily_aggregater   Tr   r   r      )r   r   r   r   	_identityr   r   )r   r   r   ri   digests        rT   r5  r5  '  s    CCC
C44I44
444^DJy)Z'X'Xdhu  A  A  A  H  H  J  J  K  K  U  U  W  W  X[  Y[  X[  \F,,	,,F,,,rV   c                    ||||                      d          |                      d          |                      d          |                      d          |                      d          |                      d          |                      d          p)|                      d          p|                      d	          d

}t          d |                                D                       r|nd|i| S )Nr   r8  endr9  
period_endrj   r?  minutesr   )
r   r+  ri   r   r8  rJ  r9  rK  rj   r?  c              3      K   | ]}|d uV  	d S r   r]   r_   vs     rT   	<genexpr>z_identity.<locals>.<genexpr>;  s&      DDQ1D=DDDDDDrV   r   )r   anyvalues)r   r   r   ri   identitys        rT   rG  rG  .  s     

6""G$$zz%  

>22jj..**X&&G$$T

9(=(=TGATAT H DD(//2C2CDDDDDh889V]JhagJhhrV   namec                 j    	 t          |           S # t          $ r t          t                    cY S w xY wr   )r   r   r   )rT  s    rT   r:  r:  >  sC    *~~ * * *()))))*s    22r?  c                 R    t          j        |                     dd                    S )NZz+00:00)r   fromisoformatr   )r?  s    rT   r;  r;  E  s!    !%--X">">???rV   r   r   r>  c                 d    fd| D             }|s| r| d         nd S t          |fd          S )Nc                 >    g | ]}|                               |S r]   r   )r_   r`   r>  s     rT   r   z_latest.<locals>.<listcomp>J  s(    555e5!555rV   c                 .    |                                S r   r[  )r`   r>  s    rT   r   z_latest.<locals>.<lambda>M  s    u rV   r   )max)r   r>  
candidatess    ` rT   _latestr`  I  sT    5555W555J 0%/wr{{4/z55556666rV   c                    |                      d          pd}|                      d          pd}t                               |d|dd                                          }|dk    r|                      d          pd                                }t	          |                      d	          pd          }|d
k    s|t
          v sd|v r|                    ddd           n|dk    r|                    ddd           |S )Nrj   rH   r+  r,  r<   exerciser   r   exercise_typerF   zmin-maxr9   r;   )r=   r?   rG   r@   rA   )r   SOURCE_RULEScopylowerr3  EXERCISE_TYPE_STRENGTHupdate)r   rj   truler   rc  s         rT   r2  r2  P  s   ZZ!!5%5F

6'iAFi&S\$]$]^^cceeDJG$$*1133FJJ77=2>>Z=4J#J#Ji[`N`N`KK"4vFFGGGG888KK/HEEFFFKrV   c                    |                      d          }|dv r|                      d          ||                      d          |                      d          |                      d          |                      d          $t          |                      d          pd          nd |                      d	          "t          |                      d	                    nd |                      d
          |                      d          |                      d          d
}ni|dv r-|                      d          ||                      d          d}n8t          | |                      d          ||                      d                    }t	          j        t          j        |dd                                                    	                                d d         S )Nr+  >   sleeprb  	hydration	nutritionr   ri   r8  rJ  rL  r   rc  r   energy_kcal	volume_ml)
r   r+  ri   r8  rJ  rL  rc  r   ro  rp  rA  )r   r+  ri   Tr   rE  rF  )
r   r   r3  rG  r   r   r   r   r   r   )r   ri  rS  s      rT   r6  r6  ^  s   

6A;;;zz),, **\22ZZ((::e$$:@**Y:O:O:[s6::i005A666aeAGOA\A\AhSO!<!<===nrZZ((!::m44K00
 
 
=	=	=%zz)44avzzZfOgOghhVVZZ	%:%:Avzz,?W?WXX>$*X*UUU\\^^__iikklomolopprV   c                 ,   |                      d          dvrdS |                      d          }|                      d          }|r|sdS 	 t          |          }t          |          }n# t          $ r Y dS w xY w||z
                                  dk    S )zWIgnore legacy aggregates that cover multi-day windows but were stored as one local day.r+     rB  rD  Fr9  rK  i@ )r   r;  r   total_seconds)r   r8  rJ  start_dtend_dts        rT   _is_stale_wide_aggregaterv  t  s    zz&!BBBuJJ~&&E
**\
"
"C  u!%(($$   uuX,,..::s   A* *
A87A8c           	         g }t          |           D ]\  }}t          |          }|                    d          pt          |          }|                    d          pd}ddddd                    |                    d	          d          }|dk    rdnd}|                    ||||||f           i }	g }
|D ]\  }}}}}}|||||f}|	                    |          }||d
d         |d
d         k    r9|1|
                    t          |d         |d         d                     ||	|<   v|
                    t          ||d                     d t          |	                                d           D             }||
fS )z[Deduplicate same semantic records, preferring named app sources over legacy health_connect.r0  rj   rH   r  r  r\   r   )r;   rA   rE   r,  r?   N   duplicate_lower_priorityreasonc                     g | ]
}|d          S )r  r]   rN  s     rT   r   z"dedupe_records.<locals>.<listcomp>  s    GGGQAaDGGGrV   c                     | d         S )Nr  r]   )xs    rT   r   z dedupe_records.<locals>.<lambda>  s
    1 rV   r   )	enumerater2  r   r6  rt   _debug_recordr   rR  )r   rankedidxr   classificationr0  rj   
trust_ranksource_rankwinnersignored	candidatecurrentkepts                 rT   dedupe_recordsr    s   F )) [ [V(00jj//P3H3P3PH%%9)91Q1EEII.J\J\]dJeJeghii
!%555aa1{JS&.YZZZZGGMS e eIZc6>c6>J	++k**?imgbqbk99"}WQZLfggghhh#,GK  NN=HbcccddddGG&!1!1~~FFFGGGD=rV   c                 n    |                      d||f                                          }d |D             S )NzdSELECT data_json FROM raw_data WHERE subject = ? AND local_date = ? ORDER BY received_at ASC, id ASCc                 B    g | ]}t          j        |d                    S )r   r  )r_   r(  s     rT   r   z$_records_for_day.<locals>.<listcomp>  s&    ///3DJs1v///rV   rc   )r   r   ri   r   s       rT   _records_for_dayr    sD    ::n	*  hjj 	 0/$////rV   r  r{  c           
         |pt          |           }|                     d          |                     d          |                    d          |                    d          |                    d          |                     d          |                     d          pt          |           |                     d          d	}d
D ]/}|                     |          |                     |          ||<   0|r||d<   |S )Nr+  rj   r>   r=   r?   rh   r0  ri   )r+  rj   source_labelr  r?   rh   r0  ri   )rL  r   r   r!   r"   ro  rp  r8  rJ  r   rc  r   r{  )r2  r   r6  )r   r  r{  outr   s        rT   r  r    s    #>v'>'>N

6""**X&&&**733(,,W55##G,,jj..zz-00Q4I&4Q4Qjj..	 	C ] ' '::c??&zz#CH HJrV   c                 ~   t          |           \  } }i ddddddddddddd	dd
ddddddddd dd dd dddddddddd dddd d d ddddd}g |i dg g g g f\  }}}}dt          dt          dt          ffd}| D ]}|                    dd          }	t	          |          }
|	dv rt          |          r,d                             t          ||
d                     gddt          fddt          fddt          fddt          fddt          ffD ]N\  }}} ||                    |d          pd          ||<    |||                    |d          pd||
           O|	dk    rW|dxx         t          |                    d d          pd          z  cc<    |d|                    d d          ||
           C|	dk    rW|dxx         t          |                    d!d          pd          z  cc<    |d|                    d!d          ||
           |	d
k    rW|dxx         t          |                    d"d          pd          z  cc<    |d|                    d"d          ||
           |	d#k    r|                    d$          }|rC|D ]?}|                    d%          r(|                    t          |d%                              @n{|                    d%          r)|                    t          |d%                              n=|                    d&          (|                    t          |d&                               |d#|                    d&          p|                    d%          ||
           |	d'k    r|dxx         t          |                    d!d          pd          z  cc<   |dxx         t          |                    d(|                    d!d                    pd          z  cc<   |dxx         t          |                    d)d          pd          z  cc<   |d*xx         t          |                    d+d          pd          z  cc<   |d,xx         t          |                    d-d          pd          z  cc<   |d.xx         t          |                    d/d          pd          z  cc<    |d|                    d!d          ||
           d0D ]E}|                    |
                    d1d                    |dk    r ||||         ||
           F|	d2k    rt          |                    d!d          pd          }|
d3         d4k    r0|dxx         |z  cc<   |d5xx         d6z  cc<    |d|||
           1|d	xx         |z  cc<   |d7xx         d6z  cc<    |d	|||
           a|	d8k    r8|                    |            |d9|                    d:          ||
           |	d;k    r8|                    |            |d<|                    d=          ||
           |	d>k    r8|                    |            |d?|                    d@          ||
           |	dAk    r_dBD ]Z\  }}||xx         t          |                    |d          pd          z  cc<    |||                    |d          ||
           [|	dCk    rU|dDxx         t          |                    dEd          pd          z  cc<    |dD|                    dEd          ||
           |d         |d	         z   |d<   |d5         |dF<   |d         p|d         |d
<   |rFt          |          t          |          z  |d<   t          |          |d<   t          |          |d<   |d         r|d         |d         z  |dG<   t          |          }t          |          }t          |          }|r*|                    d:          t          |d:                   nd |d9<   |r*|                    d=          t          |d=                   nd |d<<   |r*|                    d@          t          |d@                   nd |d?<   |fS )HNr   r   r   g        r   r   r   r   r   r    r!   r"   r#   r$   r%   r&   r'   r(   r)   )r*   r+   r,   r-   r.   r/   r0   r1   r2   r3   r4   r5   r6   r7   )usedr  	by_metricmetricr   r  c                     d                              | g                               t          ||                     d                             t          ||                     d S )Nr  r  )
setdefaultrt   r  )r  r?  r   r  
provenances       rT   
add_metricz)compute_daily_summary.<locals>.add_metric  s`    ;**6266==mFTb>c>cddd6!!-"G"GHHHHHrV   r+  r   rr  r  stale_wide_aggregaterz  r   rL  kcalrC  samplesbpmavg_bpmrl  duration_minutesawake_minutesr*   light_minutesr+   deep_minutesr,   rem_minutes)r(   r)   r*   r+   r,   sleep_rb  r=   r9   r/   r\   r0   weightr1   kgbody_fatr2   
percentageheightr3   metersrn  ))r4   r4   )r5   r5   )r6   r6   )r#   ro  rm  r7   rp  r.   r-   )r  r3  r&  r   r2  rv  rt   r  r   floatr   sumr   minr^  r`  )r   r  summary	hr_valuesweights	body_fatsheightsr  r`   ri  clsr  r>  casterr  srL  latest_weightlatest_body_fatlatest_heightr  s                       @rT   compute_daily_summaryr    s
   %g..GW

 #
'/
6F
A
0!
5La
 	C
 '
 .:3
 AQRU
 	$	
 !1$	
 9I$	

 	

 5a

 :OPQ
  !PQ a!\]$DS3
 
 
G rBBJ-/R^*Iw	7I3 It IT I I I I I I  EF EFEE&"a  111'** 9%,,]1cJ`-a-a-abbb'3',e)LxYachNiu5lTY7Z* A A%v #)&ua)=A">">
6155??#7aC@@@@A '\\GAEE'1$5$5$: ; ;;Jwgq 1 11c::::"""$%%%QUU9a-@-@-EA)F)FF%%%J'y!)<)<aEEEE*__L!!!U155+;+;+@q%A%AA!!!J|QUU61%5%5q#>>>>,eeI&&G 6  : :AuuU|| :!((qx999: u 6  qx1111y!!-  q|!4!4555J|QUU9%5%5%Euq#NNNN'\\O$$$AEE)Q,?,?,D1(E(EE$$$,---QUU;MquuU^`aObOb5c5c5hgh1i1ii---)***c!%%2K2K2Pq.Q.QQ***)***c!%%2K2K2Pq.Q.QQ***()))S~q1I1I1NQ-O-OO)))'(((CmQ0G0G0L1,M,MM(((Ji(;(;QDDD N @ @55"5566BfPhFhFhJvwv3???@ *__!%%	1--233G7|111)***g5***(((A-(((
,gq#>>>>/000G;000-...!3...
2GQDDDD(]]NN1J{AEE$KKC8888*__QJ,aeeL.A.A1cJJJJ(]]NN1Jz155??As;;;;+ "M < <5ua)=A#>#>>
6155??As;;;;< +N###uQUU;-B-B-Ga'H'HH###J~quu[!'<'<aEEE")*;"<wG^?_"_G!/2GJ!,/I7=3IGJ 3$'	NNS^^$C !$'	NN !$'	NN !'( c&-o&>IaAb&b"#G$$Mi((OG$$M9Fx=K\K\]aKbKbKn5t!4555txGKM\  &Yapatat  vB  bC  bC  bOU?<+H%I%I%I  UYG!"<ImN_N_`hNiNiNu%h 7888{GJJrV   c           	      R  	 t          t          | ||                    \  }}||t          j        t          j                                      d                               d          d|	t          }d	                    dgt          |          z            }d |D             }d	                    d	 |D                       }|                     d
d	                    |           d| d| dt          	fd|D                                  |                                  d S )N)tzinfoseconds)timespec)r   r   r8   rn   ?c                     g | ]}|d v|	S )>   r   r   r]   r_   cs     rT   r   z*_rebuild_daily_summary.<locals>.<listcomp>1  s#    FFF2E)E)E1)E)E)ErV   z,
            c                 ,    g | ]}|d k    rdn| d| S )r8   zupdated_at = datetime('now')z = excluded.r]   r  s     rT   r   z*_rebuild_daily_summary.<locals>.<listcomp>3  s8    mmm\]1+<+<	'	'QBWBWTUBWBWmmmrV   z$
        INSERT INTO daily_summary (z)
        VALUES (z?)
        ON CONFLICT(subject, date) DO UPDATE SET
            z
    c              3   B   K   | ]}                     |          V  d S r   r[  )r_   r  rR  s     rT   rP  z)_rebuild_daily_summary.<locals>.<genexpr>:  s-      ..vzz!}}......rV   )r  r  r   r   r   r   r   r   rs   ru   r   rN   tuplerQ   )
r   r   ri   r  _columnsplaceholdersupdate_cols
update_sqlrR  s
            @rT   r   r   '  sZ   &'7GZ'P'PQQJGQl8<00888EEOOYbOcc  	F G99cUS\\122LFFgFFFK"''mmalmmm J JJ $(IIg$6$6   	  
 
....g...	.	.0 0 0 IIKKKKKrV   c           
         t          | ||          }t          |          \  }}|                     dd                    t                     d||f                                          }|rt          |          nd }i }|D ]}	|	                    d          pd}
t          |	          }|	                    |
|d         dddd          }|d	xx         d
z  cc<   |dxx         t          |	                    dd          pd          z  cc<   |dxx         t          |	                    dd          pd          z  cc<   ||||||d         |d         |d         dS )Nr
  rn   z2 FROM daily_summary WHERE subject = ? AND date = ?rj   r,  r=   r   )r  r   rL  r   r   r\   rL  r   r  r  r  )r   r   computed_summarypersisted_summarysource_totalsused_recordsignored_recordsr  )r  r  rN   ru   rs   rv   r  r   r2  r  r   )r   r   ri   rawr  r  r(  	persistedr  r`   rj   r  slots                rT   r  r  >  s   
2w

3
3C/44GZ
**`$))O,,```	*  hjj  '*3c"""tIM 5 5x-Ia  ''3w<\]jkvw0x0xyyY1Y3quuY227a888WQUU7A..3!444#&&"6*%i0,	 	 	rV   __main__PORT3007z0.0.0.0)hostport)NNr   )r  Nr   )r   )NN)X__doc__r   r   r   r   rK   r   
contextlibr   r   r   zoneinfor   fastapir   r   r	   fastapi.middleware.corsr
   fastapi.responsesr   uvicornr   r   rM   environr   r   r   r   r   r   r3  r   r   r   rs   rd  rg  rU   
Connectionr&  re   rP   rr   boolrq   rx   r   r   add_middlewarer   bytesr   r   r   r   r   postr   r   r  r  r  r  r$  r  r   r1  r5  rG  r:  r;  listr`  r2  r6  rv  r  r  r  r  r  r   r  __name__r  runr]   rV   rT   <module>r     s	  	 	    				   * * * * * * ' ' ' ' ' ' ' '       3 3 3 3 3 3 3 3 3 3 2 2 2 2 2 2 * * * * * * 
'

8
9
9Z^^1:>>
z~~&JBOO z~~&JL\]] !$RZ^^4bdi%j%j!k!k "s2:>>2^`c`cds`t`t#u#uvv :>>":<\]] *..!@(KK
 
 
 -vOO1@<bj'k'k)<xZ`aa 0;R]bcc	   B B BJU+ UC UD U U U UFf', Ff Ff Ff FfR'	g&8 '	 '	 '	 '	T*(: *t * * * *
(: 
 
 
 
:      gOh777   >#se\_[`  a a aw    !Z7 !Ze !ZAS !Z !Z !Z !ZH&HW' &Ht &H &H &H &H &HR L L L 8 8 8 v v v )"' " " " " 
" " " " "  G  S X[    " J Jw Jc J J J J J ! ! !s ! ! ! ! !H ) )W )C )# ) ) ) ) 
6 67 6S 6 6 6 6&+$ + + + + L[  d s S _c    8	4 	C 	C$J 	 	 	 	- - -T -sUYz -^a - - - -id iS iS icDj iUY i i i i * * * * * *@# @( @ @ @ @7 7T$Z 7 7 7 7 7 7D T    q$ q3 q q q q,;T ;d ; ; ; ; DJ 5dT$Z1G+H    40+ 0c 0s 0tTXz 0 0 0 0 $ t CRVJ bf    (j4: j%d
2C j j j jZw1 C S    .7%       8 z3rz~~ff--..DGK)$////// rV   