CREATE TABLE IF NOT EXISTS meter_samples (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  gateway_id INT NOT NULL,
  meter_id INT NOT NULL,
  serial_number VARCHAR(64) NOT NULL,
  sampled_at DATETIME NOT NULL,
  voltage_l1 DOUBLE NULL,
  voltage_l2 DOUBLE NULL,
  voltage_l3 DOUBLE NULL,
  current_l1 DOUBLE NULL,
  current_l2 DOUBLE NULL,
  current_l3 DOUBLE NULL,
  active_power_total DOUBLE NULL,
  active_energy_imported_total DOUBLE NULL,
  source VARCHAR(32) NOT NULL,
  raw_payload_json LONGTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY meter_samples_gateway_serial_sampled_at_uq (gateway_id, serial_number, sampled_at),
  KEY meter_samples_sampled_at_idx (sampled_at),
  KEY meter_samples_serial_number_idx (serial_number)
);

CREATE TABLE IF NOT EXISTS ingest_requests (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  gateway_id INT NULL,
  meter_id INT NULL,
  serial_number VARCHAR(64) NULL,
  status VARCHAR(32) NOT NULL,
  error_text TEXT NULL,
  raw_payload_json LONGTEXT NOT NULL,
  KEY ingest_requests_received_at_idx (received_at),
  KEY ingest_requests_status_idx (status)
);