CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nickname VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    profile_image_url TEXT NULL,
    profile_background_image_url TEXT NULL,
    provider ENUM('GOOGLE', 'KAKAO', 'NAVER') NOT NULL,
    role ENUM('ADMIN', 'USER') NOT NULL DEFAULT 'USER',
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    last_login_at DATETIME NULL,
    active TINYINT NULL DEFAULT 1,
    report_count TINYINT NULL DEFAULT 0,
    self_introduction VARCHAR(255) NULL,
    access_range ENUM('PUBLIC','PRIVATE','FRIEND') NOT NULL DEFAULT 'PUBLIC',
    warning_enabled TINYINT NOT NULL DEFAULT 1
);

CREATE TABLE badge (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    standard SMALLINT NOT NULL,
    description VARCHAR(255) NOT NULL
);

CREATE TABLE user_badge (
    id INT PRIMARY KEY AUTO_INCREMENT,
    badgeId INT NOT NULL,
    userId INT NOT NULL,
    count INT NOT NULL, // 가입일로 며칠 지났다거나, 채소를 몇번 등록했는지에 대한 수치
    FOREIGN KEY (badgeId) REFERENCES badge(id),
    FOREIGN KEY (userId) REFERENCES users(id)
);

CREATE TABLE food_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(10) NOT NULL UNIQUE
);

CREATE TABLE recipes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    author_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    thumbnail_url VARCHAR(255) NULL,
    view_cnt INT NOT NULL DEFAULT 0,
    like_cnt INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    FOREIGN KEY (writer_id) REFERENCES users(id)
);

CREATE TABLE user_receipts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    store_name VARCHAR(100) NULL,
    purchase_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE user_ingredients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    receipt_id INT NOT NULL,
    category_id INT NOT NULL,
    ingredient_quantity INT NOT NULL DEFAULT 100,
    ingredient_up DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ingredient_name VARCHAR(100) NOT NULL,
    due_date DATETIME NOT NULL,
    expiration_date DATETIME NOT NULL,
    ingredient_storage ENUM('FROZEN', 'REFRIGERATED', 'ROOM_TEMPERATURE') NOT NULL,
    price INT NOT NULL DEFAULT 0,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    normalized_ingredient_id INT,
    FOREIGN KEY (normalized_ingredient_id) REFERENCES normalized_ingredient(id),
    FOREIGN KEY (receipt_id) REFERENCES user_receipts(id),
    FOREIGN KEY (category_id) REFERENCES food_categories(id)
);

CREATE TABLE recipe_steps (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recipe_id INT NOT NULL,
    step_number INT NOT NULL DEFAULT 0,
    content TEXT NULL,
    image_url VARCHAR(255) NULL,
    FOREIGN KEY (recipe_id) REFERENCES recipes(id)
);

CREATE TABLE recipe_ingredients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recipe_id INT NOT NULL,
    ingredient_name VARCHAR(50) NOT NULL,
    amount VARCHAR(20) NOT NULL,
    unit VARCHAR(10) NULL,
    ingredient_order INT NOT NULL DEFAULT 0,
    normalized_ingredient_id INT,
    FOREIGN KEY (normalized_ingredient_id) REFERENCES normalized_ingredient(id),
    FOREIGN KEY (recipe_id) REFERENCES recipes(id)
);

CREATE TABLE follows (
    id INT PRIMARY KEY AUTO_INCREMENT,
    follower_id INT NOT NULL,
    following_id INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (follower_id) REFERENCES users(id),
    FOREIGN KEY (following_id) REFERENCES users(id)
);

CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tag_name VARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE recipe_tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recipe_id INT NOT NULL,
    tag_id INT NOT NULL,
    FOREIGN KEY (recipe_id) REFERENCES recipes(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);

CREATE TABLE recipe_comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recipe_id INT NOT NULL,
    author_id INT NOT NULL,
    parent_id INT NULL,
    comment TEXT NOT NULL,
    depth TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted TINYINT NOT NULL DEFAULT 0,
    FOREIGN KEY (recipe_id) REFERENCES recipes(id),
    FOREIGN KEY (writer_id) REFERENCES users(id),
    FOREIGN KEY (parent_id) REFERENCES recipe_comments(id)
);

CREATE TABLE bookmarks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recipe_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (recipe_id) REFERENCES recipes(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE recipe_like (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recipe_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (recipe_id) REFERENCES recipes(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE chart_ingredients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    indegrient_name VARCHAR(50) NOT NULL UNIQUE,
    ingredient_code INT NOT NULL,
    FOREIGN KEY (category_id) REFERENCES food_categories(id)
);

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    author_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    thumbnail_url VARCHAR(255) NULL,
    content TEXT NOT NULL,
    post_type ENUM('GROUP_PURCHASE', 'SHARE') NOT NULL,
    status ENUM('ACTIVE', 'COMPLETED', 'DELETED') NOT NULL DEFAULT 'ACTIVE',
    view_cnt INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    FOREIGN KEY (writer_id) REFERENCES users(id)
);

CREATE TABLE post_images (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    image_url VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

CREATE TABLE post_comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    author_id INT NOT NULL,
    parent_id INT NULL,
    comment TEXT NOT NULL,
    depth TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted TINYINT NOT NULL DEFAULT 0,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (writer_id) REFERENCES users(id),
    FOREIGN KEY (parent_id) REFERENCES post_comments(id)
);

CREATE TABLE terms_and_conditions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL
);

CREATE TABLE report_category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL
);

CREATE TABLE report (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reporter_id INT NOT NULL,
    category_id INT NOT NULL,
    report_type ENUM('RECIPE', 'COMMENT', 'POST') NOT NULL,
    target_id INT NOT NULL,
    description TEXT NULL,
    status ENUM('PENDING', 'RESOLVED', 'REJECTED') NOT NULL DEFAULT 'PENDING',
    report_image_url TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    FOREIGN KEY (reporter_id) REFERENCES users(id),
    FOREIGN KEY (category_id) REFERENCES report_category(id)
);

CREATE TABLE chat_rooms (
    id INT PRIMARY KEY AUTO_INCREMENT,
    creator_id INT NULL,
    post_id INT NULL,
    room_name VARCHAR(100) NOT NULL,
    room_type ENUM('PRIVATE', 'GROUP') NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    member_limit TINYINT NOT NULL DEFAULT 2,
    FOREIGN KEY (creator_id) REFERENCES users(id),
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

CREATE TABLE chat_room_members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    room_id INT NOT NULL,
    user_id INT NOT NULL,
    nickname VARCHAR(50) NOT NULL,
    role ENUM('ADMIN', 'MEMBER') NOT NULL DEFAULT 'MEMBER',
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_read_message_id INT NOT NULL DEFAULT 0,
    FOREIGN KEY (room_id) REFERENCES chat_rooms(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE chat_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    room_id INT NOT NULL,
    sender_id INT NOT NULL,
    message_type ENUM('TEXT', 'IMAGE', 'FILE') NOT NULL DEFAULT 'TEXT',
    content TEXT NOT NULL,
    file_url VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (room_id) REFERENCES chat_rooms(id),
    FOREIGN KEY (sender_id) REFERENCES users(id)
);

CREATE TABLE food_preference (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    category_id INT NOT NULL,
    category_preference INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (category_id) REFERENCES food_categories(id)
);

CREATE TABLE normalized_ingredient (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);