-- ALTER TABLE fulltext DROP COLUMN urhebnama;

ALTER TABLE fulltext ADD COLUMN urhebnama character varying(2000);
ALTER TABLE fulltext ALTER COLUMN urhebnama SET STORAGE EXTENDED;


-- ALTER TABLE fulltext DROP COLUMN recipienta;

ALTER TABLE fulltext ADD COLUMN recipienta character varying(2000);
ALTER TABLE fulltext ALTER COLUMN recipienta SET STORAGE EXTENDED;


INSERT INTO fulltextdescription(columnname, columndesc, columnclass) 
SELECT 'URHEBNAMA', '-Einsender/Verfasser Address ID', 0
WHERE NOT EXISTS (select columnid From fulltextdescription where columnname = 'URHEBNAMA');

INSERT INTO fulltextdescription(columnname, columndesc, columnclass) 
SELECT 'RECIPIENTA', '-Empfänger Address ID', 0
WHERE NOT EXISTS (select columnid From fulltextdescription where columnname = 'RECIPIENTA');
		
INSERT INTO usersallelements(elementname, elementcat, elementsubcat, elementtype, isdynamic, ishierarchical, usesublevels, linkword, elementvaluedefault)
SELECT 'txtURHEBNAMA', 'DD', 'Schriftstück', 'Control', -1, 0, 0, '', ''
WHERE NOT EXISTS (select id From usersallelements where elementname = 'txtURHEBNAMA' and elementcat = 'DD');

INSERT INTO usersallelements(elementname, elementcat, elementsubcat, elementtype, isdynamic, ishierarchical, usesublevels, linkword, elementvaluedefault)
SELECT 'txtRECIPIENTA', 'DD', 'Schriftstück', 'Control', -1, 0, 0, '', ''
WHERE NOT EXISTS (select id From usersallelements where elementname = 'txtRECIPIENTA' and elementcat = 'DD');


-- Interne Benutzer hinzufügen
CREATE TABLE addressusersint
(
  "id" bigserial NOT NULL,
  givenname  character varying(255),
  additionalname character varying(255),
  familyname character varying(255),
  nickname character varying(255),
  prefix character varying(100),
  suffix character varying(100),
  gender character varying(1),
  bday date,
  url character varying(2000),
  title character varying(255),
  note text,
  CONSTRAINT pk_addressusersint PRIMARY KEY ("id")
);
ALTER TABLE addressusersint
  OWNER TO postgres;
GRANT ALL ON TABLE addressusersint TO postgres;
GRANT ALL ON TABLE addressusersint TO dorisuser WITH GRANT OPTION;

CREATE OR REPLACE FUNCTION getusersaddressusersint(integer)
  RETURNS SETOF addressusersint AS
$BODY$

select * from addressusersint
where id = $1

$BODY$
  LANGUAGE sql STABLE
  COST 100
  ROWS 1000;

ALTER FUNCTION getusersaddressusersint(integer)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION getusersaddressusersint(integer) TO postgres;
GRANT EXECUTE ON FUNCTION getusersaddressusersint(integer) TO public;
GRANT EXECUTE ON FUNCTION getusersaddressusersint(integer) TO dorisuser WITH GRANT OPTION;

GRANT ALL ON SEQUENCE addressusersint_id_seq TO dorisuser WITH GRANT OPTION;
GRANT ALL ON SEQUENCE addressusersint_id_seq TO postgres;



-- Externe Benutzer hinzufügen
CREATE TABLE addressusersext
(
  "id" bigserial NOT NULL,
  givenname  character varying(255),
  additionalname character varying(255),
  familyname character varying(255),
  nickname character varying(255),
  prefix character varying(100),
  suffix character varying(100),
  gender character varying(1),
  bday date,
  url character varying(2000),
  title character varying(255),
  note text,
  CONSTRAINT pk_addressusersext PRIMARY KEY ("id")
);
ALTER TABLE addressusersext
  OWNER TO postgres;
GRANT ALL ON TABLE addressusersext TO postgres;
GRANT ALL ON TABLE addressusersext TO dorisuser WITH GRANT OPTION;

