o
    i!\                     @   s  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ZddlZddl	m	Z	m
Z
mZ ddlmZ edZ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&i d'd(d)d*d+d,d-d.d/d0d1d2d3d4d5d6d7d8d9d:d;d<d=d>d?d@dAdBdCdDdEdFdGdHdIdJdKdLdMdNdOdPdQdRdSdTdUdVdWZdXdYdZd[d\d]d^d_ZG d`da daZdbedcefdddeZdS )fa  
Health Connect ZIP ingestion pipeline.

Parses the Health Connect export SQLite database from a zip file
and updates dashboard-data.json with:
  - Weight (latest morning reading)
  - Body fat (latest reading)
  - Blood pressure (latest reading)
  - Sleep (last night: duration, stages, HRV)
  - Exercise (recent sessions: type, duration, calories)
  - Steps (daily total)
  - Resting heart rate

Bill drops a Health Connect zip into Google Drive each morning between 7-8 AM.
The scheduler watches for new files and auto-ingests.
    N)datetimedate	timedelta)Pathzhealth-connectOther   	Badminton   Baseball   Biking   Boxing
   Cricket   Dancing   Fencing   zFootball (American)   zFootball (Australian)   zFrisbee Disc   Golf   zGuided Breathing   
Gymnastics   Handball   Walking   HIIT   Hiking   z
Ice Hockey   zIce Skating   zMartial Arts   Paddling    Paragliding!   Pilates"   Racquetball#   zRock Climbing$   zRoller Hockey%   Rowing&   Rugby'   Running(   Sailing)   zScuba Diving*   Skating+   SkiingSnowboardingzStrength Training
StretchingSurfingzSwimming (Open Water)zSwimming (Pool)zTable TennisTennis
Volleyball
Elliptical
Wheelchairz
Water PoloYogazStair Climbing),   -   .   /   0   1   2   3   4   5   8   9   :   ;   unknownawakesleeping
out_of_bedlightdeeprem)r      r      r	   r      c                   @   s   e Zd ZdZdefddZdd Zdd Zd	d
 Zdd Z	dd Z
d%ddZdd Zd%ddZdd Zdd Zd&ddZd'ddZd'd d!Zd"d# Zd$S )(HealthConnectParserz=Parse a Health Connect export zip and extract health metrics.zip_pathc                 C   s   || _ d | _d | _d S N)r`   db_pathtemp_dir)selfr`    re   health_connect.py__init__?   s   
zHealthConnectParser.__init__c                 C   s8  t  | _zt| jd}|| j W d   n1 sw   Y  W nU tjyy } zHdt|v rnt	
d|  tjj}z)dd tj_t| jd}|| j W d   n1 s^w   Y  W |tj_n|tj_w  W Y d}~nd}~ww t| jD ]}|drtj| j|| _ nq| jstd| S )	z7Extract the SQLite DB from the zip to a temp directory.rNz
Bad CRC-32u4   CRC mismatch in zip — retrying without CRC check: c                 S   s   d S ra   re   )rd   datare   re   rf   <lambda>Q   s    z/HealthConnectParser.__enter__.<locals>.<lambda>z.dbz'No .db file found in Health Connect zip)tempfilemkdtemprc   zipfileZipFiler`   
extractall
BadZipFilestrloggerwarning
ZipExtFile_update_crcoslistdirendswithpathjoinrb   FileNotFoundError)rd   zfe_orig_update_crcfre   re   rf   	__enter__D   s:   

zHealthConnectParser.__enter__c                 G   s   | j rtj| j dd dS dS )zCleanup temp directory.T)ignore_errorsN)rc   shutilrmtree)rd   argsre   re   rf   __exit__a   s   zHealthConnectParser.__exit__c                 C   sr   t | drt| jS t| j}z|d d| _|W S  tjy8   |  t	d | 
 }d| _| Y S w )N_db_verified)SELECT 1 FROM weight_record_table LIMIT 1Tu,   DB appears malformed — attempting recovery)hasattrsqlite3connectrb   executer   DatabaseErrorcloserr   rs   _recover_db)rd   conn	recoveredre   re   rf   _connectf   s   


