db_skeleton.sql

--
-- m2mApp skeleton
-- Note: DO NOT EDIT THIS FILE IN PHPSTORM (OR ANY OTHER JETBRAINS IDE) - THE FORMATTING GETS SCREWED
-- Make edits to this in another editor (i.e. VSCode, Sublime Text etc.)
--
-- Author: D Hollis (p2533140@my365.dmu.ac.uk)
-- Author: A Grosvenor (p2573368@my365.dmu.ac.uk)
-- Date: 17/01/22
--
DROP DATABASE IF EXISTS `m2mApp`;
CREATE DATABASE `m2mApp`;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
    `u_id` INT(100) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `admin` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `messages`;
CREATE TABLE `messages`(
  `msg_id` INT(11) NOT NULL AUTO_INCREMENT,
  `srcMSISDN` VARCHAR(20) NOT NULL,
  `destMSISDN` VARCHAR(20) DEFAULT NULL,
  `receivedDate` VARCHAR(20) DEFAULT NULL,
  `teamID` VARCHAR(10) DEFAULT NULL,
  `bearer` VARCHAR(5) DEFAULT NULL,
  `messageRef` VARCHAR(20) DEFAULT NULL,
  `switch1` INT DEFAULT NULL,
  `switch2` INT DEFAULT NULL,
  `switch3` INT DEFAULT NULL,
  `switch4` INT DEFAULT NULL,
  `fanEnabled` INT DEFAULT NULL,
  `fanForward` INT DEFAULT NULL,
  `fanReverse` INT DEFAULT NULL,
  `heaterEnabled` INT DEFAULT NULL,
  `heaterTemperature` DECIMAL(5,2) DEFAULT NULL,
  `keypadLastValue` INT(11) DEFAULT NULL,
  PRIMARY KEY (`msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `log`;
CREATE TABLE `log`(
  `log_id` INT(11) NOT NULL AUTO_INCREMENT,
  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `logType` VARCHAR(20) DEFAULT NULL,
  `logMsg` TEXT DEFAULT NULL,
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `2fa`;
CREATE TABLE `2fa` (
  `2fa_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL UNIQUE,
  `2faToken` varchar(6) NOT NULL,
  `issuedAt` varchar(20) NOT NULL,
  `validTill` varchar(20) NOT NULL,
  PRIMARY KEY (`2fa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;