o
    L½i"R  ã                   @   s˜  d Z ddlZddlZddlZddlZddlmZmZmZm	Z	m
Z
mZmZmZ ddlmZmZmZmZmZmZ edeƒZeeeeeejjdœZdZdd	„ Zd
d„ Zdd„ Zdd„ Zdd„ Zdd„ Z dd„ Z!dd„ Z"dd„ Z#dd„ Z$e %d¡dd„ ƒZ&ej%dd d!gd"d#d$„ ƒZ'e %d%¡d&d'„ ƒZ(ej%d(d d!gd"d)d*„ ƒZ)d+d,„ Z*ej%d-d!gd"d.d/„ ƒZ+e %d0¡d1d2„ ƒZ,e %d3¡d4d5„ ƒZ-ej%d6d!gd"d7d8„ ƒZ.dS )9z™
OTA Update Provider: manage multiple Arduino-style projects and .bin firmware uploads.
Uses shared MySQL config (config.py); stores .bin files on disk.
é    N)Ú	BlueprintÚrequestÚredirectÚurl_forÚrender_templateÚ	send_fileÚflashÚResponse)Ú
MYSQL_HOSTÚ
MYSQL_PORTÚMYSQL_DATABASEÚ
MYSQL_USERÚMYSQL_PASSWORDÚOTA_FIRMWARE_DIRÚota)ÚhostÚportÚuserÚpasswordÚdatabaseÚcursorclassé   c              	   C   s@   | pd  ¡ } ztdd„ |  d¡D ƒƒW S  ttfy   Y dS w )z7Parse 'X.Y.Z' or 'X.Y' to tuple of ints for comparison.Ú c                 s   s     | ]}|  ¡ rt|ƒV  qd S )N)ÚstripÚint)Ú.0Úx© r   ú&/var/www/html/Server/blueprints/ota.pyÚ	<genexpr>&   s   € z parse_version.<locals>.<genexpr>Ú.)r   )r   ÚtupleÚsplitÚ
ValueErrorÚAttributeError)Úsr   r   r   Úparse_version"   s   ÿr&   c                 C   s|   t | ƒ}t |ƒ}ttt|ƒt|ƒƒƒD ](}|t|ƒk r|| nd}|t|ƒk r+|| nd}||k r4 dS ||kr; dS qdS )z7True if a is strictly less than b (e.g. 1.0.0 < 1.0.1).r   TF)r&   ÚrangeÚmaxÚlen)Úa_strÚb_strÚaÚbÚiÚaiÚbir   r   r   Úversion_less+   s   ÿr1   c                   C   s   t jdi t¤ŽS )Nr   )ÚpymysqlÚconnectÚOTA_DB_CONFIGr   r   r   r   Úget_db9   s   r5   c              	   C   s  |   ¡ q}| d¡ | d¡ | d¡ z| d¡ W n
 tjy%   Y nw z| d¡ W n
 tjy7   Y nw z| d¡ W n
 tjyI   Y nw z| d¡ W n
 tjy[   Y nw z| d¡ W n
 tjym   Y nw W d   ƒ n1 sxw   Y  |  ¡  d S )	Na  
            CREATE TABLE IF NOT EXISTS ota_projects (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                slug VARCHAR(255) NOT NULL UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        aÚ  
            CREATE TABLE IF NOT EXISTS ota_firmware (
                id INT AUTO_INCREMENT PRIMARY KEY,
                project_id INT NOT NULL,
                version VARCHAR(64) NOT NULL,
                original_filename VARCHAR(255) NOT NULL,
                stored_path VARCHAR(512) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES ota_projects(id) ON DELETE CASCADE
            )
        aü  
            CREATE TABLE IF NOT EXISTS ota_update_log (
                id INT AUTO_INCREMENT PRIMARY KEY,
                project_id INT NOT NULL,
                project_name VARCHAR(255) NULL,
                project_slug VARCHAR(255) NULL,
                version VARCHAR(64) NOT NULL,
                client_ip VARCHAR(64) NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES ota_projects(id) ON DELETE CASCADE
            )
        zDALTER TABLE ota_update_log ADD COLUMN project_name VARCHAR(255) NULLzDALTER TABLE ota_update_log ADD COLUMN project_slug VARCHAR(255) NULLz?ALTER TABLE ota_update_log ADD COLUMN node_id VARCHAR(255) NULLzAALTER TABLE ota_update_log ADD COLUMN node_name VARCHAR(255) NULLzCALTER TABLE ota_update_log ADD COLUMN from_version VARCHAR(64) NULL)ÚcursorÚexecuter2   ÚOperationalErrorÚcommit)ÚconnÚcurr   r   r   Úensure_ota_tables=   s@   



ÿÿÿÿÿ€Î4r<   c                 C   s:   | pd  ¡  ¡ }t dd|¡}t dd|¡  d¡}|pdS )zBLowercase, replace spaces with -, keep only alphanumeric and dash.r   z[^a-z0-9\-]ú-z-+Úproject)r   ÚlowerÚreÚsub)Únamer%   r   r   r   Úslugifyu   s   rC   c                 C   sf   t | ƒ}t|ƒdkr|d |d |d fS t|ƒdkr$|d |d dfS t|ƒdkr1|d ddfS dS )z]Return (major, minor, patch) with at least 3 components. E.g. 1.0.1 -> (1,0,1), 1 -> (1,0,0).é   r   é   r   )r   r   r   )r&   r)   )r%   Útr   r   r   Ú_parse_version_parts}   s   rG   c                 C   sŠ   | pd  ¡ sdS t| ƒ\}}}|d7 }|dkr!|› d|› d|› S d}|d7 }|dkr5|› d|› d|› S d}|d7 }|› d|› d|› S )z[Next semantic version: 1.0.1 -> 1.0.2; 1.0.9 -> 1.1.0; 1.9.9 -> 2.0.0. No version -> 1.0.0.r   z1.0.0rE   é	   r    r   )r   rG   )Úversion_strÚmajorÚminorÚpatchr   r   r   Únext_version_from‰   s   rM   c                 C   s€   |   ¡ }| d|f¡ | ¡ }W d  ƒ n1 sw   Y  dd„ |D ƒ}|s*dS |d }|dd… D ]	}t||ƒr=|}q4|S )zRReturn the latest (semantically greatest) version string for the project, or None.z6SELECT version FROM ota_firmware WHERE project_id = %sNc                 S   s0   g | ]}|  d ¡p
d ¡ r|  d ¡pd ¡ ‘qS )Úversionr   )Úgetr   )r   Úrr   r   r   Ú
<listcomp>¢   s   0 z*get_latest_version_str.<locals>.<listcomp>r   rE   )r6   r7   Úfetchallr1   )r:   Ú
project_idr;   ÚrowsÚversionsÚlatestÚvr   r   r   Úget_latest_version_strš   s    
þ
û
€rX   c                 C   s   t | |ƒ}t|ƒS )zrReturn next version string for project: last version + 1 (semantic: 1.0.1->1.0.2, 1.0.9->1.1.0), or 1.0.0 if none.)rX   rM   )r:   rS   rV   r   r   r   Únext_version¬   s   
rY   c                   C   s   t jtdd d S )NT©Úexist_ok)ÚosÚmakedirsr   r   r   r   r   Ú_ensure_firmware_dir²   s   r^   ú/c                  C   sî   t ƒ } znt| ƒ |  ¡ #}| d¡ | ¡ }|D ]}| d¡r'|d  ¡ |d< qW d  ƒ n1 s2w   Y  |  ¡ #}| d¡ | ¡ }|D ]}| d¡rV|d  ¡ |d< qGW d  ƒ n1 saw   Y  td||dW |  ¡  S |  ¡  w )zDashboard: list all projects.zASELECT id, name, slug, created_at FROM ota_projects ORDER BY nameÚ
created_atNa³  SELECT l.id, l.version, l.from_version, l.client_ip, l.node_id, l.node_name, l.created_at,
                          COALESCE(NULLIF(TRIM(l.project_name), ''), p.name) AS project_name,
                          COALESCE(NULLIF(TRIM(l.project_slug), ''), p.slug) AS project_slug
                   FROM ota_update_log l
                   JOIN ota_projects p ON l.project_id = p.id
                   ORDER BY l.created_at DESC LIMIT 50zota/index.html)ÚprojectsÚ
