-- V014: AI 어시스턴트 메뉴 등록
-- 관리자 메뉴(MENU_TYPE='0')에 AI 어시스턴트 그룹 및 하위 7개 메뉴를 등록합니다.
-- SCREEN_GROUPS에 메뉴 그룹도 함께 등록하여 화면 그룹 트리와 연결합니다.
-- 멱등성: INSERT ... WHERE NOT EXISTS 사용 (unique constraint 없는 기존 테이블 대응).

-- ══════════════════════════════════════════════════════════════
-- 1. SCREEN_GROUPS: AI 어시스턴트 루트 그룹
-- ══════════════════════════════════════════════════════════════
INSERT INTO SCREEN_GROUPS (
    GROUP_NAME, GROUP_CODE, PARENT_GROUP_ID, GROUP_LEVEL,
    DISPLAY_ORDER, COMPANY_CODE, WRITER, HIERARCHY_PATH, DESCRIPTION, IS_ACTIVE, ICON
)
SELECT
    'AI 어시스턴트', 'AI_ASSISTANT', NULL, 0,
    9900, '*', 'system', 'AI_ASSISTANT', 'AI 멀티 에이전트 관리 메뉴 그룹', 'Y', 'robot'
WHERE NOT EXISTS (
    SELECT 1 FROM SCREEN_GROUPS
    WHERE GROUP_CODE = 'AI_ASSISTANT' AND COMPANY_CODE = '*'
);

-- ══════════════════════════════════════════════════════════════
-- 2. MENU_INFO: AI 어시스턴트 부모 메뉴 (관리자 메뉴, 루트)
-- ══════════════════════════════════════════════════════════════
INSERT INTO MENU_INFO (
    OBJID, MENU_TYPE, PARENT_OBJ_ID, MENU_NAME_KOR,
    MENU_URL, MENU_DESC, SEQ, WRITER, CREATED_DATE, STATUS, COMPANY_CODE, MENU_ICON
)
SELECT
    'AI_ASSISTANT_ROOT', '0', '0', 'AI 어시스턴트',
    '/admin/aiAssistant', 'AI 멀티 에이전트 관리',
    9900, 'system', NOW(), 'active', '*', 'robot'
WHERE NOT EXISTS (
    SELECT 1 FROM MENU_INFO WHERE OBJID = 'AI_ASSISTANT_ROOT'
);

-- ══════════════════════════════════════════════════════════════
-- 3. SCREEN_GROUPS: 하위 그룹 6개
--    PARENT_GROUP_ID는 위에서 삽입한 AI_ASSISTANT 그룹을 서브쿼리로 참조
-- ══════════════════════════════════════════════════════════════

-- 3-1. 에이전트 관리
INSERT INTO SCREEN_GROUPS (
    GROUP_NAME, GROUP_CODE, PARENT_GROUP_ID, GROUP_LEVEL,
    DISPLAY_ORDER, COMPANY_CODE, WRITER, HIERARCHY_PATH, DESCRIPTION, IS_ACTIVE, ICON
)
SELECT
    '에이전트 관리', 'AI_AGENTS', SG.ID, 1,
    9901, '*', 'system', 'AI_ASSISTANT/AI_AGENTS', 'LLM 에이전트 CRUD', 'Y', 'cpu'
FROM SCREEN_GROUPS SG
WHERE SG.GROUP_CODE = 'AI_ASSISTANT' AND SG.COMPANY_CODE = '*'
  AND NOT EXISTS (
      SELECT 1 FROM SCREEN_GROUPS WHERE GROUP_CODE = 'AI_AGENTS' AND COMPANY_CODE = '*'
  );

-- 3-2. LLM 프로바이더
INSERT INTO SCREEN_GROUPS (
    GROUP_NAME, GROUP_CODE, PARENT_GROUP_ID, GROUP_LEVEL,
    DISPLAY_ORDER, COMPANY_CODE, WRITER, HIERARCHY_PATH, DESCRIPTION, IS_ACTIVE, ICON
)
SELECT
    'LLM 프로바이더', 'AI_PROVIDERS', SG.ID, 1,
    9902, '*', 'system', 'AI_ASSISTANT/AI_PROVIDERS',
    'Anthropic/OpenAI/Google/Ollama 프로바이더 관리', 'Y', 'cloud'
FROM SCREEN_GROUPS SG
WHERE SG.GROUP_CODE = 'AI_ASSISTANT' AND SG.COMPANY_CODE = '*'
  AND NOT EXISTS (
      SELECT 1 FROM SCREEN_GROUPS WHERE GROUP_CODE = 'AI_PROVIDERS' AND COMPANY_CODE = '*'
  );

