BEGIN TRANSACTION;
DROP TABLE IF EXISTS `aspect`;
CREATE TABLE IF NOT EXISTS `aspect` (
	`id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	`medaltype_id`	INTEGER NOT NULL,
	`name`	TEXT NOT NULL UNIQUE CHECK(`name` <> ''),
	`desc`	TEXT NOT NULL
);
DROP TABLE IF EXISTS `meta`;
CREATE TABLE IF NOT EXISTS `meta` (
	`version`	INTEGER NOT NULL
);
DROP TABLE IF EXISTS `medal`;
CREATE TABLE IF NOT EXISTS `medal` (
	`id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	`entity_id`	INTEGER NOT NULL,
	`aspect_id`	INTEGER NOT NULL,
	`value`	INTEGER NOT NULL,
	`created_at`	INTEGER NOT NULL,
	FOREIGN KEY(`aspect_id`) REFERENCES `aspect`(`id`),
	FOREIGN KEY(`entity_id`) REFERENCES `entity`(`id`)
);
DROP TABLE IF EXISTS `group`;
CREATE TABLE IF NOT EXISTS `group` (
	`id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`name`	TEXT NOT NULL UNIQUE CHECK(`name` <> ''),
	`desc`	TEXT NOT NULL
);
DROP TABLE IF EXISTS `entity`;
CREATE TABLE IF NOT EXISTS `entity` (
	`id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`name`	TEXT NOT NULL UNIQUE CHECK(`name` <> ''),
	`desc`	TEXT NOT NULL
);
DROP TABLE IF EXISTS `group_entity`;
CREATE TABLE IF NOT EXISTS `group_entity` (
	`group_id`	INTEGER NOT NULL,
	`entity_id`	INTEGER NOT NULL,
	FOREIGN KEY(`group_id`) REFERENCES `group`(`id`),
	FOREIGN KEY(`entity_id`) REFERENCES `entity`(`id`),
	PRIMARY KEY(`group_id`, `entity_id`)
);
DROP TABLE IF EXISTS `parent_entity`;
CREATE TABLE IF NOT EXISTS `parent_entity` (
	`parent_id`	INTEGER NOT NULL,
	`entity_id`	INTEGER NOT NULL,
	FOREIGN KEY(`parent_id`) REFERENCES `entity`(`id`),
	FOREIGN KEY(`entity_id`) REFERENCES `entity`(`id`),
	PRIMARY KEY(`parent_id`, `entity_id`)
);
INSERT INTO meta (version) VALUES (0);
COMMIT;