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 구분