create table usergroup
(
 id serial primary key,
 name varchar(255)  not null,
 in_use boolean default true not null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
);

create table operator
(
 id serial primary key,
 loginname varchar(30)  not null,
 usergroupid bigint null references usergroup(id),
 fullname varchar(255)  not null,
 password varchar(255)  not null,
 description text  null,
 regsamples bigint default 0 null,
 in_use boolean default true not null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
);

CREATE unique Index operator_loginname on operator (loginname);
CREATE index operator_usergroup on operator(usergroupid);

create table lab
(
 id serial primary key,
 name varchar(255)  not null,
 in_use boolean default true not null,
 lab boolean default false not null,
 managerid bigint null,
 description text null,
 address text null,
 tel char(20) null,
 fax char(20) null,
 homepage char(50) null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;

create table contact
(
 id serial primary key,
 name varchar(255)  null,
 adress text null,
 in_use boolean default true not null,
 labid bigint null references lab(id),
 memo text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
);


create index contact_lab on contact(labid);


create table project
(
 id serial primary key,
 name varchar(255)  not null,
 contactid bigint null references contact(id),
 labcontactid bigint null references operator(id),
 start integer null,
 stop integer null,
 deadline integer null,
 in_use boolean default true not null,
 memo text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;


create unique index idx_projectname on project(name)
;
create index project_contact on project(contactid)
;

create table contract
(
  id serial primary key,
  reference varchar(255)  not null,
  Projectid bigint not null references project(id) ,
  memo text null,
  in_use boolean default true not null,
  username varchar(30) null,
  addtime timestamp default current_timestamp
);
create unique index idx_contractref on contract(reference);
create index project_contract on contract(projectid);
              
create table samplecategory
(
 id serial primary key,
 category varchar(50)  not null,
 in_use boolean default true not null,
 memo text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp

);
create unique index idx_samplecategory on samplecategory(category);

create table samplingmethod
(
 id serial primary key,
 name varchar(255)  not null, 
 samplecategoryid bigint not null references samplecategory(id), 
 in_use boolean default true not null, 
 url varchar(255) null,
 description text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;

create index samplingmethod_category on samplingmethod(samplecategoryid)
;

create table sampletype
(
 id serial primary key,
 name varchar(50)  not null, 
 samplecategoryid bigint not null references samplecategory(id), 
 maintypeid bigint null references sampletype(id),
 in_use boolean default true not null, 
 memo text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp

);
create index idx_sampletypename on sampletype(name);
create index sampletype_category on sampletype(samplecategoryid);

create table subtype
(
 id serial primary key,
 name varchar(255)  not null, 
 in_use boolean default true not null, 
 sampletypeid bigint not null references sampletype(id), 
 memo text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;
create index fk_subtypeSampletype on subtype(sampletypeid)
;
create index idx_subtypename on subtype(name)
;

create table nuts1
(
 id serial primary key,
 name varchar(255)   not null, 
 extid varchar(20) null,
 in_use boolean default true not null, 
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;


create unique index idx_nuts1name on nuts1(name)
;


create table nuts2
(
 id serial primary key,
 name varchar(255)  not null, 
 extid varchar(20) null,
 nuts1id bigint null references nuts1(id) ,
 in_use boolean default true not null, 
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;
create unique index idx_nuts2name on nuts2(name)
;
create index fk_nuts2nuts1 on nuts2(nuts1id)
;

create table species
(
 id serial primary key,
 latname varchar(255)  not null,
 locname varchar(255)  not null,
 engname varchar(255) null,
 sampletypeid bigint not null references sampletype(id),
 in_use boolean default true not null,
 memo text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;

create index species_sampletype on species(sampletypeid)
;

create table location(
 id serial primary key,
 name varchar (255) null,
 latitude float null,
 longitude float null,
 latitudestop float null,
 longitudestop float null,
 height float null,
 nuts2id bigint null references nuts2(id),
 in_use boolean default true not null,
 memo text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
);
create index locataion_nuts2 on location(nuts2id);
create index location_lat on location(latitude);
create index location_lon on location(longitude);
create index location_name on location(name);

create table SamplerProject
(
 id serial primary key,
 projectid bigint null references project(id) ,
 samplerid bigint null references contact(id) ,
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;


create unique index ix_samplerproject on samplerproject(projectid,samplerid)
;
create unique index ix_projectsampler on samplerproject(samplerid,projectid)
;


create table LocationProject
(
 id serial primary key,
 projectid bigint null references project(id) ,
 locationid bigint null references location(id) ,
 username varchar(30) null,
 addtime timestamp default current_timestamp
);


create unique index ix_locationproject on locationproject(projectid,locationid);
create unique index ix_projectlocation on locationproject(locationid,projectid);


create table Labcontpers
( id serial primary key,
  labid bigint null references lab(id) ,
  contactid bigint null references contact(id),
  username varchar(30) null,
  addtime timestamp default current_timestamp
)
;
create unique index ix_labcontact on labcontpers(labid,contactid)
;
create unique index ix_contactlab	on labcontpers(contactid,labid)
;

create table sample
(
 id serial primary key,
 contactid bigint not null references contact(id),
 organisationid bigint  not null references lab(id) ,
 projectid bigint not null references project(id) ,
 contractid bigint not null references contract(id) ,
 responsibleid bigint not null references operator(id) ,
 samplingmethodid bigint not null references samplingmethod(id) ,
 samplerid bigint not null references contact(id) ,
 ExternalId varchar(255) null,
 SampleCategoryid bigint not null references samplecategory(id) ,
 SampleTypeid bigint not null references sampletype(id) ,
 Subtypeid bigint null references subtype(id),
 samplingdate integer ,
 samplingtime float null,
 samplingstopdate integer null,
 samplingstoptime float null,
 refdateint integer null,
 deadline integer null,
 locationid bigint null references location(id),
 splitto bigint null references lab(id),
 mainsample bigint null ,
 WetWeight float null,
 DryWeight float null,
 Wetremains float null,
 DryRemains float null,
 DryingTemp float null,
 TotalVolume float null,
 RemainsVolume float null,
 speciesid bigint null references species(id),
 preparationId bigint null,
 memo text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp,
 refdate float null
);

create index sample_contact on sample(contactid);
create index sample_organisation on sample(organisationid);
create index sample_project on sample(projectid);
create index sample_contract on sample(contractid);
create index sample_responsible on sample(responsibleid);
create index sample_sampmet on sample(samplingmethodid);
create index sample_sampler on sample(samplerid);
create index sample_category on sample(samplecategoryid);
create index sample_type on sample(sampletypeid);
create index sample_subtype on sample(subtypeid);
create index sample_samplingdate on sample(samplingdate);
create index sample_location on sample(locationid);
create index sample_splitto on sample(splitto);
create index sample_mainsample on sample(mainsample);
create index sample_species on sample(speciesid);



create table sample_log
(
 logid serial primary key,
 id bigint null,
 contactid bigint null,
 organisationid bigint null,
 projectid bigint null,
 contractid bigint null,
 responsibleid bigint null,
 sampling_metid bigint null,
 samplerid bigint null,
 ExternalId varchar(255) null,
 SampleCategoryid bigint null,
 SampleTypeid bigint null,
 Subtypeid bigint null,
 samplingdate integer  null,
 samplingtime float null,
 samplingstopdate integer null,
 samplingstoptime float null,
 refdate float null,
 deadline integer null,
 locationid bigint null,
 splitto bigint null,
 mainsample bigint null ,
 WetWeight float null,
 DryWeight float null,
 Wetremains float null,
 DryRemains float null,
 DryingTemp float null,
 TotalVolume float null,
 RemainsVolume float null,
 speciesid bigint null,
 preparationId bigint null,
 memo text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp

);
create index idx_sample_log on sample_log(id)
;


create table parameter_list
(
 id serial primary key,
 name varchar(255) null,
 unit varchar(40) null,
 samplecategoryid bigint null references samplecategory(id),
 memo text null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;

create index param_category on parameter_list(samplecategoryid)
;





create table unit
(
  id serial primary key,
  name varchar (100)  not null,
  activity boolean default true not null,
  username varchar(30) null,
  addtime timestamp default current_timestamp
);




create table parameter_value
(id serial primary key,
 sampleid bigint not null references  sample(id),
 parameter_listid bigint not null references  parameter_list(id),
 parvalue float null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;
create index parvalue_sample on parameter_value(sampleid)
;
create index parvalue_param on parameter_value(parameter_listid)
;




create table prepmethod
(
  id serial primary key,
  name varchar(255)  null,
  samplecategoryid bigint not null references samplecategory(id),
  revision varchar(10) null,
  in_use boolean default true not null,
  analytical boolean default true not null,
  descriptive boolean default false not null,
  unitid bigint not null references  unit(id),
  measureunitid bigint null references  unit(id),
  description text null,
  url varchar(255) null,
  username varchar(30) null,
  addtime timestamp default current_timestamp
)
;
create index prep_category on prepmethod(samplecategoryid)
;


create table preppara
(
	id serial primary key,
	prepmethodid bigint not null references prepmethod(id),
	parameter_listid bigint not null references parameter_list(id),
	username varchar(30) null,
	addtime timestamp default current_timestamp
)
;
create index fk_prepmethodid on preppara(prepmethodid)
;
create index fk_parameter_listid on preppara(parameter_listid)
;

create table prepsubtype
(
	id serial primary key,
	prepmethodid bigint not null references prepmethod(id),
	subtypeid bigint not null references subtype(id),
	username varchar(30) null,
	addtime timestamp default current_timestamp
);
create index fk_prepsubtype_prep on prepsubtype(prepmethodid);
create index fk_prepsubtype_subtype on prepsubtype(subtypeid);

create table boxtype
(
  id serial primary key,
  name varchar(255)  not null,
  maxheight float null,
  minheight float null,
  description text null,
  in_use boolean default true not null,
  username varchar(30) null,
  addtime timestamp default current_timestamp
);


create table preparation
(
  id serial primary key,
  sampleid bigint not null references  sample(id),
  prepmetid bigint not null references  prepmethod(id),
  amount float null,
  unitid bigint  not null references  unit(id),
  dryweight boolean default true not null,
  responsible bigint null references operator(id),
  finished boolean default false not null,
  labjournal varchar(255) null,
  boxtypeid bigint null references boxtype,
  fillheight float null,
  sampPrepId bigint not null,
  memo text null,
  username varchar(30) null,
  addtime timestamp default current_timestamp
);
create index preparation_sample on preparation(sampleid);
create index prepmethodhod on preparation(prepmetid);
create index preparation_responsible on preparation(responsible);
create unique index sampPrepId on preparation(sampPrepId,sampleid);


create table preparation_log
(
  logid serial primary key,
  id bigint null,
  sampleid bigint null,
  prepmetid bigint null,
  amount float null,
  unitid bigint null,
  dryweight boolean default true not null,
  responsible bigint null,
  finished boolean default false not null,
  labjournal varchar(255) null,
  boxtypeid bigint null,
  fillheight float null,
  sampPrepId bigint not null,
  memo text null,
  username varchar(30) null,
  addtime timestamp default current_timestamp
);
create index idx_preparation_log on preparation_log(id)
;



create table analyticalmet
(
 id serial primary key,
 name varchar(255)  not null,
 in_use boolean default true not null,
 accredited boolean default false not null,
 description text null,
 url varchar(255) null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
)
;

create table analyticalmet_log
(
 logid serial primary key,
 id bigint null,
 name varchar(255)   null,
 in_use boolean default true not null,
 accredited boolean default false not null,
 description text null,
 url varchar(255) null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
);
create index idx_analyticalmet_log on analyticalmet_log(id)
;

create table analysis
(
  id serial primary key,
  preparationid bigint  not null references preparation(id),
  analyticalmetid bigint  not null references analyticalmet(id),
  responsible bigint null references operator(id),
  finished boolean default false not null,
  refused boolean default false not null,
  specter_ref varchar(255) null,
  labjournal varchar(255) null,
  sampleid bigint null,
  memo text null,
  sampAnaId bigint not null,
  username varchar(30) null,
  addtime timestamp default current_timestamp
);
create index analyse_prep on analysis(preparationid);
create index analyticalmethod on analysis(analyticalmetid);

create table analysis_log
(
  logid serial primary key,
  id bigint null,
  preparationid bigint null,
  analyticalmetid bigint null,
  responsible bigint null,
  finished boolean default false not null,
  refused boolean default false not null,
  specter_ref varchar(255) null,
  labjournal varchar(255) null,
  sampleid bigint null,
  memo text null,
  username varchar(30) null,
  addtime timestamp default current_timestamp
);
create index idx_analysis_log on analysis_log(id);

create table nuclide
(
 id serial primary key,
 name varchar(100)  null,
 halflife float null,
 in_use boolean default true not null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
);

create unique index nuclide_navn on nuclide (name)
;


create table nuclide_log
(
 logid serial primary key,
 id bigint null,
 name varchar(100)  null,
 halflife float null,
 in_use boolean default true not null,
 username varchar(30) null,
 addtime timestamp default current_timestamp
);
create index idx_nuclide_log on nuclide_log(id)
;


create table ananuc
(
  id serial primary key,
  nuclideid bigint not null references  nuclide(id),
  analyticalmetid bigint not null references  analyticalmet(id),
  username varchar(30) null,
  addtime timestamp default current_timestamp
);
create unique index idxnucana on ananuc(nuclideid,analyticalmetid);
create unique index idxananuc on ananuc(analyticalmetid,nuclideid);



create table result
(
  id serial primary key,
  sampleid bigint not null references  sample(id),
  analysisid bigint null references analysis(id),
  nuclideid bigint not null references  nuclide(id),
  activity  double precision null,
  unitid bigint not null references  unit(id),
  prec float null,
  relprec boolean default false not null,
  detlimit  float null,
  bld boolean default false not null,
  notchecked boolean default false not null,
  responsible varchar(30) null references operator(loginname),
  OK boolean default false not null,
  refused boolean default false not null,
  accredited boolean default false not null,
  accredited_by varchar(30) null references operator(loginname),
  labid bigint null references lab(id),
  username varchar(30) null,
  addtime timestamp default current_timestamp
);
create index result_sample on result(sampleid);
create index result_analyse on result(analysisid);
create index result_nuclide on result(nuclideid);
create index result_lab on result(labid);


create view resultdata as
select sample.id as sampleid ,sample.externalid, nuclideid,sample.projectid,
	sample.locationid,
	sample.samplingdate,sample.samplingtime,sample.samplingstopdate,
	sample.samplingstoptime,sample.addtime,
	sample.dryweight gdw, sample.wetweight gww,
	result.activity,prec,sample.samplecategoryid,sample.sampletypeid,
	relprec,detlimit,bld,ok,result.refused,result.accredited,
	accredited_by,nuclide.name as nuclide,unit.name as unit,
	analyticalmet.name as ana_met,sampletype.name as sampletype,
	subtype.name as subtype,species.locname,species.latname,
	location.name as location,sample.refdate,project.name as project
from result,analysis,nuclide,unit,analyticalmet,sample,project,
	sampletype,
	sample as locsample left join location on (locsample.locationid = location.id),
	sample as specsm left join species on (specsm.speciesid = species.id),
	sample as subsm left join subtype on (subsm.subtypeid = subtype.id)
where  sampletype.id = sample.sampletypeid and project.id = sample.projectid and
	sample.id =result.sampleid and analysisid=analysis.id and
	nuclide.id = nuclideid and result.unitid = unit.id 
	and analyticalmet.id = analyticalmetid
	and specsm.id = sample.id and subsm.id = sample.id 
	and locsample.id = sample.id;