
/* Tabulka vedeckeho stromu */

create table SCTREE
(
    IDUSER bigint not null,
    IDNODE bigint not null,
    ASSIGNEDPTS bigint,
    INT1 bigint,
    ICOMP1 bigint,
    DCOMP1 double precision,
    ICOMP2 bigint,
    DCOMP2 double precision,
    ICOMP3 bigint,
    DCOMP3 double precision,
    ICOMP4 bigint,
    DCOMP4 double precision,
    ICOMP5 bigint,
    DCOMP5 double precision
);

/* Primarni klice a indexy k tabulce SCTREE */
alter table SCTREE add primary key (IDUSER, IDNODE);
create index SCTREE_IDUSER on SCTREE (IDUSER);

/* tabulka vzoru */

create table ITEMPATTERNS
(
    IDPATTERN bigint not null,
    DENSITY bigint,
    TRANSPORT bigint,
    CONSULREI bigint,
    CONSTITANIUM bigint,
    CONSDEUTERIUM bigint,
    CONSHEAVYWATER bigint,
    REPAIRABILITY bigint,
    MOUNTING bigint,
    MINWEIGHT bigint,
    MAXWEIGHT bigint
);

/* Primarni klic */
alter table ITEMPATTERNS add primary key (IDPATTERN);

/* tabulka itemu */

create table ITEMS
(
    IDITEM bigint not null,
    PATTERN bigint not null,
    WEIGHT bigint,
    DAMAGE double precision,
    SHADOWDAMAGE double precision,
    WORKING bigint,
    DAMAGED bigint,
    SHADOWDAMAGED bigint,
    D1 double precision,
    I1 bigint,
    I2 bigint,
    I3 bigint,
    I4 bigint,
    I5 bigint,
    I6 bigint
);

/* pridani primarniho klice do tabulky items */
alter table ITEMS add primary key (IDITEM);