-- 3-3. 멀티에이전트 워크스페이스
INSERT INTO SCREEN_GROUPS (
    GROUP_NAME, GROUP_CODE, PARENT_GROUP_ID, GROUP_LEVEL,
    DISPLAY_ORDER, COMPANY_CODE, WRITER, HIERARCHY_PATH, DESCRIPTION, IS_ACTIVE, ICON
)
SELECT
    '멀티에이전트 워크스페이스', 'AI_WORKSPACE', SG.ID, 1,
    9903, '*', 'system', 'AI_ASSISTANT/AI_WORKSPACE', '에이전트 그룹 조립 및 실행', 'Y', 'diagram-3'
FROM SCREEN_GROUPS SG
WHERE SG.GROUP_CODE = 'AI_ASSISTANT' AND SG.COMPANY_CODE = '*'
  AND NOT EXISTS (
      SELECT 1 FROM SCREEN_GROUPS WHERE GROUP_CODE = 'AI_WORKSPACE' AND COMPANY_CODE = '*'
  );

-- 3-4. 대화 모니터링
INSERT INTO SCREEN_GROUPS (
    GROUP_NAME, GROUP_CODE, PARENT_GROUP_ID, GROUP_LEVEL,
    DISPLAY_ORDER, COMPANY_CODE, WRITER, HIERARCHY_PATH, DESCRIPTION, IS_ACTIVE, ICON
)
SELECT
    '대화 모니터링', 'AI_CONVERSATIONS', SG.ID, 1,
    9904, '*', 'system', 'AI_ASSISTANT/AI_CONVERSATIONS', '에이전트 대화 메시지 열람', 'Y', 'chat-dots'
FROM SCREEN_GROUPS SG
WHERE SG.GROUP_CODE = 'AI_ASSISTANT' AND SG.COMPANY_CODE = '*'
  AND NOT EXISTS (
      SELECT 1 FROM SCREEN_GROUPS WHERE GROUP_CODE = 'AI_CONVERSATIONS' AND COMPANY_CODE = '*'
  );

-- 3-5. API 키 관리
INSERT INTO SCREEN_GROUPS (
    GROUP_NAME, GROUP_CODE, PARENT_GROUP_ID, GROUP_LEVEL,
    DISPLAY_ORDER, COMPANY_CODE, WRITER, HIERARCHY_PATH, DESCRIPTION, IS_ACTIVE, ICON
)
SELECT
    'API 키 관리', 'AI_API_KEYS', SG.ID, 1,
    9905, '*', 'system', 'AI_ASSISTANT/AI_API_KEYS', 'sk-pipe-* API 키 발급 및 폐기', 'Y', 'key'
FROM SCREEN_GROUPS SG
WHERE SG.GROUP_CODE = 'AI_ASSISTANT' AND SG.COMPANY_CODE = '*'
  AND NOT EXISTS (
      SELECT 1 FROM SCREEN_GROUPS WHERE GROUP_CODE = 'AI_API_KEYS' AND COMPANY_CODE = '*'
  );

-- 3-6. 지식 라이브러리
INSERT INTO SCREEN_GROUPS (
    GROUP_NAME, GROUP_CODE, PARENT_GROUP_ID, GROUP_LEVEL,
    DISPLAY_ORDER, COMPANY_CODE, WRITER, HIERARCHY_PATH, DESCRIPTION, IS_ACTIVE, ICON
)
SELECT
    '지식 라이브러리', 'AI_KNOWLEDGE', SG.ID, 1,
    9906, '*', 'system', 'AI_ASSISTANT/AI_KNOWLEDGE', '지식 파일 업로드 및 관리', 'Y', 'book'
FROM SCREEN_GROUPS SG
WHERE SG.GROUP_CODE = 'AI_ASSISTANT' AND SG.COMPANY_CODE = '*'
  AND NOT EXISTS (
      SELECT 1 FROM SCREEN_GROUPS WHERE GROUP_CODE = 'AI_KNOWLEDGE' AND COMPANY_CODE = '*'
  );

-- ══════════════════════════════════════════════════════════════
-- 4. MENU_INFO: 하위 메뉴 7개 (MENU_TYPE='0', 관리자 메뉴)
-- ══════════════════════════════════════════════════════════════

INSERT INTO MENU_INFO (
    OBJID, MENU_TYPE, PARENT_OBJ_ID, MENU_NAME_KOR,
    MENU_URL, MENU_DESC, SEQ, WRITER, CREATED_DATE, STATUS, COMPANY_CODE, MENU_ICON
)
SELECT 'AI_MENU_ASSISTANT', '0', 'AI_ASSISTANT_ROOT', 'AI 어시스턴트',
       '/admin/aiAssistant', 'AI 어시스턴트 대시보드 (워크스페이스 리다이렉트)',
       9901, 'system', NOW(), 'active', '*', 'robot'
WHERE NOT EXISTS (SELECT 1 FROM MENU_INFO WHERE OBJID = 'AI_MENU_ASSISTANT');

