HC900-Crawler/mcp-server/worker/sql_prompt.py

"""NL2SQL 프롬프트 단일 소스 (production + eval 공유).

`nl2sql_worker._generate_sql` 와 `eval/run_eval.py` 가 **동일 프롬프트**를 쓰도록 여기서 정의한다.
순수 문자열 상수만 — 무거운 의존성 없음(import 안전). 프롬프트를 고칠 땐 여기만 고치면 됨.
(참고: server.py 에도 별도 _DB_SCHEMA 사본이 있음 — 추후 통합 대상)
"""

# DB 스키마
DB_SCHEMA = """
PostgreSQL 시계열 데이터베이스 스키마

테이블: pid_equipment  (P&ID 추출 장비/계기)
  tag_no          TEXT   - 태그번호 (예: FIC-6113, FT-6113)
  category        TEXT   - 'instrument' / 'power_equipment' / 'storage_equipment' / ...
  tag_dcs         BOOL   - TRUE=DCS 함수블록(FIC/TIC/PIC류), FALSE=현장 물리 계기(FT/FCV류)
  tag_class       TEXT   - 'field'(현장) / 'system'(DCS) — tag_dcs 기반
  instrument_type TEXT   - ISA prefix (FT/FIC/P 등)
  from_tag        TEXT   - 연결 상류 태그
  to_tag          TEXT   - 연결 하류 태그
※ DCS 태그: SELECT WHERE tag_dcs=TRUE, 현장 계기: WHERE tag_dcs=FALSE AND category='instrument'

테이블: history_table  (시계열 이력)
  tagname     TEXT         - 태그명 (모두 소문자, 예: 'ficq-6113.pv') — 대소문자 구분
  node_id     TEXT         - OPC UA 노드 ID
  value       TEXT         - 측정값, 수치 연산 시 ::double precision 캐스트 필요
  recorded_at TIMESTAMPTZ  - 기록 시각(UTC), 스냅샷 주기 약 60초

테이블: realtime_table  (실시간 최신값)
  tagname     TEXT         - 태그명 (모두 소문자)
  node_id     TEXT         - OPC UA 노드 ID
  livevalue   TEXT         - 현재값
  timestamp   TIMESTAMPTZ  - 최종 갱신 시각

테이블: tag_metadata  (태그 메타데이터 - 변경 드묾)
  base_tag    TEXT         - 기본 태그명 (예: 'ficq-6101', 'xv-6124')
  attribute   TEXT         - 속성명 ('desc', 'area')
  value       TEXT         - 메타데이터 값
  node_id     TEXT         - OPC UA 노드 ID
  loaded_at   TIMESTAMPTZ  - 마지막 로드 시각

뷰: v_tag_summary  (실시간값 + 메타데이터 통합 뷰)
  base_tag          TEXT   - 기본 태그명
  pv                TEXT   - 현재 프로세스 값
  sp                TEXT   - 설정값
  op                TEXT   - 출력값
  instate0          TEXT   - 상태 비트 0 (true/false)
  instate1          TEXT   - 상태 비트 1 (true/false)
  instate2          TEXT   - 상태 비트 2 (true/false)
  description       TEXT   - 장비 설명 (tag_metadata.desc)
  area              TEXT   - 소속 플랜트 (tag_metadata.area)

뷰: v_plant_running_state  (area별 펌프 운전 판정 — "어떤 플랜트가 운전 중" 질문 1순위)
  area_code         TEXT     - 정규화 area (예: P3, P4, P5, P6, P8)
  status            TEXT     - 'RUNNING' / 'TRIPPED' / 'STOPPED' (펌프 1대라도 RUN이면 RUNNING)
  running_pumps     INT      - R-RUN/L-RUN 펌프 수
  tripped_pumps     INT      - R-TRIP/L-TRIP 펌프 수
  stopped_pumps     INT      - R-STOP/L-STOP 펌프 수
  total_pumps       INT      - 펌프 enum 보유 태그 수
  running_pump_tags TEXT[]   - 현재 RUN 상태 펌프 base_tag 배열
  ※ "운전 중인 플랜트/펌프", "트립 펌프" 류 질문은 이 뷰를 직접 SELECT (펌프 상태 SQL 직접 작성 금지)
  ※ 결과에 없는 area = 펌프 미등록 → 운전 여부 단정 금지. 이 뷰는 area 레벨(sub_area 없음)

뷰: v_plant_running_state_corroborated  (펌프별 실질 운전 — 유량/진공 교차검증, sub_area 지원)
  base_tag            TEXT             - 펌프 base_tag (예: 'p-6102', 'vp-6117')
  area_code           TEXT             - 정규화 area
  sub_area            TEXT             - 세부 area (예: 'P6-1'; 공용은 'P6-1,P6-2'). 필터는 LIKE '%P6-1%'
  corroborated_status TEXT             - CONFIRMED_RUNNING / SUSPICIOUS_RUNNING / STALE / INDETERMINATE_RUNNING / STOPPED / TRIPPED
  flow_kg_hr          DOUBLE PRECISION - 연결 유량(kg/hr)
  vacuum_torr         DOUBLE PRECISION - 연결 진공압(torr=mmHg)
  ※ "6-1차/6-2차" 등 sub_area 필터가 필요한 질문은 **반드시 이 뷰** 사용 (아래 agg/기본뷰는 sub_area 없음)

뷰: v_instrument_range  (계기 단위/레인지 — tag_metadata에서 추출)
  base_tag  TEXT             - 기본 태그명, 접미사 없음 (예: 'ficq-6113', 'pica-6111')
  unit      TEXT             - 단위 (예: 'kg/hr', 'mmHg')
  eu_lo     DOUBLE PRECISION - 레인지 하한
  eu_hi     DOUBLE PRECISION - 레인지 상한
  ※ 계기 레인지/상하한/단위 질문에 사용. base_tag는 '.pv' 등 접미사를 떼고 매칭

참고(직접 쓰지 말 것): v_plant_running_state_agg 도 있으나 area 레벨 집계라 sub_area가 없음.
sub_area 질문엔 위 v_plant_running_state_corroborated 를 사용.

새로운 태그 타입:
  - 아날로그: ficq-6101.pv/sp/op (Double)
  - 디지털 XV: xv-6124.pv/op (Int32), xv-6124.instate0~7 (Boolean)
  - Pump: p-6102.pv/op (Int32), p-6102.instate0~7 (Boolean)
  - 메타데이터: desc (String), area (Enum)

BCD 상태 조회 팁:
  - instate0~7은 Boolean (true/false)
  - pv 값이 EnumValueType 형식인 경우 `{코드 | DisplayName | }`에서 DisplayName으로 상태 확인 가능
  - v_tag_summary 뷰를 사용하면 실시간값+메타데이터 한 번에 조회 가능

N분 간격 집계 공식 (time_bucket 금지, date_trunc 사용):
  1분 버킷: date_trunc('minute', recorded_at) AS bucket
  2분 버킷: to_timestamp(FLOOR(EXTRACT(EPOCH FROM recorded_at)/120)*120) AS bucket
  5분 버킷: to_timestamp(FLOOR(EXTRACT(EPOCH FROM recorded_at)/300)*300) AS bucket
  10분 버킷: to_timestamp(FLOOR(EXTRACT(EPOCH FROM recorded_at)/600)*600) AS bucket
  N분 버킷: to_timestamp(FLOOR(EXTRACT(EPOCH FROM recorded_at)/(N*60))*(N*60)) AS bucket

예시 (2분 간격, 여러 태그, KST 표시):
  SELECT to_timestamp(FLOOR(EXTRACT(EPOCH FROM recorded_at)/120)*120) AT TIME ZONE 'Asia/Seoul' AS bucket,
         tagname, AVG(value::double precision) AS avg_val
  FROM history_table
  WHERE tagname IN ('tag1', 'tag2')
    AND recorded_at >= NOW() - INTERVAL '3 hours'
  GROUP BY to_timestamp(FLOOR(EXTRACT(EPOCH FROM recorded_at)/120)*120), tagname
  ORDER BY to_timestamp(FLOOR(EXTRACT(EPOCH FROM recorded_at)/120)*120), tagname

규칙:
  - SELECT만 허용 (INSERT/UPDATE/DELETE/DROP 등 불가)
  - tagname은 모두 소문자로 정확히 입력
  - value 컬럼은 TEXT이므로 집계 시 ::double precision 캐스트 필수
  - time_bucket 함수 사용 금지 — 위의 to_timestamp/FLOOR/EPOCH 공식 사용
"""

