algoga111

Tables (35)

ai_itineraries

  • itinerary_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • country_id — BIGINT — Foreign key → countries.country_id — NOT NULL
  • departure_date — DATE — NOT NULL
  • return_date — DATE — NOT NULL
  • itinerary_json — JSON — NOT NULL — 환불을 승인/반려한 매니저
  • created_at — DATETIME

banners

  • banner_id — BIGINT
  • manager_id — BIGINT — Foreign key → managers.manager_id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • link_url — VARCHAR(500)
  • text — VARCHAR(255)
  • start_date — DATETIME — NOT NULL
  • end_date — DATETIME — NOT NULL
  • created_at — DATETIME

bookings

  • booking_id — BIGINT
  • package_id — BIGINT — Foreign key → packages.package_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • status — ENUM('PENDING', 'DEPOSIT_PAID', 'FULL_PAID', 'CANCEL_REQUESTED', 'REFUNDED')
  • total_price — INT — NOT NULL
  • deposit_price — INT — NOT NULL
  • balance_price — INT — NOT NULL
  • booking_number — VARCHAR(50) — NOT NULL — 예약 번호
  • accommodation_id — BIGINT
  • flight_info — JSON
  • check_in_date — DATE
  • check_out_date — DATE
  • nights — INT
  • created_at — DATETIME
  • updated_at — DATETIME

calendars

  • calendar_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • event_date — DATETIME — NOT NULL
  • type — ENUM('LECTURE', 'TRIP', 'D_DAY') — NOT NULL
  • memo — TEXT
  • is_d_day_alert_sent — BOOLEAN — 1~5점
  • created_at — DATETIME

chapters

  • chapter_id — BIGINT
  • lecture_id — BIGINT — Foreign key → lectures.lecture_id — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • video_url — VARCHAR(500) — NOT NULL
  • order_num — INT — NOT NULL — 순서
  • created_at — DATETIME

chat_messages

  • message_id — BIGINT
  • sender_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • receiver_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • message — VARCHAR(300) — NOT NULL — 지급(+), 사용(-)
  • is_read — BOOLEAN
  • created_at — DATETIME

comments

  • comment_id — BIGINT
  • post_id — BIGINT — Foreign key → posts.post_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • parent_id — BIGINT — 대댓글 처리를 위한 Self-referencing
  • content — VARCHAR(500) — NOT NULL
  • is_deleted — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME

countries

  • country_id — BIGINT
  • continent — VARCHAR(50) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • is_active — BOOLEAN — 강의/여행 준비중 여부

coupons

  • coupon_id — BIGINT
  • name — VARCHAR(50) — NOT NULL
  • target_type — ENUM('LECTURE', 'BOOKING') — NOT NULL
  • discount_type — ENUM('PERCENT', 'AMOUNT') — NOT NULL
  • discount_value — INT — NOT NULL — % 또는 원
  • max_discount_amount — INT — 최대 할인 금액
  • valid_months — INT — NOT NULL — 만료기간(개월수)
  • created_at — DATETIME

friends

  • friendship_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • friend_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • status — ENUM('PENDING', 'ACCEPTED', 'BLOCKED')
  • created_at — DATETIME

inquiries

  • inquiry_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • manager_id — BIGINT — Foreign key → managers.manager_id — AI가 생성한 일정 데이터
  • question — TEXT — NOT NULL
  • answer — TEXT
  • status — ENUM('PENDING', 'COMPLETED')
  • created_at — DATETIME
  • answered_at — DATETIME

lecture_qna

  • qna_id — BIGINT
  • lecture_id — BIGINT — Foreign key → lectures.lecture_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • parent_id — BIGINT — D-day 알림 발송 여부
  • content — TEXT — NOT NULL
  • created_at — DATETIME

lecture_reviews

  • review_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • lecture_id — BIGINT — Foreign key → lectures.lecture_id — NOT NULL
  • rating — INT — NOT NULL — 10글자 이상
  • content — TEXT — NOT NULL — 여권상 영문 이름
  • created_at — DATETIME

lectures

  • lecture_id — BIGINT
  • country_id — BIGINT — Foreign key → countries.country_id — NOT NULL
  • manager_id — BIGINT — Foreign key → managers.manager_id — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT — NOT NULL
  • thumbnail_url — VARCHAR(500) — NOT NULL
  • file_url — VARCHAR(500) — 강의 자료
  • is_deleted — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME

likes_dislikes

  • like_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • target_type — ENUM('POST', 'COMMENT 'TRUE: 좋아요, FALSE: 싫어요',') — NOT NULL — TRUE: 좋아요, FALSE: 싫어요',') NOT NULL `target_id` BIGINT NOT NUL
  • is_like — BOOLEAN — NOT NULL — 최대 300자
  • created_at — DATETIME

