use abbboo;
CREATE TABLE `user` (
  `user_id` int PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '유저 id',
  `family_id` int COMMENT '유저가 속한 가족 id',
  `kakao_id` varchar(20) UNIQUE COMMENT '유저 카카오 아이디',
  `nickname` varchar(10) COMMENT '어플 내에서 유저가 불러질 이름',
  `birthday` datetime COMMENT '유저의 생년월일',
  `mood` varchar(20) DEFAULT "NORMAL" COMMENT '유저의 최신 감정상태 (예 : 열정활활, 우울)',
  `decoration` varchar(20) DEFAULT "NORMAL" COMMENT '유저 펭귄 장식',
  `refresh_token` varchar(255) DEFAULT null COMMENT '리프레시 토큰', -- UNIQUE
  `fcm_token` varchar(255) DEFAULT null COMMENT 'fcm 기기 ID 토큰', -- UNIQUE
  `is_deleted` bool DEFAULT false COMMENT '회원 탈퇴 여부',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `family` (
  `family_id` int PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '가족 id',
  `family_code` varchar(10) UNIQUE NOT NULL COMMENT '가족 초대용 구분 코드',
  `level` int NOT NULL DEFAULT 1 COMMENT '현재 민들레 레벨',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `exp_history` (
  `exp_id` bigint PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '경험치 내역 id',
  `user_id` int NOT NULL COMMENT '경험치를 올린 사용자 id',
  `family_id` int NOT NULL COMMENT '유저가 속한 가족 id',
  `level` int NOT NULL COMMENT '경험치 증가 시점의 가족레벨(경험치는 해당 레벨에만 반영됨)',
  `point` int NOT NULL COMMENT '증가한 경험치량',
  `content` varchar(50) NOT NULL COMMENT '예시 : 메시지 작성으로 +10',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `mood` (
  `mood_id` int PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '감정 상태 id',
  `expression` varchar(20) COMMENT '등록된 감정 상태 내용(e.g. PASSION, SAD)',
  `is_deleted` bool DEFAULT false COMMENT '삭제된 상태 여부',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `mood_history` (
  `mood_history_id` bigint PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '유저가 남긴 상태 id',
  `user_id` int NOT NULL COMMENT '상태를 등록한 유저 id',
  `mood_id` int NOT NULL COMMENT '감정 상태의 id',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `message` (
  `message_id` bigint PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '메시지 id',
  `sender_id` int NOT NULL COMMENT '발신자 유저 id',
  `receiver_id` int NOT NULL COMMENT '수신자 유저 id',
  `is_read` bool DEFAULT false COMMENT '메시지 읽었는지 여부',
  `content` varchar(100) NOT NULL COMMENT '메시지 내용',
  `tts_url` varchar(2000) NOT NULL COMMENT '메시지를 읽은 음성파일의 s3 url',
  `is_auto` bool DEFAULT false COMMENT '자동인지 여부',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `story` (
  `story_id` bigint PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '스토리 id',
  `user_id` int NOT NULL COMMENT '유저 id',
  `family_id` int NOT NULL COMMENT '가족 id',
  `front_url` varchar(2000) NOT NULL COMMENT '전면카메라로 찍은 사진이나 동영상 s3 url',
  `rear_url` varchar(2000) NOT NULL COMMENT '후면카메라로 찍은 사진이나 동영상 혹은 앨범 사진의 s3 url',
  `is_saved` bool DEFAULT false COMMENT '온실 캘린더에 저장되었는지 여부',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `reaction` (
  `reaction_id` int PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '반응 id',
  `expression` varchar(10) COMMENT '반응 종류 (예 : CLAP)',
  `is_deleted` bool DEFAULT false COMMENT '삭제된 반응 여부',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `reaction_history` (
  `reaction_history_id` bigint PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '유저가 남긴 반응 기록 id',
  `story_id` bigint NOT NULL COMMENT '스토리 id',
  `user_id` int NOT NULL COMMENT '반응한 유저 id',
  `reaction_id` int NOT NULL COMMENT '반응 종류 id',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `decoration` (
  `decoration_id` int PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '장식 종류 id',
  `item` varchar(20) COMMENT '장식 종류 (예 : ALIEN, BEE)',
  `is_deleted` bool DEFAULT false COMMENT '삭제된 장식인지 여부',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `decoration_history` (
  `decoration_history_id` bigint PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '유저가 사용한 장식 기록 id',
  `user_id` int NOT NULL COMMENT '반응한 유저 id',
  `decoration_id` int NOT NULL COMMENT '반응 종류 id',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `notification` (
  `notification_id` bigint PRIMARY KEY AUTO_INCREMENT COMMENT '알림 id',
  `sender_id` int NOT NULL COMMENT '발신자 유저 id'
  `receiver_id` int NOT NULL COMMENT '수신자 유저 id',
  `title` varchar(20) NOT NULL COMMENT '알림 제목',
  `content` varchar(300) NOT NULL COMMENT '알림 내용',
  `is_read` bool DEFAULT false COMMENT '알림을 읽었는지 여부',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

CREATE TABLE `message_template` (
  `template_id` int PRIMARY KEY AUTO_INCREMENT COMMENT '템플릿 id',
  `content` varchar(300) NOT NULL COMMENT '템플릿 내용',
  `is_deleted` bool DEFAULT false COMMENT '템플릿 삭제 여부',
  `created_at` datetime NOT NULL COMMENT '생성 시각',
  `created_ip` varchar(15) NOT NULL COMMENT '생성 ip',
  `updated_at` datetime NOT NULL COMMENT '수정 시각',
  `updated_ip` varchar(15) NOT NULL COMMENT '수정 ip'
);

ALTER TABLE `user` ADD FOREIGN KEY (`family_id`) REFERENCES `family` (`family_id`);

ALTER TABLE `exp_history` ADD FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`);

ALTER TABLE `exp_history` ADD FOREIGN KEY (`family_id`) REFERENCES `family` (`family_id`);

ALTER TABLE `mood_history` ADD FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`);

ALTER TABLE `mood_history` ADD FOREIGN KEY (`mood_id`) REFERENCES `mood` (`mood_id`);

ALTER TABLE `message` ADD FOREIGN KEY (`sender_id`) REFERENCES `user` (`user_id`);

ALTER TABLE `message` ADD FOREIGN KEY (`receiver_id`) REFERENCES `user` (`user_id`);

ALTER TABLE `story` ADD FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`);

ALTER TABLE `story` ADD FOREIGN KEY (`family_id`) REFERENCES `family` (`family_id`);

ALTER TABLE `reaction_history` ADD FOREIGN KEY (`story_id`) REFERENCES `story` (`story_id`);

ALTER TABLE `reaction_history` ADD FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`);

ALTER TABLE `reaction_history` ADD FOREIGN KEY (`reaction_id`) REFERENCES `reaction` (`reaction_id`);

ALTER TABLE `decoration_history` ADD FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`);

ALTER TABLE `decoration_history` ADD FOREIGN KEY (`decoration_id`) REFERENCES `decoration` (`decoration_id`);

ALTER TABLE `notification` ADD FOREIGN KEY (`sender_id`) REFERENCES `user` (`user_id`);
ALTER TABLE `notification` ADD FOREIGN KEY (`receiver_id`) REFERENCES `user` (`user_id`);

-- 메시지에 이모티콘 도입을 위한 수정 사항
ALTER DATABASE abbboo CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE abbboo.message CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SELECT default_character_set_name, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA 
WHERE schema_name = "abbboo";
SHOW FULL COLUMNS FROM message;