Coverage for ivatar/views.py: 48%

1# -*- coding: utf-8 -*- 

2""" 

3views under / 

4""" 

5 

6import contextlib 

7from io import BytesIO 

8from os import path 

9import hashlib 

10import logging 

11import threading 

12from ivatar.utils import urlopen, Bluesky 

13from urllib.error import HTTPError, URLError 

14from ssl import SSLError 

15from django.views.generic.base import TemplateView, View 

16from django.http import HttpResponse, HttpResponseRedirect 

17from django.http import HttpResponseNotFound, JsonResponse 

18from django.core.exceptions import ObjectDoesNotExist 

19from django.core.cache import cache, caches 

20from django.utils.translation import gettext_lazy as _ 

21from django.urls import reverse_lazy 

22from django.db.models import Q 

23from django.contrib.auth.models import User 

24 

25from PIL import Image 

26 

27from monsterid.id import build_monster as BuildMonster 

28import Identicon 

29from pydenticon5 import Pydenticon5 

30import pagan 

31from robohash import Robohash 

32 

33from ivatar.settings import AVATAR_MAX_SIZE, JPEG_QUALITY, DEFAULT_AVATAR_SIZE 

34from ivatar.settings import CACHE_RESPONSE 

35from ivatar.settings import CACHE_IMAGES_MAX_AGE 

36from ivatar.settings import TRUSTED_DEFAULT_URLS 

37from .ivataraccount.models import ConfirmedEmail, ConfirmedOpenId 

38from .ivataraccount.models import UnconfirmedEmail, UnconfirmedOpenId 

39from .ivataraccount.models import Photo 

40from .ivataraccount.models import pil_format, file_format 

41from .utils import is_trusted_url, mm_ng, resize_animated_gif 

42 

43# Import OpenTelemetry (always enabled, export controlled by OTEL_EXPORT_ENABLED) 

44try: 

45 from .opentelemetry_middleware import trace_avatar_operation, get_avatar_metrics 

46 

47 avatar_metrics = get_avatar_metrics() 

48except ImportError: 

49 # OpenTelemetry packages not installed 

50 def trace_avatar_operation(operation_name): 

51 def decorator(func): 

52 return func 

53 

54 return decorator 

55 

56 class NoOpMetrics: 

57 def record_avatar_generated(self, *args, **kwargs): 

58 pass 

59 

60 def record_cache_hit(self, *args, **kwargs): 

61 pass 

62 

63 def record_cache_miss(self, *args, **kwargs): 

64 pass 

65 

66 def record_external_request(self, *args, **kwargs): 

67 pass 

68 

69 def record_file_upload(self, *args, **kwargs): 

70 pass 

71 

72 avatar_metrics = NoOpMetrics() 

73 

74# Initialize loggers 

75logger = logging.getLogger("ivatar") 

76security_logger = logging.getLogger("ivatar.security") 

77 

78 

79def get_size(request, size=DEFAULT_AVATAR_SIZE): 

80 """ 

81 Get size from the URL arguments 

82 """ 

83 sizetemp = None 

84 if "s" in request.GET: 

85 sizetemp = request.GET["s"] 

86 if "size" in request.GET: 

87 sizetemp = request.GET["size"] 

88 if sizetemp: 

89 if sizetemp not in ["", "0"]: 

90 with contextlib.suppress(ValueError): 

91 if int(sizetemp) > 0: 

92 size = int(sizetemp) 

93 size = min(size, int(AVATAR_MAX_SIZE)) 

94 return size 

95 

96 

97class CachingHttpResponse(HttpResponse): 

98 """ 

99 Handle caching of response 

100 """ 

101 

102 def __init__( 

103 self, 

104 uri, 

105 content=b"", 

106 content_type=None, 

107 status=200, # pylint: disable=too-many-arguments 

108 reason=None, 

109 charset=None, 

110 ): 

111 if CACHE_RESPONSE: 

