# Callkeep: Fällige und überfällige Anrufe abfragen

Use this Python snippet to list overdue and due contacts directly from the Callkeep SQLite database.

## Path

- Database: `/DATA/.media/HDD_1TB/CallKeep/callkeep.db`
- Table: `contacts` joined with `calls`

## Frequency-to-days mapping

| frequency | days |
|-----------|------|
| Täglich | 1 |
| Wöchentlich | 7 |
| Alle 2 Wochen | 14 |
| Monatlich | 30 |
| Alle 2 Monate | 60 |
| Vierteljährlich | 90 |
| Halbjährlich | 180 |
| Jährlich | 365 |

## Python snippet

```python
import sqlite3, datetime

db_path = "/DATA/.media/HDD_1TB/CallKeep/callkeep.db"
conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row
c = conn.cursor()

today = datetime.date.today()

freq_days = {
    'Täglich': 1,
    'Wöchentlich': 7,
    'Alle 2 Wochen': 14,
    'Monatlich': 30,
    'Alle 2 Monate': 60,
    'Vierteljährlich': 90,
    'Halbjährlich': 180,
    'Jährlich': 365,
}

c.execute("""
SELECT c.id, c.name, c.frequency, MAX(cl.called_at) as last_call
FROM contacts c
LEFT JOIN calls cl ON c.id = cl.contact_id
GROUP BY c.id
ORDER BY c.name ASC
""")

overdue = []
for row in c.fetchall():
    name = row['name']
    freq = row['frequency']
    last = row['last_call']
    if last:
        try:
            last_date = datetime.datetime.fromisoformat(last.replace('T', ' ').split('.')[0]).date()
        except:
            try:
                last_date = datetime.datetime.strptime(last.split('T')[0], '%Y-%m-%d').date()
            except:
                continue
        days_since = (today - last_date).days
        interval = freq_days.get(freq, 30)
        due_in = interval - days_since
        if due_in < 0 or due_in <= 2:
            overdue.append((name, freq, days_since, due_in))
    else:
        overdue.append((name, freq, None, 'never called'))

for item in overdue:
    print(item)

conn.close()
```

## Notes

- `called_at` may be a full ISO datetime or just a date string — parse defensively.
- Contacts with `next_call_at` set in the database are also valid triggers, but the app mostly derives due dates from `frequency` and the latest call.
- The app stores no explicit status field; compute status in the query.