-- -*- mode: sql; -*-

CREATE TABLE "Prospects" (
"id"	INTEGER UNIQUE,
"email"	TEXT NOT NULL,
"date_time"	TEXT NOT NULL,
PRIMARY KEY("id" AUTOINCREMENT)
);

CREATE TABLE "Users" (
"id"	INTEGER UNIQUE,
"username"	TEXT NOT NULL,
"password_hash"	BLOB NOT NULL,
"real_first_name"	TEXT,
"real_last_name"	TEXT,
"date_of_birth"	TEXT,
"last_login_date_time"	TEXT,
"account_register_date_time"	TEXT,
PRIMARY KEY("id" AUTOINCREMENT)
);

INSERT INTO Users VALUES (
'1',
'testMFT',
'testMFTUserNeedsNoPassword!',
'Testimothy',
'McTestington',
'1996-06-14',
'2024-05-09T14:34:12',
'2024-05-09T14:31:12'
);

CREATE TABLE "Muscle_Groups" (
"id"	INTEGER NOT NULL UNIQUE,
"english_name"	TEXT NOT NULL UNIQUE,
"french_name"	TEXT NOT NULL UNIQUE,
"function"	TEXT,
PRIMARY KEY("id" AUTOINCREMENT)
);

-- Upper body
INSERT INTO Muscle_Groups ("id", "english_name", "french_name", "function") VALUES
('1', 'neck', 'cou', NULL),
('2', 'upper back', 'dos supérieur', NULL),
('3', 'shoulder', 'épaule', NULL),
('4', 'chest', 'torse', NULL),
('5', 'triceps', 'triceps', NULL),
('6', 'biceps', 'biceps', NULL),
('7', 'forearm', 'avant-bras', NULL);

-- Core
INSERT INTO Muscle_Groups ("id", "english_name", "french_name", "function") VALUES
('8', 'abdominals', 'abdominaux', NULL),
('9', 'lower back', 'dos inférieur', NULL),
('10', 'hip', 'hanche', NULL),
('11', 'glutes', 'fessier', NULL);

-- Lower body
INSERT INTO Muscle_Groups ("id", "english_name", "french_name", "function") VALUES
('12', 'quad', 'quadriceps', 'extends the knee'),
('13', 'hamstring', 'ischio-jambier', 'flexes the knee and extends the hip'),
('14', 'calf', 'mollet', NULL);

CREATE TABLE "Muscles" (
"id"	INTEGER NOT NULL UNIQUE,
"muscle_group_id"	INTEGER NOT NULL,
"latin_name"	TEXT NOT NULL UNIQUE,
"english_name"	TEXT,
"function"	TEXT,
FOREIGN KEY("muscle_group_id") REFERENCES Muscle_Groups("id"),
PRIMARY KEY("id" AUTOINCREMENT)
);

-- Upper body
INSERT INTO Muscles ("muscle_group_id", "latin_name", "english_name", "function") VALUES
('2',
'latissimus dorsi',
'lats',
'adducts and extends the shoulder'
),
('2',
'trapezius',
'trap',
'assists in moving and stabilizing the shoulder blades'
),
('3',
'deltoideus',
'delts',
NULL
),
('4',
'pectoralis major',
'pecs',
'performs shoulder flexion and adduction'
),
('4',
'pectorali minor',
'pectoral',
'plays a role in stabilizing the shoulder blade and assists in deep breathing'
),
('5',
'triceps brachii',
'triceps',
NULL
),
('6',
'biceps brachii',
'biceps',
NULL
),
('7',
'extensor carpi radialias brevis',
NULL,
'Responsible for extending the wrist and abducting the hand'
),
('7',
'brachio radialis',
NULL,
'Responsible for flexing the forearm at the elbow joint'
),
('7',
'flexor carpi radialis',
NULL,
'Responsible for flexing the wrist and abducting the hand'
),
('7',
'extensor carpi ulnaris',
NULL,
'Responsible for extending the wrist and adducting the hand'
),
('7',
'flexor carpi ulnaris',
NULL,
'Responsible for flexing the wrist and adducting the hand'
),
('7',
'extensor carpi radialis longus',
NULL,
'Responsible for extending the wrist and abducting the hand'
);

