-- 1. V36 롤백 (컬럼 삭제 - MySQL 5.7용)
SET @col_exists_poc_drop = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'aRMS'
      AND TABLE_NAME = 'T_ARMS_POC'
      AND COLUMN_NAME = 'c_poc_assignee_link'
);

SET @query_poc_drop = IF(@col_exists_poc_drop > 0,
    'ALTER TABLE `aRMS`.`T_ARMS_POC` DROP COLUMN `c_poc_assignee_link`',
    'SELECT ''Column c_poc_assignee_link does not exist in T_ARMS_POC'' AS message'
);

PREPARE stmt_poc_drop FROM @query_poc_drop;
EXECUTE stmt_poc_drop;
DEALLOCATE PREPARE stmt_poc_drop;

SET @col_exists_poc_log_drop = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'aRMS'
      AND TABLE_NAME = 'T_ARMS_POC_LOG'
      AND COLUMN_NAME = 'c_poc_assignee_link'
);

SET @query_poc_log_drop = IF(@col_exists_poc_log_drop > 0,
    'ALTER TABLE `aRMS`.`T_ARMS_POC_LOG` DROP COLUMN `c_poc_assignee_link`',
    'SELECT ''Column c_poc_assignee_link does not exist in T_ARMS_POC_LOG'' AS message'
);

PREPARE stmt_poc_log_drop FROM @query_poc_log_drop;
EXECUTE stmt_poc_log_drop;
DEALLOCATE PREPARE stmt_poc_log_drop;

-- 2. V35 롤백 (T_ARMS_POC_ASSIGNEE 관련)
DROP TRIGGER IF EXISTS TG_DELETE_T_ARMS_POC_ASSIGNEE;
DROP TRIGGER IF EXISTS TG_UPDATE_T_ARMS_POC_ASSIGNEE;
DROP TRIGGER IF EXISTS TG_INSERT_T_ARMS_POC_ASSIGNEE;

DROP TABLE IF EXISTS `aRMS`.`T_ARMS_POC_ASSIGNEE`;
DROP TABLE IF EXISTS `aRMS`.`T_ARMS_POC_ASSIGNEE_LOG`;

-- 3. V34 롤백 (스킵 - Database MODIFY is idempotent)

-- 여기서부터 V34~V36 재적용
-- V34 재적용
ALTER TABLE `aRMS`.`T_ARMS_PATCHNOTE`
    MODIFY `c_patchnote_thumbnail_url` LONGTEXT;

