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)