Repository navigation
Expand file tree
/
Copy pathschema.sql
More file actions
138 lines (116 loc) · 4.94 KB
/
Copy pathschema.sql
File metadata and controls
138 lines (116 loc) · 4.94 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
BEGIN;
-- 0) Extensions (fyrst)
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;
-- 1) Grunntöflur
-- users
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email CITEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
display_name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- exercises
CREATE TABLE IF NOT EXISTS exercises (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL UNIQUE,
primary_muscle TEXT,
equipment TEXT,
difficulty TEXT,
is_public BOOLEAN NOT NULL DEFAULT TRUE
);
-- workouts
CREATE TABLE IF NOT EXISTS workouts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
title TEXT NOT NULL,
start_time TIMESTAMPTZ NOT NULL,
end_time TIMESTAMPTZ,
notes TEXT
);
CREATE INDEX IF NOT EXISTS idx_workouts_user_time ON workouts(user_id, start_time DESC);
-- workout_exercises
CREATE TABLE IF NOT EXISTS workout_exercises (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
workout_id UUID NOT NULL REFERENCES workouts(id) ON UPDATE CASCADE ON DELETE CASCADE,
exercise_id UUID NOT NULL REFERENCES exercises(id) ON UPDATE CASCADE ON DELETE RESTRICT,
order_index INT NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_wex_workout ON workout_exercises(workout_id, order_index);
CREATE INDEX IF NOT EXISTS idx_wex_exercise ON workout_exercises(exercise_id);
-- sets
CREATE TABLE IF NOT EXISTS sets (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
workout_exercise_id UUID NOT NULL REFERENCES workout_exercises(id) ON UPDATE CASCADE ON DELETE CASCADE,
set_index INT NOT NULL DEFAULT 1 CHECK (set_index >= 1),
reps INT CHECK (reps IS NULL OR reps >= 0),
weight_kg NUMERIC(6,2) CHECK (weight_kg IS NULL OR weight_kg >= 0),
rir INT CHECK (rir IS NULL OR (rir >= 0 AND rir <= 10)),
duration_sec INT CHECK (duration_sec IS NULL OR duration_sec >= 0),
notes TEXT
);
CREATE INDEX IF NOT EXISTS idx_sets_wex ON sets(workout_exercise_id, set_index);
-- templates
CREATE TABLE IF NOT EXISTS templates (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
name TEXT NOT NULL,
notes TEXT,
CONSTRAINT uq_template_name_per_user UNIQUE (user_id, name)
);
-- template_exercises
CREATE TABLE IF NOT EXISTS template_exercises (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
template_id UUID NOT NULL REFERENCES templates(id) ON UPDATE CASCADE ON DELETE CASCADE,
exercise_id UUID NOT NULL REFERENCES exercises(id) ON UPDATE CASCADE ON DELETE RESTRICT,
order_index INT NOT NULL DEFAULT 0,
default_reps INT CHECK (default_reps IS NULL OR default_reps >= 0),
default_weight_kg NUMERIC(6,2) CHECK (default_weight_kg IS NULL OR default_weight_kg >= 0)
);
CREATE INDEX IF NOT EXISTS idx_tex_template ON template_exercises(template_id, order_index);
-- favorite_exercises (junction)
CREATE TABLE IF NOT EXISTS favorite_exercises (
user_id UUID NOT NULL REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
exercise_id UUID NOT NULL REFERENCES exercises(id) ON UPDATE CASCADE ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (user_id, exercise_id)
);
-- body_metrics
CREATE TABLE IF NOT EXISTS body_metrics (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
recorded_at TIMESTAMPTZ NOT NULL,
weight_kg NUMERIC(6,2),
body_fat_pct NUMERIC(5,2) CHECK (body_fat_pct IS NULL OR (body_fat_pct >= 0 AND body_fat_pct <= 100)),
chest_cm NUMERIC(6,2),
waist_cm NUMERIC(6,2),
hips_cm NUMERIC(6,2)
);
CREATE INDEX IF NOT EXISTS idx_body_metrics_user_time ON body_metrics(user_id, recorded_at DESC);
-- progress_photos
CREATE TABLE IF NOT EXISTS progress_photos (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
taken_at TIMESTAMPTZ NOT NULL,
caption TEXT,
media_key TEXT NOT NULL,
mime_type TEXT NOT NULL,
size_bytes BIGINT CHECK (size_bytes IS NULL OR size_bytes >= 0)
);
CREATE INDEX IF NOT EXISTS idx_photos_user_time ON progress_photos(user_id, taken_at DESC);
-- 2) Endurbyggja view(s) örugglega: fella niður ef til og búa til aftur
DROP VIEW IF EXISTS v_set_volume;
CREATE VIEW v_set_volume AS
SELECT
s.workout_exercise_id,
SUM(COALESCE(s.reps,0) * COALESCE(s.weight_kg,0))::NUMERIC(12,2) AS total_volume
FROM sets s
GROUP BY s.workout_exercise_id;
DROP VIEW IF EXISTS v_recent_workouts;
CREATE VIEW v_recent_workouts AS
SELECT w.*
FROM workouts w
ORDER BY w.start_time DESC;
COMMIT;
END;