new-schema.sql

/* 1. 유저 및 인증 관련 */
create table users (
                       user_id bigint generated by default as identity,
                       uuid uuid unique,
                       email varchar(255),
                       nickname varchar(255) not null,
                       password_hash varchar(255),
                       profile_image_url varchar(2048), -- URL 길이 확장
                       role varchar(50) not null check (role in ('USER','ADMIN')),
                       status varchar(50) check (status in ('ACTIVE','INACTIVE','BANNED')),
                       notification_enabled boolean,
                       unlockable_garden_count integer,
                       created_at timestamp(6) not null,
                       updated_at timestamp(6) not null,
                       primary key (user_id)
);

create table device_tokens (
                               id bigint generated by default as identity,
                               user_id bigint not null,
                               token varchar(255) not null unique,
                               created_at timestamp(6) not null,
                               updated_at timestamp(6) not null,
                               primary key (id)
);

create table refresh_tokens (
                                token varchar(512) not null,
                                user_uuid uuid not null,
                                device_id varchar(255),
                                expires_at timestamp(6) not null,
                                primary key (token)
);

/* 2. 아바타 시스템 */
create table avatar_master (
                               id bigint generated by default as identity,
                               default_image_url varchar(2048) not null,
                               description varchar(255),
                               primary key (id)
);

create table avatar (
                        avatar_id bigint generated by default as identity,
                        master_id bigint not null,
                        user_id bigint not null,
                        nickname varchar(14) not null,
                        image_url varchar(2048),
                        primary key (avatar_id)
);

/* 3. 소셜 기능 (피드, 팔로우, 차단) */
create table follows (
                         id bigint generated by default as identity,
                         follower_id bigint not null,
                         following_id bigint not null,
                         primary key (id)
);

create table user_block (
                            id bigint generated by default as identity,
                            blocker_user_id bigint not null,
                            blocked_user_id bigint not null,
                            created_at timestamp(6) not null, -- bytea -> timestamp 수정
                            primary key (id)
);

create table feeds (
                       id bigint generated by default as identity,
                       user_id bigint not null,
                       content varchar(255) not null,
                       image_url varchar(2048) not null,
                       created_at timestamp(6) not null,
                       primary key (id)
);

create table diaries (
                         diary_id bigint generated by default as identity,
                         user_id bigint not null,
                         title varchar(255) not null,
                         content TEXT not null,
                         keyword varchar(255),
                         is_public boolean,
                         like_count integer,
                         created_at timestamp(6),
                         updated_at timestamp(6),
                         primary key (diary_id)
);

create table diary_image (
                             id bigint generated by default as identity,
                             diary_id bigint unique,
                             user_id bigint not null,
                             image_url varchar(2048) not null,
                             created_at timestamp(6),
                             primary key (id)
);

create table avatar_post (
                             avatar_post_id bigint generated by default as identity,
                             avatar_id bigint not null,
                             user_id bigint not null,
                             caption varchar(2000),
                             image_url varchar(2048),
                             like_count bigint,
                             created_at timestamp(6) not null,
                             updated_at timestamp(6) not null,
                             primary key (avatar_post_id)
);

/* 4. 좋아요 테이블 분리 (구조 개선됨) */
create table feed_likes (
                            id bigint generated by default as identity,
                            user_id bigint not null,
                            feed_id bigint not null,
                            created_at timestamp(6) not null,
                            primary key (id),
                            unique (user_id, feed_id)
);

create table diary_likes (
                             id bigint generated by default as identity,
                             user_id bigint not null,
                             diary_id bigint not null,
                             created_at timestamp(6) not null,
                             primary key (id),
                             unique (user_id, diary_id)
);

create table avatar_post_likes (
                                   id bigint generated by default as identity,
                                   user_id bigint not null,
                                   avatar_post_id bigint not null,
                                   created_at timestamp(6) not null,
                                   primary key (id),
                                   unique (user_id, avatar_post_id)
);

/* 5. 댓글 및 북마크 */
create table comments (
                          id bigint generated by default as identity,
                          writer_id bigint not null,
                          avatar_post_id bigint,
                          diary_id bigint,
                          content varchar(255) not null,
                          created_at timestamp(6) not null,
                          updated_at timestamp(6) not null,
                          primary key (id)
);

create table bookmark (
                          bookmark_id bigint generated by default as identity,
                          user_id bigint not null,
                          avatar_post_id bigint,
                          target_type varchar(50), -- ENUM 등으로 관리 권장
                          created_at timestamp(6) not null,
                          primary key (bookmark_id)
);

/* 6. 정원(Garden) 및 식물 */
create table garden_background (
                                   id bigint generated by default as identity,
                                   name varchar(255) not null,
                                   image_url varchar(2048) not null,
                                   primary key (id)
);

create table garden (
                        id bigint generated by default as identity,
                        user_id bigint not null,
                        avatar_id bigint unique,
                        garden_background_id bigint,
                        slot_number integer not null,
                        sunlight_count integer not null,
                        water_count integer not null,
                        is_locked boolean not null,
                        last_sunlight_received_at timestamp(6),
                        last_watered_by_owner_at timestamp(6),
                        last_watered_by_friend_at timestamp(6),
                        created_at timestamp(6) not null,
                        updated_at timestamp(6) not null,
                        primary key (id)
);

create table friend_watering_log (
                                     id bigint generated by default as identity,
                                     water_giver_id bigint not null,
                                     watered_garden_id bigint not null,
                                     watered_at timestamp(6) not null,
                                     primary key (id)
);

create table plant_masters (
                               plant_master_id bigint generated by default as identity,
                               plant_name varchar(255),
                               plant_type varchar(255),
                               description varchar(255),
                               image_url varchar(2048),
                               growth_stages integer,
                               unlock_level integer,
                               created_at timestamp(6),
                               primary key (plant_master_id)
);

/* 7. 미션 시스템 (대폭 개선: 단일 테이블 전략) */
create table daily_mission_master (
                                      mission_master_id bigint generated by default as identity,
                                      title varchar(255),
                                      content varchar(255),
                                      description varchar(255),
                                      mission_type varchar(50) check (mission_type in ('PHOTO','QUIZ','DIARY')),
                                      reward_points bigint,
                                      is_active boolean,
                                      created_at timestamp(6),
                                      primary key (mission_master_id)
);

/* 통합된 유저 미션 테이블 (Image, Quiz 미션 테이블 제거됨) */
create table user_daily_mission (
                                    user_mission_id bigint generated by default as identity,
                                    user_id bigint not null,
                                    mission_master_id bigint not null,
                                    mission_type varchar(31) not null,

    -- 상태 정보
                                    is_completed boolean not null,
                                    score bigint,
                                    completed_at timestamp(6),
                                    created_at timestamp(6) not null,

    -- 사진 미션용 컬럼
                                    submission_image_url varchar(2048),

    -- 퀴즈 미션용 컬럼
                                    selected_answer_number integer,
                                    is_quiz_correct boolean,

                                    primary key (user_mission_id)
);

/* 퀴즈 마스터 데이터 */
create table quiz (
                      id bigint generated by default as identity,
                      mission_master_id bigint unique,
                      quiz_question varchar(255),
                      quiz_type smallint,
                      answer_number bigint,
                      primary key (id)
);

create table quiz_options (
                              quiz_option_id bigint generated by default as identity,
                              quiz_id bigint,
                              option_text varchar(255),
                              option_order integer,
                              primary key (quiz_option_id)
);

/* 8. 기타 기능 (배송, 알림, 신고 등) */
create table delivery_plant (
                                id bigint generated by default as identity,
                                name varchar(255),
                                image_url varchar(2048),
                                primary key (id)
);

create table delivery (
                          id bigint generated by default as identity,
                          user_id bigint not null,
                          delivery_plant_id bigint not null,
                          recipient_name varchar(255) not null,
                          recipient_phone varchar(255) not null,
                          postal_code varchar(255) not null,
                          address varchar(255) not null,
                          address_detail varchar(255),
                          message varchar(255),
                          created_at timestamp(6),
                          primary key (id)
);

create table notifications (
                               id bigint generated by default as identity,
                               user_id bigint not null,
                               content varchar(255) not null,
                               url varchar(2048) not null,
                               notification_type varchar(50) not null,
                               is_read boolean not null,
                               created_at timestamp(6) not null,
                               primary key (id)
);

create table report_reason (
                               id bigint generated by default as identity,
                               reason_type varchar(50),
                               reason_text varchar(255),
                               is_active boolean,
                               created_at timestamp(6),
                               primary key (id)
);

create table reports (
                         report_id bigint generated by default as identity,
                         user_id bigint, -- 신고자
                         reported_user_id bigint, -- 신고 당한 사람
                         target_type smallint,
                         target_id bigint,
                         report_reason_id bigint,
                         additional_comment varchar(255),
                         report_status varchar(50),
                         reviewer_id bigint,
                         reviewed_at timestamp(6),
                         created_at timestamp(6),
                         primary key (report_id)
);

create table guestbooks (
                            id bigint generated by default as identity,
                            owner_id bigint not null,
                            writer_id bigint not null,
                            content varchar(500) not null,
                            created_at timestamp(6) not null,
                            primary key (id)
);

