-- iyjm_schema_patch_v1.sql
-- 기준:
--   1) 업로드된 실제 DB 구조: iyjm.sql
--   2) 업로드된 현재 프로젝트: iyjm_v3_google_oauth_debug.zip
--
-- 목적:
--   - Google 로그인 실패 원인인 users.name 누락 수정
--   - 현재 Facebook/Instagram 연동 코드가 요구하는 connected_accounts 컬럼 보완
--   - 자동댓글 코드가 요구하는 테이블 생성
--
-- 주의:
--   - items/posts 구조는 현재 DB와 소스의 설계가 충돌하므로 이 파일에서 변경하지 않는다.
--   - 실행 전 반드시 DB 백업을 권장한다.
--   - 현재 선택된 DB가 iyjm인지 확인한 뒤 실행한다.

USE `iyjm`;

SET @iyjm_db := DATABASE();

-- =========================================================
-- 1. users.name 추가
-- Google 로그인, 일반 회원가입/로그인, 공개 사용자 페이지에서 사용
-- =========================================================

SET @sql := IF(
  (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @iyjm_db
      AND TABLE_NAME = 'users'
      AND COLUMN_NAME = 'name'
  ) = 0,
  'ALTER TABLE `users`
     ADD COLUMN `name` VARCHAR(255) NULL
     COMMENT ''사용자 표시 이름''
     AFTER `email`',
  'SELECT ''[SKIP] users.name already exists'' AS result'
);

PREPARE iyjm_stmt FROM @sql;
EXECUTE iyjm_stmt;
DEALLOCATE PREPARE iyjm_stmt;


-- =========================================================
-- 2. connected_accounts 컬럼 보완
-- 현재 소스가 name, expires_at, parent_id를 사용함
-- =========================================================

SET @sql := IF(
  (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @iyjm_db
      AND TABLE_NAME = 'connected_accounts'
      AND COLUMN_NAME = 'name'
  ) = 0,
  'ALTER TABLE `connected_accounts`
     ADD COLUMN `name` VARCHAR(255) NULL
     COMMENT ''SNS 계정 또는 페이지 표시 이름''
     AFTER `provider_user_id`',
  'SELECT ''[SKIP] connected_accounts.name already exists'' AS result'
);

PREPARE iyjm_stmt FROM @sql;
EXECUTE iyjm_stmt;
DEALLOCATE PREPARE iyjm_stmt;


SET @sql := IF(
  (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @iyjm_db
      AND TABLE_NAME = 'connected_accounts'
      AND COLUMN_NAME = 'expires_at'
  ) = 0,
  'ALTER TABLE `connected_accounts`
     ADD COLUMN `expires_at` DATETIME NULL
     COMMENT ''액세스 토큰 만료 예정 시각''
     AFTER `access_token`',
  'SELECT ''[SKIP] connected_accounts.expires_at already exists'' AS result'
);

PREPARE iyjm_stmt FROM @sql;
EXECUTE iyjm_stmt;
DEALLOCATE PREPARE iyjm_stmt;


SET @sql := IF(
  (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @iyjm_db
      AND TABLE_NAME = 'connected_accounts'
      AND COLUMN_NAME = 'parent_id'
  ) = 0,
  'ALTER TABLE `connected_accounts`
     ADD COLUMN `parent_id` VARCHAR(255) NULL
     COMMENT ''상위 Facebook 사용자 또는 페이지 ID''
     AFTER `expires_at`',
  'SELECT ''[SKIP] connected_accounts.parent_id already exists'' AS result'
);

PREPARE iyjm_stmt FROM @sql;
EXECUTE iyjm_stmt;
DEALLOCATE PREPARE iyjm_stmt;


-- facebook_page 값을 현재 코드가 사용하므로 ENUM 확장
ALTER TABLE `connected_accounts`
  MODIFY COLUMN `provider`
  ENUM('facebook','facebook_page','instagram','threads')
  DEFAULT NULL
  COMMENT 'SNS 플랫폼 종류';


-- 현재 코드가 connected_accounts.id를 INSERT하지 않으므로 UUID 자동 생성
DROP TRIGGER IF EXISTS `trg_connected_accounts_set_uuid`;

DELIMITER $$

CREATE TRIGGER `trg_connected_accounts_set_uuid`
BEFORE INSERT ON `connected_accounts`
FOR EACH ROW
BEGIN
  IF NEW.`id` IS NULL OR NEW.`id` = '' THEN
    SET NEW.`id` = UUID();
  END IF;
END$$

DELIMITER ;


