-- phpMyAdmin SQL Dump
-- version 4.6.6deb5
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Feb 13, 2023 at 11:13 PM
-- Server version: 10.3.34-MariaDB-0+deb10u1
-- PHP Version: 7.3.31-1~deb10u1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `robotank`
--

CREATE DATABASE robotank;
USE robotank;

-- --------------------------------------------------------

--
-- Table structure for table `button_labels`
--

CREATE TABLE `button_labels` (
  `buttonID` int(11) NOT NULL,
  `label` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `button_labels`
--

INSERT INTO `button_labels` (`buttonID`, `label`) VALUES
(1, 'Configure Ports'),
(2, 'Dosing Pumps'),
(3, 'Email'),
(4, 'Calibrate Probes'),
(5, 'System'),
(6, 'Customize'),
(7, 'Hardware'),
(8, 'Schedules'),
(9, 'System'),
(10, 'Set Clock'),
(11, 'Equipment Connected'),
(12, 'Temperature Sensors'),
(13, 'Analog Sensors'),
(14, 'DC Accessories'),
(15, 'Dosing Pumps'),
(16, 'Flow Meter'),
(17, 'Automatic Top Off'),
(18, 'Sensor Probes'),
(19, 'Energy Power Bar');

-- --------------------------------------------------------

--
-- Table structure for table `dosingPumps`
--

CREATE TABLE `dosingPumps` (
  `doseID` int(11) NOT NULL,
  `dosePinID` int(11) NOT NULL COMMENT 'connected to pinID in pinsAvailable',
  `doseStirrerID` int(11) NOT NULL,
  `doseName` varchar(55) NOT NULL DEFAULT 'Add Custom Name',
  `doseAmount` int(11) NOT NULL DEFAULT 10 COMMENT 'milliliters',
  `reservoirRemain` float NOT NULL DEFAULT 250 COMMENT 'milliliters',
  `reservoirSize` int(11) NOT NULL DEFAULT 250 COMMENT 'milliliters',
  `dosePumpRate` int(11) NOT NULL DEFAULT 600 COMMENT 'ms/ml',
  `doseStirrer` int(11) NOT NULL DEFAULT 0,
  `stirrerRuntime` mediumint(9) NOT NULL DEFAULT 10 COMMENT 'seconds',
  `settlingRuntime` mediumint(9) NOT NULL DEFAULT 5 COMMENT 'seconds',
  `stirrerSpeed` smallint(6) NOT NULL DEFAULT 4095 COMMENT 'percent',
  `calibrateAmount` int(11) NOT NULL DEFAULT 5 COMMENT 'milliliters',
  `reservoirColor` varchar(12) NOT NULL DEFAULT '#FF0000',
  `doseInterval` int(11) NOT NULL DEFAULT 1 COMMENT 'min delay between doses - minutes',
  `disableRes` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Reservoir Disabled ',
  `pumpSpeed` smallint(6) NOT NULL DEFAULT 4095 COMMENT 'PWM value',
  `resAlert` tinyint(1) NOT NULL DEFAULT 1,
  `resEmail` tinyint(1) NOT NULL DEFAULT 0,
  `resAlertML` mediumint(9) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `dosingSchedules`
--

CREATE TABLE `dosingSchedules` (
  `doseSchID` int(11) NOT NULL,
  `scheduleID` int(11) NOT NULL,
  `mL` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `drivers`
--

CREATE TABLE `drivers` (
  `driverID` int(11) NOT NULL,
  `driverName` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `drivers`
--

INSERT INTO `drivers` (`driverID`, `driverName`) VALUES
(1, 'Dummy'),
(2, 'Raspberry Pi'),
(3, 'PCA9685');

-- --------------------------------------------------------

--
-- Table structure for table `ds18b20`
--

CREATE TABLE `ds18b20` (
  `ds18b20ID` int(11) NOT NULL,
  `id` int(11) NOT NULL DEFAULT 0,
  `address` varchar(30) NOT NULL,
  `name` varchar(60) DEFAULT NULL,
  `plugged` tinyint(1) NOT NULL DEFAULT 0,
  `graphVisible` tinyint(1) NOT NULL DEFAULT 1,
  `graphRange` tinyint(4) NOT NULL DEFAULT 0,
  `graphAverage` smallint(6) NOT NULL DEFAULT 10,
  `min` float DEFAULT 0,
  `max` float DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `errors`
--

CREATE TABLE `errors` (
  `errorID` int(11) NOT NULL,
  `errorSourceID` int(11) NOT NULL,
  `sensor` varchar(50) NOT NULL,
  `message` varchar(200) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `errorsSource`
--

CREATE TABLE `errorsSource` (
  `errorSourceID` int(11) NOT NULL,
  `source` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `errorsSource`
--

INSERT INTO `errorsSource` (`errorSourceID`, `source`) VALUES
(1, 'DS18B20 Sensors'),
(2, 'AC Outlet'),
(3, 'PCA9685'),
(4, 'GPIO');

-- --------------------------------------------------------

--
-- Table structure for table `i2cDevices`
--

CREATE TABLE `i2cDevices` (
  `i2cID` int(11) NOT NULL,
  `i2cDeviceTypeID` int(11) NOT NULL DEFAULT 0,
  `i2cDriverID` int(11) DEFAULT 1,
  `decAddress` int(11) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `new` tinyint(1) NOT NULL DEFAULT 1,
  `running` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `i2cDevices`
--

INSERT INTO `i2cDevices` (`i2cID`, `i2cDeviceTypeID`, `i2cDriverID`, `decAddress`, `enabled`, `new`, `running`) VALUES
(1, 0, 1, 112, 0, 0, 0),
(2, 2, 3, 64, 1, 0, 1),
(3, 0, 1, 104, 0, 0, 0),
(4, 0, 1, 87, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `i2cDeviceTypes`
--

CREATE TABLE `i2cDeviceTypes` (
  `i2cDeviceTypeID` int(11) NOT NULL,
  `type` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `i2cDeviceTypes`
--

INSERT INTO `i2cDeviceTypes` (`i2cDeviceTypeID`, `type`) VALUES
(0, 'not used'),
(1, 'pH'),
(2, 'PCA9685'),
(3, 'Conductivity'),
(4, 'ORP'),
(5, 'Dissolved Oxygen');

-- --------------------------------------------------------

--
-- Table structure for table `lightModes`
--

CREATE TABLE `lightModes` (
  `modeID` int(11) NOT NULL,
  `pinID` int(11) NOT NULL,
  `start` int(11) NOT NULL DEFAULT 86399,
  `startLevel` int(11) NOT NULL DEFAULT 0,
  `enabled` tinyint(4) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

-- --------------------------------------------------------

--
-- Table structure for table `log`
--

CREATE TABLE `log` (
  `logID` int(11) NOT NULL,
  `logSourceID` int(11) NOT NULL,
  `logTypeID` int(11) NOT NULL,
  `logPinID` int(11) NOT NULL,
  `logActionID` int(11) NOT NULL,
  `logTime` bigint(20) DEFAULT NULL,
  `note` varchar(100) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `logAction`
--

CREATE TABLE `logAction` (
  `logActionID` int(11) NOT NULL,
  `logAction` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `logAction`
--

INSERT INTO `logAction` (`logActionID`, `logAction`) VALUES
(0, 'Turn Off'),
(1, 'Turn On'),
(2, 'Rebooted Controller');

-- --------------------------------------------------------

--
-- Table structure for table `logSource`
--

CREATE TABLE `logSource` (
  `logSourceID` int(11) NOT NULL,
  `logSource` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `logSource`
--

INSERT INTO `logSource` (`logSourceID`, `logSource`) VALUES
(1, 'Manual'),
(2, 'Light Mode'),
(3, 'Schedule'),
(4, 'Custom Rule'),
(5, 'Entered Manual Mode'),
(6, 'Resume Auto Mode'),
(7, 'Smart Startup'),
(8, 'Changed Controller Version'),
(9, 'Enabled DS18B20 Port'),
(10, 'Disable DS18B20 Port');

-- --------------------------------------------------------

--
-- Table structure for table `logType`
--

CREATE TABLE `logType` (
  `logTypeID` int(11) NOT NULL,
  `logType` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `logType`
--

INSERT INTO `logType` (`logTypeID`, `logType`) VALUES
(1, 'AC Outlet'),
(2, 'DC Port'),
(3, 'Dosing Pump'),
(4, 'Auto Feeder'),
(5, 'Light'),
(6, 'Sensor Port');

-- --------------------------------------------------------

--
-- Table structure for table `pinsAvailable`
--

CREATE TABLE `pinsAvailable` (
  `pinID` int(11) NOT NULL,
  `driverID` int(11) NOT NULL,
  `gpio` int(11) NOT NULL,
  `assigned` tinyint(1) NOT NULL DEFAULT 0,
  `pinTypeID` int(11) DEFAULT NULL,
  `name` varchar(70) DEFAULT NULL,
  `portName` varchar(70) DEFAULT NULL COMMENT 'DO NOT CHANGE',
  `iconID` int(11) DEFAULT NULL,
  `invert` tinyint(1) NOT NULL DEFAULT 0,
  `manual` tinyint(1) NOT NULL DEFAULT 0,
  `manualStatus` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `pinsAvailable`
--

INSERT INTO `pinsAvailable` (`pinID`, `driverID`, `gpio`, `assigned`, `pinTypeID`, `name`, `portName`, `iconID`, `invert`, `manual`, `manualStatus`) VALUES
(1, 2, 9, 0, 1, 'Power Bar 2 - Outlet 3', 'Power Bar 2 - Outlet 3', 0, 0, 0, 0),
(2, 2, 11, 0, 1, 'Power Bar 2 - Outlet 2', 'Power Bar 2 - Outlet 2', 0, 0, 0, 0),
(3, 2, 10, 0, 1, 'Power Bar 2 - Outlet 4', 'Power Bar 2 - Outlet 4', 0, 0, 0, 0),
(4, 2, 25, 0, 1, 'Power Bar 2 - Outlet 6', 'Power Bar 2 - Outlet 6', 0, 0, 0, 0),
(5, 2, 21, 0, 1, 'Power Bar 1 - Outlet 1', 'Power Bar 1 - Outlet 1', 0, 0, 0, 0),
(6, 2, 20, 0, 1, 'Power Bar 1 - Outlet 2', 'Power Bar 1 - Outlet 2', 0, 0, 0, 0),
(7, 2, 24, 0, 1, 'Power Bar 2 - Outlet 5', 'Power Bar 2 - Outlet 5', 0, 0, 0, 0),
(8, 2, 8, 0, 1, 'Power Bar 2 - Outlet 7', 'Power Bar 2 - Outlet 7', 0, 0, 0, 0),
(9, 2, 7, 0, 1, 'Power Bar 2 - Outlet 8', 'Power Bar 2 - Outlet 8', 0, 0, 0, 0),
(10, 2, 12, 0, 1, 'Power Bar 1 - Outlet 5', 'Power Bar 1 - Outlet 5', 0, 0, 0, 0),
(11, 2, 6, 0, 1, 'Power Bar 1 - Outlet 4', 'Power Bar 1 - Outlet 4', 0, 0, 0, 0),
(12, 2, 5, 0, 1, 'Power Bar 2 - Outlet 1', 'Power Bar 2 - Outlet 1', 0, 0, 0, 0),
(13, 2, 14, 0, 8, 'Sensor Port 1', 'Sensor Port 1', 0, 0, 0, 0),
(14, 2, 22, 0, 5, 'DS18B20', 'DS18B20', 0, 0, 0, 0),
(15, 2, 17, 0, 8, 'Sensor Port 3', 'Sensor Port 3', 0, 0, 0, 0),
(16, 2, 18, 0, 8, 'Sensor Port 4', 'Sensor Port 4', 0, 0, 0, 0),
(17, 2, 23, 0, 8, 'Sensor Port 6', 'Sensor Port 6', 0, 0, 0, 0),
(18, 2, 16, 0, 1, 'Power Bar 1 - Outlet 3', 'Power Bar 1 - Outlet 3', 0, 0, 0, 0),
(19, 2, 13, 0, 1, 'Power Bar 1 - Outlet 6', 'Power Bar 1 - Outlet 6', 0, 0, 0, 0),
(20, 2, 19, 0, 1, 'Power Bar 1 - Outlet 7', 'Power Bar 1 - Outlet 7', 0, 0, 0, 0),
(21, 2, 26, 0, 1, 'Power Bar 1 - Outlet 8', 'Power Bar 1 - Outlet 8', 0, 0, 0, 0),
(22, 2, 27, 0, 8, 'Sensor Port 5', 'Sensor Port 5', 0, 0, 0, 0),
(23, 2, 15, 0, 8, 'Sensor Port 2', 'Sensor Port 2', 0, 0, 0, 0),
(24, 3, 9, 0, 11, 'PWM / Analog Port 1', 'PWM / Analog Port 1', 0, 0, 0, 0),
(25, 3, 8, 0, 11, 'PWM / Analog Port 2', 'PWM / Analog Port 2', 0, 0, 0, 0),
(26, 3, 10, 0, 11, 'PWM / Analog Port 3', 'PWM / Analog Port 3', 0, 0, 0, 0),
(27, 3, 11, 0, 11, 'PWM / Analog Port 4', 'PWM / Analog Port 4', 0, 0, 0, 0),
(28, 3, 12, 0, 11, 'PWM / Analog Port 5', 'PWM / Analog Port 5', 0, 0, 0, 0),
(29, 3, 13, 0, 11, 'PWM / Analog Port 6', 'PWM / Analog Port 6', 0, 0, 0, 0),
(30, 3, 14, 0, 11, 'PWM / Analog Port 7', 'PWM / Analog Port 7', 0, 0, 0, 0),
(31, 3, 15, 0, 11, 'PWM / Analog Port 8', 'PWM / Analog Port 8', 0, 0, 0, 0),
(32, 3, 0, 0, 13, 'Feeder Port', 'Feeder Port', 0, 0, 0, 0),
(33, 3, 5, 0, 2, 'DC Port 7', 'Internal Fan', 0, 0, 0, 0),
(34, 3, 6, 0, 2, 'DC Port 6', 'DC Port 6', 0, 0, 0, 0),
(35, 3, 7, 0, 2, 'DC Port 5', 'DC Port 5', 0, 0, 0, 0),
(36, 3, 4, 0, 2, 'DC Port 4', 'DC Port 4', 0, 0, 0, 0),
(37, 3, 3, 0, 2, 'DC Port 3', 'DC Port 3', 0, 0, 0, 0),
(38, 3, 2, 0, 2, 'DC Port 2', 'DC Port 2', 0, 0, 0, 0),
(39, 3, 1, 0, 2, 'DC Port 1', 'DC Port 1', 0, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `pinsType`
--

CREATE TABLE `pinsType` (
  `pinTypeID` int(11) NOT NULL,
  `pinTypeName` varchar(75) NOT NULL,
  `pinType` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

--
-- Dumping data for table `pinsType`
--

INSERT INTO `pinsType` (`pinTypeID`, `pinTypeName`, `pinType`) VALUES
(1, 'AC Outlet', 0),
(2, 'DC Port', 0),
(3, 'pH Probe', 1),
(4, 'DHT22', 1),
(5, 'DS18B20', 1),
(6, 'Non-Contact Sensor', 1),
(7, 'Optical Sensor', 1),
(8, 'Float Switch', 1),
(9, 'Other Sensor', 1),
(10, 'Flow Meter', 1),
(11, 'Light', 0),
(12, 'Dosing Pump', 0),
(13, 'Auto Feeder', 0),
(14, 'Toggle Switch', 0),
(15, 'Dosing Pump Stirrer', 0);

-- --------------------------------------------------------

--
-- Table structure for table `probes`
--

CREATE TABLE `probes` (
  `probeID` int(11) NOT NULL,
  `name` varchar(60) DEFAULT NULL,
  `address` int(11) NOT NULL,
  `plugged` tinyint(4) NOT NULL,
  `graphVisible` tinyint(4) NOT NULL DEFAULT 1,
  `graphRange` tinyint(4) NOT NULL DEFAULT 2,
  `graphAverage` smallint(6) NOT NULL DEFAULT 10,
  `min` float NOT NULL DEFAULT 0,
  `max` float NOT NULL DEFAULT 0,
  `buffer1` double NOT NULL DEFAULT 7,
  `buffer2` double NOT NULL DEFAULT 10,
  `cal1` double NOT NULL DEFAULT 7,
  `cal2` double NOT NULL DEFAULT 10,
  `calType` tinyint(4) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `rules`
--

CREATE TABLE `rules` (
  `ruleID` int(11) NOT NULL,
  `ruleTypeID` int(11) NOT NULL,
  `ruleName` varchar(255) DEFAULT NULL,
  `email` tinyint(1) DEFAULT 0,
  `alert` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Dashboard Alerts',
  `enabled` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `rulesEquipment`
--

CREATE TABLE `rulesEquipment` (
  `rulesEquipmentID` int(11) NOT NULL,
  `ruleID` int(11) NOT NULL,
  `equipmentPinID` int(11) NOT NULL COMMENT 'pinID from pinsAvailable',
  `equipmentOffOn` tinyint(1) NOT NULL,
  `switchDelay` int(11) NOT NULL DEFAULT 0 COMMENT 'delay until device is switched',
  `nextDoseDelay` int(11) NOT NULL DEFAULT 0 COMMENT 'For Dosing Pumps - minutes',
  `equipIgnore` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'ignore equipment if schedule is running',
  `doseML` float NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `rulesSensors`
--

CREATE TABLE `rulesSensors` (
  `rulesSensorID` int(11) NOT NULL,
  `ruleID` int(11) NOT NULL,
  `sensorPinID` int(11) DEFAULT NULL COMMENT 'pinID from pinsAvailable',
  `sensorAddress` varchar(18) DEFAULT NULL,
  `sensorDataType` smallint(6) NOT NULL DEFAULT 0,
  `sensorValue` double NOT NULL,
  `lessGreater` smallint(1) NOT NULL,
  `stabilitySeconds` int(11) NOT NULL DEFAULT 1,
  `logic` smallint(6) NOT NULL DEFAULT 0 COMMENT 'None, AND, OR'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `rulesType`
--

CREATE TABLE `rulesType` (
  `ruleTypeID` int(11) NOT NULL,
  `ruleType` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `rulesType`
--

INSERT INTO `rulesType` (`ruleTypeID`, `ruleType`) VALUES
(1, 'Schedule'),
(2, 'Sensor'),
(3, 'Manual');

-- --------------------------------------------------------

--
-- Table structure for table `schedules`
--

CREATE TABLE `schedules` (
  `scheduleID` int(11) NOT NULL,
  `scheduleTypeID` int(11) NOT NULL,
  `ruleID` int(11) DEFAULT NULL,
  `pinID` int(11) DEFAULT NULL,
  `offOn` tinyint(1) NOT NULL,
  `schName` varchar(120) NOT NULL,
  `start` int(11) NOT NULL,
  `sun` tinyint(1) NOT NULL DEFAULT 1,
  `mon` tinyint(1) NOT NULL DEFAULT 1,
  `tue` tinyint(1) NOT NULL DEFAULT 1,
  `wed` tinyint(1) NOT NULL DEFAULT 1,
  `thu` tinyint(1) NOT NULL DEFAULT 1,
  `fri` tinyint(1) NOT NULL DEFAULT 1,
  `sat` tinyint(1) NOT NULL DEFAULT 1,
  `repeatDays` smallint(6) NOT NULL DEFAULT 0,
  `disableMaint` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'disabled for maintenance',
  `enabled` int(11) NOT NULL DEFAULT 1,
  `schEmail` tinyint(4) NOT NULL DEFAULT 0,
  `schAlert` tinyint(4) NOT NULL DEFAULT 0,
  `dateCreated` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `scheduleType`
--

CREATE TABLE `scheduleType` (
  `scheduleTypeID` int(11) NOT NULL,
  `type` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `scheduleType`
--

INSERT INTO `scheduleType` (`scheduleTypeID`, `type`) VALUES
(1, 'AC Outlet'),
(2, 'DC Port'),
(3, 'Dosing'),
(4, 'Feeder'),
(5, 'Custom Rule'),
(6, 'Custom Mode');

-- --------------------------------------------------------

--
-- Table structure for table `stirrers`
--

CREATE TABLE `stirrers` (
  `stirrerID` int(11) NOT NULL,
  `stirrerPinID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- --------------------------------------------------------

--
-- Table structure for table `systemSettings`
--

CREATE TABLE `systemSettings` (
  `systemSettingID` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  `configView` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Configure Ports View',
  `tempCorF` tinyint(1) NOT NULL DEFAULT 0,
  `timeFormat` tinyint(4) NOT NULL DEFAULT 1,
  `outletSortID` tinyint(4) NOT NULL DEFAULT 1,
  `dcSortID` tinyint(4) NOT NULL DEFAULT 1,
  `scheduleTabSelected` tinyint(4) NOT NULL DEFAULT 0,
  `scheduleSortID` smallint(6) NOT NULL DEFAULT 0,
  `scheduleSortOrder` tinyint(1) NOT NULL DEFAULT 0,
  `emailEnabled` tinyint(4) NOT NULL DEFAULT 0,
  `smtpServer` varchar(60) DEFAULT NULL,
  `smtpPort` smallint(6) DEFAULT NULL,
  `smtpUser` varchar(60) DEFAULT NULL,
  `smtpPass` varchar(75) DEFAULT NULL,
  `smtpProvider` smallint(6) NOT NULL DEFAULT 1,
  `autoUpdate` tinyint(4) NOT NULL DEFAULT 1,
  `controllerVersion` tinyint(4) NOT NULL DEFAULT 0,
  `version` float NOT NULL,
  `versionLatest` float NOT NULL,
  `updateRan` tinyint(4) NOT NULL DEFAULT 0,
  `supportID` int(11) DEFAULT 0,
  `db9_1` tinyint(4) NOT NULL DEFAULT 0,
  `db9_2` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `systemSettings`
--

INSERT INTO `systemSettings` (`systemSettingID`, `userID`, `configView`, `tempCorF`, `timeFormat`, `outletSortID`, `dcSortID`, `scheduleTabSelected`, `scheduleSortID`, `scheduleSortOrder`, `emailEnabled`, `smtpServer`, `smtpPort`, `smtpUser`, `smtpPass`, `smtpProvider`, `autoUpdate`, `controllerVersion`, `version`, `versionLatest`, `updateRan`, `supportID`, `db9_1`, `db9_2`) VALUES
(1, 1, 0, 1, 1, 1, 1, 0, 2, 1, 0, 'smtp.office365.com', 587, '', '', 0, 0, 2, 6.6, 6.6, 0, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `userID` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `adminEmail` varchar(80) DEFAULT NULL,
  `email1` varchar(80) DEFAULT NULL,
  `email2` varchar(80) DEFAULT 'NULL',
  `email2Enabled` tinyint(1) NOT NULL DEFAULT 0,
  `email3` varchar(80) DEFAULT 'NULL',
  `email3Enabled` tinyint(1) NOT NULL DEFAULT 0,
  `firstRun` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`userID`, `username`, `password`, `adminEmail`, `email1`, `email2`, `email2Enabled`, `email3`, `email3Enabled`, `firstRun`) VALUES
(1, 'Robo-Tank', '$2y$10$prs2O2elGMJHCEIAgOCs.uvMJ9UObZc/HHtw/TJJ0/q59zbZOGy2C', '', '', '', 0, '', 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `version`
--

CREATE TABLE `version` (
  `versionID` int(11) NOT NULL,
  `version` float NOT NULL,
  `versionDate` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `version`
--

INSERT INTO `version` (`versionID`, `version`, `versionDate`) VALUES
(1, 6.1, '2021-10-14'),
(2, 6.2, '2021-12-18'),
(3, 6.3, '2021-12-25'),
(4, 6.4, '2022-03-17'),
(5, 6.5, '2022-04-09'),
(6, 6.6, '2023-02-12');

-- --------------------------------------------------------

--
-- Table structure for table `versionInfo`
--

CREATE TABLE `versionInfo` (
  `versionInfoID` int(11) NOT NULL,
  `versionID` int(11) NOT NULL,
  `versionTypeID` int(11) NOT NULL,
  `change` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `versionInfo`
--

INSERT INTO `versionInfo` (`versionInfoID`, `versionID`, `versionTypeID`, `change`) VALUES
(1, 1, 2, 'Configure Ports - Added Robo-Tank extensions to DB9 accessory types'),
(2, 1, 2, 'Configure Ports - Added all pin functions to advanced tab'),
(3, 1, 3, 'Program - Sensors were being checked when disabled'),
(4, 1, 1, 'Added a toggle switch as a sensor type'),
(5, 1, 2, 'Dashboard - Added sensor status to info boxes'),
(6, 1, 3, 'Custom Rules - Off rules were not saved correctly'),
(8, 2, 3, 'Custom Rules - Added ability to disable/enable dosing pumps'),
(9, 2, 3, 'pH Calibration - Couldn\'t adjust 2nd point value'),
(10, 2, 2, 'Light Modes - Change how modes are created and work'),
(11, 2, 1, 'Added Auto Logout'),
(12, 3, 3, 'Could not access Advanced view on Configure Ports Page'),
(13, 3, 3, 'Could not disable schedules'),
(14, 4, 3, 'DC Ports - When put in manual mode the red dot on icon would disappear after a page refresh although it still remained in manual mode'),
(15, 4, 2, 'System - removed wiringPi library to control GPIOs, now reading/writing GPIOs using sysfs'),
(16, 4, 2, 'Dashboard - changed bottom dock to snap to bottom of browser'),
(17, 4, 1, 'System - Added v2 and v3 controller compatibility'),
(18, 4, 1, 'AC Outlets - Added ability to Invert output logic'),
(19, 4, 1, 'DC Ports - Added ability to Invert output logic'),
(20, 4, 3, 'Setup Wizard - Fixed form to configure new pca9685'),
(21, 4, 3, 'PCA9685 - Fixed bug that wouldn\'t allowing more than 2 PCA9685 modules'),
(22, 4, 1, 'RTC - Added support for DS1307 and DS3231 real time clocks'),
(23, 4, 2, 'Interface - Date and time on webpage now gets updated from backend instead of using computer date and time'),
(24, 4, 2, 'Startup Wizard - Fixed DS18B20 scan and added controller version option'),
(25, 5, 1, 'PCA9685 - Added verification, when pca9685 is updated it is immediately read to verify it updated correctly, if not it tries again'),
(26, 5, 1, 'GPIO - Added verification, when GPIO is updated it is immediately read to verify it updated correctly, if not it tries again'),
(27, 5, 3, 'Browser DateTime - Clock would not show 24 hour format after adding RTC'),
(28, 5, 1, 'System Logs - Added logging system to record all actions'),
(29, 5, 3, 'Schedules - Date added was saved wrong in database after RTC was added'),
(30, 5, 3, 'I2C Scanner - Removed auto scanner as it is not required'),
(31, 5, 3, 'Charts - pH chart table in database wasn\'t being updated'),
(32, 5, 2, 'Charts - charts were still getting timestamp from internet time, changed to controller system time'),
(33, 6, 2, 'Schedules - Updated schedules table for user interface'),
(34, 6, 3, 'Schedules - If schedule was disabled when schedule list was filtered it would not appear disabled in list, only visual error'),
(35, 6, 2, 'Schedules - List of schedules is now updated after confirmation is received from backend, previously it waited 250ms before reloading table'),
(36, 6, 2, 'Schedules - Removed Off option for dosing pumps when creating a schedule'),
(37, 6, 3, 'Schedules - If schedule was created using Repeat Every 1 Day setting the first days schedule would not run'),
(38, 6, 3, 'Schedules - It was possible to add a new schedule without any days selected'),
(39, 6, 1, 'Schedules - Added validation when editing a schedule, was possible to crash program without this'),
(40, 6, 3, 'Custom Rules - If custom rule was for a pH probe it failed to run or disply correctly in UI'),
(42, 6, 2, 'System Logs - Added port custom name to source field'),
(43, 6, 1, 'Version Logs - Added version log to system settings page'),
(46, 6, 1, 'Dosing Pumps - Added ability to create a group of schedules'),
(48, 6, 1, 'Dosing Pumps - Added system for dosing pump stirrers'),
(49, 6, 1, 'Dosing Pumps - Speed of dosing pump can now be adjusted during calibration'),
(50, 6, 1, 'Dosing Pumps - User can opt out using the reservior feature, pump can still run if reservior is empty'),
(51, 6, 1, 'Dosing Pumps - Added alert for low reservior, user can set the low limit in milliliters'),
(52, 6, 2, 'Real Time Clock - Raspberry Pi is now managing the real time clock'),
(53, 6, 3, 'Configure Ports - When saving settings from a DB9 it was likely the program would crash as it was repeating save 8 times'),
(54, 6, 2, 'Custom Rules - Updated custom rules to allow user to specify how many milliliters to dose');

-- --------------------------------------------------------

--
-- Table structure for table `versionType`
--

CREATE TABLE `versionType` (
  `versionTypeID` int(11) NOT NULL,
  `versionType` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `versionType`
--

INSERT INTO `versionType` (`versionTypeID`, `versionType`) VALUES
(1, 'Added'),
(2, 'Updated'),
(3, 'Fixed'),
(4, 'Changed');

--
-- Indexes for dumped tables
--


--
-- Indexes for table `button_labels`
--
ALTER TABLE `button_labels`
  ADD PRIMARY KEY (`buttonID`);

--
-- Indexes for table `dosingPumps`
--
ALTER TABLE `dosingPumps`
  ADD PRIMARY KEY (`doseID`),
  ADD KEY `pinID` (`dosePinID`),
  ADD KEY `doseStirrerID` (`doseStirrerID`);

--
-- Indexes for table `dosingSchedules`
--
ALTER TABLE `dosingSchedules`
  ADD PRIMARY KEY (`doseSchID`),
  ADD KEY `scheduleID` (`scheduleID`);

--
-- Indexes for table `drivers`
--
ALTER TABLE `drivers`
  ADD PRIMARY KEY (`driverID`);

--
-- Indexes for table `ds18b20`
--
ALTER TABLE `ds18b20`
  ADD PRIMARY KEY (`ds18b20ID`),
  ADD UNIQUE KEY `address` (`address`);

--
-- Indexes for table `errors`
--
ALTER TABLE `errors`
  ADD PRIMARY KEY (`errorID`),
  ADD KEY `errorSourceID` (`errorSourceID`);

--
-- Indexes for table `errorsSource`
--
ALTER TABLE `errorsSource`
  ADD PRIMARY KEY (`errorSourceID`);

--
-- Indexes for table `i2cDevices`
--
ALTER TABLE `i2cDevices`
  ADD PRIMARY KEY (`i2cID`),
  ADD UNIQUE KEY `decAddress` (`decAddress`),
  ADD KEY `i2cDeviceTypeID` (`i2cDeviceTypeID`),
  ADD KEY `i2cDriverID` (`i2cDriverID`);

--
-- Indexes for table `i2cDeviceTypes`
--
ALTER TABLE `i2cDeviceTypes`
  ADD PRIMARY KEY (`i2cDeviceTypeID`);

--
-- Indexes for table `lightModes`
--
ALTER TABLE `lightModes`
  ADD PRIMARY KEY (`modeID`),
  ADD KEY `pinID` (`pinID`);

--
-- Indexes for table `log`
--
ALTER TABLE `log`
  ADD PRIMARY KEY (`logID`),
  ADD KEY `logSourceID` (`logSourceID`),
  ADD KEY `logTypeID` (`logTypeID`),
  ADD KEY `logPinID` (`logPinID`),
  ADD KEY `logActionID` (`logActionID`);

--
-- Indexes for table `logAction`
--
ALTER TABLE `logAction`
  ADD PRIMARY KEY (`logActionID`);

--
-- Indexes for table `logSource`
--
ALTER TABLE `logSource`
  ADD PRIMARY KEY (`logSourceID`);

--
-- Indexes for table `logType`
--
ALTER TABLE `logType`
  ADD PRIMARY KEY (`logTypeID`);

--
-- Indexes for table `pinsAvailable`
--
ALTER TABLE `pinsAvailable`
  ADD PRIMARY KEY (`pinID`),
  ADD KEY `driverID` (`driverID`),
  ADD KEY `equipmentID` (`pinTypeID`);

--
-- Indexes for table `pinsType`
--
ALTER TABLE `pinsType`
  ADD PRIMARY KEY (`pinTypeID`);

--
-- Indexes for table `probes`
--
ALTER TABLE `probes`
  ADD PRIMARY KEY (`probeID`),
  ADD KEY `address` (`address`);

--
-- Indexes for table `rules`
--
ALTER TABLE `rules`
  ADD PRIMARY KEY (`ruleID`),
  ADD KEY `ruleTypeID` (`ruleTypeID`);

--
-- Indexes for table `rulesEquipment`
--
ALTER TABLE `rulesEquipment`
  ADD PRIMARY KEY (`rulesEquipmentID`),
  ADD KEY `ruleID` (`ruleID`),
  ADD KEY `equipmentPinID` (`equipmentPinID`);

--
-- Indexes for table `rulesSensors`
--
ALTER TABLE `rulesSensors`
  ADD PRIMARY KEY (`rulesSensorID`),
  ADD KEY `ruleID` (`ruleID`),
  ADD KEY `sensorPinID` (`sensorPinID`);

--
-- Indexes for table `rulesType`
--
ALTER TABLE `rulesType`
  ADD PRIMARY KEY (`ruleTypeID`);

--
-- Indexes for table `schedules`
--
ALTER TABLE `schedules`
  ADD PRIMARY KEY (`scheduleID`),
  ADD KEY `scheduleTypeID` (`scheduleTypeID`),
  ADD KEY `pinID` (`pinID`),
  ADD KEY `ruleID` (`ruleID`);

--
-- Indexes for table `scheduleType`
--
ALTER TABLE `scheduleType`
  ADD PRIMARY KEY (`scheduleTypeID`);

--
-- Indexes for table `stirrers`
--
ALTER TABLE `stirrers`
  ADD PRIMARY KEY (`stirrerID`),
  ADD KEY `stirrerPinID` (`stirrerPinID`);

--
-- Indexes for table `systemSettings`
--
ALTER TABLE `systemSettings`
  ADD PRIMARY KEY (`systemSettingID`),
  ADD KEY `userID` (`userID`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`userID`),
  ADD UNIQUE KEY `username` (`username`);

--
-- Indexes for table `version`
--
ALTER TABLE `version`
  ADD PRIMARY KEY (`versionID`);

--
-- Indexes for table `versionInfo`
--
ALTER TABLE `versionInfo`
  ADD PRIMARY KEY (`versionInfoID`),
  ADD KEY `versionID` (`versionID`),
  ADD KEY `versionTypeID` (`versionTypeID`);

--
-- Indexes for table `versionType`
--
ALTER TABLE `versionType`
  ADD PRIMARY KEY (`versionTypeID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `button_labels`
--
ALTER TABLE `button_labels`
  MODIFY `buttonID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;
--
-- AUTO_INCREMENT for table `dosingPumps`
--
ALTER TABLE `dosingPumps`
  MODIFY `doseID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `dosingSchedules`
--
ALTER TABLE `dosingSchedules`
  MODIFY `doseSchID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `drivers`
--
ALTER TABLE `drivers`
  MODIFY `driverID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `ds18b20`
--
ALTER TABLE `ds18b20`
  MODIFY `ds18b20ID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `errors`
--
ALTER TABLE `errors`
  MODIFY `errorID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `errorsSource`
--
ALTER TABLE `errorsSource`
  MODIFY `errorSourceID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `i2cDevices`
--
ALTER TABLE `i2cDevices`
  MODIFY `i2cID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `i2cDeviceTypes`
--
ALTER TABLE `i2cDeviceTypes`
  MODIFY `i2cDeviceTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `lightModes`
--
ALTER TABLE `lightModes`
  MODIFY `modeID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `log`
--
ALTER TABLE `log`
  MODIFY `logID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `logAction`
--
ALTER TABLE `logAction`
  MODIFY `logActionID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `logSource`
--
ALTER TABLE `logSource`
  MODIFY `logSourceID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
--
-- AUTO_INCREMENT for table `logType`
--
ALTER TABLE `logType`
  MODIFY `logTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `pinsAvailable`
--
ALTER TABLE `pinsAvailable`
  MODIFY `pinID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=40;
--
-- AUTO_INCREMENT for table `pinsType`
--
ALTER TABLE `pinsType`
  MODIFY `pinTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;
--
-- AUTO_INCREMENT for table `probes`
--
ALTER TABLE `probes`
  MODIFY `probeID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `rules`
--
ALTER TABLE `rules`
  MODIFY `ruleID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `rulesEquipment`
--
ALTER TABLE `rulesEquipment`
  MODIFY `rulesEquipmentID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `rulesSensors`
--
ALTER TABLE `rulesSensors`
  MODIFY `rulesSensorID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `rulesType`
--
ALTER TABLE `rulesType`
  MODIFY `ruleTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `schedules`
--
ALTER TABLE `schedules`
  MODIFY `scheduleID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `scheduleType`
--
ALTER TABLE `scheduleType`
  MODIFY `scheduleTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `stirrers`
--
ALTER TABLE `stirrers`
  MODIFY `stirrerID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `systemSettings`
--
ALTER TABLE `systemSettings`
  MODIFY `systemSettingID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `userID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `version`
--
ALTER TABLE `version`
  MODIFY `versionID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `versionInfo`
--
ALTER TABLE `versionInfo`
  MODIFY `versionInfoID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=55;
--
-- AUTO_INCREMENT for table `versionType`
--
ALTER TABLE `versionType`
  MODIFY `versionTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `dosingPumps`
--
ALTER TABLE `dosingPumps`
  ADD CONSTRAINT `dosingPumps_ibfk_1` FOREIGN KEY (`dosePinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `dosingSchedules`
--
ALTER TABLE `dosingSchedules`
  ADD CONSTRAINT `dosingSchedules_ibfk_1` FOREIGN KEY (`scheduleID`) REFERENCES `schedules` (`scheduleID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `errors`
--
ALTER TABLE `errors`
  ADD CONSTRAINT `errors_ibfk_1` FOREIGN KEY (`errorSourceID`) REFERENCES `errorsSource` (`errorSourceID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `i2cDevices`
--
ALTER TABLE `i2cDevices`
  ADD CONSTRAINT `i2cDevices_ibfk_1` FOREIGN KEY (`i2cDeviceTypeID`) REFERENCES `i2cDeviceTypes` (`i2cDeviceTypeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `i2cDevices_ibfk_2` FOREIGN KEY (`i2cDriverID`) REFERENCES `drivers` (`driverID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `lightModes`
--
ALTER TABLE `lightModes`
  ADD CONSTRAINT `lightModes_ibfk_1` FOREIGN KEY (`pinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `log`
--
ALTER TABLE `log`
  ADD CONSTRAINT `log_ibfk_1` FOREIGN KEY (`logSourceID`) REFERENCES `logSource` (`logSourceID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `log_ibfk_2` FOREIGN KEY (`logTypeID`) REFERENCES `logType` (`logTypeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `log_ibfk_3` FOREIGN KEY (`logPinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `log_ibfk_4` FOREIGN KEY (`logActionID`) REFERENCES `logAction` (`logActionID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `pinsAvailable`
--
ALTER TABLE `pinsAvailable`
  ADD CONSTRAINT `pinsAvailable_ibfk_1` FOREIGN KEY (`driverID`) REFERENCES `drivers` (`driverID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `pinsAvailable_ibfk_2` FOREIGN KEY (`pinTypeID`) REFERENCES `pinsType` (`pinTypeID`);

--
-- Constraints for table `probes`
--
ALTER TABLE `probes`
  ADD CONSTRAINT `probes_ibfk_1` FOREIGN KEY (`address`) REFERENCES `i2cDevices` (`decAddress`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `rules`
--
ALTER TABLE `rules`
  ADD CONSTRAINT `rules_ibfk_1` FOREIGN KEY (`ruleTypeID`) REFERENCES `rulesType` (`ruleTypeID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `rulesEquipment`
--
ALTER TABLE `rulesEquipment`
  ADD CONSTRAINT `rulesEquipment_ibfk_1` FOREIGN KEY (`equipmentPinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `rulesEquipment_ibfk_2` FOREIGN KEY (`ruleID`) REFERENCES `rules` (`ruleID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `rulesSensors`
--
ALTER TABLE `rulesSensors`
  ADD CONSTRAINT `rulesSensors_ibfk_1` FOREIGN KEY (`ruleID`) REFERENCES `rules` (`ruleID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `rulesSensors_ibfk_2` FOREIGN KEY (`sensorPinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `schedules`
--
ALTER TABLE `schedules`
  ADD CONSTRAINT `schedules_ibfk_1` FOREIGN KEY (`scheduleTypeID`) REFERENCES `scheduleType` (`scheduleTypeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `schedules_ibfk_2` FOREIGN KEY (`pinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `schedules_ibfk_3` FOREIGN KEY (`ruleID`) REFERENCES `rules` (`ruleID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `stirrers`
--
ALTER TABLE `stirrers`
  ADD CONSTRAINT `stirrers_ibfk_1` FOREIGN KEY (`stirrerPinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `systemSettings`
--
ALTER TABLE `systemSettings`
  ADD CONSTRAINT `systemSettings_ibfk_2` FOREIGN KEY (`userID`) REFERENCES `users` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `versionInfo`
--
ALTER TABLE `versionInfo`
  ADD CONSTRAINT `versionInfo_ibfk_1` FOREIGN KEY (`versionID`) REFERENCES `version` (`versionID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `versionInfo_ibfk_2` FOREIGN KEY (`versionTypeID`) REFERENCES `versionType` (`versionTypeID`) ON DELETE CASCADE ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