-- Core
INSERT INTO Muscles ("muscle_group_id", "latin_name", "english_name", "function") VALUES
('8',
'abdominalis',
'abs',
'wraps around the spine for protection and stability'
),
('8',
'obliquus externus abdominis',
'external oblique',
NULL
),
('8',
'rectus abdominis',
'six-pack',
NULL
),
('8',
'obliquus internus abdominis',
'internal oblique',
'Located on the front and side of the abdomen'
),
('9',
'erector spinae',
'erectors',
'extends the vertebral column and ensures maintaining an upright posture'
),
('11',
'gluteus maximus',
NULL,
'performs hip extension, outward rotation, and abduction'
),
('11',
'gluteus medius',
NULL,
'plays a role in hip abduction and medial rotation'
),
('11',
'gluteus minimus',
NULL,
'assists in hip abduction and medial rotation, works with the gluteus medius'
);

-- Lower body
INSERT INTO Muscles ("muscle_group_id", "latin_name", "english_name", "function") VALUES
('12',
'quadriceps femoris',
'quad',
NULL
),
('13',
'biceps femoris',
'hamstrings',
NULL
),
('13',
'semitendinosus',
'hamstrings',
NULL
),
('13',
'semimembranosus',
'hamstrings',
NULL
),
('14',
'gastrocnemius',
NULL,
'performs plantar flexion of the foot (pointing toes)'
),
('14',
'soleus',
NULL,
'assists the gastrocnemius in plantar flexion and helps maintain posture'
);

CREATE TABLE "Exercises" (
"id"	INTEGER NOT NULL UNIQUE,
"primary_muscle_group_id"	INTEGER NOT NULL,
"english_name"	TEXT NOT NULL,
"french_name"	TEXT,
FOREIGN KEY("primary_muscle_group_id") REFERENCES Muscle_Groups("id"),
PRIMARY KEY("id" AUTOINCREMENT)
);

INSERT INTO Exercises ("primary_muscle_group_id", "english_name", "french_name") VALUES
('02', 'cable row', 'rame avec cable'),
('02', 'deadlift', 'soulevé de terre'),
('02', 'lat pulldown', NULL),
('02', 'pull up', 'traction'),
('03', 'shoulder press (dumbell)', NULL),
('04', 'bench press', 'développé couché'),
('04', 'decline bench press', 'développé couché, incliné négatif'),
('04', 'incline bench press', 'développé couché, incliné positif'),
('05', 'tricep extension', 'extension triceps'),
('06', 'bicep curl', 'flexion biceps'),
('06', 'hammer curl', 'curl marteau'),
('09', 'snatch', 'arraché'),
('12', 'barbell squat', 'flexion sur jambes avec barre'),
('12', 'clean and jerk', 'épaulé-jeté'),
('12', 'goblet squat', NULL),
('12', 'leg extension', NULL),
('13', 'leg curl', NULL);

CREATE TABLE "Workouts" (
"id"				INTEGER UNIQUE,
"user_id"			INTEGER NOT NULL,
"date"				TEXT NOT NULL,
"primary_muscle_group_id"	INTEGER,
FOREIGN KEY("user_id") REFERENCES Users("id"),
PRIMARY KEY("id" AUTOINCREMENT)
);

CREATE TABLE "Sets" (
"id"   	     	INTEGER NOT NULL UNIQUE,
"workout_id"	INTEGER NOT NULL,
"exercise_id"	INTEGER NOT NULL,
"set_group"	INTEGER NOT NULL,
"set_number"	INTEGER NOT NULL,
"reps"		INTEGER NOT NULL,
"load"		INTEGER,
"rpe"		INTEGER,
"rest_after"	NUMERIC,
FOREIGN KEY("workout_id") REFERENCES Workouts("id"),
FOREIGN KEY("exercise_id") REFERENCES Exercises("id"),
PRIMARY KEY("id" AUTOINCREMENT)
);

CREATE TABLE "Weights" (
"id"   	     INTEGER   UNIQUE,
"user_id"    INTEGER   NOT NULL,
"date"	     TEXT      NOT NULL,
"value"	     NUMERIC   NOT NULL,
PRIMARY KEY("id" AUTOINCREMENT)
);

-- Local Variables:
-- sql-product: sqlite
-- End: