SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


CREATE TABLE `config` (
`id` int(11) NOT NULL,
  `group_name` varchar(50) NOT NULL,
  `config_key` varchar(50) NOT NULL,
  `config_value` varchar(255) NOT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `contacts` (
`id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `data_provider` varchar(150) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL COMMENT 'email, phone, twitter',
  `contact` varchar(255) NOT NULL,
  `created` int(11) NOT NULL DEFAULT '0',
  `updated` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `forms` (
`id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `type` varchar(30) NOT NULL DEFAULT 'report' COMMENT 'report, comment, stream',
  `created` int(11) NOT NULL DEFAULT '0',
  `updated` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `form_attributes` (
`id` int(11) NOT NULL,
  `key` varchar(150) NOT NULL,
  `label` varchar(150) NOT NULL,
  `input` varchar(30) NOT NULL DEFAULT 'text' COMMENT 'text, textarea, select, radio, checkbox, file, date, location',
  `type` varchar(30) NOT NULL DEFAULT 'varchar' COMMENT 'decimal, int, geometry, text, varchar, point',
  `required` tinyint(1) NOT NULL DEFAULT '0',
  `default` varchar(150) DEFAULT NULL,
  `priority` int(11) NOT NULL DEFAULT '99',
  `options` varchar(255) DEFAULT NULL,
  `cardinality` int(11) NOT NULL DEFAULT '1' COMMENT 'maximum number of values, 0 for unlimited'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `form_groups` (
`id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `label` varchar(150) NOT NULL,
  `priority` int(11) NOT NULL DEFAULT '99',
  `icon` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `form_groups_form_attributes` (
  `form_group_id` int(11) NOT NULL,
  `form_attribute_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `media` (
`id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `mime` varchar(50) NOT NULL,
  `caption` varchar(255) NOT NULL,
  `o_filename` varchar(255) NOT NULL,
  `o_size` int(11) NOT NULL,
  `o_width` int(11) DEFAULT NULL,
  `o_height` int(11) DEFAULT NULL,
  `created` int(11) NOT NULL DEFAULT '0',
  `updated` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `messages` (
`id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL COMMENT 'marks messages being replied to',
  `contact_id` int(11) DEFAULT NULL,
  `post_id` int(11) DEFAULT NULL,
  `data_provider` varchar(150) DEFAULT NULL,
  `data_provider_message_id` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `datetime` datetime DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL COMMENT 'email, phone, twitter',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, received, expired, cancelled, failed, sent',
  `direction` varchar(20) NOT NULL DEFAULT 'incoming' COMMENT 'incoming, outgoing',
  `created` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `phinxlog` (
  `version` bigint(14) NOT NULL,
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `phinxlog` (`version`, `start_time`, `end_time`) VALUES
(20140716082651, '2014-10-24 21:01:39', '2014-10-24 21:01:39');

CREATE TABLE `posts` (
`id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `form_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `type` varchar(20) NOT NULL DEFAULT 'report' COMMENT 'report, update, revision',
  `title` varchar(150) NOT NULL,
  `slug` varchar(150) DEFAULT NULL,
  `content` text,
  `status` varchar(20) NOT NULL DEFAULT 'draft' COMMENT 'draft, published, pending',
  `locale` varchar(5) NOT NULL DEFAULT 'en_US',
  `created` int(11) NOT NULL DEFAULT '0',
  `updated` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `posts_media` (
  `post_id` int(11) NOT NULL,
  `media_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `posts_sets` (
  `post_id` int(11) NOT NULL,
  `set_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `posts_tags` (
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `post_comments` (
`id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `post_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `content` text NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, published',
  `created` int(11) NOT NULL DEFAULT '0',
  `updated` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `post_datetime` (
`id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `form_attribute_id` int(11) NOT NULL,
  `value` datetime DEFAULT NULL,
  `created` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `post_decimal` (
`id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `form_attribute_id` int(11) NOT NULL,
  `value` decimal(12,4) DEFAULT NULL,
  `created` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `post_geometry` (
`id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `form_attribute_id` int(11) NOT NULL,
  `value` geometry DEFAULT NULL,
  `created` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `post_int` (
`id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `form_attribute_id` int(11) NOT NULL,
  `value` int(11) DEFAULT NULL,
  `created` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `post_point` (
`id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `form_attribute_id` int(11) NOT NULL,
  `value` point DEFAULT NULL,
  `created` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `post_text` (
`id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `form_attribute_id` int(11) NOT NULL,
  `value` text,
  `created` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `post_varchar` (
`id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `form_attribute_id` int(11) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  `created` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `roles` (
  `name` varchar(50) NOT NULL,
  `display_name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `permissions` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sets` (
`id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `filter` text,
  `created` int(11) NOT NULL DEFAULT '0',
  `updated` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tags` (
`id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `tag` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `type` varchar(20) NOT NULL DEFAULT 'category' COMMENT 'category, status',
  `color` varchar(6) DEFAULT NULL,
  `icon` varchar(20) NOT NULL DEFAULT 'tag',
  `description` text,
  `role` varchar(255) DEFAULT NULL,
  `priority` int(11) NOT NULL DEFAULT '99',
  `created` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tasks` (
`id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `post_id` int(11) DEFAULT NULL,
  `assignee` int(11) DEFAULT NULL,
  `assignor` int(11) DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, complete, later',
  `due` int(11) DEFAULT NULL,
  `created` int(11) NOT NULL DEFAULT '0',
  `updated` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
`id` int(11) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `realname` varchar(150) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `role` varchar(50) NOT NULL DEFAULT 'user',
  `logins` int(11) NOT NULL DEFAULT '0',
  `failed_attempts` int(11) NOT NULL DEFAULT '0',
  `last_login` int(11) DEFAULT NULL,
  `last_attempt` int(11) DEFAULT NULL,
  `created` int(11) NOT NULL DEFAULT '0',
  `updated` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `config`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `group_name` (`group_name`,`config_key`);

ALTER TABLE `contacts`
 ADD PRIMARY KEY (`id`), ADD KEY `data_provider` (`data_provider`), ADD KEY `user_id` (`user_id`);

ALTER TABLE `forms`
 ADD PRIMARY KEY (`id`), ADD KEY `parent_id` (`parent_id`);

ALTER TABLE `form_attributes`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `key` (`key`);

ALTER TABLE `form_groups`
 ADD PRIMARY KEY (`id`), ADD KEY `form_id` (`form_id`);

ALTER TABLE `form_groups_form_attributes`
 ADD PRIMARY KEY (`form_group_id`,`form_attribute_id`), ADD KEY `form_attribute_id` (`form_attribute_id`);

ALTER TABLE `media`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `messages`
 ADD PRIMARY KEY (`id`), ADD KEY `data_provider` (`data_provider`), ADD KEY `type` (`type`), ADD KEY `status` (`status`), ADD KEY `direction` (`direction`), ADD KEY `parent_id` (`parent_id`);

ALTER TABLE `posts`
 ADD PRIMARY KEY (`id`), ADD KEY `type` (`type`), ADD KEY `status` (`status`), ADD KEY `parent_id` (`parent_id`), ADD KEY `form_id` (`form_id`), ADD KEY `user_id` (`user_id`);

ALTER TABLE `posts_media`
 ADD PRIMARY KEY (`post_id`,`media_id`), ADD KEY `media_id` (`media_id`);

ALTER TABLE `posts_sets`
 ADD PRIMARY KEY (`post_id`,`set_id`);

ALTER TABLE `posts_tags`
 ADD PRIMARY KEY (`post_id`,`tag_id`);

ALTER TABLE `post_comments`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `post_datetime`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `post_decimal`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `post_geometry`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `post_int`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `post_point`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `post_text`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `post_varchar`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `roles`
 ADD PRIMARY KEY (`name`);

ALTER TABLE `sets`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `tags`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `tasks`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `email` (`email`);


ALTER TABLE `config`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `contacts`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `forms`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `form_attributes`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `form_groups`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `media`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `messages`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `posts`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `post_comments`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `post_datetime`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `post_decimal`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `post_geometry`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `post_int`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `post_point`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `post_text`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `post_varchar`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `sets`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tags`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tasks`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `contacts`
ADD CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

ALTER TABLE `forms`
ADD CONSTRAINT `forms_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE;

ALTER TABLE `form_groups`
ADD CONSTRAINT `form_groups_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE;

ALTER TABLE `form_groups_form_attributes`
ADD CONSTRAINT `form_groups_form_attributes_ibfk_1` FOREIGN KEY (`form_group_id`) REFERENCES `form_groups` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `form_groups_form_attributes_ibfk_2` FOREIGN KEY (`form_attribute_id`) REFERENCES `form_attributes` (`id`) ON DELETE CASCADE;

ALTER TABLE `messages`
ADD CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE;

ALTER TABLE `posts`
ADD CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `posts_ibfk_2` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `posts_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

ALTER TABLE `posts_media`
ADD CONSTRAINT `posts_media_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `posts_media_ibfk_2` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
