DDL

CREATE TYPE role AuS ENUM ('admin', 'general');
CREATE TYPE tag AS ENUM ('top', 'pants', 'shoes', 'etc');

CREATE TABLE users
(
    id            BIGSERIAL PRIMARY KEY NOT NULL,
    oauth_id      VARCHAR               NOT NULL UNIQUE,
    email         VARCHAR               NOT NULL UNIQUE,
    profile_image VARCHAR               NOT NULL,
    point         INTEGER               NOT NULL,
    nickname      VARCHAR               NOT NULL,
    create_at     TIMESTAMP             NOT NULL,
    users_role    role                  NOT NULL
);

ALTER TABLE users
    ALTER COLUMN point SET DEFAULT 0;

CREATE TABLE information
(
    id           BIGSERIAL PRIMARY KEY NOT NULL,
    user_id      BIGSERIAL             NOT NULL,
    username     VARCHAR(10)           NOT NULL,
    address      TEXT                  NOT NULL,
    phone_number VARCHAR(20)           NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE find
(
    id        BIGSERIAL PRIMARY KEY NOT NULL,
    user_id   BIGSERIAL             NOT NULL,
    title     VARCHAR               NOT NULL,
    create_at TIMESTAMP             NOT NULL,
    body      TEXT                  NOT NULL,
    image     VARCHAR,
    view      INTEGER               NOT NULL,
    tag       tag                   NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users (id)
);

ALTER TABLE find
    ALTER COLUMN view SET DEFAULT 0;

CREATE TABLE find_order
(
    id        BIGSERIAL PRIMARY KEY NOT NULL,
    info_id   BIGSERIAL             NOT NULL,
    find_id   BIGSERIAL             NOT NULL,
    user_id   BIGSERIAL,
    send_date TIMESTAMP             NOT NULL,
    body      TEXT,

    FOREIGN KEY (info_id) REFERENCES information (id),
    FOREIGN KEY (find_id) REFERENCES find (id),
		FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE donate
(
    id        BIGSERIAL PRIMARY KEY NOT NULL,
    user_id   BIGSERIAL             NOT NULL,
    title     VARCHAR               NOT NULL,
    create_at TIMESTAMP             NOT NULL,
    body      TEXT                  NOT NULL,
    image     VARCHAR,
    view      INTEGER               NOT NULL,
    tag       tag                   NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users (id)
);

ALTER TABLE donate
    ALTER COLUMN view SET DEFAULT 0;

CREATE TABLE donate_order
(
    id        BIGSERIAL PRIMARY KEY NOT NULL,
    info_id   BIGSERIAL             NOT NULL,
    donate_id BIGSERIAL             NOT NULL,
    user_id   BIGSERIAL,
    send_date TIMESTAMP             NOT NULL,
    body      TEXT,

    FOREIGN KEY (info_id) REFERENCES information (id),
    FOREIGN KEY (donate_id) REFERENCES donate (id)
		FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE shop
(
    id    BIGSERIAL PRIMARY KEY NOT NULL,
    title VARCHAR               NOT NULL,
    price INTEGER               NOT NULL,
    image VARCHAR               NOT NULL,
    body  VARCHAR
);

ALTER TABLE shop
    ALTER COLUMN price SET DEFAULT 0;

CREATE TABLE user_shop
(
    id      BIGSERIAL PRIMARY KEY NOT NULL,
    user_id BIGSERIAL             NOT NULL,
    shop_id BIGSERIAL             NOT NULL,
    info_id BIGSERIAL             NOT NULL,
    point   INTEGER               NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (shop_id) REFERENCES shop (id),
    FOREIGN KEY (info_id) REFERENCES information (id)
);

CREATE TABLE chat
(
    id        BIGSERIAL PRIMARY KEY NOT NULL,
    create_at TIMESTAMP             NOT NULL
);

CREATE TABLE message
(
    id         BIGSERIAL PRIMARY KEY NOT NULL,
    sender_id  BIGSERIAL             NOT NULL,
    chat_id    BIGSERIAL             NOT NULL,
    created_at TIMESTAMP             NOT NULL,
    body       TEXT                  NOT NULL,

    FOREIGN KEY (sender_id) REFERENCES users (id),
    FOREIGN KEY (chat_id) REFERENCES chat (id)
);

CREATE TABLE user_chat
(
    id      BIGSERIAL PRIMARY KEY NOT NULL,
    user_id BIGSERIAL             NOT NULL,
    chat_id BIGSERIAL             NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (chat_id) REFERENCES chat (id)
);

최종 다이어그램