unit uNILMManager;

{
  NILM 장비 / 센서 설정 CRUD 매니저
  - DB(PostgreSQL) 와 연동하여 장비·센서 설정을 관리
  - JSON 내보내기 / 가져오기 지원
}

interface

uses
  System.SysUtils, System.Classes, System.Generics.Collections,
  System.JSON,
  FireDAC.Comp.Client, FireDAC.Stan.Param,
  Data.DB,
  U_DM, uNILMTypes;

type
  TNILMManager = class
  private
    function GetConnection: TFDConnection;
    procedure ExecSQL(const ASQL: string; AParams: array of Variant);
    function  QuerySingleInt(const ASQL: string; AParams: array of Variant): Integer;

    procedure LoadSensorsForDevice(ADeviceID: Integer; AList: TNILMSensorConfigList);
  public
    // ─── DDL ────────────────────────────────────────────────────────
    /// DB 테이블이 없으면 자동 생성
    procedure EnsureTables;

    // ─── 장비 CRUD ──────────────────────────────────────────────────
    /// DB에서 전체 장비 목록 로드
    function  LoadDevices: TList<TNILMDevice>;
    /// device_id 로 단일 장비 로드
    function  LoadDevice(ADeviceID: Integer): TNILMDevice;
    /// 장비 저장 (ID=0 이면 INSERT, 아니면 UPDATE)
    function  SaveDevice(var ADevice: TNILMDevice): Boolean;
    /// 장비 삭제 (연관 센서도 함께 삭제)
    function  DeleteDevice(ADeviceID: Integer): Boolean;
    /// 장비 device_id 중복 여부 확인
    function  DeviceIDExists(ADeviceID, AExcludeID: Integer): Boolean;

    // ─── 센서 설정 CRUD ─────────────────────────────────────────────
    /// 특정 장비의 센서 설정 저장 (UPSERT)
    function  SaveSensors(ADeviceID: Integer; AList: TNILMSensorConfigList): Boolean;

    // ── JSON 내보내기 / 가져오기 ──────────────────────────────────
    procedure ExportToJSON(const AFilePath: string; ADevices: TList<TNILMDevice>);
    function  ImportFromJSON(const AFilePath: string): TList<TNILMDevice>;

    // ── 히스토리 데이터 저장 ─────────────────────────────────
    /// MQTT 수신 데이터를 nilm_data에 저장
    procedure SaveNILMData(ADeviceID, ASeq, APreSeq, ACommStatus: Integer;
      const APayloadJSON: string; AData: TJSONObject; ASensorList: TNILMSensorConfigList);
    /// 이벤트 로그 저장
    procedure SaveEventLog(const AEventType: string; ADeviceID: Integer;
      const AMessage, ADetail: string);
    /// 히스토리 조회 (TFDQuery 반환 — 호출자가 Free 책임)
    function  QueryNILMHistory(ADeviceID: Integer;
      ADateFrom, ADateTo: TDateTime;
      ACommStatusFilter: Integer;   // -1=전체, 0=정상, 255=오류
      AMaxRows: Integer): TFDQuery;
    /// 이벤트 로그 조회
    function  QueryEventLog(ADeviceID: Integer;
      ADateFrom, ADateTo: TDateTime;
      const AEventType: string;     // ''=전체
      AMaxRows: Integer): TFDQuery;
    /// 장비목록 콤보용 (id, name)
    function  LoadDevicesSimple: TFDQuery;
  end;

implementation

uses
  System.IOUtils;

{ TNILMManager }

function TNILMManager.GetConnection: TFDConnection;
begin
  Result := DM.fdConnNilm;
  if not Result.Connected then
    raise Exception.Create('DB(NILM)가 연결되지 않았습니다. DB 탭에서 먼저 연결해주세요.');
end;

procedure TNILMManager.ExecSQL(const ASQL: string; AParams: array of Variant);
var
  Q: TFDQuery;
  i: Integer;
begin
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    Q.SQL.Text   := ASQL;
    for i := 0 to High(AParams) do
      Q.Params[i].Value := AParams[i];
    Q.ExecSQL;
  finally
    Q.Free;
  end;
end;

function TNILMManager.QuerySingleInt(const ASQL: string;
  AParams: array of Variant): Integer;
var
  Q: TFDQuery;
  i: Integer;
begin
  Result := 0;
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    Q.SQL.Text   := ASQL;
    for i := 0 to High(AParams) do
      Q.Params[i].Value := AParams[i];
    Q.Open;
    if not Q.IsEmpty then
      Result := Q.Fields[0].AsInteger;
  finally
    Q.Free;
  end;
end;