zHealthConnectParser._connectc           	   
   C   sN  | j d }| j }ddl}z[|jd| j dgdddd}|jrft|jd	krf|jd|g|jdddd
}tj|rftj|dkrf|| _ t	
| j }|d |  tdtj|dd t	
| j W S W n* |jtt	jfy } ztd|  tj|rt| W Y d}~nd}~ww zZ|jd|dgdddd}|jrt|jd	kr|jd|g|jdddd
}tj|rtj|dkr|| _ t	
| j }|d |  tdtj|dd t	
| j W S W n, |jtt	jfy } ztd|  tj|rt| W Y d}~nd}~ww td || _ t	
| j S )z
        Recover a malformed SQLite DB.
        Strategy 1: sqlite3 CLI .recover (most robust for page-level corruption)
        Strategy 2: sqlite3 CLI .dump + rebuild
        Strategy 3: Python-level PRAGMA integrity_check + connect anyway
        z
.recoveredr   Nr   z.recoverT<   )capture_outputtexttimeout  )inputr   r   r   i r   zDB recovered via .recover (,z bytes)zStrategy 1 (.recover) failed: z.dumpzDB recovered via .dump (zStrategy 2 (.dump) failed: uG   DB recovery failed — proceeding with original (some queries may fail))rb   
subprocessrunstdoutlenrv   ry   existsgetsizer   r   r   r   rr   infoTimeoutExpiredr{   r   debugremovers   )	rd   recovered_pathoriginal_pathr   recoverrebuild	test_connr}   dumpre   re   rf   r   w   sl   








zHealthConnectParser._recover_dbc                 C   sl   |   }| }|d | }|  |r4t|d d }t|d d d}| |	d|dS dS )	z9Get the most recent weight reading. Returns dict or None.zi
            SELECT time, weight FROM weight_record_table
            ORDER BY time DESC LIMIT 1
        r   r   r\   xY|@%Y-%m-%d)	timestampr   lbsN
r   cursorr   fetchoner   r   fromtimestampround	isoformatstrftime)rd   r   crowdtr   re   re   rf   get_latest_weight   s   
z%HealthConnectParser.get_latest_weight   c                 C   s   |   }| }tt t|d  d }|d|f | }|	  i }|D ]\}}t
|d }	|	d}
|
|vrGt|d d||
< q*|S )zJGet daily weight readings (first reading of each day) for the last N days.daysr   zo
            SELECT time, weight FROM weight_record_table
            WHERE time > ? ORDER BY time ASC
        r   r   r\   r   r   intr   nowr   r   r   fetchallr   r   r   r   )rd   r   r   r   cutoffrowsdailytsgramsr   day_keyre   re   rf   get_weight_series   s    
z%HealthConnectParser.get_weight_seriesc                 C   s\   |   }| }|d | }|  |r,t|d d }| t|d ddS dS )z(Get the most recent body fat percentage.zo
            SELECT time, percentage FROM body_fat_record_table
            ORDER BY time DESC LIMIT 1
        r   r   r\   )r   
percentageN)	r   r   r   r   r   r   r   r   r   )rd   r   r   r   r   re   re   rf   get_latest_body_fat   s   
z'HealthConnectParser.get_latest_body_fatc                 C   s   |   }| }tt t|d  d }|d|f | }|	  i }|D ]\}}t
|d }	|	d}
|
|vrEt|d||
< q*|S )z0Get daily body fat readings for the last N days.r   r   zu
            SELECT time, percentage FROM body_fat_record_table
            WHERE time > ? ORDER BY time ASC
        r   r\   r   )rd   r   r   r   r   r   r   r   pctr   r   re   re   rf   get_body_fat_series   s    
z'HealthConnectParser.get_body_fat_seriesc                 C   s   |   }| }|d | }|  |r\t|d d }t|d }t|d }|dk r7|dk r7d}n|d	k rB|dk rBd
}n|dk sJ|dk rMd}nd}| |	d|||dS dS )zGet the most recent BP reading.z
            SELECT time, systolic, diastolic
            FROM blood_pressure_record_table
            ORDER BY time DESC LIMIT 1
        r   r   r\   r   x   P   normal   elevated   Z   zhigh-stage1zhigh-stage2r   )r   r   systolic	diastolicstatusNr   )rd   r   r   r   r   sys_valdia_valr   re   re   rf   get_latest_blood_pressure  s.   
