
    #jd                       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	 d dl
mZ d dlmZmZ d dlmZmZ d dlmZmZ d dlmZmZ d dlmZ  ee      j7                         j8                  Zed	z  d
z  Zedz  Zh dZ dZ!da" ejF                         Z$d Z%d Z&d Z'd Z(dVdZ)d Z*dWdZ+d Z,d Z-d Z.d Z/d Z0d Z1d Z2dXdZ3d Z4d Z5d  Z6d! Z7d" Z8dVd#Z9d$ Z:d% Z;d& Z<d' Z=dYd)Z>d* Z?d+ Z@d, ZAd- ZBd. ZCd/ ZDd0 ZEd1 ZFdZd2ZGd3 ZHd4 ZId[d5ZJd6 ZDd7 ZKd8 ZLd9 ZMd: ZNd; ZOd< ZPd= ZQd> ZRd? ZSd\d@ZTdA ZUd[dBZVe!fdCZWdD ZXd]dEZYdF ZZdG Z[dH Z\ G dI dJe      Z]d^dKZ^d^dLZ_d^dMZTd[dNZVe`dOk(  ri eaej                        dPkD  r ecej                  dP         ndQZd e'         e)d(R        e\         eedSed dT        edUedfe]      j                          yy)_    N)datetimetimezone)Path)ThreadingHTTPServerSimpleHTTPRequestHandler)urlparseparse_qs)Requesturlopen)	HTTPErrorURLError)load_databaseDATABASEzGRAFO_D_DATABASE.sqlitezrender_excel_like.py>   folders
event_metafolder_metagraph_set_cards   Fc                  R    t        j                  dt        j                               S )N%Y-%m-%dT%H:%M:%SZ)timestrftimegmtime     grafo_d_server.pynow_isor      s    ==-t{{}==r   c                      t        j                  t        d      } t         j                  | _        | j                  d       | j                  d       | j                  d       | S )N   timeoutzPRAGMA foreign_keys = ONzPRAGMA journal_mode = WALzPRAGMA busy_timeout = 5000)sqlite3connectDBRowrow_factoryexecutecons    r   
connect_dbr*   !   sI    
//"b
)CkkCOKK*+KK+,KK,-Jr   c                      t               5 } | j                  d       | j                  d       t        |        | j                          ddd       y# 1 sw Y   yxY w)zFIdempotent schema extension inside the same canonical SQLite database.z
            CREATE TABLE IF NOT EXISTS ui_state (
                key TEXT PRIMARY KEY,
                value_json TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )
        aL  
            CREATE TABLE IF NOT EXISTS api_ingest_log (
                id INTEGER PRIMARY KEY,
                api_id TEXT NOT NULL,
                event_legacy_id TEXT NOT NULL,
                action TEXT NOT NULL,
                created_at TEXT NOT NULL,
                UNIQUE(api_id, event_legacy_id)
            )
        N)r*   r'   repair_malformed_api_eventscommitr(   s    r   ensure_runtime_schemar.   *   sU    	   	 	 	 		 	$C(

)  s   >AAc                  *    t                t               S N)r.   r   r   r   r   load_dbr1   C   s    ?r   c                 h    d }| r |        y t        j                  |d      j                          y )Nc                      t         j                         sy t        5  t        j                  t
        j                  t        t               gt        t              d       d d d        y # 1 sw Y   y xY w)NF)cwdcheck)	RENDERexists_render_lock
subprocessrunsys
executablestrROOTr   r   r   _runzrender_frontend.<locals>._runI   sK    }} 	VNNCNNCK8c$iuU	V 	V 	Vs   AA((A1Ttargetdaemon)	threadingThreadstart)blockr?   s     r   render_frontendrG   H   s+    V
 T288:r   c                 p   | yt        |       j                         }|sy	 t        |      }|dkD  r|dz  }|dk  s|dkD  ryt	        j
                  |t        j                        }|j                  d      |j                  d      |j                  |j                  |j                  fS # t        $ r Y yw xY w)	z^Return (iso_datetime, yyyy-mm-dd, year, month, day) for valid Unix epoch seconds/milliseconds.Nl    d(	 g     @@iCm8l    Wi )tzr   z%Y-%m-%d)r=   stripfloat	Exceptionr   fromtimestampr   utcr   yearmonthday)valuerawnumberdts       r   epoch_to_utc_partsrV   T   s    }
e*


Cs &	Vj0			8<<	8B
()
J


   s   B) )	B54B5c                     |D ]9  }t        | t              s| j                  |      dvs(| j                  |      c S  |S )N)N )
isinstancedictget)mappingkeysdefaultkeys       r   first_presentr`   n   sA     $gt$S)9)K;;s##$ Nr   c                    t        | t              sy| g}dD ]5  }| j                  |      }t        |t              s%|j                  |       7 d}d}|D ]9  }t	        ||d      }t	        ||d      }| |#t        |      t        |      fc S  | j                  d      }	t        |	t        t        f      r*t        |	      dk\  rt        |	d         t        |	d	         fS y)
zIExtract latitude/longitude from common API-center and ISS payload shapes.NN)iss_positionpositionlocationcoordinatesgeo)latitudelat)	longitudelonlnglongNrf         r   )	rY   rZ   r[   appendr`   r=   listtuplelen)
item
candidatesr_   vallat_keyslon_keyssrcri   rk   coordss
             r   extract_lat_lonr{   u   s    dD!JM #hhsmc4 c"#
 #H2H &C40C40?ss8SX%%	& XXm$F&4-(S[A-=6!9~s6!9~--r   c                     t        | dd      }|rt        |      S t        | t              r| j	                  d      nd }t        |t              r |j                         r|j                         S |
|d| d| S y)N)placelocation_namecitycountryregionrX   re   zLow Earth Orbit / lat z, lon )r`   r=   rY   rZ   r[   rJ   )rt   ri   rk   explicitlocs        r   build_placer      su    T#Z\^_H8}",T4"8$((:
dC#s		yy{
3?'uF3%88r   c                     t        | j                  d      xs | j                  d      xs d      j                         }d|v xs t        |t              xr d|v S )NnameidrX   ISSrc   )r=   r[   upperrY   rZ   )api_rowrt   api_names      r   is_iss_payloadr      sR    7;;v&A'++d*;ArBHHJHHSD$!7!RNd<RSr   c                 &    | j                  d       y)z
    Remove malformed generic ISS rows produced by the earlier API mapper.
    They are identifiable by an epoch value parsed as a year plus empty place.
    Valid historical ISS_POS_* rows are not touched.
    af  
        DELETE FROM events
        WHERE COALESCE(place, '') = ''
          AND COALESCE(date_year, 0) > 9999
          AND (
              UPPER(COALESCE(event_name, '')) LIKE '%ISS-LIVE-LOCATION%'
              OR UPPER(COALESCE(api_text, '')) LIKE '%ISS-LIVE-LOCATION%'
              OR UPPER(COALESCE(who, '')) = 'ISS-LIVE-LOCATION'
          )
        N)r'   r(   s    r   r,   r,      s     KK		r   c           	      x   t        | j                  d      xsN | j                  d      xs; | j                  d      xs( | j                  d      xs | j                  d      xs d      j                         }dd l}t	        |      }|r9|\  }}}}}| j                  d      xs || d<   || d<   || d<   || d	<   || d
<   |||fS |j                  d|      }	|	rut        |	j                  d            }|	j                  d      t        |	j                  d            nd}|	j                  d      t        |	j                  d            nd}n|j                  d|      D 
cg c]  }
t        |
       }}
|sF|j                  dt        | j                  dd                  D 
cg c]  }
t        |
       }}
|d d }t        |      dk\  r|d   nd }t        |      dk\  r|d   nd}t        |      dk\  r|d   nd}|0|| d<   || d	<   || d
<   | j                  d      s|dd|dd|d| d<   |||fS c c}
w c c}
w )Ndate	date_text	timestampr   rO   rX   r   r   rP   rQ   z)^(\d{1,5})(?:-(\d{1,2}))?(?:-(\d{1,2}))?$ro   rn      z\d+r   04d-02d)
r=   r[   rJ   rerV   matchintgroupfindallrs   )eventrS   r   epochiso_datetimer   rO   rP   rQ   mxnums	name_numss                r   parse_event_dater      sU   
eii  D599[#9  DUYY{=S  DW\W`W`agWh  Dlqluluv|l}  D  BD  E  K  K  MCs#E491iuc!IIj1A\j!ffgeUC
=sCA1771:#$771:#9AGGAJq!"!7c!''!*oQ "

63 781A88)+FC		&RT@U<V)WXAQXIXRa=Dd)q.tAwdt9>QqTad1gQfgeyy #Cj%Ac#Y?E&M 9Xs    H2#H7c                 .   t        | xs i       }|j                  d|j                  d      xs0 |j                  d      xs t        t	        j
                                      |j                  d|j                  d      xs( |j                  d      xs |j                  d      xs d       |j                  d	|j                  d
      xs( |j                  d      xs |j                  d      xs d       t        |       |j                  d|j                  d      xs d       |j                  d|j                  d      xs |d          t        |d         |d<   t        |d         |d<   t               |d<   |j                  d|d          |S )Nr   event_id	legacy_idr   titlelabel
event_nameUntitled eventrowgraphgraph_idrow_keyr^   	start_colrE   ro   end_colend
updated_at
created_at)	rZ   
setdefaultr[   r=   uuiduuid4r   r   r   )payloadr   s     r   normalize_eventr      sZ   BE	T599Z0_EIIk4J_cRVR\R\R^N_`	VUYYw/t599W3EtS_I`tdtu	UEIIg.l%))J2Gl599U^K_lclmU	[%))G"4"9:	Y		% 0 FE+4FGU;/0E+5+,E)!)E,	\5#67Lr   c                    t        |xs d      j                         xs d}t               }| j                  d|f      j	                         }|rt        |d         S | j                  d|||f      }t        |j                        S )NUncategorized%SELECT id FROM folders WHERE name = ?r   zNINSERT INTO folders(name, is_open, created_at, updated_at) VALUES (?, 1, ?, ?))r=   rJ   r   r'   fetchoner   	lastrowid)r)   r   folder_nametsr   curs         r   get_or_create_folderr      s    d-o.446I/K	B
++=~
N
W
W
YC
3t9~
++X	b"C s}}r   c                 D   t        |xs d      j                         xs d}t               }| j                  d|f      j	                         }|rt        |d         S t        | |      }| j                  d|t        |xs |      |||f      }t        |j                        S )Nr^   'SELECT id FROM graphs WHERE row_key = ?r   z
        INSERT INTO graphs(row_key, visible_name, folder_id, color, created_at, updated_at)
        VALUES (?, ?, ?, NULL, ?, ?)
        )r=   rJ   r   r'   r   r   r   r   )	r)   r   visible_namer   r_   r   r   	folder_idr   s	            r   get_or_create_graphr      s    
g"
#
)
)
+
8yC	B
++?#
H
Q
Q
SC
3t9~$S+6I
++	 
c,%#&	2r:C s}}r   c                 h    | j                  d|f      j                         }t        |d   xs d      S )NzGSELECT COALESCE(MAX(sort_index), 0) AS n FROM events WHERE graph_id = ?nr   )r'   r   r   )r)   r   r   s      r   max_sort_indexr   	  s3    
++_bjal
m
v
v
xCs3x}1r   c                    t        |j                  d      xs( |j                  d      xs |j                  d      xs d      }t        | |||j                  d      xs |j                  d      xs d      }t        |      \  }}}|j                  d      xs |j                  d	      }|s)|'|d
dt	        |xs d      ddt	        |xs d      d}t	        |j                  d      xs" |j                  d      xs t        | |      dz         }t	        |j                  d      xs |j                  d      xs |      }	t	        |j                  d      xs t        d|	|z
  dz               }
t	        |j                  d      xs |      }t               }i dt        |j                  d      xs' |j                  d      xs t        j                               d|d|dt        |j                  d      xs |j                  d      xs d      d	t        |xs d      d|d|d|d|d|	d|
dt        |j                  d      xs |j                  d      xs d      d t        |j                  d       xs( |j                  d!      xs |j                  d"      xs d      d#t        |j                  d#      xs; |j                  d$      xs( |j                  d%      xs |j                  d&      xs d      d't        |j                  d'      xs |j                  d(      xs d      d)t        |j                  d*      xs |j                  d)      xs d      d|d+|iS ),Nr   r   r   r^   r   folderr   r   r   r   r   ro   r   r   rE   r   r   duration
sort_indexr   r   r   r   r   r   rX   	date_year
date_monthdate_dayr}   PLACEwhoWHOactorbrief_descriptionbriefBRIEFdescriptionsource_linkurlapi_textapir   )
r=   r[   r   r   r   r   maxr   r   r   )r)   r   r   r   rO   rP   rQ   r   r   r   r   r   r   s                r   event_to_sql_valuesr     s   %))E"]eii	&:]eii>P]T]^G"3%))G:L:vPUPYPYZbPc:vgvwH'.D%		&!;UYY{%;I)Cj#ejq/#!6aCH1c7JK	EIIk*eeii.@eNSVX`DadeDefI%))I&G%))E*:GiHG599Z(KC7Y3F3J,KLHUYY|,9	:J	BS4REIIk,BRdjjlSH 	7 	c%))F+Zuyy/FZJZ[	
 	Sb) 	T 	e 	C 	Y 	7 	H 	UYYw'C599W+=CD 	s599U#Suyy'7S599W;MSQST 	SII)* yy!yy! yy' 