update_log)	r5   r<   r6   r7   rR   rO   Ú	isoformatr   Úclose)r:   r;   ra   Úprb   Úer   r   r   Úindex¶   s6   
ÿ
€þû
ÿ
€þörg   z/project/newÚGETÚPOST)Úmethodsc                  C   s  t jdkr	tdƒS t j d¡pd ¡ } | stdƒ tdƒdfS t| ƒ}tƒ }zVt	|ƒ | 
¡ 5}| d|f¡ | ¡ rUtd|› d	ƒ tdƒdfW  d
  ƒ W | ¡  S | d| |f¡ W d
  ƒ n1 sgw   Y  | ¡  ttd|dƒW | ¡  S | ¡  w )z$Create a new project (name -> slug).rh   zota/project_new.htmlrB   r   úProject name is required.é  z+SELECT id FROM ota_projects WHERE slug = %szA project with slug 'z*' already exists. Choose a different name.Nz5INSERT INTO ota_projects (name, slug) VALUES (%s, %s)úota.project_detail©Úslug)r   Úmethodr   ÚformrO   r   r   rC   r5   r<   r6   r7   Úfetchonerd   r9   r   r   )rB   ro   r:   r;   r   r   r   Úproject_newÖ   s2   


ü
ùþû	rs   z/project/<slug>c           	      C   sˆ  | pd  ¡ } tƒ }zµt|ƒ | ¡ }| d| f¡ | ¡ }W d  ƒ n1 s(w   Y  |s>tdƒ ttdƒƒW | 	¡  S | ¡ '}| d|d f¡ | 
¡ }|D ]}| d¡ra|d  ¡ |d< qRW d  ƒ n1 slw   Y  t||d ƒ}| ¡ -}| d	|d
 |d |d f¡ | 
¡ }|D ]}| d¡r¡|d  ¡ |d< q’W d  ƒ n1 s¬w   Y  td||||dW | 	¡  S | 	¡  w )zBProject detail: latest firmware row only, upload form, update log.r   ú7SELECT id, name, slug FROM ota_projects WHERE slug = %sNúProject not found.ú	ota.indexz…SELECT id, version, original_filename, created_at
                   FROM ota_firmware WHERE project_id = %s ORDER BY id DESC LIMIT 1Úidr`   aZ  SELECT id, version, from_version, client_ip, node_id, node_name, created_at,
                          COALESCE(NULLIF(TRIM(project_name), ''), %s) AS project_name,
                          COALESCE(NULLIF(TRIM(project_slug), ''), %s) AS project_slug
                   FROM ota_update_log WHERE project_id = %s ORDER BY created_at DESC LIMIT 30rB   ro   zota/project_detail.html)r>   ÚfirmwarerY   Úproject_update_log)r   r5   r<   r6   r7   rr   r   r   r   rd   rR   rO   rc   rY   r   )	ro   r:   r;   r>   rx   ÚfÚnext_verry   rf   r   r   r   Úproject_detailò   s`   
þ
û
 
áý
€þù

û
€þ÷ûr|   z/project/<slug>/editc           
   	   C   s–  | pd  ¡ } tƒ }z;t|ƒ | ¡ }| d| f¡ | ¡ }W d  ƒ n1 s)w   Y  |s?tdƒ ttdƒƒW | 	¡  S t
jdkrOtd|dW | 	¡  S t
j d	¡pVd  ¡ }tt
j d
¡pc|d
 ƒ}|sxtdƒ td|ddfW | 	¡  S || kr| ¡ 2}| d||d f¡ | ¡ r¬td|› dƒ td|ddfW  d  ƒ W | 	¡  S W d  ƒ n1 s¶w   Y  tj t| ¡}tj t|¡}tj |¡rÕt ||¡ | ¡ /}| d|d f¡ | ¡ D ]}tj |tj |d ¡¡}	| d|	|d f¡ qçW d  ƒ n	1 sw   Y  | ¡ }| d|||d f¡ W d  ƒ n	1 s,w   Y  | ¡  tdƒ ttd|dƒW | 	¡  S | 	¡  w )zEdit project name and slug.r   rt   Nru   rv   rh   zota/project_edit.html)r>   rB   ro   rk   rl   z8SELECT id FROM ota_projects WHERE slug = %s AND id != %srw   zSlug 'z%' is already used by another project.z>SELECT id, stored_path FROM ota_firmware WHERE project_id = %sÚstored_pathz6UPDATE ota_firmware SET stored_path = %s WHERE id = %sz:UPDATE ota_projects SET name = %s, slug = %s WHERE id = %szProject updated.rm   rn   )r   r5   r<   r6   r7   rr   r   r   r   rd   r   rp   r   rq   rO   rC   r\   ÚpathÚjoinr   ÚisdirÚrenamerR   Úbasenamer9   )
ro   r:   r;   r>   rB   Únew_slugÚold_dirÚnew_dirÚrowÚnew_pathr   r   r   Úproject_edit$  st   
þ
û
#
Þ
!à

å
ü
èþ
þþû
þÿrˆ   c                 C   s&   t j | pd¡} t dd| ¡} | pdS )z0Keep only safe characters for a stored filename.úfirmware.binú[^a-zA-Z0-9._\-]Ú_)r\   r~   r‚   r@   rA   )rB   r   r   r   Ú_sanitize_filenameY  s   rŒ   z/project/<slug>/uploadc                 C   s`  | pd  ¡ } tj d¡}tj d¡pd  ¡ }tƒ }zt|ƒ | ¡ }| d| f¡ | 	¡ }W d  ƒ n1 s9w   Y  |sOt
dƒ ttdƒƒW | ¡  S |rT|jset
dƒ ttd	| d
ƒW | ¡  S |j ¡  d¡s~t
dƒ ttd	| d
ƒW | ¡  S tjrŸtjtd d krŸt
dt› dƒ ttd	| d
ƒW | ¡  S |r£|nt||d ƒ}tƒ  tj t| ¡}tj|dd t dd|¡}t|jƒ}	|› d|	› }
|
 ¡  d¡sÙ|
d7 }
tj ||
¡}| |¡ tj | |
¡}| ¡ }| d|d ||j|f¡ W d  ƒ n	1 s	w   Y  | ¡  t
d|j› d|› dƒ ttd	| d
ƒW | ¡  S | ¡  w )zEUpload a .bin file; optional version override. A file must be chosen.r   ÚfilerN   rt   Nru   rv   z$Please choose a .bin file to upload.rm   rn   z.binzOnly .bin files are allowed.i   zFile too large (max z MB).rw   TrZ   rŠ   r‹   zyINSERT INTO ota_firmware (project_id, version, original_filename, stored_path)
                   VALUES (%s, %s, %s, %s)z	Uploaded z as version r    )r   r   ÚfilesrO   rq   r5   r<   r6   r7   rr   r   r   r   rd   Úfilenamer?   ÚendswithÚcontent_lengthÚMAX_UPLOAD_MBrY   r^   r\   r~   r   r   r]   r@   rA   rŒ   Úsaver9   )ro   r   Úversion_overrider:   r;   r>   rN   Úproject_dirÚsafe_versionÚoriginal_safeÚstored_namer}   Úrel_pathr   r   r   Úupload`  sf   
þ
û
 
á
ä
ç
ê


ýÿrš   z/project/<slug>/updatec                 C   sz  | pd  ¡ } tj d¡pd  ¡ }|stddS tƒ }z| ¡ }| d| f¡ | ¡ }W d  ƒ n1 s6w   Y  |sHtdddW | 	¡  S | ¡ }| d	|d
 f¡ | 
¡ }W d  ƒ n1 sdw   Y  |svtdddW | 	¡  S |d }|dd… D ]}t|d |d ƒr|}q€| d¡p”d  ¡ }tj t|d ¡}	tj |	¡s±tdddW | 	¡  S t||ƒr.tjp»d  ¡ p¿d}
tj d¡pÇd  ¡ pËd}tj d¡pÓd  ¡ p×d}| ¡ 8}| d|d
 ||
f¡ | ¡ du r| d|d
 | d¡pûd| d¡pd||pd|
