# ************************************************************
# Sequel Pro SQL dump
# Version 4096
#
# http://www.sequelpro.com/
# http://code.google.com/p/sequel-pro/
#
# Host: localhost (MySQL 5.5.38)
# Database: iris
# Generation Time: 2015-06-16 18:00:36 +0000
# ************************************************************




# Dump of table addresses
# ------------------------------------------------------------


CREATE TABLE `addresses` (
  `address_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `address_street_1` varchar(255) DEFAULT NULL,
  `address_street_2` varchar(255) DEFAULT NULL,
  `address_city` varchar(255) DEFAULT NULL,
  `address_state` varchar(2) DEFAULT NULL,
  `address_zip` char(10) DEFAULT NULL,
  `address_country` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table permissions
# ------------------------------------------------------------


CREATE TABLE `permissions` (
  `permission_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `permission_token` varchar(70) NOT NULL DEFAULT '',
  `permission_value` int(10) DEFAULT NULL,
  PRIMARY KEY (`permission_id`),
  UNIQUE KEY `permission_name` (`permission_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table profiles
# ------------------------------------------------------------


CREATE TABLE `profiles` (
  `profile_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `address_id` int(11) unsigned DEFAULT NULL,
  `profile_slug` varchar(255) DEFAULT NULL,
  `profile_first_name` varchar(255) DEFAULT NULL,
  `profile_last_name` varchar(255) DEFAULT NULL,
  `profile_dob` date DEFAULT NULL,
  `profile_title` varchar(255) DEFAULT NULL,
  `profile_avatar` varchar(255) DEFAULT NULL,
  `profile_content` mediumtext,
  `profile_phone` varchar(50) DEFAULT NULL,
  `profile_public` tinyint(1) NOT NULL DEFAULT '0',
  `profile_index` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`profile_id`),
  UNIQUE KEY `profile_slug` (`profile_slug`),
  KEY `address_id` (`address_id`),
  CONSTRAINT `profiles_ibfk_1` FOREIGN KEY (`address_id`) REFERENCES `addresses` (`address_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table role_permissions
# ------------------------------------------------------------


CREATE TABLE `role_permissions` (
  `role_permission_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(11) unsigned NOT NULL,
  `permission_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`role_permission_id`),
  UNIQUE KEY `role_id_2` (`role_id`,`permission_id`),
  KEY `role_id` (`role_id`),
  KEY `permission_id` (`permission_id`),
  CONSTRAINT `role_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `role_permissions_ibfk_2` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`permission_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table roles
# ------------------------------------------------------------


CREATE TABLE `roles` (
  `role_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `role_token` varchar(100) NOT NULL DEFAULT '',
  `role_hidden` tinyint(1) NOT NULL DEFAULT '0',
  `role_title` varchar(50) DEFAULT '',
  PRIMARY KEY (`role_id`),
  KEY `role_name` (`role_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table settings
# ------------------------------------------------------------


CREATE TABLE `settings` (
  `setting_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `setting_token` varchar(100) NOT NULL DEFAULT '',
  `setting_title` varchar(255) DEFAULT NULL,
  `setting_value` varchar(250) DEFAULT NULL,
  `setting_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`setting_id`),
  UNIQUE KEY `setting_name` (`setting_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table user_logins
# ------------------------------------------------------------


CREATE TABLE `user_logins` (
  `user_login_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `user_login_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_login_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_logins_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table user_roles
# ------------------------------------------------------------


CREATE TABLE `user_roles` (
  `user_role_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `role_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`user_role_id`),
  KEY `user_id` (`user_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `user_roles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table user_statuses
# ------------------------------------------------------------


CREATE TABLE `user_statuses` (
  `user_status_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_status_title` varchar(50) DEFAULT NULL,
  `user_status_token` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`user_status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table users
# ------------------------------------------------------------


CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `profile_id` int(11) unsigned NOT NULL,
  `user_status_id` int(11) unsigned NOT NULL DEFAULT '1',
  `user_password` varchar(255) NOT NULL DEFAULT '',
  `user_email` varchar(255) DEFAULT NULL,
  `user_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_password_code` varchar(255) DEFAULT NULL,
  `user_password_timestamp` timestamp NULL DEFAULT NULL,
  `user_activation_code` varchar(255) DEFAULT NULL,
  `user_activation_timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `user_status_id` (`user_status_id`),
  KEY `profile_id` (`profile_id`),
  CONSTRAINT `users_ibfk_2` FOREIGN KEY (`user_status_id`) REFERENCES `user_statuses` (`user_status_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `users_ibfk_3` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`profile_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `roles` (`role_id`, `role_token`, `role_hidden`, `role_title`)
VALUES
	(1, 'super', 0, 'Super Admin'),
	(2, 'admin', 0, 'Administrator');



INSERT INTO `permissions` (`permission_id`, `permission_token`, `permission_value`)
VALUES
	(NULL, 'access_admin', 1);

INSERT INTO `role_permissions` (`role_permission_id`, `role_id`, `permission_id`)
VALUES
	(NULL, 1, LAST_INSERT_ID()),
	(NULL, 2, LAST_INSERT_ID());


INSERT INTO `permissions` (`permission_id`, `permission_token`, `permission_value`)
VALUES
	(NULL, 'access_users', 2);

INSERT INTO `role_permissions` (`role_permission_id`, `role_id`, `permission_id`)
VALUES
	(NULL, 1, LAST_INSERT_ID()),
	(NULL, 2, LAST_INSERT_ID());


INSERT INTO `permissions` (`permission_id`, `permission_token`, `permission_value`)
VALUES
	(NULL, 'add_users', 4);

INSERT INTO `role_permissions` (`role_permission_id`, `role_id`, `permission_id`)
VALUES
	(NULL, 1, LAST_INSERT_ID()),
	(NULL, 2, LAST_INSERT_ID());


INSERT INTO `permissions` (`permission_id`, `permission_token`, `permission_value`)
VALUES
	(NULL, 'modify_users', 8);

INSERT INTO `role_permissions` (`role_permission_id`, `role_id`, `permission_id`)
VALUES
	(NULL, 1, LAST_INSERT_ID()),
	(NULL, 2, LAST_INSERT_ID());


INSERT INTO `permissions` (`permission_id`, `permission_token`, `permission_value`)
VALUES
	(NULL, 'delete_users', 16);

INSERT INTO `role_permissions` (`role_permission_id`, `role_id`, `permission_id`)
VALUES
	(NULL, 1, LAST_INSERT_ID()),
	(NULL, 2, LAST_INSERT_ID());


INSERT INTO `permissions` (`permission_id`, `permission_token`, `permission_value`)
VALUES
	(NULL, 'login_as_user', 32);

INSERT INTO `role_permissions` (`role_permission_id`, `role_id`, `permission_id`)
VALUES
	(NULL, 1, LAST_INSERT_ID());


INSERT INTO `permissions` (`permission_id`, `permission_token`, `permission_value`)
VALUES
	(NULL, 'manage_roles', 128);

INSERT INTO `role_permissions` (`role_permission_id`, `role_id`, `permission_id`)
VALUES
	(NULL, 1, LAST_INSERT_ID());

INSERT INTO `user_statuses` (`user_status_id`, `user_status_title`, `user_status_token`)
VALUES
  (1, 'Active', 'active'),
  (2, 'Inactive', 'inactive');

ALTER TABLE `users` ADD UNIQUE INDEX (`user_email`);



