o
    Hý¤hs  ã                   @   s  d Z ddlZddlZddlZddlZddlZddlmZmZm	Z	m
Z
mZmZmZmZ ddl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mZm Z m!Z! ede"ƒZ#e# $d¡edd„ ƒƒZ%e#j$ddgdedd„ ƒƒZ&e#j$ddgdede'fdd„ƒƒZ(e#j$ddgdede'fdd„ƒƒZ)e# $d¡edd„ ƒƒZ*e# $d¡ede'fdd „ƒƒZ+e# $d!¡ede'fd"d#„ƒƒZ,e#j$d$dgded%d&„ ƒƒZ-e# $d'¡ede'd(e.fd)d*„ƒƒZ/e#j$d+dgdede'fd,d-„ƒƒZ0e#j$d.dgdede'fd/d0„ƒƒZ1e#j$d1dgdede'fd2d3„ƒƒZ2e#j$d4dgded5d6„ ƒƒZ3e# $d7¡ed8d9„ ƒƒZ4e# $d:¡ed;d<„ ƒƒZ5e# $d=¡ede'fd>d?„ƒƒZ6dS )@aæ  
admin_routes.py

Purpose:
  Admin panel HTML and actions for managing users. Includes add, remove, and
  change-password endpoints, protected by the admin_required decorator.
  Also exposes admin-only JSON APIs to view and manage any user's cameras and
  images, and to read audit logs. All routes are CSRF-protected globally.

Routes:
  - GET /admin
  - POST /admin/add_user
  - POST /admin/remove_user/<int:user_id>
  - POST /admin/change_password/<int:user_id>
  - GET  /admin/users.json
  - GET  /admin/user/<int:user_id>/cameras.json
  - GET  /admin/user/<int:user_id>/images.json
  - POST /admin/user/<int:user_id>/cameras/rename
  - POST /admin/user/<int:user_id>/cameras/delete
  - POST /admin/image/delete
  - GET  /admin/logs.json
é    N)Ú	BlueprintÚrender_templateÚrequestÚredirectÚurl_forÚflashÚsessionÚjsonifyé   )Úget_db)Úadmin_required)Úlog_admin_actionÚget_request_ip)ÚSTATIC_PATHÚUSER_PHOTOS_REAL)Úlatest_from_db_or_fsÚcollect_user_images)Úparse_ts_from_anyÚ	format_dtÚnormalize_to_static_user_photosÚbuild_media_urlÚbuild_share_urlÚadmin_routesz/adminc                  C   s    t ƒ } |  d¡ ¡ }td|dS )Nz_SELECT id, username, IFNULL(is_admin,0) AS is_admin FROM users ORDER BY username COLLATE NOCASEz
admin.html)Úusers)r   ÚexecuteÚfetchallr   )Údbr   © r   ú)/var/www/html/app_modules/admin_routes.pyÚadmin_panel)   s   r   z/admin/add_userÚPOST)Úmethodsc                  C   s2  t j d¡pd ¡  ¡ } t j d¡pd}| r|s#tddƒ ttdƒƒS t 	d| ¡s4tddƒ ttdƒƒS zAt
 | d	¡t
 ¡ ¡ d	¡}tƒ }| d
| |f¡ | ¡  tddƒ ztdt d¡d tt ƒd| › d W n	 tys   Y nw W n tjy„   tddƒ Y n tjy’   tddƒ Y nw ttdƒƒS )NÚusernameÚ Úpasswordu&   KorisniÄko ime i lozinka su obavezni.Úerrorúadmin_routes.admin_panelz[a-z0-9_]{3,32}uU   KorisniÄko ime smije sadrÅ¾avati samo mala slova, brojeve i donju crtu (3-32 znaka).úutf-8z9INSERT INTO users (username, password_hash) VALUES (?, ?)zKorisnik dodan.ÚsuccessÚadd_userÚuser_idú	username=©Úadmin_user_idÚtarget_user_idÚipÚdetailu   KorisniÄko ime veÄ‡ postoji.õ   GreÅ¡ka baze.)r   ÚformÚgetÚstripÚlowerr   r   r   ÚreÚ	fullmatchÚbcryptÚhashpwÚencodeÚgensaltÚdecoder   r   Úcommitr   r   r   Ú	ExceptionÚsqlite3ÚIntegrityErrorÚError)r"   r$   Úpwd_hashr   r   r   r   Úadmin_add_user1   s4   