112 caches["filesystem"].set( 

113 uri, 

114 { 

115 "content": content, 

116 "content_type": content_type, 

117 "status": status, 

118 "reason": reason, 

119 "charset": charset, 

120 }, 

121 ) 

122 super().__init__(content, content_type, status, reason, charset) 

123 

124 

125class AvatarImageView(TemplateView): 

126 """ 

127 View to return (binary) image, based on OpenID/Email (both by digest) 

128 """ 

129 

130 # TODO: Do cache resize images!! Memcached? 

131 

132 def options(self, request, *args, **kwargs): 

133 response = HttpResponse("", content_type="text/plain") 

134 response["Allow"] = "404 mm mp retro pagan wavatar monsterid robohash identicon" 

135 return response 

136 

137 def get( 

138 self, request, *args, **kwargs 

139 ): # pylint: disable=too-many-branches,too-many-statements,too-many-locals,too-many-return-statements 

140 """ 

141 Override get from parent class 

142 """ 

143 model = ConfirmedEmail 

144 size = get_size(request) 

145 imgformat = "png" 

146 obj = None 

147 default = None 

148 forcedefault = False 

149 gravatarredirect = False 

150 gravatarproxy = True 

151 uri = request.build_absolute_uri() 

152 

153 # Check the cache first 

154 if CACHE_RESPONSE: 

155 if centry := caches["filesystem"].get(uri): 

156 # Record cache hit 

157 avatar_metrics.record_cache_hit(size=str(size), format_type=imgformat) 

158 # For DEBUG purpose only 

159 # print('Cached entry for %s' % uri) 

160 return HttpResponse( 

161 centry["content"], 

162 content_type=centry["content_type"], 

163 status=centry["status"], 

164 reason=centry["reason"], 

165 charset=centry["charset"], 

166 ) 

167 else: 

168 # Record cache miss 

169 avatar_metrics.record_cache_miss(size=str(size), format_type=imgformat) 

170 

171 # In case no digest at all is provided, return to home page 

172 if "digest" not in kwargs: 

173 return HttpResponseRedirect(reverse_lazy("home")) 

174 

175 if "d" in request.GET: 

176 default = request.GET["d"] 

177 if "default" in request.GET: 

178 default = request.GET["default"] 

179 

180 if default is not None: 

181 if TRUSTED_DEFAULT_URLS is None: 

182 logger.warning("Query parameter `default` is disabled.") 

183 default = None 

184 elif default.find("://") > 0: 

185 # Check if it's trusted, if not, reset to None 

186 trusted_url = is_trusted_url(default, TRUSTED_DEFAULT_URLS) 

187 

188 if not trusted_url: 

189 security_logger.warning( 

190 f"Default URL is not in trusted URLs: '{default}'; Kicking it!" 

191 ) 

192 default = None 

193 

194 if "f" in request.GET: 

195 if request.GET["f"] == "y": 

196 forcedefault = True 

197 if "forcedefault" in request.GET: 

198 if request.GET["forcedefault"] == "y": 

199 forcedefault = True 

200 

201 if "gravatarredirect" in request.GET: 

202 if request.GET["gravatarredirect"] == "y": 

203 gravatarredirect = True 

204 

205 if "gravatarproxy" in request.GET: 

206 if request.GET["gravatarproxy"] == "n": 

207 gravatarproxy = False 

208 

209 try: 

210 obj = model.objects.get(digest=kwargs["digest"]) 

211 except ObjectDoesNotExist: 

212 try: 

213 obj = model.objects.get(digest_sha256=kwargs["digest"]) 

214 except ObjectDoesNotExist: 

215 model = ConfirmedOpenId 

216 with contextlib.suppress(Exception): 

217 d = kwargs["digest"] # pylint: disable=invalid-name 

218 # OpenID is tricky. http vs. https, versus trailing slash or not 

219 # However, some users eventually have added their variations already 

220 # and therefore we need to use filter() and first() 

