CREATE DATABASE usm DEFAULT CHARACTER SET utf8;

use usm;

CREATE TABLE user (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    password VARCHAR(64) NOT NULL,
    email VARCHAR(128) DEFAULT NULL,
    validation VARCHAR(64) DEFAULT NULL,
    info VARCHAR(1024) DEFAULT NULL,
    permissions MEDIUMINT DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY user (name)
) ENGINE=InnoDB;

CREATE TABLE campus (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    info VARCHAR(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY campus (name)
) ENGINE=InnoDB;

CREATE TABLE building (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    campus_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    info VARCHAR(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (campus_id) REFERENCES campus(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY building (campus_id, name)
) ENGINE=InnoDB;

CREATE TABLE room (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    building_id INT UNSIGNED NOT NULL,
    processor_id INT UNSIGNED DEFAULT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    argument VARCHAR(255) DEFAULT NULL,
    alt_argument VARCHAR(255) DEFAULT NULL,
    online TINYINT DEFAULT 0,
    info VARCHAR(1024) DEFAULT NULL,
    last_updated DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (building_id) REFERENCES building(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY room (building_id, name)
) ENGINE=InnoDB;

CREATE TABLE device (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    room_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    info VARCHAR(1024) DEFAULT NULL,
    last_updated DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (room_id) REFERENCES room(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY device (room_id, name)
) ENGINE=InnoDB;

CREATE TABLE property (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    value VARCHAR(64) DEFAULT NULL,
    type VARCHAR(16) DEFAULT 'STRING',
    prefix VARCHAR(16) DEFAULT NULL,
    suffix VARCHAR(16) DEFAULT NULL,
    alert_config VARCHAR(16) DEFAULT NULL,
    alert_value VARCHAR(64) DEFAULT NULL,
    alert_type INT DEFAULT 0,
    alert_status TINYINT DEFAULT 0,
    allow_reset TINYINT DEFAULT 1,
    capitalize TINYINT DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (device_id) REFERENCES device(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY property (device_id, name)
) ENGINE=InnoDB;

CREATE TABLE button (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    room_id INT UNSIGNED NOT NULL,
    group VARCHAR(64) DEFAULT NULL,
    alt_group VARCHAR(64) DEFAULT NULL,
    name VARCHAR(64) DEFAULT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    command VARCHAR(255) DEFAULT NULL,
    alt_command VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (room_id) REFERENCES room(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY button (room_id, group, name)
) ENGINE=InnoDB;

CREATE TABLE processor (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    alt_name VARHAR(255) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY processor (name)
) ENGINE=InnoDB;

CREATE TABLE event (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    campus_id INT UNSIGNED,
    building_id INT UNSIGNED,
    room_id INT UNSIGNED,
    name VARCHAR(50) NOT NULL,
    button_group VARCHAR(50) NOT NULL,
    button_name VARCHAR(50) NOT NULL,
    by_day TINYINT,
    date DATE,
    time TIME,
    days INT,		-- bit mask for day of the week
    repeat_event TINYINT,
    PRIMARY KEY (id),
    UNIQUE KEY event (campus_id, building_id, room_id, name)
    -- TODO: should have foreign keys on campus, building, room
    --       remember that a value of '%' selects all campus, building, room
) ENGINE=InnoDB;

CREATE TABLE color (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    value VARCHAR(32) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY color (name)
) ENGINE=InnoDB;



INSERT INTO color (name, value) VALUES ('Informational', '#00FF00');
INSERT INTO color (name, value) VALUES ('Maintenance', '#FFFF00');
INSERT INTO color (name, value) VALUES ('Security', '#FF0000');



CREATE DATABASE codificator DEFAULT CHARACTER SET utf8;

use codificator;

CREATE TABLE campus (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    info VARCHAR(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY campus (name)
) ENGINE=InnoDB;

CREATE TABLE building (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    campus_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    info VARCHAR(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (campus_id) REFERENCES campus(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY building (campus_id, name)
) ENGINE=InnoDB;

CREATE TABLE room (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    building_id INT UNSIGNED NOT NULL,
    processor_id INT UNSIGNED DEFAULT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    argument VARCHAR(255) DEFAULT NULL,
    alt_argument VARCHAR(255) DEFAULT NULL,
    online TINYINT DEFAULT 0,
    info VARCHAR(1024) DEFAULT NULL,
    last_updated DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (building_id) REFERENCES building(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY room (building_id, name)
) ENGINE=InnoDB;

CREATE TABLE device (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    room_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    info VARCHAR(1024) DEFAULT NULL,
    last_updated DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (room_id) REFERENCES room(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY device (room_id, name)
) ENGINE=InnoDB;

CREATE TABLE property (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name 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,
    alert_config VARCHAR(16) DEFAULT NULL,
    alert_value VARCHAR(64) DEFAULT NULL,
    alert_type INT DEFAULT 0,
    alert_status TINYINT DEFAULT 0,
    allow_reset TINYINT DEFAULT 1,
    capitalize TINYINT DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (device_id) REFERENCES device(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY property (device_id, name)
) ENGINE=InnoDB;

CREATE TABLE translation (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (property_id) REFERENCES property(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY translation (property_id, name)
) ENGINE = InnoDB;



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



CREATE DATABASE history DEFAULT CHARACTER SET utf8;

use history;

CREATE TABLE campus (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    info VARCHAR(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY campus (name)
) ENGINE=InnoDB;

CREATE TABLE building (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    campus_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    info VARCHAR(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (campus_id) REFERENCES campus(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY building (campus_id, name)
) ENGINE=InnoDB;

CREATE TABLE room (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    building_id INT UNSIGNED NOT NULL,
    processor_id INT UNSIGNED DEFAULT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    argument VARCHAR(255) DEFAULT NULL,
    alt_argument VARCHAR(255) DEFAULT NULL,
    online TINYINT DEFAULT 0,
    info VARCHAR(1024) DEFAULT NULL,
    last_updated DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (building_id) REFERENCES building(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY room (building_id, name)
) ENGINE=InnoDB;

CREATE TABLE device (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    room_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    info VARCHAR(1024) DEFAULT NULL,
    last_updated DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (room_id) REFERENCES room(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY device (room_id, name)
) ENGINE=InnoDB;

CREATE TABLE property (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    alt_name VARCHAR(64) DEFAULT NULL,
    value VARCHAR(64) DEFAULT NULL,
    type VARCHAR(16) DEFAULT 'STRING',
    prefix VARCHAR(16) DEFAULT NULL,
    suffix VARCHAR(16) DEFAULT NULL,
    alert_config VARCHAR(16) DEFAULT NULL,
    alert_value VARCHAR(64) DEFAULT NULL,
    alert_type INT DEFAULT 0,
    alert_status TINYINT DEFAULT 0,
    allow_reset TINYINT DEFAULT 1,
    capitalize TINYINT DEFAULT 0,
    timestamp DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (device_id) REFERENCES device(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY property (device_id, name, timestamp)
) ENGINE=InnoDB;

use usm;

-- TODO: 'FOR EACH ROW' triggers don't exist in MsSQL

CREATE TRIGGER usm.campus_insert
AFTER INSERT ON usm.campus
FOR EACH ROW INSERT INTO history.campus
    (SELECT * FROM usm.campus WHERE id=NEW.id);

CREATE TRIGGER usm.building_insert
AFTER INSERT ON usm.building
FOR EACH ROW INSERT INTO history.building
    (SELECT * FROM usm.building WHERE id=NEW.id);

CREATE TRIGGER usm.room_insert
AFTER INSERT ON usm.room
FOR EACH ROW INSERT INTO history.room
    (SELECT * FROM usm.room WHERE id=NEW.id);

CREATE TRIGGER usm.device_insert
AFTER INSERT ON usm.device
FOR EACH ROW INSERT INTO history.device
    (SELECT * FROM usm.device WHERE id=NEW.id);

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

CREATE TRIGGER usm.campus_update
AFTER UPDATE ON usm.campus
FOR EACH ROW UPDATE history.campus
    SET name=NEW.name, alt_name=NEW.alt_name, info=NEW.info
    WHERE id=NEW.id;

CREATE TRIGGER usm.building_update
AFTER UPDATE ON usm.building
FOR EACH ROW UPDATE history.building
    SET campus_id=NEW.campus_id, name=NEW.name,
        alt_name=NEW.alt_name, info=NEW.info
    WHERE id=NEW.id;

CREATE TRIGGER usm.room_update
AFTER UPDATE ON usm.room
FOR EACH ROW UPDATE history.room
    SET building_id=NEW.building_id, processor_id=NEW.processor_id,
        name=NEW.name, alt_name=NEW.alt_name, argument=NEW.argument,
        alt_argument=NEW.alt_argument, online=NEW.online, info=NEW.info,
        last_updated=NEW.last_updated
    WHERE id=NEW.id;

CREATE TRIGGER usm.device_update
AFTER UPDATE ON usm.device
FOR EACH ROW UPDATE history.device
    SET room_id=NEW.room_id, name=NEW.name, alt_name=NEW.alt_name,
        info=NEW.info, last_updated=NEW.last_updated
    WHERE id=NEW.id;

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