* 	s599]3Muyy7GM2N+, 	C		%(GEIIj,AGRH-. 	j/0 	b1 r   c                    ddl }ddlm t        | xs d      j	                         }|st        d      |j                         j	                         }|j                  dd      }|j                  d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ddddd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,d0d1d2d1d3d1d4d5d5d5d6d6d6d6d7}dAfd8	}|j                  d9|      }|r ||j                  d
            S |j                  d:|      }|r' ||j                  d
      |j                  d            S |j                  d;|      }|r7 ||j                  d
      |j                  d      |j                  d            S |j                  d<|      }|r7 ||j                  d      |j                  d      |j                  d
            S |j                  d=|      }|r=|j                  d
      |v r* ||j                  d      ||j                  d
               S |j                  d>|      }|rM|j                  d      |v r: ||j                  d      ||j                  d         |j                  d
            S |j                  d?|      }|rM|j                  d
      |v r: ||j                  d      ||j                  d
         |j                  d            S t        d@|z         )Ba  
    Deterministic smart date parser for manual UI/API events.

    Accepted:
    - 1989
    - 1989-01
    - 1989-01-01
    - 01/01/1989
    - 1/1/1989
    - 01-01-1989
    - 1989/01/01
    - June 1989
    - 4 June 1989
    - Junio 1989
    - 4 Junio 1989

    Policy:
    - Year-only remains year-only: date_text='1989', month/day NULL.
    - Month-only remains month-level: date_text='1989-06', day NULL.
    - Full date is normalized to YYYY-MM-DD.
    - Slash dates default to DD/MM/YYYY.
    r   N)r   rX   zdate is required, z\s+janro   januaryeneenerofebrn   februaryfebreromarr   marchmarzoapr   aprilabrabrilmay   mayojun   junejuniojul   julyjulioaug   augustagoagostosep	   sept	september
septiembreoct
   octoberoctubrenov      )november	noviembredecdecemberdic	diciembrec                    t        |       } | dk  s| dkD  rt        d      |t        |       | d d fS t        |      }|dk  s|dkD  rt        d      || dd|d| |d fS t        |      } | ||       | dd|dd|d| ||fS )	Nro   i'  zyear out of ranger  zmonth out of ranger   r   r   )r   
ValueErrorr=   )yr   dr   s      r   r5   zsmart_parse_date.<locals>.checkh  s    Fq5AH0119q61dD((Fq5AF1229WAaW%q!T11FQ1C!C!C)1a22r   z(\d{4})z(\d{4})[-/](\d{1,2})z!(\d{4})[-/](\d{1,2})[-/](\d{1,2})z!(\d{1,2})[-/](\d{1,2})[-/](\d{4})u   ([a-záéíóúñ]+)\s+(\d{4})u*   (\d{1,2})\s+([a-záéíóúñ]+)\s+(\d{4})u*   ([a-záéíóúñ]+)\s+(\d{1,2})\s+(\d{4})zunsupported date format: rb   )r   r   r   r=   rJ   r  lowerreplacesub	fullmatchr   )rR   r   rS   txtmonthsr5   r   r   s          @r   smart_parse_dater  7  s   . 
ekr

 
 
"C+,,
))+


C
++c3
C
&&c
"CqQ %q*11qa!*A 	q 1 &q 	q	 1	 $Q	 )0	
 	q
 ! 	q ! %a 	q ! %a 	q A  %a *21 	q ! )! .:1 	r b #,R 	r  "r"2F3& 	Z%AQWWQZ  
,c2AQWWQZ,,
93?AQWWQZQWWQZ88
93?AQWWQZQWWQZ88
6<AQWWQZ6!QWWQZ
!344
BCHAQWWQZ6!QWWQZ
!3QWWQZ@@
BCHAQWWQZ6!QWWQZ
!3QWWQZ@@
036
77r   c                 >   t                t        | xs i       }t        |j                  d      xs; |j                  d      xs( |j                  d      xs |j                  d      xs d      j	                         }|st        d      t        |j                  d      xs( |j                  d      xs |j                  d	      xs d      j	                         }|st        d
      t        |j                  d      xs |j                  d      xs d      j	                         }t        |      \  }}}}t        |j                  d      xs |j                  d      xs d      j	                         }	t        |j                  d      xs |j                  d      xs d      j	                         }
t        |j                  d      xs; |j                  d      xs( |j                  d      xs |j                  d      xs d      j	                         }t        |j                  d      xs |j                  d      xs d      j	                         }t        |j                  d      xs |j                  d      xs d      j	                         }t               }t               5 }|j                  d       |j                  d|f      j                         }|rt        |d         }nt        |||d      }|j                  d|f      j                         }t        |d   xs d       }| d!| d!| d!| }|j                  d"||||||||d d d |	|
||||||f      }|j                          d#d#d#       d$j                  ||||||d%S # 1 sw Y   xY w)&z
    Insert one manually-created event into canonical SQLite using smart date parsing.
    This is used by POST /api/events.
    r   r   r   	graph_rowrX   zrow_key/row/graph is requiredr   r   r   zevent name is requiredr   r   r}   r   r   r   r   r   r   r   r   r   r   sourcez	manual-uiBEGIN IMMEDIATEz5SELECT id, visible_name FROM graphs WHERE row_key = ?r   r   zSSELECT COALESCE(MAX(sort_index), 0) + 1 AS next_sort FROM events WHERE graph_id = ?	next_sortro   :a  
            INSERT INTO events(
                legacy_id, graph_id, row_key, event_name,
                date_text, date_year, date_month, date_day,
                start_col, end_col, duration,
                place, who, brief_description, source_link, api_text,
                sort_index, created_at, updated_at
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            NT)okr   r   r   r   r   r   r   )r.   rZ   r=   r[   rJ   r  r  r   r*   r'   r   r   r   r-   r   )r   datar   r   raw_dater   r   r   r   r}   r   r   r   r   r   r)   r   r   sort_rowr   r   r   s                         r   insert_manual_event_sqlr'    s   
 2D 	88E?	88G	 88K 	  eg  899 	88F	88G	 	
 eg  122488F#Btxx'<BCIIKH1A(1K.Iy*h!<TXXg%6<"=CCEE
dhhuo6%6B
7
=
=
?C$% 	88G	88G	 88M"	  eg  dhh}-F%FBGMMOK488J'L488H+=LMSSUH	B	 7%&CJ
 (* 	
 5;'H*3/RH;;aK
 (* 	 +.3!4
iqAi[*F	kk	 !' 
D 	

o7t mm  	 	s7 7s   /CNNc                    t                t        | xs i       }t        |j                  d      xs |j                  d      xs d      j	                         }t        |j                  d      xs( |j                  d      xs |j                  d      xs d      j	                         }t        |j                  d      xs( |j                  d      xs |j                  d	      xs d      j	                         }t        |j                  d
      xs |j                  d      xs d      j	                         }d}d}d}d}	|rt        |      \  }}}}	t        |j                  d      xs d      j	                         }