221 obj = model.objects.filter( 

222 Q(digest=d) 

223 | Q(alt_digest1=d) 

224 | Q(alt_digest2=d) 

225 | Q(alt_digest3=d) 

226 ).first() 

227 # Handle the special case of Bluesky 

228 if obj: 

229 if obj.bluesky_handle: 

230 return HttpResponseRedirect( 

231 reverse_lazy("blueskyproxy", args=[kwargs["digest"]]) 

232 ) 

233 # If that mail/openid doesn't exist, or has no photo linked to it 

234 if not obj or not obj.photo or forcedefault: 

235 gravatar_url = ( 

236 "https://secure.gravatar.com/avatar/" 

237 + kwargs["digest"] 

238 + "?s=%i" % size 

239 ) 

240 

241 # If we have redirection to Gravatar enabled, this overrides all 

242 # default= settings, except forcedefault! 

243 if gravatarredirect and not forcedefault: 

244 return HttpResponseRedirect(gravatar_url) 

245 

246 # Request to proxy Gravatar image - only if not forcedefault 

247 if gravatarproxy and not forcedefault: 

248 url = ( 

249 reverse_lazy("gravatarproxy", args=[kwargs["digest"]]) 

250 + "?s=%i" % size 

251 ) 

252 # Ensure we do not convert None to string 'None' 

253 if default: 

254 url += f"&default={default}" 

255 return HttpResponseRedirect(url) 

256 

257 # Return the default URL, as specified, or 404 Not Found, if default=404 

258 if default: 

259 # Proxy to gravatar to generate wavatar - lazy me 

260 if str(default) == "wavatar": 

261 url = ( 

262 reverse_lazy("gravatarproxy", args=[kwargs["digest"]]) 

263 + "?s=%i" % size 

264 + f"&default={default}&f=y" 

265 ) 

266 return HttpResponseRedirect(url) 

267 

268 if str(default) == str(404): 

269 return HttpResponseNotFound(_("<h1>Image not found</h1>")) 

270 

271 if str(default) == "monsterid": 

272 monsterdata = BuildMonster(seed=kwargs["digest"], size=(size, size)) 

273 data = BytesIO() 

274 return self._return_cached_png(monsterdata, data, uri) 

275 if str(default) == "robohash": 

276 roboset = request.GET.get("robohash") or "any" 

277 robohash = Robohash(kwargs["digest"]) 

278 robohash.assemble(roboset=roboset, sizex=size, sizey=size) 

279 data = BytesIO() 

280 robohash.img.save(data, format="png") 

281 return self._return_cached_response(data, uri) 

282 if str(default) == "retro": 

283 identicon = Identicon.render(kwargs["digest"]) 

284 data = BytesIO() 

285 img = Image.open(BytesIO(identicon)) 

286 img = img.resize((size, size), Image.LANCZOS) 

287 return self._return_cached_png(img, data, uri) 

288 if str(default) == "pagan": 

289 paganobj = pagan.Avatar(kwargs["digest"]) 

290 data = BytesIO() 

291 img = paganobj.img.resize((size, size), Image.LANCZOS) 

292 return self._return_cached_png(img, data, uri) 

293 if str(default) == "identicon": 

294 p = Pydenticon5() # pylint: disable=invalid-name 

295 # In order to make use of the whole 32 bytes digest, we need to redigest them. 

296 newdigest = hashlib.md5( 

297 bytes(kwargs["digest"], "utf-8") 

298 ).hexdigest() 

299 img = p.draw(newdigest, size, 0) 

300 data = BytesIO() 

301 return self._return_cached_png(img, data, uri) 

302 if str(default) == "mmng": 

303 mmngimg = mm_ng(idhash=kwargs["digest"], size=size) 

304 data = BytesIO() 

305 return self._return_cached_png(mmngimg, data, uri) 

306 if str(default) in {"mm", "mp"}: 

307 return self._redirect_static_w_size("mm", size) 

308 return HttpResponseRedirect(default) 

309 

310 return self._redirect_static_w_size("nobody", size) 

311 imgformat = obj.photo.format 

312 photodata = Image.open(BytesIO(obj.photo.data)) 

313 

314 data = BytesIO() 

315 

316 # Animated GIFs need additional handling 

317 if imgformat == "gif" and photodata.is_animated: 

318 # Debug only 

319 # print("Object is animated and has %i frames" % photodata.n_frames) 

320 data = resize_animated_gif(photodata, (size, size)) 

321 else: 

322 # If the image is smaller than what was requested, we need 

323 # to use the function resize 

324 if photodata.size[0] < size or photodata.size[1] < size: 

325 photodata = photodata.resize((size, size), Image.LANCZOS) 

326 else: 

327 photodata.thumbnail((size, size), Image.LANCZOS) 

328 photodata.save(data, pil_format(imgformat), quality=JPEG_QUALITY) 

329 

330 data.seek(0) 

331 obj.photo.access_count += 1 

332 obj.photo.save() 

333 obj.access_count += 1 

334 obj.save() 

335 if imgformat == "jpg": 

336 imgformat = "jpeg" 

337 

338 # Record avatar generation metrics 

339 avatar_metrics.record_avatar_generated( 

340 size=str(size), 

341 format_type=imgformat, 

342 source="uploaded" if obj else "generated", 

343 ) 

344 

345 response = CachingHttpResponse(uri, data, content_type=f"image/{imgformat}") 

346 response["Cache-Control"] = "max-age=%i" % CACHE_IMAGES_MAX_AGE 

347 # Remove Vary header for images since language doesn't matter 

348 response["Vary"] = "" 

349 return response 

350 

351 def _redirect_static_w_size(self, arg0, size): 

352 """ 

353 Helper method to redirect to static image with size i/a 

354 """ 

355 # If mm is explicitly given, we need to catch that 

356 static_img = path.join("static", "img", arg0, f"{str(size)}.png") 

357 if not path.isfile(static_img): 

358 # We trust this exists!!! 

359 static_img = path.join("static", "img", arg0, "512.png") 

360 # We trust static/ is mapped to /static/ 

361 return HttpResponseRedirect(f"/{static_img}") 

362 

363 def _return_cached_response(self, data, uri): 

364 data.seek(0) 

365 response = CachingHttpResponse(uri, data, content_type="image/png") 

366 response["Cache-Control"] = "max-age=%i" % CACHE_IMAGES_MAX_AGE 

367 # Remove Vary header for images since language doesn't matter 

368 response["Vary"] = "" 

369 return response 

370 

371 @trace_avatar_operation("generate_png") 

372 def _return_cached_png(self, arg0, data, uri): 

373 arg0.save(data, "PNG", quality=JPEG_QUALITY) 

374 return self._return_cached_response(data, uri) 

375 

376 

377class GravatarProxyView(View): 

378 """ 

379 Proxy request to Gravatar and return the image from there 

380 """ 

381 

382 # TODO: Do cache images!! Memcached? 

383 

384 @trace_avatar_operation("gravatar_proxy") 

385 def get( 

386 self, request, *args, **kwargs 

387 ): # pylint: disable=too-many-branches,too-many-statements,too-many-locals,no-self-use,unused-argument,too-many-return-statements 

388 """ 

389 Override get from parent class 

390 """ 

391 

392 def redir_default(default=None): 

393 url = ( 

394 reverse_lazy("avatar_view", args=[kwargs["digest"]]) 

395 + "?s=%i" % size 

396 + "&forcedefault=y" 

397 ) 

398 if default is not None: 

399 url += f"&default={default}" 

400 return HttpResponseRedirect(url) 

401 

402 size = get_size(request) 

403 gravatarimagedata = None 

404 default = None 

405 

406 with contextlib.suppress(Exception): 

407 if str(request.GET["default"]) != "None": 

408 default = request.GET["default"] 

409 if str(default) != "wavatar": 

410 # This part is special/hackish 

411 # Check if the image returned by Gravatar is their default image, if so, 

412 # redirect to our default instead. 

413 gravatar_test_url = ( 

414 "https://secure.gravatar.com/avatar/" 

415 + kwargs["digest"] 

416 + "?s=%i&d=%i" % (50, 404) 

417 ) 

418 if cache.get(gravatar_test_url) == "default": 

419 # DEBUG only 

420 # print("Cached Gravatar response: Default.") 

421 return redir_default(default) 

422 try: 

423 urlopen(gravatar_test_url) 

424 except HTTPError as exc: 

425 if exc.code == 404: 

426 cache.set(gravatar_test_url, "default", 60) 

427 else: 

428 logger.warning(f"Gravatar test url fetch failed: {exc}") 

429 return redir_default(default) 

430 

431 gravatar_url = ( 

432 "https://secure.gravatar.com/avatar/" + kwargs["digest"] + "?s=%i" % size 

433 ) 

434 if default: 

435 gravatar_url += f"&d={default}" 

436 

437 try: 

438 if cache.get(gravatar_url) == "err": 

439 logger.warning( 

440 f"Cached Gravatar fetch failed with URL error: {gravatar_url}" 

441 ) 

442 return redir_default(default) 

443 

444 gravatarimagedata = urlopen(gravatar_url) 

445 except HTTPError as exc: 

446 if exc.code not in [404, 503]: 

447 logger.warning( 

448 f"Gravatar fetch failed with an unexpected {exc.code} HTTP error: {gravatar_url}" 

449 ) 

450 cache.set(gravatar_url, "err", 30) 

451 return redir_default(default) 

452 except URLError as exc: 

453 logger.warning(f"Gravatar fetch failed with URL error: {exc.reason}") 

454 cache.set(gravatar_url, "err", 30) 

455 return redir_default(default) 

456 except SSLError as exc: 

457 logger.warning(f"Gravatar fetch failed with SSL error: {exc.reason}") 

458 cache.set(gravatar_url, "err", 30) 

459 return redir_default(default) 

460 try: 

461 data = BytesIO(gravatarimagedata.read()) 

462 img = Image.open(data) 

463 data.seek(0) 

464 response = HttpResponse( 

465 data.read(), content_type=f"image/{file_format(img.format)}" 

466 ) 

467 response["Cache-Control"] = "max-age=%i" % CACHE_IMAGES_MAX_AGE 

468 # Remove Vary header for images since language doesn't matter 

469 response["Vary"] = "" 

470 return response 

471 

472 except ValueError as exc: 

473 logger.error(f"Value error: {exc}") 

474 return redir_default(default) 

475 

476 # We shouldn't reach this point... But make sure we do something 

477 return redir_default(default) 

478 

479 

480class BlueskyProxyView(View): 

481 """ 

482 Proxy request to Bluesky and return the image from there 

483 """ 

484 

485 def get( 

486 self, request, *args, **kwargs 

487 ): # pylint: disable=too-many-branches,too-many-statements,too-many-locals,no-self-use,unused-argument,too-many-return-statements 

488 """ 

489 Override get from parent class 

490 """ 

491 

492 def redir_default(default=None): 

493 url = ( 

494 reverse_lazy("avatar_view", args=[kwargs["digest"]]) 

495 + "?s=%i" % size 

496 + "&forcedefault=y" 

497 ) 

498 if default is not None: 

499 url += f"&default={default}" 

500 return HttpResponseRedirect(url) 

501 

502 size = get_size(request) 

503 logger.debug(f"Bluesky avatar size requested: {size}") 

504 blueskyimagedata = None 

505 default = None 

506 

507 with contextlib.suppress(Exception): 

508 if str(request.GET["default"]) != "None": 

509 default = request.GET["default"] 

510 identity = None 

511 

512 # First check for email, as this is the most common 

513 try: 

514 identity = ConfirmedEmail.objects.filter( 

515 Q(digest=kwargs["digest"]) | Q(digest_sha256=kwargs["digest"]) 

516 ).first() 

517 except Exception as exc: 

518 logger.warning(f"Exception: {exc}") 

519 

520 # If no identity is found in the email table, try the openid table 

521 if not identity: 

522 try: 

523 identity = ConfirmedOpenId.objects.filter( 

524 Q(digest=kwargs["digest"]) 

525 | Q(alt_digest1=kwargs["digest"]) 

526 | Q(alt_digest2=kwargs["digest"]) 

527 | Q(alt_digest3=kwargs["digest"]) 

528 ).first() 

529 except Exception as exc: 

530 logger.warning(f"Exception: {exc}") 

531 

532 # If still no identity is found, redirect to the default 

533 if not identity: 

534 return redir_default(default) 

535 

536 bs = Bluesky() 

537 bluesky_url = None 

538 # Try with the cache first 

539 with contextlib.suppress(Exception): 

540 if cache.get(identity.bluesky_handle): 

541 bluesky_url = cache.get(identity.bluesky_handle) 

542 if not bluesky_url: 

543 try: 

544 bluesky_url = bs.get_avatar(identity.bluesky_handle) 

545 cache.set(identity.bluesky_handle, bluesky_url) 

546 except Exception: # pylint: disable=bare-except 

547 return redir_default(default) 

548 

549 try: 

550 if cache.get(bluesky_url) == "err": 

551 logger.warning( 

552 f"Cached Bluesky fetch failed with URL error: {bluesky_url}" 

553 ) 

554 return redir_default(default) 

555 

556 blueskyimagedata = urlopen(bluesky_url) 

557 except HTTPError as exc: 

558 if exc.code not in [404, 503]: 

559 print( 

560 f"Bluesky fetch failed with an unexpected {exc.code} HTTP error: {bluesky_url}" 

561 ) 

562 cache.set(bluesky_url, "err", 30) 

563 return redir_default(default) 

564 except URLError as exc: 

565 logger.warning(f"Bluesky fetch failed with URL error: {exc.reason}") 

566 cache.set(bluesky_url, "err", 30) 

567 return redir_default(default) 

568 except SSLError as exc: 

569 logger.warning(f"Bluesky fetch failed with SSL error: {exc.reason}") 

570 cache.set(bluesky_url, "err", 30) 

571 return redir_default(default) 

572 try: 

573 data = BytesIO(blueskyimagedata.read()) 

574 img = Image.open(data) 

575 img_format = img.format 

576 if max(img.size) > size: 

577 aspect = img.size[0] / float(img.size[1]) 

578 if aspect > 1: 

579 new_size = (size, int(size / aspect)) 

580 else: 

581 new_size = (int(size * aspect), size) 

582 img = img.resize(new_size) 

583 data = BytesIO() 

584 img.save(data, format=img_format) 

585 

586 data.seek(0) 

587 response = HttpResponse( 

588 data.read(), content_type=f"image/{file_format(format)}" 

589 ) 

590 response["Cache-Control"] = "max-age=%i" % CACHE_IMAGES_MAX_AGE 

591 # Remove Vary header for images since language doesn't matter 

592 response["Vary"] = "" 

593 return response 

594 except ValueError as exc: 

595 logger.error(f"Value error: {exc}") 

596 return redir_default(default) 

597 

598 # We shouldn't reach this point... But make sure we do something 

599 return redir_default(default) 

600 

601 

602class StatsView(TemplateView, JsonResponse): 

603 """ 

604 Return stats 

605 """ 

606 

607 def get( 

608 self, request, *args, **kwargs 

609 ): # pylint: disable=too-many-branches,too-many-statements,too-many-locals,no-self-use,unused-argument,too-many-return-statements 

