BEGIN TRANSACTION;
CREATE TABLE "weapon_fire" (
	`gameid`	INTEGER NOT NULL,
	`robotid`	INTEGER NOT NULL,
	`time`	TEXT NOT NULL,
	`bullettype`	TEXT,
	`speed`	REAL,
	`frequency`	REAL,
	PRIMARY KEY(`gameid`,`robotid`,`time`)
);
CREATE TABLE "team_data_statistics" (
	`game_id`	INTEGER NOT NULL,
	`team_id`	INTEGER NOT NULL,
	`remain_total_hp`	INTEGER,
	`kill_num`	INTEGER,
	`add_bullets`	INTEGER,
	`shoot_num`	INTEGER,
	`hit_rate`	REAL,
	`make_attack_harm`	INTEGER,
	`foul_num`	INTEGER,
	`pillar_achieved`	INTEGER,
	`airport_achieved`	INTEGER,
	`mainbuff_achieved`	INTEGER,
	`recover_hp`	INTEGER,
	`first_landed`	TEXT,
	PRIMARY KEY(`game_id`,`team_id`)
);
CREATE TABLE `shortcut` (
	`game_id`	INTEGER NOT NULL,
	`shortcut_id`	INTEGER NOT NULL,
	`time`	TEXT NOT NULL,
	`status`	INTEGER NOT NULL,
	PRIMARY KEY(`game_id`,`shortcut_id`,`time`,`status`)
);
CREATE TABLE "score" (
	`gameid`	INTEGER NOT NULL,
	`winner_team`	INTEGER,
	`win_reason`	TEXT,
	`start_time`	TEXT,
	`game_duration`	INTEGER,
	PRIMARY KEY(`gameid`),
	FOREIGN KEY(`gameid`) REFERENCES `game`(`gameid`) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "robot_info" (
	`gameid`	INTEGER NOT NULL,
	`robotid`	INTEGER NOT NULL,
	`time`	TEXT NOT NULL,
	`hp`	INTEGER,
	`voltage`	REAL,
	`current`	REAL,
	`power`	REAL,
	`rssi`	INTEGER,
	PRIMARY KEY(`gameid`,`robotid`,`time`)
);
CREATE TABLE "robot_data_statistics" (
	`game_id`	INTEGER NOT NULL,
	`robot_type`	INTEGER NOT NULL,
	`robot_id`	INTEGER NOT NULL,
	`remain_hp`	INTEGER,
	`shot_num_nonmal`	INTEGER,
	`shot_num_golf`	INTEGER,
	`mileage`	REAL,
	`max_speed`	REAL,
	`recover_hp`	INTEGER,
	`freeze_num`	INTEGER,
	`land_island`	INTEGER,
	PRIMARY KEY(`game_id`,`robot_type`,`robot_id`)
);
CREATE TABLE "remainhp" (
	`gameid`	INTEGER NOT NULL,
	`robotid`	INTEGER NOT NULL,
	`remain_hp_value`	INTEGER NOT NULL DEFAULT 0,
	`status`	INTEGER NOT NULL,
	PRIMARY KEY(`gameid`,`robotid`),
	FOREIGN KEY(`gameid`) REFERENCES `game`(`gameid`) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE `punishment` (
	`game_id`	INTEGER NOT NULL,
	`punish_time`	TEXT NOT NULL,
	`punish_type`	INTEGER NOT NULL,
	`team_id`	INTEGER NOT NULL,
	PRIMARY KEY(`game_id`,`punish_time`,`punish_type`,`team_id`)
);
CREATE TABLE `pillar` (
	`game_id`	INTEGER NOT NULL,
	`pillar_id`	INTEGER NOT NULL,
	`time`	TEXT NOT NULL,
	`status`	INTEGER NOT NULL,
	PRIMARY KEY(`game_id`,`pillar_id`,`time`,`status`)
);
CREATE TABLE "mainbuff" (
	`game_id`	INTEGER NOT NULL,
	`mainbuff_id`	INTEGER NOT NULL,
	`time`	TEXT NOT NULL,
	`status`	INTEGER NOT NULL,
	`robot_id`	INTEGER NOT NULL,
	PRIMARY KEY(`game_id`,`mainbuff_id`,`time`,`status`,`robot_id`)
);
CREATE TABLE "magazine" (
	`game_id`	INTEGER NOT NULL,
	`magazine_id`	INTEGER NOT NULL,
	`time`	TEXT NOT NULL,
	`append_bullet_num`	INTEGER NOT NULL,
	PRIMARY KEY(`game_id`,`magazine_id`,`time`,`append_bullet_num`)
);
CREATE TABLE "hpchange" (
	`gameid`	INTEGER NOT NULL,
	`hpchange_id`	INTEGER NOT NULL,
	`robotid`	INTEGER NOT NULL,
	`time`	TEXT NOT NULL,
	`attack_type`	INTEGER,
	`hpflag`	TEXT,
	`hpchangevalue`	INTEGER,
	`hpchangereason`	TEXT,
	`overflow`	REAL,
	PRIMARY KEY(`gameid`,`hpchange_id`,`robotid`,`time`),
	FOREIGN KEY(`gameid`) REFERENCES `game`(`gameid`) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "gps" (
	`gameid`	INTEGER NOT NULL,
	`robotid`	INTEGER NOT NULL,
	`time`	TEXT NOT NULL,
	`x`	REAL,
	`y`	REAL,
	`z`	REAL,
	`compass`	REAL,
	PRIMARY KEY(`gameid`,`robotid`,`time`),
	FOREIGN KEY(`gameid`) REFERENCES `game`(`gameid`) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "game" (
"gameid"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"stage"  INTEGER,
"round"  INTEGER,
"redteam_id"  INTEGER,
"redteam_name"  TEXT,
"redteam_draw"  TEXT,
"redteam_emblem"  TEXT,
"redteam_college_name"  TEXT,
"redteam_college_logo"  TEXT,
"blueteam_id"  INTEGER,
"blueteam_name"  TEXT,
"blueteam_draw"  TEXT,
"blueteam_emblem"  TEXT,
"blueteam_college_name"  TEXT,
"blueteam_college_logo"  TEXT,
"redteam_score"  INTEGER,
"blueteam_scrore"  INTEGER
);
CREATE TABLE `airport` (
	`game_id`	INTEGER NOT NULL,
	`airport_id`	INTEGER NOT NULL,
	`time`	INTEGER NOT NULL,
	`airport_status`	INTEGER NOT NULL,
	PRIMARY KEY(`game_id`,`airport_id`,`time`,`airport_status`)
);
COMMIT;
