# Sql create tables

## Relational database

CREATE TABLE `users` (
  `UID` int(10) UNSIGNED NOT NULL,
  `account` varchar(256) NOT NULL,
  `password` varchar(256) NOT NULL,
  `name` varchar(256) NOT NULL,
  `identity` varchar(256) NOT NULL,
  `address` varchar(256) NOT NULL,
  `age` int(10) UNSIGNED NOT NULL,
  `sex` int(10) UNSIGNED NOT NULL,
  `phone_number` char(10) NOT NULL,
  `post_reported_counts` int(10) UNSIGNED NOT NULL,
  `influencer` int(10) UNSIGNED NOT NULL, -- 可以發文的身份
  -- 頭像(BLOD, sql server)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `shops` (
  `SID` int(10) UNSIGNED NOT NULL,
  `account` varchar(256) NOT NULL,
  `password` varchar(256) NOT NULL,
  `name` varchar(256) NOT NULL,
  `address` varchar(256) NOT NULL,
  `phone_number` char(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `consumption_preference` (
  `CID` int(10) UNSIGNED NOT NULL,
  `UID` int(10) UNSIGNED NOT NULL,
  `KID` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `shop_categories` (
  `KID` int(10) UNSIGNED NOT NULL,
  `SID` int(10) UNSIGNED NOT NULL,
  `category` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `product` (
  `PID` int(10) UNSIGNED NOT NULL,
  `SID` int(10) UNSIGNED NOT NULL,
  `shop_name` varchar(256) NOT NULL,
  `shop_category` varchar(256) NOT NULL,
  `product_name` varchar(256) NOT NULL,
  `price` int(10) UNSIGNED NOT NULL,
  `image` mediumblob NOT NULL
-- `picture_type` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `order` (
  `OID` int(10) UNSIGNED NOT NULL,
  `UID` int(10) UNSIGNED NOT NULL,
  `SID` int(10) UNSIGNED NOT NULL,
  `CouponID` int(10) UNSIGNED NULL, 
  `time` varchar(256) NOT NULL,
  `type` varchar(256) NOT NULL,
  `amount` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `order_details` (
  `ODID` int(10) UNSIGNED NOT NULL,
  `OID` int(10) UNSIGNED NOT NULL,
  `PID` int(10) UNSIGNED NOT NULL,
  `quantity` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--CREATE TABLE `transaction` (
-- `TID` int(10) UNSIGNED NOT NULL,
--) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `coupons` (
  `CouponID` int(10) UNSIGNED NOT NULL,
  `UID` int(10) UNSIGNED NOT NULL,
  `SID` int(10) UNSIGNED NOT NULL,
  `status` int(10) UNSIGNED NOT NULL,
  `content` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 2022/07/11 add
CREATE TABLE `invite_codes` (
  `IID` int(10) UNSIGNED NOT NULL,
  `UID_influencer` int(10) UNSIGNED NOT NULL,
  `UID_normal_user` int(10) UNSIGNED NOT NULL, -- 誰可以用這個邀請碼
  -- `SID` int(10) UNSIGNED NOT NULL,
  -- `status` int(10) UNSIGNED NOT NULL,
  `content` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

## Non-Relational database
- 貼文 posts
- PostID
- influencer_UID
- basic_content (array)(?) {衛生、燈光...}
- other_content (array)
- status {normal/deleted/reported/archived}
- evaluate_to_shop {1 to 5 stars}
- num_of_likes
- num_of_saved
- time
- location
- comment (comment object)
- list_of_people_who_saved (array)
- picture (array)
- video (array)

# server組 備忘錄
用 post_ID request
回傳貼文的所有資料

下禮拜三 每組報告完整的頁面 盡量全部做完
之後進度一人一頁
八月中 python 全弄完
九月初轉到手機

貼文分數：用演算法根據每個人，算出各自的貼文分數

07/11
邀請碼 table
邀請碼 網紅ID UID 折扣內容（不一定有）
// 邀請碼跟使用者折扣有關係，使用者出示 QRCode 等抽獎。

###
net/https: go package
Jason 格式

### homepage
（首頁右上角）登入後抓頭貼跟點數餘額

### 貼文兩個前後端串接
下拉是選單 最近吃過得店家（存在server）可以從消費紀錄看
發文功能 前端送資料過來 我們把他存在 DB 再回傳要 show 的資料回去前端

推薦貼文
1. 追蹤者
2. 按照 發文者地理位置

#### 後期改進：
前端可以存 cache，有些會重複用到的資料，像是照片，可以第一次 request 之後就存在使用者端，節省之後 request 的時間。
更細節的話，回傳可以分成 cacheable 或 noncacheable（應該不用做那麼細，前端想存就存，有存就用，沒存就跟 server 要）

#### 資安問題：登入認證 authentication
不能每個人瀏覽器敲一下，就亂新增優惠ㄟ

#### HTTP authentication

HTTP defines some authentication schemes that you can use directly when you are implementing REST API. The following are two of these schemes:

##### Basic authentication

In basic authentication, the client sends the user name and password in the request header. It encodes them with base64, which is an encoding technique that converts the pair into a set of 64 characters for safe transmission.

##### Bearer authentication

The term bearer authentication refers to the process of giving access control to the token bearer. The bearer token is typically an encrypted string of characters that the server generates in response to a login request. The client sends the token in the request headers to access resources.

[reference](https://aws.amazon.com/what-is/restful-api/?nc1=h_ls)