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

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

ALTER SCHEMA `test-db`  DEFAULT COLLATE utf8_czech_ci ;

USE `test-db`;

ALTER TABLE `test-db`.`session` DROP COLUMN `timestamp_lastmove` , DROP COLUMN `users_id` 
, DROP INDEX `users_id` ;

ALTER TABLE `test-db`.`users` DROP COLUMN `account_status` , DROP COLUMN `adminlogin_data` , DROP COLUMN `adminlogin_key` , DROP COLUMN `adminlogin_timeout` , DROP COLUMN `age` , DROP COLUMN `bayes_rating` , DROP COLUMN `buddylist` , DROP COLUMN `cookielogin_key` , DROP COLUMN `has_first_portrait` , DROP COLUMN `homedir` , DROP COLUMN `ignorelist` , DROP COLUMN `is_team_member` , DROP COLUMN `is_vip` , DROP COLUMN `new_login_requested` , DROP COLUMN `nickname_lowercase` , DROP COLUMN `passwd_lastchange` , DROP COLUMN `password_change_requested` , DROP COLUMN `phonenumber` , DROP COLUMN `profile_review_requested` , DROP COLUMN `remote_ip_lastlogin` , DROP COLUMN `remote_ip_registered` , DROP COLUMN `timestamp_lastmove` , DROP COLUMN `timestamp_profile_lastchange` , DROP COLUMN `top10_position` , DROP COLUMN `users_moods_id` , DROP COLUMN `user_description` , ADD COLUMN `user_status` ENUM('inactive','active','banished','deleted') NOT NULL DEFAULT 'inactive'  AFTER `users_id_invitator` , CHANGE COLUMN `users_id` `users_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT  , CHANGE COLUMN `users_id_invitator` `users_id_invitator` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `confirm_code` `confirm_code` VARCHAR(6) NULL DEFAULT NULL  , CHANGE COLUMN `passwd` `passwd` VARCHAR(40) NOT NULL  , CHANGE COLUMN `email` `email` VARCHAR(127) NOT NULL  , CHANGE COLUMN `name` `name` VARCHAR(63) NOT NULL  , CHANGE COLUMN `surname` `surname` VARCHAR(63) NOT NULL  , CHANGE COLUMN `gender` `gender` ENUM('m','f') NOT NULL  , CHANGE COLUMN `address_street` `address_street` VARCHAR(127) NOT NULL  , CHANGE COLUMN `cities_id` `cities_id` SMALLINT(5) UNSIGNED NULL DEFAULT NULL  , CHANGE COLUMN `address_city_manual` `address_city_manual` VARCHAR(63) NULL DEFAULT NULL  , CHANGE COLUMN `address_postnumber` `address_postnumber` VARCHAR(7) NOT NULL  , CHANGE COLUMN `salutation` `salutation` VARCHAR(63) NULL DEFAULT NULL  , CHANGE COLUMN `rating_votes` `rating_votes` INT(10) UNSIGNED NOT NULL DEFAULT 0  , CHANGE COLUMN `rating_score` `rating_score` INT(10) UNSIGNED NOT NULL DEFAULT 0  , CHANGE COLUMN `rating` `rating` FLOAT(255) UNSIGNED NOT NULL DEFAULT 0  , CHANGE COLUMN `profile_views` `profile_views` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0  , CHANGE COLUMN `uptime` `uptime` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Time spent online'  , CHANGE COLUMN `max_albums` `max_albums` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0  , CHANGE COLUMN `is_top10` `is_top10` BOOLEAN NOT NULL DEFAULT 0  , CHANGE COLUMN `is_new` `is_new` BOOLEAN NOT NULL DEFAULT 1  , CHANGE COLUMN `is_online` `is_online` BOOLEAN NOT NULL DEFAULT 0  , DROP FOREIGN KEY `users_ibfk_1` , DROP FOREIGN KEY `users_ibfk_2` , DROP FOREIGN KEY `users_ibfk_3` ;

ALTER TABLE `test-db`.`users` 
  ADD CONSTRAINT `fk_users_cities`
  FOREIGN KEY (`cities_id` )
  REFERENCES `test-db`.`cities` (`cities_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `fk_users_users`
  FOREIGN KEY (`users_id_invitator` )
  REFERENCES `test-db`.`users` (`users_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE
, ADD UNIQUE INDEX `nickname` (`nickname` ASC) 
, DROP INDEX `account_status` 
, DROP INDEX `gender` 
, DROP INDEX `is_vip` 
, DROP INDEX `nickname_lowercase` 
, DROP INDEX `users_moods_id` ;

ALTER TABLE `test-db`.`users_profileratings` CHANGE COLUMN `users_id_rater` `users_id_rater` MEDIUMINT(8) UNSIGNED NOT NULL  , CHANGE COLUMN `users_id_rated` `users_id_rated` MEDIUMINT(8) UNSIGNED NOT NULL  , CHANGE COLUMN `rating` `rating` ENUM('1','2','3','4','5','6','7','8','9','10') NOT NULL  , CHANGE COLUMN `comment` `comment` VARCHAR(127) NULL DEFAULT NULL  , COLLATE = utf8_general_ci 
, ADD PRIMARY KEY (`users_id_rater`, `users_id_rated`) 
, DROP INDEX `timestamp_rated` ;


-- -----------------------------------------------------
-- Placeholder table for view `test-db`.`view_blogposts_list`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test-db`.`view_blogposts_list` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `test-db`.`view_clubs_list`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test-db`.`view_clubs_list` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `test-db`.`view_events_list`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test-db`.`view_events_list` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `test-db`.`view_reports_list`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test-db`.`view_reports_list` (`id` INT);
USE `test-db`;

-- -----------------------------------------------------
-- View `test-db`.`view_blogposts_list`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test-db`.`view_blogposts_list`;
DROP VIEW IF EXISTS `test-db`.`view_blogposts_list` ;

-- -----------------------------------------------------
-- View `test-db`.`view_clubs_list`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test-db`.`view_clubs_list`;
DROP VIEW IF EXISTS `test-db`.`view_clubs_list` ;

-- -----------------------------------------------------
-- View `test-db`.`view_events_list`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test-db`.`view_events_list`;
DROP VIEW IF EXISTS `test-db`.`view_events_list` ;

-- -----------------------------------------------------
-- View `test-db`.`view_reports_list`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test-db`.`view_reports_list`;
DROP VIEW IF EXISTS `test-db`.`view_reports_list` ;

DELIMITER //

DROP procedure IF EXISTS `test-db`.`refresh_sitemap` //

DROP procedure IF EXISTS `test-db`.`refresh_top_10_cache_today` //

DROP procedure IF EXISTS `test-db`.`refresh_top_10_cache` //

DROP procedure IF EXISTS `test-db`.`update_albums_photos_count` //

DROP procedure IF EXISTS `test-db`.`update_online_users` //

DROP procedure IF EXISTS `test-db`.`update_reports_photos_count` //

DROP procedure IF EXISTS `test-db`.`update_top_10_photos_daily` //

DROP procedure IF EXISTS `test-db`.`update_top_10_photos_monthly` //

DROP procedure IF EXISTS `test-db`.`update_top_10_photos_weekly` //

DROP procedure IF EXISTS `test-db`.`update_top_10_photos` //

DROP procedure IF EXISTS `test-db`.`update_top_10_users` //

DROP procedure IF EXISTS `test-db`.`update_usergroup_members_count` //

DROP procedure IF EXISTS `test-db`.`update_user_age` //

DROP procedure IF EXISTS `test-db`.`update_user_uptime` //


DELIMITER ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

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