-- V35 재적용
CREATE TABLE IF NOT EXISTS `aRMS`.`T_ARMS_POC_ASSIGNEE_LOG` (
    `c_id`                                  bigint(20) NOT NULL COMMENT '노드 아이디',
    `c_parentid`                            bigint(20) NOT NULL COMMENT '부모 노드 아이디',
    `c_position`                            bigint(20) NOT NULL COMMENT '노드 포지션',
    `c_left`                                bigint(20) NOT NULL COMMENT '노드 좌측 끝 포인트',
    `c_right`                               bigint(20) NOT NULL COMMENT '노드 우측 끝 포인트',
    `c_level`                               bigint(20) NOT NULL COMMENT '노드 DEPTH',
    `c_title`                               VARCHAR(255) COMMENT '노드 명',
    `c_type`                                VARCHAR(255) COMMENT '노드 타입',
    `c_method`                              text NULL COMMENT '노드 변경 행위',
    `c_state`                               text NULL COMMENT '노드 상태값 ( 이전인지. 이후인지)',
    `c_date`                                DATETIME NULL COMMENT '노드 변경 시',
    `c_poc_assignee_name`                   VARCHAR(255) COMMENT 'PoC 담당자 이름',
    `c_poc_assignee_email`                  VARCHAR(255) COMMENT 'PoC 담당자 이메일',
    `c_poc_assignee_department`             VARCHAR(255) COMMENT 'PoC 담당자 부서',
    `c_poc_assignee_position`               VARCHAR(255) COMMENT 'PoC 담당자 직급',
    `c_poc_assignee_phone`                  VARCHAR(255) COMMENT 'PoC 담당자 전화번호',
    `c_poc_assignee_photo_url`              text COMMENT 'PoC 담당자 사진 URL',
    `c_poc_assignee_education`              text COMMENT 'PoC 담당자 학력',
    `c_poc_assignee_career`                 text COMMENT 'PoC 담당자 커리어',
    `c_poc_assignee_specialty`              VARCHAR(255) COMMENT 'PoC 담당자 전문분야',
    `c_poc_assignee_is_active`              TINYINT(1) COMMENT '활성 상태',
    `c_poc_assignee_etc`                 	VARCHAR(255) COMMENT '비고',
    `c_poc_assignee_desc`                	text COMMENT '설명',
    `c_poc_assignee_contents`            	longtext COMMENT '내용'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='POC 담당자 테이블 로그';

CREATE TABLE IF NOT EXISTS `aRMS`.`T_ARMS_POC_ASSIGNEE` (
                                                            `c_id`                                  bigint(20) AUTO_INCREMENT primary key COMMENT '노드 아이디',
    `c_parentid`                            bigint(20) NOT NULL COMMENT '부모 노드 아이디',
    `c_position`                            bigint(20) NOT NULL COMMENT '노드 포지션',
    `c_left`                                bigint(20) NOT NULL COMMENT '노드 좌측 끝 포인트',
    `c_right`                               bigint(20) NOT NULL COMMENT '노드 우측 끝 포인트',
    `c_level`                               bigint(20) NOT NULL COMMENT '노드 DEPTH',
    `c_title`                               VARCHAR(255) COMMENT '노드 명',
    `c_type`                                VARCHAR(255) COMMENT '노드 타입',
    `c_poc_assignee_name`                   VARCHAR(255) COMMENT 'PoC 담당자 이름',
    `c_poc_assignee_email`                  VARCHAR(255) COMMENT 'PoC 담당자 이메일',
    `c_poc_assignee_department`             VARCHAR(255) COMMENT 'PoC 담당자 부서',
    `c_poc_assignee_position`               VARCHAR(255) COMMENT 'PoC 담당자 직급',
    `c_poc_assignee_phone`                  VARCHAR(255) COMMENT 'PoC 담당자 전화번호',
    `c_poc_assignee_photo_url`              text COMMENT 'PoC 담당자 사진 URL',
    `c_poc_assignee_education`              text COMMENT 'PoC 담당자 학력',
    `c_poc_assignee_career`                 text COMMENT 'PoC 담당자 커리어',
    `c_poc_assignee_specialty`              VARCHAR(255) COMMENT 'PoC 담당자 전문분야',
    `c_poc_assignee_is_active`              TINYINT(1) DEFAULT 1 COMMENT '활성 상태 (1: 활성, 0: 비활성)',
    `c_poc_assignee_etc`                 	VARCHAR(255) COMMENT '비고',
    `c_poc_assignee_desc`                	text COMMENT '설명',
    `c_poc_assignee_contents`            	longtext COMMENT '내용'
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='POC 담당자 테이블';

DELIMITER $$
CREATE TRIGGER TG_INSERT_T_ARMS_POC_ASSIGNEE
    BEFORE INSERT ON T_ARMS_POC_ASSIGNEE
    FOR EACH ROW
BEGIN
    insert into T_ARMS_POC_ASSIGNEE_LOG (C_ID, C_PARENTID, C_POSITION, C_LEFT, C_RIGHT, C_LEVEL, C_TITLE, C_TYPE, C_METHOD, C_STATE, C_DATE)
    values (NEW.C_ID,NEW.C_PARENTID,NEW.C_POSITION,NEW.C_LEFT,NEW.C_RIGHT,NEW.C_LEVEL,NEW.C_TITLE,NEW.C_TYPE,'insert','변경이전데이터',now());
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER TG_UPDATE_T_ARMS_POC_ASSIGNEE
    BEFORE UPDATE ON T_ARMS_POC_ASSIGNEE
    FOR EACH ROW
BEGIN
    insert into T_ARMS_POC_ASSIGNEE_LOG (C_ID, C_PARENTID, C_POSITION, C_LEFT, C_RIGHT, C_LEVEL, C_TITLE, C_TYPE, C_METHOD, C_STATE, C_DATE)
    values (OLD.C_ID,OLD.C_PARENTID,OLD.C_POSITION,OLD.C_LEFT,OLD.C_RIGHT,OLD.C_LEVEL,OLD.C_TITLE,OLD.C_TYPE,'update','변경이전데이터',now());
    insert into T_ARMS_POC_ASSIGNEE_LOG (C_ID, C_PARENTID, C_POSITION, C_LEFT, C_RIGHT, C_LEVEL, C_TITLE, C_TYPE, C_METHOD, C_STATE, C_DATE)
    values (NEW.C_ID,NEW.C_PARENTID,NEW.C_POSITION,NEW.C_LEFT,NEW.C_RIGHT,NEW.C_LEVEL,NEW.C_TITLE,NEW.C_TYPE,'update','변경이후데이터',now());
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER TG_DELETE_T_ARMS_POC_ASSIGNEE
    BEFORE DELETE ON T_ARMS_POC_ASSIGNEE
    FOR EACH ROW
BEGIN
    insert into T_ARMS_POC_ASSIGNEE_LOG (C_ID, C_PARENTID, C_POSITION, C_LEFT, C_RIGHT, C_LEVEL, C_TITLE, C_TYPE, C_METHOD, C_STATE, C_DATE)
    values (OLD.C_ID,OLD.C_PARENTID,OLD.C_POSITION,OLD.C_LEFT,OLD.C_RIGHT,OLD.C_LEVEL,OLD.C_TITLE,OLD.C_TYPE,'delete','삭제된데이터',now());
END $$
DELIMITER ;

-- V35 초기 데이터 (멱등성 있게)
INSERT IGNORE INTO `aRMS`.`T_ARMS_POC_ASSIGNEE` (C_ID, C_PARENTID, C_POSITION, C_LEFT, C_RIGHT, C_LEVEL, C_TITLE, C_TYPE)
VALUES (1, 0, 0, 1, 4, 0, 'T_ARMS_POC_ASSIGNEE', 'root');

INSERT IGNORE INTO `aRMS`.`T_ARMS_POC_ASSIGNEE` (C_ID, C_PARENTID, C_POSITION, C_LEFT, C_RIGHT, C_LEVEL, C_TITLE, C_TYPE)
VALUES (2, 1, 0, 2, 3, 1, 'POC 담당자', 'drive');

-- V36 재적용 (MySQL 5.7용 - 컬럼 존재 여부 체크)
SET @col_exists_poc = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'aRMS'
    AND TABLE_NAME = 'T_ARMS_POC'
    AND COLUMN_NAME = 'c_poc_assignee_link'
);

SET @query_poc = IF(@col_exists_poc = 0,
    'ALTER TABLE `aRMS`.`T_ARMS_POC` ADD COLUMN `c_poc_assignee_link` bigint(20) COMMENT ''POC 담당자 링크 (T_ARMS_POC_ASSIGNEE의 c_id 참조)'' AFTER `c_poc_assignee_id`',
    'SELECT ''Column c_poc_assignee_link already exists in T_ARMS_POC'' AS message'
);

PREPARE stmt_poc FROM @query_poc;
EXECUTE stmt_poc;
DEALLOCATE PREPARE stmt_poc;

SET @col_exists_poc_log = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'aRMS'
    AND TABLE_NAME = 'T_ARMS_POC_LOG'
    AND COLUMN_NAME = 'c_poc_assignee_link'
);

SET @query_poc_log = IF(@col_exists_poc_log = 0,
    'ALTER TABLE `aRMS`.`T_ARMS_POC_LOG` ADD COLUMN `c_poc_assignee_link` bigint(20) COMMENT ''POC 담당자 링크 (T_ARMS_POC_ASSIGNEE의 c_id 참조)'' AFTER `c_poc_assignee_id`',
    'SELECT ''Column c_poc_assignee_link already exists in T_ARMS_POC_LOG'' AS message'
);

PREPARE stmt_poc_log FROM @query_poc_log;
EXECUTE stmt_poc_log;
DEALLOCATE PREPARE stmt_poc_log;