CREATE DATABASE Coffee DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
use Coffee;

CREATE TABLE IF NOT EXISTS user (
userID int(11) NOT NULL AUTO_INCREMENT,
datetime TIMESTAMP NOT NULL DEFAULT current_timestamp() COMMENT '紀錄時間',
firstname varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓氏',
lastname varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名字',
mail varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '電子郵件',
phone varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '連絡電話',
username varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '使用者名稱',
password varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密碼',
status int(11) NOT NULL COMMENT '0:admin;1:superuser;2:enduser;8:new;9:disable',
PRIMARY KEY(userID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO user (firstname, lastname, mail, phone, username, password, status) VALUES
('firstname', 'lastname', 'aaa@gmail.com', '0987654321', 'user', '123456', 1);
  
-- CREATE TABLE IF NOT EXISTS dry_tank_actuator(
-- sn int(11) NOT NULL AUTO_INCREMENT COMMENT '流水號',
-- datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '紀錄時間',
-- tank_num varchar(3) NOT NULL COMMENT '乾燥槽編號(D1~D12)',
-- vacuum tinyint(1) NOT NULL DEFAULT '0' COMMENT '真空吸料機',
-- threewayvalve_input tinyint(1) NOT NULL DEFAULT '0' COMMENT '吸料機三通閥',
-- solenoid_disinfect tinyint(1) NOT NULL DEFAULT '0' COMMENT '消毒閥',
-- motor int(11) NOT NULL DEFAULT '0' COMMENT '馬達',
-- solenoid_outer_water tinyint(1) NOT NULL DEFAULT '0' COMMENT '桶外進水電磁閥',
-- solenoid_water_out tinyint(1) NOT NULL DEFAULT '0' COMMENT '廢水排水電磁閥',
-- heater1 tinyint(1) NOT NULL DEFAULT '0' COMMENT '加熱管 1',
-- heater2 tinyint(1) NOT NULL DEFAULT '0' COMMENT '加熱管 2',
-- temp_enable tinyint(1) NOT NULL DEFAULT '0' COMMENT '溫控開關',
-- temp float NOT NULL DEFAULT '0' COMMENT '設定溫度',
-- diskvalve tinyint(1) NOT NULL DEFAULT '0' COMMENT '蝴蝶閥',
-- switch_magnetic tinyint(1) NOT NULL DEFAULT '0' COMMENT '電磁開關',
-- switch_emergency tinyint(1) NOT NULL DEFAULT '0' COMMENT '緊急開關',
-- camera tinyint(1) NOT NULL DEFAULT '0' COMMENT '攝影機',
-- light_warning tinyint(1) NOT NULL DEFAULT '0' COMMENT '警示燈',
-- PRIMARY KEY (sn)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- INSERT INTO dry_tank_actuator (tank_num, vacuum, threewayvalve_input, solenoid_disinfect, motor, solenoid_outer_water, solenoid_water_out, heater1, heater2, temp_enable, temp, diskvalve, switch_magnetic, switch_emergency, camera, light_warning) 
-- VALUES ('D13', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');

-- CREATE TABLE IF NOT EXISTS dry_tank_SHT11(
-- sn int(11) NOT NULL AUTO_INCREMENT COMMENT '流水號',
-- datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '紀錄時間',
-- tank_num varchar(3) NOT NULL COMMENT '乾燥槽編號(D1~D12)',
-- SHT11_Temp varchar(16) NOT NULL DEFAULT '0' COMMENT 'SHT11 溫度 (單位℃)',
-- SHT11_Humidity varchar(16) NOT NULL DEFAULT '0' COMMENT 'SHT11 濕度 (單位%)',
-- PRIMARY KEY (sn)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- INSERT INTO dry_tank_SHT11 (tank_num, SHT11_Temp, SHT11_Humidity) VALUES ('D13', '0', '0')

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

CREATE DATABASE Sixth DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
use Sixth;

--
-- 資料表結構 `farm_info`
--

CREATE TABLE `farm_info` (
  `sn` int(11) NOT NULL,
  `path` text NOT NULL,
  `location` text NOT NULL,
  `loc_lat` text NOT NULL,
  `loc_lng` text NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `datetime` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- 資料表的匯出資料 `farm_info`
--

INSERT INTO `farm_info` (`sn`, `path`, `location`, `loc_lat`, `loc_lng`, `user_id`, `datetime`) VALUES
(6, '0', 'Statue of Liberty', '40.6892500000001', '-74.04445', 1, '2021-01-26 11:32:48');

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

--
-- 資料表結構 `node_img`
--

CREATE TABLE `node_img` (
  `sn` int(11) NOT NULL,
  `node_name` varchar(10) NOT NULL,
  `path` text NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `datetime` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- 資料表的匯出資料 `node_img`
--

INSERT INTO `node_img` (`sn`, `node_name`, `path`, `user_id`, `datetime`) VALUES
(1, 'Nr.1', 'app/static/farm_img\\benson\\1.jpg', 1, '2020-12-24 11:20:08'),
(2, 'Nr.2', 'app/static/farm_img\\benson\\2.jpg', 1, '2020-12-24 11:20:08'),
(3, 'Nr.3', 'app/static/farm_img\\benson\\3.jpg', 1, '2020-12-24 11:20:08'),
(4, 'Nr.4', 'app/static/farm_img\\benson\\4.jpg', 1, '2020-12-24 11:20:08'),
(5, 'Nr.5', 'app/static/farm_img\\benson\\5.jpg', 1, '2020-12-24 11:20:08'),
(6, 'Nr.6', 'app/static/farm_img\\benson\\6.jpg', 1, '2020-12-24 11:20:08'),
(7, 'Nr.7', 'app/static/farm_img\\benson\\7.jpg', 1, '2020-12-24 11:20:08'),
(8, 'Nr.8', 'app/static/farm_img\\benson\\8.jpg', 1, '2020-12-24 11:20:08'),
(9, 'Nr.9', 'app/static/farm_img\\benson\\9.jpg', 1, '2020-12-24 11:20:08'),
(10, 'Nr.10', 'app/static/farm_img\\benson\\10.jpg', 1, '2020-12-24 11:20:08'),
(11, 'Nr.11', 'app/static/farm_img\\benson\\11.jpg', 1, '2020-12-24 11:20:08');

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

--
-- 資料表結構 `sensor_list`
--

CREATE TABLE `sensor_list` (
  `sn` int(11) NOT NULL,
  `ch_name` varchar(20) NOT NULL,
  `en_name` varchar(20) NOT NULL,
  `version` varchar(10) NOT NULL,
  `datetime` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- 資料表的匯出資料 `sensor_list`
--

INSERT INTO `sensor_list` (`sn`, `ch_name`, `en_name`, `version`, `datetime`) VALUES
(1, '空氣溫度', 'airTem', 'v1.0', '2021-01-20 16:27:59'),
(2, '空氣濕度', 'airHum', 'v1.0', '2021-01-20 16:45:09'),
(3, '露點', 'dewPoint', 'v1.0', '2021-01-20 16:45:33'),
(4, '日照', 'sun', 'v1.0', '2021-01-20 16:46:17'),
(5, '土壤含水率', 'soilHum', 'v1.0', '2021-01-20 16:46:42'),
(6, '土壤溫度', 'soilTem', 'v1.0', '2021-01-20 16:46:59'),
(7, '土壤EC值', 'soilEC', 'v1.0', '2021-01-20 16:47:30'),
(8, '風速', 'windSpd', 'v1.0', '2021-01-20 16:47:58'),
(9, '風向', 'windDin', 'v1.0', '2021-01-20 16:48:20'),
(10, '降雨量', 'rain', 'v1.0', '2021-01-20 16:48:45'),
(11, '大氣壓', 'atmosphere', 'v1.0', '2021-01-20 16:49:06'),
(19, '酸鹼值', 'PH', 'v1.0', '2021-01-21 14:57:20'),
(20, '超音波', 'sonic', 'v2.0', '2021-01-21 15:02:06'),
(21, '水位', 'water level', 'v1.0', '2021-01-21 16:42:55'),
(22, '光速', 'light', 'v1.0', '2021-01-21 16:44:17'),
(23, '美女', 'girl', 'v1.0', '2021-01-21 17:45:41'),
(24, '自由女神', 'Statue', 'v1.0', '2021-01-26 10:58:37'),
(25, '帥哥', 'handsome', 'v1.0', '2021-01-28 17:52:31');

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

--
-- 資料表結構 `user`
--

CREATE TABLE `user` (
  `sn` int(11) NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `mail` varchar(50) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `username` varchar(30) NOT NULL,
  `password` varchar(40) NOT NULL,
  `status` int(11) NOT NULL COMMENT '0:admin,1:new,2:old',
  `isActive` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- 資料表的匯出資料 `user`
--

INSERT INTO `user` (`firstname`, `lastname`, `mail`, `phone`, `username`, `password`, `status`, `isActive`) VALUES
('firstname', 'lastname', 'aaa@gmail.com', '0123456789', 'user', '123456', 2, 1);