createDBTable

CREATE TABLE Users (
    userID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    phoneNo VARCHAR(255) NOT NULL,
    userProfile VARCHAR(255) NOT NULL,
    emailAdd VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE BidList (
    bidID int AUTO_INCREMENT NOT NULL PRIMARY KEY,
    userID INT NOT NULL,
    auctionID INT NOT NULL,
    bidPrice FLOAT NOT NULL,
    bidDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (auctionID) REFERENCES Auction(auctionID) ON DELETE CASCADE,
    FOREIGN KEY (userID) REFERENCES Users(userID) ON DELETE CASCADE,
    CONSTRAINT no_self_bidding CHECK (auctionID NOT IN (SELECT auctionID FROM Auction WHERE userID =
BidList.userID)),
    CONSTRAINT valid_bidding_time CHECK (bidDate BETWEEN (SELECT startTime FROM Auction WHERE Auction.auctionID = BidList.auctionID) AND (SELECT endTime FROM Auction WHERE Auction.auctionID = BidList.auctionID))
);

CREATE TABLE Auction (
    auctionID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    userID INT NOT NULL,
    itemName VARCHAR(255) NOT NULL,
    itemPrice FLOAT NOT NULL,
    itemDescription TEXT,
    startingPice FLOAT NOT NULL,
    itemPicture MEDIUMBLOB,
    status ENUM('open', 'closed') NOT NULL,
    startTime DATETIME NOT NULL,
    endTime DATETIME NOT NULL,
    FOREIGN KEY (userID) REFERENCES Users(userID) ON DELETE CASCADE -- If user being deleted, its auction will also be deleted

);

CREATE TABLE Wishlist (
    wishlistID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    userID INT NOT NULL,
    auctionID INT NOT NULL,
    addedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (userID) REFERENCES Users(userID) ON DELETE CASCADE,
    FOREIGN KEY (auctionID) REFERENCES Auction(auctionID) ON DELETE CASCADE,
    UNIQUE (userID, auctionID) -- Prevent duplicate entries
);