--------------------------------------------------------------------------------
-- Database
--------------------------------------------------------------------------------

-- DROP DATABASE livez;
CREATE DATABASE livez CHARACTER SET sjis;

USE livez;

--------------------------------------------------------------------------------
-- i-mode
--------------------------------------------------------------------------------

-- DROP TABLE imode_series;
CREATE TABLE imode_series (
	series_name VARCHAR(10)       NOT NULL PRIMARY KEY   COMMENT 'シリーズ名(略称)',
	family      SMALLINT UNSIGNED NOT NULL               COMMENT '系統',
	foma        BOOLEAN           NOT NULL DEFAULT FALSE COMMENT 'FOMA'
)
TYPE=InnoDB
COMMENT='iモードシリーズマスタ';



-- DROP TABLE imode_html_version;
CREATE TABLE imode_html_version (
	html FLOAT NOT NULL PRIMARY KEY COMMENT 'i-HTMLバージョン'
)
TYPE=InnoDB
COMMENT='i-HTMLバージョンマスタ';



-- DROP TABLE imode_xhtml_version;
CREATE TABLE imode_xhtml_version (
	xhtml FLOAT NOT NULL PRIMARY KEY COMMENT 'i-XHTMLバージョン'
)
TYPE=InnoDB
COMMENT='i-XHTMLバージョンマスタ';



-- DROP TABLE imode_color_version;
CREATE TABLE imode_color_version (
	color INTEGER UNSIGNED NOT NULL PRIMARY KEY COMMENT '色数/階調数'
)
TYPE=InnoDB
COMMENT='iモード色数/階調数マスタ';



-- DROP TABLE imode_imotion_version;
CREATE TABLE imode_imotion_version (
	imotion CHAR(3) NOT NULL PRIMARY KEY COMMENT 'iモーション種別'
)
TYPE=InnoDB
COMMENT='iモーション種別マスタ';



-- DROP TABLE imode_barcode_version;
CREATE TABLE imode_barcode_version (
	barcode FLOAT NOT NULL PRIMARY KEY COMMENT 'バーコードバージョン'
)
TYPE=InnoDB
COMMENT='iバーコードバージョンマスタ';



-- DROP TABLE imode_ssl_version;
CREATE TABLE imode_ssl_version (
	id          INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'ID',
	description TEXT(300)        NOT NULL                            COMMENt '説明'
)
TYPE=InnoDB
COMMENT='iモードSSLマスタ';



-- DROP TABLE imode;
CREATE TABLE imode (
	name           VARCHAR(10)       NOT NULL               COMMENT '機種名',
	series_name    VARCHAR(10)       NOT NULL               COMMENT 'シリーズ名',
	product_name   VARCHAR(20)                DEFAULT NULL  COMMENT '製品名',
	html           FLOAT             NOT NULL               COMMENT 'i-HTMLバージョン',
	xhtml          FLOAT                      DEFAULT NULL  COMMENT 'i-XHTMLバージョン',

	browser_column SMALLINT UNSIGNED          DEFAULT NULL  COMMENT 'ブラウザ表示桁数',
	browser_row    SMALLINT UNSIGNED          DEFAULT NULL  COMMENT 'ブラウザ表示行数',
	browser_width  SMALLINT UNSIGNED NOT NULL               COMMENT 'ブラウザ幅(ピクセル)',
	browser_height SMALLINT UNSIGNED NOT NULL               COMMENT 'ブラウザ高さ(ピクセル)',
	screen_width   SMALLINT UNSIGNED          DEFAULT NULL  COMMENT '待受け幅(ピクセル)',
	screen_height  SMALLINT UNSIGNED          DEFAULT NULL  COMMENT '待受け高さ(ピクセル)',
	monochrome     BOOLEAN           NOT NULL DEFAULT FALSE COMMENT '白黒画面',
	color          INTEGER UNSIGNED  NOT NULL               COMMENT '色数/階調数',
	jpeg           BOOLEAN           NOT NULL DEFAULT FALSE COMMENT 'JPEG対応',

	imotion        CHAR(3)                    DEFAULT NULL  COMMENT 'iモーション種別',
	pdf            BOOLEAN           NOT NULL DEFAULT FALSE COMMENT 'PDF対応',
	chara_den      BOOLEAN           NOT NULL DEFAULT FALSE COMMENT 'キャラ電対応',
	toruca         BOOLEAN           NOT NULL DEFAULT FALSE COMMENT 'トルカ対応',
	ichannel       BOOLEAN           NOT NULL DEFAULT FALSE COMMENT 'iチャネル対応',
	menu_icon      BOOLEAN           NOT NULL DEFAULT FALSE COMMENT 'メニューアイコン対応',
	barcode        FLOAT                      DEFAULT NULL  COMMENT 'バーコードバージョン',
	ssl_type       INTEGER UNSIGNED           DEFAULT NULL  COMMENT 'SSL対応',

	PRIMARY KEY (name),
	UNIQUE INDEX unq_product_name$imode (product_name)
)
TYPE=InnoDB
COMMENT='iモード端末マスタ';