t        |j                  d      xs d      j	                         }t        |j                  d      xs |j                  d      xs d      j	                         }t        |j                  d      xs |j                  d      xs d      j	                         }t        |j                  d      xs |j                  d      xs d      j	                         }t               }t               5 }|j                  d       d}|r!|j                  d|f      j                         }|)|r'|r%|r#|j                  d|||f      j                         }|&|r$|r"|j                  d||f      j                         }|t        d      t        |d         }|j                  d|f      j                         }|xs |d   }||n|d   }||n|d   }||n|d   }||	n|d   }|j                  d||||||
||||xs |d   ||f       |j                  d|f      j                         }|j                          ddd       	 t        d d!d"#       d$t              d%S # 1 sw Y   &xY w# t        $ r Y %w xY w)&a|  
    Update canonical event fields from the right-side technical card.

    Matching policy:
    1. legacy_id/id if present.
    2. row_key + event_name + date_text if legacy id is unavailable.
    3. row_key + event_name fallback.

    This updates SQLite canonical columns:
    event_name, date_text/year/month/day, place, who,
    brief_description, source_link, api_text.
    r   r   rX   r   r   r   r   r   r   r   r   Nr}   r   r   r   r   r   r   r   r   zTSELECT id, legacy_id, row_key, event_name, date_text FROM events WHERE legacy_id = ?z
                SELECT id, legacy_id, row_key, event_name, date_text
                FROM events
                WHERE row_key = ? AND event_name = ? AND date_text = ?
                ORDER BY id DESC
                LIMIT 1
                z
                SELECT id, legacy_id, row_key, event_name, date_text
                FROM events
                WHERE row_key = ? AND event_name = ?
                ORDER BY id DESC
                LIMIT 1
                z$event not found for canonical updatez
            SELECT event_name, date_text, date_year, date_month, date_day,
                   place, who, brief_description, source_link, api_text
            FROM events
            WHERE id = ?
            r   r   r   a  
            UPDATE events
            SET event_name = ?,
                date_text = ?,
                date_year = ?,
                date_month = ?,
                date_day = ?,
                place = ?,
                who = ?,
                brief_description = ?,
                source_link = ?,
                api_text = ?,
                updated_at = ?
            WHERE id = ?
            z
            SELECT id, legacy_id, row_key, event_name, date_text,
                   date_year, date_month, date_day,
                   place, who, brief_description, source_link, api_text
            FROM events
            WHERE id = ?
            zevent-card-saver   r#  dirtystatusTr#  r   )r.   rZ   r=   r[   rJ   r  r   r*   r'   r   r  r   r-   record_persistence_auditrL   )r   r$  r   r   r   r%  r   r   r   r   r}   r   r   r   r   r   r)   r   r   currentnew_event_namenew_date_textnew_date_yearnew_date_monthnew_date_daysaveds                             r   update_event_card_sqlr5    s    2DDHHTNAdhh{&;ArBHHJI$((9%S%SDHHW<MSQSTZZ\GTXXl+Ztxx/?Z488GCTZXZ[aacJ488F#Btxx'<BCIIKHIIJH5Eh5O2	9j(!'R(..0E
dhhuo#
$
*
*
,C$% 	88M"	 eg	 
 dhh}-F%FBGMMOK488J'@488E?@bAGGIH	B	 c%&++f hj 
 ;7zi++ *i0	 hj  ;7z++ *%	 hj  ;CDDs4y>++ K
 (* 	 $<w|'<%.%:	@T%.%:	@T'0'<',BW#,#8xgj>Q  !/GJ/!	
@  K	
 (* 	 	

GcJ !2!DI
 e Uc cN  s   ?E	O-O9 -O69	PPc                    t                t        |       }t               5 }|j                  d       t	        ||      }|j                  d|d   f      j                         }|r|s|j                          t        d      |r|j                  d|       d}n4|j                  d      xs
 t               |d<   |j                  d|       d	}|j                          d d d        |d
S # 1 sw Y   xY w)Nr   z)SELECT id FROM events WHERE legacy_id = ?r   zduplicate event ida?  
                UPDATE events
                SET graph_id=:graph_id, row_key=:row_key, event_name=:event_name,
                    date_text=:date_text, date_year=:date_year, date_month=:date_month,
                    date_day=:date_day, start_col=:start_col, end_col=:end_col,
                    duration=:duration, place=:place, who=:who,
                    brief_description=:brief_description, source_link=:source_link,
                    api_text=:api_text, sort_index=:sort_index, updated_at=:updated_at
                WHERE legacy_id=:legacy_id
                updatedr   a  
                INSERT INTO events(
                    legacy_id, graph_id, row_key, event_name, date_text, date_year,
                    date_month, date_day, start_col, end_col, duration, place, who,
                    brief_description, source_link, api_text, sort_index, created_at, updated_at
                )
                VALUES(
                    :legacy_id, :graph_id, :row_key, :event_name, :date_text, :date_year,
                    :date_month, :date_day, :start_col, :end_col, :duration, :place, :who,
                    :brief_description, :source_link, :api_text, :sort_index, :created_at, :updated_at
                )
                inserted)actionr   )r.   r   r*   r'   r   r   rollbackr  r[   r   r-   )r   allow_upsertnormr)   valuesexistingr9  s          r   insert_event_sqlr?    s    5!D	 *%&$S$/;;7&:M9O

(* 	 LLLN122KK	  F#'88L#9#FWYF< KK   F

U*V t,,W* *s   B<C))C2c                    t                t               5 }|j                  d       |j                  dt        |       t        |       f      j	                         }|s|j                          	 d d d        y i d|d   xs t        |d         d|d   d|d   d	|d
   xs 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   }|j                  |xs i        t        |t        |            }|d   |d<   |j                  d|       |j                          d d d        |S # 1 sw Y   S xY w)Nr   z@SELECT * FROM events WHERE legacy_id = ? OR CAST(id AS TEXT) = ?r   r   r   r   r   r   r   r   rX   rO   r   rP   r   rQ   r   r   r   r   r}   r   r   r   r   r   r   db_ida  
            UPDATE events
            SET graph_id=:graph_id, row_key=:row_key, event_name=:event_name,
                date_text=:date_text, date_year=:date_year, date_month=:date_month,
                date_day=:date_day, start_col=:start_col, end_col=:end_col,
                duration=:duration, place=:place, who=:who,
                brief_description=:brief_description, source_link=:source_link,
                api_text=:api_text, sort_index=:sort_index, updated_at=:updated_at
            WHERE id=:db_id
            )
r.   r*   r'   r=   r   r:  updater   r   r-   )r   patchr)   r   r.  r=  s         r   update_event_sqlrD    s   	 ,%&kkN]CM*
 (* 	 LLN, ,
#k"4c#d)n
3y>
 C%
 C$*	

 C$
 S&
 3z?
 [)
 s9~
 J
 S\
 3u:
 323
 3}-
 J
  #l+!
$ 	u{#$S/'*BCd)w	 	
 	

Y,Z N[,Z Ns   AE7C
EEc                    t                t               5 }|j                  d       |j                  dt        |       t        |       f      }|j	                          |j
                  dkD  cd d d        S # 1 sw Y   y xY w)Nr   z>DELETE FROM events WHERE legacy_id = ? OR CAST(id AS TEXT) = ?r   )r.   r*   r'   r=   r-   rowcount)r   r)   r   s      r   delete_event_sqlrG    si    	  %&kkL]CM*
 	

||a     s   AA55A>c           	         |sy |D ch c]  }t        |       }}| j                  d      j                         }|D ]h  }|d   }	 t        j                  |d         }d}|dk(  rt        |t              rdD ]R  }	t        |j                  |	      t              s#||	   D cg c]  }t        |      |vs| }
}|
||	   k7  sL|
||	<   d}T t        |j                  d      t              r7t        |d   j                               D ]  }t        |      |v s|d   |= d} |d	v rHt        |t              r8t        |j                               D ]  t        fd
|D              s|= d} |s6| j                  dt        j                  |dd      t               |f       k y c c}w # t
        $ r Y ~w xY wc c}w )Nz$SELECT key, value_json FROM ui_stater_   
value_jsonFr   )deletedRows
deleteRowsTgraphFolder)r   r   r   c              3   x   K   | ]1  }t              j                  |d z         xs t              |k(   3 yw)r"  N)r=   
startswith).0r  meta_keys     r   	<genexpr>z6cleanup_ui_state_after_graph_delete.<locals>.<genexpr>:  s6     `STs8}//C8NCMQ<NN`s   7:z@UPDATE ui_state SET value_json = ?, updated_at = ? WHERE key = ?ensure_ascii	sort_keys)r=   r'   fetchalljsonloadsrL   rY   rZ   r[   rq   r]   anydumpsr   )r)   deleted_rowsr   deletedrowsr   r_   rR   changedknew_list	graph_keyrP  s               @r   #cleanup_ui_state_after_graph_deletera    s   +,!s1v,G,;;=>GGID !%j	JJs<01E )
5$ 72 'eiilD1+08Mas1vW7LMHM58+#+a"&' %))M2D9!%eM&:&?&?&A!B 'I9~0!-0;"&'
 BBzRWY]G^ . #`X_``h"G#
 KKREF	SVW=! -  		  Ns#   F+F0#G 6G 0	F=<F=c           	         t                ddlm} g }| xs g D ]>  } |t        |xs d            j	                         }|s)||vs.|j                  |       @ g }g }t               }t               5 }|j                  d       |D ]  }|dk(  r|j                  |dd       |j                  d|f      j                         }	|	s|j                  |d	d       U|j                  d
|	d   f      j                         d   }
|
r|j                  |d|
d       |j                  d|	d   f       |j                  |        |j                  d      j                         }	|	r	 t        j                  |	d         }ni }t        |t              rt        |      }|j!                  dg       D cg c]  }t        |      |vr| c}|d<   |j!                  di       }t        |t              r+t#        |j%                               D ]
  }||v s||=  ||d<   |j!                  di       }t        |t              r0t#        |j'                               D ]  \  }}||v sd||<    ||d<   |j                  dt        j(                  |dd      ||f       |j+                          ddd       	 t-        ddd       d|||dS # t        $ r i }Y Rw xY wc c}w # 1 sw Y   6xY w# t        $ r Y 5w xY w)z
    Physically delete explicitly selected empty folders from SQLite.

    Rules:
    - Only requested folders are evaluated.
    - Uncategorized is protected.
    - Non-empty folders are rejected.
    - ui_state is cleaned only for deleted folders.
    r   unquoterX   r   r   	protected)r   reasonz+SELECT id, name FROM folders WHERE name = ?z	not foundz/SELECT COUNT(*) FROM graphs WHERE folder_id = ?r   z	not empty)r   rf  graphsz DELETE FROM folders WHERE id = ?3SELECT value_json FROM ui_state WHERE key='folders'rI  r   openrL  a!  
                INSERT INTO ui_state(key, value_json, created_at, updated_at)
                VALUES ('folders', ?, ?, ?)
                ON CONFLICT(key) DO UPDATE SET
                    value_json=excluded.value_json,
                    updated_at=excluded.updated_at
                FTrR  Nzdelete-empty-folderr#  r)  )r#  	requestedr[  skipped)r.   urllib.parserd  r=   rJ   rp   r   r*   r'   r   rV  rW  rL   rY   rZ   setr[   rq   r]   itemsrY  r-   r-  )folder_namesrd  namesr   r   r[  rk  r   r)   r   graph_countstatedeleted_setf
open_stategraph_folderr   r   s                     r   delete_empty_folders_sqlrw  F  s3    $ER s17|$**,D%LL
 GG	B	 N%& 	!D&$+FG++= hj 
 $+FG++AT hjK
 "))  
 KK:SYLINN4 9	!< kkA

