DROP TABLE IF EXISTS `{prefix}_weather_city`;
DROP TABLE IF EXISTS `{prefix}_weather_country`;
DROP TABLE IF EXISTS `{prefix}_weather_current`;
DROP TABLE IF EXISTS `{prefix}_weather_forecast`;

CREATE TABLE `{prefix}_weather_city` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`name` varchar(255) NOT NULL,
	`region` varchar(255) NOT NULL,
	`country` varchar(255) NOT NULL,
	`country_id` int(10) unsigned NOT NULL default 0,
	`last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	INDEX `city_name` (`name`(8)),
	INDEX `country_name` (`country`),
	INDEX `counry_id` (`country_id`)
);

CREATE TABLE `{prefix}_weather_country` (
	`id` int(10) unsigned NOT NULL,
	`iso2` char(2) NOT NULL,
	`iso3` char(3) NOT NULL,
	`name` varchar(255) NOT NULL,
	`last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE `iso2` (`iso2`),
	UNIQUE `iso3` (`iso3`),
	INDEX `country_name` (`name`)
);

CREATE TABLE `{prefix}_weather_current` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`city_id` int(10) unsigned NOT NULL default 0,
	`date` datetime NOT NULL,
	`cloud` tinyint(3) unsigned NOT NULL default 0,
	`t` tinyint(4) NOT NULL  default 0,
	`t_flik` tinyint(4) NOT NULL default 0,
	`p` smallint(6) NOT NULL default 0,
	`w` smallint(5) unsigned NOT NULL default 0,
	`w_rumb` smallint(5) unsigned NOT NULL,
	`h` tinyint(3) unsigned NOT NULL,
	`last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE KEY `city_id` (`city_id`)
);

CREATE TABLE `{prefix}_weather_forecast` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`city_id` int(10) unsigned NOT NULL,
	`date` date NOT NULL,
	`hour` tinyint(3) unsigned NOT NULL,
	`cloud` tinyint(3) unsigned NOT NULL,
	`precip` tinyint(3) unsigned NOT NULL,
	`t_min` tinyint(4) NOT NULL,
	`t_max` tinyint(4) NOT NULL,
	`p_min` smallint(6) NOT NULL,
	`p_max` smallint(6) NOT NULL,
	`w_min` smallint(5) unsigned NOT NULL,
	`w_max` smallint(5) unsigned NOT NULL,
	`w_rumb` smallint(5) unsigned NOT NULL,
	`h_min` tinyint(3) unsigned NOT NULL,
	`h_max` tinyint(3) unsigned NOT NULL,
	`wpi` tinyint(3) unsigned NOT NULL,
	`last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE KEY `forecast_unique` (`city_id`,`date`,`hour`)
);