db_schema.sql

CREATE DATABASE IF NOT EXISTS team7_db;
use team7_db;

-- USER 테이블
CREATE TABLE USER (
  id VARCHAR(255) PRIMARY KEY,
  nickname VARCHAR(255),
  password VARCHAR(255)
);

-- GROUP 테이블
CREATE TABLE `GROUP` (
  id INT PRIMARY KEY AUTO_INCREMENT,
  owner VARCHAR(255),
  name VARCHAR(255),
  imageUrl VARCHAR(255),
  introduction TEXT,
  postCount INT,
  memberCount INT,
  badges JSON DEFAULT ('[]'),
  FOREIGN KEY (owner) REFERENCES USER(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- POST 테이블
CREATE TABLE POST (
  id INT PRIMARY KEY AUTO_INCREMENT,
  userId VARCHAR(255),
  groupId INT,
  title VARCHAR(255),
  content TEXT,
  imageUrl VARCHAR(255),
  isPublic BOOLEAN,
  location VARCHAR(255),
  moment DATETIME,
  likeCount INT,
  commentCount INT,
  createdAt DATETIME,
  FOREIGN KEY (userId) REFERENCES USER(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (groupId) REFERENCES `GROUP`(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- PARTICIPATE 테이블
CREATE TABLE PARTICIPATE (
  userId VARCHAR(255),
  groupId INT,
  PRIMARY KEY (userId, groupId),
  FOREIGN KEY (userId) REFERENCES USER(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (groupId) REFERENCES `GROUP`(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- COMMENT 테이블
CREATE TABLE COMMENT (
  id INT PRIMARY KEY AUTO_INCREMENT,
  userId VARCHAR(255),
  content TEXT,
  postId INT,
  createdAt DATETIME,
  FOREIGN KEY (userId) REFERENCES USER(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (postId) REFERENCES POST(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- SCRAP 테이블
CREATE TABLE SCRAP (
  userId VARCHAR(255),
  postId INT,
  PRIMARY KEY (userId, postId),
  FOREIGN KEY (userId) REFERENCES USER(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (postId) REFERENCES POST(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- LIKE 테이블
CREATE TABLE `LIKE` (
  userId VARCHAR(255),
  postId INT,
  PRIMARY KEY (userId, postId),
  FOREIGN KEY (userId) REFERENCES USER(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (postId) REFERENCES POST(id) ON DELETE CASCADE ON UPDATE CASCADE
);