(* 	 

3|#45 EeT"g,K !99Y3 q6,  E)
 62.J*d+joo/0 *AK'&qM* !+f 99]B7L,-'+L,>,>,@'A @OGV,0?W-@ (4m$KK EFBO	 	

]N` !6aM
 	 [   aN Nd  s^   2C2K%K=3K0KAKAKAK/K' KKKKK$'	K32K3Tc           	      l   t                t        | t              r| g} | xs g D cg c]5  }t        |      j                         st        |      j                         7 } }| s	dddddg dS d}d}d}d}g }t	               5 }|j                  d       | D ](  }	|j                  d|	f      j                         }
|
s(t        |
d         }|j                  d|	|	f      j                         }|D cg c]  }t        |d          }}|D ]$  }|j                  d|f      }||j                  z  }& |D ]$  }|j                  d	|f      }||j                  z  }& |j                  d
||	f      }||j                  z  }|j                  d|f      }||j                  z  }|j                  s|j                  |	       + t        ||       |j                          d d d        |r$t	               5 }|j                  d       d d d        d|||||dS c c}w c c}w # 1 sw Y   BxY w# 1 sw Y   (xY w)NTr   )r#  deleted_graphsdeleted_eventsdeleted_apisdeleted_api_logsr\  r   z0SELECT id, row_key FROM graphs WHERE row_key = ?r   z>SELECT id FROM api_registry WHERE target_graph = ? OR name = ?+DELETE FROM api_ingest_log WHERE api_id = ?%DELETE FROM api_registry WHERE id = ?z4DELETE FROM events WHERE graph_id = ? OR row_key = ?zDELETE FROM graphs WHERE id = ?VACUUM)r.   rY   r=   rJ   r*   r'   r   r   rU  rF  rp   ra  r-   )row_keysvacuumr   ry  rz  r{  r|  physically_deleted_rowsr)   r   r   r   api_rowsrapi_idsapi_idr   s                    r   delete_graphs_sqlr    sz   (C :)1RK1CFLLNAKHK !
 	
 NNL 	 3%& -	8GKKB
 hj 
 5;'H{{P'" hj 
 .66s1T7|6G6! 1kkAI !CLL0 1 " -kk;I ,- ++F7#C cll*N++1C cll*N||'..w7[-	8^ 	,C1HI

g3j \ 	"SKK!	" (($,' W LJ 7'3 3l	" 	"s=   HHA0H1HB%H-0H1H*HH'*H3c                 2   t        | xs d      j                         }|sd}g }d}|j                         D ]<  }|j                         r|j	                  |       d}'|r*|j	                  d       d}> dj                  |      j                  d      xs dS )NrX   z	NEW GRAPHF_T	NEW_GRAPH)r=   rJ   r   isalnumrp   join)r   rS   outprev_uschs        r   slugify_graph_keyr    s    
djb/


!C
CGiik ::<JJrNG

3 GGCLs#2{3r   c           	      B   t                t        | xs i       }t        |j                  d      xs |j                  d      xs d      j	                         }|st        d      t               }t               5 }|j                  d       |j                  d|||f       |j                  d||f       |j                  d      j                         }|r	 t        j                  |d	         }ni }t        |t              si }|j                  d
g        |j                  di        |j                  di        |j                  dg        |j                  dg        d|d
   vr|d
   j                  dd       ||d
   vr|d
   j!                  |       d|d   |<   |j                  dt        j"                  |dd      ||f       |j%                          ddd       	 t'        ddd       d|dS # t        $ r i }Y w xY w# 1 sw Y   /xY w# t        $ r Y .w xY w)z
    Create a folder physically in SQLite.

    Canonical policy:
    - folders table is the source of truth.
    - ui_state is synchronized for UI open/visibility state.
    - no graph and no event is created.
    r   r   rX   zfolder name is requiredr   z~
            INSERT OR IGNORE INTO folders(name, is_open, created_at, updated_at)
            VALUES (?, 1, ?, ?)
            z
            UPDATE folders
            SET is_open = 1,
                updated_at = ?
            WHERE name = ?
            rh  rI  r   rL  ri  rJ  rK  r   r   T	  
            INSERT INTO ui_state(key, value_json, created_at, updated_at)
            VALUES ('folders', ?, ?, ?)
            ON CONFLICT(key) DO UPDATE SET
                value_json=excluded.value_json,
                updated_at=excluded.updated_at
            FrR  Nzcreate-folderr#  r)  )r#  r   )r.   rZ   r=   r[   rJ   r  r   r*   r'   r   rV  rW  rL   rY   r   insertrp   rY  r-   r-  )r   r$  r   r   r)   r   rr  s          r   create_folder_sqlr  ,  s.    2Dtxx;488H#5;<BBDD233	B	 =%& 2rN	
 	 J	
 kkA

(* 	 

3|#45 E%&EB'+$+r*%	"22)##A7uY'')##D)"fd ZZETBBK		
 	

{=~ $G $''Q  9= =B  sD   :AHG4/C)H!H 4H?HHHH	HHc                     t                t               } t               5 }|j                  d      j	                         }|sdddcddd       S 	 t        j                  |d         }t        |t              sddd
d	cddd       S |j                  dg       }t        |t              sdddd	cddd       S d}|j                  d       |D ]t  }t        |xs d      j                         }|s#|j                  d|f      j	                         }|j                  d|| | f       |j                  d| |f       |p|dz  }v |j                          ddd       ddS # t        $ r dddd	cY cddd       S w xY w# 1 sw Y   ,xY w)z
    Convert folders present in ui_state into canonical rows in folders.

    This makes Save meaningful for folders that were created by older UI code
    before POST /api/folders existed.
    rh  Tr   )r#  syncedNrI  Fzinvalid ui_state folders json)r#  r  errorzui_state folders is not objectr   zui_state folders list missingr   rX   r   z
                INSERT OR IGNORE INTO folders(name, is_open, created_at, updated_at)
                VALUES (?, 1, ?, ?)
                z=UPDATE folders SET is_open = 1, updated_at = ? WHERE name = ?ro   )r.   r   r*   r'   r   rV  rW  rL   rY   rZ   r[   rq   r=   rJ   r-   )r   r)   r   rr  r   r  r   befores           r   sync_ui_folders_to_sqlr    s    	B	 1kkA

(* 	 !,1 1	XJJs<01E %&17WX1 1  ))Ir*'4(17VW%1 1( %& 	Dtzr?((*D[[7 hj 
 KK r2 KKOT

 ~!1	4 	

c1f &))S  	X17VWW1 1	X1 1sB   &E?E!(E?'E?8BE?=E?!E<0E?;E<<E??Fc           	         t                t        | xs i       }t        |j                  d      xs |j                  d      xs d      j	                         }|st        d      t        |j                  d      xs d      j	                         xs d}t        |j                  d      xs t        |            j	                         }t               }t               5 }|j                  d       |}|}d	}	|j                  d
|f      j                         r.| d|	 }|	dz  }	|j                  d
|f      j                         r.|}t        ||||      }
|j                  d      j                         }|r	 t        j                  |d         }ni }|j                  dg        |j                  di        |j                  di        |j                  dg        |j                  dg        ||d   vr|d   j!                  |       ||d   |<   d|d   |<   |j                  dg       D cg c]  }t        |      |k7  s| c}|d<   g |d<   |j                  dt        j"                  |dd      ||f       |j%                          ddd       d
|||dddS # t        $ r i }Y w xY wc c}w # 1 sw Y   +xY w)z
    Create a graph/container physically in SQLite.

    Policy:
    - A graph is a container/row.
    - Creating a graph must NOT create timeline events.
    - Timeline components are created later by manual event insertion or API ingest.
    r   r   rX   zgraph name is requiredr   r   r   r   rn   r   r  ro   z5SELECT value_json FROM ui_state WHERE key = 'folders'rI  r   rL  ri  rJ  rK  Tr  FrR  Nr   )r   r   r   r   events_created)r#  r   )r.   rZ   r=   r[   rJ   r  r  r   r*   r'   r   r   rV  rW  rL   r   rp   rY  r-   )r   r$  r   r   r   r   r)   base	candidater   r   r   rr  r   s                 r   create_graph_sqlr    s    2DtxxA488N#;ArBHHJD122dhhx(;O<BBDWK$((9%@):4)@AGGIG	B	 4%&	kkCi\R[[]&!IFA kkCi\R[[] &sGT;GkkC

(* 	 

3|#45 EB'+$+r*eI..)##K0(3mW%%)fk"+099]B+G]a3q6U\K\]m l ZZETBBK		
 	

i4n  !
	 	A     ^M4 4sK   A&J41J48JBJ4,J/ J/AJ4J,(J4+J,,J44J=c                  X	   t                ddl} d}d}t               }t               5 }|j	                  d      j                         }|sddddcddd       S 	  | j                  |d   xs d      }t        |t              sddd	dcddd       S |j	                  d
       |j                         D ]|  \  }}	t        |	t              s|dz  }t        |xs d      }
|
j                  dd      }t        |      dk\  r|d   j                         nd}d}d}d}t        |      dk\  r0	 t        |d         }t        |d         }|d   j                         }t        |	j!                  d      xs, |	j!                  d      xs |xs |	j!                  d      xs d      j                         }t        |	j!                  d      xs |	j!                  d      xs |xs d      j                         }t        |	j!                  d      xs |	j!                  d      xs d      j                         }t        |	j!                  d      xs |	j!                  d      xs d      j                         }t        |	j!                  d      xs |	j!                  d      xs d      j                         }t        |	j!                  d      xs; |	j!                  d      xs( |	j!                  d      xs |	j!                  d      xs d      j                         }t        |	j!                  d       xs |	j!                  d!      xs d      j                         }|r|s|dz  }d}|&|$|j	                  d"||||f      j                         }|%|r#|j	                  d#|||f      j                         }|"|j	                  d$||f      j                         }||dz  }(|d   }|d%   }|d&   }|d'   }|r	 t#        |      \  }}}}|j	                  d(||||||||||t        |d)         f       |dz  } |j%                          ddd       ||d*S # t        $ r"}ddt        |      dcY d}~cddd       S d}~ww xY w# t        $ r d}d}Y Iw xY w# t        $ r Y w xY w# 1 sw Y   _xY w)+a  
    Synchronize UI technical-card metadata into canonical events.

    Source:
    - ui_state key = 'event_meta'

    Target:
    - events.place
    - events.who
    - events.brief_description
    - events.source_link
    - events.event_name
    - events.date_text/date_year/date_month/date_day

    This function does not create events. It only updates existing events.
    Matching uses:
    1. event_meta key format row:start:end:name when available.
    2. saved group/name/date fields.
    3. row_key + event_name fallback.
    r   Nz6SELECT value_json FROM ui_state WHERE key='event_meta'zno event_meta)r7  rk  rf  rI  z{})r7  rk  r  zevent_meta is not objectr   ro   rX   r"  r   r   rn   r   r   r   r   r   r   r   r}   r   r   r   r   r   r   r   r   r   ai  
                    SELECT id, event_name, date_text, date_year, date_month, date_day
                    FROM events
                    WHERE row_key = ?
                      AND start_col = ?
                      AND end_col = ?
                      AND event_name = ?
                    ORDER BY id DESC
                    LIMIT 1
                    aC  
                    SELECT id, event_name, date_text, date_year, date_month, date_day
                    FROM events
                    WHERE row_key = ?
                      AND event_name = ?
                      AND date_text = ?
                    ORDER BY id DESC
                    LIMIT 1
                    a  
                    SELECT id, event_name, date_text, date_year, date_month, date_day
                    FROM events
                    WHERE row_key = ?
                      AND event_name = ?
                    ORDER BY id DESC
                    LIMIT 1
                    r   r   r   a  
                UPDATE events
                SET event_name = ?,
                    date_text = ?,
                    date_year = ?,
                    date_month = ?,
                    date_day = ?,
                    place = ?,
                    who = ?,
                    brief_description = ?,
                    source_link = ?,
                    updated_at = ?
                WHERE id = ?
                r   )r7  rk  )r.   rV  r   r*   r'   r   rW  rL   r=   rY   rZ   rn  splitrs   rJ   r   r[   r  r-   )rV  r7  rk  r   r)   r   metaexcr_   rR   key_spartskey_row	key_startkey_endkey_namer   r   r%  r}   r   r   r   rA   r   r   r   r   s                               r   sync_event_meta_to_events_sqlr  !  s   * GG	B	 fkkD

(* 	  Q/Jf f	C4::c,/748D $% Q:TUf f  	%&**, R	JCeT*1	rNEKKQ'E*-e*/eAhnn&rGIGH5zQ# #E!HI!%(mG !8>>+		%  99Y' 99W%  eg  		&! 99\* 	
 eg  		&! 99[) eg	  		'*Feii.@FBGMMOEeii&@%))E*:@bAGGIC #		-. 99W%99W% 99]+ ! eg  eii6P%))E:JPbQWWYK*1F$)<	 i*= (*  ~( j(3 (*  ~ j)
 (*  ~1{+I{+I-Jj)HAQRZA[>Iy*h KK %t%: qLGeR	h 	

MfP 733}  	C QSBBf f	C2 ! # $I"G#B ! If fs   'R Q2R BR (Q=J,R 1RAR 	Q:Q5&Q:'R 5Q::R =R
R RR 	RR RR  R)c                     t                t               } t                t               5 }|j	                  d      j                         d   }|j	                  d      j                         d   }|j	                  d      j                         d   }d}	 |j	                  d      j                         d   }ddd       t                dk(  |dS # t        $ r d}Y *w xY w# 1 sw Y   /xY w)	af  
    Explicit session save endpoint.

    Operational policy:
    - Does not create data.
    - Does not delete data.
    - Forces frontend render from canonical SQLite.
    - Verifies SQLite integrity.
    - Returns current object counts.

    Most operations already persist transactionally. This endpoint is the
    explicit user-facing Save barrier.
    PRAGMA integrity_checkr   SELECT COUNT(*) FROM graphsSELECT COUNT(*) FROM events!SELECT COUNT(*) FROM api_registryNr#  )r#  sqlite_integrityrg  eventsapis)r.   r  r  r*   r'   r   rL   rG   )event_meta_syncr)   	integrityrq  event_count	api_counts         r   save_session_sqlr    s     35O	 	KK 89BBDQG	kk"?@IIKANkk"?@IIKAN		$GHQQSTUVI	  4%   	I		 	s*   A)C#"CC C#C  C##C,c                      t                t               5 } | j                  d       | j                  dt               f       | j	                          d d d        y # 1 sw Y   y xY w)Na\  
            CREATE TABLE IF NOT EXISTS persistence_audit(
                id INTEGER PRIMARY KEY CHECK(id = 1),
                revision INTEGER NOT NULL DEFAULT 0,
                dirty INTEGER NOT NULL DEFAULT 0,
                last_operation TEXT,
                last_status TEXT,
                last_error TEXT,
                sqlite_integrity TEXT,
                graph_count INTEGER DEFAULT 0,
                event_count INTEGER DEFAULT 0,
                folder_count INTEGER DEFAULT 0,
                api_count INTEGER DEFAULT 0,
                updated_at TEXT
            )
            z
            INSERT OR IGNORE INTO persistence_audit(
                id, revision, dirty, last_operation, last_status,
                sqlite_integrity, updated_at
            )
            VALUES (1, 0, 0, 'init', 'ok', 'unknown', ?)
            )r.   r*   r'   r   r-   r(   s    r   ensure_persistence_audit_schemar    s\    	 	
$ 	 YL		
 	

;  s   =AA$c                 f   | j                  d      j                         d   }| j                  d      j                         d   }	 | j                  d      j                         d   }	 | j                  d      j                         d   }||||fS # t        $ r d}Y 6w xY w# t        $ r d}Y $w xY w)Nr  r   r  zSELECT COUNT(*) FROM foldersr  )r'   r   rL   )r)   rq  r  folder_countr  s        r   persistence_counts_sqlr  5  s    ++;<EEGJK++;<EEGJK{{#ABKKMaPKK CDMMOPQR	 \9<<  
  	s$   "B )"B" BB"B0/B0c                    t                t               5 }|j                  d      j                         d   }t	        |      \  }}}}	|dk(  xr |dk(  }
t               }|j                  d|rdnd| |
rdndt        |xs d      |||||	|f
       |j                          d d d        
t        |      | |
rdndt        |xs d      	dS # 1 sw Y   1xY w)	Nr  r   r#  a  
            UPDATE persistence_audit
            SET revision = revision + 1,
                dirty = ?,
                last_operation = ?,
                last_status = ?,
                last_error = ?,
                sqlite_integrity = ?,
                graph_count = ?,
                event_count = ?,
                folder_count = ?,
                api_count = ?,
                updated_at = ?
            WHERE id = 1
            ro   r  rX   )r#  r*  	operationr+  r  r  rg  r  r   r  r   )	r  r*   r'   r   r  r   r=   r-   bool)r  r*  r+  r  r)   r  rq  r  r  r  r#  r   s               r   r-  r-  F  s   #%	 #KK 89BBDQG	<RSV<W9[,	$16T>Y  EKR !	
: 	

G#L e$'U[b!% K# #s   B
CCc                  :   t                t               5 } | j                  d      j                         d   }t	        |       \  }}}}| j                  d      j                         }|rt        |      ni }|dk(  xr |j                  dd      dk(  t        |j                  dd            |j                  dd      |j                  dd	      |j                  dd	      |j                  d
d	      ||||||j                  dd	      dcd d d        S # 1 sw Y   y xY w)Nr  r   z,SELECT * FROM persistence_audit WHERE id = 1r#  last_statusr*  revisionlast_operationrX   
last_errorr   )r#  r*  r  r  r  r  r  rg  r  r   r  r   )r  r*   r'   r   r  rZ   r[   r  )r)   r  rq  r  r  r  r   r$  s           r   persistence_status_sqlr  }  s   #%	 
KK 89BBDQG	<RSV<W9[,	kkHIRRTtCyR t#M(E(M$((7A./Q/"hh'7<88M26((<4 )!!#((<4

 
 
s   C2DDc           
         t                t        | xs i       }t        |j                  d      xs |j                  d      xs d      j	                         xs d}d|v r|d   }nd|v r|d   }nd|v r|d   }n|}t        |t              r	 t        j                  |      }t        |t              sd|i}t               }t               5 }|j                  d       |dk(  r~|j                  dg        |j                  d	i        |j                  d
i        |j                  dg        |j                  dg        |j                  dg       xs g D ]d  }t        |      j	                         }|st        ||      }	 |j                  d|j                  d
i       j                  |d      rdnd||f       f |j                  d	i       xs i j                         D ]  \  }}t        |      j	                         }t        |      j	                         xs d}|j                  d|f      j                         }	|	s`t        ||      }|j                  d|||f        |j                  d|t        j                   |dd      ||f       |j#                          ddd       t%        ddd       d|ddS # t        $ r d|i}Y &w xY w# t        $ r Y w xY w# 1 sw Y   AxY w)z
    Persist current UI folder state into SQLite.

    This is required for autosave of folders, graph-folder mappings,
    open/closed folder state, and deleted-row state.
    r_   	state_keyr   rR   r$  rI  rS   r   rL  ri  rJ  rK  z;UPDATE folders SET is_open = ?, updated_at = ? WHERE id = ?Tro   r   r   r   zAUPDATE graphs SET folder_id = ?, updated_at = ? WHERE row_key = ?a  
            INSERT INTO ui_state(key, value_json, created_at, updated_at)
            VALUES (?, ?, ?, ?)
            ON CONFLICT(key) DO UPDATE SET
                value_json=excluded.value_json,
                updated_at=excluded.updated_at
            FrR  Nzui-stater#  r)  )r#  r_   r4  )r.   rZ   r=   r[   rJ   rY   rV  rW  rL   r   r*   r'   r   r   rn  r   rY  r-   r-  )