&ÿ€ÿrC   z /admin/remove_user/<int:user_id>r*   c                 C   s–   z5t ƒ }| d| f¡ | d| f¡ | ¡  tddƒ ztdt d¡| ttƒd W n	 t	y3   Y nw W n t
jyD   tdd	ƒ Y nw ttd
ƒƒS )Nz%DELETE FROM cameras WHERE user_id = ?zDELETE FROM users WHERE id = ?zKorisnik i kamere obrisani.r(   Úremove_userr*   ©r-   r.   r/   r1   r%   r&   )r   r   r=   r   r   r   r3   r   r   r>   r?   rA   r   r   )r*   r   r   r   r   Úadmin_remove_userN   s    
ÿ€ÿrF   z$/admin/change_password/<int:user_id>c                 C   sp  t jpt j d¡p
d dd¡d  ¡ dk}|r)t jddpi }| d	¡p%d ¡ }n
t j d	¡p0d ¡ }t|ƒd
k rO|rDt	dddœƒdfS t
ddƒ ttdƒƒS zFt | d¡t ¡ ¡ d¡}tƒ }| d|| f¡ | ¡  ztdt d¡| tt ƒd W n	 ty…   Y nw |rt	ddiƒW S t
ddƒ W n tjy±   |rªt	dddœƒdf Y S t
ddƒ Y nw ttdƒƒS )z˜Change a user's password.
    - Supports form POST (HTML) and JSON (AJAX) with CSRF protection.
    - Enforces minimal password policy server-side.
    zContent-Typer#   ú;r
   r   úapplication/jsonT©ÚsilentÚnew_passwordé   Fz&Lozinka mora imati najmanje 8 znakova.©r(   Úmessageé  r%   r&   r'   z/UPDATE users SET password_hash = ? WHERE id = ?Úchange_passwordr*   rE   r(   zLozinka promijenjena.r1   éô  )r   Úis_jsonÚheadersr3   Úsplitr4   Úget_jsonr2   Úlenr	   r   r   r   r8   r9   r:   r;   r<   r   r   r=   r   r   r   r>   r?   rA   )r*   rR   ÚdatarK   Únew_hashr   r   r   r   Úadmin_change_password`   s>   "ÿ
ÿýrY   z/admin/users.jsonc               	   C   sf   t ƒ } |  d¡ ¡ }g }|D ]}| |d |d t|d pdƒdkt|d p'dƒdœ¡ qtd	|iƒS )
Na   
        SELECT u.id, u.username, IFNULL(u.is_admin,0) AS is_admin,
               COUNT(c.camera_id) AS camera_count
        FROM users u
        LEFT JOIN cameras c ON c.user_id = u.id
        GROUP BY u.id
        ORDER BY u.username COLLATE NOCASE
    Úidr"   Úis_adminr   r
   Úcamera_count)rZ   r"   r[   r\   r   )r   r   r   ÚappendÚintr	   )r   Úrowsr   Úrr   r   r   Úadmin_users_json   s   ù
üra   z&/admin/user/<int:user_id>/cameras.jsonc              
   C   sâ   t ƒ }dd„ | d¡ ¡ D ƒ}dt|ƒv }d|rdnd }| d|› d	| f¡}g }| ¡ D ]<}t|d
 ƒ}t||d p=dtƒ\}	}
d}|rJ|d nd pMd}| ||d |	rZt|	ƒnd|	rct	|	 
¡ ƒnd ||dœ¡ q.td|iƒS )Nc                 S   s   g | ]}|d  ‘qS )r
   r   )Ú.0Úrowr   r   r   Ú
