
DROP TABLE image_file_association;
DROP TABLE image_group_association;
DROP TABLE image_source_object;
DROP TABLE geospatial_object;
DROP TABLE geospatial_type;
DROP TABLE unit_type;
DROP TABLE group_type;
DROP TABLE repository_object;
DROP TABLE image_source_file_object;
DROP TABLE image_source_type;
DROP TABLE security_classification_type;


DROP SEQUENCE image_file_association_id_pk_seq;
DROP SEQUENCE image_group_association_id_pk_seq;
DROP SEQUENCE image_source_object_id_pk_seq;
DROP SEQUENCE geospatial_object_id_pk_seq;
DROP SEQUENCE geospatial_type_id_pk_seq;
DROP SEQUENCE unit_type_id_pk_seq;
DROP SEQUENCE group_type_id_pk_seq;
DROP SEQUENCE repository_object_id_pk_seq;
DROP SEQUENCE image_source_file_object_id_pk_seq;
DROP SEQUENCE image_source_type_id_pk_seq;
DROP SEQUENCE security_classification_type_id_pk_seq;

CREATE SEQUENCE security_classification_type_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;

CREATE TABLE security_classification_type (
       id_pk INT4 NOT NULL DEFAULT nextval('security_classification_type_id_pk_seq'::regclass)
     , type VARCHAR(256) NOT NULL
     , description VARCHAR(256)
     , PRIMARY KEY (id_pk)
) WITH OIDS;

CREATE SEQUENCE image_source_type_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
  
CREATE TABLE image_source_type (
       id_pk INT4 NOT NULL DEFAULT nextval('image_source_type_id_pk_seq'::regclass)
     , type VARCHAR(256) NOT NULL
     , description VARCHAR(256)
     , PRIMARY KEY (id_pk)
) WITH OIDS;

CREATE SEQUENCE image_source_file_object_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
  
CREATE TABLE image_source_file_object (
       id_pk INT4 NOT NULL DEFAULT nextval('image_source_file_object_id_pk_seq'::regclass)
     , path VARCHAR(256) NOT NULL
     , filename VARCHAR(256)
     , repository_id_fk INTEGER
     , main_image_file BOOLEAN NOT NULL
     , PRIMARY KEY (id_pk)
) WITH OIDS;

CREATE SEQUENCE repository_object_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
  
CREATE TABLE repository_object (
       id_pk INT4 NOT NULL DEFAULT nextval('repository_object_id_pk_seq'::regclass)
     , path VARCHAR(256) NOT NULL
     , PRIMARY KEY (id_pk)
) WITH OIDS;

CREATE SEQUENCE group_type_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
  
CREATE TABLE group_type (
       id_pk INT4 NOT NULL DEFAULT nextval('group_type_id_pk_seq'::regclass)
     , type VARCHAR(256) NOT NULL
     , description VARCHAR(256)
     , PRIMARY KEY (id_pk)
) WITH OIDS;

CREATE SEQUENCE unit_type_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
  
CREATE TABLE unit_type (
       id_pk INT4 NOT NULL DEFAULT nextval('unit_type_id_pk_seq'::regclass)
     , type VARCHAR(256) NOT NULL
     , description VARCHAR(256)
     , PRIMARY KEY (id_pk)
) WITH OIDS;

CREATE SEQUENCE geospatial_type_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
  
CREATE TABLE geospatial_type (
       id_pk INT4 NOT NULL DEFAULT nextval('geospatial_type_id_pk_seq'::regclass)
     , type VARCHAR(256) NOT NULL
     , description VARCHAR(256)
     , PRIMARY KEY (id_pk)
) WITH OIDS;

CREATE SEQUENCE geospatial_object_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
  
CREATE TABLE geospatial_object (
       id_pk INT4 NOT NULL DEFAULT nextval('geospatial_object_id_pk_seq'::regclass)
     , type_id_fk INTEGER NOT NULL
     , security_classification_id_fk INTEGER NOT NULL
     , PRIMARY KEY (id_pk)
     , CONSTRAINT FK_geospatial_object_1 FOREIGN KEY (type_id_fk)
                  REFERENCES geospatial_type (id_pk)
     , CONSTRAINT FK_geospatial_object_2 FOREIGN KEY (security_classification_id_fk)
                  REFERENCES security_classification_type (id_pk)
) WITH OIDS;

CREATE SEQUENCE image_source_object_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
  
CREATE TABLE image_source_object (
       id_pk INT4 NOT NULL DEFAULT nextval('image_source_object_id_pk_seq'::regclass)
     , entry INTEGER DEFAULT 0 NOT NULL
     , acquisition_date DATE
     , type_id_fk INTEGER NOT NULL
     , width INTEGER NOT NULL
     , height INTEGER NOT NULL
     , number_of_bands INTEGER NOT NULL
     , overview_available BOOLEAN
     , histogram_available BOOLEAN
     , geospatial_object_id_fk INTEGER NOT NULL
     , gsd_x DOUBLE PRECISION NOT NULL
     , gsd_y DOUBLE PRECISION NOT NULL
     , gsd_unit_type_id_fk INTEGER NOT NULL
     , PRIMARY KEY (id_pk)
     , CONSTRAINT FK_image_source_object_1 FOREIGN KEY (type_id_fk)
                  REFERENCES image_source_type (id_pk)
     , CONSTRAINT FK_image_source_object_2 FOREIGN KEY (gsd_unit_type_id_fk)
                  REFERENCES unit_type (id_pk)
     , CONSTRAINT FK_image_source_object_3 FOREIGN KEY (geospatial_object_id_fk)
                  REFERENCES geospatial_object (id_pk)
) WITH OIDS;

CREATE SEQUENCE image_group_association_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
  
CREATE TABLE image_group_association (
       id_pk INT4 NOT NULL DEFAULT nextval('image_group_association_id_pk_seq'::regclass)
     , group_type_id_fk INTEGER NOT NULL
     , image_source_type_id_fk INTEGER NOT NULL
     , PRIMARY KEY (id_pk)
     , CONSTRAINT FK_group_members_object_1 FOREIGN KEY (group_type_id_fk)
                  REFERENCES group_type (id_pk)
     , CONSTRAINT FK_image_group_association_2 FOREIGN KEY (image_source_type_id_fk)
                  REFERENCES image_source_type (id_pk)
) WITH OIDS;

CREATE SEQUENCE image_file_association_id_pk_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
  
CREATE TABLE image_file_association (
       id_pk INT4 NOT NULL DEFAULT nextval('image_file_association_id_pk_seq'::regclass)
     , image_id_fk INTEGER NOT NULL
     , file_id_fk INTEGER NOT NULL
     , PRIMARY KEY (id_pk)
     , CONSTRAINT FK_image_source_file_association_1 FOREIGN KEY (image_id_fk)
                  REFERENCES image_source_object (id_pk)
     , CONSTRAINT FK_image_source_file_association_2 FOREIGN KEY (file_id_fk)
                  REFERENCES image_source_file_object (id_pk)
) WITH OIDS;

