fp_sql.sql

DROP DATABASE IF EXISTS BasketballLeague;
Create DATABASE IF NOT EXISTS BasketballLeague;

DROP USER IF EXISTS 'admin'@'localhost';
DROP USER IF EXISTS 'manager'@'localhost';
DROP USER IF EXISTS 'coach'@'localhost';
DROP USER IF EXISTS 'player'@'localhost';
DROP USER IF EXISTS 'unassigned'@'localhost';



-- create role accounts
CREATE USER 'manager'@'localhost' IDENTIFIED BY 'manager_password';
CREATE USER 'coach'@'localhost'   IDENTIFIED BY 'coach_password';
CREATE USER 'player'@'localhost'  IDENTIFIED BY 'player_password';
CREATE USER 'admin'@'localhost'  IDENTIFIED BY '431Project!';
CREATE USER 'unassigned'@'localhost'  IDENTIFIED BY 'accountPending';

-- Grant all power to ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON BasketballLeague.* TO 'admin'@'localhost';

--Manager can see everything
GRANT SELECT ON BasketballLeague.* to 'manager'@'localhost';


USE BasketballLeague;

-- Authentication stuff

CREATE TABLE Roles
(
  Role_ID        TINYINT UNSIGNED   AUTO_INCREMENT   PRIMARY KEY,
  roleName       VARCHAR(30)        NOT NULL   UNIQUE   COMMENT 'Must match Database Users',
  homePage       VARCHAR(30)        DEFAULT 'PlayerHomePage'
);

GRANT select, insert, delete, update ON Roles TO 'admin'@'localhost';
-- GRANT select, insert, delete, update ON Roles TO 'manager'@'localhost';
-- GRANT select ON Roles TO 'manager'@'localhost';
GRANT SELECT on Roles TO 'player'@'localhost';



INSERT INTO Roles VALUES 
-- NOTE:  These values Must match the Database Users created at the end of this script
 (1, 'player', 'PlayerHomepage.php'),
 (2, 'coach', 'CoachHomepage.php'),
 (3, 'manager', 'ManagerHomepage.php'),
 (4, 'unassigned', 'AccountPending.php');   -- DEFAULT value


-- Team creation
 CREATE TABLE Team (
    Team_ID     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    TeamName    VARCHAR(30),
    Wins        TINYINT DEFAULT 0,
    Losses      TINYINT DEFAULT 0,
    Draws       TINYINT DEFAULT 0,
    HomeCourt   VARCHAR(50) NOT NULL,
    HeadCoach   INT UNSIGNED DEFAULT NULL -- This is added as a foreign key after user table creation

);

GRANT select, update ON Team TO 'coach'@'localhost';

INSERT INTO Team (TeamName, HomeCourt) VALUES
    ('Team Alpha', 'Stadium A'),
    ('Team Beta', 'Arena B'),
    ('Team Gamma', 'Field C'),
    ('Team Delta', 'Court D'),
    ('Team Epsilon', 'Gym E'),
    ('Team Zeta', 'Park F');


-- User tables

CREATE TABLE Users
(
    User_ID       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Role          TINYINT UNSIGNED DEFAULT 4,
    TeamID            INT UNSIGNED DEFAULT NULL,
    Name_First    VARCHAR(100),
    Name_Last     VARCHAR(150)     NOT NULL,
    Email         VARCHAR(250)     UNIQUE,
    Password      CHAR(60),
    Created       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Active        BOOLEAN   NOT NULL DEFAULT 0,
    LastLogin     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (TeamID) REFERENCES Team(Team_ID),
    FOREIGN KEY (Role) REFERENCES Roles(Role_ID) ON DELETE CASCADE
);

-- add HeadCoach to Team
ALTER TABLE Team ADD FOREIGN KEY (HeadCoach) REFERENCES Users(User_ID);


GRANT select, insert, delete, update ON Users TO 'manager'@'localhost';
GRANT select, update ON Users TO 'coach'@'localhost';
GRANT select on Users to 'player'@'localhost';
GRANT select on Users to 'unassigned'@'localhost';



-- example users

-- Inserting players
INSERT INTO Users (Name_First, Name_Last, Email, Password, Role, Active, TeamID)
VALUES ('Emily', 'Jones', 'emily@example.com', '$2y$10$qCWtuoNg7IfpFQAnoNfAoOPHQ4zDlmLLt9ReJT6ix9Ju0i7m5s2cu', 1, 1, 1),
       ('Michael', 'Brown', 'michael@example.com', 'password', 1, 1, 2),
       ('Jessica', 'Taylor', 'jessica@example.com', 'password', 1, 1, 3),
       ('David', 'Martinez', 'david@example.com', 'password', 1, 1, 1),
       ('Jeff', 'Doe', 'jeff@example.com', 'password', 1, 1, NULL),  -- new record with null TeamID
       ('Jane', 'Doe', 'jane@example.com', 'password', 1, 1, NULL);  -- new record with null TeamID
       


