/*!40101 SET NAMES utf8 */;

/* Change ENUM to INT */
/* public_flag */
ALTER TABLE `news` MODIFY `public_flag` TINYINT(2) NOT NULL DEFAULT '1';
ALTER TABLE `systems` MODIFY `public_flag` TINYINT(2) NOT NULL DEFAULT '1';
ALTER TABLE `character_sheets` MODIFY `public_flag` TINYINT(2) NOT NULL DEFAULT '1';
ALTER TABLE `characters_has_profiles` MODIFY `public_flag` TINYINT(2) NOT NULL DEFAULT '1';
ALTER TABLE `characters` MODIFY `public_flag` TINYINT(2) NOT NULL DEFAULT '1';
ALTER TABLE `character_pictures` MODIFY `public_flag` TINYINT(2) NOT NULL DEFAULT '1';
ALTER TABLE `character_profile_archives` MODIFY `public_flag` TINYINT(2) NOT NULL DEFAULT '1';
/* other */
ALTER TABLE `profiles` MODIFY `profile_type` TINYINT(2) NOT NULL DEFAULT '1';
ALTER TABLE `characters` MODIFY `status` TINYINT(2) NOT NULL DEFAULT '1';

/* Add Table */

DROP TABLE IF EXISTS `tags`;
DROP TABLE IF EXISTS `tagged`;

DROP TABLE IF EXISTS `default_profiles`;

CREATE TABLE `default_profiles` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `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` TINYINT(2) NOT NULL DEFAULT '1',
  PRIMARY KEY(`id`),
  CONSTRAINT `fk_profiles-default_profiles`
    FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_profile_selects-default_profiles`
    FOREIGN KEY (`profile_select_id`) REFERENCES `profile_selects` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_profile_tables-default_profiles`
    FOREIGN KEY (`profile_table_id`) REFERENCES `profile_tables` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_profile_table_statics-default_profiles`
    FOREIGN KEY (`profile_table_static_id`) REFERENCES `profile_table_statics` (`id`) ON DELETE CASCADE
) TYPE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `profiles` DROP column `public_flag_default`;

ALTER TABLE `profiles` ADD column `all_public` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `profile_type`;

ALTER TABLE `trpgdb_systems` ADD column `modified` DATETIME default '0000-00-00 00:00:00'

CREATE TABLE `tagged` (
  `id` varchar(36) NOT NULL,
  `foreign_key` varchar(36) NOT NULL,
  `tag_id` varchar(36) NOT NULL,
  `model` varchar(255) NOT NULL,
  `language` varchar(6) DEFAULT NULL,
  `times_tagged` int(11) DEFAULT 1 NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `UNIQUE_TAGGING` (`model`, `foreign_key`, `tag_id`, `language`),
  KEY `INDEX_TAGGED` (`model`),
  KEY `INDEX_LANGUAGE` (`language`)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tags` (
  `id` varchar(36) NOT NULL,
  `identifier` varchar(30) DEFAULT NULL,
  `name` varchar(30) NOT NULL,
  `keyname` varchar(30) NOT NULL,
  `weight` int(2) DEFAULT 0 NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `UNIQUE_TAG` (`identifier`, `keyname`)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `access` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `model` varchar(255) NOT NULL,
  `foreign_key` int(10) NOT NULL,
  `host` varchar(64) NOT NULL,
  `created` DATETIME NULL,
  PRIMARY KEY(`id`),
  INDEX `access_model`(`model`, `foreign_key`),
  INDEX `access_model_created`(`model`, `foreign_key`, `created`)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `access_counts` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `counts` int(10) NOT NULL,
  `model` varchar(255) NOT NULL,
  `foreign_key` int(10) NOT NULL,
  `created` DATETIME NULL,
  PRIMARY KEY(`id`),
  INDEX `access_counts`(`counts`)
  INDEX `access_model`(`model`, `foreign_key`)
) TYPE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `cron_results` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `cron_name` varchar(255) NOT NULL,
  `model` varchar(255) NULL,
  `foreign_key` int(10) NULL,
  `start_date` DATETIME NULL,
  `end_date` DATETIME NULL,
  `created` DATETIME NULL,
  PRIMARY KEY(`id`),
  INDEX `cron_model`(`model`, `foreign_key`),
  INDEX `cron_range`(`cron_name`, `start_date`, `end_date`)
) TYPE=InnoDB DEFAULT CHARSET=utf8;
