 
CREATE TABLE `connected_accounts` (
`id` varchar(36) NOT NULL COMMENT '연결 계정 고유 ID',
`user_id` varchar(36) DEFAULT NULL COMMENT '연결된 유저 ID (users.id)',
`provider` enum('facebook','instagram','threads') DEFAULT NULL COMMENT 'SNS 플랫폼 종류',
`provider_user_id` varchar(255) DEFAULT NULL COMMENT 'SNS측 유저 식별값',
`access_token` text DEFAULT NULL COMMENT 'SNS API 액세스 토큰',
`status` enum('active','expired','banned') DEFAULT 'active' COMMENT '계정 상태 (active:정상, banned:제재됨)',
`created_at` datetime DEFAULT current_timestamp() COMMENT '연동일시',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='SNS 연동 리모컨 테이블 (제재 독립형)';


 
CREATE TABLE `items` (
`id` varchar(36) NOT NULL COMMENT '아이템 고유 ID',
`post_id` varchar(36) DEFAULT NULL COMMENT '소속 게시물 ID (posts.id)',
`title` varchar(255) DEFAULT NULL COMMENT '상품명',
`shop_link` text DEFAULT NULL COMMENT '쇼핑몰 외부 링크 (쿠팡/아마존/알리/테무)',
`description` text DEFAULT NULL COMMENT '상품 설명',
`thumbnail_url` text DEFAULT NULL COMMENT '썸네일 이미지 주소',
`is_soft_deleted` tinyint(1) DEFAULT 0 COMMENT '소프트 삭제 여부 (0:정상, 1:B/C 비공개, A만 취소선 표시)',
`created_at` datetime DEFAULT current_timestamp() COMMENT '등록일시',
PRIMARY KEY (`id`),
KEY `post_id` (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='쇼핑 아이템 테이블 (소프트 삭제 지원)';


CREATE TABLE `posts` (
`id` varchar(36) NOT NULL COMMENT '게시물 고유 ID',
`owner_id` varchar(36) DEFAULT NULL COMMENT '소유자 유저 ID (users.id)',
`title` varchar(255) DEFAULT NULL COMMENT '큐레이션 게시물 제목',
`slug` varchar(255) DEFAULT NULL COMMENT 'URL 슬러그 경로',
PRIMARY KEY (`id`),
UNIQUE KEY `slug` (`slug`) USING HASH,
KEY `owner_id` (`owner_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='큐레이션 게시물 테이블';


CREATE TABLE `users` (
`id` varchar(36) NOT NULL COMMENT '유저 고유 ID (UUID)',
`email` varchar(255) DEFAULT NULL COMMENT '로그인 메인 키 (Gmail)',
`created_at` datetime DEFAULT current_timestamp() COMMENT '가입일시',
`level` tinyint(4) NOT NULL DEFAULT 1,
`password_hash` varchar(255) DEFAULT NULL,
`picture` text DEFAULT NULL,
`last_login` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='유저 본체 테이블 (Gmail 메인)';

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;


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;

ALTER TABLE `connected_accounts`
  MODIFY COLUMN `provider`
  ENUM('facebook','facebook_page','instagram','threads')
  DEFAULT NULL
  COMMENT 'SNS 플랫폼 종류';

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 ;

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;


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 자동댓글 처리 이력';


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;
