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
  TNILMErrorEvent = procedure(const AMsg: string) of object;

  TNILMManager = class
  private
    FOnError: TNILMErrorEvent;
    FExternalOpStatusCache: TDictionary<Integer, Integer>;
    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
    constructor Create;
    destructor Destroy; override;

    property OnError: TNILMErrorEvent read FOnError write FOnError;

    function GetConnection: TFDConnection;
    // ─── 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>;

    // ── 히스토리 데이터 저장 ─────────────────────────────────
    /// 신규 규격 측정 데이터 저장 (3P3W/3P4W 공통)
    procedure SaveMeasurementData(ADeviceID: Integer; const ADeviceName: string; const APayload: TNILMMeasurementPayload;
      const APayloadJSON, AOpStatus: string; ASensorList: TNILMSensorConfigList; bSeqChanged: Boolean);
    /// 레거시 및 하위 호환 MQTT 수신 데이터를 nilm_data에 저장
    procedure SaveNILMData(ADeviceID: Integer; const ADeviceName: string; ASeq, APreSeq, ACommStatus: Integer;
      const APayloadJSON, AOpStatus: string; AData: TJSONObject; ASensorList: TNILMSensorConfigList; bSeqChanged: Boolean);
    /// 노드 통신 상태 저장 (/{NDID}/status)
    procedure SaveNodeStatus(ADeviceID: Integer; const AStatus: TNILMNodeStatusPayload);
    /// 게이트웨이 상태 저장 (/gateway)
    procedure SaveGatewayStatus(const AGateway: TNILMGatewayPayload);
    /// 이벤트 로그 저장
    procedure SaveEventLog(const AEventType: string; ADeviceID: Integer;
      const AMessage, ADetail: string);
    /// 신규 규격 장비 이벤트 저장 (/{NDID}/event)
    procedure SaveNILMEvent(ADeviceID: Integer; const AEvent: TNILMEventPayload);
    /// 히스토리 조회 (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;

    /// 지정된 기간의 전력량(Wh) 계산 (L1, L2, L3)
    procedure CalculateEnergyConsumption(ADeviceID: Integer; ADateFrom, ADateTo: TDateTime; var AEnergyL1, AEnergyL2, AEnergyL3: Double);
    
    // ── 외부 DB (ETC) 연동 ─────────────────────────────────
    /// 외부 DB에 실시간 및 이력 상태 저장 (opStatus 변경 시 호출)
    procedure SaveExternalOpStatus(const ANILMName: string; AOpStatus: Integer);
    /// 외부 DB에 일일 전력량 저장 (자정에 호출)
    procedure SaveExternalDailyEnergy(const ANILMName: string; ADate: TDateTime; AEnergyValue: Double);
  end;

implementation

uses
  System.IOUtils;

{ TNILMManager }

constructor TNILMManager.Create;
begin
  inherited Create;
  FExternalOpStatusCache := TDictionary<Integer, Integer>.Create;
end;

destructor TNILMManager.Destroy;
begin
  FExternalOpStatusCache.Free;
  inherited Destroy;
end;

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,' +
    '  op_target_phase VARCHAR(10) DEFAULT ''AVG'',' +
    '  op_threshold_off_current NUMERIC(10,4) DEFAULT 0,' +
    '  op_threshold_run_current NUMERIC(10,4) DEFAULT 0,' +
    '  op_threshold_pf NUMERIC(8,4) DEFAULT 0,' +
    '  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 ON UPDATE 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,' +
    '  power_factor_a NUMERIC(8,4), pk_l1 NUMERIC(10,4), vrms_a NUMERIC(8,2), irms_a NUMERIC(10,4), active_power_a NUMERIC(10,2), reactive_power_a NUMERIC(10,2), apparent_power_a NUMERIC(10,2),' +
    '  phase_angle_a NUMERIC(8,2), vthd_a NUMERIC(8,2), ithd_a NUMERIC(8,2), active_energy_import_a NUMERIC(14,2), active_energy_export_a NUMERIC(14,2), reactive_energy_import_a NUMERIC(14,2), reactive_energy_export_a NUMERIC(14,2), apparent_energy_a NUMERIC(14,2),' +
    '  power_factor_b NUMERIC(8,4), pk_l2 NUMERIC(10,4), vrms_b NUMERIC(8,2), irms_b NUMERIC(10,4), active_power_b NUMERIC(10,2), reactive_power_b NUMERIC(10,2), apparent_power_b NUMERIC(10,2),' +
    '  phase_angle_b NUMERIC(8,2), vthd_b NUMERIC(8,2), ithd_b NUMERIC(8,2), active_energy_import_b NUMERIC(14,2), active_energy_export_b NUMERIC(14,2), reactive_energy_import_b NUMERIC(14,2), reactive_energy_export_b NUMERIC(14,2), apparent_energy_b NUMERIC(14,2),' +
    '  power_factor_c NUMERIC(8,4), pk_l3 NUMERIC(10,4), vrms_c NUMERIC(8,2), irms_c NUMERIC(10,4), active_power_c NUMERIC(10,2), reactive_power_c NUMERIC(10,2), apparent_power_c NUMERIC(10,2),' +
    '  phase_angle_c NUMERIC(8,2), vthd_c NUMERIC(8,2), ithd_c NUMERIC(8,2), active_energy_import_c NUMERIC(14,2), active_energy_export_c NUMERIC(14,2), reactive_energy_import_c NUMERIC(14,2), reactive_energy_export_c NUMERIC(14,2), apparent_energy_c NUMERIC(14,2),' +
    '  temperature NUMERIC(6,2), temp_ext NUMERIC(6,2), frequency NUMERIC(8,2), event_flags VARCHAR(50),' +
    '  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),' +
    '  op_status VARCHAR(20)' +
    '); ' +
    'CREATE INDEX IF NOT EXISTS idx_nilm_data_device_received ON nilm_data (device_id, received_at DESC)';

  DDL_DATA_HISTORY =
    'CREATE TABLE IF NOT EXISTS nilm_data_history (' +
    '  id          BIGSERIAL PRIMARY KEY,' +
    '  device_id   INTEGER   NOT NULL,' +
    '  seq         INTEGER,' +
    '  received_at TIMESTAMP NOT NULL DEFAULT NOW(),' +
    '  active_power_a NUMERIC(10,2), active_power_b NUMERIC(10,2), active_power_c NUMERIC(10,2),' +
    '  reactive_power_a NUMERIC(10,2), reactive_power_b NUMERIC(10,2), reactive_power_c NUMERIC(10,2),' +
    '  apparent_power_a NUMERIC(10,2), apparent_power_b NUMERIC(10,2), apparent_power_c NUMERIC(10,2)' +
    '); ' +
    'CREATE INDEX IF NOT EXISTS idx_nilm_data_history_device_time ON nilm_data_history (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)';
  DDL_GATEWAY_STATUS =
    'CREATE TABLE IF NOT EXISTS nilm_gateway_status (' +
    '  gw           VARCHAR(50) PRIMARY KEY,' +
    '  online       BOOLEAN     NOT NULL DEFAULT TRUE,' +
    '  nodes_total  INTEGER     DEFAULT 0,' +
    '  nodes_online INTEGER     DEFAULT 0,' +
    '  cycle_ms     INTEGER     DEFAULT 0,' +
    '  uptime_s     BIGINT      DEFAULT 0,' +
    '  updated_at   TIMESTAMP   NOT NULL DEFAULT NOW()' +
    ')';