create table wish_tree (
                           id bigint generated by default as identity,
                           user_id bigint not null unique,
                           points bigint not null,
                           stage varchar(50) not null,
                           primary key (id)
);

create table daily_keywords (
                                keyword_id bigint generated by default as identity,
                                keyword varchar(255),
                                keyword_date timestamp(6),
                                created_at timestamp(6),
                                primary key (keyword_id)
);

create table daily_question_answer (
                                       id bigint generated by default as identity,
                                       user_id bigint not null,
                                       question varchar(500) not null,
                                       answer varchar(50) not null,
                                       answered_date date not null,
                                       primary key (id),
                                       unique (user_id, answered_date)
);

/* =========================================
   Foreign Keys (관계 설정)
   ========================================= */

/* Users & Auth */
alter table device_tokens add constraint FK_device_user foreign key (user_id) references users;
alter table follows add constraint FK_follow_follower foreign key (follower_id) references users;
alter table follows add constraint FK_follow_following foreign key (following_id) references users;
alter table user_block add constraint FK_block_blocker foreign key (blocker_user_id) references users;
alter table user_block add constraint FK_block_blocked foreign key (blocked_user_id) references users;

/* Avatar */
alter table avatar add constraint FK_avatar_master foreign key (master_id) references avatar_master;
alter table avatar add constraint FK_avatar_user foreign key (user_id) references users;
alter table avatar_post add constraint FK_post_avatar foreign key (avatar_id) references avatar;
alter table avatar_post add constraint FK_post_user foreign key (user_id) references users;

/* Likes (분리된 FK) */
alter table feed_likes add constraint FK_feed_likes_user foreign key (user_id) references users;
alter table feed_likes add constraint FK_feed_likes_feed foreign key (feed_id) references feeds;
alter table diary_likes add constraint FK_diary_likes_user foreign key (user_id) references users;
alter table diary_likes add constraint FK_diary_likes_diary foreign key (diary_id) references diaries;
alter table avatar_post_likes add constraint FK_avt_likes_user foreign key (user_id) references users;
alter table avatar_post_likes add constraint FK_avt_likes_post foreign key (avatar_post_id) references avatar_post;

/* Comments & Bookmarks */
alter table comments add constraint FK_comment_user foreign key (writer_id) references users;
alter table comments add constraint FK_comment_post foreign key (avatar_post_id) references avatar_post;
alter table comments add constraint FK_comment_diary foreign key (diary_id) references diaries;
alter table bookmark add constraint FK_bookmark_user foreign key (user_id) references users;
alter table bookmark add constraint FK_bookmark_post foreign key (avatar_post_id) references avatar_post;

/* Garden */
alter table garden add constraint FK_garden_user foreign key (user_id) references users;
alter table garden add constraint FK_garden_avatar foreign key (avatar_id) references avatar;
alter table garden add constraint FK_garden_bg foreign key (garden_background_id) references garden_background;
alter table friend_watering_log add constraint FK_water_giver foreign key (water_giver_id) references users;
alter table friend_watering_log add constraint FK_water_garden foreign key (watered_garden_id) references garden;

/* Missions (개선된 관계) */
alter table quiz add constraint FK_quiz_master foreign key (mission_master_id) references daily_mission_master;
alter table quiz_options add constraint FK_quiz_opt_quiz foreign key (quiz_id) references quiz;
alter table user_daily_mission add constraint FK_user_mission_user foreign key (user_id) references users;
alter table user_daily_mission add constraint FK_user_mission_master foreign key (mission_master_id) references daily_mission_master;

/* Others */
alter table delivery add constraint FK_delivery_user foreign key (user_id) references users;
alter table delivery add constraint FK_delivery_plant foreign key (delivery_plant_id) references delivery_plant;
alter table notifications add constraint FK_noti_user foreign key (user_id) references users on delete cascade;
alter table reports add constraint FK_report_reason foreign key (report_reason_id) references report_reason;
alter table reports add constraint FK_report_user foreign key (user_id) references users;
alter table guestbooks add constraint FK_guest_owner foreign key (owner_id) references users;
alter table guestbooks add constraint FK_guest_writer foreign key (writer_id) references users;
alter table wish_tree add constraint FK_wish_user foreign key (user_id) references users;
alter table daily_question_answer add constraint FK_daily_q_user foreign key (user_id) references users;
alter table diary_image add constraint FK_diary_img_diary foreign key (diary_id) references diaries;
alter table diary_image add constraint FK_diary_img_user foreign key (user_id) references users;
alter table feeds add constraint FK_feed_user foreign key (user_id) references users;