CREATE SCHEMA IF NOT EXISTS aswi;

CREATE  TABLE aswi."catalog" ( 
	id                   uuid  NOT NULL  ,
	name                 varchar(100)  NOT NULL  ,
	certainty            integer    ,
	latitude             double precision    ,
	longitude            double precision    ,
	CONSTRAINT pk_katalog PRIMARY KEY ( id )
 );

CREATE  TABLE aswi.countries ( 
	name                 varchar(100)  NOT NULL  ,
	catalog_id           uuid  NOT NULL  ,
	CONSTRAINT pk_countries PRIMARY KEY ( name )
 );

CREATE  TABLE aswi.types ( 
	"type"               varchar(100)  NOT NULL  ,
	CONSTRAINT pk_types PRIMARY KEY ( "type" )
 );

CREATE  TABLE aswi.users ( 
	name                 varchar(100)    ,
	email                varchar(100)  NOT NULL  ,
	"password"           varchar(128)  NOT NULL  ,
	is_admin             bit DEFAULT 0   ,
	CONSTRAINT pk_users PRIMARY KEY ( email )
 );

CREATE  TABLE aswi.written_forms ( 
	form                 varchar(100)  NOT NULL  ,
	catalog_id           uuid  NOT NULL  ,
	CONSTRAINT pk_written_forms PRIMARY KEY ( form, catalog_id )
 );

CREATE  TABLE aswi.alternative_names ( 
	catalog_id           uuid  NOT NULL  ,
	name                 varchar(100)  NOT NULL  ,
	CONSTRAINT pk_alternative_names PRIMARY KEY ( catalog_id, name )
 );

CREATE  TABLE aswi.bibliography ( 
	"source"             varchar(150)  NOT NULL  ,
	catalog_id           uuid  NOT NULL  ,
	CONSTRAINT pk_bibliography PRIMARY KEY ( "source", catalog_id )
 );

CREATE  TABLE aswi.catalog_type ( 
	catalog_id           uuid  NOT NULL  ,
	"type"               uuid  NOT NULL  ,
	CONSTRAINT pk_catalog_type PRIMARY KEY ( catalog_id, "type" )
 );

ALTER TABLE aswi.alternative_names ADD CONSTRAINT fk_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id ) ON DELETE CASCADE;

ALTER TABLE aswi.bibliography ADD CONSTRAINT fk_bibliography_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );

ALTER TABLE aswi.catalog_type ADD CONSTRAINT fk_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );

ALTER TABLE aswi.catalog_type ADD CONSTRAINT fk_type_id FOREIGN KEY (  ) REFERENCES aswi.types(  );

ALTER TABLE aswi.countries ADD CONSTRAINT fk_countries_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );

ALTER TABLE aswi.written_forms ADD CONSTRAINT fk_written_forms_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );

COMMENT ON TABLE aswi."catalog" IS 'Catalog with geografical terms';

COMMENT ON COLUMN aswi."catalog".name IS 'Name of the geografical term';

COMMENT ON COLUMN aswi."catalog".latitude IS 'Geografical latitude';

COMMENT ON COLUMN aswi."catalog".longitude IS 'Geografical longitude';

COMMENT ON TABLE aswi.users IS 'Table to store different application users';

COMMENT ON COLUMN aswi.users.is_admin IS 'Logical value if the user has admin privileges';