CREATE TABLE TableNames
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	Name VARCHAR (255) NOT NULL
)
GO

CREATE TABLE TurnDescription
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	Turn INTEGER NOT NULL
)
GO

CREATE TABLE DatabaseDescription
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	NextID INTEGER NOT NULL,
	NextLockID INTEGER NOT NULL
)
GO

CREATE TABLE MapDescription 
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	Width INTEGER NOT NULL,
	Height INTEGER NOT NULL,
	CorrectedBaseAIs INTEGER NOT NULL
)
GO
	 
CREATE TABLE MapHex 
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	X INTEGER NOT NULL,
	Y INTEGER NOT NULL,
	PoliticalControl INTEGER NOT NULL,
	TerrainType INTEGER NOT NULL,
	PlanetType INTEGER NOT NULL,
	StarbaseType INTEGER NOT NULL,
	BaseEconomicPoints INTEGER NOT NULL,
	CurrentEconomicPoints INTEGER NOT NULL,
	BaseVictoryPoints INTEGER NOT NULL,
	CurrentVictoryPoints INTEGER NOT NULL,
	BaseSpeedPoints DOUBLE NOT NULL,
	CurrentSpeedPoints DOUBLE NOT NULL,

	CONSTRAINT XYIndex UNIQUE(X, Y)
)
GO
	
CREATE TABLE PoliticalTensionMatrix
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	MatrixSize INTEGER NOT NULL,
	Matrix BLOB NOT NULL
)
GO

CREATE TABLE ServCharacter
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	WONLogon VARCHAR (255) NOT NULL,
	LastLoggedOn INTEGER NOT NULL,
	MissionsPlayedVectorSize INTEGER NOT NULL,
	MissionsPlayedVector BLOB NOT NULL,
	IPAddress VARCHAR (15) NOT NULL,
	NextMissionTitle VARCHAR (255) NOT NULL,
	NextMissionScore INTEGER NOT NULL,
	HomeWorldLocationX INTEGER NOT NULL,
	HomeWorldLocationY INTEGER NOT NULL,
	LastMustPlayLocationX INTEGER NOT NULL,
	LastMustPlayLocationY INTEGER NOT NULL,
	PreparedMissionsID INTEGER NOT NULL,
	OpenShipBidsSize INTEGER NOT NULL,
	OpenShipBids BLOB NOT NULL,
	CharacterName VARCHAR (255) NOT NULL,
	ShipCacheVectorSize INTEGER NOT NULL,
	ShipCacheVector BLOB NOT NULL,
	CharacterRace INTEGER NOT NULL,
	CharacterRank INTEGER NOT NULL,
	CharacterPoliticalControl INTEGER NOT NULL,
	Flags INTEGER NOT NULL,
	CharacterLastBattleResult DOUBLE NOT NULL,
	CharacterLocationX INTEGER NOT NULL,
	CharacterLocationY INTEGER NOT NULL,
	CharacterRating INTEGER NOT NULL,
	Medals INTEGER NOT NULL,
	CharacterCurrentPrestige INTEGER NOT NULL,
	CharacterLifetimePrestige INTEGER NOT NULL,
	CharacterBattlesPlayed INTEGER NOT NULL,
	MissionSlotIdx INTEGER NOT NULL,
	MoveCompletes INTEGER NOT NULL,
	MoveDestinationX INTEGER NOT NULL,
	MoveDestinationY INTEGER NOT NULL
)
GO

CREATE INDEX CharNameIndex ON ServCharacter (CharacterName)
GO

CREATE INDEX WONLogonIndex ON ServCharacter (WONLogon)
GO

CREATE TABLE BidItem
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	BiddingHasBegun BIT NOT NULL,
	ItemName VARCHAR (255) NOT NULL,
	ItemID INTEGER NOT NULL,
	ItemInfo INTEGER NOT NULL,
	AuctionValue INTEGER NOT NULL,
	AuctionRate DOUBLE NOT NULL,
	TurnOpened INTEGER NOT NULL,
	TurnToClose INTEGER NOT NULL,
	CurrentBid INTEGER NOT NULL,
	BidOwnerID INTEGER NOT NULL,
	TurnBidMade INTEGER NOT NULL,
	BidMaximum INTEGER NOT NULL
)
GO
	
