CREATE TABLE IF NOT EXISTS push_subscriptions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    endpoint TEXT NOT NULL UNIQUE,
    p256dh TEXT NOT NULL,
    auth TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS vapid_keys (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    private_key_pem TEXT NOT NULL,
    public_key_base64url TEXT NOT NULL
);

ALTER TABLE user_stats ADD COLUMN reminder_enabled INTEGER DEFAULT 0;
ALTER TABLE user_stats ADD COLUMN reminder_time TEXT DEFAULT '20:00';
ALTER TABLE user_stats ADD COLUMN last_notified_date TEXT;