-- 촬영지 테이블
CREATE TABLE filming_location (
	film_id			NUMBER	PRIMARY KEY,
	media_code		NUMBER	NOT NULL,
	place_code		NUMBER	NOT NULL,
	title			VARCHAR2(100) NOT NULL,
	place			VARCHAR2(100) NOT NULL,
	place_describe	VARCHAR2(2000) NOT NULL,	
	business_hour	VARCHAR2(300),				
	break_time		VARCHAR2(80),				
	closed_day		VARCHAR2(100),	
	address  		VARCHAR2(150) NOT NULL,
	latitude		NUMBER(12,9) NOT NULL,
	longitude		NUMBER(12,9) NOT NULL,
	phone_number	VARCHAR2(20)
);

CREATE SEQUENCE film_id_seq START WITH 1 INCREMENT BY 1;

-- 미디어 정보 테이블 
CREATE TABLE media_info (
	media_code NUMBER PRIMARY KEY,
	media_type VARCHAR2(10) NOT NULL
);

-- 장소 정보 테이블 
CREATE TABLE place_info (
	place_code NUMBER PRIMARY KEY,
	place_type VARCHAR2(10) NOT NULL
);

ALTER TABLE filming_location 
ADD CONSTRAINT fk_film_media_code FOREIGN KEY ( media_code ) REFERENCES media_info( media_code ); 

ALTER TABLE filming_location 
ADD CONSTRAINT fk_film_place_code FOREIGN KEY ( place_code ) REFERENCES place_info( place_code ); 

----------------------------------------------------------------------------------------------------

-- 사용자 테이블
CREATE TABLE wedug_user (
    user_id 	VARCHAR2(20) PRIMARY KEY,
    nickname 	VARCHAR2(20) NOT NULL,
    password 	VARCHAR2(60) NOT NULL,
    gender 		CHAR(1) NOT NULL CHECK (gender IN ('M', 'F', 'O')),
    birth_year 	NUMBER(4) NOT NULL,
		nation_code 		NUMBER NOT NULL	
);

-- 국적코드 테이블
CREATE TABLE nation_info (
	nation_code NUMBER PRIMARY KEY,
	en_name	 	VARCHAR2(60),
	ko_name		VARCHAR2(60)
);

ALTER TABLE wedug_user 
ADD CONSTRAINT fk_user_nation_code FOREIGN KEY ( nation_code ) REFERENCES nation_info( nation_code ); 

----------------------------------------------------------------------------------------------------

-- 즐겨찾기 테이블
CREATE TABLE favorite (
	favorite_id NUMBER PRIMARY KEY,
	user_id	 	VARCHAR2(20),
	film_id		NUMBER
);

CREATE SEQUENCE favorite_id_seq START WITH 1 INCREMENT BY 1;

ALTER TABLE favorite 
ADD CONSTRAINT fk_favorite_user_id FOREIGN KEY ( user_id ) REFERENCES wedug_user( user_id ); 

ALTER TABLE favorite 
ADD CONSTRAINT fk_favorite_film_id FOREIGN KEY ( film_id ) REFERENCES filming_location( film_id );