login_database.sql

CREATE DATABASE IF NOT EXISTS login_app;
USE login_app;

CREATE TABLE IF NOT EXISTS user (
	user_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    password VARCHAR(100),
    type ENUM('buyer', 'seller', 'customer representative', 'admin')
);

CREATE TABLE IF NOT EXISTS item (
	item_id INT AUTO_INCREMENT PRIMARY KEY,
    inStock BOOLEAN,
    color varchar(20),
    size varchar(10),
    price FLOAT
);

CREATE TABLE IF NOT EXISTS auction_isAuctioned (
	auction_id INT AUTO_INCREMENT PRIMARY KEY,
    minPrice FLOAT,
    start_date DATETIME,
    end_date DATETIME,
    highestPrice FLOAT,
    item_id INT,
    FOREIGN KEY (item_id) REFERENCES item(item_id)
		ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS bidsOn_bid_itemBid (
	bid_id INT AUTO_INCREMENT PRIMARY KEY,
    maxPrice FLOAT,
    autoBidPrice FLOAT,
    bid_amount FLOAT, 
    bid_item VARCHAR(50),
    bid_time DATETIME,
    user_id INT,
    auction_id INT,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
		ON DELETE CASCADE,
	FOREIGN KEY (auction_id) REFERENCES auction_isAuctioned(auction_id)
		ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS T_QuestionAnswers (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    question VARCHAR(500) NOT NULL,
    answer VARCHAR(500),
    dateAsked TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    dateAnswered TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);

CREATE TABLE watchlist (
    watch_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    auction_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE,
    FOREIGN KEY (auction_id) REFERENCES T_Auction_isAuctioned(auction_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS saved_alert (
    alert_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    color VARCHAR(20),
    size  VARCHAR(10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS alerts (
    alert_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    message VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS T_ItemAlerts (
    alert_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    category VARCHAR(50),
    subcategory VARCHAR(50),
    size VARCHAR(10),
    color VARCHAR(20),
    min_price DECIMAL(10,2),
    max_price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_alert_user FOREIGN KEY (user_id) REFERENCES user(user_id)
);

CREATE TABLE earnings_report (
    report_id INT AUTO_INCREMENT PRIMARY KEY,
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_revenue DECIMAL(12,2) NOT NULL
);

CREATE TABLE earnings_item (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_id INT NOT NULL,
    item_name VARCHAR(255),
    category VARCHAR(255),
    revenue DECIMAL(12,2),
    FOREIGN KEY (report_id) REFERENCES earnings_report(report_id) ON DELETE CASCADE
);

CREATE TABLE earnings_buyer (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_id INT NOT NULL,
    buyer_id INT,
    buyer_name VARCHAR(255),
    spent DECIMAL(12,2),
    FOREIGN KEY (report_id) REFERENCES earnings_report(report_id) ON DELETE CASCADE
);