-- MySQL Temperature setup schema

-- Run from command line:
-- mysql -u root -p < temp_db.sql



-- Create database, choose name
--
-- Database : `temp_db`
-- drop database if exists temp_db;
CREATE DATABASE IF NOT EXISTS `temp_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE temp_db;



-- Create user; change user, password and database name

-- create user 'temp_user'@'%' identified by 'temp_pass';
create user 'temp_user'@'localhost' identified by 'temp_pass';
-- GRANT ALL PRIVILEGES ON temp_user.* TO 'temp_pass'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON temp_user.* TO 'temp_pass'@'localhost' WITH GRANT OPTION;



-- Create temperature databases

--DROP TABLE if exists nattsjo_outdoor1;
create table if not exists nattsjo_outdoor1 (id INTEGER UNSIGNED not null auto_increment, time DATETIME, temp FLOAT, primary key (id));

--DROP TABLE if exists nattsjo_indoor1;
create table not exists nattsjo_indoor1 (id INTEGER UNSIGNED not null auto_increment, time DATETIME, temp FLOAT, primary key (id));

--DROP TABLE if exists nattsjo_ack1;
create table not exists nattsjo_ack1 (id INTEGER UNSIGNED not null auto_increment, time DATETIME, temp FLOAT, primary key (id));

--DROP TABLE if exists nattsjo_solarin;
create table not exists nattsjo_solarin (id INTEGER UNSIGNED not null auto_increment, time DATETIME, temp FLOAT, primary key (id));

--DROP TABLE if exists nattsjo_solarout;
create table not exists nattsjo_solarout (id INTEGER UNSIGNED not null auto_increment, time DATETIME, temp FLOAT, primary key (id));



-- Create relays scheduler

--DROP TABLE if exists nattsjo_relays_cron;
CREATE TABLE if not exists nattsjo_relays_cron (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, relay INTEGER, hour SMALLINT UNSIGNED, minute SMALLINT UNSIGNED, PRIMARY KEY(id));

-- Example definitions
--INSERT INTO nattsjo_relays_cron VALUES (null, 1, 1, 0);
--INSERT INTO nattsjo_relays_cron VALUES (null, 3, 1, 0);
--INSERT INTO nattsjo_relays_cron VALUES (null, 2, 15, 0);
--INSERT INTO nattsjo_relays_cron VALUES (null, 4, 15, 0);


--DROP TABLE if exists nattsjo_events_log;
create table if not exists nattsjo_events_log (id INTEGER UNSIGNED not null auto_increment, time DATETIME, td_event INTEGER UNSIGNED, method INTEGER UNSIGNED, primary key (id));

--SELECT l.time, d.descr FROM nattsjo_events_log l, nattsjo_events_defs d WHERE l.method = d.method AND l.td_event = d.td_event;
--INSERT INTO nattsjo_events_log VALUES (null, now(), $id, $method):          



-- +----------+---------------------+------+-----+---------+----------------+
-- | Field    | Type                | Null | Key | Default | Extra          |
-- +----------+---------------------+------+-----+---------+----------------+
-- | id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
-- | type     | tinyint(3) unsigned | YES  |     | NULL    |                |
-- | td_event | int(10) unsigned    | YES  |     | NULL    |                |
-- | method   | int(10) unsigned    | YES  |     | NULL    |                |
-- | descr    | varchar(35)         | YES  |     | NULL    |                |
-- | action   | varchar(65)         | YES  |     | NULL    |                |
-- +----------+---------------------+------+-----+---------+----------------+


-- Type 0: macro
-- Type 1: relay
-- type 2: remote

-- td_event: Tellstick ID

--//Device methods
--#define TELLSTICK_TURNON        1
--#define TELLSTICK_TURNOFF       2
--#define TELLSTICK_BELL          4
--#define TELLSTICK_TOGGLE        8
--#define TELLSTICK_DIM           16
--#define TELLSTICK_LEARN         32
--#define TELLSTICK_EXECUTE       64
--#define TELLSTICK_UP            128
--#define TELLSTICK_DOWN          256
--#define TELLSTICK_STOP          512


DROP TABLE if exists nattsjo_devices;
CREATE TABLE if not exists nattsjo_devices (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, type TINYINT UNSIGNED, td_event INTEGER UNSIGNED, method INTEGER UNSIGNED, descr VARCHAR(35), action VARCHAR(65), PRIMARY KEY(id)) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

-- Relays and macro
INSERT INTO nattsjo_devices VALUES (null, 1, 1, 1, "Kök övervåning ON", "/root/bin/tdtool.sh --on 1");
INSERT INTO nattsjo_devices VALUES (null, 1, 1, 2, "Kök övervåning OFF", "/root/bin/tdtool.sh --off 1");    
INSERT INTO nattsjo_devices VALUES (null, 1, 2, 1, "Hall övervåning ON", "/root/bin/tdtool.sh --on 2");
INSERT INTO nattsjo_devices VALUES (null, 1, 2, 2, "Hall övervåning OFF", "/root/bin/tdtool.sh --off 2");
INSERT INTO nattsjo_devices VALUES (null, 0, 0, 0, "Starta om mätutrustning", "/root/reboot_pannrum.sh");
INSERT INTO nattsjo_devices VALUES (null, 0, 0, 1, "Elpatron ON", "/root/elpatron.pl on");
INSERT INTO nattsjo_devices VALUES (null, 0, 0, 2, "Elpatron OFF", "/root/elpatron.pl off");
INSERT INTO nattsjo_devices VALUES (null, 1, 4, 1, "Utebelysning huvudentre ON", "/root/bin/tdtool.sh --dimlevel 255 --dim 4");
INSERT INTO nattsjo_devices VALUES (null, 1, 4, 2, "Utebelysning huvudentre OFF", "/root/bin/tdtool.sh --off 4");
INSERT INTO nattsjo_devices VALUES (null, 1, 5, 1, "Solpanel ON",  "/root/bin/tdtool.sh --on 5");
INSERT INTO nattsjo_devices VALUES (null, 1, 5, 2, "Solpanel OFF", "/root/bin/tdtool.sh --off 5");
INSERT INTO nattsjo_devices VALUES (null, 1, 6, 1, "Hall huvudentre ON", "/root/bin/tdtool.sh --on 6");
INSERT INTO nattsjo_devices VALUES (null, 1, 6, 2, "Hall huvudentre OFF", "/root/bin/tdtool.sh --off 6");
INSERT INTO nattsjo_devices VALUES (null, 1, 7, 1, "Tavla vardagsrum ON", "/root/bin/tdtool.sh --on 7");
INSERT INTO nattsjo_devices VALUES (null, 1, 7, 2, "Tavla vardagsrum OFF", "/root/bin/tdtool.sh --off 7");
INSERT INTO nattsjo_devices VALUES (null, 0, 0, 1, "Fullvärme", "/root/bin/set_triggers.pl 170 200");
INSERT INTO nattsjo_devices VALUES (null, 0, 0, 1, "Mediumvärme", "/root/bin/set_triggers.pl 120 160");
INSERT INTO nattsjo_devices VALUES (null, 0, 0, 1, "Underhållsvärme", "/root/bin/set_triggers.pl 60 100");
INSERT INTO nattsjo_devices VALUES (null, 1, 9, 1, "Sovrum ON", "/root/bin/tdtool.sh --on 9");
INSERT INTO nattsjo_devices VALUES (null, 1, 9, 2, "Sovrum OFF", "/root/bin/tdtool.sh --off 9");
INSERT INTO nattsjo_devices VALUES (null, 0, 0, 1, "Värme konstant ON ", "/root/bin/set_triggers.pl 220 300");
INSERT INTO nattsjo_devices VALUES (null, 0, 0, 1, "Alla lampor ON", "/root/bin/allalampor.sh on");
INSERT INTO nattsjo_devices VALUES (null, 0, 0, 2, "Alla lampor OFF", "/root/bin/allalampor.sh off");
INSERT INTO nattsjo_devices VALUES (null, 1, 8, 1, "Fönster vardagsrum ON", "/root/bin/tdtool.sh --on 8");
INSERT INTO nattsjo_devices VALUES (null, 1, 8, 2, "Fönster vardagsrum OFF", "/root/bin/tdtool.sh --off 8");

-- Remote
INSERT INTO nattsjo_devices VALUES (null, 2, 11, 1, "Nexafjärr 1:1 ON", "/usr/bin/tdtool --on 1");
INSERT INTO nattsjo_devices VALUES (null, 2, 11, 2, "Nexafjärr 1:1 OFF", "/usr/bin/tdtool --off 1");
INSERT INTO nattsjo_devices VALUES (null, 2, 12, 1, "Nexafjärr 1:2 ON", "/usr/bin/tdtool --on 2");
INSERT INTO nattsjo_devices VALUES (null, 2, 12, 2, "Nexafjärr 1:2 OFF", "/usr/bin/tdtool --off 2");
INSERT INTO nattsjo_devices VALUES (null, 2, 13, 1, "Nexafjärr 1:3 ON", "/usr/bin/tdtool --on 6");
INSERT INTO nattsjo_devices VALUES (null, 2, 13, 2, "Nexafjärr 1:3 OFF", "/usr/bin/tdtool --off 6");
INSERT INTO nattsjo_devices VALUES (null, 2, 14, 1, "Nexafjärr 1:4 ON", "/usr/bin/tdtool --on 7");
INSERT INTO nattsjo_devices VALUES (null, 2, 14, 2, "Nexafjärr 1:4 OFF", "/usr/bin/tdtool --off 7");
INSERT INTO nattsjo_devices VALUES (null, 2, 21, 1, "Nexafjärr 2:1 ON", "/usr/bin/tdtool --on 9");
INSERT INTO nattsjo_devices VALUES (null, 2, 21, 2, "Nexafjärr 2:1 OFF", "/usr/bin/tdtool --off 9");
INSERT INTO nattsjo_devices VALUES (null, 2, 22, 1, "Nexafjärr 2:2 ON", "/usr/bin/tdtool --dimlevel 255 --dim 4");
INSERT INTO nattsjo_devices VALUES (null, 2, 22, 2, "Nexafjärr 2:2 OFF", "/usr/bin/tdtool --off 4");
INSERT INTO nattsjo_devices VALUES (null, 2, 23, 1, "Nexafjärr 2:3 Fönster vardagsrum ON", "/usr/bin/tdtool --on 8");  
INSERT INTO nattsjo_devices VALUES (null, 2, 23, 2, "Nexafjärr 2:3 Fönster vardagsrum OFF", "/usr/bin/tdtool --off 8");
INSERT INTO nattsjo_devices VALUES (null, 2, 31, 1, "Nexafjärr 3:1 ON", "/usr/bin/tdtool --on 5");
INSERT INTO nattsjo_devices VALUES (null, 2, 31, 2, "Nexafjärr 3:1 OFF", "/usr/bin/tdtool --off 5");
INSERT INTO nattsjo_devices VALUES (null, 2, 15, 1, "Dörr huvudentré öppnas (ON)", "/root/bin/log_event.pl 15 1");
INSERT INTO nattsjo_devices VALUES (null, 2, 15, 2, "Dörr huvudentré stängs (OFF)", "/root/bin/log_event.pl 15 2");
INSERT INTO nattsjo_devices VALUES (null, 2, 16, 1, "Köksingång öppnas (ON)", "/root/bin/log_event.pl 16 1");
INSERT INTO nattsjo_devices VALUES (null, 2, 16, 2, "Köksingång stängs (OFF)", "/root/bin/log_event.pl 16 2");
INSERT INTO nattsjo_devices VALUES (null, 2, 20, 1, "Vägpassage", "/root/bin/log_event.pl 20 1");
INSERT INTO nattsjo_devices VALUES (null, 2, 32 ,1, "Nexafjärr 3:2 ON", "ssh n_rpi '/usr/bin/tdtool --on 3'");
INSERT INTO nattsjo_devices VALUES (null, 2, 32 ,2, "Nexafjärr 3:2 OFF", "ssh n_rpi '/usr/bin/tdtool --off 3'");