-- 001_create_tables.sql
CREATE TABLE IF NOT EXISTS users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  password_hash VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS devices (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NULL,
  device_uuid VARCHAR(128) UNIQUE,
  model VARCHAR(100),
  os_version VARCHAR(50),
  device_token VARCHAR(128) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sessions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  device_id BIGINT NOT NULL,
  user_id BIGINT NULL,
  started_at TIMESTAMP NOT NULL,
  stopped_at TIMESTAMP NULL,
  metadata JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS telemetry_batches (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  session_id BIGINT NOT NULL,
  batch_id VARCHAR(128) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_batch_session (session_id, batch_id),
  FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS telemetry (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  session_id BIGINT NOT NULL,
  sample_ts TIMESTAMP NOT NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  speed_m_s FLOAT NULL,
  accel_x FLOAT NULL,
  accel_y FLOAT NULL,
  accel_z FLOAT NULL,
  stroke_number INT NULL,
  stroke_rate_spm FLOAT NULL,
  stroke_distance_m FLOAT NULL,
  stroke_duration_ms INT NULL,
  cumulative_distance_m FLOAT NULL,
  payload_json JSON NULL,
  batch_id BIGINT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (batch_id) REFERENCES telemetry_batches(id) ON DELETE SET NULL,
  INDEX idx_session_ts (session_id, sample_ts),
  INDEX idx_stroke (stroke_number)
);