/* tabulka fortressu */
create table FORTRESSES
(
    IDFORTRESS bigint not null,
	IDREALM bigint,
    FORTRESSNAME varchar(32),
    WEIGHT bigint,
    TSINTEGRITY bigint,
    SINTEGRITY bigint,
    SHIPLENGTH double precision,
    SHIPWIDTH double precision,
    SHIPHEIGHT double precision,
    FORTRESSTYPE bigint,

    /* suroviny */
    QGPLASMA double precision,              
    ULREA double precision,                
    HEAVYWATER double precision,            
    DEUTERIUM double precision,             
    ZCRYSTAL double precision,             
    ENERGY double precision,                
    ILMENITE double precision,              
    TITANIUM double precision,   
	
    /* upgrady surovin */
    QGPLASMAGRAD double precision,              
    ULREAGRAD double precision,                
    HEAVYWATERGRAD double precision,            
    DEUTERIUMGRAD double precision,             
    ZCRYSTALGRAD double precision,             
    ENERGYGRAD double precision,                
    ILMENITEGRAD double precision,              
    TITANIUMGRAD double precision,   	  

    /* doba, kdy byly suroviny posledne upgradovany */
    UPDQGPLASMA double precision,              
    UPDULREA double precision,                
    UPDCONRES double precision,                        
    UPDENERGY double precision,                
    
    /* triggery */
    QGPLASMATRIGGER double precision,
    CRTRIGGER double precision,
    ENERGYTRIGGER double precision,
    
    /* misc */
    TARGET bigint,
    LTAIMTIME double precision,

    /* energetic system */	  
    ENE_ACCUMULATOR bigint,
    ENE_ESC_ENERGY bigint,
    ENE_ESC_TENERGY bigint,
    
    /* deflector shield */
    DEF_SHIELDGENERATOR bigint, 
    DEF_ESC1_ENERGY bigint,
    DEF_ESC1_TENERGY bigint,
    DEF_EDC_PRIORITY smallint,
    DEF_EDC_RATIO bigint,
    DEF_EDC_MPD double precision,
    DEF_EDC_SDOEL bigint,
    
	/* zbranovy system */   
    WEP_ENERGYCIRCUIT bigint, 
    WEP_DRONEMISSILES bigint, 
    WEP_EDC_PRIORITY smallint,
    WEP_EDC_RATIO bigint,
    WEP_EDC_MPD double precision,
    WEP_EDC_SDOEL bigint,    
    
    /* transportni paprsek */
	TBM_GENERATOR bigint, 
	TBM_ESC_ENERGY bigint,
	TBM_ESC_TENERGY bigint,
    TBM_EDC_PRIORITY smallint,
    TBM_EDC_RATIO bigint,
    TBM_EDC_MPD double precision,
    TBM_EDC_SDOEL bigint, 	    
    
    /* mining devices */    
    MIN_MAGCOLLECTORS bigint,
    MIN_FILTRATIONTANK bigint, 
    MIN_MININGMACHINE bigint, 
    MIN_DRILLINGMACHINE bigint,
    MIN_QGQUARRYING double precision,
    MIN_DEQUARRYING double precision,
    MIN_ZCQUARRYING double precision,
    MIN_ILQUARRYING double precision,
    
    /* senzory */
    SEN_HIRESSENSORS bigint,
    SEN_LONGRANGESENSORS bigint,
	SEN_PSC1_STATE bigint,
	SEN_PSC1_CHARGING double precision,
    SEN_EDC1_PRIORITY smallint,
    SEN_EDC1_RATIO bigint,
    SEN_EDC1_MPD double precision,
    SEN_EDC1_SDOEL bigint, 	
	SEN_PSC2_STATE bigint,
	SEN_PSC2_CHARGING double precision,
    SEN_EDC2_PRIORITY smallint,
    SEN_EDC2_RATIO bigint,
    SEN_EDC2_MPD double precision,
    SEN_EDC2_SDOEL bigint, 			
	 
	/* cloaking */
    CLO_CLOAKINGDEVICE bigint,
	CLO_PSC_STATE bigint,
	CLO_PSC_CHARGING double precision,
    CLO_EDC_PRIORITY smallint,
    CLO_EDC_RATIO bigint,
    CLO_EDC_MPD double precision,
    CLO_EDC_SDOEL bigint, 	 	
	
	 
	/* posadka - nezranena */
    CRE_CREW_CAPITAN bigint, 
    CRE_CREW_DOCTOR bigint,     
    CRE_CREW_OFFICER bigint, 
    CRE_CREW_ENGINEER bigint,     
    CRE_CREW_PILOT bigint, 
    CRE_CREW_SCIENTIST bigint,
    CRE_CREW_PASSENGER bigint,
    
    /* posadka - zranena */
    CRE_WOUNDED_CAPITAN bigint, 
    CRE_WOUNDED_DOCTOR bigint,     
    CRE_WOUNDED_OFFICER bigint, 
    CRE_WOUNDED_ENGINEER bigint, 
	CRE_WOUNDED_PILOT bigint,   
    CRE_WOUNDED_SCIENTIST bigint, 
    CRE_WOUNDED_PASSENGER bigint,
	 
	/* akomodace */
    ACC_LUXURYAPARTMENTS bigint,
    ACC_COMFORTABLEROOMS bigint, 
    ACC_COMMONCABINS bigint,  
    ACC_WAREHOUSES bigint, 
    
    /* androidi */
    MEC_ANDROIDS bigint,
	MEC_PSC_STATE bigint,
	MEC_PSC_CHARGING double precision,
    MEC_EDC_PRIORITY smallint,
    MEC_EDC_RATIO bigint,
    MEC_EDC_MPD double precision,
    MEC_EDC_SDOEL bigint, 
	MEC_MAX_WORK_ANDR bigint,
	MEC_MAX_REPA_ANDR bigint,
	MEC_ACT_REPA_ANDR bigint,
	MEC_ACT_CONS_ANDR bigint,	     
    
    /* skladiste */
    WAR_MAGNETICALGRID bigint, 
    WAR_CONTAINER bigint, 
    WAR_ENERGETICCELL bigint, 
    
    /* other equipment */
    OTH_PLASMAFILTER bigint, 
    OTH_LABS bigint,
    OTH_LIFESUPPORT bigint, 
    OTH_TRANSMITTER bigint, 
    OTH_EDC_PRIORITY smallint,
    OTH_EDC_RATIO bigint,
    OTH_EDC_MPD double precision,
    OTH_EDC_SDOEL bigint, 	 
    
    /* mainframe */
    MAI_MAINFRAME bigint,
    MAI_EDC_PRIORITY smallint,
    MAI_EDC_RATIO bigint,
    MAI_EDC_MPD double precision,
    MAI_EDC_SDOEL bigint, 	     

    /* polozky specificke pro lod */
    SUB_COMPUTER bigint,
    SUB_QGCORE bigint,
    SUB_BEACON bigint,
    SUB_DAMPERS bigint,
    
    IMP_JETENGINE bigint,
    IMP_MECHDAMPERS bigint,
    
    /* polozky specificke pro zakladnu */
    FAC_MAGNETICALFILTER bigint,
    FAC_DEUTERIUMDECOMP bigint,
    FAC_ZIRCONIUMREACTOR bigint,
    FAC_ILMENITEFURNACE bigint,
	
	IMP_ACTUALFORCE bigint,
	
	POSITIONX bigint,
    POSITIONY bigint,
    COURSEX double precision,
    COURSEY double precision,
    SPEEDNORM bigint,            
    ACCELERATION bigint,          
    SPOBJACTUALLYIN bigint,      
    NEARESTSTAR bigint,       
    WESPOBJ bigint,             
    MOV_STATE bigint,           
    MOV_WARP_TARGET_X bigint,  
    MOV_WARP_TARGET_Y bigint,     
    MOV_WARP_START double precision,
    ENGINE_LIMIT double precision, 
    TARGETSPACEOBJECT bigint,     
    TARGETSHIP bigint,           
    TARGETPOSITIONX bigint,     
    TARGETPOSITIONY bigint,     
    LASTACTUALIZATION double precision, 
    SHIPMOVEACTION bigint,    
    SHIPMOVETARGET bigint,
	FORTRESSCLASSCLASS bigint,
	SUB_ACTUALSPEEDTOC double precision,
	GRAVSPEEDNORM bigint,
	STRUCTINTGRAD double precision,
	CONTROLLABLE bigint,
	
	FS_VOI_ANDR_PRIORITY bigint,  
	FS_VOI_ANDR_RATIO bigint,
	FS_VOI_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_ENE_ANDR_PRIORITY bigint,  
	FS_ENE_ANDR_RATIO bigint,
	FS_ENE_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_DEF_ANDR_PRIORITY bigint,  
	FS_DEF_ANDR_RATIO bigint,
	FS_DEF_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_WEP_ANDR_PRIORITY bigint,  
	FS_WEP_ANDR_RATIO bigint,
	FS_WEP_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_TBM_ANDR_PRIORITY bigint,  
	FS_TBM_ANDR_RATIO bigint,
	FS_TBM_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_MIN_ANDR_PRIORITY bigint,  
	FS_MIN_ANDR_RATIO bigint,
	FS_MIN_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_PRO_ANDR_PRIORITY bigint,  
	FS_PRO_ANDR_RATIO bigint,
	FS_PRO_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_SEN_ANDR_PRIORITY bigint,  
	FS_SEN_ANDR_RATIO bigint,
	FS_SEN_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_CLO_ANDR_PRIORITY bigint,  
	FS_CLO_ANDR_RATIO bigint,
	FS_CLO_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_CRE_ANDR_PRIORITY bigint,  
	FS_CRE_ANDR_RATIO bigint,
	FS_CRE_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_ACC_ANDR_PRIORITY bigint,  
	FS_ACC_ANDR_RATIO bigint,
	FS_ACC_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_MEC_ANDR_PRIORITY bigint,  
	FS_MEC_ANDR_RATIO bigint,
	FS_MEC_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_WAR_ANDR_PRIORITY bigint,  
	FS_WAR_ANDR_RATIO bigint,
	FS_WAR_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_OTH_ANDR_PRIORITY bigint,  
	FS_OTH_ANDR_RATIO bigint,
	FS_OTH_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_MAI_ANDR_PRIORITY bigint,  
	FS_MAI_ANDR_RATIO bigint,
	FS_MAI_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_SUB_ANDR_PRIORITY bigint,  
	FS_SUB_ANDR_RATIO bigint,
	FS_SUB_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_IMP_ANDR_PRIORITY bigint,  
	FS_IMP_ANDR_RATIO bigint,
	FS_IMP_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_HAN_ANDR_PRIORITY bigint,  
	FS_HAN_ANDR_RATIO bigint,
	FS_HAN_ANDR_ASSIGNED_DROIDS bigint,
	
	FS_FAC_ANDR_PRIORITY bigint,  
	FS_FAC_ANDR_RATIO bigint,
	FS_FAC_ANDR_ASSIGNED_DROIDS bigint																									         
);

