733 lines
26 KiB
Python
733 lines
26 KiB
Python
"""
|
|
database.py - Módulo de banco de dados SQLite para o StudyCalendar.
|
|
Suporte a múltiplos usuários, regras de notificação, e CRUD completo.
|
|
"""
|
|
|
|
import sqlite3
|
|
import os
|
|
import secrets
|
|
from datetime import datetime, timedelta, timezone
|
|
from werkzeug.security import generate_password_hash, check_password_hash
|
|
|
|
|
|
_DUMMY_PASSWORD_HASH = generate_password_hash(secrets.token_urlsafe(32), method='scrypt')
|
|
|
|
DATABASE_PATH = os.environ.get(
|
|
'DATABASE_PATH', os.path.join(os.path.dirname(os.path.abspath(__file__)), 'calendario.db')
|
|
)
|
|
|
|
|
|
def configure(path):
|
|
"""Configura o arquivo SQLite antes de inicializar a aplicação."""
|
|
global DATABASE_PATH
|
|
DATABASE_PATH = os.path.abspath(path)
|
|
|
|
|
|
def get_db():
|
|
"""Retorna uma conexão com o banco de dados SQLite."""
|
|
conn = sqlite3.connect(DATABASE_PATH)
|
|
os.chmod(DATABASE_PATH, 0o600)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA journal_mode=WAL")
|
|
conn.execute("PRAGMA foreign_keys=ON")
|
|
return conn
|
|
|
|
|
|
def init_db():
|
|
"""Cria as tabelas e executa migrações necessárias."""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
# Tabela de usuários
|
|
cur.execute('''
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
display_name TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
user_columns = {row[1] for row in cur.execute("PRAGMA table_info(users)").fetchall()}
|
|
if 'session_version' not in user_columns:
|
|
cur.execute("ALTER TABLE users ADD COLUMN session_version INTEGER NOT NULL DEFAULT 0")
|
|
|
|
# Tabela de feeds ICS
|
|
cur.execute('''
|
|
CREATE TABLE IF NOT EXISTS feeds (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
url TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
color TEXT DEFAULT '#4A90D9',
|
|
sync_interval_minutes INTEGER DEFAULT 30,
|
|
last_synced TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE(user_id, url)
|
|
)
|
|
''')
|
|
feed_columns = {row[1] for row in cur.execute("PRAGMA table_info(feeds)").fetchall()}
|
|
if 'sync_requested_at' not in feed_columns:
|
|
cur.execute("ALTER TABLE feeds ADD COLUMN sync_requested_at TIMESTAMP")
|
|
if 'last_attempted' not in feed_columns:
|
|
cur.execute("ALTER TABLE feeds ADD COLUMN last_attempted TIMESTAMP")
|
|
|
|
# Tabela de eventos
|
|
cur.execute('''
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
feed_id INTEGER,
|
|
uid TEXT,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
location TEXT,
|
|
start_datetime TIMESTAMP NOT NULL,
|
|
end_datetime TIMESTAMP,
|
|
all_day BOOLEAN DEFAULT 0,
|
|
category TEXT DEFAULT 'geral',
|
|
priority TEXT DEFAULT 'normal' CHECK (priority IN ('baixa','normal','alta','urgente')),
|
|
completed BOOLEAN DEFAULT 0,
|
|
source TEXT DEFAULT 'manual' CHECK (source IN ('ics','manual')),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (feed_id) REFERENCES feeds(id) ON DELETE CASCADE,
|
|
UNIQUE(uid, feed_id)
|
|
)
|
|
''')
|
|
|
|
event_columns = {row[1] for row in cur.execute("PRAGMA table_info(events)").fetchall()}
|
|
current_feed_columns = {row[1] for row in cur.execute("PRAGMA table_info(feeds)").fetchall()}
|
|
if 'user_id' not in event_columns or 'user_id' not in current_feed_columns:
|
|
raise RuntimeError(
|
|
'Banco legado sem proprietário. Execute uma migração offline definindo user_id.'
|
|
)
|
|
|
|
# Regras de notificação (múltiplas por usuário)
|
|
cur.execute('''
|
|
CREATE TABLE IF NOT EXISTS notification_rules (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
minutes_before INTEGER NOT NULL,
|
|
label TEXT NOT NULL,
|
|
enabled BOOLEAN DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE(user_id, minutes_before)
|
|
)
|
|
''')
|
|
|
|
notification_columns = {
|
|
row[1] for row in cur.execute("PRAGMA table_info(notification_log)").fetchall()
|
|
}
|
|
if notification_columns and 'rule_id' not in notification_columns:
|
|
cur.execute("DROP TABLE notification_log")
|
|
|
|
# Log de notificações enviadas (por evento + regra)
|
|
cur.execute('''
|
|
CREATE TABLE IF NOT EXISTS notification_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
event_id INTEGER NOT NULL,
|
|
rule_id INTEGER NOT NULL,
|
|
notified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (rule_id) REFERENCES notification_rules(id) ON DELETE CASCADE,
|
|
UNIQUE(event_id, rule_id)
|
|
)
|
|
''')
|
|
cur.execute("PRAGMA user_version = 3")
|
|
|
|
# Índices
|
|
cur.execute("CREATE INDEX IF NOT EXISTS idx_events_start ON events(start_datetime)")
|
|
cur.execute("CREATE INDEX IF NOT EXISTS idx_events_user ON events(user_id)")
|
|
cur.execute("CREATE INDEX IF NOT EXISTS idx_events_uid_feed ON events(uid, feed_id)")
|
|
cur.execute("CREATE INDEX IF NOT EXISTS idx_feeds_user ON feeds(user_id)")
|
|
cur.execute("CREATE INDEX IF NOT EXISTS idx_notif_rules_user ON notification_rules(user_id)")
|
|
cur.execute("CREATE INDEX IF NOT EXISTS idx_notif_log_event ON notification_log(event_id)")
|
|
|
|
# Garante que evento e regra do log pertençam ao mesmo usuário.
|
|
cur.execute('''
|
|
CREATE TRIGGER IF NOT EXISTS notification_log_same_owner_insert
|
|
BEFORE INSERT ON notification_log
|
|
BEGIN
|
|
SELECT CASE WHEN NOT EXISTS (
|
|
SELECT 1 FROM events e JOIN notification_rules r ON r.id = NEW.rule_id
|
|
WHERE e.id = NEW.event_id AND e.user_id = r.user_id
|
|
) THEN RAISE(ABORT, 'notification ownership mismatch') END;
|
|
END
|
|
''')
|
|
cur.execute('''
|
|
DELETE FROM notification_log
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM events e JOIN notification_rules r
|
|
ON r.id = notification_log.rule_id
|
|
WHERE e.id = notification_log.event_id AND e.user_id = r.user_id
|
|
)
|
|
''')
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
# ===================== Usuários =====================
|
|
|
|
def create_user(username, password, display_name):
|
|
"""Cria um novo usuário. Retorna user_id ou None se username já existe."""
|
|
conn = get_db()
|
|
try:
|
|
pw_hash = generate_password_hash(password, method='scrypt')
|
|
cur = conn.execute(
|
|
"INSERT INTO users (username, password_hash, display_name) VALUES (?, ?, ?)",
|
|
(username.strip().lower(), pw_hash, display_name.strip())
|
|
)
|
|
user_id = cur.lastrowid
|
|
|
|
# Criar regras de notificação padrão para o novo usuário
|
|
default_rules = [
|
|
(user_id, 2880, '2 dias antes', 1),
|
|
(user_id, 1440, '1 dia antes', 1),
|
|
(user_id, 60, '1 hora antes', 1),
|
|
(user_id, 30, '30 minutos antes', 0),
|
|
(user_id, 15, '15 minutos antes', 0),
|
|
]
|
|
cur.executemany(
|
|
"INSERT INTO notification_rules (user_id, minutes_before, label, enabled) VALUES (?, ?, ?, ?)",
|
|
default_rules
|
|
)
|
|
|
|
conn.commit()
|
|
return user_id
|
|
except sqlite3.IntegrityError:
|
|
return None
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
def get_user_by_username(username):
|
|
"""Retorna dados do usuário pelo username."""
|
|
conn = get_db()
|
|
if not isinstance(username, str):
|
|
conn.close()
|
|
return None
|
|
user = conn.execute("SELECT * FROM users WHERE username = ?", (username.strip().lower(),)).fetchone()
|
|
conn.close()
|
|
return dict(user) if user else None
|
|
|
|
|
|
def get_user_by_id(user_id):
|
|
"""Retorna dados do usuário pelo ID."""
|
|
conn = get_db()
|
|
user = conn.execute("SELECT id, username, display_name, created_at FROM users WHERE id = ?", (user_id,)).fetchone()
|
|
conn.close()
|
|
return dict(user) if user else None
|
|
|
|
|
|
def get_user_session_version(user_id):
|
|
conn = get_db()
|
|
row = conn.execute("SELECT session_version FROM users WHERE id = ?", (user_id,)).fetchone()
|
|
conn.close()
|
|
return row['session_version'] if row else None
|
|
|
|
|
|
def revoke_user_sessions(user_id):
|
|
conn = get_db()
|
|
cur = conn.execute(
|
|
"UPDATE users SET session_version = session_version + 1 WHERE id = ?", (user_id,)
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
return cur.rowcount > 0
|
|
|
|
|
|
def verify_user_password(username, password):
|
|
"""Verifica username e senha. Retorna o usuário ou None."""
|
|
user = get_user_by_username(username)
|
|
if not user:
|
|
check_password_hash(_DUMMY_PASSWORD_HASH, password)
|
|
return None
|
|
if check_password_hash(user['password_hash'], password):
|
|
return user
|
|
return None
|
|
|
|
|
|
# ===================== Feeds =====================
|
|
|
|
def get_feeds(user_id):
|
|
"""Retorna todos os feeds de um usuário."""
|
|
conn = get_db()
|
|
feeds = conn.execute(
|
|
"""SELECT id, user_id, name, color, sync_interval_minutes, last_synced,
|
|
last_attempted, sync_requested_at, created_at
|
|
FROM feeds WHERE user_id = ? ORDER BY created_at DESC""", (user_id,)
|
|
).fetchall()
|
|
conn.close()
|
|
return [dict(f) for f in feeds]
|
|
|
|
|
|
def count_feeds(user_id):
|
|
conn = get_db()
|
|
count = conn.execute("SELECT COUNT(*) FROM feeds WHERE user_id = ?", (user_id,)).fetchone()[0]
|
|
conn.close()
|
|
return count
|
|
|
|
|
|
def get_feed(feed_id, user_id=None):
|
|
"""Retorna um feed pelo ID, verificando propriedade se user_id fornecido."""
|
|
conn = get_db()
|
|
if user_id:
|
|
feed = conn.execute(
|
|
"SELECT * FROM feeds WHERE id = ? AND user_id = ?", (feed_id, user_id)
|
|
).fetchone()
|
|
else:
|
|
feed = conn.execute("SELECT * FROM feeds WHERE id = ?", (feed_id,)).fetchone()
|
|
conn.close()
|
|
return dict(feed) if feed else None
|
|
|
|
|
|
def get_all_feeds():
|
|
"""Retorna todos os feeds (para sync em background)."""
|
|
conn = get_db()
|
|
feeds = conn.execute("SELECT * FROM feeds").fetchall()
|
|
conn.close()
|
|
return [dict(f) for f in feeds]
|
|
|
|
|
|
def add_feed(user_id, url, name, color='#4A90D9', sync_interval_minutes=30, max_count=None):
|
|
"""Cria um novo feed ICS."""
|
|
conn = get_db()
|
|
try:
|
|
conn.execute("BEGIN IMMEDIATE")
|
|
if max_count is not None:
|
|
count = conn.execute(
|
|
"SELECT COUNT(*) FROM feeds WHERE user_id = ?", (user_id,)
|
|
).fetchone()[0]
|
|
if count >= max_count:
|
|
conn.rollback()
|
|
return None
|
|
now = datetime.now(timezone.utc).isoformat()
|
|
cur = conn.execute(
|
|
"""INSERT INTO feeds
|
|
(user_id, url, name, color, sync_interval_minutes, sync_requested_at)
|
|
VALUES (?, ?, ?, ?, ?, ?)""",
|
|
(user_id, url, name, color, sync_interval_minutes, now)
|
|
)
|
|
conn.commit()
|
|
return cur.lastrowid
|
|
except sqlite3.IntegrityError:
|
|
return None
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
def delete_feed(feed_id, user_id):
|
|
"""Remove um feed verificando propriedade."""
|
|
conn = get_db()
|
|
cur = conn.execute("DELETE FROM feeds WHERE id = ? AND user_id = ?", (feed_id, user_id))
|
|
conn.commit()
|
|
conn.close()
|
|
return cur.rowcount > 0
|
|
|
|
|
|
def update_feed_sync_time(feed_id):
|
|
"""Atualiza o timestamp de última sincronização."""
|
|
conn = get_db()
|
|
cur = conn.execute(
|
|
"UPDATE feeds SET last_synced = ?, sync_requested_at = NULL WHERE id = ?",
|
|
(datetime.now(timezone.utc).isoformat(), feed_id)
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
return cur.rowcount > 0
|
|
|
|
|
|
def request_feed_sync(feed_id, user_id):
|
|
conn = get_db()
|
|
cur = conn.execute(
|
|
"UPDATE feeds SET sync_requested_at = ? WHERE id = ? AND user_id = ?",
|
|
(datetime.now(timezone.utc).isoformat(), feed_id, user_id)
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
return cur.rowcount > 0
|
|
|
|
|
|
def mark_feed_sync_attempt(feed_id):
|
|
"""Registra uma tentativa e retira o pedido da fila para aplicar backoff pelo intervalo."""
|
|
conn = get_db()
|
|
cur = conn.execute(
|
|
"UPDATE feeds SET last_attempted = ?, sync_requested_at = NULL WHERE id = ?",
|
|
(datetime.now(timezone.utc).isoformat(), feed_id)
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
return cur.rowcount > 0
|
|
|
|
|
|
# ===================== Eventos =====================
|
|
|
|
def get_events(user_id, start_iso=None, end_iso=None, category=None, search=None, limit=5000):
|
|
"""Retorna eventos de um usuário com filtros."""
|
|
conn = get_db()
|
|
query = """SELECT events.*, feeds.color AS feed_color
|
|
FROM events LEFT JOIN feeds ON feeds.id = events.feed_id
|
|
WHERE events.user_id = ?"""
|
|
params = [user_id]
|
|
|
|
if start_iso:
|
|
query += " AND (julianday(events.end_datetime) > julianday(?) OR (events.end_datetime IS NULL AND julianday(events.start_datetime) >= julianday(?)))"
|
|
params.extend([start_iso, start_iso])
|
|
if end_iso:
|
|
query += " AND julianday(events.start_datetime) < julianday(?)"
|
|
params.append(end_iso)
|
|
if category and category != 'todas':
|
|
query += " AND events.category = ?"
|
|
params.append(category)
|
|
if search:
|
|
query += " AND (events.title LIKE ? OR events.description LIKE ?)"
|
|
params.extend([f'%{search}%', f'%{search}%'])
|
|
|
|
query += " ORDER BY julianday(events.start_datetime) ASC LIMIT ?"
|
|
params.append(limit)
|
|
events = conn.execute(query, params).fetchall()
|
|
conn.close()
|
|
return [dict(e) for e in events]
|
|
|
|
|
|
def count_manual_events(user_id):
|
|
conn = get_db()
|
|
count = conn.execute(
|
|
"SELECT COUNT(*) FROM events WHERE user_id = ? AND source = 'manual'", (user_id,)
|
|
).fetchone()[0]
|
|
conn.close()
|
|
return count
|
|
|
|
|
|
def get_events_for_day(user_id, date_str):
|
|
"""Retorna eventos de um dia específico (formato: YYYY-MM-DD)."""
|
|
day_start = f"{date_str}T00:00:00"
|
|
day_end = (datetime.fromisoformat(date_str) + timedelta(days=1)).isoformat()
|
|
return get_events(user_id, start_iso=day_start, end_iso=day_end)
|
|
|
|
|
|
def get_event(event_id, user_id):
|
|
"""Retorna um evento verificando propriedade."""
|
|
conn = get_db()
|
|
event = conn.execute(
|
|
"SELECT * FROM events WHERE id = ? AND user_id = ?", (event_id, user_id)
|
|
).fetchone()
|
|
conn.close()
|
|
return dict(event) if event else None
|
|
|
|
|
|
def add_event(user_id, title, start_datetime, end_datetime=None, description=None,
|
|
location=None, all_day=False, category='geral', priority='normal', max_count=None):
|
|
"""Cria um evento manual."""
|
|
conn = get_db()
|
|
conn.execute("BEGIN IMMEDIATE")
|
|
if max_count is not None:
|
|
count = conn.execute(
|
|
"SELECT COUNT(*) FROM events WHERE user_id = ? AND source = 'manual'", (user_id,)
|
|
).fetchone()[0]
|
|
if count >= max_count:
|
|
conn.rollback()
|
|
conn.close()
|
|
return None
|
|
now = datetime.now(timezone.utc).isoformat()
|
|
cur = conn.execute(
|
|
"""INSERT INTO events (user_id, title, start_datetime, end_datetime, description,
|
|
location, all_day, category, priority, source, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'manual', ?, ?)""",
|
|
(user_id, title, start_datetime, end_datetime, description,
|
|
location, 1 if all_day else 0, category, priority, now, now)
|
|
)
|
|
conn.commit()
|
|
event_id = cur.lastrowid
|
|
conn.close()
|
|
return event_id
|
|
|
|
|
|
def update_event(event_id, user_id, title, start_datetime, end_datetime=None,
|
|
description=None, location=None, all_day=False, category='geral', priority='normal'):
|
|
"""Atualiza um evento verificando propriedade."""
|
|
conn = get_db()
|
|
cur = conn.execute(
|
|
"""UPDATE events SET title=?, start_datetime=?, end_datetime=?, description=?,
|
|
location=?, all_day=?, category=?, priority=?, updated_at=?
|
|
WHERE id=? AND user_id=?""",
|
|
(title, start_datetime, end_datetime, description, location,
|
|
1 if all_day else 0, category, priority, datetime.now(timezone.utc).isoformat(),
|
|
event_id, user_id)
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
return cur.rowcount > 0
|
|
|
|
|
|
def delete_event(event_id, user_id):
|
|
"""Remove um evento verificando propriedade."""
|
|
conn = get_db()
|
|
cur = conn.execute("DELETE FROM events WHERE id = ? AND user_id = ?", (event_id, user_id))
|
|
conn.commit()
|
|
conn.close()
|
|
return cur.rowcount > 0
|
|
|
|
|
|
def toggle_event_completed(event_id, user_id):
|
|
"""Alterna o status de conclusão."""
|
|
conn = get_db()
|
|
conn.execute(
|
|
"UPDATE events SET completed = NOT completed, updated_at = ? WHERE id = ? AND user_id = ?",
|
|
(datetime.now(timezone.utc).isoformat(), event_id, user_id)
|
|
)
|
|
conn.commit()
|
|
event = conn.execute("SELECT completed FROM events WHERE id = ? AND user_id = ?", (event_id, user_id)).fetchone()
|
|
conn.close()
|
|
return dict(event) if event else None
|
|
|
|
|
|
def upsert_ics_event(feed_id, user_id, uid, title, start_datetime, end_datetime,
|
|
description, location, all_day):
|
|
"""Insere ou atualiza evento de feed ICS."""
|
|
conn = get_db()
|
|
now = datetime.now(timezone.utc).isoformat()
|
|
existing = conn.execute(
|
|
"SELECT id FROM events WHERE uid = ? AND feed_id = ?", (uid, feed_id)
|
|
).fetchone()
|
|
|
|
if existing:
|
|
conn.execute(
|
|
"""UPDATE events SET title=?, start_datetime=?, end_datetime=?, description=?,
|
|
location=?, all_day=?, updated_at=? WHERE id=?""",
|
|
(title, start_datetime, end_datetime, description, location,
|
|
1 if all_day else 0, now, existing['id'])
|
|
)
|
|
else:
|
|
conn.execute(
|
|
"""INSERT INTO events (user_id, feed_id, uid, title, start_datetime, end_datetime,
|
|
description, location, all_day, source, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'ics', ?, ?)""",
|
|
(user_id, feed_id, uid, title, start_datetime, end_datetime,
|
|
description, location, 1 if all_day else 0, now, now)
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def replace_ics_events(feed_id, user_id, events):
|
|
"""Atualiza um feed inteiro em uma única transação e remove itens ausentes."""
|
|
conn = get_db()
|
|
now = datetime.now(timezone.utc).isoformat()
|
|
try:
|
|
owner = conn.execute(
|
|
"SELECT 1 FROM feeds WHERE id = ? AND user_id = ?", (feed_id, user_id)
|
|
).fetchone()
|
|
if not owner:
|
|
return False
|
|
uids = []
|
|
for event in events:
|
|
uids.append(event['uid'])
|
|
conn.execute(
|
|
"""INSERT INTO events
|
|
(user_id, feed_id, uid, title, start_datetime, end_datetime, description,
|
|
location, all_day, source, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'ics', ?, ?)
|
|
ON CONFLICT(uid, feed_id) DO UPDATE SET
|
|
title=excluded.title, start_datetime=excluded.start_datetime,
|
|
end_datetime=excluded.end_datetime, description=excluded.description,
|
|
location=excluded.location, all_day=excluded.all_day,
|
|
updated_at=excluded.updated_at""",
|
|
(user_id, feed_id, event['uid'], event['title'], event['start_datetime'],
|
|
event['end_datetime'], event['description'], event['location'],
|
|
1 if event['all_day'] else 0, now, now)
|
|
)
|
|
if uids:
|
|
placeholders = ','.join('?' for _ in uids)
|
|
conn.execute(
|
|
f"DELETE FROM events WHERE feed_id = ? AND uid NOT IN ({placeholders})",
|
|
[feed_id, *uids]
|
|
)
|
|
else:
|
|
conn.execute("DELETE FROM events WHERE feed_id = ?", (feed_id,))
|
|
conn.execute(
|
|
"""UPDATE feeds SET last_synced = ?, last_attempted = ?, sync_requested_at = NULL
|
|
WHERE id = ?""",
|
|
(now, now, feed_id)
|
|
)
|
|
conn.commit()
|
|
return True
|
|
except Exception:
|
|
conn.rollback()
|
|
raise
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
# ===================== Regras de Notificação =====================
|
|
|
|
def get_notification_rules(user_id):
|
|
"""Retorna todas as regras de notificação do usuário."""
|
|
conn = get_db()
|
|
rules = conn.execute(
|
|
"SELECT * FROM notification_rules WHERE user_id = ? ORDER BY minutes_before DESC",
|
|
(user_id,)
|
|
).fetchall()
|
|
conn.close()
|
|
return [dict(r) for r in rules]
|
|
|
|
|
|
def add_notification_rule(user_id, minutes_before, label, max_count=None):
|
|
"""Adiciona uma regra de notificação."""
|
|
conn = get_db()
|
|
try:
|
|
conn.execute("BEGIN IMMEDIATE")
|
|
if max_count is not None:
|
|
count = conn.execute(
|
|
"SELECT COUNT(*) FROM notification_rules WHERE user_id = ?", (user_id,)
|
|
).fetchone()[0]
|
|
if count >= max_count:
|
|
conn.rollback()
|
|
return None
|
|
cur = conn.execute(
|
|
"INSERT INTO notification_rules (user_id, minutes_before, label) VALUES (?, ?, ?)",
|
|
(user_id, minutes_before, label)
|
|
)
|
|
conn.commit()
|
|
return cur.lastrowid
|
|
except sqlite3.IntegrityError:
|
|
return None
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
def delete_notification_rule(rule_id, user_id):
|
|
"""Remove uma regra de notificação."""
|
|
conn = get_db()
|
|
cur = conn.execute(
|
|
"DELETE FROM notification_rules WHERE id = ? AND user_id = ?", (rule_id, user_id)
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
return cur.rowcount > 0
|
|
|
|
|
|
def toggle_notification_rule(rule_id, user_id):
|
|
"""Alterna o estado de uma regra."""
|
|
conn = get_db()
|
|
conn.execute(
|
|
"UPDATE notification_rules SET enabled = NOT enabled WHERE id = ? AND user_id = ?",
|
|
(rule_id, user_id)
|
|
)
|
|
conn.commit()
|
|
rule = conn.execute(
|
|
"SELECT * FROM notification_rules WHERE id = ? AND user_id = ?", (rule_id, user_id)
|
|
).fetchone()
|
|
conn.close()
|
|
return dict(rule) if rule else None
|
|
|
|
|
|
def get_upcoming_notifications(user_id):
|
|
"""
|
|
Retorna eventos que precisam de notificação baseado em TODAS as regras ativas.
|
|
Para cada regra ativa, verifica se há eventos dentro da janela de tempo
|
|
que ainda não foram notificados para essa regra específica.
|
|
"""
|
|
conn = get_db()
|
|
now = datetime.now(timezone.utc)
|
|
|
|
# Buscar regras ativas do usuário
|
|
rules = conn.execute(
|
|
"SELECT * FROM notification_rules WHERE user_id = ? AND enabled = 1",
|
|
(user_id,)
|
|
).fetchall()
|
|
|
|
notifications = []
|
|
for rule in rules:
|
|
minutes = rule['minutes_before']
|
|
future = now + timedelta(minutes=minutes)
|
|
|
|
events = conn.execute(
|
|
"""SELECT e.*, ? as rule_id, ? as rule_label, ? as rule_minutes
|
|
FROM events e
|
|
WHERE e.user_id = ?
|
|
AND julianday(e.start_datetime) >= julianday(?)
|
|
AND julianday(e.start_datetime) <= julianday(?)
|
|
AND e.completed = 0
|
|
AND e.id NOT IN (
|
|
SELECT event_id FROM notification_log WHERE rule_id = ?
|
|
)
|
|
ORDER BY e.start_datetime ASC""",
|
|
(rule['id'], rule['label'], minutes, user_id,
|
|
now.isoformat(), future.isoformat(), rule['id'])
|
|
).fetchall()
|
|
|
|
for ev in events:
|
|
notifications.append(dict(ev))
|
|
|
|
conn.close()
|
|
|
|
# Remover duplicatas (mesmo evento pode aparecer em múltiplas regras)
|
|
seen = set()
|
|
unique = []
|
|
for n in notifications:
|
|
key = (n['id'], n['rule_id'])
|
|
if key not in seen:
|
|
seen.add(key)
|
|
unique.append(n)
|
|
|
|
return unique
|
|
|
|
|
|
def mark_event_notified(event_id, rule_id, user_id):
|
|
"""Registra que uma notificação foi enviada para evento+regra."""
|
|
conn = get_db()
|
|
try:
|
|
cur = conn.execute(
|
|
"""INSERT OR IGNORE INTO notification_log (event_id, rule_id)
|
|
SELECT e.id, r.id FROM events e JOIN notification_rules r
|
|
ON r.id = ? AND r.user_id = ? WHERE e.id = ? AND e.user_id = ?""",
|
|
(rule_id, user_id, event_id, user_id)
|
|
)
|
|
conn.commit()
|
|
return cur.rowcount > 0 or conn.execute(
|
|
"""SELECT 1 FROM notification_log l JOIN events e ON e.id=l.event_id
|
|
JOIN notification_rules r ON r.id=l.rule_id
|
|
WHERE l.event_id=? AND l.rule_id=? AND e.user_id=? AND r.user_id=?""",
|
|
(event_id, rule_id, user_id, user_id)
|
|
).fetchone() is not None
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
# ===================== Estatísticas =====================
|
|
|
|
def get_stats(user_id):
|
|
"""Retorna estatísticas dos eventos do usuário."""
|
|
conn = get_db()
|
|
now = datetime.now(timezone.utc)
|
|
week_end = now + timedelta(days=7)
|
|
|
|
total = conn.execute(
|
|
"SELECT COUNT(*) FROM events WHERE user_id = ?", (user_id,)
|
|
).fetchone()[0]
|
|
upcoming_week = conn.execute(
|
|
"SELECT COUNT(*) FROM events WHERE user_id = ? AND julianday(start_datetime) >= julianday(?) AND julianday(start_datetime) <= julianday(?) AND completed = 0",
|
|
(user_id, now.isoformat(), week_end.isoformat())
|
|
).fetchone()[0]
|
|
overdue = conn.execute(
|
|
"SELECT COUNT(*) FROM events WHERE user_id = ? AND julianday(start_datetime) < julianday(?) AND completed = 0",
|
|
(user_id, now.isoformat())
|
|
).fetchone()[0]
|
|
completed = conn.execute(
|
|
"SELECT COUNT(*) FROM events WHERE user_id = ? AND completed = 1", (user_id,)
|
|
).fetchone()[0]
|
|
|
|
conn.close()
|
|
return {
|
|
'total_events': total,
|
|
'upcoming_this_week': upcoming_week,
|
|
'overdue': overdue,
|
|
'completed_count': completed
|
|
}
|