# 限時取餐 Meal Time - 資料庫欄位表

[資料庫結構](https://dbdiagram.io/d/61333a55825b5b0146f2c91c)

Table "users" {
  "id" int [pk]
  "username" varchar(64)
  "password" varchar(64)
  "nickname" varchar(64)
  "email" varchar(64)
  "phone" varchar(16)
  "role" enum['all', 'member', 'vendor', 'admin', 'suspended']
  "vendorId" int
  "createdAt" datetime
  "updatedAt" datetime
}

users

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|username|varchar|64|Y|||
|password|varchar|64|Y||需經過 hash|
|nickname|varchar|64|Y|||
|email|varchar|64|Y|||
|phone|varchar|16|Y|||
|role|enum('member', 'vendor', 'admin', 'suspended')||Y|member||
|avatarURL|varchar|64||||
|vendorId|int|||null|"vendor"."id"|
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

***

Table "vendors" {
  "id" int [pk]
  "userId" int
  "vendorName" varchar(64)
  "address" varchar(128)
  "position" point
  "phone" varchar(16)
  "avatarUrl" varchar(64)
  "bannerUrl" varchar(64)
  "categoryId" int
  "description" text
  "openingHour" text
  "isOpen" tinyint
  "isSuspended" tinyint
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|userId|integer|64|Y||"users"."id"|
|vendorName|varchar|64|Y|||
|address|varchar|128|Y|||
|position|point||Y|||
|phone|varchar|16|Y|||
|avatarUrl|varchar|64||||
|bannerUrl|varchar|64||||
|categoryId|int||Y|1|"vendor_categories"."id"|
|description|text|||||
|openingHour|text||Y||JSON 格式*|
|isSuspended|boolean||Y|false||
|isOpen|boolean||Y|true||
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

\* `openingHour` 格式：

[{
  Monday: {
    isOpen,
    startTime,
    endTime
  },
  Tuesday: {
    isOpen,
    startTime,
    endTime
  },
  Wednesday: {
    isOpen,
    startTime,
    endTime
  },
  .
  .
  .
  
  Sunday: {
    isOpen,
    startTime,
    endTime
  },
}]

***

Table "vendor_categories" {
  "id" int [pk]
  "name" varchar(64)
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|name|varchar|64|Y||id: 1 為未分類|
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

***

Table "products" {
  "id" int [pk]
  "vendorId" int
  "name" varchar(64)
  "categoryId" int
  "pictureUrl" varchar(64)
  "price" int
  "quantity" int
  "manufactureDate" date
  "expiryDate" date
  "description" text
  "isAvailable" tinyint
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|vendorId|int||Y||"vendors"."id"|
|name|varchar|64|Y|||
|categoryId|int||Y|1|"product_categories"."id"|
|pictureUrl|varchar|64||||
|price|int||Y|||
|quantity|int||Y|||
|manufactureDate|date||||前端提交資料時需確認是否至少擇一填寫|
|expiryDate|date||||前端提交資料時需確認是否至少擇一填寫|
|description|text||Y|||
|isAvailable|boolean||Y|true||
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

***

Table "product_categories" {
  "id" int [pk]
  "name" varchar(64)
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|name|varchar|64|Y||id: 1 為未分類|
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

***

Table "orders" {
  "id" int [pk]
  "orderNumber" varchar(16)
  "vendorId" int
  "clientId" int
  "totalQuantity" int
  "totalPrice" int
  "pickupTime" datetime
  "remarks" text
  "isPaid" tinyint
  "isCompleted" tinyint
  "isCanceledByVendor" tinyint
  "isCanceledByClient" tinyint
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|orderNumber|varchar(16)||Y||每張訂單自己的流水編號|
|vendorId|int||Y||"vendors"."id"|
|clientId|int||Y||"users"."id"|
|totalQuantity|int||Y|||
|totalPrice|int||Y|||
|pickupTime|datetime||Y|||
|remarks|text||Y||訂單備註|
|isPaid|boolean||Y|false||
|isCompleted|boolean||Y|false||
|isCanceledByVendor|boolean||Y|false||
|isCanceledByClient|boolean||Y|false||
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

***

Table "order_items" {
  "id" int [pk]
  "productId" int
  "orderId" int
  "quantity" int
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|productId|int||Y||"products"."id"|
|orderId|int||Y||"orders"."id"|
|quantity|int||Y|||
|totalPrice|int||Y|||
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

***

Table "faq" {
  "id" int [pk]
  "question" text
  "categoryId" int
  "answer" text
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|question|text||Y|||
|categoryId|int||Y|1|"faq_categories"."id"|
|answer|text||Y|||
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

***

Table "faq_categories" {
  "id" int [pk]
  "name" varchar(64)
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|name|varchar|64|Y||id: 1 為未分類|
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

***

Table "report_messages" {
  "id" int
  "userId" int
  "reportedVendorId" int
  "reportedProductId" int
  "content" text
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|userId|int||Y||"users"."id"|
|reportedVendorId|int||Y||"vendors"."id"|
|reportedProductId|int||||"products"."id"|
|content|text||Y|||
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

***

Table "messages" {
  "id" int
  "clientId" int
  "vendorId" int
  "content" text
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|clientId|int||Y||"users"."id"|
|vendorId|int||Y||"vendors"."id"|
|content|text||Y|||
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||

***

Table "messages_to_admin" {
  "id" int
  "userId" int
  "content" text
  "createdAt" datetime
  "updatedAt" datetime
}

|欄位|型態|長度|必填|預設|備註|
|-|-|-|-|-|-|
|id|int||Y|A.I.||
|userId|int||Y||"users"."id"|
|content|text||Y|||
|createdAt|datetime||Y|CURRENTTIME||
|updatedAt|datetime||Y|CURRENTTIME||