||f¡ W d  ƒ n	1 sw   Y  | ¡  t|	ddddW | 	¡  S tddW | 	¡  S | 	¡  w )zaESP32 OTA: GET with header x-esp32-version. Return 200+bin if server has newer, else 304, or 404.r   zx-esp32-versioni0  )Ústatusrt   NzNo project or firmwarei”  zkSELECT id, version, stored_path FROM ota_firmware
                   WHERE project_id = %s ORDER BY id DESCrw   r   rE   rN   r}   zx-esp32-node-idzx-esp32-node-namezÒSELECT 1 FROM ota_update_log
                       WHERE project_id = %s AND version = %s AND (client_ip <=> %s)
                       AND created_at > NOW() - INTERVAL 2 MINUTE
                       LIMIT 1z¼INSERT INTO ota_update_log (project_id, project_name, project_slug, version, from_version, client_ip, node_id, node_name)
                           VALUES (%s, %s, %s, %s, %s, %s, %s, %s)rB   ro   úapplication/octet-streamTr‰   )ÚmimetypeÚas_attachmentÚdownload_name)r   r   ÚheadersrO   r	   r5   r6   r7   rr   rd   rR   r1   r\   r~   r   r   ÚisfileÚremote_addrr9   r   )ro   Údevice_versionr:   r;   r>   Úfirmware_rowsrV   r†   Úserver_versionÚ	full_pathÚ	client_ipÚnode_idÚ	node_namer   r   r   Úproject_update”  s|   

þ
û
/
Òý
ú
&Ü€
ä
û2ý€÷ü

þrª   z*/project/<slug>/firmware/<int:firmware_id>c                 C   sæ   | pd  ¡ } tƒ }zd| ¡ }| d| |f¡ | ¡ }W d  ƒ n1 s%w   Y  |s;tdƒ ttdƒƒW | ¡  S t	j
 t|d ¡}t	j
 |¡s[tdƒ ttd| d	ƒW | ¡  S t|d
t	j
 |d ¡ddW | ¡  S | ¡  w )z+Download the .bin file for Arduino clients.r   úºSELECT f.id, f.stored_path, p.slug
                   FROM ota_firmware f
                   JOIN ota_projects p ON f.project_id = p.id
                   WHERE p.slug = %s AND f.id = %sNúFirmware not found.rv   r}   zFile no longer exists on disk.rm   rn   Trœ   )rž   rŸ   r   )r   r5   r6   r7   rr   r   r   r   rd   r\   r~   r   r   r¡   r   r‚   ©ro   Úfirmware_idr:   r;   r†   r¦   r   r   r   Údownload_firmware×  s4   
û
ø	
õ
ùür¯   z1/project/<slug>/firmware/<int:firmware_id>/deletec                 C   s$  | pd  ¡ } tƒ }zƒ| ¡ }| d| |f¡ | ¡ }W d  ƒ n1 s%w   Y  |s=tdƒ ttd| dƒW | ¡  S t	j
 t|d ¡}| ¡ }| d|f¡ W d  ƒ n1 s\w   Y  | ¡  t	j
 |¡r|zt	 |¡ W n	 ty{   Y nw td	ƒ ttd| dƒW | ¡  S | ¡  w )
z0Delete a firmware record and its file from disk.r   r«   Nr¬   rm   rn   r}   z&DELETE FROM ota_firmware WHERE id = %szFirmware deleted.)r   r5   r6   r7   rr   r   r   r   rd   r\   r~   r   r   r9   r¡   ÚremoveÚOSErrorr­   r   r   r   Údelete_firmware÷  s8   
û
ø	
ô
ÿÿr²   )/Ú__doc__r\   r@   Úshutilr2   Úflaskr   r   r   r   r   r   r   r	   Úconfigr
   r   r   r   r   r   Ú__name__ÚbpÚcursorsÚ
DictCursorr4   r’   r&   r1   r5   r<   rC   rG   rM   rX   rY   r^   Úrouterg   rs   r|   rˆ   rŒ   rš   rª   r¯   r²   r   r   r   r   Ú<module>   sV    ( 
	ú		8


1
4
3
B