-- ON DUPLICATE KEY UPDATE가 실제로 동작하도록 중복 여부 확인 후 UNIQUE KEY 추가
SET @iyjm_connected_duplicate_count := (
  SELECT COUNT(*)
  FROM (
    SELECT
      `user_id`,
      `provider`,
      `provider_user_id`
    FROM `connected_accounts`
    WHERE `user_id` IS NOT NULL
      AND `provider` IS NOT NULL
      AND `provider_user_id` IS NOT NULL
    GROUP BY
      `user_id`,
      `provider`,
      `provider_user_id`
    HAVING COUNT(*) > 1
  ) AS duplicated_accounts
);

SET @iyjm_connected_unique_exists := (
  SELECT COUNT(*)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @iyjm_db
    AND TABLE_NAME = 'connected_accounts'
    AND INDEX_NAME = 'uniq_user_provider_account'
);

SET @sql := IF(
  @iyjm_connected_unique_exists > 0,
  'SELECT ''[SKIP] connected_accounts unique key already exists'' AS result',
  IF(
    @iyjm_connected_duplicate_count = 0,
    'ALTER TABLE `connected_accounts`
       ADD UNIQUE KEY `uniq_user_provider_account`
       (`user_id`, `provider`, `provider_user_id`)',
    'SELECT ''[FAIL] connected_accounts has duplicate user/provider/provider_user_id rows. Unique key was not added.'' AS result'
  )
);

PREPARE iyjm_stmt FROM @sql;
EXECUTE iyjm_stmt;
DEALLOCATE PREPARE iyjm_stmt;


-- =========================================================
-- 3. 자동댓글 테이블 생성
-- 현재 소스가 auto_reply_rules, auto_reply_logs를 직접 사용함
-- =========================================================

CREATE TABLE IF NOT EXISTS `auto_reply_rules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '자동댓글 규칙 ID',
  `user_id` VARCHAR(36) NOT NULL COMMENT 'users.id',
  `page_id` VARCHAR(255) NOT NULL COMMENT 'Facebook/Instagram 페이지 또는 계정 ID',
  `reply_text` TEXT NOT NULL COMMENT '자동 응답 문구',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=사용, 0=중지',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_auto_reply_rule` (`user_id`, `page_id`),
  KEY `idx_auto_reply_rule_user` (`user_id`)
) ENGINE=MyISAM
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_general_ci
  COMMENT='사용자별 SNS 자동댓글 규칙';


CREATE TABLE IF NOT EXISTS `auto_reply_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '자동댓글 처리 로그 ID',
  `user_id` VARCHAR(36) DEFAULT NULL COMMENT 'users.id',
  `page_id` VARCHAR(255) DEFAULT NULL COMMENT 'SNS 페이지 또는 계정 ID',
  `comment_id` VARCHAR(255) DEFAULT NULL COMMENT '원본 댓글 ID',
  `comment_text` TEXT DEFAULT NULL COMMENT '원본 댓글 내용',
  `reply_text` TEXT DEFAULT NULL COMMENT '전송한 자동 응답',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '처리일시',
  PRIMARY KEY (`id`),
  KEY `idx_auto_reply_log_user` (`user_id`),
  KEY `idx_auto_reply_log_page` (`page_id`),
  KEY `idx_auto_reply_log_comment` (`comment_id`)
) ENGINE=MyISAM
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_general_ci
  COMMENT='SNS 자동댓글 처리 이력';


-- =========================================================
-- 4. 적용 결과 확인
-- =========================================================

SELECT
  '[OK] users columns' AS section,
  GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION SEPARATOR ', ') AS columns_found
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @iyjm_db
  AND TABLE_NAME = 'users';

SELECT
  '[OK] connected_accounts columns' AS section,
  GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION SEPARATOR ', ') AS columns_found
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @iyjm_db
  AND TABLE_NAME = 'connected_accounts';

SELECT
  TABLE_NAME,
  ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @iyjm_db
  AND TABLE_NAME IN (
    'users',
    'connected_accounts',
    'auto_reply_rules',
    'auto_reply_logs'
  )
ORDER BY TABLE_NAME;

-- =========================================================
-- 5. 별도 검토가 필요한 항목
-- =========================================================
--
-- 실제 DB의 items:
--   post_id, shop_link, thumbnail_url
--
-- 현재 소스의 items API:
--   user_id, url, image, sort_order
--
-- 두 구조는 단순 컬럼 누락이 아니라 소유 관계와 데이터 모델이 다르다.
-- 데이터 중복 및 기존 posts 관계 손상을 막기 위해 이 파일에서는 변경하지 않는다.
-- items 기능 수정 시 DB 구조를 유지하며 코드를 고칠지,
-- 코드 구조에 맞춰 DB를 마이그레이션할지 별도 결정해야 한다.
