-- =======================================================
-- FieldLog Server - Unified Schema (MySQL 5.7/8.0+)
-- utf8mb4 + proper indexes + safe defaults
-- =======================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS readings (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  received_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  recorded_at  DATETIME NOT NULL,
  kapi_no      VARCHAR(64)  NOT NULL,
  unit         ENUM('hour','km') NOT NULL,
  value_num    DECIMAL(15,3) NOT NULL,
  location     VARCHAR(128) DEFAULT NULL,
  device_id    VARCHAR(64)  NOT NULL DEFAULT '',
  source_ip    VARCHAR(45)  DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_reading (kapi_no, unit, recorded_at, device_id),
  KEY idx_kapi_unit_time (kapi_no, unit, recorded_at),
  KEY idx_device_time (device_id, recorded_at),
  KEY idx_recorded_at (recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- View for latest reading per (kapi_no, unit)
DROP VIEW IF EXISTS readings_latest;
CREATE VIEW readings_latest AS
SELECT r.*
FROM readings r
JOIN (
  SELECT kapi_no, unit, MAX(recorded_at) AS max_rec
  FROM readings
  GROUP BY kapi_no, unit
) t ON t.kapi_no = r.kapi_no AND t.unit = r.unit AND t.max_rec = r.recorded_at;


-- Fault/jobs tables
CREATE TABLE IF NOT EXISTS jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  kapino VARCHAR(64) NOT NULL,
  job_desc TEXT NOT NULL,
  recorded_at DATETIME NOT NULL,
  device_id VARCHAR(64) DEFAULT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  source_ip VARCHAR(45) DEFAULT NULL,
  status ENUM('received','in_progress','done') NOT NULL DEFAULT 'received',
  PRIMARY KEY (id),
  KEY idx_kapino_time (kapino, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS job_images (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id BIGINT UNSIGNED NOT NULL,
  filename VARCHAR(255) NOT NULL,
  mime VARCHAR(64) NOT NULL,
  size INT UNSIGNED NOT NULL,
  url VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_job (job_id),
  CONSTRAINT fk_job_images_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