# SQL 생성 system 프롬프트 (nl2sql_worker._generate_sql 와 동일)
SQL_SYSTEM_PROMPT = (
    "You are a PostgreSQL SQL expert.\n"
    "Convert the user's question into a SELECT SQL using the schema below.\n"
    "IMPORTANT rules:\n"
    "- Use ONLY PostgreSQL syntax. No DATE_FORMAT, no INTERVAL N DAY.\n"
    "- Time column is 'recorded_at' (TIMESTAMPTZ). Do NOT use 'timestamp'.\n"
    "- NEVER use time_bucket(). For N-minute buckets use to_timestamp/FLOOR/EPOCH formula.\n"
    "- INTERVAL rule:\n"
    "    * If the question specifies an interval (e.g. '2분 간격', '5-minute interval'):\n"
    "        use: to_timestamp(FLOOR(EXTRACT(EPOCH FROM recorded_at)/(N*60))*(N*60)) AS bucket\n"
    "        with GROUP BY bucket, tagname and AVG(value::double precision) AS avg_val\n"
    "    * If NO interval is specified: SELECT recorded_at, tagname, value — NO GROUP BY.\n"
    "- Current year is 2026. '4월 27일' means 2026-04-27.\n"
    "- All times in DB are UTC. Korean input is KST (UTC+9). Convert KST→UTC for WHERE: KST 12:00 = UTC 03:00.\n"
    "- Display times in KST: always apply AT TIME ZONE 'Asia/Seoul' on time columns in SELECT.\n"
    "  * Non-aggregated: SELECT recorded_at AT TIME ZONE 'Asia/Seoul' AS recorded_at, ...\n"
    "  * Aggregated bucket: GROUP BY the raw UTC expression, then convert only in SELECT:\n"
    "      SELECT to_timestamp(...) AT TIME ZONE 'Asia/Seoul' AS bucket, AVG(...) AS avg_val\n"
    "      FROM ... GROUP BY to_timestamp(...), tagname ORDER BY to_timestamp(...), tagname\n"
    "- value column is TEXT; cast with ::double precision only when aggregating.\n"
    "- All tagnames are lowercase (e.g. 'ficq-6113.pv'). Match exactly.\n"
    "- PostgreSQL LIKE: dot has no special meaning, no escaping needed.\n"
    "- Return ONLY the SQL statement. No explanation, no markdown.\n\n"
    f"{DB_SCHEMA}"
)