testte
Tables (29)
ad_slot
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — 수료 완료 시각
- placement — VARCHAR(80) — NOT NULL
- status — ENUM('ACTIVE', 'INACTIVE') — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
advertisement
- id — BIGINT — Primary key — NOT NULL
- ad_slot_id — BIGINT — Foreign key → ad_slot.id — NOT NULL
- advertiser_name — VARCHAR(200) — NOT NULL
- exhibitor_id — BIGINT — Foreign key → exhibitor.id — NOT NULL
- image_url — VARCHAR(500)
- link_url — VARCHAR(500)
- start_at — DATETIME — NOT NULL
- end_at — DATETIME — NOT NULL
- price — DECIMAL(12, 2) — NOT NULL
- status — ENUM('DRAFT', 'ACTIVE', 'PAUSED', 'EXPIRED') — NOT NULL
- impressions — BIGINT — NOT NULL
- clicks — BIGINT — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
booth
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- exhibitor_id — BIGINT — Foreign key → exhibitor.id — NOT NULL
- category_id — BIGINT
- name — VARCHAR(200) — NOT NULL
- description — TEXT — 예약 확정/보류 인원수 (원자 UPDATE 대상)
- tags — VARCHAR(500) — 발행 한도 (NULL = 무제한)
- pos_x — INT — 예약 대표자 (users.id, VISITOR)
- pos_y — INT — INDIVIDUAL=개별QR추적, GROUP=대표QR 단체이동
- floor — INT — 예약 인원수 (정원 차감 단위, 기본 1)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =전시 — 부스(RAG 인덱싱 대상: description tags
booth_category
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — NOT NULL
- name — VARCHAR(100) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
booth_embedding
- id — BIGINT — Primary key — NOT NULL
- booth_id — BIGINT — NOT NULL
- embedding — JSON — NOT NULL — 통과 기준 0~100 (NULL=퀴즈 없음)
- model — VARCHAR(100) — 영상 시청 완료 (영상 있는 가이드의 이수 필수 조건)
- source_hash — VARCHAR(64) — 획득 점수 0~100 (NULL=퀴즈 없음)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =부스 — 임베딩(RAG MySQL JSON, source_hash 변경감지
checkin_log
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- reservation_id — BIGINT — Foreign key → reservation.id — NOT NULL
- attendee_id — BIGINT — Foreign key → reservation_attendee.id — NOT NULL
- name_tag_id — BIGINT — Foreign key → name_tag.id
- checkin_method — ENUM( 'QR_SELF', 'MANUAL_SEARCH', 'ONSITE_MANUAL', 'WALK_IN', 'REISSUE' ) — NOT NULL — 자동 추정 마감 여부
- checked_in_by_user_id — BIGINT — NOT NULL — 체류 종료 이유 (미종결이면 NULL)
- checked_in_at — DATETIME — NOT NULL
- memo — VARCHAR(500) — 테넌트 스코프 (파생, FK 없음)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =체크인 — 감사
education_completion
- id — BIGINT — Primary key — NOT NULL
- guide_id — BIGINT — Foreign key → education_guide.id — NOT NULL
- user_id — BIGINT — NOT NULL
- video_completed — TINYINT(1) — NOT NULL
- updated_at — DATETIME — NOT NULL
education_guide
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — NOT NULL
- category — VARCHAR(50) — NOT NULL
- content — TEXT — NOT NULL
- quiz_questions — JSON — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
exhibition
- id — BIGINT — Primary key — NOT NULL
- title — VARCHAR(200) — NOT NULL
- slug — VARCHAR(120) — NOT NULL — 배치도 축척 게이트 좌표
- venue — VARCHAR(200)
- address — VARCHAR(255)
- floor_map_meta — JSON — TRUE면 LMS 미수료 STAFF 체크인/현장결제 차단 (하드 게이트)
- start_date — DATE — NOT NULL
- end_date — DATE — NOT NULL
- status — ENUM('DRAFT', 'OPEN', 'CLOSED') — NOT NULL
- enforce_staff_qualification — TINYINT(1) — NOT NULL — users.id (PLATFORM_ADMIN)
- created_by — BIGINT — NOT NULL — 논리 삭제 시각
- deleted_at — DATETIME — users.id (EXPO_ADMIN)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =박람회 — (멀티테넌트 경계 STAFF 하드 게이트 제어
exhibition_admin
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- user_id — BIGINT — NOT NULL — RAG 인덱싱 대상
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
exhibition_staff
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL — RAG 인덱싱 대상
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
exhibitor
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- company_name — VARCHAR(200) — NOT NULL
- intro — TEXT — 배치 X좌표
- website — VARCHAR(255)
- account_user_id — BIGINT — Foreign key → users.id — 배치 Y좌표
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
media_asset
- id — BIGINT — Primary key — NOT NULL — 기본 키
- owner_type — ENUM('BOOTH', 'EXHIBITION', 'EDUCATION_GUIDE', 'ADVERTISEMENT') — NOT NULL — 소속 도메인 구분
- owner_id — BIGINT — NOT NULL — 소속 도메인의 PK (다형성, 하드 FK 없음)
- media_type — ENUM('IMAGE', 'VIDEO') — NOT NULL — 미디어 유형
- s3_key — VARCHAR(500) — NOT NULL — S3 객체 키 (버킷 내부 경로)
- s3_bucket — VARCHAR(120) — NOT NULL — S3 버킷명 (환경별 분리 대비)
- content_type — VARCHAR(100) — MIME 타입 (image/png, video/mp4 등)
- file_size — BIGINT — 파일 크기 (byte)
- display_order — INT — NOT NULL — 같은 owner 내 노출 순서 (썸네일/갤러리 정렬용)
- is_thumbnail — TINYINT(1) — NOT NULL — 대표 썸네일 여부 (owner당 1개만 TRUE 권장)
- uploaded_by — BIGINT — Foreign key — 업로드한 users.id
- is_deleted — TINYINT(1) — NOT NULL — Soft Delete
- created_at — DATETIME — NOT NULL — 생성 시각
- updated_at — DATETIME — NOT NULL — 수정 시각
name_tag
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL — EXHIBITOR 또는 게이트 담당자. 자동EXIT는 NULL
- attendee_id — BIGINT — Foreign key → reservation_attendee.id — 수기 입력 여부 (1=수기)
- token — VARCHAR(64) — NOT NULL — 시스템 자동 합성 EXIT (60분/타부스ENTRY)
- status — ENUM('AVAILABLE', 'ISSUED', 'REVOKED') — NOT NULL
- issued_by_user_id — BIGINT — 미종결이면 NULL
- issued_at — DATETIME — exit_at - entry_at (초)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =QR — 네임태그(사전인쇄풀 AVAILABLE→바인딩 ISSUED→무효 REVOKED
payment
- id — BIGINT — Primary key — NOT NULL
- reservation_id — BIGINT — NOT NULL
- pg_provider — VARCHAR(40) — QR_SELF=QR스캔, MANUAL_SEARCH=수기조회, ONSITE_MANUAL=현장수기, WALK_IN=워크인, REISSUE=재발급
- pg_tx_id — VARCHAR(120) — EXPO_ADMIN 또는 STAFF (users.id)
- amount — DECIMAL(12, 2) — NOT NULL
- fee_amount — DECIMAL(12, 2) — NOT NULL — 수기 사유 / 재발급 사유
- status — ENUM( 'READY', 'PAID', 'FAILED', 'CANCELLED', 'REFUNDED' ) — NOT NULL
- paid_at — DATETIME — 동선 추적 단위 (reservation_attendee.id)
- deleted_at — DATETIME — booth.id / sessions.id (GATE는 NULL)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =결제 — (Soft Delete webhook 멱등 pg_tx_id, ONSITE 현장결제 지원
recommended_route
- id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- preference_id — BIGINT — Foreign key → user_preference.id — 순지급액
- rationale — TEXT — 지급 완료 시각
- total_est_minutes — INT — 논리 삭제
- route_status — ENUM('CREATED', 'EXPIRED', 'DELETED') — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
reservation
- id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL — 수기 체크인 조회용
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- time_slot_id — BIGINT — Foreign key → time_slot.id
- ticket_type_id — BIGINT — Foreign key → ticket_type.id
- movement_mode — ENUM('INDIVIDUAL', 'GROUP') — NOT NULL — GROUP 대표 QR 대상 (GROUP 예약은 정확히 1명)
- group_size — INT — NOT NULL — UNIQUE. INDIVIDUAL=각자, GROUP=대표만 발급
- status — ENUM( 'PENDING', 'PAID', 'CANCELLED', 'REFUNDED', 'CHECKED_IN' ) — NOT NULL
- reservation_source — ENUM('ONLINE', 'ONSITE_MANUAL') — NOT NULL — 논리 삭제
- deleted_at — DATETIME — PG사 코드 또는 ONSITE
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =예약 — 헤더(Soft Delete 팀/개인 예약, 워크인 지원
reservation_attendee
- id — BIGINT — Primary key — NOT NULL
- reservation_id — BIGINT — Foreign key → reservation.id — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL — 수수료
- linked_user_id — BIGINT — 결제 완료 시각
- name — VARCHAR(100) — NOT NULL — 논리 삭제
- phone — VARCHAR(30) — 사전 발급 재고의 소속 행사
- email — VARCHAR(255)
- is_group_leader — TINYINT(1) — NOT NULL — 바인딩 전 NULL (reservation_attendee.id)
- ticket_qr_token — VARCHAR(64) — QR 인코딩 UUID (UNIQUE)
- checkin_status — ENUM('NOT_CHECKED_IN', 'CHECKED_IN') — NOT NULL
- attendee_status — ENUM('ACTIVE', 'CANCELLED', 'NO_SHOW') — NOT NULL
- checked_in_at — DATETIME
- deleted_at — DATETIME — 바인딩 처리자 (users.id, EXPO_ADMIN/STAFF)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =참석자 — 단위
route_stop
- id — BIGINT — Primary key — NOT NULL
- route_id — BIGINT — Foreign key → recommended_route.id — NOT NULL
- booth_id — BIGINT — NOT NULL
- visit_order — INT — NOT NULL — 행사안내/안전수칙/비상대처/부스설치/전기네트워크
- est_minutes — INT — 텍스트 가이드 본문
- reason — VARCHAR(500) — 영상 URL (STAFF 교육에 사용)
- congestion_snapshot — INT — 필수 이수 여부
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =추천 — 동선
sessions
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- host_exhibitor_id — BIGINT — Foreign key → exhibitor.id
- title — VARCHAR(200) — NOT NULL
- description — TEXT
- location — VARCHAR(120)
- start_at — DATETIME — NOT NULL
- end_at — DATETIME — NOT NULL
- capacity — INT — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
settlement
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — NOT NULL
- period_start — DATE — NOT NULL
- period_end — DATE — NOT NULL
- gross_amount — DECIMAL(14, 2) — NOT NULL
- generated_at — DATETIME — NOT NULL
- paid_out_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
stat_congestion_hourly
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — NOT NULL — 임베딩 모델명
- stat_date — DATE — NOT NULL — booth.name/description/tags 변경 감지용
- stat_hour — TINYINT — NOT NULL — NULL = 플랫폼 공통
- head_count — INT — NOT NULL — HOME_TOP / EXPO_BANNER / BOOTH_RECOMMEND 등
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =혼잡도 — 시간대별
stat_visit_point
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — NOT NULL — 관심사 (콤마 구분)
- scan_point_type — ENUM('BOOTH', 'SESSION') — NOT NULL
- scan_point_id — BIGINT — NOT NULL — 가용 시간 (분)
- stat_date — DATE — NOT NULL — 필수 방문 부스 id 배열
- stat_hour — TINYINT — NOT NULL — user_preference 연결
- visit_count — INT — NOT NULL — 전체 추천 사유 (LLM 생성)
- visitor_count — INT — NOT NULL — 총 예상 소요 (분)
- unique_attendee — INT — NOT NULL — 방문 순서
- sum_dwell_sec — BIGINT — NOT NULL — 예상 체류 (분)
- dwell_count — INT — NOT NULL — 부스별 추천 사유
- avg_dwell_sec — INT — NOT NULL — 추천 당시 혼잡도 스냅샷
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =통계 — 집계
ticket_type
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — NOT NULL
- name — VARCHAR(100) — NOT NULL
- price — DECIMAL(12, 2) — NOT NULL
- quota — INT — 참석자가 계정 보유 시 연결 (users.id)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =티켓 — 타입(무료/유료/VIP
time_slot
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- start_at — DATETIME — NOT NULL
- end_at — DATETIME — NOT NULL
- capacity — INT — NOT NULL
- reserved_count — INT — NOT NULL — 논리 삭제 (@SQLDelete)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =입장 — 시간대
user_preference
- id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL — 총 매출 (온라인+현장)
- exhibition_id — BIGINT — NOT NULL
- interest_tags — VARCHAR(500) — PG 결제 매출
- available_minutes — INT — ONSITE 현장 결제 매출
- must_visit_booth_ids — JSON — PG/플랫폼 수수료
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =AI — 추천
users
- id — BIGINT — Primary key — NOT NULL
- email — VARCHAR(255) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- name — VARCHAR(100) — NOT NULL
- phone — VARCHAR(30)
- role — ENUM( 'PLATFORM_ADMIN', 'EXPO_ADMIN', 'EXHIBITOR', 'VISITOR', 'ACCOUNTANT', 'STAFF' ) — NOT NULL
- deleted_at — DATETIME — 논리 삭제 시각
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =사용자 — 계정(전 역할 공통 Soft Delete
visit_dwell
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- attendee_id — BIGINT — NOT NULL
- scan_point_type — ENUM('BOOTH', 'SESSION') — NOT NULL
- scan_point_id — BIGINT — NOT NULL
- entry_at — DATETIME — NOT NULL
- exit_at — DATETIME — 해당 버킷 평균 (=sum/count, 파생 편의값 전용)
- dwell_seconds — INT — NOT NULL — 테넌트 스코프 (파생, FK 없음)
- head_count — INT — NOT NULL — KST 기준
- is_estimated — TINYINT(1) — NOT NULL — 0-23, KST
- close_reason — ENUM( 'NORMAL_EXIT', 'NEXT_ENTRY_AUTO', 'TIMEOUT_AUTO', 'ADMIN_MANUAL' ) — 해당 시간대 최대 동시 입장 인원
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =체류시간 — 집계(배치 close_reason으로 자동/수동 마감 구분
visit_log
- id — BIGINT — Primary key — NOT NULL
- exhibition_id — BIGINT — Foreign key → exhibition.id — NOT NULL
- attendee_id — BIGINT — Foreign key → reservation_attendee.id — NOT NULL — 집계 날짜 (KST 기준)
- name_tag_id — BIGINT — Foreign key → name_tag.id — NOT NULL
- scan_point_type — ENUM('BOOTH', 'SESSION', 'GATE') — NOT NULL
- scan_point_id — BIGINT — 집계 시 (0-23, KST)
- scan_type — ENUM('ENTRY', 'EXIT') — NOT NULL
- head_count — INT — NOT NULL — 스캔/ENTRY 건수
- scanned_by_user_id — BIGINT — head_count 합 = 인원 (GROUP 가중)
- is_manual — TINYINT(1) — NOT NULL — distinct attendee = 태그 수 (GROUP 대표 1)
- is_auto_exit — TINYINT(1) — NOT NULL — 마감된 체류시간 합 (상위 구간 평균 분자)
- scanned_at — DATETIME — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- =스캔 — 로그(1스캔=1행 ENTRY/EXIT 토글, 수기/자동EXIT 구분