INSERT INTO MENU_INFO (
    OBJID, MENU_TYPE, PARENT_OBJ_ID, MENU_NAME_KOR,
    MENU_URL, MENU_DESC, SEQ, WRITER, CREATED_DATE, STATUS, COMPANY_CODE, MENU_ICON
)
SELECT 'AI_MENU_AGENTS', '0', 'AI_ASSISTANT_ROOT', '에이전트 관리',
       '/admin/aiAssistant/agents', 'LLM 에이전트 CRUD',
       9902, 'system', NOW(), 'active', '*', 'cpu'
WHERE NOT EXISTS (SELECT 1 FROM MENU_INFO WHERE OBJID = 'AI_MENU_AGENTS');

INSERT INTO MENU_INFO (
    OBJID, MENU_TYPE, PARENT_OBJ_ID, MENU_NAME_KOR,
    MENU_URL, MENU_DESC, SEQ, WRITER, CREATED_DATE, STATUS, COMPANY_CODE, MENU_ICON
)
SELECT 'AI_MENU_PROVIDERS', '0', 'AI_ASSISTANT_ROOT', 'LLM 프로바이더',
       '/admin/aiAssistant/providers', 'Anthropic/OpenAI/Google/Ollama 프로바이더 설정',
       9903, 'system', NOW(), 'active', '*', 'cloud'
WHERE NOT EXISTS (SELECT 1 FROM MENU_INFO WHERE OBJID = 'AI_MENU_PROVIDERS');

INSERT INTO MENU_INFO (
    OBJID, MENU_TYPE, PARENT_OBJ_ID, MENU_NAME_KOR,
    MENU_URL, MENU_DESC, SEQ, WRITER, CREATED_DATE, STATUS, COMPANY_CODE, MENU_ICON
)
SELECT 'AI_MENU_WORKSPACE', '0', 'AI_ASSISTANT_ROOT', '멀티에이전트 워크스페이스',
       '/admin/aiAssistant/workspace', '에이전트 그룹 조립 및 실행',
       9904, 'system', NOW(), 'active', '*', 'diagram-3'
WHERE NOT EXISTS (SELECT 1 FROM MENU_INFO WHERE OBJID = 'AI_MENU_WORKSPACE');

INSERT INTO MENU_INFO (
    OBJID, MENU_TYPE, PARENT_OBJ_ID, MENU_NAME_KOR,
    MENU_URL, MENU_DESC, SEQ, WRITER, CREATED_DATE, STATUS, COMPANY_CODE, MENU_ICON
)
SELECT 'AI_MENU_CONVERSATIONS', '0', 'AI_ASSISTANT_ROOT', '대화 모니터링',
       '/admin/aiAssistant/conversations', '에이전트 대화 메시지 열람',
       9905, 'system', NOW(), 'active', '*', 'chat-dots'
WHERE NOT EXISTS (SELECT 1 FROM MENU_INFO WHERE OBJID = 'AI_MENU_CONVERSATIONS');

INSERT INTO MENU_INFO (
    OBJID, MENU_TYPE, PARENT_OBJ_ID, MENU_NAME_KOR,
    MENU_URL, MENU_DESC, SEQ, WRITER, CREATED_DATE, STATUS, COMPANY_CODE, MENU_ICON
)
SELECT 'AI_MENU_API_KEYS', '0', 'AI_ASSISTANT_ROOT', 'API 키 관리',
       '/admin/aiAssistant/api-keys-manage', 'sk-pipe-* API 키 발급 및 폐기',
       9906, 'system', NOW(), 'active', '*', 'key'
WHERE NOT EXISTS (SELECT 1 FROM MENU_INFO WHERE OBJID = 'AI_MENU_API_KEYS');

INSERT INTO MENU_INFO (
    OBJID, MENU_TYPE, PARENT_OBJ_ID, MENU_NAME_KOR,
    MENU_URL, MENU_DESC, SEQ, WRITER, CREATED_DATE, STATUS, COMPANY_CODE, MENU_ICON
)
SELECT 'AI_MENU_KNOWLEDGE', '0', 'AI_ASSISTANT_ROOT', '지식 라이브러리',
       '/admin/aiAssistant/knowledge', '지식 파일 업로드 및 관리',
       9907, 'system', NOW(), 'active', '*', 'book'
WHERE NOT EXISTS (SELECT 1 FROM MENU_INFO WHERE OBJID = 'AI_MENU_KNOWLEDGE');

-- ══════════════════════════════════════════════════════════════
-- 5. SCREEN_GROUPS.MENU_OBJID 연결 (루트 그룹 → 루트 메뉴)
-- ══════════════════════════════════════════════════════════════
UPDATE SCREEN_GROUPS
SET MENU_OBJID = 'AI_ASSISTANT_ROOT'
WHERE GROUP_CODE = 'AI_ASSISTANT'
  AND COMPANY_CODE = '*'
  AND MENU_OBJID IS NULL;