<listcomp>¨   s    z+admin_user_cameras_json.<locals>.<listcomp>zPRAGMA table_info(cameras)Úmodelz"camera_id, camera_name, file_pathsz, modelr#   zSELECT zC FROM cameras WHERE user_id = ? ORDER BY camera_name COLLATE NOCASEÚ	camera_idÚ
file_pathsz/static/camera_render.pngzVision miniÚcamera_namezNema aktivnosti)rf   rh   Úlast_activeÚlatest_image_tsÚthumbnail_urlre   Úcameras)r   r   r   ÚsetÚstrr   r   r]   r   r^   Ú	timestampr	   )r*   r   ÚcolsÚ	has_modelÚselect_fieldsÚcurr_   r`   Úcam_idÚ	latest_dtÚ_Ú	thumb_urlre   r   r   r   Úadmin_user_cameras_json¤   s(   
úrx   z%/admin/user/<int:user_id>/images.jsonc              
   C   st  t ƒ }t| |tƒ}| d| f¡ ¡ }dd„ |D ƒ}i }|D ];}| d¡}|s'q||vr:|| |d|› ¡g dœ||< || d  |d |d	 || d
¡rSt| d
¡ƒnddœ¡ qtdt	t
j dd¡ƒƒ}tdtt	t
j dd¡ƒdƒƒ}	g }
| ¡ D ].\}}t|d ƒ}|d |||	 … }||	 |k }|
 ||d ||||r£||	 nd dœ¡ qz|
jdd„ d t|
t|
ƒdœƒS )NzQSELECT camera_id, camera_name FROM cameras WHERE user_id = ? ORDER BY camera_namec                 S   s   i | ]}t |d  ƒ|d “qS )rf   rh   ©rn   ©rb   r`   r   r   r   Ú
<dictcomp>Æ   ó    z*admin_user_images_json.<locals>.<dictcomp>rf   zCamera )rf   rh   Úimagesr}   ÚurlÚrelÚtsr#   ©r~   r   rf   r€   r   Úoffsetr
   Úlimité   éd   rh   )rf   rh   r}   Útotal_countÚhas_moreÚnext_offsetc                 S   ó   | d S )Nrh   r   ©Úxr   r   r   Ú<lambda>ï   ó    z(admin_user_images_json.<locals>.<lambda>)Úkey)rl   Útotal_cameras)r   r   r   r   r   r3   r]   r   Úmaxr^   r   ÚargsÚminÚitemsrV   Úsortr	   )r*   r   Ú
all_imagesÚcam_rowsÚcamera_namesrl   Úimgrt   r‚   rƒ   Úresult_camerasÚcam_dataÚtotal_imagesÚimages_slicer‡   r   r   r   Úadmin_user_images_json½   sP   

ý
ü
ú
þr   z/admin/share_linkc                  C   sÚ   t jddpi } tt|  d¡pdƒƒ}t|  d¡pdƒ}tdt|dƒƒ}|s/td	d
dœƒdfS t	|ƒ\}}|s@td	ddœƒdfS t
j t|¡}t
j |¡sVtd	ddœƒdfS tt ¡ ƒ|d  }t||ƒ}td||dœƒS )zÃGenerate a short-lived public share URL for an image.
    Admin-only endpoint to prevent user-level mass sharing without oversight.
    Body: { rel: 'User-photos/PICT_...', ttl_minutes: 30 }
    TrI   r   r#   Úttl_minutesiÀN  r
   i   FzNedostaje slika.rM   rO   úNeispravno ime datoteke.zDatoteka ne postoji.é”  é<   )r(   r~   Ú
expires_at)r   rU   r   rn   r3   r^   r   r’   r	   r   ÚosÚpathÚjoinr   ÚexistsÚtimer   )rW   r   rž   r€   ÚcamÚabs_pathÚexpr~   r   r   r   Úadmin_generate_share_link÷   s   
r«   z8/admin/user/<int:user_id>/camera/<camera_id>/images.jsonrf   c              
      s4  ˆ   ¡ r
