CREATE DATABASE IF NOT EXISTS DB;

USE DB;

DROP TABLE IF EXISTS td_t;
DROP TABLE IF EXISTS td_n;
DROP TABLE IF EXISTS td_i;
DROP TABLE IF EXISTS td_m;
DROP TABLE IF EXISTS td_v;
DROP TABLE IF EXISTS tc_d;
DROP TABLE IF EXISTS tc_r;
DROP TABLE IF EXISTS tc_h;
DROP TABLE IF EXISTS tc_v;
DROP TABLE IF EXISTS tc_m;
DROP TABLE IF EXISTS tb_w;
DROP TABLE IF EXISTS tb_h;
DROP TABLE IF EXISTS tb_m;
DROP TABLE IF EXISTS tb_v;
DROP TABLE IF EXISTS ta_w;
DROP TABLE IF EXISTS ta_i;
DROP TABLE IF EXISTS ta_h;
DROP TABLE IF EXISTS ta_r;
DROP TABLE IF EXISTS ta_v;
DROP TABLE IF EXISTS ta_m;

CREATE TABLE ta_m (
	id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	label VARCHAR(255) NOT NULL,
	UNIQUE UQ_TA_M (label)
) CHARSET = latin1;

CREATE TABLE ta_v (
	`date` DATE NOT NULL,
	ta_m TINYINT UNSIGNED NOT NULL,
	PRIMARY KEY PK_TA_V (`date`, ta_m)
) CHARSET = latin1;

CREATE TABLE ta_h (
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	parent SMALLINT UNSIGNED NOT NULL,
	ta_m TINYINT UNSIGNED NOT NULL,
	UNIQUE UQ_TA_H (parent, ta_m),
	INDEX IX_TA_H (ta_m)
) CHARSET = latin1;

CREATE TABLE ta_w (
	ta_h SMALLINT UNSIGNED NOT NULL,
	`date` DATE NOT NULL,
	PRIMARY KEY PK_TA_W (`date`, ta_h)
) CHARSET = latin1;

CREATE TABLE tb_v (
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`date` DATE NOT NULL,
	`table` CHAR(1) NOT NULL,
	UNIQUE UQ_TB_V (`table`, `date`)
) CHARSET = latin1;

CREATE TABLE tb_m (
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	label VARCHAR(255) NOT NULL,
	UNIQUE UQ_TB_M (label)
) CHARSET = latin1;

CREATE TABLE tb_h (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	parent INT UNSIGNED NOT NULL,
	tb_m SMALLINT UNSIGNED NOT NULL,
	UNIQUE UQ_TB_H (parent, tb_m)
) CHARSET = latin1;

CREATE TABLE tb_w (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	tb_h INT UNSIGNED NOT NULL,
	tb_v SMALLINT UNSIGNED NOT NULL,
	UNIQUE UQ_TB_W (tb_v, tb_h)
) CHARSET = latin1;

CREATE TABLE tc_m (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	ta_h SMALLINT UNSIGNED NOT NULL,
	code SMALLINT UNSIGNED NOT NULL,
	label VARCHAR(21841) CHARACTER SET utf8 NOT NULL,
	UNIQUE UQ_TC_M (ta_h, code, label(1022)),
	INDEX IX_TC_M (code)
) CHARSET = latin1;

CREATE TABLE tc_v (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	tc_m INT UNSIGNED NOT NULL,
	`date` DATE NOT NULL,
	UNIQUE UQ_TC_V (`date`, tc_m),
	INDEX IX_TC_V (tc_m)
) CHARSET = latin1;

CREATE TABLE tc_h (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	parent BIGINT UNSIGNED NOT NULL,
	tc_v BIGINT UNSIGNED NOT NULL,
	UNIQUE UQ_TC_H (parent, tc_v),
	INDEX IX_TC_H (tc_v)
) CHARSET = latin1;

CREATE TABLE tc_r (
	a BIGINT UNSIGNED NOT NULL,
	b BIGINT UNSIGNED NOT NULL,
	PRIMARY KEY PK_TC_R (a, b),
	INDEX IX_TC_R (b)
) CHARSET = latin1;

CREATE TABLE tc_d (
	`old` BIGINT UNSIGNED NOT NULL,
	`new` BIGINT UNSIGNED NOT NULL,
	PRIMARY KEY PK_TC_D (`old`, `new`),
	INDEX IX_TC_D (`new`)
) CHARSET = latin1;

CREATE TABLE td_v (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`date` DATE NOT NULL,
	`table` CHAR(1) NOT NULL,
	UNIQUE UQ_TD_V (`table`, `date`)
) CHARSET = latin1;

CREATE TABLE td_m (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	tc_h BIGINT UNSIGNED NOT NULL,
	td_v INT UNSIGNED NOT NULL,
	UNIQUE UQ_TD_M (td_v, tc_h)
) CHARSET = latin1;

CREATE TABLE td_i (
	td_m BIGINT UNSIGNED NOT NULL,
	tb_h INT UNSIGNED NOT NULL,
	val INT NOT NULL,
	PRIMARY KEY PK_TD_I (td_m, tb_h),
	INDEX IX_TD_I (tb_h),
	INDEX IX_TD_I_VAL (val)
) CHARSET = latin1;

CREATE TABLE td_n (
	td_m BIGINT UNSIGNED NOT NULL,
	tb_h INT UNSIGNED NOT NULL,
	val NUMERIC(9, 2) NOT NULL,
	PRIMARY KEY PK_TD_N (td_m, tb_h),
	INDEX IX_TD_N (tb_h)
) CHARSET = latin1;

CREATE TABLE td_t (
	td_m BIGINT UNSIGNED NOT NULL,
	tb_h INT UNSIGNED NOT NULL,
	val VARCHAR(255) CHARACTER SET utf8 NOT NULL,
	PRIMARY KEY PK_TD_T (td_m, tb_h),
	INDEX IX_TD_T (tb_h)
) CHARSET = latin1;
