DB="$HOME/Library/Group Containers/group.com.apple.calendar/Calendar.sqlitedb"

echo "=== ALL CALENDARS BY ACCOUNT ==="
sqlite3 -separator '|' "$DB" "
SELECT
  s.name as account,
  CASE s.type
    WHEN 0 THEN 'Local'
    WHEN 1 THEN 'Local'
    WHEN 2 THEN 'CalDAV/Google'
    WHEN 3 THEN 'Exchange'
    WHEN 5 THEN 'Found in Mail'
    ELSE 'Other'
  END as account_type,
  s.disabled as is_disabled,
  c.title as calendar_name,
  c.ROWID as cal_id,
  COUNT(DISTINCT ci.ROWID) as total_events
FROM Store s
JOIN Calendar c ON c.store_id = s.ROWID
LEFT JOIN CalendarItem ci ON ci.calendar_id = c.ROWID
  AND ci.hidden = 0
  AND ci.status != 2
WHERE s.type != 5
GROUP BY c.ROWID
ORDER BY s.disabled, s.name, c.title;
"

echo ""
echo "=== UPCOMING EVENTS (next 30 days) PER CALENDAR ==="
NOW_CD=$(( $(date +%s) - 978307200 ))
FUTURE_CD=$(( $(date +%s) + 30 * 86400 - 978307200 ))
sqlite3 -separator '|' "$DB" "
SELECT
  c.title as calendar_name,
  s.name as account,
  COUNT(DISTINCT ci.ROWID) as upcoming_count
FROM OccurrenceCache oc
JOIN CalendarItem ci ON oc.event_id = ci.ROWID
JOIN Calendar c ON ci.calendar_id = c.ROWID
JOIN Store s ON c.store_id = s.ROWID
WHERE oc.occurrence_date >= $NOW_CD
  AND oc.occurrence_date <= $FUTURE_CD
  AND ci.hidden = 0
  AND ci.status != 2
  AND s.type != 5
  AND s.disabled = 0
GROUP BY c.ROWID
ORDER BY upcoming_count DESC;
"