aaronangxz/carouhell

AY21/22 NUS TIC2601 Web App Project

carouhell's Issues

[BUG#2] CreateListing validations error handling returns duplicated RespMeta

[DATABASE#10] No UpdateTime field in listing_tab

Might need to add listing_mtime to store updated time when update_single_listing is called

[FEATURE#16] Typo in GetLatestListingsResponse struct

ItemPamentInfo should be ItemPaymentInfo

type GetLatestListingsResponse struct {
	ItemID                uint32
	ItemName              string
	ItemPrice             uint32
	ItemQuantity          uint32
	ItemPurchasedQuantity uint32
	ItemDescription       string
	ItemShippingInfo      uint32
	ItemPamentInfo        uint32
	ItemLocation          string
	ItemStatus            uint32
	ItemCategory          uint32
	ItemImage             string
	SellerID              uint32
	ListingCtime          uint32
}

[BUG#3] CreateListing - misleading RespMeta error message

[API #3] GetListingUsingSearchTerm

[FEATURE #1] Add input validations

• Check all input parameters
• Check if required fields are nill
• Check for invalid input - empty/negative/exceeds length etc

[DATABASE#4] Unable to establish connection after deploying to Heroku

[RELEASE] v1.6

Release Checklist

• Merge test into master (#50)
• Final test against master locally
• Build docker image
• Deploy

[FEATURE#9] GetListingByItemID returns DB error 1103: Incorrect table name

SQL query seems to be ok, to investigate

[CORE#2] Listing Page

Front end

Elements

No.	Description	Type	Status	Remarks

Issues

Back End

APIs

No.	API name	Type	Status	Remarks
1	/get_single_listing_by_itemid	POST	✅
2	/update_single_listing	PATCH	✅
3	/delete_single_listing	DELETE	✅
4	/get_listing_reactions	POST	✅
5	/add_listing_likes	POST	✅
6	/purchase_single_item	POST	✅

In Progress

• #68
• #65
• #63

Issues

• #102
• #103
• #104

[FEATURE#14] GetListingsUsingFilters crashes when search_keyword / sort_flag is empty

To include validations for these 2 fields

[DATABASE #2] Implement MySQL into Docker container

Currently, we are implementing static SQLite for ease of debugging. However, to fully utilize all the SQL features and scalability MySQL is needed.

To assess feasibility:
Plan 1: Integrate into the same docker container.
Plan 2: Deploy a separate MySQL container.

Progess:

1. Implemented MySQL in the same docker container
2. PHPMyAdmin for SQL GUI

[API#12] GetUserWalletDetails

Use Case

• Wallet page

Request

user_id

Response

type GetUserWalletResponse struct {
	WalletID      *uint32 `json:"wallet_id"`
	UserID        *uint32 `json:"user_id"`
	WalletBalance *uint32 `json:"wallet_balance"`
	WalletStatus  *uint32 `json:"wallet_status"`
	LastTopUp     *int64  `json:"last_topup"`
	LastUsed      *int64  `json:"last_used"`
}

DB

• wallet_tab

SELECT * FROM wallet_tab WHERE user_id = ?

[API#13] GetUserDetails - Refactor

Update:

get_user_details = get_user_listings + get_user_ratings

Use Case

• User Profile page

Request

user_id

Response

type GetUserListingsResponse struct {
	LItemID               uint32 `json:"item_id"`
	ItemName              string `json:"item_name"`
	ItemPrice             uint32 `json:"item_price"`
	ItemQuantity          uint32 `json:"item_quantity"`
	ItemPurchasedQuantity uint32 `json:"item_purchasedquantity"`
	ItemDescription       string `json:"item_description"`
	ItemLocation          uint32 `json:"item_location"`
	ItemStatus            uint32 `json:"item_status"`
	ItemCategory          uint32 `json:"item_category"`
	ItemImage             string `json:"item_image"`
	LSellerID             uint32 `json:"seller_id"`
	SellerName            string `json:"seller_name"`
	ListingCtime          int64  `json:"listing_ctime"`
	ListingMtime          int64  `json:"listing_mtime"`
	ListingLikes          uint32 `json:"listing_likes"`
}

type UserReview struct {
	RVUserID   *uint32 `json:"user_id"`
	RVSellerID *uint32 `json:"seller_id"`
	Ratings    *uint32 `json:"ratings"`
	ReviewText *string `json:"review_text"`
	Ctime      *int64  `json:"ctime"`
}

type GetUserDetailsResponse struct {
	AUserID       uint32                    `json:"user_id"`
	UserName      string                    `json:"user_name"`
	UserEmail     string                    `json:"user_email"`
	UserCtime     int64                     `json:"user_ctime"`
	UserStatus    uint32                    `json:"user_status"`
	UserImage     string                    `json:"user_image"`
	UserLastLogin int64                     `json:"user_last_login"`
	ReviewCount   uint32                    `json:"review_count"`
	UserReviews   []UserReview              `json:"user_review"`
	ListingCount  uint32                    `json:"listing_count"`
	UserListings  []GetUserListingsResponse `json:"user_listings"`
}

DB

• user_review_tab
• acc_tab
• listing_tab

Find reviews left for user_id and the reviewer's information
Find listings posted by user_id

//IF EXISTS user_id

//SELECT * FROM listing_tab WHERE seller_id = user_id

//SELECT * FROM user_review_tab WHERE rv_seller_id = user_id

[FEATURE#15] Change GetLatestListings to POST method

r.GET("/get_latest_listings", listings.GetLatestListings)

[CORE#1] Home Page

Front end

Elements

No.	Description	Type	Status	Remarks
1	Display listings	API call	🛠️
2	Create listings	API call	🛠️

Issues

Back End

APIs

No.	API name	Type	Status	Remarks
1	/create_listing	POST	✅
2	/get_all_listings	GET	✅
3	/get_latest_listings	POST	✅
4	/get_listings_using_filters	POST	✅

API Progress

• #25
• #27

Issues

• #91
• #90
• #60
• #92
• #85

[API#8] AddListingComment

[FEATURE#10] Integrate Redis Cache

Usage

Batch get listings query

1. First query, read from DB, and retrieve the whole list of listings
2. Write marshaled list (is it too large?) to Redis with key latest_listing with expiry time of 60 secs
3. On next query, check Redis first and return if key exists
4. Else read from DB and repeat 2

Invalidation: Delete key when there is any write to listing_tab

Single listing query

1. First query, read from DB, and retrieve struct of listing
2. Write marshaled struct to Redis with key <API name>:<item_id> with expiry time of 60 secs
3. On next query, check Redis first and return if key exists
4. Else read from DB and repeat 2

Invalidation: Delete key when there is any write to this item_id

APIs

API	KEY	Expiry
get_single_listing_by_itemid	get_single_listing_by_itemid:<item_id>	60s
get_user_wallet_details	get_user_wallet_details:<user_id>	60s

[API#6] GetUserDetails

Use Case

• User Profile page

Request

user_id

Response

//everything from acc_tab
//compute and return listing count?

DB

• acc_tab
• listing_tab

SELECT *  FROM acc_tab WHERE user_id = ?
SELECT COUNT(*) FROM listing_tab WHERE user_id = ?

[API#11] GetUserSavedListings

Use Case

• Liked page

Request

user_id

Response

//listing struct

DB

• reaction_tab (Store both comment and likes)
• listing_tab

user_id
item_id
reaction_type // 0 = like, 1 = comment
comment //null if reaction_type = 0
ctime

Find a list of item_id which is reaction_type = 0, join listing_tab to get listing details

SELECT * FROM listing_tab l WHERE l.item_id IN
(SELECT r.item_id FROM reaction_tab r
WHERE r.user_id = ? AND r.reaction_type = 0 
ORDER BY r.ctime DESC)

[API #2] GetLatestListings

Get the latest listings according to category / status (optional)

{
    "item_category":"",
    "item_status":"",
    "listing_time":"",
    "limit":
}

[FEATURE#8] UpdateSingleListing Request does not match DB schema

Update Request params to align with current listing_tab schema

[FEATURE#13] UpdateSingleListing returns duplicated RespMeta

Due to params validations not being handled properly.

[RELEASE v1.3]

Changes:

Issue	Description	Type
#32	[BUG#2] Header URL redirects to /index.html	Bug
#31	[DATABASE#1] Update Request and Response Structs to uint32	Enhancement
#34	[API#2] Add structs and methods for GetLatestListings	New Feature

[RELEASE] v1.8

Release Checklist

• Merge test into master ( #72 )
• Final test against master locally
• Build docker image
• Deploy

Changes:

Issue	Description	Type	Status
#40	[FEATURE#5] Add structs and Getters according to DB schema	Core Feature	WIP
#70	[API#4] GetListingUsingFilters	API	OK
#74	[API#4] GetListingUsingFilters - Adding New Filters	Core Feature	OK
#77	[FEATURE#13] UpdateSingleListing BugFix	Bug Fix	OK
#76	[FEATURE#14] GetListingsUsingFilters crashes when search_keyword / sort_flag is empty	Bug Fix	OK

[DATABASE #3] Query statements for features

We can decide the different queries needed for each feature, according to those in the Google Sheet.

[API #4] GetListingUsingFilters

[FEATURE#6] Modularize validations in each APIs

Currently, all validations are being done in the main function, making the code very long.
TODO: Modularize into smaller sub-functions to improve readability.

[RELEASE] v1.9

Release Checklist

• Merge test into master (#84 )
• Final test against master locally
• Build docker image
• Deploy

[FEATURE #5] Add structs and Getters according to DB schema

[FEATURE#12] Return verbose logs and error handling

[FEATURE #4] Update UpdateListing struct

Bottom Nav Bar - selections not highlighted

Current Behaviour
Selections not highlighted

Expected Behaviour
Should be highlighted according to CSS

[API#7] AddListingLikes

[FEATURE#7] GetAllListings does not return all fields

[RELEASE] v1.7

Release Checklist

• Merge test into master (#61 )
• Final test against master locally
• Build docker image
• Deploy

[FEATURE #3] Refactor API methods

Currently, all methods exist in the same go file and this could be a problem when the program becomes big.
To refactor into individual files for better maintainability.

Timeline:
After closing #34, #29

[BUG#1] Decode base64 image and store on S3

Flow:
FE encode into base64 -> Pass via create_voucher -> BE decode into jpg -> store -> generate jpg URL string

Temp Data fix:
Modify all existing jpeg records to jpg

[DATABASE#11] Adding Secret Question field in acc_tab for user registration

@nleo7826 @xkerris @pehqingwen @li3nl

[FEATURE#13] Listings: Return seller_name instead of seller_id

Currently, we are returning seller_id in our listing object:

type Listing struct {
	ItemID                *uint32 `json:"item_id" gorm:"primary_key"`
	ItemName              *string `json:"item_name"`
	ItemPrice             *uint32 `json:"item_price"`
	ItemQuantity          *uint32 `json:"item_quantity"`
	ItemPurchasedQuantity *uint32 `json:"item_purchasedquantity"`
	ItemDescription       *string `json:"item_description"`
	ItemShippingInfo      *uint32 `json:"item_shippinginfo"`
	ItemPaymentInfo       *uint32 `json:"item_paymentinfo"`
	ItemLocation          *string `json:"item_location"`
	ItemStatus            *uint32 `json:"item_status"`
	ItemCategory          *uint32 `json:"item_category"`
	ItemImage             *string `json:"item_image"`
	SellerID              *uint32 `json:"seller_id"`
	ListingCtime          *int64  `json:"listing_ctime"`
	ListingMtime          *int64  `json:"listing_mtime"`
	ListingLikes          *uint32 `json:"listing_likes"`
}

However, this is not helpful when we want to display the seller's name on FE.
We will need a query to join listing_tab and acc_tab to retrieve user_name

Affected:
All APIs returning listing

[API#5] PurchaseSingleItem

Add request and response struct:

Request:

item_id
user_id
purchase_quantity

Flow:
update listing_tab.quantity -1. If == 0, set status = soldout (use TRIGGER?)
insert row in listing_transaction_tab
insert row in wallet_transaction_tab

Validations:
Check buyer exists
Check listing exists
Check item status, quantity > 0
check wallet status, balance

[API#10] GetListingReactions

Use Case

• Listing page

Request

item_id

Response

//array of comments
//count of likes

DB

• reaction_tab (Store both comment and likes)

CREATE TABLE `listing_reactions_tab` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `reaction_type` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `comment` varchar(256) DEFAULT NULL,
  `ctime` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Find item_id and return all

SELECT * FROM reaction_tab WHERE item_id = ?

[API#9] AddUserReviews

[DATABASE#12] Migrate all images to AWS S3 and set to uniform ratio and size

[API #1] GetPopularListing

Get the most popular (most likes) listings according to category / status (optional)

{
    "item_category":"",
    "item_status":"",
    "limit":
}

[FEATURE #2] Add verbose debug messages

• Should return verbose debug msg instead of generic errors

[RELEASE] v1.4

Changes

Issue	Description	Type
#32	[BUG#2] Header URL redirects to /index.html	Bug
#31	[DATABASE#1] Update Request and Response Structs to uint32	Enhancement
#34	[API#2] Add structs and methods for GetLatestListings	New Feature

Release Checklist

• Merge test into master (#33)
• Final test against master locally
• Build docker image
• Deploy

[DATABASE # 1] Finalise listing_tab and user_tab schema

To finalize the DB schema for all DB tables.

No.	Table name	Priority	Status
1	User	Very High
2	Listing	Very High
3	Listing_transactions	High
4	Interactions	Low
5	Wallet	Normal
6	Wallet_transactions	Normal

[RELEASE] v1.5

Release Checklist

• Merge test into master (#44)
• Final test against master locally
• Build docker image
• Deploy

[DATABASE #4] Segregate test and live DB

Mirror the whole tic2601_db as tic2601_test_db for mock data and testing purposes.

Changes when switching between test and live:

DB Startup
Live: username:password@tcp(tic2601-db)/tic2601_db
Test: username:password@tcp(tic2601-db)/tic2601_test_db

.env
Add LIVE credentials

API_SECRET=98hbun98h
DB_HOST=127.0.0.1
DB_DRIVER=mysql
DB_USER=username
DB_PASSWORD=password
DB_NAME=tic2601_db
DB_PORT=3306

SQL Queries
SELECT * FROM tic2601_test_db.listing_tab
SELECT * FROM tic2601_db.listing_tab