#!/bin/bash

#########################################################################################
# Criando tabela do Projeto do Portão:
#########################################################################################

/bin/echo 'SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for portao
-- ----------------------------
DROP TABLE IF EXISTS `portao`;
CREATE TABLE `portao`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `comando` smallint(6) NULL DEFAULT NULL,
  `sync` tinyint(4) NULL DEFAULT 1,
  `datah` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  `email` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;' > /tmp/portao.sql

/bin/cat /tmp/portao.sql | mysql -u root -pmysqllt38c lt38c

#echo "Insert Comando: [1-Abrir, 2-Fechar] lt38c.portao ..."
/usr/bin/mysql -u root -pmysqllt38c -Bse "INSERT INTO lt38c.portao (id,comando,sync,datah,email) VALUES ('','1','1',CURRENT_TIMESTAMP(),'muralha@utfpr.edu.br');"
/usr/bin/mysql -u root -pmysqllt38c -Bse "INSERT INTO lt38c.portao (id,comando,sync,datah,email) VALUES ('','2','1',CURRENT_TIMESTAMP(),'muralha@utfpr.edu.br');"

#echo "Update Sync lt38c.portao ..."
/usr/bin/mysql -u root -pmysqllt38c -Bse "UPDATE lt38c.portao SET sync=0 WHERE id=1;"
/usr/bin/mysql -u root -pmysqllt38c -Bse "UPDATE lt38c.portao SET sync=0 WHERE id=2;"

#########################################################################################
# Criando tabela do Projeto do Arduino:
#########################################################################################

/bin/echo 'SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for arduino
-- ----------------------------
DROP TABLE IF EXISTS `arduino`;
CREATE TABLE `arduino`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `versao` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `firmware` blob NULL,
  `datah` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  `sync` tinyint(4) NULL DEFAULT NULL,
  `email` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `md5sum` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;' > /tmp/arduino.sql

/bin/cat /tmp/arduino.sql | mysql -u root -pmysqllt38c lt38c

# Arquivos Hex:
/usr/bin/wget www.lt38c.hturbo.com/tmp/blink1s.hex -O /tmp/blink1s.hex
/usr/bin/wget www.lt38c.hturbo.com/tmp/blink3s.hex -O /tmp/blink3s.hex
/usr/bin/wget www.lt38c.hturbo.com/tmp/blink5s.hex -O /tmp/blink5s.hex
/bin/sleep 1
# Criar cheksums MD5:
/usr/bin/md5sum /tmp/blink1s.hex > /tmp/blink1s.md5
/usr/bin/md5sum /tmp/blink3s.hex > /tmp/blink3s.md5
/usr/bin/md5sum /tmp/blink5s.hex > /tmp/blink5s.md5
/bin/sleep 1
# Verificar cheksums MD5:
/usr/bin/md5sum -c /tmp/blink1s.md5
/usr/bin/md5sum -c /tmp/blink3s.md5
/usr/bin/md5sum -c /tmp/blink5s.md5

#echo "Insert blobs lt38c.arduino ..."
/usr/bin/mysql -u root -pmysqllt38c -Bse "INSERT INTO lt38c.arduino (id,versao,firmware,datah,sync,email,md5sum) VALUES ('','1.00',LOAD_FILE('/tmp/blink1s.hex'),CURRENT_TIMESTAMP(),'1','muralha@utfpr.edu.br','e52c115ebdc2de4a3b0d2668011e1dce');"
/usr/bin/mysql -u root -pmysqllt38c -Bse "INSERT INTO lt38c.arduino (id,versao,firmware,datah,sync,email,md5sum) VALUES ('','1.01',LOAD_FILE('/tmp/blink3s.hex'),CURRENT_TIMESTAMP(),'1','muralha@utfpr.edu.br','3a697f26bc6c5a64a124048c8cff17cd');"
/usr/bin/mysql -u root -pmysqllt38c -Bse "INSERT INTO lt38c.arduino (id,versao,firmware,datah,sync,email,md5sum) VALUES ('','1.02',LOAD_FILE('/tmp/blink5s.hex'),CURRENT_TIMESTAMP(),'1','muralha@utfpr.edu.br','8c7e74d7dead1665ba01cd0c307f9a60');"

#echo "Select blobs lt38c.arduino ..."
/usr/bin/mysql -u root -pmysqllt38c -Bse "SELECT firmware INTO DUMPFILE '/tmp/dblink1s.hex' FROM lt38c.arduino WHERE id=1;"
/usr/bin/mysql -u root -pmysqllt38c -Bse "SELECT firmware INTO DUMPFILE '/tmp/dblink3s.hex' FROM lt38c.arduino WHERE id=2;"
/usr/bin/mysql -u root -pmysqllt38c -Bse "SELECT firmware INTO DUMPFILE '/tmp/dblink5s.hex' FROM lt38c.arduino WHERE id=3;"

#echo "Update Sync lt38c.arduino ..."
/usr/bin/mysql -u root -pmysqllt38c -Bse "UPDATE lt38c.arduino SET sync=0 WHERE id=1;"
/usr/bin/mysql -u root -pmysqllt38c -Bse "UPDATE lt38c.arduino SET sync=0 WHERE id=2;"
/usr/bin/mysql -u root -pmysqllt38c -Bse "UPDATE lt38c.arduino SET sync=0 WHERE id=3;"

#End