r   r$  r_   rR   r   r)   r   r   r   r7   s
             r   save_ui_state_payload_sqlr    s    2D
dhhuoC+!6C)
D
J
J
L
YPYC$W	4V		\"%	#JJu%E eT"% 	B	 7%&)Y+]B/VR(]B/\2.$yyB7=2 !+.446"0kB	KKU#ii377TJPQSUW`a */=")E)K(R(R(T $g,,,.!+.446I/=J (* 
 0kB	WG,& 	 $**U$GRP		
 	

o7r Zq>sT22E  	#ENE	#< ! -7 7sD   J9 B3K?9K8C%K9K
	K
	KKKKK&c           	      
   t        | xs i       } 	 | j                  d      rt        d| j                  d      d       t                t	        ddd      S # t
        $ r"}t	        ddd	t        |      
      cY d}~S d}~ww xY w)z
    Autosave barrier.

    It first persists UI state when present, then verifies SQLite and regenerates
    the HTML. It does not fabricate application data.
    ui_stater   )r_   rR   autosaver   r#  r)  ro   r  )r*  r+  r  N)rZ   r[   r  rG   r-  rL   r=   )r   r  s     r   autosave_session_sqlr    s     7=b!G
;;z"%  Z0' 
 	'
!DII 
'c(	
 	

s   AA 	B A=7B=Bc                      t        i       S )zK
    Manual Save button uses the same real autosave verification path.
    )r  r   r   r   r  r    s      ##r   c                     t                dddddddddddddddd} t               5 }|j                  d      j                         D cg c]  }|d	   	 }}|sg cddd       S d
j	                  |      }|j                  d| d      j                         }g }|D ]  }t        |      }| j                         D ]  \  }}	|j                  ||	        d|vr+|j                  d	      xs |j                  d      xs d|d<   t        |j                  d            |d<   |j                  |        |j                  d        |cddd       S c c}w # 1 sw Y   yxY w)a2  
    Schema-tolerant API registry reader.

    It reads the actual api_registry columns present in SQLite and fills
    missing operational fields with safe defaults. This prevents /api/health
    from failing when older databases do not yet contain last_status,
    last_latency_ms, last_run_at, etc.
    Nr   unknownrX   TGET)r  last_latency_mslast_run_atlast_insertedlast_updatedlast_skippedr+  raw_jsonr   r   enabledmethodtarget_graphendpointr   PRAGMA table_info(api_registry)r   , zSELECT z FROM api_registryr   r  r   r  c                 z    t        | j                  d      xs d      t        | j                  d      xs d      fS )Nr   rX   r   )r=   r[   )r   s    r   <lambda>zlist_apis.<locals>.<lambda>P  s/    AEE&M$7R 8#aeeDk>OR:PQ r   )r_   )r.   r*   r'   rU  r  rZ   rn  r   r[   r  rp   sort)
