text: /*
    This script should populate the database with some data that we can use 
    to test out the supporting code and ensure that we get realistic data 
  */
  
    /*Create a test experiment */
  INSERT INTO test.experiment(experiment_id, start_date, end_date, owner)
    VALUES ('E_001', CURDATE()-1, CURDATE()+10, 'Dr Test'); 
  
    /*Creating a test host (bench 15 is the tester) */ 
  INSERT INTO test.hosts(host_name)
    VALUES('1'),
    ('2'),
    ('3'),
    ('4'),
    ('5'),
    ('6'); 
  
    /*Create 1-96 for testing with */
  INSERT INTO test.balances(balance_id, cable_id, address, pi_assigned)
  VALUES('1','1', 'usb-FTDI_USB-RS232_Cable_FTWL23KO-if00-port0','1'),
  ('2','2', 'usb-FTDI_USB-RS232_Cable_FTWLD8KD-if00-port0','1'),
  ('3','3', 'usb-FTDI_USB-RS232_Cable_FTWL23RK-if00-port0','1'),
  ('4','4', 'usb-FTDI_USB-RS232_Cable_FTWLDJOH-if00-port0','1'),
  ('5','5', 'usb-FTDI_USB-RS232_Cable_FTWLDSPA-if00-port0','1'),
  ('6','6', 'usb-FTDI_USB-RS232_Cable_FTWLAF8I-if00-port0','1'),
  ('7','7', 'usb-FTDI_USB-RS232_Cable_FTWLDTO6-if00-port0','1'),
  ('8','8', 'usb-FTDI_USB-RS232_Cable_FTWLDAJP-if00-port0','1'),
  ('9','9', 'usb-FTDI_USB-RS232_Cable_FTWLDE24-if00-port0','1'),
  ('10','10', 'usb-FTDI_USB-RS232_Cable_FTWL2H69-if00-port0','1'),
  ('11','11', 'usb-FTDI_USB-RS232_Cable_FTWL2GLC-if00-port0','1'),
  ('12','12', 'usb-FTDI_USB-RS232_Cable_FTWL2J7L-if00-port0','1'),
  ('13','13', 'usb-FTDI_USB-RS232_Cable_FTWL21OB-if00-port0','1'),
  ('14','14', 'usb-FTDI_USB-RS232_Cable_FTWL2ID6-if00-port0','1'),
  ('15','15', 'usb-FTDI_USB-RS232_Cable_FTWL23LB-if00-port0','1'),
  ('16','16', 'usb-FTDI_USB-RS232_Cable_FTWLDAP2-if00-port0','1'),
  ('17','1', 'usb-FTDI_USB-RS232_Cable_FTWLD6AN-if00-port0','2'),
  ('18','2', 'usb-FTDI_USB-RS232_Cable_FTWKZPNN-if00-port0','2'),
  ('19','3', 'usb-FTDI_USB-RS232_Cable_FTWLDDZY-if00-port0','2'),
  ('20','4', 'usb-FTDI_USB-RS232_Cable_FTWL2GWC-if00-port0','2'),
  ('21','5', 'usb-FTDI_USB-RS232_Cable_FTWLD66F-if00-port0','2'),
  ('22','6', 'usb-FTDI_USB-RS232_Cable_FTWLDJBA-if00-port0','2'),
  ('23','7', 'usb-FTDI_USB-RS232_Cable_FTWLD9TE-if00-port0','2'),
  ('24','8', 'usb-FTDI_USB-RS232_Cable_FTWLDTA0-if00-port0','2'),
  ('25','9', 'usb-FTDI_USB-RS232_Cable_FTWKZPRN-if00-port0','2'),
  ('26','10', 'usb-FTDI_USB-RS232_Cable_FTWLAITZ-if00-port0','2'),
  ('27','11', 'usb-FTDI_USB-RS232_Cable_FTWLDE2I-if00-port0','2'),
  ('28','12', 'usb-FTDI_USB-RS232_Cable_FTWLDT75-if00-port0','2'),
  ('29','13', 'usb-FTDI_USB-RS232_Cable_FTWLWS4P-if00-port0','2'),
  ('30','14', 'usb-FTDI_USB-RS232_Cable_FTWLAIHR-if00-port0','2'),
  ('31','15', 'usb-FTDI_USB-RS232_Cable_FTWLADPB-if00-port0','2'),
  ('32','16', 'usb-FTDI_USB-RS232_Cable_FTWLD8UY-if00-port0','2'),
  ('33','1', 'usb-FTDI_USB-RS232_Cable_FTWLD77S-if00-port0','3'),
  ('34','2', 'usb-FTDI_USB-RS232_Cable_FTWKZO6D-if00-port0','3'),
  ('35','3', 'usb-FTDI_USB-RS232_Cable_FTWLADMO-if00-port0','3'),
  ('36','4', 'usb-FTDI_USB-RS232_Cable_FTWL2EH3-if00-port0','3'),
  ('37','5', 'usb-FTDI_USB-RS232_Cable_FTWLAJ2K-if00-port0','3'),
  ('38','6', 'usb-FTDI_USB-RS232_Cable_FTWLDJLX-if00-port0','3'),
  ('39','7', 'usb-FTDI_USB-RS232_Cable_FTWLADR7-if00-port0','3'),
  ('40','8', 'usb-FTDI_USB-RS232_Cable_FTWLD6A8-if00-port0','3'),
  ('41','9', 'usb-FTDI_USB-RS232_Cable_FTWLDT5Y-if00-port0','3'),
  ('42','10', 'usb-FTDI_USB-RS232_Cable_FTWLD7IL-if00-port0','3'),
  ('43','11', 'usb-FTDI_USB-RS232_Cable_FTWL21N1-if00-port0','3'),
  ('44','12', 'usb-FTDI_USB-RS232_Cable_FTWKZQEC-if00-port0','3'),
  ('45','13', 'usb-FTDI_USB-RS232_Cable_FTWLD3W8-if00-port0','3'),
  ('46','14', 'usb-FTDI_USB-RS232_Cable_FTWLDGG9-if00-port0','3'),
  ('47','15', 'usb-FTDI_USB-RS232_Cable_FTWLAHLW-if00-port0','3'),
  ('48','16', 'usb-FTDI_USB-RS232_Cable_FTWL231T-if00-port0','3'),
  ('49','1', 'usb-FTDI_USB-RS232_Cable_FTWLDDZ6-if00-port0','4'),
  ('50','2', 'usb-FTDI_USB-RS232_Cable_FTWLDH2W-if00-port0','4'),
  ('51','3', 'usb-FTDI_USB-RS232_Cable_FTWL1Z9T-if00-port0','4'),
  ('52','4', 'usb-FTDI_USB-RS232_Cable_FTWLWRWX-if00-port0','4'),
  ('53','5', 'usb-FTDI_USB-RS232_Cable_FTWLDAS7-if00-port0','4'),
  ('54','6', 'usb-FTDI_USB-RS232_Cable_FTWLAHII-if00-port0','4'),
  ('55','7', 'usb-FTDI_USB-RS232_Cable_FTWLD8CI-if00-port0','4'),
  ('56','8', 'usb-FTDI_USB-RS232_Cable_FTWLDQ2P-if00-port0','4'),
  ('57','9', 'usb-FTDI_USB-RS232_Cable_FTWL2IX7-if00-port0','4'),
  ('58','10', 'usb-FTDI_USB-RS232_Cable_FTWLDT8H-if00-port0','4'),
  ('59','11', 'usb-FTDI_USB-RS232_Cable_FTWL2CIF-if00-port0','4'),
  ('60','12', 'usb-FTDI_USB-RS232_Cable_FTWLAOQD-if00-port0','4'),
  ('61','13', 'usb-FTDI_USB-RS232_Cable_FTWLD9OP-if00-port0','4'),
  ('62','14', 'usb-FTDI_USB-RS232_Cable_FTWLD9EE-if00-port0','4'),
  ('63','15', 'usb-FTDI_USB-RS232_Cable_FTWLAD9V-if00-port0','4'),
  ('64','16', 'usb-FTDI_USB-RS232_Cable_FTWLDSQA-if00-port0','4'),
  ('65','1', 'usb-FTDI_USB-RS232_Cable_FTWLD7AC-if00-port0','5'),
  ('66','2', 'usb-FTDI_USB-RS232_Cable_FTWL240F-if00-port0','5'),
  ('67','3', 'usb-FTDI_USB-RS232_Cable_FTWL23P2-if00-port0','5'),
  ('68','4', 'usb-FTDI_USB-RS232_Cable_FTWL2J0H-if00-port0','5'),
  ('69','5', 'usb-FTDI_USB-RS232_Cable_FTWLDHG9-if00-port0','5'),
  ('70','6', 'usb-FTDI_USB-RS232_Cable_FTWLD8NX-if00-port0','5'),
  ('71','7', 'usb-FTDI_USB-RS232_Cable_FTWLD993-if00-port0','5'),
  ('72','8', 'usb-FTDI_USB-RS232_Cable_FTWL22BB-if00-port0','5'),
  ('73','9', 'usb-FTDI_USB-RS232_Cable_FTWLD9KM-if00-port0','5'),
  ('74','10', 'usb-FTDI_USB-RS232_Cable_FTWLDSWY-if00-port0','5'),
  ('75','11', 'usb-FTDI_USB-RS232_Cable_FTWLDHR3-if00-port0','5'),
  ('76','12', 'usb-FTDI_USB-RS232_Cable_FTWLDSP7-if00-port0','5'),
  ('77','13', 'usb-FTDI_USB-RS232_Cable_FTWLANYO-if00-port0','5'),
  ('78','14', 'usb-FTDI_USB-RS232_Cable_FTWLDTI6-if00-port0','5'),
  ('79','15', 'usb-FTDI_USB-RS232_Cable_FTWL22FC-if00-port0','5'),
  ('80','16', 'usb-FTDI_USB-RS232_Cable_FTWLDE1A-if00-port0','5'),
  ('81','1', 'usb-FTDI_USB-RS232_Cable_FTWLD8BK-if00-port0','6'),
  ('82','2', 'usb-FTDI_USB-RS232_Cable_FTWL23TN-if00-port0','6'),
  ('83','3', 'usb-FTDI_USB-RS232_Cable_FTWLD9H0-if00-port0','6'),
  ('84','4', 'usb-FTDI_USB-RS232_Cable_FTWLDE0C-if00-port0','6'),
  ('85','5', 'usb-FTDI_USB-RS232_Cable_FTWLDGYB-if00-port0','6'),
  ('86','6', 'usb-FTDI_USB-RS232_Cable_FTWLDGS9-if00-port0','6'),
  ('87','7', 'usb-FTDI_USB-RS232_Cable_FTWKZOI1-if00-port0','6'),
  ('88','8', 'usb-FTDI_USB-RS232_Cable_FTWL21E2-if00-port0','6'),
  ('89','9', 'usb-FTDI_USB-RS232_Cable_FTWL23MV-if00-port0','6'),
  ('90','10', 'usb-FTDI_USB-RS232_Cable_FTWLDJW8-if00-port0','6'),
  ('91','11', 'usb-FTDI_USB-RS232_Cable_FTWL2J4Q-if00-port0','6'),
  ('92','12', 'usb-FTDI_USB-RS232_Cable_FTWLDHTE-if00-port0','6'),
  ('93','13', 'usb-FTDI_USB-RS232_Cable_FTWL22C6-if00-port0','6'),
  ('94','14', 'usb-FTDI_USB-RS232_Cable_FTWLDDJV-if00-port0','6'),
  ('95','15', 'usb-FTDI_USB-RS232_Cable_FTWLDSRX-if00-port0','6'),
  ('96','16', 'usb-FTDI_USB-RS232_Cable_FTWLD91K-if00-port0','6');
  
    /*Create a few test plants to play with! */
  INSERT INTO test.plants(plant_id, experiment_id, target_weight)
    VALUES('M_001', 'E_001', '650'),
          ('M_002', 'E_001', '400'),
          ('M_003', 'E_001', '200'),
          ('M_004', 'E_001', '200'),
          ('M_005', 'E_001', '530'),
          ('M_006', 'E_001', '510'),
  	('M_007', 'E_001', '650'),
          ('M_008', 'E_001', '400'),
          ('M_009', 'E_001', '200'),
          ('M_010', 'E_001', '200'),
    	('M_011', 'E_001', '510'),
  	('M_012', 'E_001', '200'),
    	('M_013', 'E_001', '510');
  
    /*Generate some balance readings*/ 
  INSERT INTO test.balance_data(balance_id, logdate, weight, experiment_id)
    VALUES('1', CURDATE(), '100', 'E_001' ),
          ('2', CURDATE(), '10', 'E_001' ),
          ('3', CURDATE(), '600', 'E_001' ),
          ('4', CURDATE()+1, '100', 'E_001' ),
          ('5', CURDATE()+1, '200', 'E_001' ),
          ('6', CURDATE()+1, '200', 'E_001' ),
          ('7', CURDATE()+2, '400', 'E_001' );
  
  
  INSERT INTO test.watering_data(balance_id, logdate, start_weight,
  end_weight, status, experiment_id)
    VALUES('1', CURDATE(), '10', '650', '1', 'E_001'),
          ('2', CURDATE(), '10', '400', '1', 'E_001'),
          ('3', CURDATE(), '10', '200', '1', 'E_001'),
          ('4', CURDATE()+1, '10', '200', '1', 'E_001'),
          ('5', CURDATE()+1, '10', '530', '0', 'E_001'),
          ('6', CURDATE(), '10', '510', '1', 'E_001');
  
  /*
    Inserts into the plant to balance linker, with a slight twist
    On plant 6 we have a switch of balances to play up things 
  */
  INSERT INTO test.plants_to_balance(start_date, end_date, plant_id, balance_id)
    VALUES(curdate()-1, NULL, 'M_001', '1'),
          (curdate()-1, NULL, 'M_002', '2'),
          (curdate()-1, NULL, 'M_003', '3'),
          (curdate()-1, NULL, 'M_004', '4'),
          (curdate()-1, NULL, 'M_005', '5'),
          (curdate()-3, curdate()-2, 'M_006', '6'),
          (curdate()-1, NULL, 'M_006', '7' ),
  	(curdate()-1, NULL, 'M_007', '20'),
          (curdate()-1, NULL, 'M_008', '19'),
          (curdate()-1, NULL, 'M_009', '50'),
          (curdate()-1, NULL, 'M_010', '33'),
          (curdate()-1, NULL, 'M_011', '22'),
          (curdate()-3, curdate()-2, 'M_012', '15'),
          (curdate()-1, NULL, 'M_013', '80' );
    
   
  INSERT INTO test.watering_valves(gpio_pin, balance_id)
    VALUES('/sys/class/gpio/gpio26/value', '  1'), 
          ('/sys/class/gpio/gpio19/value', '  2'), 
          ('/sys/class/gpio/gpio13/value', '  3'), 
          ('/sys/class/gpio/gpio6/value' , '  4'), 
          ('/sys/class/gpio/gpio21value' , '  5'), 
          ('/sys/class/gpio/gpio20/value', '  6'), 
          ('/sys/class/gpio/gpio16/value', '  7'), 
  	('/sys/class/gpio/gpio12/value', '  8'), 
  	('/sys/class/gpio/gpio14/value', '  9'), 
          ('/sys/class/gpio/gpio15/value', ' 10'), 
          ('/sys/class/gpio/gpio18/value', ' 11'), 
          ('/sys/class/gpio/gpio23/value', ' 12'), 
          ('/sys/class/gpio/gpio4/value' , ' 13'), 
          ('/sys/class/gpio/gpio2/value' , ' 14'), 
          ('/sys/class/gpio/gpio3/value' , ' 15'), 
  	('/sys/class/gpio/gpio17/value', ' 16'), 
  	('/sys/class/gpio/gpio26/value', ' 17'), 
          ('/sys/class/gpio/gpio19/value', ' 18'), 
          ('/sys/class/gpio/gpio13/value', ' 19'), 
          ('/sys/class/gpio/gpio6/value' , ' 20'), 
          ('/sys/class/gpio/gpio21value' , ' 21'), 
          ('/sys/class/gpio/gpio20/value', ' 22'), 
          ('/sys/class/gpio/gpio16/value', ' 23'), 
  	('/sys/class/gpio/gpio12/value', ' 24'), 
  	('/sys/class/gpio/gpio14/value', ' 25'), 
          ('/sys/class/gpio/gpio15/value', ' 26'), 
          ('/sys/class/gpio/gpio18/value', ' 27'), 
          ('/sys/class/gpio/gpio23/value', ' 28'), 
          ('/sys/class/gpio/gpio4/value' , ' 29'), 
          ('/sys/class/gpio/gpio2/value' , ' 30'), 
          ('/sys/class/gpio/gpio3/value' , ' 31'), 
  	('/sys/class/gpio/gpio17/value', ' 32'), 
  	('/sys/class/gpio/gpio26/value', ' 33'), 
          ('/sys/class/gpio/gpio19/value', ' 34'), 
          ('/sys/class/gpio/gpio13/value', ' 35'), 
          ('/sys/class/gpio/gpio6/value' , ' 36'), 
          ('/sys/class/gpio/gpio21value' , ' 37'), 
          ('/sys/class/gpio/gpio20/value', ' 38'), 
          ('/sys/class/gpio/gpio16/value', ' 39'), 
  	('/sys/class/gpio/gpio12/value', ' 40'), 
  	('/sys/class/gpio/gpio14/value', ' 41'), 
          ('/sys/class/gpio/gpio15/value', ' 42'), 
          ('/sys/class/gpio/gpio18/value', ' 43'), 
          ('/sys/class/gpio/gpio23/value', ' 44'), 
          ('/sys/class/gpio/gpio4/value' , ' 45'), 
          ('/sys/class/gpio/gpio2/value' , ' 46'), 
          ('/sys/class/gpio/gpio3/value' , ' 47'), 
  	('/sys/class/gpio/gpio17/value', ' 48'), 
  	('/sys/class/gpio/gpio26/value', ' 49'), 
          ('/sys/class/gpio/gpio19/value', ' 50'), 
          ('/sys/class/gpio/gpio13/value', ' 51'), 
          ('/sys/class/gpio/gpio6/value' , ' 52'), 
          ('/sys/class/gpio/gpio21value' , ' 53'), 
          ('/sys/class/gpio/gpio20/value', ' 54'), 
          ('/sys/class/gpio/gpio16/value', ' 55'), 
  	('/sys/class/gpio/gpio12/value', ' 56'), 
  	('/sys/class/gpio/gpio14/value', ' 57'), 
          ('/sys/class/gpio/gpio15/value', ' 58'), 
          ('/sys/class/gpio/gpio18/value', ' 59'), 
          ('/sys/class/gpio/gpio23/value', ' 60'), 
          ('/sys/class/gpio/gpio4/value' , ' 61'), 
          ('/sys/class/gpio/gpio2/value' , ' 62'), 
          ('/sys/class/gpio/gpio3/value' , ' 63'), 
  	('/sys/class/gpio/gpio17/value', ' 64'), 
  	('/sys/class/gpio/gpio26/value', ' 65'), 
          ('/sys/class/gpio/gpio19/value', ' 66'), 
          ('/sys/class/gpio/gpio13/value', ' 67'), 
          ('/sys/class/gpio/gpio6/value' , ' 68'), 
          ('/sys/class/gpio/gpio21value' , ' 69'), 
          ('/sys/class/gpio/gpio20/value', ' 70'), 
          ('/sys/class/gpio/gpio16/value', ' 71'), 
  	('/sys/class/gpio/gpio12/value', ' 72'), 
  	('/sys/class/gpio/gpio14/value', ' 73'), 
          ('/sys/class/gpio/gpio15/value', ' 74'), 
          ('/sys/class/gpio/gpio18/value', ' 75'), 
          ('/sys/class/gpio/gpio23/value', ' 76'), 
          ('/sys/class/gpio/gpio4/value' , ' 77'), 
          ('/sys/class/gpio/gpio2/value' , ' 78'), 
          ('/sys/class/gpio/gpio3/value' , ' 79'), 
  	('/sys/class/gpio/gpio17/value', ' 80'), 
  	('/sys/class/gpio/gpio26/value', ' 81'), 
          ('/sys/class/gpio/gpio19/value', ' 82'), 
          ('/sys/class/gpio/gpio13/value', ' 83'), 
          ('/sys/class/gpio/gpio6/value' , ' 84'), 
          ('/sys/class/gpio/gpio21value' , ' 85'), 
          ('/sys/class/gpio/gpio20/value', ' 86'), 
          ('/sys/class/gpio/gpio16/value', ' 87'), 
  	('/sys/class/gpio/gpio12/value', ' 88'), 
  	('/sys/class/gpio/gpio14/value', ' 89'), 
          ('/sys/class/gpio/gpio15/value', ' 90'), 
          ('/sys/class/gpio/gpio18/value', ' 91'), 
          ('/sys/class/gpio/gpio23/value', ' 92'), 
          ('/sys/class/gpio/gpio4/value' , ' 93'), 
          ('/sys/class/gpio/gpio2/value' , ' 94'), 
          ('/sys/class/gpio/gpio3/value' , ' 95'), 
  	('/sys/class/gpio/gpio17/value', ' 96'), 
  	('/sys/class/gpio/gpio26/value', ' 97'), 
          ('/sys/class/gpio/gpio19/value', ' 98'), 
          ('/sys/class/gpio/gpio13/value', ' 99'), 
          ('/sys/class/gpio/gpio6/value' , '100'), 
          ('/sys/class/gpio/gpio21value' , '101'), 
          ('/sys/class/gpio/gpio20/value', '102'), 
          ('/sys/class/gpio/gpio16/value', '103'), 
  	('/sys/class/gpio/gpio12/value', '104'), 
  	('/sys/class/gpio/gpio14/value', '105'), 
          ('/sys/class/gpio/gpio15/value', '106'), 
          ('/sys/class/gpio/gpio18/value', '107'), 
          ('/sys/class/gpio/gpio23/value', '108'), 
          ('/sys/class/gpio/gpio4/value' , '109'), 
          ('/sys/class/gpio/gpio2/value' , '110'), 
          ('/sys/class/gpio/gpio3/value' , '111'), 
  	('/sys/class/gpio/gpio17/value', '112'); 
  	
  	
  