DROP TABLE RESUME_STACK; 
DROP TABLE SCHOOL;
DROP TABLE USER_LIKE;
DROP TABLE USER_BOARD; 
DROP TABLE POSTING_STACK; 
DROP TABLE USER_BOOKMARK;
DROP TABLE APPLY; 
DROP TABLE POSTING;
DROP TABLE COMPANY; 
DROP TABLE RESUME; 
DROP TABLE USERS; 
COMMIT;

-- 회사 정보 ===============================================================
CREATE TABLE "COMPANY" (
   "COM_ID" number(10,0),
   "COM_NAME" varchar2(255) NOT NULL,
   "COM_EMAIL" varchar2(255) NOT NULL,
   "COM_TYPE" varchar2(255) NOT NULL,
   "COM_CEO" varchar2(255) NOT NULL,
   "COM_PASSWD" varchar2(255) NOT NULL,
   "COM_ADDRESS" varchar2(255) NOT NULL,
   "COM_PHONE" varchar2(255) NOT NULL,
   -- "COM_INDATE" date DEFAULT SYSDATE NOT NULL , 
   "COM_SALARY" varchar2(10),
   "COM_BIRTH" date,
   "COM_LOGO" varchar2(255),
   -- "ROLE" varchar2(20) DEFAULT 'USER_COMPANY',
   "EMPLOYEE" varchar2(20),
   "SALES" varchar2(20),
   "BGLOGO" varchar2(20),
   "STOCK" varchar2(255),
   "PAGE" varchar2(255),
   PRIMARY KEY ("COM_ID")
);

-- 개인 회원 ================================================================
CREATE TABLE "USERS" (
   "USER_NAME" varchar2(255) NOT NULL,
   "USER_PASSWD" varchar2(255) NOT NULL,
   "USER_EMAIL" varchar2(255) NOT NULL,
   "USER_ADDRESS" varchar2(255) NOT NULL,
   "USER_PHONE" varchar2(255) NOT NULL,
   "USER_ID" number(10,0),
   "USER_SOCIAL_NUM" varchar2(255) NOT NULL,
   -- "INDATE" date DEFAULT SYSDATE NOT NULL ,
   "USER_GENDER" varchar2(255) NOT NULL,
   --"ROLE" varchar2(20) NOT NULL,
   PRIMARY KEY ("USER_ID")
);

-- 이력서 정보 =============================================================
CREATE TABLE "RESUME" (
   "USER_ID" number(10,0)  NOT NULL,
   "RE_ID" number(10,0) ,
   "RE_PROFILE" varchar2(255),
   "RE_TITLE" varchar2(255) NOT NULL,
   "RE_INTTI" varchar2(255) NOT NULL,
   "RE_INTCON" varchar2(255) NOT NULL,
   "LICENSE" varchar2(255),
   "INDATE" date DEFAULT SYSDATE NOT NULL ,
   "SCHOOL_ID" number(10,0),
   PRIMARY KEY ("RE_ID")
);

-- 채용 공고 ==============================================================
CREATE TABLE "POSTING" (
   "COM_ID" number(10,0) ,
   "PO_ID" number(10,0) ,
   "PO_GRADE" varchar2(255) DEFAULT '고졸' NOT NULL ,
   "HOPE_DEPARTMENT" varchar2(255),
   "PO_START_DATE" date DEFAULT SYSDATE NOT NULL ,
   "PO_END_DATE" date,
   "PO_PROFILE" varchar2(255),
   "PO_TITLE" varchar2(255),
   "PO_CONTENT" varchar2(255),
   "CAREER" varchar2(255) DEFAULT '신입' NOT NULL ,
   PRIMARY KEY ("PO_ID")
);

-- 게시판 =================================================================
CREATE TABLE "USER_BOARD" (
   "USER_ID" number(10,0) NOT NULL,
   "TITLE" varchar2(255) NOT NULL,
   "CONTENT" varchar2(255) NOT NULL,
   "HIT" number(10,0) NOT NULL,
   "INDATE" date DEFAULT SYSDATE NOT NULL ,
   "BOARD_ID" number(10,0) ,
   "BOARD_BOOLEAN" number(10,0) DEFAULT '1' NOT NULL ,
    "BOARD_LIKE" number(10,0),
   PRIMARY KEY ("BOARD_ID")
);

-- 개인 북마크 ============================================================= 
CREATE TABLE "USER_BOOKMARK" (
   "USER_ID" number(10,0) NOT NULL,
   "PO_ID" number(10,0) NOT NULL,
   "UB_BOOLEAN" number(10,0) NOT NULL,
   "UB_ID" number(10,0) DEFAULT '1' NOT NULL  ,
   PRIMARY KEY ("UB_ID")
);

-- 이력서용 기술 스택 =======================================================
CREATE TABLE "RESUME_STACK" (
   "RE_ID" number(10,0) NOT NULL,
   "STACK_NAME" varchar2(255) NOT NULL,
   "RE_STACK_ID" number(10,0) ,
   PRIMARY KEY ("RE_STACK_ID")
);

-- 공고용 기술 스택 =========================================================
CREATE TABLE "POSTING_STACK" (
   "PO_ID" number(10,0) NOT NULL,
   "STACK" varchar2(255) NOT NULL,
   "PO_ST_ID" number(10,0) ,
   PRIMARY KEY ("PO_ST_ID")
);

-- 개인 지원 ==============================================================
CREATE TABLE "APPLY" (
   "PO_ID" number(10,0) NOT NULL,
   "RE_ID" number(10,0) NOT NULL,
   "RESULT" varchar2(255) DEFAULT '진행중' NOT NULL ,
   "AP_ID" number(10,0) ,
   PRIMARY KEY ("AP_ID")
);

-- 유저 좋아요 기능 =========================================================
CREATE TABLE "USER_LIKE" (
		"LIKE_ID" number(10,0) PRIMARY KEY,
		"USER_ID" number(10,0),
		"BOARD_ID" number(10,0),
		"LIKE_BOOLEAN" number(10,0),
		"BOARD_COMMENT" varchar2(255) DEFAULT '댓글' NOT NULL,
		CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES
		users(user_id),
		CONSTRAINT fk_board_id FOREIGN KEY (board_id) REFERENCES
		user_board(board_id)
);

-- 학력 사항 ==============================================================
CREATE TABLE "SCHOOL" (
    "SCHOOL_ID" NUMBER PRIMARY KEY,
    "RE_ID" NUMBER(10,0),
    "SCHOOL_NAME" varchar2(255),
    "SCHOOL_TYPE" varchar2(255),
    CONSTRAINT fk_resume_school_id FOREIGN KEY (re_id) REFERENCES resume(re_id)
);

ALTER TABLE "RESUME" ADD CONSTRAINT "RESUME_fk0" FOREIGN KEY ("USER_ID") REFERENCES "USERS"("USER_ID");
ALTER TABLE "POSTING" ADD CONSTRAINT "POSTING_fk0" FOREIGN KEY ("COM_ID") REFERENCES "COMPANY"("COM_ID");
ALTER TABLE "USER_BOARD" ADD CONSTRAINT "USER_BOARD_fk0" FOREIGN KEY ("USER_ID") REFERENCES "USERS"("USER_ID");
ALTER TABLE "USER_BOOKMARK" ADD CONSTRAINT "USER_BOOKMARK_fk0" FOREIGN KEY ("USER_ID") REFERENCES "USERS"("USER_ID");

ALTER TABLE "USER_BOOKMARK" ADD CONSTRAINT "USER_BOOKMARK_fk1" FOREIGN KEY ("PO_ID") REFERENCES "POSTING"("PO_ID");
ALTER TABLE "RESUME_STACK" ADD CONSTRAINT "RESUME_STACK_fk0" FOREIGN KEY ("RE_ID") REFERENCES "RESUME"("RE_ID");
ALTER TABLE "APPLY" ADD CONSTRAINT "APPLY_fk0" FOREIGN KEY ("PO_ID") REFERENCES "POSTING"("PO_ID");

ALTER TABLE "APPLY" ADD CONSTRAINT "APPLY_fk1" FOREIGN KEY ("RE_ID") REFERENCES "RESUME"("RE_ID");
ALTER TABLE "POSTING_STACK" ADD CONSTRAINT "POSTING_STACK_fk0" FOREIGN KEY ("PO_ID") REFERENCES "POSTING"("PO_ID");