z-HealthConnectParser.get_latest_blood_pressurec                 C   sf  |   }| }|d | }|s|  dS |\}}}}}}	t|p&|d }
t|	p/|d }|| d }|d|f | }ddddd}|D ]\}}}|| d }t	|d	}||v rj||  |7  < qM|d
||f | d }|  |

d|

d|
dt|d|pdt|d t|d t|d t|d d|rt|ddS ddS )z4Get last night's sleep session with stage breakdown.z
            SELECT row_id, start_time, end_time, title,
                   local_date_time_start_time, local_date_time_end_time
            FROM sleep_session_record_table
            ORDER BY start_time DESC LIMIT 1
        Nr   6 z
            SELECT stage_start_time, stage_end_time, stage_type
            FROM sleep_stages_table
            WHERE parent_key = ?
        r   )rV   rY   rZ   r[   `  rU   z
            SELECT AVG(heart_rate_variability_millis)
            FROM heart_rate_variability_rmssd_record_table
            WHERE time BETWEEN ? AND ?
        r   %H:%Mr\   UnknownrZ   r[   rY   rV   )deep_minrem_min	light_min	awake_min)r   bedtime	wake_timetotal_hourssourcestagesavg_hrv)r   r   r   r   r   r   r   r   SLEEP_STAGESgetr   r   )rd   r   r   sessionrow_idstart_msend_mstitlelocal_start_mslocal_end_msstartendr   r   stage_minutess_starts_ends_typemins
stage_namer   re   re   rf   get_last_sleep)  sR   




z"HealthConnectParser.get_last_sleepr   c                 C   s   |   }| }tt t|d  d }|d|f | }|	  i }|D ]'\}}t
|d }	|	d}
|| d }|
|vsJ|||
 krQt|d||
< q*|S )z)Get sleep duration for the last N nights.r   r   z
            SELECT start_time, end_time
            FROM sleep_session_record_table
            WHERE start_time > ?
            ORDER BY start_time ASC
        r   r   r\   r   )rd   r   r   r   r   r   nightlyr   r   r   nighthoursre   re   rf   get_sleep_seriesi  s"   
z$HealthConnectParser.get_sleep_series   c              	   C   s   |   }| }tt t|d  d }|d|f | }|	  g }|D ]?\}}}	}
}t
|d }t|| d }t|	d|	 }|
rM|
n|}|dk rX|	dkrXq*||d|d	|||d
 q*|S )z+Get exercise sessions from the last N days.r   r   z
            SELECT start_time, end_time, exercise_type, title, notes
            FROM exercise_session_record_table
            WHERE start_time > ?
            ORDER BY start_time DESC
        r   zType r   r   r   r   )r   timetypenameduration_min)r   r   r   r   r   r   r   r   r   r   r   r   EXERCISE_TYPESr   appendr   )rd   r   r   r   r   r   sessionsr   r   ex_typer   notesr   r   
type_labelr   re   re   rf   get_recent_exercises  s0   
z(HealthConnectParser.get_recent_exercisesc           
      C   s   |   }| }tt t|d  d }|d|f | }|	  i }|D ]\}}t
|d d}	||	d| ||	< q*|S )z*Get daily step totals for the last N days.r   r   zg
            SELECT start_time, count FROM steps_record_table
            WHERE start_time > ?
        r   r   )r   r   r   r   r   r   r   r   r   r   r   r   r   )
rd   r   r   r   r   r   r   r   countr   re   re   rf   get_daily_steps  s   z#HealthConnectParser.get_daily_stepsc                    s.  t   tj jd}d fddfd fddfd fddfd	 fd
dfd fddfd fddfd fddfd fddfd fddfg	}|D ],\}}z| }|r_|||< W qQ ty} } zt	d| d|  W Y d}~qQd}~ww dd |D }t
dt| dd|  |S )u   
        Extract all health metrics into a single dict.
        Each metric is extracted independently — if one table is corrupt,
        the rest still get pulled.
        exported_atsource_fileweightc                            S ra   )r   re   rd   re   rf   rj         z1HealthConnectParser.extract_all.<locals>.<lambda>weight_seriesc                      
     dS Nr   )r   re   r  re   rf   rj        
 body_fatc                      r  ra   )r   re   r  re   rf   rj     r  body_fat_seriesc                      r  r  )r   re   r  re   rf   rj     r  blood_pressurec                      r  ra   )r   re   r  re   rf   rj     r  sleepc                      r  ra   )r   re   r  re   rf   rj     r  sleep_seriesc                      r  )Nr   )r   re   r  re   rf   rj     r  	exercisesc                      r  Nr   )r  re   r  re   rf   rj     r  stepsc                      r  r  )r  re   r  re   rf   rj     r  zFailed to extract z: Nc                 S   s   g | ]}|d vr|qS )r	  re   ).0kre   re   rf   
<listcomp>  s    z3HealthConnectParser.extract_all.<locals>.<listcomp>z
Extracted z
 metrics: z, )r   r   r   rv   ry   basenamer`   	Exceptionrr   rs   r   r   rz   )rd   result
