CREATE DATABASE sensors;
GRANT ALL PRIVILEGES ON sensors.* TO SensorsAdmin;

USE sensors;
GO

CREATE TABLE Users (`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` TEXT, `salt` BLOB, `key` BLOB, `administer` BOOLEAN DEFAULT 0);
INSERT INTO Users (`ID`, `username`, `salt`, `key`, `administer`) VALUES (1,'nouser',NULL,NULL,0);

CREATE TABLE Permissions (`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `userID` INT, `roomID` INT, `view` BOOLEAN DEFAULT 0, `purge_data` BOOLEAN DEFAULT 0, `administer` BOOLEAN DEFAULT 0);
CREATE TABLE Rooms (`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` TEXT, `shortname` TEXT UNIQUE);
CREATE TABLE Sensors(`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `type` INT, `deviceID` INT, `mqttTopic` TEXT, `name` TEXT);
CREATE TABLE Types(`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `type_desc` TEXT);

CREATE TABLE Devices(`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `roomID` INT, `name` TEXT, `description` TEXT, `mqttTopic` TEXT);

CREATE TABLE DeviceLog( `timestamp` TIMESTAMP, `deviceID` INT, `message` TEXT);
CREATE TABLE Readings(`sensorID` INT, `Timestamp` DATETIME, `reading` DOUBLE);
CREATE INDEX `sensor_index` ON Readings (`sensorID`);

CREATE TABLE Log( `timestamp` TIMESTAMP, `type` INT, `message` TEXT);

INSERT INTO Rooms (`name`,`shortname`) VALUES ('101','101'),('102','102'),('210','210'),('211','211'),('215 - Studovna','215')('000','Venku');

INSERT INTO Devices (`roomID`, `name`, `description`, `mqttTopic`) VALUES
                    (1, "Raspberry Pi 101", "Main controller for room 101", "101/device"),
                    (3, "Raspberry Pi 210", "Main controller for room 210", "210/device"),
                    (5, "Raspberry Pi 215", "Main controller for room 215", "215/device"),
                    (6, "Outdoor Sensor", "Outdoor temperature sensor", "000/device");

INSERT INTO Sensors (`type`, `deviceID`,`mqttTopic`,`name`) VALUES (0,1,"101/floortemp", "Floor Temperatue"),(0,1,"101/ceiltemp", "Ceiling Temperature"),(1,1,"101/humidity", "Room Humidity"),
                    (0,3,"210/temp", "Whole Room Temperature"),(0,5,"215/temp", "Whole Room Temperature"),(1,5,"215/humidity", "Whole Room Humidity"),(0,4,"000/temp", "Outdoor Temperature");
INSERT INTO Types (type_desc) VALUES ("Temperature"), ('Humidity');
INSERT INTO `readings`(`Timestamp`,`reading`,`sensorID`) VALUES
    ('2025-11-12 00:00:00',25.7,1),('2025-11-12 01:00:00',26.7,1),('2025-11-12 02:00:00',27.4,1),('2025-11-12 02:04:00',28.0,1),('2025-11-12 03:22:00',28.2,1),
    ('2025-11-12 00:00:00',22.7,2),('2025-11-12 01:00:00',23.4,2),('2025-11-12 02:00:00',23.9,2),('2025-11-12 02:05:00',25.5,2),('2025-11-12 03:20:00',27.7,2),
    ('2025-11-12 00:00:00',88.2,3),('2025-11-12 00:30:00',77.2,3),('2025-11-12 01:02:00',69.8,3),('2025-11-12 01:25:00',65.1,3),('2025-11-12 02:02:00',58.2,3);
INSERT INTO `permissions`(`userID`,`roomID`,`view`) VALUES (1,1,1),(1,2,1),(1,3,1);

# other useful queries
SELECT reading, Timestamp FROM Readings WHERE `sensorID` = 1 ORDER BY `Timestamp` DESC LIMIT 10000;
# SELECT roomID FROM permissions WHERE `userID` = 1 AND `view` = 1 INNER JOIN Rooms ON `permissions`.`roomID` = `Rooms`.`ID`;

SELECT rooms.name, rooms.shortname from permissions INNER JOIN rooms ON permissions.`roomID` = rooms.`ID` WHERE permissions.`userID` = 1 AND permissions.`view` = 1;

SELECT rooms.`ID` from rooms WHERE rooms.shortname = '101';

SELECT Sensors.`ID` from Sensors LEFT JOIN rooms ON Sensors.`roomID` = rooms.ID WHERE rooms.shortname = '101';
SELECT types.`type_desc` from types LEFT JOIN Sensors ON types.`ID` = Sensors.`type` WHERE Sensors.`ID` = 1;

SELECT permissions.`view` FROM permissions LEFT JOIN rooms ON permissions.`roomID` = rooms.ID WHERE rooms.shortname = '101' AND `userID` = 1;

# get latest reading from a sensor
SELECT readings.`Timestamp`, readings.`reading` FROM readings WHERE readings.`sensorID` = '1' ORDER BY readings.`Timestamp` DESC FETCH FIRST 1 ROWS ONLY;

Select sensors.`ID`, sensors.`Type` FROM sensors LEFT JOIN devices ON devices.`ID` = sensors.`deviceID` LEFT JOIN rooms ON devices.`roomID` = rooms.`ID` WHERE rooms.shortname = "101";

SELECT users.`ID`, users.`username`, users.`administer` FROM Users;
