/*!40101 SET NAMES utf8 */;

DROP TABLE IF EXISTS `character_profile_archives`;
DROP TABLE IF EXISTS `character_pictures`;

DROP TABLE IF EXISTS `characters`;

DROP TABLE IF EXISTS `characters_has_profiles`;
DROP TABLE IF EXISTS `profile_table_statics`;

DROP TABLE IF EXISTS `profile_selects`;
DROP TABLE IF EXISTS `profile_tables`;
DROP TABLE IF EXISTS `profiles`;

DROP TABLE IF EXISTS `character_sheets`;

DROP TABLE IF EXISTS `systems`;

DROP TABLE IF EXISTS `attachments`;

DROP TABLE IF EXISTS `regist_mails`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `groups`;

DROP TABLE IF EXISTS `send_mails`;
DROP TABLE IF EXISTS `news`;
DROP TABLE IF EXISTS `site_configs`;
DROP TABLE IF EXISTS `cake_sessions`;


/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cake_sessions` (
  `id` varchar(255) NOT NULL default '',
  `data` text,
  `expires` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `site_configs` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `key_name` VARCHAR(64) NOT NULL,
  `value` TEXT NOT NULL,
  `modified` DATETIME NOT NULL,
  PRIMARY KEY(`id`),
  UNIQUE INDEX `site_settings_key`(`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `news` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(512) NOT NULL,
  `value` TEXT NOT NULL,
  `public_flag` ENUM('public','private') NOT NULL DEFAULT 'public',
  `date` DATE NOT NULL,
  `end_date` DATE NOT NULL DEFAULT '0000-00-00',
  `modified` DATETIME NOT NULL,
  PRIMARY KEY(`id`),
  INDEX `news_date`(`date`),
  INDEX `news_public_date_end`(`public_flag`, `date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `send_mails` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER UNSIGNED NULL,
  `from` blob NOT NULL,
  `from_user_id` INTEGER UNSIGNED NULL,
  `subject` TEXT NOT NULL,
  `body` TEXT NOT NULL,
  `useragent` varchar(512) NOT NULL,
  `host` varchar(64) NOT NULL,
  `created` DATETIME NOT NULL,
  `modified` DATETIME NOT NULL,
  PRIMARY KEY(`id`),
  INDEX `send_mails_user_id`(`user_id`),
  CONSTRAINT `fk_send_mails-users`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `groups` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(8) default NULL,
  `parent_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `groups_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `group_id` int(10) unsigned NOT NULL,
  `name` varchar(64) NOT NULL,
  `username` varchar(40) NOT NULL,
  `password` varchar(40) NOT NULL,
  `pcmail` blob NOT NULL,
  `url` TEXT NOT NULL,
  `notes` TEXT NOT NULL,
  `useragent` varchar(512) NOT NULL,
  `host` varchar(64) NOT NULL,
  `sid` varchar(40) NOT NULL,
  `created` datetime NULL default NULL,
  `modified` datetime NULL default NULL,
  PRIMARY KEY  (`id`),
  INDEX `users_login`(`username`, `password`),
  UNIQUE INDEX `users_username`(`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `regist_mails` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` INTEGER UNSIGNED NULL,
  `mail` BLOB NOT NULL,
  `key_code` VARCHAR(40) NOT NULL,
  `modified` DATETIME NOT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY(`id`),
  INDEX `regist_mails_key_code`(`key_code`),
  INDEX `regist_mails_user_id`(`user_id`),
  CONSTRAINT `fk_regist_mails-users`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `attachments` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` INTEGER UNSIGNED NOT NULL,
  `model` varchar(255) NOT NULL,
  `foreign_key` int(10) NOT NULL,
  `dirname` varchar(255) default NULL,
  `basename` varchar(255) NOT NULL,
  `checksum` varchar(255) NOT NULL,
  `size` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `alternative` varchar(50) default NULL,
  `group` varchar(255) default NULL,
  `created` datetime default NULL,
  `modified` datetime default NULL,
  PRIMARY KEY  (`id`),
  INDEX `attachments_user_id`(`user_id`),
  INDEX `attachments_model`(`model`, `foreign_key`)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `systems` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `sort_order` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `url` TEXT NOT NULL,
  `copyright` TEXT NOT NULL,
  `set_npc` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `public_flag` ENUM('public','private') NOT NULL DEFAULT 'public',
  `detail` TEXT NOT NULL,
  `ad` TEXT NOT NULL,
  `modified` DATETIME NULL,
  PRIMARY KEY(`id`),
  INDEX `systems_public_flag`(`public_flag`)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `character_sheets` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `system_id` INTEGER UNSIGNED NULL,
  `user_id` INTEGER UNSIGNED NOT NULL,
  `key_name` VARCHAR(40) NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `public_flag` ENUM('public', 'private') NOT NULL DEFAULT 'public',
  `sort_order` INTEGER UNSIGNED NOT NULL,
  `created` DATETIME NULL,
  `modified` DATETIME NULL,
  PRIMARY KEY(`id`),
  INDEX `character_sheets_system`(`system_id`, `public_flag`),
  INDEX `character_sheets_user`(`user_id`, `public_flag`),
  UNIQUE INDEX `character_sheets_key_name`(`key_name`),
  CONSTRAINT `fk_systems-character_sheets`
    FOREIGN KEY (`system_id`) REFERENCES `systems` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_users-character_sheets`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) TYPE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `profiles` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `system_id` INTEGER UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `key_name` VARCHAR(64) NOT NULL,
  `profile_type` ENUM('input', 'textarea', 'select', 'radio', 'checkbox', 'm-input', 'table', 's-table') NOT NULL DEFAULT 'input',
  `all_public` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `required` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `show_list` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `search` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `sort_order` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY(`id`),
  INDEX `profiles_sort`(`sort_order`),
  INDEX `profiles_system_id`(`system_id`),
  INDEX `profiles_system_id_show_list`(`system_id`, `show_list`),
  INDEX `profiles_system_id_search`(`system_id`, `search`),
  CONSTRAINT `fk_systems-profiles`
    FOREIGN KEY (`system_id`) REFERENCES `systems` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `profile_selects` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `profile_id` INTEGER UNSIGNED NOT NULL,
  `value` TEXT NOT NULL,
  `sort_order` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY(`id`),
  INDEX `profile_selects_profile`(`profile_id`),
  CONSTRAINT `fk_profiles-profile_selects`
    FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE
) TYPE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `profile_tables` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `profile_id` INTEGER UNSIGNED NOT NULL,
  `title` VARCHAR(64) NOT NULL,
  `tablekey_name` VARCHAR(32) NOT NULL,
  `width` INTEGER UNSIGNED NULL,
  `sort_order` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `show_list` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `search` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY(`id`),
  INDEX `profile_tables_profile`(`profile_id`),
  CONSTRAINT `fk_profiles-profile_talbles`
    FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE
) TYPE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `profile_table_statics` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `profile_table_id` INTEGER UNSIGNED NOT NULL,
  `title` VARCHAR(64) NULL,
  `sort_order` INTEGER UNSIGNED NULL,
  PRIMARY KEY(`id`),
  INDEX `profile_table_statics`(`profile_table_id`),
  CONSTRAINT `fk_profile_tables-profile_table_statics`
    FOREIGN KEY (`profile_table_id`) REFERENCES `profile_tables` (`id`) ON DELETE CASCADE
) TYPE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `characters_has_profiles` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `character_id` INTEGER UNSIGNED NOT NULL,
  `profile_id` INTEGER UNSIGNED NOT NULL,
  `profile_select_id` INTEGER UNSIGNED NULL,
  `profile_table_id` INTEGER UNSIGNED NULL,
  `profile_table_static_id` INTEGER UNSIGNED NULL,
  `value` TEXT NOT NULL,
  `public_flag` ENUM('public', 'private') NOT NULL DEFAULT 'public',
  `link_value` TEXT NOT NULL,
  PRIMARY KEY(`id`),
  INDEX `characters_has_profiles_character` (`character_id`),
  INDEX `characters_has_profiles_character_public` (`character_id`, `public_flag`),
  CONSTRAINT `fk_profiles-characters_has_profiles`
    FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_profile_selects-characters_has_profiles`
    FOREIGN KEY (`profile_select_id`) REFERENCES `profile_selects` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_profile_tables-characters_has_profiles`
    FOREIGN KEY (`profile_table_id`) REFERENCES `profile_tables` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_profile_table_statics-characters_has_profiles`
    FOREIGN KEY (`profile_table_static_id`) REFERENCES `profile_table_statics` (`id`) ON DELETE CASCADE
) TYPE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `characters` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `system_id` INTEGER UNSIGNED,
  `user_id` INTEGER UNSIGNED NOT NULL,
  `name` TEXT NOT NULL,
  `main_picture` VARCHAR(255) NULL,
  `full_length` VARCHAR(255) NULL,
  `notes` MEDIUMTEXT NOT NULL,
  `secret_notes` MEDIUMTEXT NOT NULL,
  `public_flag` ENUM('public','private') NOT NULL DEFAULT 'public',
  `status` ENUM('active', 'npc', 'inactive') NOT NULL DEFAULT 'active',
  `sort_order` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `deleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `created` DATETIME NULL,
  `modified` DATETIME NULL,
  `deleted_date` DATETIME NULL,
  PRIMARY KEY(`id`),
  INDEX `characters_user`(`user_id`, `deleted`, `modified`),
  INDEX `characters_modified`(`public_flag`, `deleted`, `modified`),
  INDEX `characters_system`(`system_id`, `modified`),
  CONSTRAINT `fk_systems-characters`
    FOREIGN KEY (`system_id`) REFERENCES `systems` (`id`) ON DELETE SET NULL
) TYPE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `character_pictures` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER UNSIGNED NOT NULL,
  `character_id` INTEGER UNSIGNED NOT NULL,
  `public_flag` ENUM('public','private') NOT NULL DEFAULT 'public',
  `created` DATETIME NULL,
  `modified` DATETIME NULL,
  PRIMARY KEY(`id`),
  INDEX `character_pictures_public`(`public_flag`, `character_id`),
  INDEX `character_pictures_character`(`character_id`),
  INDEX `character_pictures_user`(`user_id`),
  CONSTRAINT `fk_users-character_pictures`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_characters-character_pictures`
    FOREIGN KEY (`character_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `character_profile_archives` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `character_id` INTEGER UNSIGNED NOT NULL,
  `value` LONGTEXT NOT NULL,
  `title` VARCHAR(64) NULL,
  `memo` TEXT NULL,
  `public_flag` ENUM('public', 'private') NOT NULL DEFAULT 'public',
  `deleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `disp_date` DATETIME NULL,
  `created` DATETIME NULL,
  `modified` DATETIME NULL,
  `deleted_date` DATETIME NULL,
  PRIMARY KEY(`id`),
  INDEX `character_profile_archives` (`character_id`, `deleted`, `disp_date`),
  INDEX `character_profile_archives_public`(`id`, `character_id`, `public_flag`, `deleted`, `disp_date`)
) TYPE=InnoDB DEFAULT CHARSET=utf8;
