-- phpMyAdmin SQL Dump
-- version 4.4.15.10
-- https://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 04, 2024 at 11:20 AM
-- Server version: 5.5.68-MariaDB
-- PHP Version: 7.3.33

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: `smawa`
--

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

--
-- Table structure for table `current_data`
--

CREATE TABLE IF NOT EXISTS `current_data` (
  `id` int(11) NOT NULL,
  `DEVSN` varchar(64) NOT NULL COMMENT 'Serial number',
  `TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Last update time stamp',
  `BATLVL` int(11) NOT NULL COMMENT 'Batteru level',
  `FSINS` int(11) NOT NULL DEFAULT '0' COMMENT 'Current water flow',
  `FSCVF` int(11) NOT NULL DEFAULT '0' COMMENT 'Cumulative water flow',
  `FSTMP` int(11) NOT NULL DEFAULT '0' COMMENT 'Water temperature',
  `FSSTS` int(11) NOT NULL DEFAULT '0' COMMENT 'Flow sensor status',
  `VLVPOS` int(11) NOT NULL DEFAULT '0' COMMENT 'Valve position',
  `VLVSTS` int(11) NOT NULL DEFAULT '0' COMMENT 'Valve status',
  `WRSSI` int(11) NOT NULL DEFAULT '0' COMMENT 'WIFI RSSI',
  `DEVSTS` int(11) NOT NULL DEFAULT '0' COMMENT 'Device status',
  `ALARMS` int(11) NOT NULL DEFAULT '0' COMMENT 'Alarms',
  `SFTVER` int(11) NOT NULL DEFAULT '0' COMMENT 'Software versions',
  `RESETR` int(11) NOT NULL DEFAULT '0' COMMENT 'Reset reason code',
  `UXTIME` int(11) NOT NULL DEFAULT '0' COMMENT 'Unix Time Stamp of the last report',
  `history` varchar(16584) NOT NULL,
  `calcTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time stamp of last calculation of historical data'
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `current_data`
--

INSERT INTO `current_data` (`id`, `DEVSN`, `TS`, `BATLVL`, `FSINS`, `FSCVF`, `FSTMP`, `FSSTS`, `VLVPOS`, `VLVSTS`, `WRSSI`, `DEVSTS`, `ALARMS`, `SFTVER`, `RESETR`, `UXTIME`, `history`, `calcTS`) VALUES
(10, '1000000000000000', '2024-11-02 10:37:59', 10, 0, 0, 0, 0, 1, 0, -76, 1, 0, 12, 3084, 2147483647, '{\n    "flow": {\n        "hour": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "day": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "week": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "month": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "year": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ]\n    },\n    "total": {\n        "hour": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "hour_TS": 1733311186,\n        "day": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "day_TS": 1733309651,\n        "week": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "week_TS": 1733270512,\n        "month": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "month_TS": 1733239293,\n        "year": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            3269\n        ],\n        "year_TS": 1723680000\n    }\n}', '2024-12-04 10:19:46'),
(11, '1000000000000001', '2024-11-01 07:44:27', 10, 0, 0, 0, 0, 1, 0, -54, 1, 0, 12, 2056, 2147483647, '{\n    "flow": {\n        "hour": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "day": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "week": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "month": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "year": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ]\n    },\n    "total": {\n        "hour": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "hour_TS": 1733311177,\n        "day": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "day_TS": 1733308946,\n        "week": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "week_TS": 1733270482,\n        "month": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "month_TS": 1733235918,\n        "year": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "year_TS": 1723680000\n    }\n}', '2024-12-04 10:19:37'),
(12, '1000000000000002', '2024-11-03 16:38:40', 10, 6375, 715, 2711, 0, 1, 0, -63, 1, 0, 12, 257, 2147483647, '{\n    "flow": {\n        "hour": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "day": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "week": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "month": [\n            655,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "year": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ]\n    },\n    "total": {\n        "hour": [\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715\n        ],\n        "hour_TS": 1733311204,\n        "day": [\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715\n        ],\n        "day_TS": 1733308468,\n        "week": [\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715\n        ],\n        "week_TS": 1733270467,\n        "month": [\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715,\n            715\n        ],\n        "month_TS": 1733233681,\n        "year": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            7742\n        ],\n        "year_TS": 1723680000\n    }\n}', '2024-12-04 10:20:04'),
(13, '1000000000000003', '2024-11-06 15:36:38', 0, 0, 2903, 2298, 2, 1, 0, -45, 1, 0, 12, 257, 2147483647, '{\n    "flow": {\n        "hour": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "day": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "week": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "month": [\n            2099,\n            0,\n            5,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ],\n        "year": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0\n        ]\n    },\n    "total": {\n        "hour": [\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903\n        ],\n        "hour_TS": 1733311177,\n        "day": [\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903\n        ],\n        "day_TS": 1733308402,\n        "week": [\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903\n        ],\n        "week_TS": 1733270464,\n        "month": [\n            2898,\n            2898,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903,\n            2903\n        ],\n        "month_TS": 1733244470,\n        "year": [\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            0,\n            799\n        ],\n        "year_TS": 1723680000\n    }\n}', '2024-12-04 10:19:37');

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

--
-- Table structure for table `devices`
--

CREATE TABLE IF NOT EXISTS `devices` (
  `id` int(11) NOT NULL,
  `DEVSN` varchar(64) NOT NULL,
  `reports_count` int(11) NOT NULL DEFAULT '0',
  `valve_cmd` int(11) NOT NULL DEFAULT '-1000' COMMENT 'Valve command. Negative when no command set',
  `TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `alias` varchar(64) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `devices`
--

INSERT INTO `devices` (`id`, `DEVSN`, `reports_count`, `valve_cmd`, `TS`, `alias`) VALUES
(45, '1000000000000000', 49248, 0, '2024-11-02 10:37:56', NULL),
(46, '1000000000000001', 799, 0, '2024-11-01 07:44:27', NULL),
(47, '1000000000000002', 5427, -1000, '2024-11-03 16:39:18', NULL),
(48, '1000000000000003', 823, -1000, '2024-11-06 15:36:37', NULL);

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

--
-- Table structure for table `inbox`
--

CREATE TABLE IF NOT EXISTS `inbox` (
  `id` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `DEVSN` varchar(32) NOT NULL,
  `PAYLOAD` varchar(16384) NOT NULL DEFAULT '"{}"',
  `TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MEMORY AUTO_INCREMENT=59051 DEFAULT CHARSET=utf8 COMMENT='Store for incoming box reports';

--
-- Indexes for dumped tables
--

--
-- Indexes for table `current_data`
--
ALTER TABLE `current_data`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `devices`
--
ALTER TABLE `devices`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `inbox`
--
ALTER TABLE `inbox`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `current_data`
--
ALTER TABLE `current_data`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14;
--
-- AUTO_INCREMENT for table `devices`
--
ALTER TABLE `devices`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=49;
--
-- AUTO_INCREMENT for table `inbox`
--
ALTER TABLE `inbox`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=59051;
/*!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 */;