tˆ ƒdkstddiƒdfS tƒ }| d| ˆ f¡ ¡ }|s)tddiƒdfS t| |tƒ}‡ fdd	„|D ƒ}td
t	t
j dd
¡ƒƒ}tdtt	t
j dd¡ƒdƒƒ}t|ƒ}|||| … }	|| |k }
g }|	D ]}| |d |d ˆ | d¡r€t| d¡ƒnddœ¡ qitˆ |d |||
|
r”|| nd |dœƒS )Né   r%   zInvalid camera IDrO   zCSELECT camera_name FROM cameras WHERE user_id = ? AND camera_id = ?zCamera not foundr    c                    s   g | ]}|  d ¡ˆ kr|‘qS ©rf   )r3   )rb   r˜   r­   r   r   rd   !  r|   z1admin_user_camera_images_json.<locals>.<listcomp>r   r‚   r
   rƒ   r„   r…   r~   r   r€   r#   r   rh   )rf   rh   r}   r†   r‡   rˆ   Úcurrent_offset)ÚisdigitrV   r	   r   r   Úfetchoner   r   r   r^   r   r‘   r3   r’   r]   r   )r*   rf   r   Ú	cam_checkr•   Úcamera_imagesr‚   rƒ   r›   rœ   r‡   Úresult_imagesr˜   r   r­   r   Úadmin_user_camera_images_json  s<   
üùr´   z%/admin/user/<int:user_id>/cameras/addc              
   C   sˆ  t jddpi }t| dd¡ƒ ¡ }| d¡pd ¡ }| ¡ r%t|ƒdks.tddd	œƒd
fS |r<t|ƒdks<t 	d|¡sEtddd	œƒd
fS t
ƒ }z_| d|f¡ ¡ }|r^tddd	œƒd
fW S | d| f¡ ¡ }|sstddd	œƒdfW S | d| ||f¡ | ¡  ztdt d¡| tt ƒd|› d|› d W n	 tyŸ   Y nw tddd	œƒW S  tjyÃ } ztddd	œƒdfW  Y d }~S d }~ww )NTrI   rf   r#   rh   r¬   FzID kamere mora biti 12 brojeva.rM   rO   r¡   ú[\w\s\-.]{1,60}úNeispravan naziv kamere.z-SELECT user_id FROM cameras WHERE camera_id=?u   Kamera s tim ID veÄ‡ postoji.zSELECT id FROM users WHERE id=?zKorisnik ne postoji.r    zi
            INSERT INTO cameras (user_id, camera_id, camera_name) 
            VALUES (?, ?, ?)
        Ú
