CREATE DATABASE IF NOT EXISTS `meteo`;

USE `meteo`;

GRANT ALL PRIVILEGES ON meteo.* TO meteo IDENTIFIED BY '';
GRANT SELECT ON meteo.* TO meteo_read IDENTIFIED BY '';

CREATE IF NOT EXISTS TABLE `datas` (
	`date_year` SMALLINT NOT NULL,
	`date_month` TINYINT NOT NULL,
	`date_day` TINYINT NOT NULL,
	`date_hour` TINYINT NOT NULL,
	`date_minute` TINYINT NOT NULL,
	`temperature0` FLOAT NOT NULL,
	`humidity0` FLOAT NOT NULL,
	`pressure0` FLOAT NOT NULL,
	`temperature1` FLOAT NOT NULL,
	`humidity1` FLOAT NOT NULL,
	`pressure1` FLOAT NOT NULL,
	`rain` FLOAT NOT NULL,
	`wind` FLOAT NOT NULL,
	`vane` FLOAT NOT NULL,
	`light` FLOAT NOT NULL,
	PRIMARY KEY (`date_year`, `date_month`, `date_day`, `date_hour`, `date_minute`)
) DEFAULT CHARSET=utf8;

CREATE OR REPLACE VIEW `moy_day` AS SELECT 
	`date_year`,
	`date_month`,
	`date_day`,
	ROUND(AVG(`temperature0`), 2) AS `temperature0`,
	ROUND(AVG(`humidity0`), 2) AS `humidity0`,
	ROUND(AVG(`pressure0`), 2) AS `pressure0`,
	ROUND(AVG(`temperature1`), 2) AS `temperature1`,
	ROUND(AVG(`humidity1`), 2) AS `humidity1`,
	ROUND(AVG(`pressure1`), 2) AS `pressure1`,
	ROUND(SUM(`rain`), 2) AS `rain`,
	ROUND(AVG(`wind`), 2) AS `wind`,
	ROUND(AVG(`vane`), 2) AS `vane`,
	ROUND(AVG(`light`), 2) AS `light`
	FROM `datas` GROUP BY `date_year`, `date_month`, `date_day`;

CREATE OR REPLACE VIEW `moy_hour` AS SELECT
	`date_year`,
	`date_month`,
	`date_day`,
	`date_hour`,
	ROUND(AVG(`temperature0`), 2) AS `temperature0`,
	ROUND(AVG(`humidity0`), 2) AS `humidity0`,
	ROUND(AVG(`pressure0`), 2) AS `pressure0`,
	ROUND(AVG(`temperature1`), 2) AS `temperature1`,
	ROUND(AVG(`humidity1`), 2) AS `humidity1`,
	ROUND(AVG(`pressure1`), 2) AS `pressure1`,
	ROUND(SUM(`rain`), 2) AS `rain`,
	ROUND(AVG(`wind`), 2) AS `wind`,
	ROUND(AVG(`vane`), 2) AS `vane`,
	ROUND(AVG(`light`), 2) AS `light`
	FROM `datas` GROUP BY `date_year`, `date_month`, `date_day`, `date_hour`;