managers

  • manager_id — BIGINT
  • login_id — VARCHAR(50) — NOT NULL — 매니저 로그인 아이디
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(20) — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • role — ENUM('CONTENT_MANAGER', 'CS_MANAGER', 'SETTLEMENT_MANAGER', 'SUPER_ADMIN') — NOT NULL
  • is_deleted — BOOLEAN — 퇴사 처리 등 Soft Delete
  • created_at — DATETIME
  • updated_at — DATETIME

mileage_history

  • mileage_history_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • amount — INT — NOT NULL — 답변을 처리한 CS 매니저
  • transaction_type — ENUM('EARN', 'USE') — NOT NULL
  • description — VARCHAR(255) — NOT NULL
  • created_at — DATETIME

notices

  • notice_id — BIGINT
  • manager_id — BIGINT — Foreign key → managers.manager_id — NOT NULL
  • type — ENUM('NOTICE', 'EVENT', 'MAINTENANCE') — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — TEXT — NOT NULL
  • start_date — DATETIME — NOT NULL
  • end_date — DATETIME — NOT NULL
  • created_at — DATETIME

notification_setting

  • user_id — BIGINT — Foreign key → users.user_id
  • learning_enabled — BOOLEAN
  • qna_enabled — BOOLEAN
  • community_enabled — BOOLEAN
  • notice_enabled — BOOLEAN
  • inquiry_enabled — BOOLEAN
  • friend_enabled — BOOLEAN

notifications

  • notification_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • type — ENUM('COURSE_REGISTERED', 'COURSE_COMPLETED', 'DDAY_REMINDER', 'QNA_ANSWERED', 'POST_COMMENTED', 'COMMENT_REPLIED', 'PAYMENT_COMPLETED', 'RESERVATION_CONFIRMED', 'REFUND_APPROVED', 'REFUND_REJECTED', 'NOTICE_CREATED', 'INQUIRY_ANSWERED', 'FRIEND_REQUESTED', 'FRIEND_ACCEPTED', 'SYSTEM') — NOT NULL
  • message — VARCHAR(255) — NOT NULL
  • created_at — DATETIME
  • is_read — BOOLEAN

packages

  • package_id — BIGINT
  • country_id — BIGINT — Foreign key → countries.country_id — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • total_price — INT — NOT NULL
  • deposit_rate — DECIMAL(5, 2) — 예약금 비율 (기본 30%)
  • description — TEXT
  • balance_price — INT
  • flight_price — INT
  • accommodation_price — INT
  • airline_code — VARCHAR(50)
  • airline_name — VARCHAR(50)
  • flight_number — VARCHAR(50)
  • departure_airport — VARCHAR(50)
  • arrival_airport — VARCHAR(50)
  • departure_date — DATE
  • return_date — DATE
  • arrival_time — TIME
  • return_flight_number — VARCHAR(50)
  • return_departure_time — VARCHAR(50)
  • return_arrival_time — TIME
  • duration_minutes — INT
  • seats_available — INT
  • accommodation_name — VARCHAR(50)
  • accommodation_address — VARCHAR(255)
  • nights — INT
  • accommodation_image — VARCHAR(255)
  • is_deleted — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME

passports

  • passport_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • english_name — VARCHAR(100) — NOT NULL — 여권 번호
  • passport_number — VARCHAR(20) — NOT NULL — 만료일
  • expiry_date — DATE — NOT NULL — 성별 (M/F)
  • gender — ENUM('MALE', 'FEMALE') — NOT NULL — 국가
  • nationality — VARCHAR(50) — NOT NULL — 수료증 PDF URL
  • created_at — DATETIME
  • updated_at — DATETIME

payments

  • payment_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • booking_id — BIGINT
  • lecture_id — BIGINT
  • payment_type — ENUM('DEPOSIT', 'BALANCE', 'FULL', 'LECTURE_ONLY') — NOT NULL
  • amount — INT — NOT NULL
  • used_mileage — INT
  • used_coupon_id — BIGINT — Foreign key → user_coupons.user_coupon_id
  • status — ENUM('SUCCESS', 'FAILED', 'REFUNDED')
  • idempotency_key — VARCHAR(255) — NOT NULL — 이중 결제 방지용 키
  • created_at — DATETIME

post_images

  • post_image_id — BIGINT
  • post_id — BIGINT — Foreign key → posts.post_id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • order_num — INT
  • created_at — DATETIME

