sqlScripts.sql

CREATE TABLE users (
  user_uuid BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
  email VARCHAR(254) NOT NULL UNIQUE,
  `password` VARCHAR(60) NOT NULL
);

CREATE TABLE sections (
  section_id INT AUTO_INCREMENT PRIMARY KEY,
  user_uuid BINARY(16) NOT NULL,
  label VARCHAR(50) NOT NULL,
  FOREIGN KEY (user_uuid) REFERENCES users(user_uuid) ON DELETE CASCADE
);

CREATE TABLE movements (
  movement_id INT AUTO_INCREMENT PRIMARY KEY,
  section_id INT NOT NULL,
  label VARCHAR(50) NOT NULL,
  FOREIGN KEY (section_id) REFERENCES sections(section_id) ON DELETE CASCADE
);

CREATE TABLE variations (
  variation_id INT AUTO_INCREMENT PRIMARY KEY,
  movement_id INT NOT NULL,
  label VARCHAR(50) NOT NULL DEFAULT '',
  weight FLOAT NULL,
  reps INT NOT NULL DEFAULT 0,
  `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (movement_id) REFERENCES movements(movement_id) ON DELETE CASCADE
);

CREATE TABLE variation_history (
  history_id INT AUTO_INCREMENT PRIMARY KEY,
  variation_id INT NOT NULL,
  weight FLOAT NULL,
  date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (variation_id) REFERENCES variations(variation_id) ON DELETE CASCADE
);

CREATE TABLE tokens (
  token_id INT AUTO_INCREMENT PRIMARY KEY,
  user_uuid BINARY(16) NOT NULL,
  token TEXT NOT NULL,
  expires_at DATETIME NOT NULL,
  FOREIGN KEY (user_uuid) REFERENCES users(user_uuid) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS body_weight (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_uuid BINARY(16) NOT NULL,
  weight FLOAT NOT NULL,
  date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS api_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  key_hash VARCHAR(64) NOT NULL UNIQUE,
  label VARCHAR(100),
  user_uuid BINARY(16) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_used_at DATETIME
);