610 retval = { 

611 "users": User.objects.count(), 

612 "mails": ConfirmedEmail.objects.count(), 

613 "openids": ConfirmedOpenId.objects.count(), # pylint: disable=no-member 

614 "unconfirmed_mails": UnconfirmedEmail.objects.count(), # pylint: disable=no-member 

615 "unconfirmed_openids": UnconfirmedOpenId.objects.count(), # pylint: disable=no-member 

616 "avatars": Photo.objects.count(), # pylint: disable=no-member 

617 } 

618 

619 # Top 10 viewed avatars 

620 top_photos = Photo.objects.order_by("-access_count")[:10] 

621 top_photos_data = [] 

622 for photo in top_photos: 

623 # Find the associated email or openid with highest access count 

624 associated_emails = photo.emails.all().order_by("-access_count") 

625 associated_openids = photo.openids.all().order_by("-access_count") 

626 

627 # Get the one with highest access count 

628 top_associated = None 

629 if associated_emails and associated_openids: 

630 if ( 

631 associated_emails[0].access_count 

632 >= associated_openids[0].access_count 

633 ): 

634 top_associated = associated_emails[0] 

635 else: 

636 top_associated = associated_openids[0] 

637 elif associated_emails: 

638 top_associated = associated_emails[0] 

639 elif associated_openids: 

640 top_associated = associated_openids[0] 

641 

642 if top_associated: 

643 if hasattr(top_associated, "email"): 

644 # It's a ConfirmedEmail 

645 top_photos_data.append( 

646 { 

647 "access_count": top_associated.access_count, 

648 "avatar_url": f"https://libravatar.org/avatar/{top_associated.digest_sha256}", 

649 } 

650 ) 

651 else: 

652 # It's a ConfirmedOpenId 

653 top_photos_data.append( 

654 { 

655 "access_count": top_associated.access_count, 

656 "avatar_url": f"https://libravatar.org/avatar/{top_associated.digest}", 

657 } 

658 ) 

659 

660 retval["top_viewed_avatars"] = top_photos_data 

661 

662 # Top 10 queried email addresses 

663 top_emails = ConfirmedEmail.objects.order_by("-access_count")[:10] 

664 top_emails_data = [] 

665 for email in top_emails: 

666 top_emails_data.append( 

667 { 

668 "access_count": email.access_count, 

669 "avatar_url": f"https://libravatar.org/avatar/{email.digest_sha256}", 

670 } 

671 ) 

672 

673 retval["top_queried_emails"] = top_emails_data 

674 

675 # Top 10 queried OpenIDs 

676 top_openids = ConfirmedOpenId.objects.order_by("-access_count")[:10] 

677 top_openids_data = [] 

678 for openid in top_openids: 

679 top_openids_data.append( 

680 { 

681 "access_count": openid.access_count, 

682 "avatar_url": f"https://libravatar.org/avatar/{openid.digest}", 

683 } 

684 ) 

685 

686 retval["top_queried_openids"] = top_openids_data 

687 

688 # Photo format distribution 

689 from django.db.models import Count 

690 

691 format_distribution = ( 

692 Photo.objects.values("format") 

693 .annotate(count=Count("format")) 

694 .order_by("-count") 

695 ) 

696 retval["photo_format_distribution"] = list(format_distribution) 

697 

698 # User activity statistics 

699 users_with_multiple_photos = ( 

700 User.objects.annotate(photo_count=Count("photo")) 

701 .filter(photo_count__gt=1) 

702 .count() 

703 ) 

704 users_with_both_email_and_openid = ( 

705 User.objects.filter( 

706 confirmedemail__isnull=False, confirmedopenid__isnull=False 

707 ) 

708 .distinct() 

709 .count() 

710 ) 

711 

712 # Calculate average photos per user 

713 total_photos = Photo.objects.count() 

714 total_users = User.objects.count() 

715 avg_photos_per_user = total_photos / total_users if total_users > 0 else 0 

716 

