[DB DDL]

회사

CREATE TABLE company (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(64) NOT NULL COMMENT '회사 이름',
    latitude DOUBLE NOT NULL COMMENT '회사 위치 위도',
    longitude DOUBLE NOT NULL COMMENT '회사 위치 경도',
    use_yn CHAR(1) NOT NULL COMMENT '사용 여부',
    created_id BIGINT NOT NULL COMMENT '생성자',
    created_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '생성 일자',
    updated_id BIGINT NULL COMMENT '수정자',
    updated_date DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '수정 일자'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

식당

CREATE TABLE restaurant (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    rounded_latitude DECIMAL(8, 5) NOT NULL COMMENT '반올림된 위도',
    rounded_longitude DECIMAL(8, 5) NOT NULL COMMENT '반올림된 경도',
    category VARCHAR(32) NOT NULL COMMENT '식당 카테고리',
    place_url VARCHAR(512) NULL COMMENT '장소 링크 url',
    address VARCHAR(255) NOT NULL COMMENT '식당 주소',
    name VARCHAR(255) NOT NULL COMMENT '식당 이름',
    road_address VARCHAR(255) NOT NULL COMMENT '식당 도로명 주소',
    latitude DOUBLE NOT NULL COMMENT '위도',
    longitude DOUBLE NOT NULL COMMENT '경도',
    use_yn CHAR(1) NOT NULL COMMENT '사용 여부',
    created_id BIGINT NOT NULL COMMENT '생성자',
    created_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '생성 일자',
    updated_id BIGINT NULL COMMENT '수정자',
    updated_date DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '수정 일자',
    CONSTRAINT uk_restaurant_name_location UNIQUE (name, rounded_longitude, rounded_latitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE restaurant_search (
   restaurant_id BIGINT NOT NULL PRIMARY KEY,
   name VARCHAR(255) NOT NULL COMMENT '식당 이름',
   road_address VARCHAR(255) NOT NULL COMMENT '식당 주소',
   created_id BIGINT NOT NULL COMMENT '생성자',
   created_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '생성 일자',
   updated_id BIGINT NULL COMMENT '수정자',
   updated_date DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '수정 일자',
   FULLTEXT INDEX ft_name (name) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

팀

CREATE TABLE team (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL COMMENT '회사 고유 ID',
    name VARCHAR(255) NOT NULL COMMENT '팀 이름',
    use_yn CHAR(1) NOT NULL COMMENT '사용 여부',
    created_id BIGINT NOT NULL COMMENT '생성자',
    created_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '생성 일자',
    updated_id BIGINT NULL COMMENT '수정자',
    updated_date DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '수정 일자'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE team_invitation (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    team_id BIGINT NOT NULL COMMENT '팀 고유 ID',
    invitation_token VARCHAR(64) NOT NULL COMMENT '팀 초대 고유 토큰',
    expires_date DATETIME(6) NOT NULL COMMENT '토큰 만료 일자',
    created_id BIGINT NOT NULL COMMENT '생성자',
    created_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '생성 일자',
    updated_id BIGINT NULL COMMENT '수정자',
    updated_date DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '수정 일자',
    CONSTRAINT UKkwrvcy3raeod6ai8mk0sioewe UNIQUE (invitation_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE team_member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '유저 고유 ID',
    team_id BIGINT NOT NULL COMMENT '팀 고유 ID',
    role VARCHAR(16) NOT NULL COMMENT '팀 내 역할',
    status VARCHAR(16) NOT NULL COMMENT '팀 가입 상태',
    use_yn CHAR(1) NOT NULL COMMENT '사용 여부',
    created_id BIGINT NOT NULL COMMENT '생성자',
    created_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '생성 일자',
    updated_id BIGINT NULL COMMENT '수정자',
    updated_date DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '수정 일자'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE team_restaurant (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT NOT NULL COMMENT '식당 고유 ID',
    team_id BIGINT NOT NULL COMMENT 'team 고유 ID',
    average_review_score DOUBLE NULL COMMENT '리뷰 평균 점수',
    average_serving_time INT NULL COMMENT '평균 음식 나오는 시간',
    average_waiting_time INT NULL COMMENT '평균 대기 시간',
    distance_from_team DOUBLE NULL COMMENT '팀에서 맛집까지 거리',
    review_count INT NULL COMMENT '리뷰 갯수',
    summary VARCHAR(20) NULL COMMENT '한줄 소개',
    use_yn CHAR(1) NOT NULL COMMENT '사용 여부',
    created_id BIGINT NOT NULL COMMENT '생성자',
    created_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '생성 일자',
    updated_id BIGINT NULL COMMENT '수정자',
    updated_date DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '수정 일자'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE team_restaurant_search (
    team_restaurant_id BIGINT NOT NULL PRIMARY KEY,
    team_id BIGINT NOT NULL COMMENT '팀 고유 ID',
    name VARCHAR(255) NOT NULL COMMENT '식당 이름',
    average_review_score DOUBLE NOT NULL COMMENT '리뷰 평균 점수',
    distance_from_team DOUBLE NOT NULL COMMENT '팀에서 맛집까지 거리',
    latitude DOUBLE NOT NULL COMMENT '사용 위도',
    longitude DOUBLE NOT NULL COMMENT '식당 경도',
    use_yn CHAR(1) NOT NULL COMMENT '사용 여부',
    created_id BIGINT NOT NULL COMMENT '생성자',
    created_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '생성 일자',
    updated_id BIGINT NULL COMMENT '수정자',
    updated_date DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '수정 일자',
    FULLTEXT INDEX ft_name (name) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;