
    RSj)0                    V   d Z ddlm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
 ddlmZ ddlmZmZ  ej                    dz  dz  d	z  Zed
z  Zedz  dz  Zedz  dz  Zd6dZd7dZd8dZd9dZd:d Zd;d$Zd<d'Zd=d+Zd>d,Zd?d/Zd@dAd3ZdBd4Z e!d5k    r e              dS dS )Ca  Import Apple Health Auto Export JSON/CSV files into health_data.db.

Recommended app setup: Google Drive + JSON.
This importer is intentionally schema-flexible because Health Auto Export can
emit slightly different field names depending on data type/app version.

It stores raw records plus normalized fields:
- record_type / metric
- start_date / end_date
- value / unit
- source/device
- raw_json for traceability

It is idempotent via a SHA256 fingerprint over the normalized record + raw JSON.
    )annotationsN)datetime)Path)AnyIterablez.hermesassets
Gesundheitzhealth_data.dbinboxapple_health_exports	processedreturnsqlite3.Connectionc                 Z    t          j        t                    } t           j        | _        | S )N)sqlite3connectDBRowrow_factory)cs    D/home/agent/.hermes/assets/Gesundheit/scripts/apple_health_import.pyconr   $   s    AKAMH    r   Nonec                   |                      d           |                      d           |                      d           |                      d           |                      d           d |                      d          D             }d|vr|                      d	           d
|vr|                      d           |                                  d S )NaJ  
        CREATE TABLE IF NOT EXISTS apple_health_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            record_type TEXT,
            metric TEXT,
            start_date TEXT,
            end_date TEXT,
            value REAL,
            value_text TEXT,
            unit TEXT,
            source_name TEXT,
            source_version TEXT,
            device TEXT,
            file_name TEXT,
            file_hash TEXT,
            record_hash TEXT UNIQUE,
            raw_json TEXT NOT NULL,
            imported_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
        zeCREATE INDEX IF NOT EXISTS idx_apple_health_type_date ON apple_health_records(record_type,start_date)zbCREATE INDEX IF NOT EXISTS idx_apple_health_metric_date ON apple_health_records(metric,start_date)zXCREATE INDEX IF NOT EXISTS idx_apple_health_file_hash ON apple_health_records(file_hash)a  
        CREATE TABLE IF NOT EXISTS apple_health_import_files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            file_name TEXT,
            file_path TEXT,
            file_hash TEXT UNIQUE,
            record_count INTEGER,
            inserted_count INTEGER,
            skipped_count INTEGER,
            status TEXT,
            error TEXT,
            imported_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
        c                    h | ]
}|d          S )    ).0rows     r   	<setcomp>z ensure_schema.<locals>.<setcomp>T   s    [[[#s1v[[[r   z,PRAGMA table_info(apple_health_import_files)retry_countzWALTER TABLE apple_health_import_files ADD COLUMN retry_count INTEGER NOT NULL DEFAULT 0last_attempt_atzEALTER TABLE apple_health_import_files ADD COLUMN last_attempt_at TEXT)executecommit)r   columnss     r   ensure_schemar&   *   s    II	  , IIuvvvIIrsssIIhiiiII	    \[+Y!Z!Z[[[GG##			klll''			YZZZHHJJJJJr   sbytes | strstrc                    t          | t                    r|                     dd          } t          j        |                                           S )Nzutf-8replace)
isinstancer)   encodehashlibsha256	hexdigest)r'   s    r   shar1   \   sC    !S )HHWi((>!&&(((r   ddict[str, Any]keys	list[str]r   c                .   |D ]}|| v r| |         dvr
| |         c S d |                                  D             }|D ]V}|                                                    dd                              dd          }||v r||         dvr
||         c S Wd S )NN c                    i | ]O\  }}t          |                                                              d d                              dd          |PS ) _-)r)   lowerr+   )r   kvs      r   
<dictcomp>zfirst.<locals>.<dictcomp>h   sL    XXXdaCFFLLNN""3,,44S#>>XXXr   r:   r;   r<   )itemsr=   r+   )r2   r4   r>   normnks        r   firstrD   b   s      66ad*,,Q4KKKXXaggiiXXXD  WWYYsC((00c::::$r(*448OOO4r   xfloat | Nonec                    | dv rd S 	 t          t          |                               dd                                                    S # t          $ r Y d S w xY w)Nr7   ,.)floatr)   r+   strip	Exception)rE   s    r   parse_floatrM   p   se    JtSVV^^C--3355666   tts   AA
 

AAr	file_name	file_hashc                4   t          | g d          }t          | g d          }t          | g d          }t          | g d          }t          | g d          }t          | g d          }t          | g d          }	t          | dd	g          }
t          | g d
          }t          j        | dd          }t          j        |||||||	| ddd          }t          |pd          t          |p|pd          t          |pd          t          |p|pd          t	          |          |d nt          |          |d nt          |          |	d nt          |	          |