717 retval["user_activity"] = { 

718 "users_with_multiple_photos": users_with_multiple_photos, 

719 "users_with_both_email_and_openid": users_with_both_email_and_openid, 

720 "average_photos_per_user": round(avg_photos_per_user, 2), 

721 } 

722 

723 # Bluesky handles statistics 

724 bluesky_emails = ConfirmedEmail.objects.filter( 

725 bluesky_handle__isnull=False 

726 ).count() 

727 bluesky_openids = ConfirmedOpenId.objects.filter( 

728 bluesky_handle__isnull=False 

729 ).count() 

730 total_bluesky_handles = bluesky_emails + bluesky_openids 

731 

732 # Top Bluesky handles by access count 

733 retval["bluesky_handles"] = { 

734 "total_bluesky_handles": total_bluesky_handles, 

735 "bluesky_emails": bluesky_emails, 

736 "bluesky_openids": bluesky_openids, 

737 } 

738 

739 # Average photo size statistics using raw SQL 

740 from django.db import connection 

741 

742 with connection.cursor() as cursor: 

743 # SQL to calculate average photo size 

744 cursor.execute( 

745 """ 

746 SELECT 

747 COUNT(*) as photo_count, 

748 AVG(LENGTH(data)) as avg_size_bytes 

749 FROM ivataraccount_photo 

750 WHERE data IS NOT NULL 

751 """ 

752 ) 

753 result = cursor.fetchone() 

754 

755 if result and result[0] > 0: 

756 photo_count, avg_size_bytes = result 

757 # Convert to float in case database returns string 

758 avg_size_bytes = float(avg_size_bytes) if avg_size_bytes else 0 

759 avg_size_kb = round(avg_size_bytes / 1024, 2) if avg_size_bytes else 0 

760 avg_size_mb = ( 

761 round(avg_size_bytes / (1024 * 1024), 2) if avg_size_bytes else 0 

762 ) 

763 

764 retval["photo_size_stats"] = { 

765 "average_size_bytes": round(avg_size_bytes, 2) 

766 if avg_size_bytes 

767 else 0, 

768 "average_size_kb": avg_size_kb, 

769 "average_size_mb": avg_size_mb, 

770 "total_photos_analyzed": photo_count, 

771 } 

772 else: 

773 retval["photo_size_stats"] = { 

774 "average_size_bytes": 0, 

775 "average_size_kb": 0, 

776 "average_size_mb": 0, 

777 "total_photos_analyzed": 0, 

778 } 

779 

780 # For potential duplicate photos, we'll check for photos with the same format and size 

781 # Note: This is not definitive - different images can have the same format and size 

782 # but it's a good indicator of potential duplicates that might warrant investigation 

783 with connection.cursor() as cursor: 

784 cursor.execute( 

785 """ 

786 SELECT 

787 format, 

788 LENGTH(data) as file_size, 

789 COUNT(*) as count 

790 FROM ivataraccount_photo 

791 WHERE data IS NOT NULL 

792 GROUP BY format, LENGTH(data) 

793 HAVING COUNT(*) > 1 

794 ORDER BY count DESC 

795 LIMIT 10 

796 """ 

797 ) 

798 duplicate_groups = cursor.fetchall() 

799 

800 total_potential_duplicate_photos = sum( 

801 group[2] for group in duplicate_groups 

802 ) 

803 

804 # Convert to list of dictionaries for JSON serialization 

805 duplicate_groups_detail = [ 

806 {"format": group[0], "file_size": group[1], "count": group[2]} 

807 for group in duplicate_groups 

808 ] 

809 

810 retval["potential_duplicate_photos"] = { 

811 "potential_duplicate_groups": len(duplicate_groups), 

812 "total_potential_duplicate_photos": total_potential_duplicate_photos, 

813 "potential_duplicate_groups_detail": duplicate_groups_detail, 

814 "note": "Potential duplicates are identified by matching file format and size - not definitive duplicates", 

815 } 

816 

817 return JsonResponse(retval)