CREATE OR REPLACE FUNCTION getaddressusersext(integer)
  RETURNS SETOF addressusersext AS
$BODY$

select * from addressusersext
where id = $1

$BODY$
  LANGUAGE sql STABLE
  COST 100
  ROWS 1000;

ALTER FUNCTION getaddressusersext(integer)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION getaddressusersext(integer) TO postgres;
GRANT EXECUTE ON FUNCTION getaddressusersext(integer) TO public;
GRANT EXECUTE ON FUNCTION getaddressusersext(integer) TO dorisuser WITH GRANT OPTION;

GRANT ALL ON SEQUENCE addressusersext_id_seq TO dorisuser WITH GRANT OPTION;
GRANT ALL ON SEQUENCE addressusersext_id_seq TO postgres;


--Organisationen hinzufüghen
CREATE TABLE addressorg
(
  "id" bigserial NOT NULL,
  "name" character varying(255),
  url character varying(255),
  CONSTRAINT pk_addressorg PRIMARY KEY ("id") 
);
ALTER TABLE addressorg
  OWNER TO postgres;
GRANT ALL ON TABLE addressorg TO postgres;
GRANT ALL ON TABLE addressorg TO dorisuser WITH GRANT OPTION;


CREATE OR REPLACE FUNCTION getaddressorgid(integer)
  RETURNS SETOF addressorg AS
$BODY$

select * from addressorg
where id = $1

$BODY$
  LANGUAGE sql STABLE
  COST 100
  ROWS 1000;

ALTER FUNCTION getaddressorgid(integer)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION getaddressorgid(integer) TO postgres;
GRANT EXECUTE ON FUNCTION getaddressorgid(integer) TO public;
GRANT EXECUTE ON FUNCTION getaddressorgid(integer) TO dorisuser WITH GRANT OPTION;

GRANT ALL ON SEQUENCE addressorg_id_seq TO dorisuser WITH GRANT OPTION;
GRANT ALL ON SEQUENCE addressorg_id_seq TO postgres;

-- Addressen hinzufügen
CREATE TABLE addressaddr
(
  "id" bigserial NOT NULL,
  orgid integer NULL,
  usersintid bigint NULL,
  usersextid bigint NULL,
  "type"  character varying(255),
  street character varying(255),
  locality character varying(255),
  region character varying(255),
  code character varying(100),
  country character varying(100),
  CONSTRAINT pk_address PRIMARY KEY ("id"),
  CONSTRAINT fk_address_1 FOREIGN KEY (orgid)
      REFERENCES addressorg("id") MATCH SIMPLE,
  CONSTRAINT fk_address_2 FOREIGN KEY (usersintid)
      REFERENCES addressusersint("id") MATCH SIMPLE,
  CONSTRAINT fk_address_3 FOREIGN KEY (usersextid)
      REFERENCES addressusersext("id") MATCH SIMPLE	  
);
ALTER TABLE addressaddr
  OWNER TO postgres;
GRANT ALL ON TABLE addressaddr TO postgres;
GRANT ALL ON TABLE addressaddr TO dorisuser WITH GRANT OPTION;

CREATE INDEX addressaddr_orgid
  ON addressaddr
  USING btree
  (orgid);

CREATE INDEX addressaddr_usersintid
  ON addressaddr
  USING btree
  (usersintid);
  
CREATE INDEX addressaddr_usersextid
  ON addressaddr
  USING btree
  (usersextid);  

CREATE OR REPLACE FUNCTION getaddressaddr(integer)
  RETURNS SETOF addressaddr AS
$BODY$

select * from addressaddr
where id = $1

$BODY$
  LANGUAGE sql STABLE
  COST 100
  ROWS 1000;

ALTER FUNCTION getaddressaddr(integer)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION getaddressaddr(integer) TO postgres;
GRANT EXECUTE ON FUNCTION getaddressaddr(integer) TO public;
GRANT EXECUTE ON FUNCTION getaddressaddr(integer) TO dorisuser WITH GRANT OPTION;

GRANT ALL ON SEQUENCE addressaddr_id_seq TO dorisuser WITH GRANT OPTION;
GRANT ALL ON SEQUENCE addressaddr_id_seq TO postgres;

--Email hinzufügen

CREATE TABLE addressemail
(
  "id" bigserial NOT NULL,
  orgid integer NULL,
  usersintid bigint NULL,
  usersextid bigint NULL,
  "type"  character varying(255),
  "value" character varying(255),
  CONSTRAINT pk_addressemail PRIMARY KEY ("id"),
  CONSTRAINT fk_addressemail_1 FOREIGN KEY (orgid)
      REFERENCES addressorg("id") MATCH SIMPLE,
  CONSTRAINT fk_addressemail_2 FOREIGN KEY (usersintid)
      REFERENCES addressusersint("id") MATCH SIMPLE,
  CONSTRAINT fk_aaddressemail_3 FOREIGN KEY (usersextid)
      REFERENCES addressusersext("id") MATCH SIMPLE	   
);
ALTER TABLE addressemail
  OWNER TO postgres;
GRANT ALL ON TABLE addressemail TO postgres;
GRANT ALL ON TABLE addressemail TO dorisuser WITH GRANT OPTION;

CREATE INDEX addressemail_orgid
  ON addressemail
  USING btree
  (orgid);

CREATE INDEX addressemail_usersintid
  ON addressemail
  USING btree
  (usersintid);
  
CREATE INDEX addressemail_usersextid
  ON addressemail
  USING btree
  (usersextid);  

CREATE OR REPLACE FUNCTION getaddressemail(integer)
  RETURNS SETOF addressemail AS
$BODY$

select * from addressemail
where id = $1

$BODY$
  LANGUAGE sql STABLE
  COST 100
  ROWS 1000;

ALTER FUNCTION getaddressemail(integer)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION getaddressemail(integer) TO postgres;
GRANT EXECUTE ON FUNCTION getaddressemail(integer) TO public;
GRANT EXECUTE ON FUNCTION getaddressemail(integer) TO dorisuser WITH GRANT OPTION;

GRANT ALL ON SEQUENCE addressemail_id_seq TO dorisuser WITH GRANT OPTION;
GRANT ALL ON SEQUENCE addressemail_id_seq TO postgres;

--Telefonummern hinzufügen
CREATE TABLE addresstel
(
  "id" bigserial NOT NULL,
  orgid integer NULL,
  usersintid bigint NULL,
  usersextid bigint NULL,
  "type"  character varying(255),
  "value" character varying(255),
  CONSTRAINT pk_addresstel PRIMARY KEY ("id"),
  CONSTRAINT fk_addresstel_1 FOREIGN KEY (orgid)
      REFERENCES addressorg("id") MATCH SIMPLE,
  CONSTRAINT fk_addresstel_2 FOREIGN KEY (usersintid)
      REFERENCES addressusersint("id") MATCH SIMPLE,
  CONSTRAINT fk_aaddresstel_3 FOREIGN KEY (usersextid)
      REFERENCES addressusersext("id") MATCH SIMPLE	   
);
ALTER TABLE addresstel
  OWNER TO postgres;
GRANT ALL ON TABLE addresstel TO postgres;
GRANT ALL ON TABLE addresstel TO dorisuser WITH GRANT OPTION;

CREATE INDEX addresstel_orgid
  ON addresstel
  USING btree
  (orgid);

CREATE INDEX addresstel_usersintid
  ON addresstel
  USING btree
  (usersintid);
  
CREATE INDEX addresstel_usersextid
  ON addresstel
  USING btree
  (usersextid);  

CREATE OR REPLACE FUNCTION getaddresstel(integer)
  RETURNS SETOF addresstel AS
$BODY$

select * from addresstel
where id = $1

$BODY$
  LANGUAGE sql STABLE
  COST 100
  ROWS 1000;

ALTER FUNCTION getaddresstel(integer)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION getaddresstel(integer) TO postgres;
GRANT EXECUTE ON FUNCTION getaddresstel(integer) TO public;
GRANT EXECUTE ON FUNCTION getaddresstel(integer) TO dorisuser WITH GRANT OPTION;

GRANT ALL ON SEQUENCE addresstel_id_seq TO dorisuser WITH GRANT OPTION;
GRANT ALL ON SEQUENCE addresstel_id_seq TO postgres;