/* hypetracker.sql */

CREATE DATABASE IF NOT EXISTS HypeTracker;

USE HypeTracker;

CREATE TABLE IF NOT EXISTS `Sneakers` (
  `Name` VARCHAR(100) NOT NULL,
  `Price` DECIMAL(9,2) UNSIGNED,
  `Brand` VARCHAR(40) NOT NULL,
  CONSTRAINT `PK_Sneakers` PRIMARY KEY (Name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `Members` (
  `Name` VARCHAR(40) NOT NULL,
  `Email` VARCHAR(100) NOT NULL,
  `Password` VARCHAR(40) NOT NULL,
  CONSTRAINT `PK_Members` PRIMARY KEY (Email)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `Watchlist` (
  `SneakerName` VARCHAR(100) NOT NULL,
  `MemberEmail` VARCHAR(100) NOT NULL,
  CONSTRAINT `PK_Watchlist` 
    PRIMARY KEY (SneakerName, MemberEmail),
  CONSTRAINT `FK_Watchlist_Sneakers`
    FOREIGN KEY (SneakerName) 
    REFERENCES Sneakers(Name)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_Watchlist_Members`
    FOREIGN KEY (MemberEmail) 
    REFERENCES Members(Email)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `Rankings` (
  `Platform` VARCHAR(100) NOT NULL,
  `Date` DATE NOT NULL,
  `SneakerName` VARCHAR(100) NOT NULL,
  `Mentions` INT UNSIGNED NOT NULL,
  CONSTRAINT `PK_Rankings`
    PRIMARY KEY (Platform, Date, SneakerName),
  CONSTRAINT `FK_Rankings_Sneakers`
    FOREIGN KEY (SneakerName)
    REFERENCES Sneakers(Name)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

/* Get All Sneakers Watched by a Member */
SELECT S.Name, S.Price, S.Brand
FROM Sneakers S
INNER JOIN Watchlist W
ON S.Name = W.SneakerName
WHERE W.MemberEmail = '$email'; /* $email is a PHP variable here */

/* Get the 5 Most Mentioned Sneakers In the Last Week */
SELECT S.Name, S.Price, S.Brand, RS.TotalMentions
FROM Sneakers S
INNER JOIN
(
  SELECT R.SneakerName, SUM(Mentions) AS TotalMentions
  FROM Rankings R
  WHERE R.Date > DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY R.SneakerName
  ORDER BY TotalMentions DESC
  LIMIT 5
) RS
ON S.Name = RS.SneakerName;