add_camerar*   ú
camera_id=z, name=r,   u   Kamera je uspjeÅ¡no dodana.u   GreÅ¡ka baze podataka.rQ   )r   rU   rn   r3   r4   r¯   rV   r	   r6   r7   r   r   r°   r=   r   r   r   r>   r?   rA   )r*   rW   rf   rh   r   ÚexistingÚuser_existsÚer   r   r   Úadmin_user_camera_add@  s:   ý,ÿ€ÿr¼   z(/admin/user/<int:user_id>/cameras/renamec                 C   s8  t jddpi }t| dd¡ƒ ¡ }| d¡pd ¡ }| ¡ r%t|ƒdks.tddd	œƒd
fS |r<t|ƒdks<t 	d|¡sEtddd	œƒd
fS t
ƒ }z@| d|| |f¡}| ¡  |jdkretddd	œƒdfW S ztdt d¡| tt ƒd|› d W n	 ty   Y nw tddiƒW S  tjy›   tddd	œƒdf Y S w )NTrI   rf   r#   rh   r¬   FzNeispravni podaci.rM   rO   r¡   rµ   r¶   z@UPDATE cameras SET camera_name=? WHERE user_id=? AND camera_id=?r   õ   Kamera nije pronaÄ‘ena.r    Úrename_camerar*   r¸   r,   r(   r1   rQ   )r   rU   rn   r3   r4   r¯   rV   r	   r6   r7   r   r   r=   Úrowcountr   r   r   r>   r?   rA   )r*   rW   rf   Únew_namer   rs   r   r   r   Úadmin_user_camera_renamei  s,   
&ÿÿrÁ   z(/admin/user/<int:user_id>/cameras/deletec                 C   sö   t jddpi }t| dd¡ƒ ¡ }| ¡ rt|ƒdks%tdddœƒd	fS tƒ }z?| 	d
| |f¡}| 
¡  |jdkrDtdddœƒdfW S ztdt d¡| tt ƒd|› d W n	 ty`   Y nw tddiƒW S  tjyz   tdddœƒdf Y S w )NTrI   rf   r#   r¬   FzNeispravan ID kamere.rM   rO   z3DELETE FROM cameras WHERE user_id=? AND camera_id=?r   r½   r    Údelete_camerar*   r¸   r,   r(   r1   rQ   )r   rU   rn   r3   r4   r¯   rV   r	   r   r   r=   r¿   r   r   r   r>   r?   rA   )r*   rW   rf   r   rs   r   r   r   Úadmin_user_camera_delete‚  s&   
&ÿÿrÃ   z/admin/image/deletec                     sX  t jddpi } t|  dd¡ƒ‰t|  d¡pdƒ}ˆr|s&tddd	œƒd
fS tˆƒ\}}|s7tddd	œƒd
fS tƒ }| d||f¡ 	¡ }|sOtddd	œƒdfS t
j tˆ¡}ˆ d¡r]tnt
j t¡}t
j |¡}| |¡swtddd	œƒd
fS t
j |¡r¾t
j |¡}	t
 |	t
j¡s“tddd	œƒdfS zt
 |¡ W n# ty¬   tddd	œƒdf Y S  ty½   tddd	œƒdf Y S w z>| d||f¡ 	¡ }
|
rû|
d rûdd„ |
d  d¡D ƒ}t
j ˆ¡‰ ‡ ‡fdd„|D ƒ}| dd |¡||f¡ | ¡  W n tjy   Y nw ztdt d¡|tt ƒdˆ› d W n
 t y%   Y nw tddiƒS )NTrI   r   r#   r*   r   FzNedostaju podaci.rM   rO   rŸ   z5SELECT 1 FROM cameras WHERE user_id=? AND camera_id=?zNedozvoljeno.i“  zUser-photos/zPutanja nije dozvoljena.z)Server nema dozvolu za brisanje datoteke.zNe mogu obrisati datoteku.rQ   z>SELECT file_paths FROM cameras WHERE user_id=? AND camera_id=?rg   c                 S   s   g | ]
}|  ¡ r|  ¡ ‘qS r   )r4   ©rb   Úpr   r   r   rd   »  s    z&admin_delete_image.<locals>.<listcomp>ú,c                    s,   g | ]}t j |¡ˆ krt|ƒˆkr|‘qS r   )r£   r¤   Úbasenamer   rÄ   ©Ú	name_onlyr   r   r   rd   ½  s   , z?UPDATE cameras SET file_paths=? WHERE user_id=? AND camera_id=?Údelete_imagezrel=r,   r(   )!r   rU   r   r3   r^   r	   r   r   r   r°   r£   r¤   r¥   r   Ú
startswithr   Úrealpathr¦   ÚdirnameÚaccessÚW_OKÚremoveÚPermissionErrorÚOSErrorrT   rÇ   r=   r?   rA   r   r   r   r>   )rW   r*   rv   r¨   r   Úownerr©   Úreal_staticÚreal_absÚ
parent_dirrc   ÚfpsÚkeptr   rÈ   r   Úadmin_delete_image˜  s\   
ÿ€ÿ&ÿrÙ   z/admin/logs.jsonc            	      C   s   t dtttj dd¡ƒdƒƒ} tƒ }| d| f¡ ¡ }| d| f¡ ¡ }g }|D ]8}| 	|d d|d	 p5d
|d pAd|d p?d› |d |d pIdd|d  
¡ v sYd|d  
¡ v r[dnddœ¡ q(|D ]V}|d pod|d › }|d p€|d rd|d › nd}|d }|r|d › d|› }| 	|d d|d	 p›d
|||d p¢dd|d  
¡ v s²d |d  
¡ v r´d!nddœ¡ qc|jd"d#„ d$d% t|d | … t|ƒd&œƒS )'Nr
   rƒ   r…   rQ   ú‰
        SELECT ts, ip, user_id, username, event, detail, 'auth' as log_type
        FROM auth_log 
        ORDER BY ts DESC LIMIT ?
    án  
        SELECT aa.ts, aa.ip, aa.admin_user_id, aa.target_user_id, aa.action, aa.detail, 'admin' as log_type,
               au.username as admin_username, tu.username as target_username
        FROM admin_audit aa
        LEFT JOIN users au ON au.id = aa.admin_user_id
        LEFT JOIN users tu ON tu.id = aa.target_user_id
        ORDER BY aa.ts DESC LIMIT ?
    r€   ÚAUTHr/   ÚUnknownr"   úUser#r*   ú?Úeventr0   r#   ÚfailÚlockr%   Úinfo)r€   Útyper/   r"   Úactionr0   ÚseverityÚadmin_usernameúAdmin#r-   Útarget_usernamer.   rå   u    â†’ ÚADMINÚdeleterÐ   Úwarningc                 S   r‰   )Nr€   r   rŠ   r   r   r   rŒ     r   z!admin_logs_json.<locals>.<lambda>T©rŽ   Úreverse)Úlogsr†   )r   r’   r^   r   r‘   r3   r   r   r   r]   r5   r”   r	   rV   )	rƒ   r   Ú	auth_logsÚ
admin_logsÚall_logsÚlogÚ
admin_nameÚtarget_nameÚaction_descr   r   r   Úadmin_logs_jsonÉ  sV   üüùù


&
ù"

&
ù
þr÷   z/admin/logs/exportc                  C   sä  t j dd¡ ¡ } tdttt j dd¡ƒdƒƒ}tƒ }| d|f¡ 	¡ }| d|f¡ 	¡ }g }|D ]C}| 
|d	 |d	 rBt|d	 ƒnd
d|d pId|d pUd|d pSd› |d |d p]d
d|d  ¡ v smd|d  ¡ v rodnddœ¡ q1|D ]a}|d pƒd|d › }|d p”|d r“d|d › nd
}|d }	|r¤|d › d|› }	| 
|d	 |d	 r³t|d	 ƒnd
d |d pºd||	|d pÁd
d!|d  ¡ v sÑd"|d  ¡ v rÓd#nddœ¡ qw|jd$d%„ d&d' | d(krd)d*lm}
 d)d l}ttt ¡ ƒƒt|ƒ|d+œ}|
|j|d,d-d.d/d0d1tt ¡ ƒ› d2id3}|S d)d*lm}
 d)d l}d)d4lm} |ƒ }| |¡}| g d5¢¡ |D ] }| |d6 |d7 |d8 |d |d |d |d |d9 g¡ q9| d)¡ |
| ¡ d:d0d1tt ¡ ƒ› d;id3S )<NÚformatÚcsvr
   rƒ   iè  iˆ  rÚ   rÛ   r€   r#   rÜ   r/   rÝ   r"   rÞ   r*   rß   rà   r0   rá   râ   r%   rã   )ro   Údatetimerä   r/   r"   rå   r0   ræ   rç   rè   r-   ré   r.   rå   z -> rê   rë   rÐ   rì   c                 S   r‰   )Nro   r   rŠ   r   r   r   rŒ   G  r   z#admin_logs_export.<locals>.<lambda>Trí   Újsonr   )ÚResponse)Úexport_timestampÚ
total_logsrï   é   F)ÚindentÚensure_asciirH   zContent-Dispositionz attachment; filename=audit_logs_z.json)ÚmimetyperS   )ÚStringIO)Ú	TimestampÚDateTimeÚTypeÚIPÚUsernameÚActionÚDetailÚSeverityro   rú   rä   ræ   ztext/csvz.csv)r   r‘   r3   r5   r   r’   r^   r   r   r   r]   r   r”   Úflaskrü   rû   r§   rV   Údumpsrù   Úior  ÚwriterÚwriterowÚseekÚgetvalue)Úformat_typerƒ   r   rð   rñ   rò   ró   rô   rõ   rö   rü   rû   Úresponse_dataÚresponserù   r  Úoutputr  r   r   r   Úadmin_logs_export
  sœ   üüùù


&
ø"

&
ø
ýý

ø
ýr  z$/admin/user/<int:user_id>/stats.jsonc                  C   sÄ  t ƒ }| d| f¡ ¡ }|stddiƒdfS | d| f¡ ¡ }dd„ |D ƒ}t|ƒ}d}d}d }tj t	d	¡}	dd l
}
|
 d
¡}t |	¡D ]Œ\}}}|D ]„}| ¡ }| d¡sl| d¡sl| d¡sl| d¡sl| d¡slqLt|ƒ\}}|s| |¡}|r|d d d… D ]
}||v rŒ|} nq‚|rÐ||v rÐtj ||¡}zt |¡}W n	 tyª   Y qLw |t|jƒ7 }|d7 }|d u rÆddlm} | |j¡}|d u sÎ||k rÐ|}qLqE| dd| |d f¡ ¡ }| d| |d f¡ ¡ }| dd|d › f¡ ¡ }ddlm} |rt|d ƒn
|rt| ¡ ƒnd}|rt|d ƒnd}|r%|d ndp)d}d }d}|rC|d p<d|d › }t|d ƒ}t|d |d t|d  pQdƒdkd!œ||||||||d"œ	ƒS )#NzKSELECT id, username, IFNULL(is_admin,0) AS is_admin FROM users WHERE id = ?r%   z	not foundr    z/SELECT camera_id FROM cameras WHERE user_id = ?c                 S   s   h | ]}t |d  ƒ’qS r­   ry   rz   r   r   r   Ú	<setcomp>  s    z(admin_user_stats_json.<locals>.<setcomp>r   zUser-photosz[A-Za-z0-9]{12}z.jpgz.jpegz.pngz.gifz.webpéÿÿÿÿr
   )rú   zfSELECT ts, ip FROM auth_log WHERE event = ? AND (user_id = ? OR username = ?) ORDER BY ts DESC LIMIT 1Úlogin_successr"   zSSELECT ts FROM auth_log WHERE (user_id = ? OR username = ?) ORDER BY ts ASC LIMIT 1zû
        SELECT aa.ts, aa.admin_user_id, au.username as admin_username
        FROM admin_audit aa
        LEFT JOIN users au ON au.id = aa.admin_user_id
        WHERE aa.action = 'add_user' AND aa.detail LIKE ?
        ORDER BY aa.ts ASC LIMIT 1
    r+   r€   r/   r#   rç   zAdmin #r-   rZ   r[   )rZ   r"   r[   )	Úuserr\   Úimage_countÚtotal_bytesÚfirst_seen_tsÚlast_login_tsÚlast_login_ipÚcreated_by_usernameÚcreated_at_ts)r   r   r°   r	   r   rV   r£   r¤   r¥   r   r6   ÚcompileÚwalkr5   Úendswithr   ÚfindallÚstatrÒ   r^   Úst_sizerú   ÚfromtimestampÚst_mtimero   ) r*   r   Úur–   Úcam_idsr\   r  r  Úearliest_tsÚrootr6   Ú
cam_any_reÚdirpathrv   ÚfilesÚnameÚnlr€   r¨   ÚmÚtokenr©   Ústrú   ÚlastÚ
first_authÚcreation_infor  r  r   r!  r"  r   r   r   Úadmin_user_stats_jsonx  sŒ   
2
þÿ€çúú	($÷r:  )7Ú__doc__r£   r6   r?   r8   r§   r  r   r   r   r   r   r   r   r	   r   r   Úsecurityr   Úauditr   r   Úpathsr   r   Úimages_servicer   r   Úhelpersr   r   r   r   r   Ú__name__ÚbpÚrouter   rC   r^   rF   rY   ra   rx   r   r«   rn   r´   r¼   rÁ   rÃ   rÙ   r÷   r  r:  r   r   r   r   Ú<module>   s|    (
+8-'/?l