-- delete the old colonies and colony_resources tables
DROP TABLE colony_defs;
DROP TABLE col_resources;
DROP TABLE col_buildings;
DROP TABLE corporation_defs;

-- statistics for the colonies
CREATE TABLE colony_defs (
col_id			int,			-- the identifier for the colony (primary key)
name			varchar(80),	-- the name of the colony
corp_id			int,			-- the identifier for the corporate owner (foreign key)
planet_id		int,			-- the identifier for the planet (foreign key)
cur_pop			int,			-- the current population of the colony
housing			int,			-- the amount of housing available in the colony
immi_rate		int,			-- rate at which immigrants move into the colony
emi_rate		int,			-- rate at which emigrants move out of the colony
birth_rate		int,			-- rate at which new citizens are born into the colony
death_rate		int,			-- rate at which citizens die out of the colony
pollution		int,			-- pollution levels in the colony
health			int,			-- average health level of the citizens
hunger			int,			-- percentage of population that is hungry
morale			int,			-- average happiness of the citizens
productivity	int,			-- the productivity of the workforce
overcrowding	int,			-- measure of the overcrowding in the colony
rule_of_law		int,			
culture			int,
crime_rate		int,
domestic_sec	int,
foreign_sec		int,
popularity		int,
research_rate	int,			-- rate at which research units are produced
construct_rate	int,			-- rate at which construction units are produced
power_level		int				-- power units available for consumption
);

-- association between resources and colonies
CREATE TABLE col_resources (
res_id			int,			-- identifier of the resource in question (foreign key)
col_id			int,			-- identifier of the colony in question (foreign key)
cur_units		int,			-- current units of the resource at the colony
max_units		int,			-- maximum units of the resource at the colony
consumption		int				-- current rate of consumption of these resources
);

-- association between colonies and buildings
CREATE TABLE col_buildings (
col_id			int, 			-- identifier of the colony in question (foreign key)
build_id		int, 			-- identifier of the building in question (foreign key)
const_units		int,			-- number of construction units spent on building (used in construction phase)
phase			int				-- state (construction, const_paused, const_repair, on, off)
);

-- statistics for corporations
CREATE TABLE corp_defs (
corp_id 		int,			-- identifier of the corporation (primary key)
name			varchar(80)		-- name of the corporation
);