CREATE INDEX BidOwnerIndex ON BidItem (BidOwnerID)
GO

CREATE INDEX ItemIndex ON BidItem (ItemID)
GO

CREATE TABLE Ship
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	Owner INTEGER NOT NULL,
	IsInAuction BIT NOT NULL,
	Race INTEGER NOT NULL,
	ClassType INTEGER NOT NULL,
	BPV INTEGER NOT NULL,
	EPV INTEGER NOT NULL,
	ShipClassName VARCHAR (255) NOT NULL,
	TurnCreated INTEGER NOT NULL,
	Name VARCHAR (255) NOT NULL,

	DamageSize INTEGER NOT NULL,
	Damage BLOB NOT NULL,

	StoresSize INTEGER NOT NULL,
	Stores BLOB NOT NULL,

	OfficersSize INTEGER NOT NULL,
	Officers BLOB NOT NULL,

	Flags INTEGER NOT NULL
)
GO
		
CREATE TABLE CampaignInfo
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	TemplateName VARCHAR (255) NOT NULL,
	Description VARCHAR (255) NOT NULL,
	EarlyMapName VARCHAR (255) NOT NULL,
	MidMapName VARCHAR (255) NOT NULL,
	LateMapName VARCHAR (255) NOT NULL,
	MissionListSize INTEGER NOT NULL,
	MissionList BLOB NOT NULL,
	RaceListSize INTEGER NOT NULL,
	RaceList BLOB NOT NULL,
	CampaignEra INTEGER NOT NULL,
	CampaignDifficulty INTEGER NOT NULL,
	TriggerPrestige INTEGER NOT NULL,
	TriggerMission VARCHAR (255) NOT NULL,
	CampaignName VARCHAR (255) NOT NULL,
	CharacterName VARCHAR (255) NOT NULL,
	CharacterRace INTEGER NOT NULL,
	ServerPassword VARCHAR (255) NOT NULL,
	CurrentTimeSize INTEGER NOT NULL,
	CurrentTime BLOB NOT NULL,
	PlayersCurrent INTEGER NOT NULL,
	PlayersMax INTEGER NOT NULL,
	PlayersLoggedOnCurrent INTEGER NOT NULL,
	PlayersLoggedOnMax INTEGER NOT NULL,
	ServerVersion VARCHAR (255) NOT NULL,
	ValidClientVersion VARCHAR (255) NOT NULL
)
GO

CREATE TABLE Notify
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	Category INTEGER NOT NULL,
	NotifyEvent INTEGER NOT NULL,
	ExtraID INTEGER NOT NULL,
	NameToNotifySize INTEGER NOT NULL,
	NameToNotify BLOB NOT NULL,
	DataID INTEGER NOT NULL
)
GO

CREATE TABLE NewsStory
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	PersistenceLevel INTEGER NOT NULL,
	UrgencyLevel INTEGER NOT NULL,
	Category INTEGER NOT NULL,
	IdiomSize INTEGER NOT NULL,
	Idiom BLOB NOT NULL,
	NewsTime INTEGER NOT NULL,
	TimeDetail INTEGER NOT NULL,
	Color INTEGER NOT NULL
)
GO

CREATE TABLE PreparedMissions
(
	ID INTEGER PRIMARY KEY,
	LOCKED DATETIME,
	LOCKID INTEGER,

	CharacterID INTEGER NOT NULL,
	MissionProfilesSize INTEGER NOT NULL,
	MissionProfiles BLOB NOT NULL,
	HaveTriggeredMission INTEGER NOT NULL,
	NearbyCharactersSize INTEGER NOT NULL,
	NearbyCharacters BLOB NOT NULL,
	NearbyHumanIDsSize INTEGER NOT NULL,
	NearbyHumanIDs BLOB NOT NULL,
	NumHumanPlayers INTEGER NOT NULL,
	X INTEGER NOT NULL,
	Y INTEGER NOT NULL
)
GO

CREATE INDEX CharacterIDIndex ON PreparedMissions (CharacterID)
GO

CREATE TABLE Battles
(
	ID				INTEGER PRIMARY KEY,
	LOCKED			DATETIME,
	LOCKID			INTEGER,

	MissionName		VARCHAR (255) NOT NULL,
	BeginTime		DATETIME NOT NULL,
	EndTime			DATETIME NOT NULL,
	X				INTEGER NOT NULL,
	Y				INTEGER NOT NULL,
	HostID			INTEGER NOT NULL,
	NumParticipants	INTEGER NOT NULL,

	HexBeforeDV		INTEGER NOT NULL,
	HexBeforeRace	INTEGER NOT NULL,
	HexAfterDV		INTEGER NOT NULL,
	HexAfterRace	INTEGER NOT NULL
)
GO

CREATE TABLE BattleParticipant
(
	ID				INTEGER PRIMARY KEY,
	BattleID		INTEGER NOT NULL,
	CharacterID		INTEGER NOT NULL,
	MissionSlot		INTEGER NOT NULL,
	VictoryLevel	INTEGER NOT NULL,
	RegularPrestige	INTEGER NOT NULL,
	BonusPrestige	INTEGER NOT NULL,
	Medal			INTEGER NOT NULL,
	CampaignEvent	INTEGER NOT NULL,
	IsAI			BIT NOT NULL
)
GO

CREATE TABLE BattleParticipantShips
(
	BattleParticipantID	INTEGER NOT NULL,
	BPV					INTEGER NOT NULL,
	ShipClassName		VARCHAR (255) NOT NULL,
	DamagePercentAtEnd	INTEGER NOT NULL,
	Destroyed			BIT NOT NULL
)
GO

CREATE TABLE BattlesRunning
(
	ID				INTEGER PRIMARY KEY,
	LOCKED			DATETIME,
	LOCKID			INTEGER,

	BeginTime		INTEGER NOT NULL,
	MissionName		VARCHAR (255) NOT NULL,
	Participants		VARCHAR (255) NOT NULL
)
GO

CREATE TABLE LoginBan
(
	WONName				VARCHAR (255),
	CharacterName		VARCHAR (255),
	IPAddress			VARCHAR (255)
)
GO

insert into TableNames ( ID, Name )
	Values
	(  2, 'TableNames'					)
GO
insert into TableNames ( ID, Name )
	Values
	(  3, 'TurnDescription'					)
GO
insert into TableNames ( ID, Name )
	Values
	(  4, 'DatabaseDescription'				)
GO
insert into TableNames ( ID, Name )
	Values
	(  5, 'MapDescription'					)
GO
insert into TableNames ( ID, Name )
	Values
	(  6, 'MapHex'						)
GO
insert into TableNames ( ID, Name )
	Values
	(  7, 'PoliticalTensionMatrix'			)
GO
insert into TableNames ( ID, Name )
	Values
	(  8, 'ServCharacter'					)
GO
insert into TableNames ( ID, Name )
	Values
	(  9, 'BidItem'						)
GO
insert into TableNames ( ID, Name )
	Values
	( 10, 'Ship'						)
GO
insert into TableNames ( ID, Name )
	Values
	( 11, 'CampaignInfo'					)
GO
insert into TableNames ( ID, Name )
	Values
	( 12, 'Notify'						)
GO
insert into TableNames ( ID, Name )
	Values
	( 13, 'NewsStory'						)
GO
insert into TableNames ( ID, Name )
	Values
	( 14, 'PreparedMissions'				)
GO

insert into DatabaseDescription ( ID, Locked, LockID, NextID, NextLockID ) Values( 1, '1969-12-31 19:00:00', 0, 15, 1 )
GO
