39 lines
1.7 KiB
MySQL
39 lines
1.7 KiB
MySQL
|
-- Fitbit integrations table (depends on users)
|
||
|
|
CREATE TABLE fitbit_integrations (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
fitbit_user_id VARCHAR(255),
|
||
|
|
access_token TEXT,
|
||
|
|
refresh_token TEXT,
|
||
|
|
token_expires_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
goal_steps_min INTEGER DEFAULT 8000,
|
||
|
|
goal_steps_max INTEGER DEFAULT 10000,
|
||
|
|
goal_floors_min INTEGER DEFAULT 8,
|
||
|
|
goal_floors_max INTEGER DEFAULT 10,
|
||
|
|
goal_azm_min INTEGER DEFAULT 22,
|
||
|
|
goal_azm_max INTEGER DEFAULT 44,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
CONSTRAINT fitbit_integrations_user_id_unique UNIQUE (user_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_fitbit_integrations_user_id ON fitbit_integrations(user_id);
|
||
|
|
CREATE UNIQUE INDEX idx_fitbit_integrations_fitbit_user_id ON fitbit_integrations(fitbit_user_id) WHERE fitbit_user_id IS NOT NULL;
|
||
|
|
|
||
|
|
-- Fitbit daily stats table (depends on users and fitbit_integrations)
|
||
|
|
CREATE TABLE fitbit_daily_stats (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
date DATE NOT NULL,
|
||
|
|
steps INTEGER DEFAULT 0,
|
||
|
|
floors INTEGER DEFAULT 0,
|
||
|
|
active_zone_minutes INTEGER DEFAULT 0,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
CONSTRAINT fitbit_daily_stats_user_date_unique UNIQUE (user_id, date)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_fitbit_daily_stats_user_id ON fitbit_daily_stats(user_id);
|
||
|
|
CREATE INDEX idx_fitbit_daily_stats_date ON fitbit_daily_stats(date);
|
||
|
|
CREATE INDEX idx_fitbit_daily_stats_user_date ON fitbit_daily_stats(user_id, date);
|