DB.sql

USE web_db;

-- USER 테이블 (사용자)
CREATE TABLE IF NOT EXISTS USER (
    id VARCHAR(255) PRIMARY KEY,
    USER_ID INT AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone_number VARCHAR(255) NOT NULL,
    zip_code VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    sex VARCHAR(255) NOT NULL,
    nickname VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    money INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS manager (
    manager_id VARCHAR(255) PRIMARY KEY,
    manager_pw VARCHAR(255) NOT NULL,
    position VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PRODUCT 테이블 (상품)
CREATE TABLE IF NOT EXISTS PRODUCT (
    PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
    SELLER_ID INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    description TEXT,
    image_url LONGBLOB,
    delivery_method VARCHAR(50),
    category VARCHAR(50) DEFAULT '기타',
    meeting_zip_code VARCHAR(20),
    meeting_address VARCHAR(255),
    meeting_detail VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_sold TINYINT(1) DEFAULT 0,
    FOREIGN KEY (SELLER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE
);

-- QNA 테이블 (게시판/문의사항)
CREATE TABLE IF NOT EXISTS QNA (
    QNA_ID INT AUTO_INCREMENT PRIMARY KEY,
    USER_ID INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    question TEXT NOT NULL,
    answer TEXT,
    view_count INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (USER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE
);

-- TRANSACTION 테이블 (거래)
CREATE TABLE IF NOT EXISTS TRANSACTION (
    TRANSACTION_ID INT AUTO_INCREMENT PRIMARY KEY,
    PRODUCT_ID INT NOT NULL,
    BUYER_ID INT NOT NULL,
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID) ON DELETE CASCADE,
    FOREIGN KEY (BUYER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE
);

-- REVIEW 테이블 (리뷰)
CREATE TABLE IF NOT EXISTS REVIEW (
    REVIEW_ID INT AUTO_INCREMENT PRIMARY KEY,
    TRANSACTION_ID INT NOT NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (TRANSACTION_ID) REFERENCES TRANSACTION(TRANSACTION_ID) ON DELETE CASCADE
);

-- COMMENTS 테이블 (상품 댓글)
CREATE TABLE IF NOT EXISTS COMMENTS (
    COMMENT_ID INT AUTO_INCREMENT PRIMARY KEY,
    PRODUCT_ID INT NOT NULL,
    USER_ID INT NOT NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID) ON DELETE CASCADE,
    FOREIGN KEY (USER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE
);

-- CHAT_ROOM 테이블 (채팅방)
CREATE TABLE IF NOT EXISTS CHAT_ROOM (
    ROOM_ID INT AUTO_INCREMENT PRIMARY KEY,
    PRODUCT_ID INT NOT NULL,
    SELLER_ID INT NOT NULL,
    BUYER_ID INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID) ON DELETE CASCADE,
    FOREIGN KEY (SELLER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE,
    FOREIGN KEY (BUYER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE,
    UNIQUE KEY unique_room (PRODUCT_ID, SELLER_ID, BUYER_ID)
);

-- CHAT_MESSAGE 테이블 (채팅 메시지)
CREATE TABLE IF NOT EXISTS CHAT_MESSAGE (
    MESSAGE_ID INT AUTO_INCREMENT PRIMARY KEY,
    ROOM_ID INT NOT NULL,
    SENDER_ID INT NOT NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ROOM_ID) REFERENCES CHAT_ROOM(ROOM_ID) ON DELETE CASCADE,
    FOREIGN KEY (SENDER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE
);

-- 인덱스 생성
CREATE INDEX idx_product_seller_id ON PRODUCT(SELLER_ID);
CREATE INDEX idx_product_is_sold ON PRODUCT(is_sold);
CREATE INDEX idx_product_category ON PRODUCT(category);
CREATE INDEX idx_qna_user_id ON QNA(USER_ID);
CREATE INDEX idx_transaction_product_id ON TRANSACTION(PRODUCT_ID);
CREATE INDEX idx_transaction_buyer_id ON TRANSACTION(BUYER_ID);
CREATE INDEX idx_review_transaction_id ON REVIEW(TRANSACTION_ID);
CREATE INDEX idx_comments_product_id ON COMMENTS(PRODUCT_ID);
CREATE INDEX idx_comments_user_id ON COMMENTS(USER_ID);
CREATE INDEX idx_chat_room_product_id ON CHAT_ROOM(PRODUCT_ID);
CREATE INDEX idx_chat_room_seller_id ON CHAT_ROOM(SELLER_ID);
CREATE INDEX idx_chat_room_buyer_id ON CHAT_ROOM(BUYER_ID);
CREATE INDEX idx_chat_message_room_id ON CHAT_MESSAGE(ROOM_ID);
CREATE INDEX idx_chat_message_sender_id ON CHAT_MESSAGE(SENDER_ID);
CREATE INDEX idx_chat_message_created_at ON CHAT_MESSAGE(created_at);

-- 관리자 계정 추가
INSERT INTO manager (manager_id, manager_pw, position) VALUES ("jin123", "jin123", "팀장");