-- Inserting manager
INSERT INTO Users (Name_First, Name_Last, Email, Password, Role, Active)
VALUES ('John', 'Doe', 'john@example.com', '$2y$10$qCWtuoNg7IfpFQAnoNfAoOPHQ4zDlmLLt9ReJT6ix9Ju0i7m5s2cu', 3, 1);
-- The password is '123' the database is holding the hash ^^^^^^^^^


-- Inserting coaches
INSERT INTO Users (Name_First, Name_Last, Email, Password, Role, Active, TeamID)
VALUES ('Alice', 'Smith', 'alice@example.com', 'password', 2, 1, 3),
       ('Bob', 'Johnson', 'bob@example.com', 'password', 2, 1, 2),
       ('coachT', 'COACH', 'coach@e.com', '$2y$10$qCWtuoNg7IfpFQAnoNfAoOPHQ4zDlmLLt9ReJT6ix9Ju0i7m5s2cu',2,1,1 );
-- The password is '123' the database is holding the hash ^^^^^^^^^


--insert test account
INSERT INTO Users (Name_First, Name_Last, Email, Password, Role, Active) 
VALUES('test', 'Test', 'e@e.com', '$2y$10$qCWtuoNg7IfpFQAnoNfAoOPHQ4zDlmLLt9ReJT6ix9Ju0i7m5s2cu',4,0 );
-- The password is '123' the database is holding the hash ^^^^^^^^^


-- Seasons

CREATE TABLE Season (
    Season_ID   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StartDate   DATE,
    EndDate     DATE
);

INSERT INTO Season (StartDate, EndDate) VALUES
    ('2024-01-01', '2024-03-31'),
    ('2024-04-01', '2024-06-30'),
    ('2024-07-01', '2024-09-30');

GRANT select, insert, update ON Season TO 'manager'@'localhost';

-- Game Info

CREATE TABLE Game (
    Game_ID    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    HomeTeam    INT UNSIGNED NOT NULL,
    AwayTeam    INT UNSIGNED NOT NULL,
    Season      INT UNSIGNED NOT NULL,
    HomeTeamPoints TINYINT DEFAULT 0,
    AwayTeamPoints TINYINT DEFAULT 0,
    Location    VARCHAR(50),

    Winner INT UNSIGNED GENERATED ALWAYS AS (
        CASE 
            WHEN HomeTeamPoints > AwayTeamPoints THEN HomeTeam
            WHEN AwayTeamPoints > HomeTeamPoints THEN AwayTeam
            ELSE NULL
        END
    ) STORED,

    FOREIGN KEY (HomeTeam) REFERENCES Team(Team_ID),
    FOREIGN KEY (AwayTeam) REFERENCES Team(Team_ID),
    FOREIGN KEY (Season) REFERENCES Season(Season_ID)
);


GRANT select, create, update, delete ON Game TO 'coach'@'localhost';
GRANT SELECT ON Game TO 'player'@'localhost';



INSERT INTO Game (HomeTeam, AwayTeam, Season, HomeTeamPoints, AwayTeamPoints, Location)
VALUES
    (3, 4, 1, 1, 2, 'Arena B'),
    (5, 6, 1, 2, 2, 'Field C'),
    (2, 1, 1, 2, 3, 'Stadium A');



CREATE TABLE PlayerStats (
    Stat_ID     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Player      INT UNSIGNED NOT NULL,
    Team        INT UNSIGNED NOT NULL,
    Game        INT UNSIGNED NOT NULL,       
    PlayTimeMin TINYINT UNSIGNED DEFAULT 0,
    PlayTimeSec TINYINT UNSIGNED DEFAULT 0,
    Points      TINYINT UNSIGNED DEFAULT 0,
    Assists     TINYINT UNSIGNED DEFAULT 0,
    Rebounds    TINYINT UNSIGNED DEFAULT 0,

    FOREIGN KEY (Player) REFERENCES Users(User_ID),
    FOREIGN KEY (Team) REFERENCES Team(Team_ID),
    FOREIGN KEY (Game) REFERENCES Game(Game_ID),

    CHECK(
        (PlayTimeMin = 0 AND PlayTimeSec BETWEEN 1 AND 59) OR
        (PlayTimeMin BETWEEN 1 AND 39 AND PlayTimeSec BETWEEN 0 AND 59) OR
        (PlayTimeMin = 40 AND PlayTimeSec = 0)
    )
);

GRANT SELECT ON PlayerStats TO 'player'@'localhost';
GRANT SELECT ON PlayerStats TO 'coach'@'localhost';


INSERT INTO PlayerStats (Player, Team, Game, PlayTimeMin, PlayTimeSec, Points, Assists, Rebounds)
VALUES
    (1, 1, 1, 30, 15, 20, 5, 10),
    (1, 1, 2, 0, 10, 200, 2, 100),
    (1, 1, 3, 14, 25, 2, 53, 1),
    (2, 1, 1, 25, 20, 15, 3, 8),
    (3, 2, 2, 35, 10, 25, 7, 12),
    (4, 2, 2, 20, 30, 10, 2, 5),
    (5, 3, 3, 40, 0, 30, 8, 15);