d nt          |
          |d nt          |          ||t          |          |dS )N)typerecord_type
RecordType
identifiernamemetricquantityType)rW   rV   rR   rU   rX   )	startDate
start_datestartdate	timestampDate	startTime)endDateend_dateendEndendTime)valueValueqtyquantitycountduration
totalSleepasleepresultsystolic	diastolic)unitUnitunits)
sourceNamesource_namesourceSourceappsourceVersionsource_version)deviceDevice
deviceNameFT)ensure_ascii	sort_keys)rS   rW   rZ   ra   re   rp   ru   rawunknownr8   )rS   rW   rZ   ra   re   
value_textrp   rt   ry   rz   rO   rP   record_hashraw_json)rD   jsondumpsr)   rM   r1   )rN   rO   rP   rS   rW   r[   rb   	value_rawrp   ru   ry   rz   r   fp_basiss                 r   normalize_recordr   y   s   pppqqK1NNNOOF!cccddE
CCC
D
DCa  O  O  O  P  PI---..D1NNNOOF10@ABBN188899F
*QUd
;
;
;Cz"	 	 T	+ 	+ 	+H ;3)44f88y99%+2&&*u*++Y'''/ddS^^#d))%~tt3v;;"0"8$$c.>Q>Q .$$c&kk8}}  r   objIterable[dict[str, Any]]c              #    K   t          | t                    r | D ]}t          |t                    r|V  d S t          | t                    rt          |                     d          t                    r)|                     di                               d          n|                     d          }t          |t                    r!|D ]}t          |t                    s|                    d          p)|                    d          p|                    d          }|                    d          p|                    d          }|                    d          }t          |t                    ro|D ]l}t          |t                    rUt          |          }|                    d|           |                    d|           |                    d|           |V  md S d	D ]M}|                     |          }	t          |	t                    r!|	D ]}t          |t                    r|V   d S N|                     d          }	t          |	t                    r |	D ]}t          |t                    r|V  d S d
}
|                                 D ]_\  }}	t          |	t                    rE|	D ]B}t          |t                    r+t          |          }|                    d|           |V  d}
C`|
rd S | V  d S d S )NdatametricsrV   rR   rU   rr   rp   rW   )recordssamplesworkoutsbodyrA   resultsFT)r,   listdictget
setdefaultrA   )r   rE   r   mrV   rr   rowsykeyvalemittedr>   s               r   iter_json_recordsr      s*     #t  	 	A!T"" #t 0 9C3776??TX8Y8Yq#''&"%%)))444_b_f_fgp_q_qgt$$ 	 $ $!!T** uuV}}LfL|9L9Lg7!%%--uuV}}dD)) $! $ $%a.. $ $QALL666LL4888LL777"#GGGFQ 	 	C''#,,C#t$$     A!!T**  	 ggfooc4   	  a&& GGGFiikk 	' 	'FAs#t$$ ' ' 'A!!T** ' GGVQ///"& 	F					a0 0r   pathr   list[dict[str, Any]]c                   | j                                         }|dk    rDt          j        |                     d                    }t          t          |                    S |dk    rQ|                     ddd          5 }t          t          j	        |                    cd d d            S # 1 swxY w Y   t          d| j                    )	Nz.jsonz	utf-8-sig)encodingz.csvrN   r8   )r   newlinezUnsupported file type: )suffixr=   r   loads	read_textr   r   opencsv
DictReader
ValueError)r   r   r   fs       r   load_recordsr      s    [  Fj==>>%c**+++YYs["Y== 	+q))**	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+
<t{<<
=
==s   !B00B47B4c                    t                               dd           t           t          j                    dd| j         z  }t          j        t          |           t          |                     |S )NT)parentsexist_okz%Y%m%d_%H%M%S_%fr;   )	PROCESSEDmkdirr   nowrV   shutilmover)   )r   targets     r   archive_filer      s^    OOD4O000HLNNIIIdiIIIF
KD		3v;;'''Mr   errorrL   c                    |                      d|j        t          |          |ddddt          |          f           |                                  d S )Na  INSERT INTO apple_health_import_files
           (file_name,file_path,file_hash,record_count,inserted_count,skipped_count,status,error,retry_count,last_attempt_at)
           VALUES(?,?,?,?,?,?,?,?,1,CURRENT_TIMESTAMP)
           ON CONFLICT(file_hash) DO UPDATE SET
             file_name=excluded.file_name,
             file_path=excluded.file_path,
             status='error',
             error=excluded.error,
             retry_count=COALESCE(apple_health_import_files.retry_count,0)+1,
             last_attempt_at=CURRENT_TIMESTAMPr   r   )r#   rV   r)   r$   )r   r   rP   r   s       r   record_import_errorr      sT    II		2 
CIIy!Q7CJJG   HHJJJJJr   Fr   boolc           
        |                                  }t          |          }t                      }t          |           |                    d|f                                          }|ri|d         dk    r]|r0|                                 rt          t          |                     nd }|	                                 t          |           ddd|dS 	 t          |           }dx}}	|                    d           |r.|                    d|f           |                    d	|f           |D ]N}
t          |
| j        |          }	 |                    d
|           |dz  }5# t          j        $ r |	dz  }	Y Kw xY w|                    d| j        t          |           |t          |          ||	df           |                                 |rt          |            t          |           dt          |          ||	d|	                                 S # t"          $ r,}|                                 t'          || ||            d }~ww xY w# |	                                 w xY w)NzASELECT id,status FROM apple_health_import_files WHERE file_hash=?statusimportedalready_importedr   )filer   insertedskippedarchived_tozBEGIN IMMEDIATEz2DELETE FROM apple_health_records WHERE file_hash=?z7DELETE FROM apple_health_import_files WHERE file_hash=?a  
                    INSERT INTO apple_health_records
                    (record_type,metric,start_date,end_date,value,value_text,unit,source_name,source_version,device,file_name,file_hash,record_hash,raw_json)
                    VALUES (:record_type,:metric,:start_date,:end_date,:value,:value_text,:unit,:source_name,:source_version,:device,:file_name,:file_hash,:record_hash,:raw_json)
                    r   zINSERT INTO apple_health_import_files
               (file_name,file_path,file_hash,record_count,inserted_count,skipped_count,status,error,retry_count,last_attempt_at)
               VALUES(?,?,?,?,?,?,?,'',0,CURRENT_TIMESTAMP))r   r   r   r   r   )
read_bytesr1   r   r&   r#   fetchoneexistsr)   r   closer   r   rV   r   IntegrityErrorlenr$   rL   rollbackr   )r   r   bfhr   existingr   r   r   r   rN   nes                r   import_filer      s   A	QBA!yy\_a^cddmmooH zHX&*4415Q$++--Qc,t,,---T				D		-?Q[\mxyyy%t$$7			#$$$ 	XIIJRERRRIIORTQVWWW 	 	A DIr22A		
    A)   1			? YD		2s7||Xw
S		
 	
 	
 	



 	D		ZCLL^fsz{{ 	
				    	

AtR+++
 	
				sJ   A2H 	E%$H %E<9H ;E<<BH 
I 'III I%c            	        t          j                    } |                     ddd           |                     ddd           |                                 d	 j        D             pt
          g}g }|D ]}|                                rM|                    t          g |	                    d
          |	                    d                               c|
                                r|                    |           fd|D             }t          t          j        t          |          |ddd                     d S )Npaths*z:JSON/CSV files or directories. Default: apple health inbox)nargshelpz--move
store_truez5Move imported files to processed/apple_health_exports)actionr   c                P    g | ]#}t          |                                          $S r   )r   
expanduser)r   ps     r   
<listcomp>zmain.<locals>.<listcomp>3  s*    666aT!WW!!666r   z*.jsonz*.csvc                <    g | ]}t          |j                   S ))r   )r   r   )r   r   argss     r   r   zmain.<locals>.<listcomp>:  s(    ===!{149---===r   )
files_seenr   F   )r}   indent)argparseArgumentParseradd_argument
parse_argsr   INBOXis_dirextendsortedglobr   appendprintr   r   r   )apr   filesr   r   r   s        @r   mainr   .  s<   		 	"	"BOOG3-iOjjjOOH\8oOppp==??D664:666A5'EE  88:: 	LL E!&&"2"2 EQVVG__ EFFGGGGXXZZ 	LLOOO====u===G	$*CJJ7CCRW`a
b
b
bcccccr   __main__)r   r   )r   r   r   r   )r'   r(   r   r)   )r2   r3   r4   r5   r   r   )rE   r   r   rF   )rN   r3   rO   r)   rP   r)   r   r3   )r   r   r   r   )r   r   r   r   )r   r   r   r   )
r   r   r   r   rP   r)   r   rL   r   r   )F)r   r   r   r   r   r3   )r   r   )"__doc__
__future__r   r   r   r.   r   osr   r   r   pathlibr   typingr   r   homeBASEr   r   r   r   r&   r1   rD   rM   r   r   r   r   r   r   r   __name__r   r   r   <module>r      s    # " " " " "  



   				                              ty{{Y)L8	w//;!77	   / / / /d) ) ) )      $ $ $ $N6 6 6 6r> > > >      "/ / / / /dd d d d  zDFFFFF r   