wanted_defaultsr)   r  colsselect_colsr\  r  rt   r_   r^   s
             r   	list_apisr    s     O$ 
  [[!BCLLN
 fI
 

   iio{{k]"45

(* 	  	A7D / 5 5 7 .WW-. 4!XXf-N*1ENT
"488I#67DOJJt	 	QR; 
 s$   "E	EE$C#EEEc                    t                t        | xs i       }t        |j                  d      xs$ dt	        j
                         j                  d d        }t        |j                  d      xs |      }|j                  dd      rdnd}t        |j                  d	      xs |j                  d
      xs d      }t        |j                  d      xs d      j                         }t        |j                  d      xs d      }t        |j                  d      xs d      }t        |j                  d      xs d      }	||t        |      |||||	d|}
t               }t               5 }|j                  d       |j                  d||||||||	t        j                  |
dd      ||f       |rt        |||d       |j!                          d d d        |
S # 1 sw Y   |
S xY w)Nr   API_r  r   r  Tro   r   r  r   rX   r  r  r  interval_seconds,  r+  
registered)r   r   r  r  r  r  r  r+  r   a  
            INSERT INTO api_registry(id, name, enabled, target_graph, method, endpoint,
                                     interval_seconds, status, raw_json, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
                name=excluded.name,
                enabled=excluded.enabled,
                target_graph=excluded.target_graph,
                method=excluded.method,
                endpoint=excluded.endpoint,
                interval_seconds=excluded.interval_seconds,
                status=excluded.status,
                raw_json=excluded.raw_json,
                updated_at=excluded.updated_at
            FrR  r   )r.   rZ   r=   r[   r   r   hexr   r   r  r   r*   r'   rV  rY  r   r-   )r   r$  r  r   r  r  r  r  intervalr+  rS   r   r)   s                r   
upsert_apir  U  s   2D$AT$**,*:*:3B*?)@#ABFtxx)6*D88It,a!Gtxx/J488G3DJKL(#,u-335F488J'-2.H488./637H(#3|4F=$$
 
C 
B	 %& T7L&(HfZZ%4@"bJ!	
& \<Q

/0 J10 Js   /A%GG(c                      	 t               5 } | j                  d      j                         D cg c]  }|d   	 c}cd d d        S c c}w # 1 sw Y   y xY w# t        $ r g cY S w xY w)Nr  r   )r*   r'   rU  rL   )r)   r  s     r   api_registry_columnsr    sq    \ 	S %FGPPR &		 		 	
  	s=   
A "AAA	A AAA A A%$A%c                     | si S t        | t              r| S t        | t              r*	 t        j                  |       }t        |t              r|S i S i S # t
        $ r i cY S w xY wr0   )rY   rZ   r=   rV  rW  rL   )rR   parseds     r   parse_json_objectr    sg    	%%	ZZ&F'56=2= I  	I	s   &A A A! A!c                    i }t        | t        j                        rt        |       } t        | t              r<t	        | j                  d            }|j                  |       |j                  |        |j                  d|j                  d      xs |j                  d      xs d       |j                  d|j                  d             |j                  dd       |j                  dd	       |j                  d|j                  d
      xs d       |j                  d|j                  d      xs |j                  d      xs d       |j                  d|j                  d      xs d       |j                  dd       |j                  dd       |j                  dd       t        |j                  d            |d<   |S )z
    Normaliza una fila de api_registry mezclando columnas reales y raw_json.
    Esto permite soportar bases viejas y nuevas.
    r  r   r   r  r   r  Tr  r  r   rX   r  r   r   r+  r  r  r  Nr  r  )	rY   r"   r%   rZ   r  r[   rB  r   r  )r   rt   rS   s      r   normalize_api_rowr    sX   
 D#w{{#3i#t
 34CCOOD$((6*Kdhhz.BKeLOOFDHHTN+OOIt$OOHe$OOJ 526OONDHHW$5$N%$NBOOOHdhh}5BCOO%t,OOM2&OOL"%488I./DOKr   c           	      z   t                t        |       } t               }|sdddS t               }t	               5 }|j                  d       |j                  d| | f      j                         }|s|j                          dd| dcddd       S i }|j                         D ]  \  }}||v s|||<    d	|v r||d	<   |rfd
j                  |j                         D 	cg c]  }	|	 d	 c}	      }
t        |j                               |d   gz   }|j                  d|
 d|       d|v ret        d|j                         v r|d   nd      }|j                  |       ||d	<   |j                  dt        j                   |dd      |d   f       |j#                          ddd       d| ddS c c}	w # 1 sw Y   xY w)u   
    Actualiza estado runtime de una API de forma tolerante al esquema.
    Si algunas columnas no existen, las ignora.
    También intenta fusionar raw_json cuando existe.
    Fz!api_registry table has no columnsr#  r  r   zISELECT * FROM api_registry WHERE CAST(id AS TEXT) = ? OR name = ? LIMIT 1zapi not found)r#  r  r  Nr   r  z = ?r   zUPDATE api_registry SET z WHERE id = ?r  rX   z1UPDATE api_registry SET raw_json = ? WHERE id = ?TrR  )r#  r  r7  )r.   r=   r  r   r*   r'   r   r:  rn  r  r]   rq   r=  r  rB  rV  rY  r-   )r  fieldsr  r   r)   r   directr_   rR   r^  assignmentsparamsrS   s                r   set_api_statusr    s    [F!D&IJJ	B	 '%&kkWV
 (* 	
 LLN/VL' '  ,,. 	$JCd{#s	$ 4#%F< ))$GAs$Z$GHK&--/*c$i[8FKK*;-}E
 #zSXXZ7OC
OUWXCJJv "CKKCCetDc$iP
 	

O'R &T::' %H-' 's+   AF1F1-/F1F,
(B6F1,F11F:c                    ddd}| j                  d      xs( | j                  d      xs | j                  d      xs d}t        |      }|j                         D ]"  \  }}|s	|t        |      |t        |      <   $ |S )NzGRAFO-D-V55-API-SENSOR/1.0z!application/json, text/plain, */*)z
User-AgentAcceptheaders_jsonheadersrequest_headersrX   )r[   r  rn  r=   )r   r  raw_headersr  r_   rR   s         r   request_headers_for_apir     s    25G 	 	779	77$%	 	  {+Flln +
U5$ #E
GCH+ Nr   c                     t        | j                  d      xs( | j                  d      xs | j                  d      xs d      j                         S )Nr  r   urirX   )r=   r[   rJ   )r   s    r   endpoint_for_apir    sN    
 	775>	775>	 	
 egr   c                     t        | j                  d      xs d      j                         j                         }|dvry|S )Nr  r  )r  HEAD)r=   r[   r   rJ   )r   r  s     r   safe_api_methodr    s>    "+e,224::<F _$Mr   c                 :   t        |       } t        | j                  d      xs | j                  d      xs d      }t        | j                  d      xs |      }t        |       }||t	        | j                  d            t        | j                  d      xs( | j                  d      xs | j                  d      xs d      |t        |       d	d	d
d
t               d
d}| j                  d      sd	|d<   d	|d<   d|d<   |S |sd	|d<   d	|d<   d|d<   |S t        |      }|j                  dvrd|d<   d|d<   d|d<   |S t        j                         }	 t        |t        |       t        |             }t        ||      5 }	t        t        j                         |z
  dz        }
t        t        |	dd      xs |	j!                         xs d      }||d<   |
|d<   d|cxk  rdk  rn nd|d<   d|d<   d
|d<   n3d|cxk  rdk  rn nd|d<   d|d<   d| |d<   nd|d<   d|d<   d | |d<   d
d
d
       	 t)        ||d   |d   |d   |d$   |d   xs d|d$   |d   %       |S # 1 sw Y   8xY w# t"        $ rd}t        t        j                         |z
  dz        }
t        t        |d!d      xs d      |d<   |
|d<   d|d<   d|d<   d"|d    |d<   Y d
}~d
}~wt$        $ rU}t        t        j                         |z
  dz        }
|
|d<   d|d<   d|d<   t        t        |d#|            |d<   Y d
}~d
}~wt&        $ rK}t        t        j                         |z
  dz        }
|
|d<   d|d<   d|d<   t        |      |d<   Y d
}~Qd
}~ww xY w# t&        $ r Y |S w xY w)&z
    Verifica conectividad real de una API registrada.

    Estados:
    - active: HTTP 2xx
    - degraded: HTTP 3xx o respuesta rara pero alcanzable
    - inactive: timeout, error de red, HTTP 4xx/5xx
    - unknown: API deshabilitada o sin endpoint
    r   r   r   r  r  r   r   rX   r  N)r   r   r  r  r  r  r+  runtime_statushttp_status
latency_mslast_checkedr  r+  r  API disabledr  Missing endpointhttphttpsinactiveUnsupported endpoint schemer  r  r      r   r  r	     r  active  degradedzRedirect/status HTTP codez
HTTPError rf  r
  r+  r  r  r  r  r
  r  )r  r=   r[   r  r  r  r   r   schemer   perf_counterr
   r  r   r   getattrgetcoder   r   rL   r  )r   r!   r  r   r  r  r  startedreqreslatencyr  r  s                r   check_api_connectivityr#  )  s    C
 C:#''&/:U;Fswwv(&)D$H 	*+CGGN3_swww7G_377SX>_]_`!#&#	D 779"X!*&W"X!**WhF}}--#X!+5W!G/!+C0"3'
 S'* 	6c4,,.8D@AGgc8Q7M3;;=MANK"-D!(Dk'C'!)X)1%& $W)c)!+X)3%&"2;- @W!+X)3%&"'} 5W%	6T>]+ .^,G}*n- 01		
 Kq	6 	6(  ;t((*W4<=!'#vq"9">Q?]$\#X!+$T-%8$9:W 9t((*W4<=$\#X!+GC378W !t((*W4<=$\#X!+CW!$  Ks^   -I/ B+I#,I/ 5,N #I,(I/ /	N
8AKN
#AL33N
?A NN
	NNc           	      h   |syt        |      j                         }t        | j                  d      xs d      t        | j                  d      xs d      t        | j                  d      xs d      t        | j                  d      xs d      t        | j                  d      xs d      h}||v S )NTr  rX   r   r   r   r   )r=   rJ   r[   )r   r   ru   s      r   api_matches_graphr%    s    JE 	CGGN#)r*CGGG"#CGGEN b!CGGFO!r"CGGDMR J Jr   c                    t                t               D cg c]  }t        |       }}|D cg c]  }t        ||       r| }}|D cg c]  }t	        |       }}|sd}nAt        d |D              rd}n,t        d |D              rd}nt        d |D              rd}nd}d| |t               t        |      |d	S c c}w c c}w c c}w )
