/*
    # EUE Server Database Schema

    - DataBase : PostgreSQL
    - 실제 서버 동작시 Sequelize가 models 디렉토리의 모델들에 따라 테이블을 생성합니다. 따라서 해당 SQL파일은 참고용으로 사용합니다.

    1. LOC_DO
        - 행정구역 도/특별시/특별자치시 이름과 코드 저장
        - LOCSIGUNGU와 LOC_EMD에 참조됨

    2. LOC_SGG
        - 행정구역 시/군/구 이름과 코드 저장
        - LOC_DO를 참조
        - LOC_EMD에 참조됨

    3. LOC_EMD
        - 행정구역 읍/면/동 이름과 코드
        - LOC_DO와 LOC_SGG를 참조
        - USERS와 WEATHER_OUT에 참조됨

    4. USERS
        - 사용자 ID, PassWord, 거주지역코드
        - LOC_EMD를 참조
    
    5. WEATHER_IN
        - 사용자 ID, 수집 날짜 및 시간, 온도, 습도, 광도
        - USERS와 LOC_EMD 참조

    6. WEATHER_OUT
        - 지역 코드, 수집 날짜 및 시간, 온도, 습도, 기압, 풍속
        - LOC_EMD 참조
    
*/

CREATE TABLE DOE
(
    CODE INT NOT NULL,
    NAME_DO VARCHAR(20) NOT NULL,
    PRIMARY KEY(CODE_DO)
);

CREATE TABLE SGG
(
    CODE INT NOT NULL,
    CODE_DO INT NOT NULL,
    NAME_SGG VARCHAR(20) NOT NULL,
    PRIMARY KEY(CODE),
    FOREIGN KEY(CODE_DO) REFERENCES DO(CODE) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE EMD
(
    CODE INT NOT NULL,
    CODE_DO INT NOT NULL,
    CODE_SGG INT NOT NULL,
    NAME_EMD VARCHAR(20) NOT NULL,
    PRIMARY KEY(CODE),
    FOREIGN KEY(CODE_DO) REFERENCES DO(CODE) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY(CODE_SGG) REFERENCES SGG(CODE) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE USERS
(
    EMAIL VARCHAR(320) UNIQUE NOT NULL,
    PW VARCHAR(20) NOT NULL,
    LOC_CODE INT NOT NULL,
    PRIMARY KEY(EMAIL),
    FOREIGN KEY(LOC_CODE) REFERENCES EMD(CODE) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE WEATHER_IN
(
    HOST VARCHAR(320) NOT NULL,
    COLLECTED_AT TIMESTAMP NOT NULL,
    TEMP FLOAT DEFAULT 0,
    HUMI FLOAT DEFAULT 0,
    LIGHTS FLOAT DEFAULT 0,
    PRIMARY KEY(EMAIL,COLLECTED_AT),
    FOREIGN KEY(EMAIL) REFERENCES USERS(EMAIL) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE WEATHER_OUT
(
    LOC_CODE INT NOT NULL,
    COLLECTED_AT TIMESTAMP NOT NULL,
    TEMP FLOAT DEFAULT 0,
    HUMI FLOAT DEFAULT 0,
    PRESS FLOAT DEFAULT 0,
    WIND_SPEED FLOAT DEFAULT 0,
    PRIMARY KEY(LOC_CODE, COLLECTED_AT),
    FOREIGN KEY(LOC_CODE) REFERENCES EMD(CODE) ON UPDATE CASCADE ON DELETE RESTRICT
);