cafe
Tables (15)
admin
- admin_id — BIGINT — Primary key — NOT NULL
- username — VARCHAR(50) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- created_at — TIMESTAMP — NOT NULL
cart
- cart_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → user.user_id — NOT NULL
- menu_id — BIGINT — Foreign key → menu.menu_id — NOT NULL
- quantity — INT — NOT NULL
- added_at — TIMESTAMP — NOT NULL
cart_option
- cart_option_id — BIGINT — Primary key — NOT NULL
- cart_id — BIGINT — Foreign key → cart.cart_id — NOT NULL
- option_item_id — BIGINT — Foreign key → option_item.option_item_id — NOT NULL
- quantity — INT — NOT NULL
category
- category_id — BIGINT — Primary key — NOT NULL
- name — VARCHAR(50) — NOT NULL
- display_order — INT — NOT NULL
- created_at — TIMESTAMP — NOT NULL
coupon_log
- log_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → user.user_id — NOT NULL
- stamp_log_id — BIGINT — Foreign key → stamp_log.log_id
- change_type — ENUM('issue','use') — NOT NULL
- change_amount — INT — NOT NULL
- balance_after — INT — NOT NULL
- processed_at — TIMESTAMP — NOT NULL
menu
- menu_id — BIGINT — Primary key — NOT NULL
- category_id — BIGINT — Foreign key → category.category_id — NOT NULL
- name — VARCHAR(100) — NOT NULL
- price — INT — NOT NULL
- image_url — VARCHAR(500)
- status — ENUM('sale','sold_out') — NOT NULL
- display_order — INT — NOT NULL
- created_at — TIMESTAMP — NOT NULL
option_group
- option_group_id — BIGINT — Primary key — NOT NULL
- menu_id — BIGINT — Foreign key → menu.menu_id — NOT NULL
- title — VARCHAR(100) — NOT NULL
- select_type — ENUM('single','multi','quantity') — NOT NULL
- is_required — TINYINT(1) — NOT NULL
- display_order — INT — NOT NULL
option_item
- option_item_id — BIGINT — Primary key — NOT NULL
- option_group_id — BIGINT — Foreign key → option_group.option_group_id — NOT NULL
- name — VARCHAR(100) — NOT NULL
- extra_price — INT — NOT NULL
- max_quantity — INT
- display_order — INT — NOT NULL
order
- order_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → user.user_id — NOT NULL
- order_number — VARCHAR(36) — NOT NULL
- order_type — ENUM('dine_in','pickup') — NOT NULL
- status — ENUM('pending','accepted','completed', 'rejected','cancelled','picked_up') — NOT NULL
- total_price — INT — NOT NULL
- reject_reason — VARCHAR(255)
- ordered_at — TIMESTAMP — NOT NULL
order_item
- order_item_id — BIGINT — Primary key — NOT NULL
- order_id — BIGINT — Foreign key → order.order_id — NOT NULL
- menu_id — BIGINT — Foreign key → menu.menu_id — NOT NULL
- menu_name_snapshot — VARCHAR(100) — NOT NULL
- unit_price_snapshot — INT — NOT NULL
- quantity — INT — NOT NULL
order_item_option
- order_item_option_id — BIGINT — Primary key — NOT NULL
- order_item_id — BIGINT — Foreign key → order_item.order_item_id — NOT NULL
- option_item_id — BIGINT — Foreign key → option_item.option_item_id — NOT NULL
- option_group_name_snapshot — VARCHAR(100) — NOT NULL
- option_name_snapshot — VARCHAR(100) — NOT NULL
- option_price_snapshot — INT — NOT NULL
- quantity — INT — NOT NULL
order_status_log
- log_id — BIGINT — Primary key — NOT NULL
- order_id — BIGINT — Foreign key → order.order_id — NOT NULL
- prev_status — ENUM('pending','accepted','completed', 'rejected','cancelled','picked_up')
- next_status — ENUM('pending','accepted','completed', 'rejected','cancelled','picked_up') — NOT NULL
- reject_reason — VARCHAR(255)
- changed_at — TIMESTAMP — NOT NULL
prepaid_log
- log_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → user.user_id — NOT NULL
- change_type — ENUM('charge','use') — NOT NULL
- change_amount — INT — NOT NULL
- balance_after — INT — NOT NULL
- processed_at — TIMESTAMP — NOT NULL
stamp_log
- log_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → user.user_id — NOT NULL
- order_id — BIGINT — Foreign key → order.order_id
- change_type — ENUM('earn','deduct') — NOT NULL
- change_amount — INT — NOT NULL
- balance_after — INT — NOT NULL
- processed_by — ENUM('staff','system') — NOT NULL
- processed_at — TIMESTAMP — NOT NULL
user
- user_id — BIGINT — Primary key — NOT NULL
- phone_number — VARCHAR(11) — NOT NULL
- status — ENUM('normal','blacklist') — NOT NULL
- stamp_count — INT — NOT NULL
- coupon_count — INT — NOT NULL
- prepaid_balance — INT — NOT NULL
- created_at — TIMESTAMP — NOT NULL