#

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `parent_id` INT UNSIGNED NOT NULL DEFAULT '0',
   `lft` INT DEFAULT '0',
   `rght` INT DEFAULT '0',
   `name` VARCHAR(32),
   `title` VARCHAR(255),
   `image` VARCHAR(255),
   `meta_description` VARCHAR(255),
   `meta_keywords` VARCHAR(255),
   `description` TEXT,
   `created` DATETIME,
   `modified` DATETIME,
   PRIMARY KEY (`id`),
   KEY `idx_categories_parent_id` (`parent_id`),
   KEY `idx_categories_lft` (`lft`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `articles`;
CREATE TABLE `articles` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `category_id` INT UNSIGNED NOT NULL,
   `name` VARCHAR(32),
   `title` VARCHAR(255),
   `meta_description` VARCHAR(255),
   `meta_keywords` VARCHAR(255),
   `search_text` TEXT,
   `status` TINYINT NOT NULL DEFAULT '1',
   `created` DATETIME,
   `modified` DATETIME,
   PRIMARY KEY (`id`),
   KEY `idx_article_title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `article_items`;
CREATE TABLE `article_items` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `article_id` INT UNSIGNED NOT NULL,
   `item_id` INT UNSIGNED NOT NULL,
   `indexed_value` VARCHAR(255),
   `number_value` BIGINT,
   `date_value` DATETIME,
   `text_value` TEXT,
   `optional_value` TEXT,
   `created` DATETIME,
   `modified` DATETIME,
   PRIMARY KEY (`id`),
   KEY `idx_article_items_article_id` (`article_id`),
   KEY `idx_article_items_item_id` (`item_id`),
   KEY `idx_article_items_indexed_value` (`indexed_value`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `item_type_id` VARCHAR(32),
   `name` VARCHAR(32),
   `title` VARCHAR(255),
   `required` TINYINT NOT NULL DEFAULT '0',
   `visible` TINYINT NOT NULL DEFAULT '1',
   `search` TINYINT NOT NULL DEFAULT '1',
   `status` TINYINT NOT NULL DEFAULT '1',
   `sort_order` INT NOT NULL DEFAULT '0',
   `header` VARCHAR(255),
   `footer` VARCHAR(255),
   `remarks` VARCHAR(255),
   `created` DATETIME,
   `modified` DATETIME,
   PRIMARY KEY (`id`),
   KEY `idx_article_items_sort_order` (`sort_order`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `items` VALUES ('1', 'h1', 'title', 'タイトル', '1', '1', '1', '1', '1', '', '', '', NOW(), NOW());

DROP TABLE IF EXISTS `item_types`;
CREATE TABLE `item_types` (
   `id` VARCHAR(32),
   `title` VARCHAR(255),
   PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `item_types` VALUES ('h1', '見出し１');
INSERT INTO `item_types` VALUES ('h2', '見出し２');
INSERT INTO `item_types` VALUES ('h3', '見出し３');
INSERT INTO `item_types` VALUES ('p_text', '文章');
INSERT INTO `item_types` VALUES ('div', '汎用ブロック');
INSERT INTO `item_types` VALUES ('p_number', '数値');
INSERT INTO `item_types` VALUES ('a_text', 'リンク');
INSERT INTO `item_types` VALUES ('img', '画像');
INSERT INTO `item_types` VALUES ('date', '日付');

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `username` VARCHAR(255),
   `password` VARCHAR(255),
   `name` VARCHAR(255),
   `fullname` VARCHAR(255),
   `created` DATETIME,
   `modified` DATETIME,
   PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
