CREATE TABLE IF NOT EXISTS `ns20_sensor_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `deviceid` VARCHAR(64) NOT NULL COMMENT 'ESP32 device id',
  `datetime` DATETIME NOT NULL COMMENT 'device RTC/GPS datetime',
  `temp` DECIMAL(8,2) NULL COMMENT 'air temperature',
  `humidity` DECIMAL(8,2) NULL COMMENT 'air humidity',
  `cds` INT NULL COMMENT 'CDS/light sensor value',
  `gtemp` DECIMAL(8,2) NULL COMMENT 'ground temperature',
  `soil_moisture` DECIMAL(8,2) NULL COMMENT 'soil moisture',
  `ver` VARCHAR(32) NULL COMMENT 'firmware version',
  `lat` DECIMAL(11,7) NULL COMMENT 'latitude',
  `lng` DECIMAL(11,7) NULL COMMENT 'longitude',
  `alt` DECIMAL(10,2) NULL COMMENT 'altitude',
  `ipaddr` VARCHAR(45) NULL COMMENT 'device local ip address',
  `country` VARCHAR(64) NULL,
  `type` VARCHAR(64) NULL,
  `source_ip` VARCHAR(45) NULL COMMENT 'remote address seen by Apache/PHP',
  `raw_json` LONGTEXT NULL COMMENT 'original JSON body',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_device_datetime` (`deviceid`, `datetime`),
  KEY `idx_datetime` (`datetime`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_type` (`type`),
  KEY `idx_country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
