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

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('viewer','operator','admin') NOT NULL DEFAULT 'viewer',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS login_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ip_address VARCHAR(45) NOT NULL,
  email VARCHAR(190) NOT NULL,
  successful TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_login_throttle(ip_address,email,created_at),
  INDEX idx_login_cleanup(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS devices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_uid CHAR(36) NOT NULL UNIQUE,
  serial_number VARCHAR(64) NOT NULL UNIQUE,
  asset_number VARCHAR(96) NULL,
  product_model VARCHAR(96) NOT NULL DEFAULT 'H2O Ultra DI',
  hardware_model VARCHAR(96) NOT NULL,
  hardware_revision VARCHAR(32) NOT NULL,
  customer_name VARCHAR(190) NULL,
  installation_address VARCHAR(255) NULL,
  firmware_version VARCHAR(48) NULL,
  operation_state VARCHAR(32) NULL,
  alarm_code VARCHAR(64) NULL,
  last_ip VARCHAR(45) NULL,
  last_seen_at DATETIME NULL,
  reported_config_revision BIGINT UNSIGNED NOT NULL DEFAULT 0,
  desired_config_revision BIGINT UNSIGNED NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_devices_last_seen(last_seen_at),
  INDEX idx_devices_customer(customer_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_credentials (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id BIGINT UNSIGNED NOT NULL,
  secret_ciphertext TEXT NOT NULL,
  key_version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at DATETIME NULL,
  CONSTRAINT fk_credentials_device FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE CASCADE,
  INDEX idx_credentials_active(device_id,revoked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_nonces (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id BIGINT UNSIGNED NOT NULL,
  nonce VARCHAR(96) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_device_nonce(device_id,nonce),
  INDEX idx_nonces_expiry(expires_at),
  CONSTRAINT fk_nonces_device FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_telemetry (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id BIGINT UNSIGNED NOT NULL,
  sequence_no BIGINT UNSIGNED NOT NULL DEFAULT 0,
  measured_at DATETIME NOT NULL,
  pressure_psi DECIMAL(10,3) NULL,
  flow_lpm DECIMAL(10,4) NULL,
  conductivity_in_us DECIMAL(12,3) NULL,
  conductivity_post_ro_us DECIMAL(12,3) NULL,
  conductivity_out_us DECIMAL(12,3) NULL,
  temperature_c DECIMAL(8,3) NULL,
  state_code VARCHAR(32) NULL,
  alarm_code VARCHAR(64) NULL,
  payload_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_telemetry_device FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE CASCADE,
  UNIQUE KEY uq_telemetry_sequence(device_id,sequence_no),
  INDEX idx_telemetry_device_time(device_id,measured_at),
  INDEX idx_telemetry_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_config_versions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id BIGINT UNSIGNED NOT NULL,
  revision BIGINT UNSIGNED NOT NULL,
  status ENUM('desired','applied','rejected','superseded') NOT NULL,
  config_json JSON NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  applied_at DATETIME NULL,
  CONSTRAINT fk_config_device FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_config_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uq_config_revision(device_id,revision),
  INDEX idx_config_status(device_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_commands (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  command_uid CHAR(36) NOT NULL UNIQUE,
  device_id BIGINT UNSIGNED NOT NULL,
  command_type VARCHAR(64) NOT NULL,
  payload_json JSON NULL,
  status ENUM('pending','delivered','acknowledged','rejected','failed','expired','cancelled') NOT NULL DEFAULT 'pending',
  result_json JSON NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  delivered_at DATETIME NULL,
  last_delivery_at DATETIME NULL,
  delivery_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  acknowledged_at DATETIME NULL,
  expires_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_commands_device FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_commands_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_commands_pending(device_id,status,expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  device_id BIGINT UNSIGNED NULL,
  action VARCHAR(96) NOT NULL,
  context_json JSON NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_device FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE SET NULL,
  INDEX idx_audit_created(created_at),
  INDEX idx_audit_device(device_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;