https://www.erdcloud.com/d/PPMbk7p7RuCwxSvJ5

CREATE TABLE "messages" (
	"id"	SERIAL		NOT NULL,
	"sender_id"	SERIAL		NOT NULL,
	"chatroom_id"	SERIAL		NOT NULL,
	"content"	VARCHAR(255)		NULL,
	"created_at"	TIMESTAMP		NULL,
	"is_read"	BOOLEAN		NOT NULL
);

COMMENT ON COLUMN "messages"."id" IS '메시지ID';

COMMENT ON COLUMN "messages"."sender_id" IS '사용자ID';

COMMENT ON COLUMN "messages"."chatroom_id" IS '채팅방ID';

COMMENT ON COLUMN "messages"."content" IS '채팅 내용';

COMMENT ON COLUMN "messages"."created_at" IS '생성일자';

COMMENT ON COLUMN "messages"."is_read" IS '읽음 상태';

CREATE TABLE "chatbot_messages" (
	"id"	SERIAL		NOT NULL,
	"user_id"	SERIAL		NOT NULL,
	"user_message"	TEXT		NULL,
	"bot_response"	TEXT		NULL,
	"created_at"	TIMESTAMP		NULL
);

CREATE TABLE "reviews" (
	"id"	SERIAL		NOT NULL,
	"reviewer_id"	SERIAL		NOT NULL,
	"reviewee_id"	SERIAL		NOT NULL,
	"trade_id"	SERIAL		NOT NULL,
	"rating"	INTEGER		NOT NULL,
	"content"	VARCHAR(200)		NULL,
	"created_at"	TIMESTAMP		NULL
);

COMMENT ON COLUMN "reviews"."id" IS '리뷰ID';

COMMENT ON COLUMN "reviews"."reviewer_id" IS '판매자ID';

COMMENT ON COLUMN "reviews"."reviewee_id" IS '구매자ID';

COMMENT ON COLUMN "reviews"."trade_id" IS '상품ID';

COMMENT ON COLUMN "reviews"."rating" IS '평점';

COMMENT ON COLUMN "reviews"."content" IS '리뷰 내용';

COMMENT ON COLUMN "reviews"."created_at" IS '생성일자';

CREATE TABLE "users" (
	"id"	SERIAL		NOT NULL,
	"provider_id"	VARCHAR(100)		NULL,
	"provider"	VARCHAR(100)		NULL,
	"username"	VARCHAR(50)		NULL,
	"nickname"	VARCHAR(50)		NULL,
	"password"	VARCHAR(255)		NULL,
	"email"	VARCHAR(100)		NULL,
	"profile_img_url"	VARCHAR(100)		NULL,
	"created_at"	TIMESTAMP		NULL,
	"location"	VARCHAR(100)		NULL,
	"position"	GEOMETRY		NULL
);

CREATE TABLE "chatrooms" (
	"id"	SERIAL		NOT NULL,
	"seller_id"	SERIAL		NOT NULL,
	"buyer_id"	SERIAL		NOT NULL,
	"trade_id"	SERIAL		NOT NULL,
	"updated_at"	TIMESTAMP		NULL
);

CREATE TABLE "trades" (
	"id"	SERIAL		NOT NULL,
	"seller_id"	SERIAL		NOT NULL,
	"buyer_id"	INTEGER		NULL,
	"title"	VARCHAR(100)		NULL,
	"price"	INTEGER		NULL,
	"description"	VARCHAR(100)		NULL,
	"status"	VARCHAR(100)		NULL,
	"view_count"	INTEGER		NULL,
	"category"	VARCHAR(100)		NULL,
	"created_at"	TIMESTAMP		NULL,
	"updated_at"	TIMESTAMP		NULL
);

CREATE TABLE "trade_images" (
	"id"	SERIAL		NOT NULL,
	"trade_id"	SERIAL		NOT NULL,
	"url"	VARCHAR(1024)		NOT NULL
);

ALTER TABLE "messages" ADD CONSTRAINT "PK_MESSAGES" PRIMARY KEY (
	"id"
);

ALTER TABLE "chatbot_messages" ADD CONSTRAINT "PK_CHATBOT_MESSAGES" PRIMARY KEY (
	"id"
);

ALTER TABLE "reviews" ADD CONSTRAINT "PK_REVIEWS" PRIMARY KEY (
	"id"
);

ALTER TABLE "users" ADD CONSTRAINT "PK_USERS" PRIMARY KEY (
	"id"
);

ALTER TABLE "chatrooms" ADD CONSTRAINT "PK_CHATROOMS" PRIMARY KEY (
	"id"
);

ALTER TABLE "trades" ADD CONSTRAINT "PK_TRADES" PRIMARY KEY (
	"id"
);

ALTER TABLE "trade_images" ADD CONSTRAINT "PK_TRADE_IMAGES" PRIMARY KEY (
	"id"
);

ALTER TABLE "messages" ADD CONSTRAINT "FK_users_TO_messages_1" FOREIGN KEY (
	"sender_id"
)
REFERENCES "users" (
	"id"
);

ALTER TABLE "messages" ADD CONSTRAINT "FK_chatrooms_TO_messages_1" FOREIGN KEY (
	"chatroom_id"
)
REFERENCES "chatrooms" (
	"id"
);

ALTER TABLE "chatbot_messages" ADD CONSTRAINT "FK_users_TO_chatbot_messages_1" FOREIGN KEY (
	"user_id"
)
REFERENCES "users" (
	"id"
);

ALTER TABLE "reviews" ADD CONSTRAINT "FK_users_TO_reviews_1" FOREIGN KEY (
	"reviewer_id"
)
REFERENCES "users" (
	"id"
);

ALTER TABLE "reviews" ADD CONSTRAINT "FK_users_TO_reviews_2" FOREIGN KEY (
	"reviewee_id"
)
REFERENCES "users" (
	"id"
);

ALTER TABLE "reviews" ADD CONSTRAINT "FK_trades_TO_reviews_1" FOREIGN KEY (
	"trade_id"
)
REFERENCES "trades" (
	"id"
);

ALTER TABLE "chatrooms" ADD CONSTRAINT "FK_users_TO_chatrooms_1" FOREIGN KEY (
	"seller_id"
)
REFERENCES "users" (
	"id"
);

ALTER TABLE "chatrooms" ADD CONSTRAINT "FK_users_TO_chatrooms_2" FOREIGN KEY (
	"buyer_id"
)
REFERENCES "users" (
	"id"
);

ALTER TABLE "chatrooms" ADD CONSTRAINT "FK_trades_TO_chatrooms_1" FOREIGN KEY (
	"trade_id"
)
REFERENCES "trades" (
	"id"
);

ALTER TABLE "trades" ADD CONSTRAINT "FK_users_TO_trades_1" FOREIGN KEY (
	"seller_id"
)
REFERENCES "users" (
	"id"
);

ALTER TABLE "trade_images" ADD CONSTRAINT "FK_trades_TO_trade_images_1" FOREIGN KEY (
	"trade_id"
)
REFERENCES "trades" (
	"id"
);