create table `apiuser` (
	id 	bigint AUTO_INCREMENT PRIMARY KEY,

	userID	int not null,
	apiKey	char(64),
	name 	char(16),

	active	int,

	first	datetime,
	last	datetime,
	tstamp	timestamp,

	unique key	userid (userID),
	key	actives (active)
	);


create table `character` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	
	userID	int not null,

	characterID int not null,
	name	char(64),

	corporationID	int not null,

	ceo	int,
	director int,

	first	datetime,
	last	datetime,
	tstamp	timestamp,

	unique key	charid (characterID),
	key	userid (userID),
	key	corpid (corporationID)
	);


create table `account` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	
	accountID	int not null,
	accountKey	int not null,

	ownerID		int not null,
	ownerType	char(4),

	balance		double,
	characterID	int,

	mine		boolean default true,

	first	datetime,
	last	datetime,
	tstamp	timestamp,

	unique key	accID (accountID),
	key	ownerkey (ownerID, accountKey)
	);


create table `transaction` (
	id	int AUTO_INCREMENT PRIMARY KEY,

	accountID	int not null,

	transactionDateTime	datetime,
	transactionID	int not null,
	typeID		int not null,
	quantity	int not null,
	price		double,
	transactionType	char(4),

	clientID	int not null,
	stationID	int not null,

	first	datetime,
	last	datetime,
	tstamp	timestamp,

	unique key	atdid (accountID,transactionID,transactionDateTime,transactionType),
	key	aid (accountID)
	);


create table `journal` (
	id	int AUTO_INCREMENT PRIMARY KEY,

	accountID	int not null,

	`date`		datetime,
	refID		int not null,
	refTypeID	int,
	ownerID1	int,
	ownerID2	int,
	argID1		int,
	amount		double,
	balance		double,
	reason		char(100),

	first	datetime,
	last	datetime,
	tstamp	timestamp,

	unique key	ardid (accountID,refID,`date`),
	key	aid (accountID)
	);
	

create table `apiNames` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	
	itemID		int not null,
	itemName	char(100),

	first	datetime,
	last	datetime,
	tstamp	timestamp,

	key	itemID (itemID),
	unique key	nameid (itemID, itemName)
	);


create table `apiTypes` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	
	typeID		int not null,
	typeName	char(100),

	first	datetime,
	last	datetime,
	tstamp	timestamp,

	key	typeID (typeID),
	unique key	nameid (typeID, typeName)
	);



drop table if exists `apiStations`;
create table `apiStations` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	tstamp	timestamp,

	stationID  INTEGER,
	stationTypeID  INTEGER,
	corporationID  INTEGER,
	solarSystemID  INTEGER,
	stationName  CHAR(100),

	unique key	staid (stationID),
	key	staname (stationName),
	key	sysid (solarSystemID),
	key	corpid (corporationID)
	);

drop table if exists `histStations`;
create table `histStations` like `apiStations`;
alter table `histStations` drop key staid;
alter table `histStations` add (
	first	datetime,
	last	datetime,

	key	first (first),
	key	last (last),
	key	staid (stationID)
	);


drop table if exists `apiSovereignty`;
create table `apiSovereignty` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	tstamp	timestamp,

	solarSystemID	INTEGER,
	allianceID	INTEGER,
	factionID	INTEGER,
	sovereigntyLevel	INTEGER,
	constellationSovereignty	INTEGER,
	
	unique key	sysid (solarSystemID),
	key	allid (allianceID)
	);

drop table if exists `histSovereignty`;
create table `histSovereignty` like `apiSovereignty`;
alter table `histSovereignty` drop key sysid;
alter table `histSovereignty` add (
	first	datetime,
	last	datetime,

	key	first (first),
	key	last (last),
	key	sysid (solarSystemID)
	);


drop table if exists `apiKills`;
create table `apiKills` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	tstamp	timestamp,

	solarSystemID	INTEGER,
	factionKills	INTEGER,
	shipKills	INTEGER,
	podKills	INTEGER,
	dataTime	datetime,

	unique key	ssdt (solarSystemID,dataTime),
	key	dt (dataTime)
	);

drop table if exists `apiJumps`;
create table `apiJumps` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	tstamp	timestamp,

	solarSystemID	INTEGER,
	shipJumps	INTEGER,
	dataTime	datetime,

	unique key	ssdt (solarSystemID,dataTime),
	key	dt (dataTime)
	);

drop table if exists `apiTasks`;
create table `apiTasks` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	tstamp	timestamp,

	taskName	CHAR(100),
	taskType	CHAR(20),
	
	lastSuccess	datetime,
	lastStatus	CHAR(100),
	nextTry		datetime,
	failCount	INTEGER,

	unique key tn (taskName),
	key	nt (nextTry)
	);

	
drop table if exists `apiCharSheet`;
create table `apiCharSheet` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	tstamp	timestamp,

	myID		INTEGER,

	myKey		varchar(50),
	valInt		integer,
	valStr		varchar(100),

	first		datetime,

	unique key	idkey (myID,myKey),
	key	first (first)
	) ENGINE=InnoDB;

drop table if exists `histCharSheet`;
create table `histCharSheet` like `apiCharSheet`;
alter table `histCharSheet` drop key idkey;
alter table `histCharSheet` add (
	last	datetime,

	key	last (last)
	);



drop table if exists `apiStandings`;
create table `apiStandings` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	tstamp	timestamp,

	myID		INTEGER,
	direction	char(4),
	otherID		INTEGER,
	standing	DOUBLE,
	first		datetime,

	unique key	stand (myID,direction,otherID),
	key	first (first)
	) ENGINE=InnoDB;

drop table if exists `histStandings`;
create table `histStandings` like `apiStandings`;
alter table `histStandings` drop key stand;
alter table `histStandings` add (
	last	datetime,

	key	last (last)
	);



drop table if exists `apiAlliances`;
create table `apiAlliances` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	tstamp	timestamp,

	allianceID	INTEGER,
	allianceName	CHAR(100),
	allianceShort	CHAR(5),

	executorID	INTEGER,
	memberCount	INTEGER,
	startDate	datetime,

	unique key	allid (allianceID),
	key	allname (allianceName)
	);

drop table if exists `histAlliances`;
create table `histAlliances` like `apiAlliances`;
alter table `histAlliances` drop key allid;
alter table `histAlliances` add (
	first	datetime,
	last	datetime,

	key	first (first),
	key	last (last)
	);

drop table if exists `apiAllianceCorporations`;
create table `apiAllianceCorporations` (
	id	int AUTO_INCREMENT PRIMARY KEY,
	tstamp	timestamp,

	allianceID	INTEGER,
	corporationID	INTEGER,
	startDate	datetime,

	unique key	alcid (allianceID,corporationID),
	unique key	cid (corporationID),
	key	allid (allianceID)
	);

drop table if exists `histAllianceCorporations`;
create table `histAllianceCorporations` like `apiAllianceCorporations`;
alter table `histAllianceCorporations` drop key alcid;
alter table `histAllianceCorporations` drop key cid;
alter table `histAllianceCorporations` add (
	first	datetime,
	last	datetime,

	key	first (first),
	key	last (last),

	key	cid (corporationID)
	);