-- Apple Health Timeline Explorer (MariaDB)
-- Run this in your MariaDB database (phpMyAdmin or CLI)

CREATE TABLE IF NOT EXISTS meta (
  `key` VARCHAR(128) PRIMARY KEY,
  `value` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS types (
  `type` VARCHAR(255) PRIMARY KEY,
  unit VARCHAR(64),
  kind VARCHAR(32),
  `count` BIGINT,
  first_ts INT NULL,
  last_ts INT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS records (
  id CHAR(40) PRIMARY KEY,           -- sha1
  `type` VARCHAR(255) NOT NULL,
  unit VARCHAR(64),
  value DOUBLE,
  start_ts INT NOT NULL,
  end_ts INT NULL,
  source_name VARCHAR(255),
  device VARCHAR(255),
  creation_ts INT NULL,
  INDEX idx_type_start (`type`, start_ts),
  INDEX idx_start (start_ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agg (
  `type` VARCHAR(255) NOT NULL,
  unit VARCHAR(64),
  granularity VARCHAR(8) NOT NULL,     -- 'hour' or 'day'
  bucket_start INT NOT NULL,
  n BIGINT NOT NULL,
  sum DOUBLE NOT NULL,
  min DOUBLE NOT NULL,
  max DOUBLE NOT NULL,
  PRIMARY KEY (`type`, granularity, bucket_start),
  INDEX idx_agg_lookup (`type`, granularity, bucket_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS workouts (
  id CHAR(40) PRIMARY KEY,
  start_ts INT NOT NULL,
  end_ts INT NOT NULL,
  activity_type VARCHAR(255),
  duration DOUBLE,
  duration_unit VARCHAR(32),
  total_distance DOUBLE,
  total_distance_unit VARCHAR(32),
  total_energy DOUBLE,
  total_energy_unit VARCHAR(32),
  source_name VARCHAR(255),
  INDEX idx_workouts_time (start_ts, end_ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS import_jobs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  filename TEXT NOT NULL,
  mode VARCHAR(16) NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'pending', -- pending|running|done|error
  created_ts INT NOT NULL,
  started_ts INT NULL,
  finished_ts INT NULL,
  message TEXT NULL,
  stats_json LONGTEXT NULL,
  INDEX idx_status_created (status, created_ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