-- DROP TABLE imode_subscreen;
CREATE TABLE imode_subscreen (
	name   VARCHAR(10)       NOT NULL COMMENT '機種名',
	width  SMALLINT UNSIGNED NOT NULL COMMENT '待受け幅(ピクセル)',
	height SMALLINT UNSIGNED NOT NULL COMMENT '待受け高さ(ピクセル)',

	PRIMARY KEY (name),
	CONSTRAINT fk_name$imode_subscreen FOREIGN KEY (name) REFERENCES imode (name) ON UPDATE CASCADE ON DELETE CASCADE
)
TYPE=InnoDB
COMMENT='背面画面マスタ';



-- DROP TABLE imode_doja_version;
CREATE TABLE imode_doja_version (
	doja FLOAT NOT NULL PRIMARY KEY COMMENT 'DoJaバージョン'
)
TYPE=InnoDB
COMMENT='DoJaバージョンマスタ';



-- DROP TABLE imode_jar_version;
CREATE TABLE imode_jar_version (
	size SMALLINT UNSIGNED NOT NULL PRIMARY KEY COMMENT 'JAR容量(KB)'
)
TYPE=InnoDB
COMMENT='JAR容量マスタ';



-- DROP TABLE imode_sp_version;
CREATE TABLE imode_sp_version (
	size SMALLINT UNSIGNED NOT NULL PRIMARY KEY COMMENT 'ScratchPad容量(KB)'
)
TYPE=InnoDB
COMMENT='ScratchPad容量マスタ';



-- DROP TABLE imode_felica_version;
CREATE TABLE imode_felica_version (
	id          INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'ID',
	description TEXT(70)         NOT NULL                            COMMENT '説明'
)
TYPE=InnoDB
COMMENT='iモードFeliCaマスタ';



-- DROP TABLE imode_iappli;
CREATE TABLE imode_iappli (
	name    VARCHAR(10)       NOT NULL               COMMENT '機種名',
	doja    FLOAT             NOT NULL               COMMENT 'DoJaバージョン',
	edition BOOLEAN           NOT NULL DEFAULT FALSE COMMENT '亜種',
	jar     SMALLINT UNSIGNED NOT NULL               COMMENT 'JAR容量(KB)',
	sp      SMALLINT UNSIGNED NOT NULL               COMMENT 'ScratchPad容量(KB)',
	width   SMALLINT UNSIGNED NOT NULL               COMMENT '描画幅(ピクセル)',
	height  SMALLINT UNSIGNED NOT NULL               COMMENT '描画高さ(ピクセル)',
	felica  INTEGER UNSIGNED           DEFAULT NULL  COMMENT 'FeliCa対応',

	PRIMARY KEY (name),
	CONSTRAINT fk_name$imode_iappli FOREIGN KEY (name) REFERENCES imode (name) ON UPDATE CASCADE ON DELETE CASCADE
)
TYPE=InnoDB
COMMENT = 'iアプリマスタ';



-- DROP TABLE imode_flash_version;
CREATE TABLE imode_flash_version (
	version FLOAT NOT NULL PRIMARY KEY COMMENT 'バージョン'
)
TYPE=InnoDB
COMMENT = 'Flash Liteバージョンマスタ';



-- DROP TABLE imode_flash;
CREATE TABLE imode_flash (
	name          VARCHAR(10)       NOT NULL COMMENT '機種名',
	version       FLOAT             NOT NULL COMMENT 'Flash Liteバージョン',
	screen_height SMALLINT UNSIGNED NOT NULL COMMENT '待受け高さ(ピクセル)',
	subscreen     BOOLEAN           NOT NULL COMMENT '背面画面対応',

	PRIMARY KEY (name),
	CONSTRAINT fk_name$imode_flash FOREIGN KEY (name) REFERENCES imode (name) ON UPDATE CASCADE ON DELETE CASCADE
)
TYPE=InnoDB
COMMENT='iモードFlashマスタ';



-- DROP TABLE imode_decomail_version;
CREATE TABLE imode_decomail_version (
	version FLOAT NOT NULL PRIMARY KEY COMMENT 'デコメールバージョン'
)
TYPE=InnoDB
COMMENT = 'デコメールバージョンマスタ';



-- DROP TABLE imode_decomail;
CREATE TABLE imode_decomail (
	name     VARCHAR(10) NOT NULL               COMMENT '機種名',
	version  FLOAT       NOT NULL               COMMENT 'デコメールバージョン',
	template BOOLEAN     NOT NULL DEFAULT FALSE COMMENT 'テンプレート対応',

	PRIMARY KEY (name),
	CONSTRAINT fk_name$imode_decomail FOREIGN KEY (name) REFERENCES imode (name) ON UPDATE CASCADE ON DELETE CASCADE
)
TYPE=InnoDB
COMMENT='デコメールマスタ';



-- DROP TABLE imode_frame;
CREATE TABLE imode_frame (
	name   VARCHAR(10)       NOT NULL COMMENT '機種名',
	width  SMALLINT UNSIGNED NOT NULL COMMENT '幅(ピクセル)',
	height SMALLINT UNSIGNED NOT NULL COMMENT '高さ(ピクセル)',

	PRIMARY KEY (name, width, height),
	CONSTRAINT fk_name$imode_frame FOREIGN KEY (name) REFERENCES imode (name) ON UPDATE CASCADE ON DELETE CASCADE
)
TYPE=InnoDB
COMMENT='写真フレーム画像マスタ';


