❗️ 변경사항

• token 테이블에 member_id 추가 및 FK 설정
• member 테이블에 token_id 제거
• post 테이블에 user_id ⇒ member_id
• created_date 컬럼 default sysdate 설정 추가
• modified_date 컬럼 default systdate 설정 추가
• post 테이블에 read_count default 0 설정

v0.1

drop table reply;
drop table heart;
drop table image;
drop table post;
drop table channel;
drop table token;
drop table member;
drop table reply_heart;

--sequence
CREATE SEQUENCE SEQ_REPLY_ID
INCREMENT BY 1;

CREATE SEQUENCE SEQ_HEART_ID
INCREMENT BY 1;

CREATE SEQUENCE SEQ_IMAGE_ID
INCREMENT BY 1;

CREATE SEQUENCE SEQ_POST_ID
INCREMENT BY 1;

CREATE SEQUENCE SEQ_CHANNEL_ID
INCREMENT BY 1;

CREATE SEQUENCE SEQ_MEMBER_ID
INCREMENT BY 1;

CREATE SEQUENCE SEQ_TOKEN_ID
INCREMENT BY 1;

CREATE SEQUENCE SEQ_REPLY_HEART_ID
INCREMENT BY 1;

create table channel (
    id number(10,0) not null,
    name varchar(255),
    location varchar(255),
    description varchar(500),
    business_hours varchar(255),
    phone_number varchar(255),
    created_date timestamp default sysdate,
    modified_date timestamp default sysdate,
    removed char(1) default 0 not null,
    primary key (id)
);

alter table channel add image_url varchar(500);

create table reply (
    id number(10,0) not null,
    content varchar(500),
    post_id number(10,0),
    member_id number(10,0),
    parent_id number(10,0),
    depth number(1,0) not null,
    created_date timestamp default sysdate,
    modified_date timestamp default sysdate,
    removed char(1) default 0 not null,
    primary key (id)
);

create table heart (
    id number(10,0) not null,
    post_id number(10,0),
    member_id number(10,0),
    created_date timestamp default sysdate,
    modified_date timestamp default sysdate,
    removed char(1) default 0 not null,
    primary key (id)
);

create table image (
    id number(10,0) not null,
    orders number(1,0) default 1,
    post_id number(10,0),
    image_url varchar(500),
    created_date timestamp default sysdate,
    modified_date timestamp default sysdate,
    removed char(1) default 0 not null,
    primary key (id)
);

create table member (
    id number(10,0) not null,
    email varchar(255) not null,
    nickname varchar(255),
    introduction varchar(500),
    image_url varchar(500),
    created_date timestamp default sysdate,
    modified_date timestamp default sysdate,
    removed char(1) default 0 not null,
    primary key (id)
);

create table post (
    id number(10,0) not null,
    title varchar(255),
    content varchar(2000),
    member_id number(10,0),
    channel_id number(10,0),
    read_count number(10,0) default 0 not null,
    created_date timestamp default sysdate,
    modified_date timestamp default sysdate,
    removed char(1) default 0 not null,
    primary key (id)
);

create table token (
    id number(10,0) not null,
    member_id number(10, 0),
    access_token varchar(255),
    refresh_token varchar(255),
    created_date timestamp default sysdate,
    modified_date timestamp default sysdate,
    removed char(1) default 0 not null,
    primary key (id)
);

create table reply_heart (
    id number(10,0) not null,
    reply_id number(10,0),
    member_id number(10,0),
    created_date timestamp default sysdate,
    modified_date timestamp default sysdate,
    removed char(1) default 0 not null,
    primary key (id)
);

// FK_현재테이블_to_자식테이블_컬럼이름

alter table reply 
   add constraint FK_reply_parent_id
   foreign key (parent_id) 
   references reply (id);

alter table reply 
   add constraint FK_reply_post_id
   foreign key (post_id) 
   references post (id);

alter table heart 
   add constraint FK_heart_member_id 
   foreign key (member_id) 
   references member (id);

alter table heart 
   add constraint FK_heart_post_id 
   foreign key (post_id) 
   references post (id);

alter table image 
   add constraint FK_image_post_id 
   foreign key (post_id) 
   references post (id);

alter table post
   add constraint FK_post_channel_id 
   foreign key (channel_id) 
   references channel (id);

alter table token
	add constraint FK_token_member_id
	foreign key (member_id)
	references member (id);

alter table reply_heart 
   add constraint FK_reply_heart_member_id 
   foreign key (member_id) 
   references member (id);

alter table reply_heart 
   add constraint FK_reply_heart_post_id 
   foreign key (reply_id) 
   references reply (id);

--pl sql
CREATE OR REPLACE PROCEDURE GetPreviousAndNextPostId(
    p_current_post_id IN NUMBER,
    p_previous_post_id OUT NUMBER,
    p_next_post_id OUT NUMBER
) AS
BEGIN
    -- 이전 게시글 ID 조회
    SELECT MAX(id) INTO p_previous_post_id
    FROM post
    WHERE id < p_current_post_id;

    -- 이후 게시글 ID 조회
    SELECT MIN(id) INTO p_next_post_id
    FROM post
    WHERE id > p_current_post_id;

END GetPreviousAndNextPostId;
/