-- Districthive Research Labs · schema v0
CREATE TABLE instruments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) UNIQUE NOT NULL,
  name VARCHAR(120) NOT NULL,
  product VARCHAR(160) NOT NULL,        -- instrument source: named product
  vendor_link VARCHAR(200) DEFAULT '',
  data_path VARCHAR(200) DEFAULT '',    -- how data reaches us (API/LAN/SD...)
  zone VARCHAR(80) DEFAULT '',          -- site zone / control site
  cadence_min INT DEFAULT 60,
  status ENUM('ok','late','silent','fault','pending') DEFAULT 'pending',
  is_public TINYINT DEFAULT 1,
  last_alert_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE streams (
  id INT AUTO_INCREMENT PRIMARY KEY,
  instrument_id INT NOT NULL,
  code VARCHAR(48) UNIQUE NOT NULL,
  label VARCHAR(120) NOT NULL,
  unit VARCHAR(24) DEFAULT '',
  page VARCHAR(40) NOT NULL,            -- which page accounts for this stream
  mode ENUM('placeholder','live') DEFAULT 'placeholder',
  visibility ENUM('public','keyed','hidden') DEFAULT 'public',
  series_slot TINYINT DEFAULT 1,        -- fixed categorical colour slot
  embargo_note VARCHAR(200) DEFAULT '',
  FOREIGN KEY (instrument_id) REFERENCES instruments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE readings (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  stream_id INT NOT NULL,
  ts DATETIME NOT NULL,
  value DOUBLE NOT NULL,
  qc_flag TINYINT DEFAULT 0,
  is_placeholder TINYINT DEFAULT 0,
  INDEX ix_sr (stream_id, ts),
  FOREIGN KEY (stream_id) REFERENCES streams(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE files_raw (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  stream_id INT NULL,
  path VARCHAR(255) NOT NULL,
  sha256 CHAR(64) NOT NULL,
  bytes BIGINT DEFAULT 0,
  pulled_at DATETIME NOT NULL,
  source_url VARCHAR(255) DEFAULT '',
  is_placeholder TINYINT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  instrument_id INT NULL,
  ts DATETIME NOT NULL,
  class ENUM('fault','gap','recovery','correction','audit','info') NOT NULL,
  detail VARCHAR(400) NOT NULL,
  is_public TINYINT DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE activity_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ts DATETIME NOT NULL,
  actor ENUM('cron','admin','system','api','visitor') NOT NULL,
  action VARCHAR(80) NOT NULL,
  detail VARCHAR(400) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE api_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  key_hash CHAR(64) NOT NULL,           -- sha256 of the key
  label VARCHAR(80) NOT NULL,
  active TINYINT DEFAULT 1,
  created_at DATETIME NOT NULL,
  last_used_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE annotations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ts DATETIME NOT NULL,
  scope VARCHAR(40) DEFAULT 'site',     -- site | construction | page code
  text VARCHAR(300) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE media (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  camera_code VARCHAR(40) NOT NULL,
  ts DATETIME NOT NULL,
  path VARCHAR(255) NOT NULL,
  kind ENUM('still','timelapse','live') DEFAULT 'still',
  is_public TINYINT DEFAULT 1,
  is_placeholder TINYINT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;