-- MySQL / MariaDB schema for fieldlog
CREATE TABLE IF NOT EXISTS readings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  kapi_no VARCHAR(64) NOT NULL,
  hours DECIMAL(10,2) NULL,
  km DECIMAL(10,2) NULL,
  device_id VARCHAR(64) NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  accuracy FLOAT NULL,
  recorded_at DATETIME NOT NULL,
  reading_date DATE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_day_kapi (kapi_no, reading_date),
  KEY idx_updated_at (updated_at),
  KEY idx_kapi_date (kapi_no, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
