xcc.es
[READ-ONLY] Mirror of https://github.com/mrgnw/scripts.
1#!/usr/bin/env python3
2"""
3Generate detailed Apple Notes attachment report with actionable recommendations.
4
5This script creates:
61. HTML report with interactive filtering
72. CSV export for spreadsheet analysis
83. Deletion recommendations based on size and type
9"""
10
11import csv
12import sqlite3
13from collections import defaultdict
14from datetime import datetime
15from pathlib import Path
16
17
18def get_human_readable_size(bytes_val):
19 """Convert bytes to human readable format."""
20 for unit in ["B", "KB", "MB", "GB", "TB"]:
21 if bytes_val < 1024:
22 return f"{bytes_val:.2f} {unit}"
23 bytes_val /= 1024
24 return f"{bytes_val:.2f} PB"
25
26
27def analyze_database():
28 """Extract attachment data from NoteStore.sqlite."""
29 db_path = Path.home() / "Library/Group Containers/group.com.apple.notes/NoteStore.sqlite"
30
31 if not db_path.exists():
32 print(f"❌ Database not found at {db_path}")
33 return None
34
35 try:
36 conn = sqlite3.connect(str(db_path))
37 conn.row_factory = sqlite3.Row
38 cursor = conn.cursor()
39
40 query = """
41 SELECT
42 obj.Z_PK AS object_id,
43 obj.ZFILENAME AS filename,
44 obj.ZTITLE AS title,
45 obj.ZTYPEUTI AS mime_type,
46 obj.ZFILESIZE AS file_size,
47 obj.ZMODIFICATIONDATE AS modification_date,
48 obj.ZNOTE AS note_fk,
49 COALESCE(note.ZTITLE1, '(Untitled)') AS note_title,
50 note.Z_PK AS note_id,
51 note.ZIDENTIFIER AS note_uuid
52 FROM ZICCLOUDSYNCINGOBJECT obj
53 LEFT JOIN ZICCLOUDSYNCINGOBJECT note ON obj.ZNOTE = note.Z_PK
54 WHERE obj.ZFILESIZE > 0
55 ORDER BY obj.ZFILESIZE DESC
56 """
57
58 cursor.execute(query)
59 results = [dict(row) for row in cursor.fetchall()]
60
61 # Also fetch note identifiers for deep linking
62 note_ids_query = """
63 SELECT Z_PK, ZIDENTIFIER, ZTITLE1
64 FROM ZICCLOUDSYNCINGOBJECT
65 WHERE ZTYPE IS NULL AND ZTITLE1 IS NOT NULL
66 """
67 cursor.execute(note_ids_query)
68 note_identifiers = {row[0]: {"id": row[1], "title": row[2]} for row in cursor.fetchall()}
69
70 conn.close()
71
72 return results, note_identifiers
73 except sqlite3.OperationalError as e:
74 print(f"❌ Cannot access database: {e}")
75 print("\nYou need to grant Full Disk Access to Terminal:")
76 print(" 1. System Settings > Security & Privacy > Privacy")
77 print(" 2. Full Disk Access > + button > Terminal.app")
78 return None
79
80
81def get_file_extension(mime_type):
82 """Get common file extension from MIME type."""
83 mime_to_ext = {
84 "public.jpeg": "jpg",
85 "public.png": "png",
86 "public.heic": "heic",
87 "public.tiff": "tiff",
88 "com.adobe.pdf": "pdf",
89 "public.mpeg-4": "mp4",
90 "com.apple.quicktime-movie": "mov",
91 "com.apple.m4a-audio": "m4a",
92 "public.data": "data",
93 }
94 return mime_to_ext.get(mime_type, mime_type.split(".")[-1] if mime_type else "unknown")
95
96
97def get_notes_app_deep_link(note_uuid):
98 """Generate a deep link to open a note in the Notes app."""
99 if not note_uuid:
100 return None
101 return f"notes://showNote?identifier={note_uuid}"
102
103
104def categorize_by_type_size(attachments):
105 """Identify large attachments by type for recommendations."""
106 by_type = defaultdict(lambda: {"count": 0, "total_size": 0, "files": []})
107
108 for att in attachments:
109 mime_type = att.get("mime_type") or "unknown"
110 size = att.get("file_size", 0)
111
112 by_type[mime_type]["count"] += 1
113 by_type[mime_type]["total_size"] += size
114 by_type[mime_type]["files"].append(
115 {
116 "filename": att.get("filename") or att.get("title") or f"Object_{att.get('object_id')}",
117 "size": size,
118 "note": att.get("note_title", "Untitled"),
119 "object_id": att.get("object_id"),
120 "note_uuid": att.get("note_uuid"),
121 }
122 )
123
124 return by_type
125
126
127def generate_csv_report(attachments, output_path):
128 """Generate detailed CSV report."""
129 sorted_files = sorted(attachments, key=lambda x: x.get("file_size", 0), reverse=True)
130
131 with open(output_path, "w", newline="", encoding="utf-8") as f:
132 writer = csv.writer(f)
133 writer.writerow(
134 ["note_title", "attachment_name", "size_bytes", "size_human", "mime_type", "object_id"]
135 )
136
137 for att in sorted_files:
138 writer.writerow(
139 [
140 att.get("note_title") or "(Untitled)",
141 att.get("filename") or att.get("title") or f"Object_{att.get('object_id')}",
142 att.get("file_size", 0),
143 get_human_readable_size(att.get("file_size", 0)),
144 att.get("mime_type", "unknown"),
145 att.get("object_id"),
146 ]
147 )
148
149 print(f"✅ CSV report: {output_path}")
150
151
152def generate_html_report(attachments, output_path):
153 """Generate interactive HTML report grouped by note."""
154 total_size = sum(att.get("file_size", 0) for att in attachments)
155 total_count = len(attachments)
156
157 # Count unique file types
158 file_types = set(att.get("mime_type", "unknown") for att in attachments)
159 num_file_types = len(file_types)
160
161 # Group attachments by note
162 notes_dict = {}
163 for att in attachments:
164 note_title = att.get("note_title") or "(Untitled)"
165 note_uuid = att.get("note_uuid")
166
167 if note_title not in notes_dict:
168 notes_dict[note_title] = {"uuid": note_uuid, "size": 0, "files": []}
169
170 notes_dict[note_title]["size"] += att.get("file_size", 0)
171 notes_dict[note_title]["files"].append(
172 {
173 "filename": att.get("filename") or att.get("title") or f"Object_{att.get('object_id')}",
174 "size": att.get("file_size", 0),
175 }
176 )
177
178 # Sort notes by total size (largest first)
179 sorted_notes = sorted(notes_dict.items(), key=lambda x: x[1]["size"], reverse=True)
180
181 html = f"""
182<!DOCTYPE html>
183<html lang="en">
184<head>
185 <meta charset="UTF-8">
186 <meta name="viewport" content="width=device-width, initial-scale=1.0">
187 <title>Apple Notes Attachment Report</title>
188 <style>
189 * {{
190 margin: 0;
191 padding: 0;
192 box-sizing: border-box;
193 }}
194
195 body {{
196 font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
197 background: #f5f5f5;
198 color: #333;
199 line-height: 1.6;
200 }}
201
202 header {{
203 background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
204 color: white;
205 padding: 20px;
206 text-align: center;
207 }}
208
209 header h1 {{
210 font-size: 1.8em;
211 margin: 0;
212 }}
213
214 header p {{
215 font-size: 0.9em;
216 margin: 5px 0 0 0;
217 opacity: 0.9;
218 }}
219
220 .container {{
221 max-width: 100%;
222 margin: 0;
223 padding: 0;
224 }}
225
226 .summary {{
227 display: flex;
228 background: white;
229 border-bottom: 1px solid #ddd;
230 padding: 0;
231 }}
232
233 .card {{
234 flex: 1;
235 padding: 15px 20px;
236 border-right: 1px solid #ddd;
237 text-align: center;
238 }}
239
240 .card:last-child {{
241 border-right: none;
242 }}
243
244 .card h3 {{
245 color: #667eea;
246 margin: 0 0 5px 0;
247 font-size: 0.75em;
248 text-transform: uppercase;
249 letter-spacing: 0.5px;
250 }}
251
252 .card .value {{
253 font-size: 1.5em;
254 font-weight: bold;
255 color: #333;
256 margin: 0;
257 }}
258
259 .types-section {{
260 margin: 30px 0;
261 }}
262
263 .types-section h2 {{
264 margin: 0;
265 padding: 20px;
266 font-size: 1.1em;
267 background: #f9f9f9;
268 border-bottom: 1px solid #ddd;
269 }}
270
271 .file-list {{
272 background: white;
273 overflow-y: auto;
274 max-height: 80vh;
275 }}
276
277 .note-group {{
278 border-bottom: 1px solid #ddd;
279 padding: 16px 20px;
280 background: white;
281 }}
282
283 .note-group:hover {{
284 background: #fafafa;
285 }}
286
287 .note-header {{
288 display: grid;
289 grid-template-columns: 2fr 1fr;
290 gap: 40px;
291 align-items: center;
292 margin-bottom: 12px;
293 }}
294
295 .note-title {{
296 font-weight: 600;
297 font-size: 1em;
298 color: #333;
299 }}
300
301 .note-title a {{
302 color: #667eea;
303 text-decoration: none;
304 cursor: pointer;
305 font-weight: 600;
306 }}
307
308 .note-title a:hover {{
309 color: #764ba2;
310 text-decoration: underline;
311 }}
312
313 .note-size {{
314 font-weight: bold;
315 color: #667eea;
316 text-align: right;
317 font-size: 0.95em;
318 }}
319
320 .note-files {{
321 font-family: 'Monaco', 'Courier New', monospace;
322 font-size: 0.85em;
323 color: #666;
324 white-space: pre-wrap;
325 word-break: break-word;
326 line-height: 1.4;
327 margin-left: 0;
328 padding: 0;
329 }}
330
331 .note-link-icon {{
332 margin-left: 4px;
333 font-size: 0.8em;
334 }}
335
336
337
338 footer {{
339 text-align: center;
340 padding: 20px;
341 color: #999;
342 font-size: 0.85em;
343 background: #f9f9f9;
344 border-top: 1px solid #ddd;
345 }}
346
347 @media (max-width: 768px) {{
348 header h1 {{
349 font-size: 1.8em;
350 }}
351
352 .type-header {{
353 flex-direction: column;
354 align-items: flex-start;
355 }}
356
357 .type-stats {{
358 width: 100%;
359 margin-top: 10px;
360 }}
361 }}
362 </style>
363</head>
364<body>
365 <header>
366 <h1>📱 Apple Notes Attachment Analysis</h1>
367 <p>Generated on {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}</p>
368 </header>
369
370 <div class="container">
371 <div class="summary">
372 <div class="card">
373 <h3>Files</h3>
374 <div class="value">{total_count:,}</div>
375 </div>
376 <div class="card">
377 <h3>Total Size</h3>
378 <div class="value">{get_human_readable_size(total_size)}</div>
379 </div>
380 <div class="card">
381 <h3>Types</h3>
382 <div class="value">{num_file_types}</div>
383 </div>
384 <div class="card">
385 <h3>Avg</h3>
386 <div class="value">{get_human_readable_size(total_size // max(total_count, 1))}</div>
387 </div>
388 </div>
389
390
391
392 <div class="types-section">
393 <h2>Notes by Storage</h2>
394 <div class="file-list">
395"""
396
397 # Add notes grouped by size with their files
398 for note_title, note_data in sorted_notes:
399 note_uuid = note_data["uuid"]
400 total_note_size = note_data["size"]
401 files = sorted(note_data["files"], key=lambda x: x["size"], reverse=True)
402
403 if note_uuid:
404 note_link = get_notes_app_deep_link(note_uuid)
405 note_display = f'<a href="{note_link}" title="Click to open this note in Notes.app">{note_title}<span class="note-link-icon">↗</span></a>'
406 else:
407 note_display = note_title
408
409 # Build file list (newline separated)
410 files_text = "\n".join([f["filename"] for f in files])
411
412 html += f"""
413 <div class="note-group">
414 <div class="note-header">
415 <div class="note-title">{note_display}</div>
416 <div class="note-size">{get_human_readable_size(total_note_size)}</div>
417 </div>
418 <div class="note-files">{files_text}</div>
419 </div>
420"""
421
422 html += """
423 </div>
424 </div>
425
426 <footer>
427 <p>Click any note name to open in Notes.app</p>
428 </footer>
429</body>
430</html>
431"""
432
433 with open(output_path, "w", encoding="utf-8") as f:
434 f.write(html)
435
436 print(f"✅ HTML report: {output_path}")
437
438
439def main():
440 print("🔍 Generating Apple Notes attachment report...\n")
441
442 # Analyze database
443 print("📚 Analyzing database...")
444 result = analyze_database()
445
446 if result is None:
447 return
448
449 db_attachments, note_identifiers = result
450
451 print(f" Found {len(db_attachments)} attachments\n")
452
453 # Generate reports
454 output_dir = Path.cwd()
455
456 # CSV Report
457 csv_path = output_dir / "notes_attachment_detailed.csv"
458 generate_csv_report(db_attachments, csv_path)
459
460 # HTML Report
461 html_path = output_dir / "notes_attachment_report.html"
462 generate_html_report(db_attachments, html_path)
463
464 print("\n" + "=" * 80)
465 print("✅ Reports generated successfully!")
466 print("=" * 80)
467 print(f"\n📄 Files created:")
468 print(f" 1. {html_path.name} - Open in your browser for interactive view")
469 print(f" 2. {csv_path.name} - Import into spreadsheet for detailed analysis")
470 print(f"\n💡 Next steps:")
471 print(f" 1. Review the HTML report to identify large attachments")
472 print(f" 2. Use the CSV for sorting and filtering in Excel/Sheets")
473 print(f" 3. Delete large attachments from Notes app")
474 print(f" 4. Re-run analyze-notes-space.py to verify cleanup\n")
475
476
477if __name__ == "__main__":
478 main()