--------------------------------------------------------------------------------
-- VIEW

-- DROP VIEW vw_imode CASCADE;
CREATE VIEW vw_imode AS
SELECT
	a.name,
	a.product_name,
	a.series_name,
	a.html,
	a.xhtml,
	a.browser_column,
	a.browser_row,
	a.browser_width,
	a.browser_height,
	a.screen_width,
	a.screen_height,
	a.monochrome,
	a.color,
	a.jpeg,

	b.name IS NOT NULL AS subscreen,
	b.width AS subscreen_width,
	b.height AS subscreen_height,

	c.name IS NOT NULL AS iappli,
	c.doja,
	c.jar,
	c.sp,
	c.width AS iappli_width,
	c.height AS iappli_height,

	d.name IS NOT NULL AS flash,
	d.version AS flash_version,
	d.screen_height AS flash_screen_height,
	d.subscreen AS flash_subscreen

FROM imode AS a
	LEFT OUTER JOIN imode_subscreen AS b ON a.name = b.name
	LEFT OUTER JOIN imode_iappli AS c ON a.name = c.name
	LEFT OUTER JOIN imode_flash AS d ON a.name = d.name
;

--------------------------------------------------------------------------------
-- EZweb
--------------------------------------------------------------------------------

-- DROP TABLE ezweb_series;
CREATE TABLE ezweb_series (
	name VARCHAR(10) NOT NULL PRIMARY KEY COMMENT 'シリーズ名'
)
TYPE=InnoDB
COMMENT='EZwebシリーズマスタ';



-- DROP TABLE ezweb;
CREATE TABLE ezweb (
	device_type   CHAR(4)           NOT NULL               COMMENT 'デバイスID',
	product_name  VARCHAR(20)       NOT NULL               COMMENT '機種名称',
	series_name   VARCHAR(10)                DEFAULT NULL  COMMENT 'シリーズ名',
	screen_width  SMALLINT UNSIGNED          DEFAULT NULL  COMMENT '待受け幅(ピクセル)',
	screen_height SMALLINT UNSIGNED          DEFAULT NULL  COMMENT '待受け高さ(ピクセル)',
	gif           BOOLEAN           NOT NULL DEFAULT FALSE COMMENT 'GIF対応',
	jpeg          BOOLEAN           NOT NULL DEFAULT FALSE COMMENT 'JPEG対応',
	png_8bit      BOOLEAN           NOT NULL DEFAULT FALSE COMMENT '8bit(256色)PNG対応',
	bmp_2bit      BOOLEAN           NOT NULL DEFAULT FALSE COMMENT '2bit(4階調)BMP対応',
	bmp_1bit      BOOLEAN           NOT NULL DEFAULT FALSE COMMENT '1bit(2階調)BMP対応',
	draft         BOOLEAN           NOT NULL DEFAULT FALSE COMMENT '暫定',

	PRIMARY KEY (device_type)
)
TYPE=InnoDB
COMMENT='EZweb端末マスタ';



-- DROP TABLE ezweb_frame;
CREATE TABLE ezweb_frame (
	device_type CHAR(4)           NOT NULL COMMENT 'デバイスID',
	width       SMALLINT UNSIGNED NOT NULL COMMENT '幅(ピクセル)',
	height      SMALLINT UNSIGNED NOT NULL COMMENT '高さ(ピクセル)',

	PRIMARY KEY (device_type, width, height),
	CONSTRAINT fk_device_type$ezweb_frame FOREIGN KEY (device_type)
		REFERENCES ezweb (device_type)
		ON UPDATE CASCADE ON DELETE CASCADE
)
TYPE=InnoDB
COMMENT='EZweb 写真フレームマスタ';



-- DROP TABLE ezweb_stamp;
CREATE TABLE ezweb_stamp (
	device_type CHAR(4)           NOT NULL COMMENT 'デバイスID',
	width       SMALLINT UNSIGNED NOT NULL COMMENT '幅(ピクセル)',
	height      SMALLINT UNSIGNED NOT NULL COMMENT '高さ(ピクセル)',

	PRIMARY KEY (device_type),
	CONSTRAINT fk_device_type$ezweb_stamp FOREIGN KEY (device_type)
		REFERENCES ezweb (device_type)
		ON UPDATE CASCADE ON DELETE CASCADE
)
TYPE=InnoDB
COMMENT='EZweb 写真スタンプマスタ';


--------------------------------------------------------------------------------
-- Vodafone
--------------------------------------------------------------------------------

-- DROP TABLE vodafone CASCADE;
CREATE TABLE vodafone (
	name         VARCHAR(10) NOT NULL               COMMENT '機種名',
	product_name VARCHAR(20)          DEFAULT NULL  COMMENT '製品名',

	PRIMARY KEY (name),
	UNIQUE unq_product_name$vodafone (product_name)
)
TYPE=InnoDB
COMMENT='ボーダフォン端末マスタ';