zk
    Endpoint backend para el sensor real:
    GET /api/apis/status
    GET /api/apis/status?graph=ISS
    r  c              3   ,   K   | ]  }|d    dk(    ywr+  r  Nr   rO  r   s     r   rQ  z!api_status_sql.<locals>.<genexpr>       81Qx[J&8   r  c              3   ,   K   | ]  }|d    dk(    ywr+  r  Nr   r)  s     r   rQ  z!api_status_sql.<locals>.<genexpr>  r*  r+  r  c              3   ,   K   | ]  }|d    dk(    ywr+  r  Nr   r)  s     r   rQ  z!api_status_sql.<locals>.<genexpr>       7!Qx[I%7r+  r  Tr#  r   r+  
checked_atcountr  r.   r  r  r%  r#  rX  r   rs   r   r   r  selectedresultsoveralls         r   api_status_sqlr9    s      ; 	#D  S%( 	H   	s#G 
 	88	8	88	8	7w7	7 iW 5

s   B>C
Cc                    t        |       } t        |       }|st        d      t        |      }|j                  dvrt        d      t        |t        |       t        |             }t        ||      5 }|j                  d      }ddd       j                  dd	
      }	 t        j                  |      S # 1 sw Y   2xY w# t        $ r d|icY S w xY w)z:
    Descarga JSON desde la API. Se usa por sync_api.
    zAPI endpoint is requiredr  r  r  r    i   Nutf-8r  )errorstext)r  r  r  r   r  r
   r  r  r   readdecoderV  rW  rL   )r   r!   r  r  r   r!  rS   r=  s           r   fetch_api_jsonr@    s     C
 C$H344hF}}--677
',s#C 
g	& $#hh{#$ ::gi:0Dzz$$ $  ~s   5B8#C 8CCCc                     t        | t              r| S t        | t              r/dD ]'  }| j                  |      }t        |t              s%|c S  | gS d| igS )zE
    Convierte respuestas comunes de APIs en una lista de items.
    )r  rn  r7  r$  r   rR   )rY   rq   rZ   r[   )r   r_   rv   s      r   payload_itemsrB    sa     '4 '4 D 	C++c"C#t$
	
 ygr   c                    t        |       } t        |t              sd|i}t        | j	                  d      xs; | j	                  d      xs( | j	                  d      xs | j	                  d      xs d      }t        | j	                  d      xs | j	                  d      xs d      }t        |      \  }}t        |dd	      }t        |      }|r|\  }	}
}}}| d
|	 }|
}n8t        t        |d| d|dz                }t        |xs t               dd       }t        | |      r#|r	d|d    }nd|v rd|j	                  d       }t        |||      }t        t        |dd	            }|s	 t        j                  |dd      }t        t        |dt        j                  t        j                  |dd      j!                  d            j#                         dd             }| j	                  d      xs | d| |||||||t        t        |d|            ||t%        |       ||dS # t        $ r t        |      }Y w xY w)u;   
    Mapeo tolerante API → evento canónico GRAFO_D.
    rR   r  r   r   r   APIr   )r   r   r   r   r   rO   rX   z
 position )r   r   r   r   z event ro   Nr  zISS position r   r   )r   r   r   summaryr=  messageFTrR  )r   r   r   r   r;     r"  )r   r   r  )r   r   r   r   r   r   r   r}   r   r   r   r   r   r   )r  rY   rZ   r=   r[   r{   r`   rV   r   r   r   rV  rY  rL   hashlibsha1encode	hexdigestr  )r   rt   indexr  r   ri   rk   raw_timer   r   r   rO   rP   rQ   r   
event_dater}   r   	source_ids                      r   event_from_api_itemrP  	  s    C
 CdD! 	777	775>	 776?	 L 3776?<cggdm<u=Ht$HCF
H x(E491iuc:l^4
8*GEAI;/
 3WYs^4
c4 "58*-DD "488K#8"9:Dc3'EW	
K 	$**TNK 3LL

4etDKKGTik#2	
I *(+1YK8='A8LM"(',   	$d)K	$s   /H, ,IIc                 V   t                t        |       } t               D cg c]  }t        |       }}d}|D ]@  }t        |j	                  d            | k(  st        |j	                  d            | k(  s>|} n |sd| ddS t        |t              }|d   d	vrd| |d
d
d
|j	                  d      xs ddS t        |t              }t        |      }d
}d
}d
}	t        |      D ]  \  }
}	 t        |||
      }t        |d      }|j	                  d      }|dk(  r|dz  }n|dk(  r|dz  }n|	dz  }		 t               5 }|j                  dt        |j	                  d      xs |       t        |j	                  d            t        |xs d      t               f       |j                          ddd        	 t#        | |||	dt                      t%                d| ||||	dS c c}w # 1 sw Y   =xY w# t         $ r Y Kw xY w# t         $ r	 |	dz  }	Y 1w xY w# t         $ r Y Sw xY w)u   
    Sincronización tolerante de una API registrada.

    - Verifica conectividad real.
    - Si responde, intenta leer JSON.
    - Normaliza items a eventos.
    - Inserta o actualiza eventos en SQLite.
    Nr   r   FzAPI not found)r#  r  r  r    r+  )r  r  r   r  zAPI not reachable)r#  r  r+  r8  r7  rk  r  )rL  Tr;  r9  r8  ro   r7  z
                        INSERT OR IGNORE INTO api_ingest_log(
                            api_id, event_legacy_id, action, created_at
                        )
                        VALUES (?, ?, ?, ?)
                        upsertr  )r  r  r  r+  r  )r#  r  r+  r8  r7  rk  )r.   r=   r  r  r[   r#  API_SYNC_TIMEOUT_SECONDSr@  rB  	enumeraterP  r?  r*   r'   r   r-   rL   r  rG   )r  r   r  r  r+  r   rn  r8  r7  rk  rL  rt   r   resultr9  r)   s                   r   sync_apirW  k	  s    [F ; 	#D 
 C 	y}}T"#v-Y]]65J1Kv1UC
 $
 	
 $C1IJFh55ZZ(?,?
 	
 S*BCG'"EHGG ' #t"	'T?E%e$?FZZ)F#A9$11\ !SKK   78		$0 2(3#I	 JJL!#J
"  		
   ul! !     	qLG	  sa   G'.AH6
G8 A0G,0G8;H ,G5	1G88	HHHHHH	H('H(c                  "   	 	 t               } t        j                         }| D ]  }|j                  d      s|j                  d      }|s)t        |j                  d      xs d      }|j                  d      }d}t	        |t        t
        f      r|t        |      z
  |k\  }|st        |d   ||j                  d      xs d	
       	 t        |d           	 t        j                  d       # t        $ r Y w xY w# t        $ r Y 0w xY w)NTr  r  r  r  last_run_epochr   r+  	scheduled)rY  r+  r   )
r  r   r[   r   rY   rK   r  rW  rL   sleep)r  nowr   r  r  last_rundues          r   api_poll_workerr_  	  s   
	;D))+C wwy)77:.sww'9:AcB77#34he5h/8;C"3t9SQYIZIi^ij T+( 	

11 ( %  		s<   B#D '$D C3D 3	C?<D >C??D 	DDc                  l    t         ry da t        j                  t        d      } | j	                          y )NTr@   )_api_worker_startedrC   rD   r_  rE   )ts    r   start_api_worker_oncerc  	  s)    =AGGIr   c                   D     e Zd ZddZd Z fdZd Zd Zd Zd Z	 xZ
S )	Handlerc                 6   t        j                  |dd      j                  d      }| j                  |       | j	                  dd       | j	                  dt        t        |                   | j                          | j                  j                  |       y )NFrn   )rS  indentr;  zContent-Typezapplication/json; charset=utf-8Content-Length)
rV  rY  rJ  send_responsesend_headerr=   rs   end_headerswfilewrite)selfobjr  r$  s       r   
_send_jsonzHandler._send_json
  su    zz#E!<CCGL4 )JK)3s4y>:

r   c                     t        | j                  j                  dd            }|r=t        j                  | j
                  j                  |      j                  d            S i S )Nrh  0r;  )r   r  r[   rV  rW  rfiler>  r?  )rn  r   s     r   _payloadzHandler._payload
  sL      !1378ABtzz$**//!,33G<=JJr   c           	         t        | j                        }|j                  }t        |j                        }	 |dv rt	               }| j                  |       y |dk(  r| j                  t                      y |dk(  rGd}|j                  d      r|j                  ddg      d   }t        |      }| j                  |       y t               }|dk(  r| j                  |       y |d	k(  rt               }| j                  |       y |d
k(  r"| j                  |j                  dg              y |dk(  r"| j                  |j                  dg              y |dk(  r"| j                  |j                  dg              y |dk(  r|j                  dg       D cg c]!  }t        |j                  d            dk7  r|# }}| j                  dt        t              t        |      t        t                     | j                         d       y 	 t         	| E         S c c}w # t        $ r(}| j                  dt        |      dd       Y d }~y d }~ww xY w)N)z/api/autosave/statusz/api/persistence/status	/api/apisz/api/apis/statusrX   r   r   )r   z/api/db/api/session/save/api/eventsr  /api/graphsrg  z/api/alertsalertsz/api/healthr   __AXIS__T)r#  dbr  r  r  Fr    )r   pathr	   queryr  rp  r  r[   r9  r1   r  r=   r$   rs   r  rL   superdo_GET)
rn  
parsed_urlr~  r  rV  r   r|  egraph_events	__class__s
            r   r  zHandler.do_GET
  s    dii(
))*9	JJ/1'{"	,))99W%!IIgt4Q7E'e4'By #**)+'}$x 45}$x 45}$x 45}$!vvh3 155<(J6     b'!,/	,(,(=(=(?!   %$ w~#   	OO53q6:C@	s[   H H 8AH  H % H &H -&H &H ;H &H:AH H 	I&I		Ic                     t               5 }|j                  d      j                         d   cd d d        S # 1 sw Y   y xY w)Nr  r   )r*   r'   r   )rn  r)   s     r   r  zHandler.sqlite_integrityS
  s=    \ 	GS;;78AACAF	G 	G 	Gs	   "7A c                    t        | j                        j                  }	 | j                         }|dk(  r't        |      }t	                | j                  |       y |dk(  r+t        |      }t	                | j                  ddi|       y |dk(  r-t        |d      }t	                | j                  ddi|       y |dk(  r't        |      }t	                | j                  |       y |d	k(  r't        |      }t	                | j                  |       y |d