alter table FORTRESSES add primary key (IDFORTRESS);
create index FORTRESSES_IDREALM on FORTRESSES(IDREALM);

create table F_PRO_PROBES 
(
	IDFORTRESS bigint not null,
	IDITEMOFLIST bigint not null
);

alter table F_PRO_PROBES add primary key (IDFORTRESS, IDITEMOFLIST);
create index F_PRO_PROBES_IDFORTRESS on F_PRO_PROBES(IDFORTRESS);

create table F_WAR_STORAGE 
(
	IDFORTRESS bigint not null,
	IDITEMOFLIST bigint not null
);

alter table F_WAR_STORAGE add primary key (IDFORTRESS, IDITEMOFLIST);
create index F_WAR_STORAGE_IDFORTRESS on F_WAR_STORAGE(IDFORTRESS);


create table F_HAN_HANGARS  
(
	IDFORTRESS bigint not null,
	IDITEMOFLIST bigint not null
);

alter table F_HAN_HANGARS add primary key (IDFORTRESS, IDITEMOFLIST);
create index F_HAN_HANGARS_IDFORTRESS on F_HAN_HANGARS(IDFORTRESS);

create table F_WEP_DRONEMISSILELAUNCHERS
(
	IDFORTRESS bigint not null,
	IDITEMOFLIST bigint not null,
	ENERGY bigint,
	TARGETENERGY bigint	
);

alter table F_WEP_DRONEMISSILELAUNCHERS add primary key (IDFORTRESS, IDITEMOFLIST);
create index F_WEP_DRONEMISSILELAUNCHERS_FR on F_WEP_DRONEMISSILELAUNCHERS(IDFORTRESS);

create table F_WEP_LASERGUNS
(
	IDFORTRESS bigint not null,
	IDITEMOFLIST bigint not null,
	ENERGY bigint,
	TARGETENERGY bigint	
);

alter table F_WEP_LASERGUNS add primary key (IDFORTRESS, IDITEMOFLIST);
create index F_WEP_LASERGUNS_FR on F_WEP_LASERGUNS(IDFORTRESS);

create table SPACEOBJECTS
(
	IDSPACEOBJECT bigint not null,
	PARENTOBJECT bigint,
	OBJTYPE bigint,
	IDFORTRESS bigint,
	POSX  bigint,
	POSY  bigint,
	ARADIUS  bigint,
	ORADIUS  bigint,
	WEIGHT  double precision,
	DISTANCEFROMPOBJ bigint,
	BROTANGLE bigint,
	ANGULARVELOCITY bigint,
	SUNTYPE bigint,
	YEARPERIOD bigint,
	DAYPERIOD bigint,
	PLANETTYPE bigint,
	DAYTEMPERATURE bigint,
	NIGHTTEMPERATURE bigint,
	SEA bigint,
	HWSOURCE bigint,
	ZISOURCE bigint,
	ILSOURCE bigint
);

alter table SPACEOBJECTS add primary key (IDSPACEOBJECT);


create table FORTRESSCLASSES
(
	FORTRESSCLASSID bigint not null,
	CLASSNAME VARCHAR(64),
	CLASSTYPE bigint,
	MINWEIGHT bigint,
	MAXWEIGHT bigint,
	MINTSI bigint,
	MAXTSI bigint,
	MAXLENGTH double precision,
	MAXWIDTH double precision,
	MAXHEIGHT double precision,
	MAXDML bigint,
	MAXLASERGUNS bigint,
	MAXWEAPONSTOTAL bigint,
	ACCPOINTSTOTAL bigint,
	QRATING double precision,
	MRATING double precision
);


alter table FORTRESSCLASSES add primary key (FORTRESSCLASSID);

create table PLAYERS
(
	IDREALM bigint not null,
	PLNAME     varchar(64) unique,
	PLLOGIN    varchar(32) unique,
	PLPASSWORD varchar(32),
	HASBAN bigint,
	BANLEVEL bigint,
	BANDURATION bigint,
	BANREASON varchar(256),
	BANTIME double precision
);

alter table PLAYERS add primary key (IDREALM);
create index PLAYERS_PLLOGIN on PLAYERS(PLLOGIN);

create table DISCOVERED_STARS  
(
	IDREALM bigint not null,
	IDITEMOFLIST bigint not null
);

alter table DISCOVERED_STARS add primary key (IDREALM, IDITEMOFLIST);
create index DISCOVERED_STARS_IDREALM on DISCOVERED_STARS(IDREALM);

create table SETTINGS
(
	IDENTIFICATOR bigint not null,
	DOUBLEITEM double precision,
	INTEGERITEM bigint
);

alter table SETTINGS add primary key (IDENTIFICATOR);

