schema.sql

CREATE DATABASE roster_system;

USE roster_system;

CREATE TABLE IF NOT EXISTS roles (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(10) NOT NULL UNIQUE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS users (
    id INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    userName VARCHAR(20) NOT NULL,
    userEmail VARCHAR(255) NOT NULL,
    userPwd VARCHAR(255) NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS password_resets (
    id INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL, 
    userID INT(11) NOT NULL,
    token VARCHAR(255) NOT NULL UNIQUE, 
    expiresAt DATETIME NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pr_user FOREIGN KEY (userID) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS types (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    userID INT(11) NOT NULL,
    type VARCHAR(255) NOT NULL UNIQUE,
    bgColor VARCHAR(7) NOT NULL,
    fontColor VARCHAR(7) NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_type_user FOREIGN KEY (userID) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS rosters (
    id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    userID INT(11) NOT NULL,
    cycle INT(10) NOT NULL,
    date DATE NOT NULL,
    type VARCHAR(20) NULL,
    remark TEXT,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_roster_user FOREIGN KEY (userID) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_roster_type FOREIGN KEY (type) REFERENCES types (type) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

INSERT INTO roles (name) VALUES ('admin'), ('customer');

--INSERT INTO rosters (userID, cycle, date, type, remark) VALUES (1, 444, '2025-05-20', 'RDO', '');

--SELECT cycle FROM rosters WHERE userID = 1 GROUP BY cycle;