// 회원 
Table p_user {
  id bigint [pk]
  username varchar(100) [not null, unique]
  nickname varchar(100)
  email varchar(255) [not null, unique]
  password varchar(255)
  role_id varchar(50) [ref: > p_role_type.id] 
  is_public boolean 

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
} 

// 권한
Table p_role_type {
  id varchar(50) [pk] // UUID 
  name varchar(50) [not null, unique]
  description TEXT
}

// 지역 
Table p_area {
  id varchar(50) [pk] // UUID 
  // 시 
  city varchar(50) 
  // 구 
  district varchar(50) 
  // 로 
  street varchar(50)

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
}

// 가게 
Table p_store {
  id varchar(50) [pk] // UUID 

  name varchar(100)
  // area
  area_id varchar(100) [ref: > p_area.id] // 시울시 ~ xx 로 

  // 상세주소 
  detail_address varchar(200)  // xx아파트 상가 1층 105호 

  // 사장 정보 
  owner_name varchar(100) 

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
}

// AI 요청 기록 
Table p_ai_request {
  id varchar(50) [pk] // UUID 

  // 질문 
  question TEXT [not null]

  // 응답 
  answer TEXT 

  // 가게 정보 
  store_id varchar(50)

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
}

// 메뉴 
Table p_menu {
  id varchar(50) [pk] // UUID 
  // 메뉴 이름 
  name varchar(100) [not null]
  // 메뉴 설명 
  description TEXT
  // 가격 
  price int 
  // 노출상태 (판매중, 하루품절, 숨김)
  expose_status varchar(30) [not null, default: "ONSALE"]

  // 가게 정보 
  store_id varchar(50) [not null]

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
}

Table p_image {
  id varchar(50) [pk] // UUID 
  // 노출 순서 
  sequence int 
  
  s3_url varchar(200)
  
  file_name varchar(100) [not null]
  
  // menu 
  menu_id varchar(50) [ref: > p_menu.id]

  // option 
  menu_option_id varchar(50) [ref: > p_menu_option.id]

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
}

// 옵션
Table p_menu_option {
  id varchar(50) [pk] // UUID 
  // 옵션 이름 
  name varchar(100) [not null]
  // 옵션 설명 
  description TEXT
  // 옵션 가격 
  price int 
  // 노출상태 (판매중, 하루품절, 숨김)
  expose_status varchar(30) [not null, default: "ONSALE"]

  // 메뉴 
  menu_id varchar(50)  [not null, ref: > p_menu.id]

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
}

// menuOrder : 매핑테이블 + 수량 
Table p_menu_order{
  id bigint [pk, increment]

  // 메뉴 
  menu_id varchar(50)
  menu_name varchar(100)
  count int 
  price int 

  // 주문 
  order_id varchar(50) [ref: > p_order.id]
}

// menuOptionOrder : 매핑테이블 + 수량 
Table p_menu_option_menu_order {
  id bigint [pk, increment]

  menu_option_id varchar(50)
  menu_option_price int 
  menu_option_name varchar(100)
  menu_option_count int 

  menu_order_id bigint [ref: > p_menu_order.id]
  
}

// 주문 
Table p_order {
  id varchar(50) [pk] // UUID 

  store_id varchar(50)
  store_name varchar(100) // 가게 이름 

  // 주문 상태 
  order_status varchar(50) [not null, default: "WAIT"]

  // 전체 주문 금액 : 반정규화 
  total_price int 
  // 주문자 
  customer_name varchar(50) 
  // 주문 유형 : 배송 or 현장 
  order_type varchar(50) [not null, default: "INSTORE"]

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
}

// 배달 (배송지정보)
Table p_delivery { 
  id varchar(50) [pk] // UUID 

  // 주문 
  order_id varchar(50)
  // 배송지 
  address varchar(100)  
  // 배송 상태 : START, END 
  status varchar(50) [not null]

  customer_name varchar(50)

  created_at timestamp 
  created_by bigint
  updated_at timestamp
  updated_by bigint
  deleted_at timestamp
  deleted_by bigint
}

// 결제 
Table p_payment {
  id varchar(50) [pk] // UUID 

  // 주문 
  order_id varchar(50)

  // 결제 주체 
  payment_agent_id varchar(50) [ref: > p_payment_agent.id]

  // 결제사측 결제 ID
  payment_pid varchar(200) [not null, unique]

  // 결제 상태 : SUCCESS, CANCEL
  status varchar(20)

  customer_name varchar(50)

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
}

Table p_payment_agent {
  id varchar(50) [pk] // UUID 
  name varchar(100) [not null, unique]
}

// 카테고리 
Table p_category {
  id varchar(50) [pk] // UUID 
  name varchar(100) [not null, unique]

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
}

// 매핑 테이블 
Table p_category_store {
  id bigint [pk, increment]
  category_id varchar(50) [ref: > p_category.id]
  store_id varchar(50) [ref: > p_store.id]
}

// 리뷰 
Table p_review {

  id varchar(50) [pk]

  // 작성자 이름 
  writer_id bigint 

  store_id varchar(50)
  store_name varchar(100)
  
  // 주문 정보 
  order_id varchar(50)
  // 별점 
  rate int 
  // 리뷰 내용 
  contents text

  created_at timestamp 
  created_by bigint 
  updated_at timestamp
  updated_by bigint 
  deleted_at timestamp
  deleted_by bigint 
}

수정 사항

• user -> role   p_role_type.role_id 수정
• p_store -> owner_id -> owner_name 수정
• p_ai_request -> request [not null] 추가
• p_menu -> name [not null] 추가,
• expose_status [not null], default(ONSALE) 추가,
• store_id not null 추가
• p_image -> file_name varchar(100) [not null] 추가,
• url -> s3_url 수정
• p_menu_option -> name [not null] 추가,
  • expose_stauts [not null], default(ONSALE) 추가,
  • menu_id [not null] 추가
• p_order -> customer_id -> customer_name  varchar(50) 수정,
• order_status [not null], default(WAIT) 추가,
• order_type [not null], default(INSTORE) 추가,
• p_delivery -> customer_name  varchar(50)
• status  [not null], default(START) 필요하지 않을까요? → 배송기능 미구현으로 인해 default 미지정
• p_payment -> p_customer_name varchar(50) 추가,