94 lines
3.6 KiB
SQL
94 lines
3.6 KiB
SQL
-- Migration: Add snitch and adaptive medication tables
|
|
-- Run this if tables don't exist
|
|
|
|
-- Snitch Settings
|
|
CREATE TABLE IF NOT EXISTS snitch_settings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_uuid UUID REFERENCES users(id) ON DELETE CASCADE UNIQUE,
|
|
snitch_enabled BOOLEAN DEFAULT FALSE,
|
|
trigger_after_nags INTEGER DEFAULT 4,
|
|
trigger_after_missed_doses INTEGER DEFAULT 1,
|
|
max_snitches_per_day INTEGER DEFAULT 2,
|
|
require_consent BOOLEAN DEFAULT TRUE,
|
|
consent_given BOOLEAN DEFAULT FALSE,
|
|
snitch_cooldown_hours INTEGER DEFAULT 4,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Snitch Contacts
|
|
CREATE TABLE IF NOT EXISTS snitch_contacts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_uuid UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
contact_name VARCHAR(255) NOT NULL,
|
|
contact_type VARCHAR(50) NOT NULL,
|
|
contact_value VARCHAR(255) NOT NULL,
|
|
priority INTEGER DEFAULT 1,
|
|
notify_all BOOLEAN DEFAULT FALSE,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Snitch Log
|
|
CREATE TABLE IF NOT EXISTS snitch_log (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_uuid UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
contact_id UUID REFERENCES snitch_contacts(id) ON DELETE SET NULL,
|
|
medication_id UUID REFERENCES medications(id) ON DELETE SET NULL,
|
|
trigger_reason VARCHAR(100) NOT NULL,
|
|
snitch_count_today INTEGER DEFAULT 1,
|
|
message_content TEXT,
|
|
sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
delivered BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
-- Adaptive Medication Settings
|
|
CREATE TABLE IF NOT EXISTS adaptive_med_settings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_uuid UUID REFERENCES users(id) ON DELETE CASCADE UNIQUE,
|
|
adaptive_timing_enabled BOOLEAN DEFAULT FALSE,
|
|
adaptive_mode VARCHAR(20) DEFAULT 'shift_all',
|
|
presence_tracking_enabled BOOLEAN DEFAULT FALSE,
|
|
nagging_enabled BOOLEAN DEFAULT TRUE,
|
|
nag_interval_minutes INTEGER DEFAULT 15,
|
|
max_nag_count INTEGER DEFAULT 4,
|
|
quiet_hours_start TIME,
|
|
quiet_hours_end TIME,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- User Discord Presence Tracking
|
|
CREATE TABLE IF NOT EXISTS user_presence (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_uuid UUID REFERENCES users(id) ON DELETE CASCADE UNIQUE,
|
|
discord_user_id VARCHAR(255),
|
|
last_online_at TIMESTAMP,
|
|
last_offline_at TIMESTAMP,
|
|
is_currently_online BOOLEAN DEFAULT FALSE,
|
|
typical_wake_time TIME,
|
|
presence_history JSONB DEFAULT '[]',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Adaptive Medication Schedules
|
|
CREATE TABLE IF NOT EXISTS medication_schedules (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
medication_id UUID REFERENCES medications(id) ON DELETE CASCADE,
|
|
user_uuid UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
base_time TIME NOT NULL,
|
|
adjusted_time TIME,
|
|
adjustment_date DATE NOT NULL,
|
|
adjustment_minutes INTEGER DEFAULT 0,
|
|
nag_count INTEGER DEFAULT 0,
|
|
last_nag_at TIMESTAMP,
|
|
status VARCHAR(20) DEFAULT 'pending',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_med_schedules_user_date ON medication_schedules(user_uuid, adjustment_date);
|
|
CREATE INDEX IF NOT EXISTS idx_med_schedules_status ON medication_schedules(status);
|
|
CREATE INDEX IF NOT EXISTS idx_snitch_log_user_date ON snitch_log(user_uuid, DATE(sent_at));
|
|
CREATE INDEX IF NOT EXISTS idx_snitch_contacts_user ON snitch_contacts(user_uuid, is_active);
|