k(  r t        |      }| j                  d|d       y |j                  d      rB|j                  d      r1|j                  d      d   }t        |      }| j                  |       y |dk(  rt	                | j                  ddd       y |dv r+t        dt!               v r|ni       }| j                  |       y | j                  dddd       y # t"        $ r9}dt%        |      v rdnd}| j                  dt%        |      d|       Y d }~y d }~wt&        $ r(}| j                  dt%        |      dd       Y d }~y d }~ww xY w)Nz/api/ui-staterx  r#  Tz/api/ingestFrR  z/api/foldersry  rv  r#  r   
/api/apis/z/sync/z/api/render)r#  rendered)z/api/autosave/saverw  r   unknown endpointr    	duplicatei  r  r}  )r   r~  rt  r  rG   rp  r'  r?  r  r  r  rN  endswithr  rW  r  localsr  r=   rL   )rn  r~  r   rV  r   r  r  r  s           r   do_POSTzHandler.do_POSTW
  s.   		"''=	AmmoG&27;!'}$09!t 6v 67}$)'F!t 6v 67~%*73!'}$)'2!'{" )tC 89|,w1GC,!&)'}$!t >?BB-fh9NgTVW'OO53EFL 	B%Q/3SDOO53q6:DAA 	AOO53q6:C@@	AsZ   ;G8 /G8 1G8 ?+G8 ++G8 $G8 <AG8 #G8 3.G8 "G8 8	I)/H55I)I$$I)c                 ^   t        | j                        j                  }	 | j                         }|j                  d      rX|j	                  dd      d   }t        ||      }|s| j                  dddd       y t                | j                  d	|d
       y |j                  d      r:|j	                  dd      d   }||d<   t        |      }| j                  d	|d       y | j                  dddd       y # t        $ r(}| j                  dt        |      dd       Y d }~y d }~ww xY w)N/api/events/r  ro   Fevent not foundr  r  Tr,  r  r   r  r  r}  )r   r~  rt  rN  rsplitrD  rp  rG   r  rL   r=   )rn  r~  r   r   r   r  r   r  s           r   do_PATCHzHandler.do_PATCH
  s   		"''	AmmoG~.;;sA.r2(7;OO5;L$MsS!te <=|,S!,R0 & )tC 89OO53EFL 	AOO53q6:C@@	As+   AC; ;C; A
C; %C; ;	D,D''D,c                 x   t        | j                        j                  }	 |j                  d      rU|j                  dd      d   }t	        |      s| j                  dddd       y t                | j                  d	|d
       y |j                  d      rIddlm}  ||j                  dd      d         }t        |g      }t                | j                  |       y |j                  d      r?|j                  dd      d   }t        |gd	      }t                | j                  |       y |j                  d      r|j                  dd      d   }t               5 }|j                  d       |j                  d|f      }	|j                  d|f      }
|j                          d d d        t                | j                  d	|
j                  	j                  d       y | j                  dddd       y # 1 sw Y   TxY w# t        $ r(}| j                  dt!        |      dd       Y d }~y d }~ww xY w)Nr  r  ro   r  Fr  r  r  T)r#  r[  z/api/folders/r   rc  z/api/graphs/)r  r  r   r}  r~  )r#  r[  r{  r|  r  r}  )r   r~  rN  r  rG  rp  rG   rl  rd  rw  r  r*   r'   r-   rF  rL   r=   )rn  r~  r   rd  r   rV  r   r  r)   cur_logsr   r  s               r   	do_DELETEzHandler.do_DELETE
  s   		"''6	A~.;;sA.r2'1OO5;L$MsS!t AB/0%dkk#q&9"&=>1;-@!'~.++c1-b1*G9TB!'|,S!,R0\ 
!SKK 12"{{E	 H ++?	C JJL
!  !%$'LL(0(9(9	!  OO53EFL+
! 
!.  	AOO53q6:C@@	AsQ   AH (H AH !AH 10H !AG<)<H &H <HH 	H9H44H9)r  )__name__
__module____qualname__rp  rt  r  r  r  r  r  __classcell__)r  s   @r   re  re  
  s/    K@ DG@ADA<9Ar   re  c                 p   t        |       } t        |       }|st        d      t        |      }|j                  dvrt        d      t        j                         }t        |t        |       t        |             }t        ||      5 }	 |j                  d       t        t        j                         |z
  dz        }t        t        |dd	      xs |j                         xs d	      }d
|cxk  xr dk  nc ||d
|cxk  rdk  rn nd nd| ddcd d d        S # t        $ r Y w xY w# 1 sw Y   y xY w)Nr  r  r  r  r    i   r  r+  r   r  r  r  urllibr#  r  r	  r  	transport)r  r  r  r   r  r   r  r
   r  r  r   r>  rL   r   r  r  )	r   r!   r  r  r  r   r!  r"  r  s	            r   _v55b_probe_with_urllibr  
  s6   
C
 C$H+,,hF}}--677!G
',s#C 
g	& 
#	HHTN t((*W4<='#x3Is{{}IJ *s*&! K5#5TU;-;P!

 
  			
 
s1   	D,DA7D,	D)&D,(D))D,,D5c                 6   t        |       } t        |       }|st        d      t        |      }|j                  dvrt        d      t        j                         }dddddd	d
dt        t        |            |g
}t        j                  |t        t              dd|dz         }t        t        j                         |z
  dz        }|j                  dk7  rH|j                  xs |j                  xs dj                         }t!        |xs d|j                         |j                  xs dj                         j#                         }	|	r!|	d   j%                         rt        |	d         nd}
d|
cxk  xr dk  nc |
|d|
cxk  rdk  rn nd ddS d|
 ddS )Nr  r  r  curlz-Lz-sSz-oz	/dev/nullz-wz%{http_code} %{time_total}z
--max-timeTr   )r4   r=  capture_outputr!   r  r   rX   zcurl returned r  r  r  r  )r  r  r  r   r  r   r  r=   r   r9   r:   r>   
returncodestderrstdoutrJ   RuntimeErrorr  isdigit)r   r!   r  r  r  cmdprocr"  errr  r  s              r   _v55b_probe_with_curlr    s   
C
 C$H+,,hF}}--677!G 	$CLC >>I!D 4$$&0D89G!{{/dkk/R6683DN4??2C"DEE[[B%%'--/E#(U1X-=-=-?#eAh-QK [&3&"1c1  :?{m7L r   c                    t        |       } t        | j                  d      xs | j                  d      xs d      }t        | j                  d      xs |      }t        |       }||t	        | j                  d            t        | j                  d      xs( | j                  d      xs | j                  d      xs d      |t        |       d	d	d
d
t               d
d
d}| j                  d      sd	|d<   d	|d<   d|d<   |S |sd	|d<   d	|d<   d|d<   |S g }d
}	 t        | |      }|r|j                  d      |d<   |j                  d      |d<   |j                  d      |d<   |j                  d      rd|d<   d|d<   d
|d<   nt        |j                  d      xs d      }
d|
cxk  rdk  r)n n&d|d<   d|d<   |j                  d      xs d|
 |d<   nHd|d<   d|d<   |j                  d      xs d|
 |d<   n"d|d<   d|d<   |rdj                  |      nd |d<   	 t        ||d   |d   |d   |d!   |d   xs d|d!   |d   "       |S # t        $ rj}|j                  dt        |      z          	 t        | |      }n3# t        $ r'}	|j                  dt        |	      z          Y d
}	~	nd
}	~	ww xY wY d
}~d
}~ww xY w# t        $ r Y |S w xY w)#z
    V55B robust real API connectivity check.
    Fixes false inactive state when urllib times out but curl succeeds locally.
    r   r   r   r  r  r   r   rX   r  N)r   r   r  r  r  r  r+  r  r  r	  r
  r  r  r+  r  r  r  r  r    zurllib: zcurl: r  r	  r  r#  r  r   r  r  r  r  r  z | zprobe failedr
  r  )r  r=   r[   r  r  r  r   r  rL   rp   r  r   r  r  )r   r!   r  r   r  r  r<  prober  exc2status_codes              r   r#  r#  R  s   
 C
 C:#''&/:U;Fswwv(&)D$H 	*+CGGN3_swww7G_377SX>_]_`!#&#	D  779"X!*&W"X!**WFE0'W= #ii6]"YY|4\!IIk2[99T?%DN%-D!" DMeii6;!<Kk'C'!+X)3%& %		' 2 Kk]6KW!+X)3%& %		' 2 Kk]6KW#X!+.4

6*.W>]+ .^,G}*n- 01		
 K_  0j3s8+,	0)#w?E 	0MM(SY.//	00X  KsT   I ",K 	KJ>7JJ>	J5J0+J>0J55J>>K	KKc                    t                t               D cg c]  }t        |       }}|D cg c]  }t        ||       r| }}|D cg c]  }t	        |d       }}|sd}nAt        d |D              rd}n,t        d |D              rd}nt        d |D              rd}nd	}d
| |t               t        |      |dS c c}w c c}w c c}w )z\
    V55B endpoint backend:
    GET /api/apis/status
    GET /api/apis/status?graph=ISS
    r   r    r  c              3   ,   K   | ]  }|d    dk(    ywr(  r   r)  s     r   rQ  z!api_status_sql.<locals>.<genexpr>  r*  r+  r  c              3   ,   K   | ]  }|d    dk(    ywr-  r   r)  s     r   rQ  z!api_status_sql.<locals>.<genexpr>  r*  r+  r  c              3   ,   K   | ]  }|d    dk(    ywr/  r   r)  s     r   rQ  z!api_status_sql.<locals>.<genexpr>  r0  r+  r  Tr1  r4  r5  s         r   r9  r9    s      ; 	#D  S%( 	H   	sB/G 
 	88	8	88	8	7w7	7 iW 5

s   C C
C
__main__ro   i="  )rF   z%GRAFO D API server: http://127.0.0.1:z1/outputs/timeline_excel_like.html?v=sqlite_serverz	127.0.0.1)F)rX   )Nr   )T)r  r   r#  rX   r0   )r   )r   )r   )grH  rV  r"   r9   r;   rC   r   r   r   r   pathlibr   http.serverr   r   rl  r   r	   urllib.requestr
   r   urllib.errorr   r   grafo_d_sqlite_adapterr   __file__resolveparentr>   r$   r6   ALLOWED_UI_KEYSrT  ra  Lockr8   r   r*   r.   r1   rG   rV   r`   r{   r   r   r,   r   r   r   r   r   r   r  r'  r5  r?  rD  rG  ra  rw  r  r  r  r  r  r  r  r  r  r-  r  r  r  r  r  r  r  r  r  r  r  r  r#  r%  r9  r@  rB  rP  rW  r_  rc  re  r  r  r  rs   argvr   portprintserve_foreverr   r   r   <module>r     si       
    '  E + + , 0H~&&	J22	&	&M  y~~>2
	;48	T
(#L
"
%R`8FqlWt.-b/d	 (XqhXv4.W(t=*@TvD4N&VD="4n
4Z3z
8$:|/h">9;x,tn")X !9 @ $Yxnh8lA& lAh#
L1h\~)\ z"388}q03sxx{dD$	1$7h
ijd+W5CCE r   