begin
  ExecSQL(DDL_DEVICE, []);
  ExecSQL(DDL_SENSOR_CONFIG, []);
  
  // 기존 FK 제약조건 변경 (ON UPDATE CASCADE 추가)
  try 
    ExecSQL('ALTER TABLE nilm_sensor_config DROP CONSTRAINT fk_nsc_device', []); 
    ExecSQL('ALTER TABLE nilm_sensor_config ADD CONSTRAINT fk_nsc_device FOREIGN KEY (device_id) REFERENCES nilm_device(device_id) ON DELETE CASCADE ON UPDATE CASCADE', []);
  except end;

  ExecSQL(DDL_DATA, []);
  ExecSQL(DDL_DATA_HISTORY, []);
  ExecSQL(DDL_EVENT_LOG, []);
  ExecSQL(DDL_GATEWAY_STATUS, []);

  try ExecSQL('ALTER TABLE nilm_device ADD COLUMN op_target_phase VARCHAR(10) DEFAULT ''TOTAL''', []); except end;
  try ExecSQL('ALTER TABLE nilm_device ADD COLUMN op_threshold_off_current NUMERIC(10,4) DEFAULT 0', []); except end;
  try ExecSQL('ALTER TABLE nilm_device ADD COLUMN op_threshold_run_current NUMERIC(10,4) DEFAULT 0', []); except end;
  try ExecSQL('ALTER TABLE nilm_device ADD COLUMN op_threshold_pf NUMERIC(8,4) DEFAULT 0', []); except end;
  try ExecSQL('ALTER TABLE nilm_device ADD COLUMN is_estimate_power BOOLEAN DEFAULT TRUE', []); except end;
  try ExecSQL('ALTER TABLE nilm_device ADD COLUMN nominal_voltage NUMERIC(10,2) DEFAULT 220.0', []); except end;
  try ExecSQL('ALTER TABLE nilm_device ADD COLUMN assumed_pf NUMERIC(8,4) DEFAULT 0.90', []); except end;
  try ExecSQL('ALTER TABLE nilm_device ADD COLUMN estimate_wiring VARCHAR(30) DEFAULT ''SINGLE''', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN op_status VARCHAR(20)', []); except end;

  // nilm_data_history 확장을 위한 컬럼 추가 (데이터 히스토리 조회용)
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN pre_seq INTEGER', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN comm_status INTEGER DEFAULT 0', []); except end;
  // ---------------------------------------------------------
  // JSON 스키마 변경에 따른 기존 데이터베이스 컬럼명 일괄 갱신
  // ---------------------------------------------------------
  // nilm_data rename
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN v_l1 TO vrms_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN i_l1 TO irms_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN w_l1 TO active_power_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN var_l1 TO reactive_power_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN va_l1 TO apparent_power_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN pf_l1 TO power_factor_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN v_l2 TO vrms_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN i_l2 TO irms_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN w_l2 TO active_power_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN var_l2 TO reactive_power_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN va_l2 TO apparent_power_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN pf_l2 TO power_factor_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN v_l3 TO vrms_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN i_l3 TO irms_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN w_l3 TO active_power_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN var_l3 TO reactive_power_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN va_l3 TO apparent_power_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN pf_l3 TO power_factor_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data RENAME COLUMN temp_int TO temperature', []); except end;
  
  // nilm_data_history rename
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN v_l1 TO vrms_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN i_l1 TO irms_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN w_l1 TO active_power_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN var_l1 TO reactive_power_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN va_l1 TO apparent_power_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN pf_l1 TO power_factor_a', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN v_l2 TO vrms_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN i_l2 TO irms_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN w_l2 TO active_power_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN var_l2 TO reactive_power_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN va_l2 TO apparent_power_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN pf_l2 TO power_factor_b', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN v_l3 TO vrms_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN i_l3 TO irms_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN w_l3 TO active_power_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN var_l3 TO reactive_power_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN va_l3 TO apparent_power_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN pf_l3 TO power_factor_c', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history RENAME COLUMN temp_int TO temperature', []); except end;
  
  // 신규 필드 추가 (nilm_data, nilm_data_history 공통 및 일부 개별)
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN pre_seq INTEGER', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN comm_status INTEGER DEFAULT 0', []); except end;
  
  // A상 추가
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN phase_angle_a NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN vthd_a NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN ithd_a NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN active_energy_import_a NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN active_energy_export_a NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN reactive_energy_import_a NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN reactive_energy_export_a NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN apparent_energy_a NUMERIC(14,2)', []); except end;
  // B상 추가
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN phase_angle_b NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN vthd_b NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN ithd_b NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN active_energy_import_b NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN active_energy_export_b NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN reactive_energy_import_b NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN reactive_energy_export_b NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN apparent_energy_b NUMERIC(14,2)', []); except end;
  // C상 추가
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN phase_angle_c NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN vthd_c NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN ithd_c NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN active_energy_import_c NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN active_energy_export_c NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN reactive_energy_import_c NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN reactive_energy_export_c NUMERIC(14,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN apparent_energy_c NUMERIC(14,2)', []); except end;
  // 기타 공통
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN frequency NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN event_flags VARCHAR(50)', []); except end;

  // 3P3W / 3P4W 신규 규격 컬럼 추가
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN wire VARCHAR(10)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN rssi INTEGER', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN wh BIGINT', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN total_p NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN total_q NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN total_s NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data ADD COLUMN total_pf NUMERIC(8,4)', []); except end;

  // nilm_data_history 누락 컬럼 일괄 보강 (신규 스키마 및 마이그레이션 대응)
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN wire VARCHAR(10)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN rssi INTEGER', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN wh BIGINT', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN total_p NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN total_q NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN total_s NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN total_pf NUMERIC(8,4)', []); except end;

  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN power_factor_a NUMERIC(8,4)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN vrms_a NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN irms_a NUMERIC(10,4)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN active_power_a NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN reactive_power_a NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN apparent_power_a NUMERIC(10,2)', []); except end;

  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN power_factor_b NUMERIC(8,4)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN vrms_b NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN irms_b NUMERIC(10,4)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN active_power_b NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN reactive_power_b NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN apparent_power_b NUMERIC(10,2)', []); except end;

  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN power_factor_c NUMERIC(8,4)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN vrms_c NUMERIC(8,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN irms_c NUMERIC(10,4)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN active_power_c NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN reactive_power_c NUMERIC(10,2)', []); except end;
  try ExecSQL('ALTER TABLE nilm_data_history ADD COLUMN apparent_power_c NUMERIC(10,2)', []); except end;

  // 장비 추가 시 시퀀스 오류(duplicate key) 방지를 위한 시퀀스 동기화
  try 
    ExecSQL('DO $$ BEGIN IF EXISTS (SELECT 1 FROM nilm_device) THEN ' +
            'PERFORM setval(pg_get_serial_sequence(''nilm_device'', ''id''), (SELECT MAX(id) FROM nilm_device)); ' +
            'END IF; END $$;', []); 
  except end;
  try 
    ExecSQL('DO $$ BEGIN IF EXISTS (SELECT 1 FROM nilm_sensor_config) THEN ' +
            'PERFORM setval(pg_get_serial_sequence(''nilm_sensor_config'', ''id''), (SELECT MAX(id) FROM nilm_sensor_config)); ' +
            'END IF; END $$;', []); 
  except end;
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, op_target_phase, ' +
      '       op_threshold_off_current, op_threshold_run_current, op_threshold_pf, ' +
      '       is_estimate_power, nominal_voltage, assumed_pf, estimate_wiring, ' +
      '       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.OpTargetPhase         := Q.FieldByName('op_target_phase').AsString;
      Device.OpThresholdOffCurrent := Q.FieldByName('op_threshold_off_current').AsFloat;
      Device.OpThresholdRunCurrent := Q.FieldByName('op_threshold_run_current').AsFloat;
      Device.OpThresholdPF         := Q.FieldByName('op_threshold_pf').AsFloat;

      if not Q.FieldByName('is_estimate_power').IsNull then
        Device.IsEstimatePower := Q.FieldByName('is_estimate_power').AsBoolean
      else
        Device.IsEstimatePower := True;

      if not Q.FieldByName('nominal_voltage').IsNull then
        Device.NominalVoltage := Q.FieldByName('nominal_voltage').AsFloat
      else
        Device.NominalVoltage := 220.0;

      if not Q.FieldByName('assumed_pf').IsNull then
        Device.AssumedPF := Q.FieldByName('assumed_pf').AsFloat
      else
        Device.AssumedPF := 0.90;

      if not Q.FieldByName('estimate_wiring').IsNull then
        Device.EstimateWiring := Q.FieldByName('estimate_wiring').AsString
      else
        Device.EstimateWiring := 'SINGLE';

      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, op_target_phase, ' +
      '       op_threshold_off_current, op_threshold_run_current, op_threshold_pf, ' +
      '       is_estimate_power, nominal_voltage, assumed_pf, estimate_wiring, ' +
      '       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.OpTargetPhase         := Q.FieldByName('op_target_phase').AsString;
      Result.OpThresholdOffCurrent := Q.FieldByName('op_threshold_off_current').AsFloat;
      Result.OpThresholdRunCurrent := Q.FieldByName('op_threshold_run_current').AsFloat;
      Result.OpThresholdPF         := Q.FieldByName('op_threshold_pf').AsFloat;

      if not Q.FieldByName('is_estimate_power').IsNull then
        Result.IsEstimatePower := Q.FieldByName('is_estimate_power').AsBoolean
      else
        Result.IsEstimatePower := True;

      if not Q.FieldByName('nominal_voltage').IsNull then
        Result.NominalVoltage := Q.FieldByName('nominal_voltage').AsFloat
      else
        Result.NominalVoltage := 220.0;

      if not Q.FieldByName('assumed_pf').IsNull then
        Result.AssumedPF := Q.FieldByName('assumed_pf').AsFloat
      else
        Result.AssumedPF := 0.90;

      if not Q.FieldByName('estimate_wiring').IsNull then
        Result.EstimateWiring := Q.FieldByName('estimate_wiring').AsString
      else
        Result.EstimateWiring := 'SINGLE';

      Result.CreatedAt := Q.FieldByName('created_at').AsDateTime;
      Result.UpdatedAt := Q.FieldByName('updated_at').AsDateTime;
      LoadSensorsForDevice(ADeviceID, 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';
    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.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, ' +
        '   op_target_phase, op_threshold_off_current, op_threshold_run_current, op_threshold_pf, ' +
        '   is_estimate_power, nominal_voltage, assumed_pf, estimate_wiring, ' +
        '   created_at, updated_at) ' +
        'VALUES (:did, :dname, :loc, :ptype, CAST(:active AS BOOLEAN), :topic, :memo, ' +
        '   :opphase, :opoffc, :oprunc, :oppf, ' +
        '   CAST(:isest AS BOOLEAN), :nomv, :asspf, :estwire, ' +
        '   NOW(), NOW()) ' +
        'RETURNING id';
      Q.ParamByName('did').AsInteger      := ADevice.DeviceID;
      Q.ParamByName('dname').AsWideString := SafeDBStr(ADevice.DeviceName);
      Q.ParamByName('loc').AsWideString   := SafeDBStr(ADevice.Location);
      Q.ParamByName('ptype').AsWideString := SafeDBStr(ADevice.PhaseType);
      if ADevice.IsActive then
        Q.ParamByName('active').AsString := 'true'
      else
        Q.ParamByName('active').AsString := 'false';
      Q.ParamByName('topic').AsWideString := SafeDBStr(ADevice.MqttTopic);
      Q.ParamByName('memo').AsWideString  := SafeDBStr(ADevice.Memo);
      Q.ParamByName('opphase').AsWideString := SafeDBStr(ADevice.OpTargetPhase);
      Q.ParamByName('opoffc').AsFloat := ADevice.OpThresholdOffCurrent;
      Q.ParamByName('oprunc').AsFloat := ADevice.OpThresholdRunCurrent;
      Q.ParamByName('oppf').AsFloat   := ADevice.OpThresholdPF;

      if ADevice.IsEstimatePower then
        Q.ParamByName('isest').AsString := 'true'
      else
        Q.ParamByName('isest').AsString := 'false';
      Q.ParamByName('nomv').AsFloat     := ADevice.NominalVoltage;
      Q.ParamByName('asspf').AsFloat    := ADevice.AssumedPF;
      Q.ParamByName('estwire').AsWideString := SafeDBStr(ADevice.EstimateWiring);

      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, ' +
        '  op_target_phase = :opphase, op_threshold_off_current = :opoffc, op_threshold_run_current = :oprunc, op_threshold_pf = :oppf, ' +
        '  is_estimate_power = CAST(:isest AS BOOLEAN), nominal_voltage = :nomv, assumed_pf = :asspf, estimate_wiring = :estwire, ' +
        '  updated_at = NOW() ' +
        'WHERE id = :id';
      Q.ParamByName('id').AsInteger       := ADevice.ID;
      Q.ParamByName('did').AsInteger      := ADevice.DeviceID;
      Q.ParamByName('dname').AsWideString := SafeDBStr(ADevice.DeviceName);
      Q.ParamByName('loc').AsWideString   := SafeDBStr(ADevice.Location);
      Q.ParamByName('ptype').AsWideString := SafeDBStr(ADevice.PhaseType);
      if ADevice.IsActive then
        Q.ParamByName('active').AsString := 'true'
      else
        Q.ParamByName('active').AsString := 'false';
      Q.ParamByName('topic').AsWideString := SafeDBStr(ADevice.MqttTopic);
      Q.ParamByName('memo').AsWideString  := SafeDBStr(ADevice.Memo);
      Q.ParamByName('opphase').AsWideString := SafeDBStr(ADevice.OpTargetPhase);
      Q.ParamByName('opoffc').AsFloat := ADevice.OpThresholdOffCurrent;
      Q.ParamByName('oprunc').AsFloat := ADevice.OpThresholdRunCurrent;
      Q.ParamByName('oppf').AsFloat   := ADevice.OpThresholdPF;

      if ADevice.IsEstimatePower then
        Q.ParamByName('isest').AsString := 'true'
      else
        Q.ParamByName('isest').AsString := 'false';
      Q.ParamByName('nomv').AsFloat     := ADevice.NominalVoltage;
      Q.ParamByName('asspf').AsFloat    := ADevice.AssumedPF;
      Q.ParamByName('estwire').AsWideString := SafeDBStr(ADevice.EstimateWiring);

      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, 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').AsWideString := SafeDBStr(SC.SensorGroup);
            Q.ParamByName('fkey').AsWideString := SafeDBStr(SC.FieldKey);
            Q.ParamByName('flbl').AsWideString := SafeDBStr(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').AsWideString := SafeDBStr(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);
      DevObj.AddPair('op_target_phase', Device.OpTargetPhase);
      DevObj.AddPair('op_threshold_off_current', TJSONNumber.Create(Device.OpThresholdOffCurrent));
      DevObj.AddPair('op_threshold_run_current', TJSONNumber.Create(Device.OpThresholdRunCurrent));
      DevObj.AddPair('op_threshold_pf', TJSONNumber.Create(Device.OpThresholdPF));

      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;
  strVal  : TJSONString;
  numVal  : TJSONNumber;
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;
      strVal := DevObj.GetValue('op_target_phase') as TJSONString;
      if Assigned(strVal) then
        Device.OpTargetPhase := strVal.Value
      else
        Device.OpTargetPhase := 'AVG';
        
      numVal := DevObj.GetValue('op_threshold_off_current') as TJSONNumber;
      if Assigned(numVal) then
        Device.OpThresholdOffCurrent := numVal.AsDouble;
        
      numVal := DevObj.GetValue('op_threshold_run_current') as TJSONNumber;
      if Assigned(numVal) then
        Device.OpThresholdRunCurrent := numVal.AsDouble;
        
      numVal := DevObj.GetValue('op_threshold_pf') as TJSONNumber;
      if Assigned(numVal) then
        Device.OpThresholdPF := numVal.AsDouble;

      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.SaveMeasurementData(ADeviceID: Integer; const ADeviceName: string;
  const APayload: TNILMMeasurementPayload; const APayloadJSON, AOpStatus: string;
  ASensorList: TNILMSensorConfigList; bSeqChanged: Boolean);
var
  Q: TFDQuery;
  NilmName: string;
begin
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;

    if bSeqChanged then
    begin
      // 상태 변경 감지 및 외부 DB 연동 (메모리 캐시 사용)
      var LNewOpStatus := StrToIntDef(AOpStatus, 0);
      var LSyncNeeded := False;
      
      if not FExternalOpStatusCache.ContainsKey(ADeviceID) then
        LSyncNeeded := True
      else if FExternalOpStatusCache[ADeviceID] <> LNewOpStatus then
        LSyncNeeded := True;

      if LSyncNeeded then
      begin
        if Trim(ADeviceName) <> '' then
          NilmName := Trim(ADeviceName)
        else
          NilmName := IntToStr(ADeviceID);
        SaveExternalOpStatus(NilmName, LNewOpStatus);
        FExternalOpStatusCache.AddOrSetValue(ADeviceID, LNewOpStatus);
      end;

      // 1. 최신 실시간 데이터 UPSERT (seq가 변경된 경우에만 전체 데이터 갱신)
      Q.SQL.Text :=
        'INSERT INTO nilm_data' +
        '  (device_id, seq, pre_seq, comm_status, received_at, payload_json, op_status,' +
        '   wire, rssi, wh, total_p, total_q, total_s, total_pf,' +
        '   power_factor_a, vrms_a, irms_a, active_power_a, reactive_power_a, apparent_power_a,' +
        '   power_factor_b, vrms_b, irms_b, active_power_b, reactive_power_b, apparent_power_b,' +
        '   power_factor_c, vrms_c, irms_c, active_power_c, reactive_power_c, apparent_power_c,' +
        '   frequency)' +
        ' VALUES' +
        '  (:did, :seq, :preseq, :comm, NOW(), CAST(:pjson AS JSONB), :opstatus,' +
        '   :wire, :rssi, :wh, :totp, :totq, :tots, :totpf,' +
        '   :pfa, :vrmsa, :irmsa, :apa, :rpa, :appa,' +
        '   :pfb, :vrmsb, :irmsb, :apb, :rpb, :appb,' +
        '   :pfc, :vrmsc, :irmsc, :apc, :rpc, :appc,' +
        '   :freq)' +
        ' 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, op_status=EXCLUDED.op_status, ' +
        '  wire=EXCLUDED.wire, rssi=EXCLUDED.rssi, wh=EXCLUDED.wh, total_p=EXCLUDED.total_p, total_q=EXCLUDED.total_q, total_s=EXCLUDED.total_s, total_pf=EXCLUDED.total_pf, ' +
        '  power_factor_a=EXCLUDED.power_factor_a, vrms_a=EXCLUDED.vrms_a, irms_a=EXCLUDED.irms_a, active_power_a=EXCLUDED.active_power_a, reactive_power_a=EXCLUDED.reactive_power_a, apparent_power_a=EXCLUDED.apparent_power_a, ' +
        '  power_factor_b=EXCLUDED.power_factor_b, vrms_b=EXCLUDED.vrms_b, irms_b=EXCLUDED.irms_b, active_power_b=EXCLUDED.active_power_b, reactive_power_b=EXCLUDED.reactive_power_b, apparent_power_b=EXCLUDED.apparent_power_b, ' +
        '  power_factor_c=EXCLUDED.power_factor_c, vrms_c=EXCLUDED.vrms_c, irms_c=EXCLUDED.irms_c, active_power_c=EXCLUDED.active_power_c, reactive_power_c=EXCLUDED.reactive_power_c, apparent_power_c=EXCLUDED.apparent_power_c, ' +
        '  frequency=EXCLUDED.frequency';

      Q.ParamByName('did').AsInteger     := ADeviceID;
      Q.ParamByName('seq').AsInteger     := APayload.seq;
      Q.ParamByName('preseq').AsInteger  := 0;
      Q.ParamByName('comm').AsInteger    := APayload.COMM;
      Q.ParamByName('pjson').AsString    := APayloadJSON;
      Q.ParamByName('opstatus').AsString := AOpStatus;

      Q.ParamByName('wire').AsString     := APayload.wire;
      Q.ParamByName('rssi').AsInteger    := APayload.rssi;
      Q.ParamByName('wh').AsLargeInt     := APayload.wh;
      Q.ParamByName('totp').AsFloat      := APayload.total.p;
      Q.ParamByName('totq').AsFloat      := APayload.total.q;
      Q.ParamByName('tots').AsFloat      := APayload.total.s;
      Q.ParamByName('totpf').AsFloat     := APayload.total.pf;

      // Channel 0 (A)
      Q.ParamByName('pfa').AsFloat       := APayload.ch[0].pf;
      Q.ParamByName('vrmsa').AsFloat     := APayload.ch[0].v;
      Q.ParamByName('irmsa').AsFloat     := APayload.ch[0].i;
      Q.ParamByName('apa').AsFloat       := APayload.ch[0].p;
      Q.ParamByName('rpa').AsFloat       := APayload.ch[0].q;
      Q.ParamByName('appa').AsFloat      := APayload.ch[0].s;

      // Channel 1 (B) - 3P3W일 경우 미사용(0 또는 null)
      if (APayload.wire = PHASE_3P3W) or (not APayload.ch[1].IsValid) then
      begin
        Q.ParamByName('pfb').AsFloat     := 0.0;
        Q.ParamByName('vrmsb').AsFloat   := 0.0;
        Q.ParamByName('irmsb').AsFloat   := 0.0;
        Q.ParamByName('apb').AsFloat     := 0.0;
        Q.ParamByName('rpb').AsFloat     := 0.0;
        Q.ParamByName('appb').AsFloat    := 0.0;
      end
      else
      begin
        Q.ParamByName('pfb').AsFloat     := APayload.ch[1].pf;
        Q.ParamByName('vrmsb').AsFloat   := APayload.ch[1].v;
        Q.ParamByName('irmsb').AsFloat   := APayload.ch[1].i;
        Q.ParamByName('apb').AsFloat     := APayload.ch[1].p;
        Q.ParamByName('rpb').AsFloat     := APayload.ch[1].q;
        Q.ParamByName('appb').AsFloat    := APayload.ch[1].s;
      end;

      // Channel 2 (C)
      Q.ParamByName('pfc').AsFloat       := APayload.ch[2].pf;
      Q.ParamByName('vrmsc').AsFloat     := APayload.ch[2].v;
      Q.ParamByName('irmsc').AsFloat     := APayload.ch[2].i;
      Q.ParamByName('apc').AsFloat       := APayload.ch[2].p;
      Q.ParamByName('rpc').AsFloat       := APayload.ch[2].q;
      Q.ParamByName('appc').AsFloat      := APayload.ch[2].s;

      Q.ParamByName('freq').AsFloat      := APayload.freq;

      Q.ExecSQL;
      Q.Close;

      // 2. 이력 테이블에 전체 데이터 저장 (예외 발생 시에도 메인 흐름 차단 방지)
      try
        Q.SQL.Text :=
          'INSERT INTO nilm_data_history ' +
          ' (device_id, seq, pre_seq, comm_status, received_at, ' +
          '  wire, rssi, wh, total_p, total_q, total_s, total_pf, ' +
          '  power_factor_a, vrms_a, irms_a, active_power_a, reactive_power_a, apparent_power_a, ' +
          '  power_factor_b, vrms_b, irms_b, active_power_b, reactive_power_b, apparent_power_b, ' +
          '  power_factor_c, vrms_c, irms_c, active_power_c, reactive_power_c, apparent_power_c) ' +
          ' VALUES ' +
          ' (:did, :seq, :preseq, :comm, NOW(), ' +
          '  :wire, :rssi, :wh, :totp, :totq, :tots, :totpf, ' +
          '  :pfa, :vrmsa, :irmsa, :apa, :rpa, :appa, ' +
          '  :pfb, :vrmsb, :irmsb, :apb, :rpb, :appb, ' +
          '  :pfc, :vrmsc, :irmsc, :apc, :rpc, :appc)';

        Q.ParamByName('did').AsInteger    := ADeviceID;
        Q.ParamByName('seq').AsInteger    := APayload.seq;
        Q.ParamByName('preseq').AsInteger := 0;
        Q.ParamByName('comm').AsInteger   := APayload.COMM;

        Q.ParamByName('wire').AsString    := APayload.wire;
        Q.ParamByName('rssi').AsInteger   := APayload.rssi;
        Q.ParamByName('wh').AsLargeInt    := APayload.wh;
        Q.ParamByName('totp').AsFloat     := APayload.total.p;
        Q.ParamByName('totq').AsFloat     := APayload.total.q;
        Q.ParamByName('tots').AsFloat     := APayload.total.s;
        Q.ParamByName('totpf').AsFloat    := APayload.total.pf;

        Q.ParamByName('pfa').AsFloat      := APayload.ch[0].pf;
        Q.ParamByName('vrmsa').AsFloat    := APayload.ch[0].v;
        Q.ParamByName('irmsa').AsFloat    := APayload.ch[0].i;
        Q.ParamByName('apa').AsFloat      := APayload.ch[0].p;
        Q.ParamByName('rpa').AsFloat      := APayload.ch[0].q;
        Q.ParamByName('appa').AsFloat     := APayload.ch[0].s;

        if (APayload.wire = PHASE_3P3W) or (not APayload.ch[1].IsValid) then
        begin
          Q.ParamByName('pfb').AsFloat    := 0.0;
          Q.ParamByName('vrmsb').AsFloat  := 0.0;
          Q.ParamByName('irmsb').AsFloat  := 0.0;
          Q.ParamByName('apb').AsFloat    := 0.0;
          Q.ParamByName('rpb').AsFloat    := 0.0;
          Q.ParamByName('appb').AsFloat   := 0.0;
        end
        else
        begin
          Q.ParamByName('pfb').AsFloat    := APayload.ch[1].pf;
          Q.ParamByName('vrmsb').AsFloat  := APayload.ch[1].v;
          Q.ParamByName('irmsb').AsFloat  := APayload.ch[1].i;
          Q.ParamByName('apb').AsFloat    := APayload.ch[1].p;
          Q.ParamByName('rpb').AsFloat    := APayload.ch[1].q;
          Q.ParamByName('appb').AsFloat   := APayload.ch[1].s;
        end;

        Q.ParamByName('pfc').AsFloat      := APayload.ch[2].pf;
        Q.ParamByName('vrmsc').AsFloat    := APayload.ch[2].v;
        Q.ParamByName('irmsc').AsFloat    := APayload.ch[2].i;
        Q.ParamByName('apc').AsFloat      := APayload.ch[2].p;
        Q.ParamByName('rpc').AsFloat      := APayload.ch[2].q;
        Q.ParamByName('appc').AsFloat     := APayload.ch[2].s;

        Q.ExecSQL;
      except
        on E: Exception do
          if Assigned(FOnError) then FOnError('[이력저장 오류] ' + E.Message);
      end;
    end
    else
    begin
      // seq가 변경되지 않은 경우 단순히 통신 시간만 갱신 (DB 부하 대폭 감소)
      Q.SQL.Text := 'UPDATE nilm_data SET received_at=NOW() WHERE device_id = :did';
      Q.ParamByName('did').AsInteger := ADeviceID;
      Q.ExecSQL;
    end;
  finally
    Q.Free;
  end;
end;

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

  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
  Payload: TNILMMeasurementPayload;
begin
  Payload.Clear;
  Payload.seq   := ASeq;
  Payload.COMM  := ACommStatus;
  Payload.wire  := PHASE_3P4W;
  
  var jvWire := AData.GetValue('wire');
  if Assigned(jvWire) then Payload.wire := jvWire.Value;

  var jvRssi := AData.GetValue('rssi');
  if Assigned(jvRssi) then Payload.rssi := StrToIntDef(jvRssi.Value, 0);

  var jvFreq := AData.GetValue('frequency');
  if Assigned(jvFreq) then Payload.freq := StrToFloatDef(jvFreq.Value, 0.0);

  var jvWh := AData.GetValue('wh');
  if Assigned(jvWh) then Payload.wh := StrToInt64Def(jvWh.Value, 0);

  // A상
  Payload.ch[0].IsValid := True;
  Payload.ch[0].pf := GetNum(GetJsonKey('power_factor_a', 'power_factor_a'));
  Payload.ch[0].v  := GetNum(GetJsonKey('vrms_a', 'vrms_a'));
  Payload.ch[0].i  := GetNum(GetJsonKey('irms_a', 'irms_a'));
  Payload.ch[0].p  := GetNum(GetJsonKey('active_power_a', 'active_power_a'));
  Payload.ch[0].q  := GetNum(GetJsonKey('reactive_power_a', 'reactive_power_a'));
  Payload.ch[0].s  := GetNum(GetJsonKey('apparent_power_a', 'apparent_power_a'));

  // B상
  if Payload.wire <> PHASE_3P3W then
  begin
    Payload.ch[1].IsValid := True;
    Payload.ch[1].pf := GetNum(GetJsonKey('power_factor_b', 'power_factor_b'));
    Payload.ch[1].v  := GetNum(GetJsonKey('vrms_b', 'vrms_b'));
    Payload.ch[1].i  := GetNum(GetJsonKey('irms_b', 'irms_b'));
    Payload.ch[1].p  := GetNum(GetJsonKey('active_power_b', 'active_power_b'));
    Payload.ch[1].q  := GetNum(GetJsonKey('reactive_power_b', 'reactive_power_b'));
    Payload.ch[1].s  := GetNum(GetJsonKey('apparent_power_b', 'apparent_power_b'));
  end;

  // C상
  Payload.ch[2].IsValid := True;
  Payload.ch[2].pf := GetNum(GetJsonKey('power_factor_c', 'power_factor_c'));
  Payload.ch[2].v  := GetNum(GetJsonKey('vrms_c', 'vrms_c'));
  Payload.ch[2].i  := GetNum(GetJsonKey('irms_c', 'irms_c'));
  Payload.ch[2].p  := GetNum(GetJsonKey('active_power_c', 'active_power_c'));
  Payload.ch[2].q  := GetNum(GetJsonKey('reactive_power_c', 'reactive_power_c'));
  Payload.ch[2].s  := GetNum(GetJsonKey('apparent_power_c', 'apparent_power_c'));

  // Total
  Payload.total.p  := GetNum('total_p');
  if (Payload.total.p = 0) and (Payload.ch[0].p > 0) then
  begin
    if Payload.wire = PHASE_3P3W then
      Payload.total.p := Payload.ch[0].p + Payload.ch[2].p
    else
      Payload.total.p := Payload.ch[0].p + Payload.ch[1].p + Payload.ch[2].p;
  end;
  Payload.total.q  := GetNum('total_q');
  Payload.total.s  := GetNum('total_s');
  Payload.total.pf := GetNum('total_pf');

  SaveMeasurementData(ADeviceID, ADeviceName, Payload, APayloadJSON, AOpStatus, ASensorList, bSeqChanged);
end;

procedure TNILMManager.SaveNodeStatus(ADeviceID: Integer;
  const AStatus: TNILMNodeStatusPayload);
var
  Q: TFDQuery;
  LCommStatus: Integer;
begin
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    if AStatus.online then LCommStatus := 0 else LCommStatus := 255;
    
    Q.SQL.Text :=
      'UPDATE nilm_data SET ' +
      '  comm_status = :comm, ' +
      '  received_at = NOW() ' +
      'WHERE device_id = :did';
    Q.ParamByName('comm').AsInteger := LCommStatus;
    Q.ParamByName('did').AsInteger  := ADeviceID;
    Q.ExecSQL;

    SaveEventLog('NODE_STATUS', ADeviceID,
      Format('[NodeStatus] online=%s, loss_rate=%.2f%% (polls:%d, resps:%d)',
        [BoolToStr(AStatus.online, True), AStatus.loss_rate * 100, AStatus.polls, AStatus.responses]),
      Format('gw=%s, dev=%s, last_seen_age_s=%d', [AStatus.gw, AStatus.dev, AStatus.last_seen_age_s]));
  finally
    Q.Free;
  end;
end;

procedure TNILMManager.SaveGatewayStatus(const AGateway: TNILMGatewayPayload);
var
  Q: TFDQuery;
begin
  if AGateway.gw = '' then Exit;
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    Q.SQL.Text :=
      'INSERT INTO nilm_gateway_status ' +
      '  (gw, online, nodes_total, nodes_online, cycle_ms, uptime_s, updated_at) ' +
      'VALUES (:gw, :online, :ntot, :nonl, :cyc, :upt, NOW()) ' +
      'ON CONFLICT (gw) DO UPDATE SET ' +
      '  online       = EXCLUDED.online, ' +
      '  nodes_total  = EXCLUDED.nodes_total, ' +
      '  nodes_online = EXCLUDED.nodes_online, ' +
      '  cycle_ms     = EXCLUDED.cycle_ms, ' +
      '  uptime_s     = EXCLUDED.uptime_s, ' +
      '  updated_at   = NOW()';
    Q.ParamByName('gw').AsString       := AGateway.gw;
    Q.ParamByName('online').AsBoolean  := AGateway.online;
    Q.ParamByName('ntot').AsInteger    := AGateway.nodes_total;
    Q.ParamByName('nonl').AsInteger    := AGateway.nodes_online;
    Q.ParamByName('cyc').AsInteger     := AGateway.cycle_ms;
    Q.ParamByName('upt').AsLargeInt    := AGateway.uptime_s;
    Q.ExecSQL;
  finally
    Q.Free;
  end;
end;

procedure TNILMManager.SaveNILMEvent(ADeviceID: Integer;
  const AEvent: TNILMEventPayload);
begin
  SaveEventLog('EVENT_' + AEvent.name, ADeviceID,
    Format('Alarm: %s (Code: %d, Value: %.2f, Seq: %d)', [AEvent.name, AEvent.code, AEvent.value, AEvent.seq]),
    Format('gw=%s, dev=%s, age_ms=%d', [AEvent.gw, AEvent.dev, AEvent.age_ms]));
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,' +
      '  wire, rssi, wh, total_p, total_q, total_s, total_pf,' +
      '  power_factor_a, vrms_a, irms_a, active_power_a, reactive_power_a, apparent_power_a,' +
      '  power_factor_b, vrms_b, irms_b, active_power_b, reactive_power_b, apparent_power_b,' +
      '  power_factor_c, vrms_c, irms_c, active_power_c, reactive_power_c, apparent_power_c,' +
      '  temperature, temp_ext, vibration, tilt_angle' +
      ' FROM nilm_data_history' +
      ' 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';

  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, mqtt_topic FROM nilm_device' +
    ' WHERE is_active = TRUE ORDER BY device_id';
  Result.Open;
end;

procedure TNILMManager.CalculateEnergyConsumption(ADeviceID: Integer;
  ADateFrom, ADateTo: TDateTime; var AEnergyL1, AEnergyL2, AEnergyL3: Double);
var
  Q: TFDQuery;
begin
  AEnergyL1 := 0.0;
  AEnergyL2 := 0.0;
  AEnergyL3 := 0.0;

  Q := TFDQuery.Create(nil);
  try
    Q.Connection := GetConnection;
    // 사다리꼴 적분법(또는 단순 리만합)을 활용하여 전력(W)을 누적 계산합니다.
    // 이전 시간(prev_time)과 현재 시간 사이의 간격을 초(second) 단위로 구하고, 
    // 전력(W)과 시간(h)을 곱하여 전력량(Wh)을 산출합니다.
    Q.SQL.Text :=
      'WITH HistoryWithLag AS (' +
      '  SELECT ' +
      '    active_power_a, active_power_b, active_power_c, apparent_power_a, apparent_power_b, apparent_power_c, reactive_power_a, reactive_power_b, reactive_power_c, ' +
      '    received_at,' +
      '    LAG(received_at) OVER (ORDER BY received_at) as prev_time' +
      '  FROM nilm_data_history' +
      '  WHERE device_id = :did AND received_at >= :dtfrom AND received_at <= :dtto' +
      ') ' +
      'SELECT ' +
      '  SUM( (CASE WHEN active_power_a > 0 THEN active_power_a ELSE SQRT(GREATEST(apparent_power_a*apparent_power_a - reactive_power_a*reactive_power_a, 0.0)) END) * EXTRACT(EPOCH FROM (received_at - prev_time)) / 3600.0 ) as energy_l1,' +
      '  SUM( (CASE WHEN active_power_b > 0 THEN active_power_b ELSE SQRT(GREATEST(apparent_power_b*apparent_power_b - reactive_power_b*reactive_power_b, 0.0)) END) * EXTRACT(EPOCH FROM (received_at - prev_time)) / 3600.0 ) as energy_l2,' +
      '  SUM( (CASE WHEN active_power_c > 0 THEN active_power_c ELSE SQRT(GREATEST(apparent_power_c*apparent_power_c - reactive_power_c*reactive_power_c, 0.0)) END) * EXTRACT(EPOCH FROM (received_at - prev_time)) / 3600.0 ) as energy_l3 ' +
      'FROM HistoryWithLag ' +
      'WHERE prev_time IS NOT NULL';
      
    Q.ParamByName('did').AsInteger := ADeviceID;
    Q.ParamByName('dtfrom').AsDateTime := ADateFrom;
    Q.ParamByName('dtto').AsDateTime := ADateTo;
    
    Q.Open;
    if not Q.IsEmpty then
    begin
      AEnergyL1 := Q.FieldByName('energy_l1').AsFloat;
      AEnergyL2 := Q.FieldByName('energy_l2').AsFloat;
      AEnergyL3 := Q.FieldByName('energy_l3').AsFloat;
    end;
  finally
    Q.Free;
  end;
end;

// ── 외부 DB (ETC) 연동 구현부 ─────────────────────────────

procedure TNILMManager.SaveExternalOpStatus(const ANILMName: string; AOpStatus: Integer);
var
  Q: TFDQuery;
begin
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := DM.fdConnEtc;
    if not Q.Connection.Connected then Exit;
    
    // 1. 실시간 상태 갱신 (Update)
    Q.SQL.Text := 'UPDATE inf_operation_active SET operation_status = :status, interface_dt = NOW() WHERE nilm = :nilmname';
    Q.ParamByName('status').AsInteger := AOpStatus;
    Q.ParamByName('nilmname').AsString := ANILMName;
    Q.ExecSQL;
    
    // 업데이트된 행이 없다면(최초) 새로 Insert
    if Q.RowsAffected = 0 then
    begin
      Q.SQL.Text := 'INSERT INTO inf_operation_active (nilm, operation_status, interface_dt) VALUES (:nilmname, :status, NOW())';
      Q.ParamByName('status').AsInteger := AOpStatus;
      Q.ParamByName('nilmname').AsString := ANILMName;
      Q.ExecSQL;
    end;

    // 2. 이력 추가 (Insert) - 상태 변경 시에만 호출된다고 가정
    Q.SQL.Text := 'INSERT INTO inf_operation_history (nilm, operation_status, interface_dt) VALUES (:nilmname, :status, NOW())';
    Q.ParamByName('status').AsInteger := AOpStatus;
    Q.ParamByName('nilmname').AsString := ANILMName;
    Q.ExecSQL;
    
  except
    on E: Exception do
    begin
      if Assigned(FOnError) then
        FOnError('[외부 DB 오류] 가동상태 업데이트 실패: ' + E.Message);
    end;
  end;
  Q.Free;
end;

procedure TNILMManager.SaveExternalDailyEnergy(const ANILMName: string; ADate: TDateTime; AEnergyValue: Double);
var
  Q: TFDQuery;
begin
  Q := TFDQuery.Create(nil);
  try
    Q.Connection := DM.fdConnEtc;
    if not Q.Connection.Connected then Exit;

    Q.SQL.Text := 'INSERT INTO inf_electricity_daily (nilm, electricity_value, interface_dt) VALUES (:nilmname, :val, :dt)';
    Q.ParamByName('nilmname').AsString := ANILMName;
    Q.ParamByName('val').AsFloat := AEnergyValue;
    Q.ParamByName('dt').AsDateTime := ADate;
    Q.ExecSQL;
  except
    on E: Exception do
    begin
      if Assigned(FOnError) then
        FOnError('[외부 DB 오류] 일일 전력량 전송 실패: ' + E.Message);
    end;
  end;
  Q.Free;
end;

end.