post_tags

  • post_tag_id — BIGINT
  • post_id — BIGINT — Foreign key → posts.post_id — NOT NULL
  • tag_type — ENUM('COUNTRY','FREE','LECTURE')
  • tag_name — VARCHAR(100)

posts

  • post_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — TEXT — NOT NULL
  • country_id — BIGINT — 나라 태그 선택 시
  • lecture_id — BIGINT — 강의 태그 선택 시
  • view_count — INT
  • is_deleted — BOOLEAN — Soft Delete
  • created_at — DATETIME
  • updated_at — DATETIME

quizzes

  • quiz_id — BIGINT
  • lecture_id — BIGINT — Foreign key → lectures.lecture_id — NOT NULL
  • question — TEXT — NOT NULL
  • option1 — VARCHAR(255) — NOT NULL
  • option2 — VARCHAR(255) — NOT NULL
  • option3 — VARCHAR(255) — NOT NULL
  • option4 — VARCHAR(255) — NOT NULL
  • answer_num — INT — NOT NULL — 정답 번호 (1~4)
  • explanation — TEXT — 오답 노트용 해설

refund_requests

  • refund_request_id — BIGINT
  • booking_id — BIGINT — Foreign key → bookings.booking_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • manager_id — BIGINT — Foreign key → managers.manager_id — 답글 처리를 위한 Self-referencing
  • reason — TEXT — NOT NULL
  • status — ENUM('REQUESTED', 'APPROVED', 'REJECTED', 'COMPLETED')
  • created_at — DATETIME
  • updated_at — DATETIME

reports

  • report_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • target_id — BIGINT — NOT NULL
  • target_type — ENUM('POST', 'COMMENT', 'USER') — NOT NULL
  • reason_type — ENUM('SPAM', 'ABUSE', 'FALSE_INFO', 'INAPPROPRIATE', 'COPYRIGHT', 'ETC')
  • detail — VARCHAR(255) — NOT NULL
  • created_at — DATETIME

user_achievements

  • achievement_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • type — ENUM('LECTURE_COMPLETE', 'BOOKING_COMPLETE', 'NATION_VISITED') — NOT NULL
  • achieved_at — DATETIME
  • lecture_id — BIGINT
  • booking_id — BIGINT
  • country_id — BIGINT

user_chapters

  • user_chapter_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • chapter_id — BIGINT — Foreign key → chapters.chapter_id — NOT NULL
  • is_completed — BOOLEAN

user_coupons

  • user_coupon_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • coupon_id — BIGINT — Foreign key → coupons.coupon_id — NOT NULL
  • is_used — BOOLEAN
  • expires_at — DATETIME — NOT NULL
  • used_at — DATETIME

user_lectures

  • user_lecture_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • lecture_id — BIGINT — Foreign key → lectures.lecture_id — NOT NULL
  • progress_rate — DECIMAL(5, 2)
  • is_completed — BOOLEAN
  • completed_at — DATETIME
  • certificate_url — VARCHAR(500) — Foreign key → users.user_id

user_quizzes

  • user_quiz_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • quiz_id — BIGINT — Foreign key → quizzes.quiz_id — NOT NULL
  • selected_option — INT — NOT NULL
  • is_correct — BOOLEAN — NOT NULL
  • created_at — DATETIME

users

  • user_id — BIGINT
  • email — VARCHAR(255) — NOT NULL — 소문자 저장
  • password — VARCHAR(255) — 소셜 로그인은 NULL 또는 더미
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(20) — NOT NULL
  • birth_date — DATE — NOT NULL
  • gender — ENUM('MALE', 'FEMALE', 'OTHER') — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • referral_code — VARCHAR(50) — 추천인 코드
  • signup_path — VARCHAR(100) — 유입 경로
  • social_type — ENUM('LOCAL', 'KAKAO', 'GOOGLE')
  • personal_code — CHAR(36) — NOT NULL — 친구 추가용 UUID
  • level — ENUM('BEGINNER', 'INTERMEDIATE', 'ADVANCED') — 진단평가 등급
  • login_fail_count — INT — 로그인 실패 횟수 (5회 시 잠금)
  • locked_until — DATETIME — 계정 잠금 해제 시간
  • is_blacklisted — BOOLEAN — 블랙리스트 여부 (누적 신고 5회)
  • is_deleted — BOOLEAN — Soft Delete 여부
  • last_login_at — DATETIME
  • last_logout_at — DATETIME
  • created_at — DATETIME
  • updated_at — DATETIME
  • isNotificationAgree — BOOLEAN — NOT NULL
  • profile_image_url — VARCHAR(255)