extractorskeyfnvalr}   	extractedre   r  rf   extract_all  s4   
" zHealthConnectParser.extract_allN)r   )r   )r   )__name__
__module____qualname____doc__rq   rg   r   r   r   r   r   r   r   r   r   r   r   r  r  r'  re   re   re   rf   r_   <   s"    B

#
@

'r_   r`   returnc                    s   t d|   t| }| W d   n1 sw   Y  g   fdd}|| t|jjd }|jdd |dt	
   d	 }t|d
}tj|dtd W d   n1 saw   Y  dddd  D  }t | |S )zb
    Ingest a Health Connect zip file and update the dashboard.
    Returns a summary string.
    z!Ingesting Health Connect export: Nc              	      s  dv rd }| d d }|d }t || d| d d< || d d< t|d d| d d< t  | d	< d
v rjtd
  }t	|dkrj|dd  | d d< |dd  }t t
|t	| d| d d< | d dd}| d dd}tddd}tt | jd d}	|| }
t |
|	 d}|| d d< || }|dkrt || nd| d d<  d| d| d d dd dv rd }| d d }|d  }t || d| d d< || d d< d!v rtd!  }|r|dd  | d d<  d"| d# d$v r?d$ }|d% | d& d%< |d' | d& d'< |d( | d& d(< |d	 | d& d	<  d)|d%  d*|d'   d+v rrd+ | d+< d,v rWd, | d+ d-<  d.d+ d/  d0d+ d1  d2d+ d3  d d4v rd4 d d5 t  d6| d7< d8d9 d4 D }|r d:t	d4  d;|d d<  d n d:t	d4  d= d>v rd> t  d?| d>< d> t  d}|r d@|dAdB d S d S d S )CNr  currentr   r\   deltaVsLastr   z%B %-dlastWeighInr   r  r   i	series30diavg7dbaselineg     n@goal   i  r   pacer   i  etaWeekszWeight: z lb (z+.1f)r  bodyFatr   r  z
Body fat: %r  r   bloodPressurer   r   zBP: /r  r  	series14dzSleep: r   zh (r   -r   r  r   )recentupdatedexercisec                 S   s    g | ]}|d  |d kr|qS )r   r   re   )r  r}   re   re   rf   r  ;  s     z:ingest_health_connect.<locals>.updater.<locals>.<listcomp>z
Exercise: z sessions (latest: r   z	 sessionsr  )r   r?  zSteps: r   z today)r   r   fromisoformatr   r   todayr   listvaluesr   sumr   maxr   r   r  )	dashboardwoldnew_valseries_valueslast7r2  r3  baseline_dateweekslostr5  	remainingbfbpnamedtoday_stepschangesri   re   rf   updater  s    "



6


*


z&ingest_health_connect.<locals>.updaterzhealth-snapshotsT)exist_okzhealth-z.jsonrH  r   )indentdefaultz Health Connect import complete:

c                 s   s    | ]}d | V  qdS )u     • Nre   )r  r   re   re   rf   	<genexpr>T  s    z(ingest_health_connect.<locals>.<genexpr>)rr   r   r_   r'  _read_and_writer   	file_pathparentmkdirr   rB  r   openjsonr   rq   rz   )r`   dashboard_managerparserrW  snapshot_dirsnapshot_pathr   summaryre   rU  rf   ingest_health_connect  s    


X
rh  )r+  rv   rerb  r   rm   r   rk   loggingr   r   r   pathlibr   	getLoggerrr   r   r   r_   rq   rh  re   re   re   rf   <module>   s    
			


   -