procedure TNILMManager.EnsureTables;
const
  DDL_DEVICE =
    'CREATE TABLE IF NOT EXISTS nilm_device (' +
    '  id           SERIAL PRIMARY KEY,' +
    '  device_id    INTEGER NOT NULL UNIQUE,' +
    '  device_name  VARCHAR(100) NOT NULL DEFAULT '''',' +
    '  location     VARCHAR(200),' +
    '  phase_type   VARCHAR(10)  NOT NULL DEFAULT ''3PHASE'',' +
    '  is_active    BOOLEAN      NOT NULL DEFAULT TRUE,' +
    '  mqtt_topic   VARCHAR(200),' +
    '  memo         TEXT,' +
    '  created_at   TIMESTAMP    NOT NULL DEFAULT NOW(),' +
    '  updated_at   TIMESTAMP    NOT NULL DEFAULT NOW()' +
    ')';

  DDL_SENSOR_CONFIG =
    'CREATE TABLE IF NOT EXISTS nilm_sensor_config (' +
    '  id            SERIAL PRIMARY KEY,' +
    '  device_id     INTEGER      NOT NULL,' +
    '  sensor_group  VARCHAR(50)  NOT NULL,' +
    '  field_key     VARCHAR(50)  NOT NULL,' +
    '  field_label   VARCHAR(100),' +
    '  is_enabled    BOOLEAN      NOT NULL DEFAULT TRUE,' +
    '  display_order INTEGER      NOT NULL DEFAULT 0,' +
    '  json_key      VARCHAR(100),' +
    '  CONSTRAINT fk_nsc_device FOREIGN KEY (device_id) REFERENCES nilm_device(device_id) ON DELETE CASCADE,' +
    '  CONSTRAINT uq_nsc UNIQUE (device_id, field_key)' +
    ')';

  DDL_DATA =
    'CREATE TABLE IF NOT EXISTS nilm_data (' +
    '  id          BIGSERIAL PRIMARY KEY,' +
    '  device_id   INTEGER   NOT NULL UNIQUE,' +
    '  seq         INTEGER,' +
    '  pre_seq     INTEGER,' +
    '  comm_status INTEGER   DEFAULT 0,' +
    '  received_at TIMESTAMP NOT NULL DEFAULT NOW(),' +
    '  payload_json JSONB,' +
    '  pf_l1 NUMERIC(8,4), pk_l1 NUMERIC(10,4), v_l1 NUMERIC(8,2), i_l1 NUMERIC(10,4), w_l1 NUMERIC(10,2), var_l1 NUMERIC(10,2), va_l1 NUMERIC(10,2),' +
    '  pf_l2 NUMERIC(8,4), pk_l2 NUMERIC(10,4), v_l2 NUMERIC(8,2), i_l2 NUMERIC(10,4), w_l2 NUMERIC(10,2), var_l2 NUMERIC(10,2), va_l2 NUMERIC(10,2),' +
    '  pf_l3 NUMERIC(8,4), pk_l3 NUMERIC(10,4), v_l3 NUMERIC(8,2), i_l3 NUMERIC(10,4), w_l3 NUMERIC(10,2), var_l3 NUMERIC(10,2), va_l3 NUMERIC(10,2),' +
    '  temp_int NUMERIC(6,2), temp_ext NUMERIC(6,2),' +
    '  acc_x NUMERIC(8,4), acc_y NUMERIC(8,4), acc_z NUMERIC(8,4), vibration NUMERIC(8,4),' +
    '  gyro_x NUMERIC(8,4), gyro_y NUMERIC(8,4), gyro_z NUMERIC(8,4), tilt_angle NUMERIC(6,2)' +
    '); ' +
    'CREATE INDEX IF NOT EXISTS idx_nilm_data_device_received ON nilm_data (device_id, received_at DESC)';

  DDL_EVENT_LOG =
    'CREATE TABLE IF NOT EXISTS nilm_event_log (' +
    '  id          BIGSERIAL   PRIMARY KEY,' +
    '  event_time  TIMESTAMP   NOT NULL DEFAULT NOW(),' +
    '  event_type  VARCHAR(20) NOT NULL,' +
    '  device_id   INTEGER,' +
    '  message     TEXT,' +
    '  detail      TEXT' +
    '); ' +
    'CREATE INDEX IF NOT EXISTS idx_nilm_event_log_time ON nilm_event_log (event_time DESC); ' +
    'CREATE INDEX IF NOT EXISTS idx_nilm_event_log_device ON nilm_event_log (device_id, event_time DESC)';
begin
  ExecSQL(DDL_DEVICE, []);
  ExecSQL(DDL_SENSOR_CONFIG, []);
  ExecSQL(DDL_DATA, []);
  ExecSQL(DDL_EVENT_LOG, []);
end;

{ ─── 장비 CRUD ─────────────────────────────────────────────────────── }

function TNILMManager.LoadDevices: TList<TNILMDevice>;
var
  Q      : TFDQuery;
  Device : TNILMDevice;
begin
  Result := TList<TNILMDevice>.Create;
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    Q.SQL.Text :=
      'SELECT id, device_id, device_name, location, phase_type, ' +
      '       is_active, mqtt_topic, memo, created_at, updated_at ' +
      'FROM nilm_device ORDER BY device_id';
    Q.Open;
    while not Q.EOF do
    begin
      Device              := TNILMDevice.CreateNew;
      Device.ID           := Q.FieldByName('id').AsInteger;
      Device.DeviceID     := Q.FieldByName('device_id').AsInteger;
      Device.DeviceName   := Q.FieldByName('device_name').AsString;
      Device.Location     := Q.FieldByName('location').AsString;
      Device.PhaseType    := Q.FieldByName('phase_type').AsString;
      Device.IsActive     := Q.FieldByName('is_active').AsBoolean;
      Device.MqttTopic    := Q.FieldByName('mqtt_topic').AsString;
      Device.Memo         := Q.FieldByName('memo').AsString;
      Device.CreatedAt    := Q.FieldByName('created_at').AsDateTime;
      Device.UpdatedAt    := Q.FieldByName('updated_at').AsDateTime;

      LoadSensorsForDevice(Device.DeviceID, Device.SensorList);

      Result.Add(Device);
      Q.Next;
    end;
  finally
    Q.Free;
  end;
end;

function TNILMManager.LoadDevice(ADeviceID: Integer): TNILMDevice;
var
  Q: TFDQuery;
begin
  Result := TNILMDevice.CreateNew;
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    Q.SQL.Text :=
      'SELECT id, device_id, device_name, location, phase_type, ' +
      '       is_active, mqtt_topic, memo, created_at, updated_at ' +
      'FROM nilm_device WHERE device_id = :did';
    Q.ParamByName('did').AsInteger := ADeviceID;
    Q.Open;
    if not Q.IsEmpty then
    begin
      Result.ID        := Q.FieldByName('id').AsInteger;
      Result.DeviceID  := Q.FieldByName('device_id').AsInteger;
      Result.DeviceName:= Q.FieldByName('device_name').AsString;
      Result.Location  := Q.FieldByName('location').AsString;
      Result.PhaseType := Q.FieldByName('phase_type').AsString;
      Result.IsActive  := Q.FieldByName('is_active').AsBoolean;
      Result.MqttTopic := Q.FieldByName('mqtt_topic').AsString;
      Result.Memo      := Q.FieldByName('memo').AsString;
      Result.CreatedAt := Q.FieldByName('created_at').AsDateTime;
      Result.UpdatedAt := Q.FieldByName('updated_at').AsDateTime;
      LoadSensorsForDevice(Result.DeviceID, Result.SensorList);
    end;
  finally
    Q.Free;
  end;
end;

procedure TNILMManager.LoadSensorsForDevice(ADeviceID: Integer;
  AList: TNILMSensorConfigList);
var
  Q  : TFDQuery;
  SC : TNILMSensorConfig;
begin
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    Q.SQL.Text :=
      'SELECT id, device_id, sensor_group, field_key, field_label, is_enabled, display_order, json_key ' +
      'FROM nilm_sensor_config WHERE device_id = :did ORDER BY display_order';
{
      'SELECT id, device_id, sensor_group, field_key, field_label, ' +
      '       is_enabled, display_order ' +
      'FROM nilm_sensor_config WHERE device_id = :did ORDER BY display_order';
}
    Q.ParamByName('did').AsInteger := ADeviceID;
    Q.Open;
    while not Q.EOF do
    begin
      SC.ID           := Q.FieldByName('id').AsInteger;
      SC.DeviceID     := Q.FieldByName('device_id').AsInteger;
      SC.SensorGroup  := Q.FieldByName('sensor_group').AsString;
      SC.FieldKey     := Q.FieldByName('field_key').AsString;
      SC.FieldLabel   := Q.FieldByName('field_label').AsString;
      SC.IsEnabled    := Q.FieldByName('is_enabled').AsBoolean;
      SC.DisplayOrder := Q.FieldByName('display_order').AsInteger;
      SC.JsonKey      := Q.FieldByName('json_key').AsString;
      AList.Add(SC);
      Q.Next;
    end;
  finally
    Q.Free;
  end;
end;

function SafeDBStr(const S: string): string;
begin
  if S = '' then Result := '' else Result := StringReplace(S, #0, '', [rfReplaceAll]);
end;

function TNILMManager.SaveDevice(var ADevice: TNILMDevice): Boolean;
var
  Q        : TFDQuery;
  IsInsert : Boolean;
begin
  Result := False;
  ADevice.MqttTopic := ADevice.BuildTopic;
  ADevice.UpdatedAt := Now;

  IsInsert := (ADevice.ID = 0);

  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    if IsInsert then
    begin
      Q.SQL.Text :=
        'INSERT INTO nilm_device ' +
        '  (device_id, device_name, location, phase_type, is_active, mqtt_topic, memo, created_at, updated_at) ' +
        'VALUES (:did, :dname, :loc, :ptype, CAST(:active AS BOOLEAN), :topic, :memo, NOW(), NOW()) ' +
        'RETURNING id';
      Q.ParamByName('did').AsInteger    := ADevice.DeviceID;
      Q.ParamByName('dname').AsString   := ADevice.DeviceName;
      Q.ParamByName('loc').AsString     := ADevice.Location;
      Q.ParamByName('ptype').AsString   := ADevice.PhaseType;
      if ADevice.IsActive then
        Q.ParamByName('active').AsString := 'true'
      else
        Q.ParamByName('active').AsString := 'false';
      Q.ParamByName('topic').AsString   := ADevice.MqttTopic;
      Q.ParamByName('memo').AsString    := ADevice.Memo;
      Q.Open;
      if not Q.IsEmpty then
        ADevice.ID := Q.Fields[0].AsInteger;
    end
    else
    begin
      Q.SQL.Text :=
        'UPDATE nilm_device SET ' +
        '  device_id = :did, device_name = :dname, location = :loc,' +
        '  phase_type = :ptype, is_active = CAST(:active AS BOOLEAN), mqtt_topic = :topic,' +
        '  memo = :memo, updated_at = NOW() ' +
        'WHERE id = :id';
      Q.ParamByName('did').AsInteger    := ADevice.DeviceID;
      Q.ParamByName('dname').AsString   := ADevice.DeviceName;
      Q.ParamByName('loc').AsString     := ADevice.Location;
      Q.ParamByName('ptype').AsString   := ADevice.PhaseType;
      if ADevice.IsActive then
        Q.ParamByName('active').AsString := 'true'
      else
        Q.ParamByName('active').AsString := 'false';
      Q.ParamByName('topic').AsString   := ADevice.MqttTopic;
      Q.ParamByName('memo').AsString    := ADevice.Memo;
      Q.ParamByName('id').AsInteger     := ADevice.ID;
      Q.ExecSQL;
    end;

    // 센서 설정도 저장
    if Assigned(ADevice.SensorList) and (ADevice.SensorList.Count > 0) then
      SaveSensors(ADevice.DeviceID, ADevice.SensorList);

    Result := True;
  finally
    Q.Free;
  end;
end;

function TNILMManager.DeleteDevice(ADeviceID: Integer): Boolean;
var
  Q: TFDQuery;
begin
  Result := False;
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    // 센서 설정은 ON DELETE CASCADE 로 자동 삭제
    Q.SQL.Text := 'DELETE FROM nilm_device WHERE device_id = :did';
    Q.ParamByName('did').AsInteger := ADeviceID;
    Q.ExecSQL;
    Result := True;
  finally
    Q.Free;
  end;
end;

function TNILMManager.DeviceIDExists(ADeviceID, AExcludeID: Integer): Boolean;
var
  Cnt: Integer;
begin
  Cnt := QuerySingleInt(
    'SELECT COUNT(*) FROM nilm_device WHERE device_id = :did AND id <> :eid',
    [ADeviceID, AExcludeID]);
  Result := (Cnt > 0);
end;

{ ─── 센서 설정 ─────────────────────────────────────────────────────── }

function TNILMManager.SaveSensors(ADeviceID: Integer;
  AList: TNILMSensorConfigList): Boolean;
var
  Q  : TFDQuery;
  SC : TNILMSensorConfig;
begin
  Result := False;
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    for SC in AList do
    begin
      // UPSERT: 같은 (device_id, field_key) 가 있으면 UPDATE, 없으면 INSERT
      Q.SQL.Text :=
      {
        'INSERT INTO nilm_sensor_config ' +
        '  (device_id, sensor_group, field_key, field_label, is_enabled, display_order) ' +
        'VALUES (:did, :sgrp, :fkey, :flbl, CAST(:enabled AS BOOLEAN), :dord) ' +
        'ON CONFLICT (device_id, field_key) DO UPDATE SET ' +
        '  sensor_group  = EXCLUDED.sensor_group,' +
        }
        'INSERT INTO nilm_sensor_config ' +
        '  (device_id, sensor_group, field_key, field_label, is_enabled, display_order, json_key) ' +
        'VALUES (:did, :sgrp, :fkey, :flbl, CAST(:enabled AS BOOLEAN), :dord, :jkey) ' +
        'ON CONFLICT (device_id, field_key) DO UPDATE SET ' +
        '  sensor_group  = EXCLUDED.sensor_group,' +
        '  field_label   = EXCLUDED.field_label,' +
        '  is_enabled    = EXCLUDED.is_enabled,' +
        '  display_order = EXCLUDED.display_order,' +
        '  json_key      = EXCLUDED.json_key';

      Q.ParamByName('did').AsInteger    := ADeviceID;
      Q.ParamByName('sgrp').AsString    := SC.SensorGroup;
      Q.ParamByName('fkey').AsString    := SC.FieldKey;
      Q.ParamByName('flbl').AsString    := SC.FieldLabel;
      if SC.IsEnabled then
        Q.ParamByName('enabled').AsString := 'true'
      else
        Q.ParamByName('enabled').AsString := 'false';
      Q.ParamByName('dord').AsInteger   := SC.DisplayOrder;
      Q.ParamByName('jkey').AsString    := SC.JsonKey;
      Q.ExecSQL;
    end;
    Result := True;
  finally
    Q.Free;
  end;
end;

{ ─── JSON 내보내기 / 가져오기 ───────────────────────────────────────── }

procedure TNILMManager.ExportToJSON(const AFilePath: string;
  ADevices: TList<TNILMDevice>);
var
  Root    : TJSONObject;
  DevArr  : TJSONArray;
  DevObj  : TJSONObject;
  SenArr  : TJSONArray;
  SenObj  : TJSONObject;
  Device  : TNILMDevice;
  SC      : TNILMSensorConfig;
begin
  Root := TJSONObject.Create;
  try
    DevArr := TJSONArray.Create;
    for Device in ADevices do
    begin
      DevObj := TJSONObject.Create;
      DevObj.AddPair('id',          TJSONNumber.Create(Device.ID));
      DevObj.AddPair('device_id',   TJSONNumber.Create(Device.DeviceID));
      DevObj.AddPair('device_name', Device.DeviceName);
      DevObj.AddPair('location',    Device.Location);
      DevObj.AddPair('phase_type',  Device.PhaseType);
      DevObj.AddPair('is_active',   TJSONBool.Create(Device.IsActive));
      DevObj.AddPair('mqtt_topic',  Device.MqttTopic);
      DevObj.AddPair('memo',        Device.Memo);

      SenArr := TJSONArray.Create;
      if Assigned(Device.SensorList) then
        for SC in Device.SensorList do
        begin
          SenObj := TJSONObject.Create;
          SenObj.AddPair('sensor_group',  SC.SensorGroup);
          SenObj.AddPair('field_key',     SC.FieldKey);
          SenObj.AddPair('field_label',   SC.FieldLabel);
          SenObj.AddPair('is_enabled',    TJSONBool.Create(SC.IsEnabled));
          SenObj.AddPair('display_order', TJSONNumber.Create(SC.DisplayOrder));
          SenArr.AddElement(SenObj);
        end;
      DevObj.AddPair('sensors', SenArr);
      DevArr.AddElement(DevObj);
    end;
    Root.AddPair('exported_at', FormatDateTime('yyyy-mm-dd hh:nn:ss', Now));
    Root.AddPair('devices',     DevArr);

    TFile.WriteAllText(AFilePath, Root.Format, TEncoding.UTF8);
  finally
    Root.Free;
  end;
end;

function TNILMManager.ImportFromJSON(const AFilePath: string): TList<TNILMDevice>;
var
  Raw     : string;
  Root    : TJSONObject;
  DevArr  : TJSONArray;
  DevObj  : TJSONObject;
  SenArr  : TJSONArray;
  SenObj  : TJSONObject;
  Device  : TNILMDevice;
  SC      : TNILMSensorConfig;
  i, j    : Integer;
begin
  Result := TList<TNILMDevice>.Create;
  Raw := TFile.ReadAllText(AFilePath, TEncoding.UTF8);
  Root := TJSONObject.ParseJSONValue(Raw) as TJSONObject;
  if Root = nil then Exit;
  try
    DevArr := Root.GetValue<TJSONArray>('devices');
    if DevArr = nil then Exit;

    for i := 0 to DevArr.Count - 1 do
    begin
      DevObj := DevArr.Items[i] as TJSONObject;
      Device := TNILMDevice.CreateNew;
      Device.ID         := DevObj.GetValue<TJSONNumber>('id').AsInt;
      Device.DeviceID   := DevObj.GetValue<TJSONNumber>('device_id').AsInt;
      Device.DeviceName := DevObj.GetValue<TJSONString>('device_name').Value;
      Device.Location   := DevObj.GetValue<TJSONString>('location').Value;
      Device.PhaseType  := DevObj.GetValue<TJSONString>('phase_type').Value;
      Device.IsActive   := DevObj.GetValue<TJSONBool>('is_active').AsBoolean;
      Device.MqttTopic  := DevObj.GetValue<TJSONString>('mqtt_topic').Value;
      Device.Memo       := DevObj.GetValue<TJSONString>('memo').Value;

      SenArr := DevObj.GetValue<TJSONArray>('sensors');
      if Assigned(SenArr) then
        for j := 0 to SenArr.Count - 1 do
        begin
          SenObj := SenArr.Items[j] as TJSONObject;
          SC.ID           := 0;
          SC.DeviceID     := Device.DeviceID;
          SC.SensorGroup  := SenObj.GetValue<TJSONString>('sensor_group').Value;
          SC.FieldKey     := SenObj.GetValue<TJSONString>('field_key').Value;
          SC.FieldLabel   := SenObj.GetValue<TJSONString>('field_label').Value;
          SC.IsEnabled    := SenObj.GetValue<TJSONBool>('is_enabled').AsBoolean;
          SC.DisplayOrder := SenObj.GetValue<TJSONNumber>('display_order').AsInt;
          Device.SensorList.Add(SC);
        end;

      Result.Add(Device);
    end;
  finally
    Root.Free;
  end;
end;

{ ─── 히스토리 데이터 저장 ─────────────────────────────────────────────── }

procedure TNILMManager.SaveNILMData(ADeviceID, ASeq, APreSeq, ACommStatus: Integer;
  const APayloadJSON: string; AData: TJSONObject; ASensorList: TNILMSensorConfigList);

  function GetNum(const AKey: string): Double;
  var JV: TJSONValue;
  begin
    Result := 0;
    JV := AData.GetValue(AKey);
    if Assigned(JV) then
      Result := StrToFloatDef(JV.Value, 0);
  end;
  
  function GetJsonKey(const AFieldKey, ADefault: string): string;
  var SC: TNILMSensorConfig;
  begin
    Result := ADefault;
    if Assigned(ASensorList) then
      for SC in ASensorList do
        if SameText(SC.FieldKey, AFieldKey) then
        begin
          if SC.JsonKey <> '' then
            Result := SC.JsonKey;
          Exit;
        end;
  end;

var
  Q: TFDQuery;
  bSeqChanged: Boolean;
begin
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;

    bSeqChanged := True; // 기본적으로 변경되었다고 가정 (최초 데이터인 경우)

    // 1. 기존 seq 확인 (로그 기록 여부 판단용)
    Q.SQL.Text := 'SELECT seq FROM nilm_data WHERE device_id = :did';
    Q.ParamByName('did').AsInteger := ADeviceID;
    Q.Open;
    if not Q.IsEmpty then
    begin
      if Q.FieldByName('seq').AsInteger = ASeq then
        bSeqChanged := False; // 변경사항 없음
    end;
    Q.Close;

    // 2. 최신 실시간 데이터 무조건 UPSERT (1행만 유지)
    Q.SQL.Text :=
      'INSERT INTO nilm_data' +
      '  (device_id,seq,pre_seq,comm_status,received_at,payload_json,' +
      '   pf_l1,pk_l1,v_l1,i_l1,w_l1,var_l1,va_l1,' +
      '   pf_l2,pk_l2,v_l2,i_l2,w_l2,var_l2,va_l2,' +
      '   pf_l3,pk_l3,v_l3,i_l3,w_l3,var_l3,va_l3,' +
      '   temp_int,temp_ext,acc_x,acc_y,acc_z,vibration,' +
      '   gyro_x,gyro_y,gyro_z,tilt_angle)' +
      ' VALUES' +
      '  (:did,:seq,:preseq,:comm,NOW(),CAST(:pjson AS JSONB),' +
      '   :pfl1,:pkl1,:vl1,:il1,:wl1,:varl1,:val1,' +
      '   :pfl2,:pkl2,:vl2,:il2,:wl2,:varl2,:val2,' +
      '   :pfl3,:pkl3,:vl3,:il3,:wl3,:varl3,:val3,' +
      '   :tint,:text,:ax,:ay,:az,:vib,' +
      '   :gx,:gy,:gz,:tilt)' +
      ' ON CONFLICT (device_id) DO UPDATE SET ' +
      '  seq=EXCLUDED.seq, pre_seq=EXCLUDED.pre_seq, comm_status=EXCLUDED.comm_status, received_at=NOW(), payload_json=EXCLUDED.payload_json, ' +
      '  pf_l1=EXCLUDED.pf_l1, pk_l1=EXCLUDED.pk_l1, v_l1=EXCLUDED.v_l1, i_l1=EXCLUDED.i_l1, w_l1=EXCLUDED.w_l1, var_l1=EXCLUDED.var_l1, va_l1=EXCLUDED.va_l1, ' +
      '  pf_l2=EXCLUDED.pf_l2, pk_l2=EXCLUDED.pk_l2, v_l2=EXCLUDED.v_l2, i_l2=EXCLUDED.i_l2, w_l2=EXCLUDED.w_l2, var_l2=EXCLUDED.var_l2, va_l2=EXCLUDED.va_l2, ' +
      '  pf_l3=EXCLUDED.pf_l3, pk_l3=EXCLUDED.pk_l3, v_l3=EXCLUDED.v_l3, i_l3=EXCLUDED.i_l3, w_l3=EXCLUDED.w_l3, var_l3=EXCLUDED.var_l3, va_l3=EXCLUDED.va_l3, ' +
      '  temp_int=EXCLUDED.temp_int, temp_ext=EXCLUDED.temp_ext, ' +
      '  acc_x=EXCLUDED.acc_x, acc_y=EXCLUDED.acc_y, acc_z=EXCLUDED.acc_z, vibration=EXCLUDED.vibration, ' +
      '  gyro_x=EXCLUDED.gyro_x, gyro_y=EXCLUDED.gyro_y, gyro_z=EXCLUDED.gyro_z, tilt_angle=EXCLUDED.tilt_angle' +
      ' WHERE nilm_data.seq IS DISTINCT FROM EXCLUDED.seq OR nilm_data.pre_seq IS DISTINCT FROM EXCLUDED.pre_seq';

    Q.ParamByName('did').AsInteger    := ADeviceID;
    Q.ParamByName('seq').AsInteger    := ASeq;
    Q.ParamByName('preseq').AsInteger := APreSeq;
    Q.ParamByName('comm').AsInteger   := ACommStatus;
    Q.ParamByName('pjson').AsString   := APayloadJSON;

    Q.ParamByName('pfl1').AsFloat := GetNum(GetJsonKey('pfL1', 'pfA'));
    Q.ParamByName('pkl1').AsFloat := GetNum(GetJsonKey('pkL1', 'pkA'));
    Q.ParamByName('vl1').AsFloat  := GetNum(GetJsonKey('vL1', 'voltageA'));
    Q.ParamByName('il1').AsFloat  := GetNum(GetJsonKey('iL1', 'currentA'));
    Q.ParamByName('wl1').AsFloat  := GetNum(GetJsonKey('wL1', 'wA'));
    Q.ParamByName('varl1').AsFloat:= GetNum(GetJsonKey('varL1', 'Var_A'));
    Q.ParamByName('val1').AsFloat := GetNum(GetJsonKey('vaL1', 'Va_A'));

    Q.ParamByName('pfl2').AsFloat := GetNum(GetJsonKey('pfL2', 'pfB'));
    Q.ParamByName('pkl2').AsFloat := GetNum(GetJsonKey('pkL2', 'pkB'));
    Q.ParamByName('vl2').AsFloat  := GetNum(GetJsonKey('vL2', 'voltageB'));
    Q.ParamByName('il2').AsFloat  := GetNum(GetJsonKey('iL2', 'currentB'));
    Q.ParamByName('wl2').AsFloat  := GetNum(GetJsonKey('wL2', 'wB'));
    Q.ParamByName('varl2').AsFloat:= GetNum(GetJsonKey('varL2', 'Var_B'));
    Q.ParamByName('val2').AsFloat := GetNum(GetJsonKey('vaL2', 'Va_B'));

    Q.ParamByName('pfl3').AsFloat := GetNum(GetJsonKey('pfL3', 'pfC'));
    Q.ParamByName('pkl3').AsFloat := GetNum(GetJsonKey('pkL3', 'pkC'));
    Q.ParamByName('vl3').AsFloat  := GetNum(GetJsonKey('vL3', 'voltageC'));
    Q.ParamByName('il3').AsFloat  := GetNum(GetJsonKey('iL3', 'currentC'));
    Q.ParamByName('wl3').AsFloat  := GetNum(GetJsonKey('wL3', 'wC'));
    Q.ParamByName('varl3').AsFloat:= GetNum(GetJsonKey('varL3', 'Var_C'));
    Q.ParamByName('val3').AsFloat := GetNum(GetJsonKey('vaL3', 'Va_C'));

    Q.ParamByName('tint').AsFloat := GetNum(GetJsonKey('tempInt', 'tempInt'));
    Q.ParamByName('text').AsFloat := GetNum(GetJsonKey('tempExt', 'tempExt'));
    Q.ParamByName('ax').AsFloat   := GetNum(GetJsonKey('accX', 'accX'));
    Q.ParamByName('ay').AsFloat   := GetNum(GetJsonKey('accY', 'accY'));
    Q.ParamByName('az').AsFloat   := GetNum(GetJsonKey('accZ', 'accZ'));
    Q.ParamByName('vib').AsFloat  := GetNum(GetJsonKey('vibration', 'vibration'));
    Q.ParamByName('gx').AsFloat   := GetNum(GetJsonKey('gyroX', 'gyroX'));
    Q.ParamByName('gy').AsFloat   := GetNum(GetJsonKey('gyroY', 'gyroY'));
    Q.ParamByName('gz').AsFloat   := GetNum(GetJsonKey('gyroZ', 'gyroZ'));
    Q.ParamByName('tilt').AsFloat := GetNum(GetJsonKey('tiltAngle', 'tiltAngle'));

    Q.ExecSQL;
    Q.Close;

    // 3. 변경 이력 저장 (nilm_event_log) - seq가 변경되었을 때만 기록
    if bSeqChanged then
      SaveEventLog('DATA', ADeviceID, 'NILM Data Update (Seq: ' + IntToStr(ASeq) + ')', APayloadJSON);
  finally
    Q.Free;
  end;
end;

procedure TNILMManager.SaveEventLog(const AEventType: string; ADeviceID: Integer;
  const AMessage, ADetail: string);
var
  Q: TFDQuery;
begin
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    Q.SQL.Text :=
      'INSERT INTO nilm_event_log (event_type, device_id, message, detail)' +
      ' VALUES (:etype, :did, :msg, :det)';
    Q.ParamByName('etype').AsString  := AEventType;
    Q.ParamByName('did').AsInteger   := ADeviceID;
    Q.ParamByName('msg').AsString    := AMessage;
    Q.ParamByName('det').AsString    := ADetail;
    Q.ExecSQL;
  finally
    Q.Free;
  end;
end;

function TNILMManager.QueryNILMHistory(ADeviceID: Integer;
  ADateFrom, ADateTo: TDateTime;
  ACommStatusFilter: Integer; AMaxRows: Integer): TFDQuery;
var
  SQL: string;
begin
  Result := TFDQuery.Create(nil);
  Result.Connection := GetConnection;

    SQL :=
      'SELECT received_at, device_id, seq, pre_seq, comm_status,' +
      '  pf_l1, v_l1, i_l1, w_l1, var_l1, va_l1,' +
      '  pf_l2, v_l2, i_l2, w_l2, var_l2, va_l2,' +
      '  pf_l3, v_l3, i_l3, w_l3, var_l3, va_l3,' +
      '  temp_int, temp_ext, vibration, tilt_angle' +
      ' FROM nilm_data' +
      ' WHERE device_id > 0 ';
//      ' WHERE received_at >= :dtfrom AND received_at <= :dtto';

    if ADeviceID > 0 then
      SQL := SQL + ' AND device_id = :did';
    if ACommStatusFilter >= 0 then
      SQL := SQL + ' AND comm_status = :comm';

//    SQL := SQL + ' ORDER BY received_at DESC';
    SQL := SQL + ' ORDER BY device_id ';

  if AMaxRows > 0 then
    SQL := SQL + ' LIMIT ' + IntToStr(AMaxRows);

  Result.SQL.Text := SQL;
//  Result.ParamByName('dtfrom').AsDateTime := ADateFrom;
//  Result.ParamByName('dtto').AsDateTime   := ADateTo;

  if ADeviceID > 0 then
    Result.ParamByName('did').AsInteger := ADeviceID;
  if ACommStatusFilter >= 0 then
    Result.ParamByName('comm').AsInteger := ACommStatusFilter;

  Result.Open;
end;

function TNILMManager.QueryEventLog(ADeviceID: Integer;
  ADateFrom, ADateTo: TDateTime;
  const AEventType: string; AMaxRows: Integer): TFDQuery;
var
  SQL: string;
begin
  Result := TFDQuery.Create(nil);
  Result.Connection := GetConnection;

  SQL :=
    'SELECT event_time, event_type, device_id, message, detail' +
    ' FROM nilm_event_log' +
    ' WHERE event_time >= :dtfrom AND event_time <= :dtto';

  if ADeviceID > 0 then
    SQL := SQL + ' AND device_id = :did';
  if AEventType <> '' then
    SQL := SQL + ' AND event_type = :etype';

  SQL := SQL + ' ORDER BY event_time DESC';

  if AMaxRows > 0 then
    SQL := SQL + ' LIMIT ' + IntToStr(AMaxRows);

  Result.SQL.Text := SQL;
  Result.ParamByName('dtfrom').AsDateTime := ADateFrom;
  Result.ParamByName('dtto').AsDateTime   := ADateTo;

  if ADeviceID > 0 then
    Result.ParamByName('did').AsInteger := ADeviceID;
  if AEventType <> '' then
    Result.ParamByName('etype').AsString := AEventType;

  Result.Open;
end;

function TNILMManager.LoadDevicesSimple: TFDQuery;
begin
  Result := TFDQuery.Create(nil);
  Result.Connection := GetConnection;
  Result.SQL.Text :=
    'SELECT device_id, device_name FROM nilm_device' +
    ' WHERE is_active = TRUE ORDER BY device_id';
  Result.Open;
end;

end.