schema.sql

USE ultimate_tic_tac_toe_db;

CREATE TABLE IF NOT EXISTS users (
    userid CHAR(36) PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    salt VARCHAR(255) NOT NULL,
    hashedPassword VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


SELECT 'Table users has been created';

CREATE TABLE IF NOT EXISTS tokens(
    userid CHAR(36) PRIMARY KEY,
    sessionToken CHAR(36),
    sessionToken_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    refreshToken CHAR(36),
    refreshToken_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (userid) REFERENCES users(userid)
);

DELIMITER $$

-- Trigger to update sessionToken_updated_at
CREATE TRIGGER update_sessionToken_timestamp
BEFORE UPDATE ON tokens
FOR EACH ROW
BEGIN
    IF NEW.sessionToken != OLD.sessionToken THEN
        SET NEW.sessionToken_updated_at = CURRENT_TIMESTAMP;
    END IF;
END $$

-- Trigger to update refreshToken_updated_at
CREATE TRIGGER update_refreshToken_timestamp
BEFORE UPDATE ON tokens
FOR EACH ROW
BEGIN
    IF NEW.refreshToken != OLD.refreshToken THEN
        SET NEW.refreshToken_updated_at = CURRENT_TIMESTAMP;
    END IF;
END $$

DELIMITER ;

SELECT 'Table tokens has been created';

CREATE TABLE IF NOT EXISTS gameinvites (
    inviterid CHAR(36) NOT NULL,
    inviteeeid CHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (inviterid) REFERENCES users(userid),
    FOREIGN KEY (inviteeeid) REFERENCES users(userid)
);

SELECT 'Table gameinvites has been created';

CREATE TABLE IF NOT EXISTS games (
    gameid CHAR(36) PRIMARY KEY,
    playerid0 CHAR(36) NOT NULL,
    playerid1 CHAR(36) NOT NULL,
    gamestate VARCHAR(500),
    lastMove INT(2),
    player0ToPlayNext BOOLEAN NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (playerid0) REFERENCES users(userid),
    FOREIGN KEY (playerid1) REFERENCES users(userid)
);

SELECT 'Table games has been created';
SELECT 'CHANGE VARCHAR(500) TO SOMETHING ELSE IN games !!!';

CREATE TABLE IF NOT EXISTS gamehistory (
    matchid CHAR(36) PRIMARY KEY,
    winnerid CHAR(36),
    loserid CHAR(36),
    endTime DATETIME NOT NULL,
    FOREIGN KEY (winnerid) REFERENCES users(userid),
    FOREIGN KEY (loserid) REFERENCES users(userid)
);
SELECT 'Table gamehistory has been created';

CREATE TABLE IF NOT EXISTS playerstats (
    playerid CHAR(36) PRIMARY KEY,
    gamesPlayed INT NOT NULL DEFAULT 0,
    gamesWon INT NOT NULL DEFAULT 0,
    gamesLost INT NOT NULL DEFAULT 0,
    FOREIGN KEY (playerid) REFERENCES users(userid)
);
SELECT 'Table playerstats has been created';

CREATE TABLE IF NOT EXISTS connectedusers (
    id CHAR(36) PRIMARY KEY,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id) REFERENCES users(userid)
);
SELECT 'Table connectedusers has been created';

-- Log entry to indicate that schema.sql has been executed
SELECT 'schema.sql has been executed';