CREATE DATABASE usm
	DEFAULT CHARACTER SET utf8;

USE usm;

CREATE TABLE usm.tblUsers (
    id int unsigned NOT NULL AUTO_INCREMENT,
    LOGIN varchar(64) NOT NULL,
    PASS varchar(64) NOT NULL,
    EMAIL varchar(128) DEFAULT NULL,
    VALCODE varchar(64) DEFAULT NULL,
    INFO varchar(1024) DEFAULT NULL,
    PERMISSIONS bigint DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY LOGIN (LOGIN)
) ENGINE=InnoDB;

CREATE TABLE usm.tblCampus (
    id int unsigned NOT NULL AUTO_INCREMENT,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    INFO varchar(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY CAMPUS (NAME)
) ENGINE=InnoDB;

CREATE TABLE usm.tblBuilding (
    id int unsigned NOT NULL AUTO_INCREMENT,
    idCampus int unsigned NOT NULL,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    INFO varchar(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (idCampus) REFERENCES tblCampus(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY BUILDING (idCampus, NAME)
) ENGINE=InnoDB;

CREATE TABLE usm.tblRoom (
    id int unsigned NOT NULL AUTO_INCREMENT,
    idBuilding int unsigned NOT NULL,
    idCmdProcs int unsigned DEFAULT NULL,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    ARGS varchar(255) DEFAULT NULL,
    ALTARGS varchar(255) DEFAULT NULL,
    ONLINE tinyint DEFAULT 0,
    INFO varchar(1024) DEFAULT NULL,
    LASTUPDATED datetime,
    PRIMARY KEY (id),
    FOREIGN KEY (idBuilding) REFERENCES tblBuilding(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY ROOM (idBuilding, NAME)
) ENGINE=InnoDB;

CREATE TABLE usm.tblDevice (
    id int unsigned NOT NULL AUTO_INCREMENT,
    idRoom int unsigned NOT NULL,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    INFO varchar(1024) DEFAULT NULL,
    LASTUPDATED datetime,
    PRIMARY KEY (id),
    FOREIGN KEY (idRoom) REFERENCES tblRoom(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY DEVICE (idRoom, NAME)
) ENGINE=InnoDB;

CREATE TABLE usm.tblProperties (
    id int unsigned NOT NULL AUTO_INCREMENT,
    idDevice int unsigned NOT NULL,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    VALUE varchar(64) DEFAULT NULL,
    TYPE varchar(16) DEFAULT 'STRING',
    PREFIX varchar(16) DEFAULT NULL,
    SUFFIX varchar(16) DEFAULT NULL,
    ALERTCONFIG varchar(16) DEFAULT NULL,
    ALERTVALUE varchar(64) DEFAULT NULL,
    ALERTTYPE int DEFAULT 0,
    ALERTSTATUS tinyint DEFAULT 0,
    ALLOWRESET tinyint DEFAULT 1,
    CAPITALIZE tinyint DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (idDevice) REFERENCES tblDevice(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY PROP (idDevice, NAME)
) ENGINE=InnoDB;

CREATE TABLE usm.tblButtons (
    id int unsigned NOT NULL AUTO_INCREMENT,
    idRoom int unsigned NOT NULL,
    GROUPNAME varchar(64) DEFAULT NULL,
    ALTGROUPNAME varchar(64) DEFAULT NULL,
    BTNNAME varchar(64) DEFAULT NULL,
    ALTBTNNAME varchar(64) DEFAULT NULL,
    COMMAND varchar(255) DEFAULT NULL,
    ALTCOMMAND varchar(255) DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (idRoom) REFERENCES tblRoom(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY BUTTON (idRoom, GROUPNAME, BTNNAME)
) ENGINE=InnoDB;

CREATE TABLE usm.tblCmdProcs (
    id int unsigned NOT NULL AUTO_INCREMENT,
    NAME varchar(255) NOT NULL,
    ALTNAME varchar(255) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY CMDPROC (NAME)
) ENGINE=InnoDB;

CREATE TABLE usm.tblSchedule(
    id int unsigned NOT NULL AUTO_INCREMENT,
    idCampus int unsigned,
    idBuilding int unsigned,
    idRoom int unsigned,
    TASKNAME varchar(50) NOT NULL,
    BTNGROUP varchar(50) NOT NULL,
    BTNNAME varchar(50) NOT NULL,
    BYDAY tinyint,
    EVENTDATE date,
    EVENTTIME time,
    EVENTDAYS int,		-- mask of mon/tue/wed/etc
    EVENTREPEAT tinyint,
    PRIMARY KEY (id),
    UNIQUE KEY EVENT (idCampus, idBuilding, idRoom, TASKNAME)
    -- TODO: should have foreign keys on campus, building, room
    --       remember that '%' selects all campus, building, room
) ENGINE=InnoDB;

CREATE TABLE usm.tblAlertColors (
    id int unsigned NOT NULL AUTO_INCREMENT,
    ALERTCOLOR varchar(32) NOT NULL,
    DESCRIPTION varchar(255) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY ALERT (DESCRIPTION)
) ENGINE=InnoDB;



CREATE DATABASE usmCodificator
    DEFAULT CHARACTER SET utf8;

USE usmCodificator;

CREATE TABLE usmCodificator.tblCampus (
    id int unsigned NOT NULL AUTO_INCREMENT,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    INFO varchar(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY CAMPUS (NAME)
) ENGINE=InnoDB;

CREATE TABLE usmCodificator.tblBuilding (
    id int unsigned NOT NULL AUTO_INCREMENT,
    idCampus int unsigned NOT NULL,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    INFO varchar(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (idCampus) REFERENCES tblCampus(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY BUILDING (idCampus, NAME)
) ENGINE=InnoDB;

CREATE TABLE usmCodificator.tblRoom (
    id int unsigned NOT NULL AUTO_INCREMENT,
    idBuilding int unsigned NOT NULL,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    INFO varchar(1024) DEFAULT NULL,
    LASTUPDATED datetime,
    PRIMARY KEY (id),
    FOREIGN KEY (idBuilding) REFERENCES tblBuilding(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY ROOM (idBuilding, NAME)
) ENGINE=InnoDB;

CREATE TABLE usmCodificator.tblDevice (
    id int unsigned NOT NULL AUTO_INCREMENT,
    idRoom int unsigned NOT NULL,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    INFO varchar(1024) DEFAULT NULL,
    LASTUPDATED datetime,
    PRIMARY KEY (id),
    FOREIGN KEY (idRoom) REFERENCES tblRoom(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY DEVICE (idRoom, NAME)
) ENGINE=InnoDB;

CREATE TABLE usmCodificator.tblProperties (
    id int unsigned NOT NULL AUTO_INCREMENT,
    idDevice int unsigned NOT NULL,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    VALUE varchar(64) DEFAULT NULL,
    TYPE varchar(16) DEFAULT 'STRING',
    TRACKER varchar(16) DEFAULT NULL,
    PREFIX varchar(16) DEFAULT NULL,
    SUFFIX varchar(16) DEFAULT NULL,
    ALERTCONFIG varchar(16) DEFAULT NULL,
    ALERTVALUE varchar(64) DEFAULT NULL,
    ALERTTYPE int DEFAULT 0,
    ALERTSTATUS tinyint DEFAULT 0,
    ALLOWRESET tinyint DEFAULT 1,
    CAPITALIZE tinyint DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (idDevice) REFERENCES tblDevice(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY PROP (idDevice, NAME)
) ENGINE=InnoDB;

CREATE TABLE usmCodificator.tblTranslations (
    id int unsigned NOT NULL AUTO_INCREMENT,
    idProperty int unsigned NOT NULL,
    NAME varchar(64) NOT NULL,
    ALTNAME varchar(64) DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (idProperty) REFERENCES tblProperties(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY TRANSLATION (idProperty, NAME)
) ENGINE = InnoDB;



CREATE DATABASE usmHistory
    DEFAULT CHARACTER SET utf8;

USE usmHistory;

CREATE TABLE usmHistory.tblCampus (
    PRIMARY KEY (id),
    UNIQUE KEY CAMPUS (NAME)
) AS SELECT * FROM usm.tblCampus;

CREATE TABLE usmHistory.tblBuilding (
    PRIMARY KEY (id),
    FOREIGN KEY (idCampus) REFERENCES tblCampus(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY BUILDING (idCampus, NAME)
) AS SELECT * FROM usm.tblBuilding;

CREATE TABLE usmHistory.tblRoom (
    PRIMARY KEY (id),
    FOREIGN KEY (idBuilding) REFERENCES tblBuilding(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY ROOM (idBuilding, NAME)
) AS SELECT * FROM usm.tblRoom;

CREATE TABLE usmHistory.tblDevice (
    PRIMARY KEY (id),
    FOREIGN KEY (idRoom) REFERENCES tblRoom(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY DEVICE (idRoom, NAME)
) AS SELECT * FROM usm.tblDevice;

CREATE TABLE usmHistory.tblProperties (
    FOREIGN KEY (idDevice) REFERENCES tblDevice(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY PROP (idDevice, NAME, LASTUPDATED)
) AS SELECT *, CURRENT_TIMESTAMP as LASTUPDATED
	FROM usm.tblProperties;



CREATE USER 'USMWeb'@'localhost'
    IDENTIFIED BY 'usmweb';
GRANT INSERT, UPDATE, SELECT, DELETE
    ON usm.* TO 'USMWeb'@'localhost';
GRANT INSERT, UPDATE, SELECT, DELETE
    ON usmCodificator.* TO 'USMWeb'@'localhost';
GRANT SELECT
    ON usmHistory.* TO 'USMWeb'@'localhost';
FLUSH PRIVILEGES;



INSERT INTO usm.tblAlertColors (id, ALERTCOLOR, DESCRIPTION)
    VALUES (1, '#00FF00', 'Informational');
INSERT INTO usm.tblAlertColors (id, ALERTCOLOR, DESCRIPTION)
    VALUES (2, '#FFFF00', 'Maintenance');
INSERT INTO usm.tblAlertColors (id, ALERTCOLOR, DESCRIPTION)
    VALUES (99, '#FF0000', 'Security');

INSERT INTO usm.tblCmdProcs (NAME, ALTNAME)
    VALUES ('python2.py', 'python2.py');
INSERT INTO usm.tblCmdProcs (NAME, ALTNAME)
    VALUES ('python3.py', 'python3.py');
INSERT INTO usm.tblCmdProcs (NAME, ALTNAME)
    VALUES ('ssh.py', 'ssh.py');
INSERT INTO usm.tblCmdProcs (NAME, ALTNAME)
    VALUES ('telnet.py', 'telnet.py');



-- useful for report query
create index idx_type on tblProperties(TYPE);
create index idx_lastupdated on tblProperties(LASTUPDATED);



CREATE TRIGGER usm.campus_insert
AFTER INSERT ON usm.tblCampus
FOR EACH ROW INSERT INTO usmHistory.tblCampus
    (SELECT * FROM usm.tblCampus WHERE id=NEW.id)
ON DUPLICATE KEY UPDATE id=NEW.id, ALTNAME=NEW.ALTNAME, INFO=NEW.INFO;

CREATE TRIGGER usm.building_insert
AFTER INSERT ON usm.tblBuilding
FOR EACH ROW INSERT INTO usmHistory.tblBuilding
    (SELECT * FROM usm.tblBuilding WHERE id=NEW.id)
ON DUPLICATE KEY UPDATE id=NEW.id, ALTNAME=NEW.ALTNAME, INFO=NEW.INFO;

CREATE TRIGGER usm.room_insert
AFTER INSERT ON usm.tblRoom
FOR EACH ROW INSERT INTO usmHistory.tblRoom
    (SELECT * FROM usm.tblRoom WHERE id=NEW.id)
ON DUPLICATE KEY UPDATE id=NEW.id, idCmdProcs=NEW.idCmdProcs,
	ALTNAME=NEW.ALTNAME, ARGS=NEW.ARGS, ALTARGS=NEW.ALTARGS,
	ONLINE=NEW.ONLINE, INFO=NEW.INFO, LASTUPDATED=NEW.LASTUPDATED;

CREATE TRIGGER usm.device_insert
AFTER INSERT ON usm.tblDevice
FOR EACH ROW INSERT INTO usmHistory.tblDevice
    (SELECT * FROM usm.tblDevice WHERE id=NEW.id)
ON DUPLICATE KEY UPDATE id=NEW.id, ALTNAME=NEW.ALTNAME, INFO=NEW.INFO,
	LASTUPDATED=NEW.LASTUPDATED;

CREATE TRIGGER usm.property_insert
AFTER INSERT ON usm.tblProperties
FOR EACH ROW INSERT IGNORE INTO usmHistory.tblProperties
    (SELECT *, CURRENT_TIMESTAMP FROM usm.tblProperties WHERE id=NEW.id);

CREATE TRIGGER usm.campus_update
AFTER UPDATE ON usm.tblCampus
FOR EACH ROW UPDATE usmHistory.tblCampus
    SET ALTNAME=NEW.ALTNAME, INFO=NEW.INFO
    WHERE id=NEW.id;

CREATE TRIGGER usm.building_update
AFTER UPDATE ON usm.tblBuilding
FOR EACH ROW UPDATE usmHistory.tblBuilding
    SET ALTNAME=NEW.ALTNAME, INFO=NEW.INFO
    WHERE id=NEW.id;

CREATE TRIGGER usm.room_update
AFTER UPDATE ON usm.tblRoom
FOR EACH ROW UPDATE usmHistory.tblRoom
    SET idCmdProcs=NEW.idCmdProcs, ALTNAME=NEW.ALTNAME, ARGS=NEW.ARGS,
		ALTARGS=NEW.ALTARGS, ONLINE=NEW.ONLINE, INFO=NEW.INFO,
		LASTUPDATED=NEW.LASTUPDATED
    WHERE id=NEW.id;

CREATE TRIGGER usm.device_update
AFTER UPDATE ON usm.tblDevice
FOR EACH ROW UPDATE usmHistory.tblDevice
    SET ALTNAME=NEW.ALTNAME, INFO=NEW.INFO, LASTUPDATED=NEW.LASTUPDATED
    WHERE id=NEW.id;

DELIMITER $$
CREATE TRIGGER usm.property_update
AFTER UPDATE ON usm.tblProperties
FOR EACH ROW
BEGIN
    IF OLD.VALUE != NEW.VALUE
    THEN
        INSERT IGNORE INTO usmHistory.tblProperties (
            SELECT *, CURRENT_TIMESTAMP
                FROM usm.tblProperties
                WHERE id=NEW.id
        );
    END IF;
END$$
DELIMITER ;