DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) COLLATE utf8_czech_ci NOT NULL,
  `created` datetime NOT NULL,
  `description` text COLLATE utf8_czech_ci,
  `status` VARCHAR(20) COLLATE utf8_czech_ci NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


DROP TABLE IF EXISTS `projects_has_users`;
CREATE TABLE `projects_has_users` (
  `project_id` INT(10) UNSIGNED NOT NULL,
  `user_id` INT(10) UNSIGNED NOT NULL,
  `role` VARCHAR(20) COLLATE utf8_czech_ci NOT NULL DEFAULT 'member',
  PRIMARY KEY (`project_id`,`user_id`),
  KEY `fk_projects_has_users_users1_idx` (`user_id`),
  KEY `fk_projects_has_users_projects1_idx` (`project_id`),
  CONSTRAINT `fk_projects_has_users_projects1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_projects_has_users_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) COLLATE utf8_czech_ci NOT NULL,
  `password` VARCHAR(60) COLLATE utf8_czech_ci NOT NULL,
  `ip` BINARY(16) NOT NULL,
  `created` datetime NOT NULL,
  `hash` VARCHAR(20) COLLATE utf8_czech_ci DEFAULT NULL,
  `activated` tinyint(1) NOT NULL DEFAULT '0',
  `name` VARCHAR(32) COLLATE utf8_czech_ci DEFAULT NULL,
  `surname` VARCHAR(32) COLLATE utf8_czech_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


-- 2013-05-03 21:33:15