Project1

Tables (15)

ai_review_summaries

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • summary — TEXT — NOT NULL
  • review_count — INT — NOT NULL
  • generated_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

cart_items

  • id — BIGINT — Primary key — NOT NULL
  • cart_id — BIGINT — Foreign key → carts.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • quantity — INT — NOT NULL
  • price_snapshot — DECIMAL(10,0) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

carts

  • id — BIGINT — Primary key — NOT NULL
  • buyer_id — BIGINT — Foreign key → users.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

chatbot_faqs

  • id — BIGINT — Primary key — NOT NULL
  • question — VARCHAR(500) — NOT NULL
  • answer — TEXT — NOT NULL
  • created_by — BIGINT — Foreign key → users.id — NOT NULL
  • is_deleted — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inventory

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • stock_quantity — INT — NOT NULL
  • updated_at — DATETIME — NOT NULL

notifications

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • type — ENUM( 'expiry_discount', 'price_change', 'order_status', 'etc' ) — NOT NULL
  • message — VARCHAR(500) — NOT NULL
  • is_read — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

order_items

  • id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • seller_id — BIGINT — Foreign key → users.id — NOT NULL
  • quantity — INT — NOT NULL
  • price — DECIMAL(10,0) — NOT NULL
  • shipping_fee — DECIMAL(10,0) — NOT NULL
  • status — ENUM( 'payment_complete', 'shipping', 'delivered', 'confirmed', 'cancelled', 'refund_requested', 'refunded' ) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

orders

  • id — BIGINT — Primary key — NOT NULL
  • buyer_id — BIGINT — Foreign key → users.id — NOT NULL
  • total_amount — DECIMAL(10,0) — NOT NULL
  • total_shipping_fee — DECIMAL(10,0) — NOT NULL
  • payment_method — ENUM('card','bank_transfer','simple_payment') — NOT NULL
  • delivery_type — ENUM('delivery','pickup') — NOT NULL
  • pickup_location_id — BIGINT — Foreign key → pickup_locations.id
  • status — ENUM( 'payment_complete', 'shipping', 'delivered', 'confirmed', 'cancelled', 'refund_requested', 'refunded' ) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

pickup_locations

  • id — BIGINT — Primary key — NOT NULL
  • seller_id — BIGINT — Foreign key → users.id — NOT NULL
  • address — VARCHAR(500) — NOT NULL
  • latitude — DECIMAL(10,7)
  • longitude — DECIMAL(10,7)
  • operating_hours — VARCHAR(200)
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

posts

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id
  • title — VARCHAR(300) — NOT NULL
  • content — TEXT — NOT NULL
  • is_notice — BOOLEAN — NOT NULL
  • is_deleted — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

products

  • id — BIGINT — Primary key — NOT NULL
  • seller_id — BIGINT — Foreign key → users.id — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • description — TEXT
  • price — DECIMAL(10,0) — NOT NULL
  • origin — VARCHAR(200) — NOT NULL
  • expiry_date — DATE — NOT NULL
  • condition_photo_url — VARCHAR(500)
  • status — ENUM('active','hidden','cancelled') — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

reviews

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • order_item_id — BIGINT — Foreign key → order_items.id — NOT NULL
  • rating — TINYINT — NOT NULL
  • content — TEXT — NOT NULL
  • is_deleted — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

sales_rankings

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • seller_id — BIGINT — Foreign key → users.id — NOT NULL
  • period_type — ENUM('daily','weekly') — NOT NULL
  • period_date — DATE — NOT NULL
  • sales_count — INT — NOT NULL
  • sales_amount — DECIMAL(12,0) — NOT NULL
  • rank — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

seller_profiles

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • business_number — VARCHAR(20) — NOT NULL
  • bank_account — VARCHAR(100) — NOT NULL
  • farm_name — VARCHAR(200)
  • farm_location — VARCHAR(500)
  • farm_description — TEXT
  • farm_photo_url — VARCHAR(500)
  • farm_philosophy — TEXT
  • rating — DECIMAL(3,2) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

users

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • address — VARCHAR(500) — NOT NULL
  • phone — VARCHAR(20)
  • role — ENUM('buyer','seller','admin') — NOT NULL
  • push_agree — BOOLEAN — NOT NULL
  • email_agree — BOOLEAN — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL