
BEGIN;

SET escape_string_warning = off;

--////////////////////////////////////////////////////////////////////////////////////////

ALTER TABLE contactcenter_contact DROP CONSTRAINT contactcenter_contact_fk_address_default_fkey;
ALTER TABLE contactcenter_contact DROP CONSTRAINT contactcenter_contact_fk_email_default_fkey;
ALTER TABLE contactcenter_contact DROP CONSTRAINT contactcenter_contact_fk_phone_default_fkey;


--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_owner (
	pk_owner integer NOT NULL
);
ALTER TABLE ONLY contactcenter_owner ADD CONSTRAINT contactcenter_owner_pkey PRIMARY KEY (pk_owner);
INSERT INTO contactcenter_owner( pk_owner )
	SELECT DISTINCT id_owner AS pk_owner FROM phpgw_cc_contact
	UNION SELECT DISTINCT id_contact AS pk_owner FROM phpgw_cc_contact_rels
	UNION SELECT DISTINCT id_related AS pk_owner FROM phpgw_cc_contact_rels
	UNION SELECT DISTINCT owner AS pk_owner FROM phpgw_cc_groups
	ORDER BY pk_owner;

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_share (
    pk_share integer NOT NULL,
    fk_owner integer NOT NULL,
    fk_owner_share integer NOT NULL,
    CONSTRAINT contactcenter_share_check CHECK ((fk_owner <> fk_owner_share))
);
CREATE SEQUENCE contactcenter_share_pk_share_seq;
ALTER SEQUENCE contactcenter_share_pk_share_seq OWNED BY contactcenter_share.pk_share;
ALTER TABLE ONLY contactcenter_share ALTER COLUMN pk_share SET DEFAULT nextval('contactcenter_share_pk_share_seq'::regclass);
ALTER TABLE ONLY contactcenter_share ADD CONSTRAINT contactcenter_share_pkey PRIMARY KEY (pk_share);
ALTER TABLE ONLY contactcenter_share ADD CONSTRAINT contactcenter_share_fk_owner_fkey FOREIGN KEY (fk_owner) REFERENCES contactcenter_owner(pk_owner);
ALTER TABLE ONLY contactcenter_share ADD CONSTRAINT contactcenter_share_fk_owner_share_fkey FOREIGN KEY (fk_owner_share) REFERENCES contactcenter_owner(pk_owner);
INSERT INTO contactcenter_share( fk_owner, fk_owner_share )
	SELECT id_contact AS fk_owner, id_related AS fk_owner_share
	FROM phpgw_cc_contact_rels
	ORDER BY id_contact, id_related;

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_group (
    pk_group integer NOT NULL,
    fk_owner integer NOT NULL,
    title_group character varying(100)
);
CREATE SEQUENCE contactcenter_group_pk_group_seq;
ALTER SEQUENCE contactcenter_group_pk_group_seq OWNED BY contactcenter_group.pk_group;
ALTER TABLE ONLY contactcenter_group ALTER COLUMN pk_group SET DEFAULT nextval('contactcenter_group_pk_group_seq'::regclass);
ALTER TABLE ONLY contactcenter_group ADD CONSTRAINT contactcenter_group_pkey PRIMARY KEY (pk_group);
ALTER TABLE ONLY contactcenter_group ADD CONSTRAINT contactcenter_group_fk_owner_fkey FOREIGN KEY (fk_owner) REFERENCES contactcenter_owner(pk_owner);
INSERT INTO contactcenter_group( pk_group, fk_owner, title_group )
	SELECT id_group AS pk_group, owner AS fk_owner, title AS title_group
	FROM phpgw_cc_groups
	ORDER BY pk_group;
SELECT pg_catalog.setval('contactcenter_group_pk_group_seq', pk_group, true)
FROM contactcenter_group
ORDER BY pk_group DESC
LIMIT 1;

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_contact (
    pk_contact integer NOT NULL,
    fk_owner integer NOT NULL,
    name character varying(200),
    fk_email_default integer,
    fk_phone_default integer,
    fk_address_default integer
);
CREATE SEQUENCE contactcenter_contact_pk_contact_seq;
ALTER SEQUENCE contactcenter_contact_pk_contact_seq OWNED BY contactcenter_contact.pk_contact;
ALTER TABLE ONLY contactcenter_contact ALTER COLUMN pk_contact SET DEFAULT nextval('contactcenter_contact_pk_contact_seq'::regclass);
ALTER TABLE ONLY contactcenter_contact ADD CONSTRAINT contactcenter_contact_pkey PRIMARY KEY (pk_contact);
ALTER TABLE ONLY contactcenter_contact ADD CONSTRAINT contactcenter_contact_fk_owner_fkey FOREIGN KEY (fk_owner) REFERENCES contactcenter_owner(pk_owner);
INSERT INTO contactcenter_contact( pk_contact, fk_owner, name )
	SELECT id_contact AS pk_contact, id_owner AS fk_owner, ( GREATEST( TRIM( BOTH ' ' FROM given_names || ' ' || family_names) , names_ordered ) || CASE WHEN LENGTH(alias) > 0 THEN ' ('||alias||')' ELSE '' END ) AS name
	FROM phpgw_cc_contact
	ORDER BY id_contact;
SELECT pg_catalog.setval('contactcenter_contact_pk_contact_seq', pk_contact, true)
FROM contactcenter_contact
ORDER BY pk_contact DESC
LIMIT 1;

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_group_contact (
    pk_group_contact integer NOT NULL,
    fk_group integer NOT NULL,
    fk_contact integer NOT NULL
);
CREATE SEQUENCE contactcenter_group_contact_pk_group_contact_seq;
ALTER SEQUENCE contactcenter_group_contact_pk_group_contact_seq OWNED BY contactcenter_group_contact.pk_group_contact;
ALTER TABLE ONLY contactcenter_group_contact ALTER COLUMN pk_group_contact SET DEFAULT nextval('contactcenter_group_contact_pk_group_contact_seq'::regclass);
ALTER TABLE ONLY contactcenter_group_contact ADD CONSTRAINT contactcenter_group_contact_pkey PRIMARY KEY (pk_group_contact);
ALTER TABLE ONLY contactcenter_group_contact ADD CONSTRAINT contactcenter_group_contact_fk_contact_fkey FOREIGN KEY (fk_contact) REFERENCES contactcenter_contact(pk_contact);
ALTER TABLE ONLY contactcenter_group_contact ADD CONSTRAINT contactcenter_group_contact_fk_group_fkey FOREIGN KEY (fk_group) REFERENCES contactcenter_group(pk_group);
ALTER TABLE ONLY contactcenter_group_contact ADD CONSTRAINT contactcenter_group_contact_fk_group_fk_contact_key UNIQUE (fk_group,fk_contact);
INSERT INTO contactcenter_group_contact( fk_group, fk_contact )
	SELECT DISTINCT phpgw_cc_contact_grps.id_group AS fk_group, phpgw_cc_contact_conns.id_contact AS fk_contact
	FROM phpgw_cc_contact_grps
	JOIN phpgw_cc_contact_conns ON phpgw_cc_contact_conns.id_connection = phpgw_cc_contact_grps.id_connection
	JOIN contactcenter_group    ON contactcenter_group.pk_group         = phpgw_cc_contact_grps.id_group
	JOIN contactcenter_contact  ON contactcenter_contact.pk_contact     = phpgw_cc_contact_conns.id_contact
	ORDER BY phpgw_cc_contact_grps.id_group, phpgw_cc_contact_conns.id_contact;
SELECT pg_catalog.setval('contactcenter_group_contact_pk_group_contact_seq', pk_group_contact, true)
FROM contactcenter_group_contact
ORDER BY pk_group_contact DESC
LIMIT 1;

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_gender_type (
    pk_gender_type integer NOT NULL,
    description character varying(30)
);
CREATE SEQUENCE contactcenter_gender_type_pk_gender_type_seq;
ALTER SEQUENCE contactcenter_gender_type_pk_gender_type_seq OWNED BY contactcenter_gender_type.pk_gender_type;
ALTER TABLE ONLY contactcenter_gender_type ALTER COLUMN pk_gender_type SET DEFAULT nextval('contactcenter_gender_type_pk_gender_type_seq'::regclass);
INSERT INTO contactcenter_gender_type (pk_gender_type, description) VALUES (1, 'Masculino');
INSERT INTO contactcenter_gender_type (pk_gender_type, description) VALUES (2, 'Feminino');
SELECT pg_catalog.setval('contactcenter_gender_type_pk_gender_type_seq', 2, true);
ALTER TABLE ONLY contactcenter_gender_type ADD CONSTRAINT contactcenter_gender_type_pkey PRIMARY KEY (pk_gender_type);

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_info_additional (
    pk_info_additional integer NOT NULL,
    fk_contact integer NOT NULL,
    fk_gender_type integer,
    birthdate date,
    company character varying(100),
    department character varying(100),
    job_title character varying(100),
    notes text,
    photo text
);
CREATE SEQUENCE contactcenter_info_additional_pk_info_additional_seq;
ALTER SEQUENCE contactcenter_info_additional_pk_info_additional_seq OWNED BY contactcenter_info_additional.pk_info_additional;
ALTER TABLE ONLY contactcenter_info_additional ALTER COLUMN pk_info_additional SET DEFAULT nextval('contactcenter_info_additional_pk_info_additional_seq'::regclass);
ALTER TABLE ONLY contactcenter_info_additional ADD CONSTRAINT contactcenter_info_additional_pkey PRIMARY KEY (pk_info_additional);
ALTER TABLE ONLY contactcenter_info_additional ADD CONSTRAINT contactcenter_info_additional_fk_contact_fkey FOREIGN KEY (fk_contact) REFERENCES contactcenter_contact(pk_contact);
ALTER TABLE ONLY contactcenter_info_additional ADD CONSTRAINT contactcenter_info_additional_fk_gender_type_fkey FOREIGN KEY (fk_gender_type) REFERENCES contactcenter_gender_type(pk_gender_type);
ALTER TABLE ONLY contactcenter_info_additional ADD CONSTRAINT contactcenter_info_additional_fk_contact_key UNIQUE (fk_contact);
INSERT INTO contactcenter_info_additional( fk_contact, fk_gender_type, photo, birthdate, company, department, job_title, notes )
	SELECT
		id_contact AS fk_contact,
		CASE sex WHEN 'M' THEN 1 WHEN 'F' THEN 2 ELSE NULL END AS fk_gender_type,
		photo,
		birthdate,
		GREATEST( organization_name, corporate_name ) AS company,
		department,
		job_title,
		notes
	FROM phpgw_cc_contact
	WHERE NOT (
		birthdate IS NULL AND
		LENGTH( photo ) = 0 AND
		LENGTH( notes ) = 0 AND
		LENGTH( job_title ) = 0	AND
		LENGTH( department ) = 0 AND
		LENGTH( GREATEST( organization_name, corporate_name ) ) = 0 AND
		CASE sex WHEN 'M' THEN 1 WHEN 'F' THEN 2 ELSE NULL END IS NULL
	)
	ORDER BY id_contact;
INSERT INTO contactcenter_info_additional( fk_contact )
	SELECT phpgw_cc_contact_conns.id_contact AS fk_contact
	FROM phpgw_cc_connections
	JOIN phpgw_cc_contact_conns ON phpgw_cc_contact_conns.id_connection = phpgw_cc_connections.id_connection
	JOIN contactcenter_contact ON contactcenter_contact.pk_contact = phpgw_cc_contact_conns.id_contact
	LEFT JOIN contactcenter_info_additional ON contactcenter_info_additional.fk_contact = phpgw_cc_contact_conns.id_contact
	WHERE contactcenter_info_additional.pk_info_additional IS NULL
	GROUP BY phpgw_cc_contact_conns.id_contact
	ORDER BY phpgw_cc_contact_conns.id_contact;
SELECT pg_catalog.setval('contactcenter_info_additional_pk_info_additional_seq', pk_info_additional, true)
FROM contactcenter_info_additional
ORDER BY pk_info_additional DESC
LIMIT 1;

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_address_type (
    pk_address_type integer NOT NULL,
    description character varying(30) NOT NULL
);
CREATE SEQUENCE contactcenter_address_type_pk_address_type_seq;
ALTER SEQUENCE contactcenter_address_type_pk_address_type_seq OWNED BY contactcenter_address_type.pk_address_type;
ALTER TABLE ONLY contactcenter_address_type ALTER COLUMN pk_address_type SET DEFAULT nextval('contactcenter_address_type_pk_address_type_seq'::regclass);
INSERT INTO contactcenter_address_type (pk_address_type, description) VALUES (1, 'Comercial');
INSERT INTO contactcenter_address_type (pk_address_type, description) VALUES (2, 'Residencial');
SELECT pg_catalog.setval('contactcenter_address_type_pk_address_type_seq', 2, true);
ALTER TABLE ONLY contactcenter_address_type ADD CONSTRAINT contactcenter_address_type_pkey PRIMARY KEY (pk_address_type);

--////////////////////////////////////////////////////////////////////////////////////////

DELETE FROM phpgw_cc_contact_addrs WHERE id_contact = 1 AND id_address = 1;
CREATE TABLE contactcenter_address (
    pk_address integer NOT NULL,
    fk_contact integer NOT NULL,
    fk_address_type integer NOT NULL,
    address character varying(200),
    complement character varying(150),
    postal_code character varying(15),
    city character varying(150),
    state character varying(150),
    country character varying(150)
);
CREATE SEQUENCE contactcenter_address_pk_address_seq;
ALTER SEQUENCE contactcenter_address_pk_address_seq OWNED BY contactcenter_address.pk_address;
ALTER TABLE ONLY contactcenter_address ALTER COLUMN pk_address SET DEFAULT nextval('contactcenter_address_pk_address_seq'::regclass);
ALTER TABLE ONLY contactcenter_address ADD CONSTRAINT contactcenter_address_pkey PRIMARY KEY (pk_address);
ALTER TABLE ONLY contactcenter_address ADD CONSTRAINT contactcenter_address_fk_address_type_fkey FOREIGN KEY (fk_address_type) REFERENCES contactcenter_address_type(pk_address_type);
ALTER TABLE ONLY contactcenter_address ADD CONSTRAINT contactcenter_address_fk_contact_fkey FOREIGN KEY (fk_contact) REFERENCES contactcenter_contact(pk_contact);
ALTER TABLE ONLY contactcenter_contact ADD CONSTRAINT contactcenter_contact_fk_address_default_fkey FOREIGN KEY (fk_address_default) REFERENCES contactcenter_address(pk_address);
INSERT INTO contactcenter_address( pk_address, fk_contact, fk_address_type, address, complement, postal_code, city, state, country )
	SELECT
		phpgw_cc_addresses.id_address AS pk_address,
		phpgw_cc_contact_addrs.id_contact AS fk_contact,
		phpgw_cc_contact_addrs.id_typeof_contact_address AS fk_address_type,
		phpgw_cc_addresses.address1 ||
			CASE WHEN phpgw_cc_addresses.address2 IS NOT NULL AND LENGTH( phpgw_cc_addresses.address2 ) > 0 THEN
				', ' || phpgw_cc_addresses.address2 ELSE ''
			END AS address,
		CASE WHEN phpgw_cc_addresses.address_other IS NOT NULL AND LENGTH( phpgw_cc_addresses.address_other ) > 0 THEN (
			CASE WHEN phpgw_cc_addresses.po_box IS NOT NULL AND LENGTH( phpgw_cc_addresses.po_box ) > 0 THEN (
				CASE WHEN phpgw_cc_addresses.complement IS NOT NULL AND LENGTH( phpgw_cc_addresses.complement ) > 0 THEN
					phpgw_cc_addresses.complement || ' - ' || phpgw_cc_addresses.address_other  || ' - CxPos: ' || phpgw_cc_addresses.po_box ELSE
					phpgw_cc_addresses.address_other || ', CxPos: ' || phpgw_cc_addresses.po_box
				END ) ELSE CASE WHEN LENGTH( phpgw_cc_addresses.complement ) > 0 THEN
					phpgw_cc_addresses.complement || ' - ' || phpgw_cc_addresses.address_other ELSE phpgw_cc_addresses.address_other
				END
			END
		) ELSE (
			CASE WHEN phpgw_cc_addresses.po_box IS NOT NULL AND LENGTH( phpgw_cc_addresses.po_box ) > 0 THEN (
				CASE WHEN phpgw_cc_addresses.complement IS NOT NULL AND LENGTH( phpgw_cc_addresses.complement ) > 0 THEN
					phpgw_cc_addresses.complement || ' - CxPos: ' || phpgw_cc_addresses.po_box ELSE 'CxPos: ' || phpgw_cc_addresses.po_box
				END ) ELSE phpgw_cc_addresses.complement
			END
		) END AS complement,
		REGEXP_REPLACE( phpgw_cc_addresses.postal_code, '[^[:digit:]]', '', 'g' ) AS postal_code,
		phpgw_cc_city.city_name AS city,
		phpgw_cc_state.state_name AS state,
		phpgw_cc_addresses.id_country AS country
	FROM phpgw_cc_addresses
	JOIN phpgw_cc_contact_addrs ON phpgw_cc_contact_addrs.id_address = phpgw_cc_addresses.id_address
	JOIN phpgw_cc_contact ON phpgw_cc_contact.id_contact = phpgw_cc_contact_addrs.id_contact
	LEFT JOIN phpgw_cc_city ON phpgw_cc_city.id_city = phpgw_cc_addresses.id_city
	LEFT JOIN phpgw_cc_state ON phpgw_cc_state.id_state = phpgw_cc_addresses.id_state
	ORDER BY phpgw_cc_contact_addrs.id_contact;
UPDATE contactcenter_contact
SET fk_address_default = sub.fk_address_default
FROM (
	SELECT phpgw_cc_contact_addrs.id_contact AS pk_contact , max(phpgw_cc_addresses.id_address) AS fk_address_default
	FROM phpgw_cc_addresses
	JOIN phpgw_cc_contact_addrs ON phpgw_cc_contact_addrs.id_address = phpgw_cc_addresses.id_address
	WHERE address_is_default = 't'
	GROUP BY phpgw_cc_contact_addrs.id_contact
	ORDER BY phpgw_cc_contact_addrs.id_contact
) AS sub
WHERE contactcenter_contact.pk_contact = sub.pk_contact;
SELECT pg_catalog.setval('contactcenter_address_pk_address_seq', pk_address, true)
FROM contactcenter_address
ORDER BY pk_address DESC
LIMIT 1;

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_phone_type (
    pk_phone_type integer NOT NULL,
    description character varying(30)
);
CREATE SEQUENCE contactcenter_phone_type_pk_phone_type_seq;
ALTER SEQUENCE contactcenter_phone_type_pk_phone_type_seq OWNED BY contactcenter_phone_type.pk_phone_type;
ALTER TABLE ONLY contactcenter_phone_type ALTER COLUMN pk_phone_type SET DEFAULT nextval('contactcenter_phone_type_pk_phone_type_seq'::regclass);
INSERT INTO contactcenter_phone_type (pk_phone_type, description) VALUES (1, 'Celular');
INSERT INTO contactcenter_phone_type (pk_phone_type, description) VALUES (2, 'Comercial');
INSERT INTO contactcenter_phone_type (pk_phone_type, description) VALUES (3, 'Residencial');
INSERT INTO contactcenter_phone_type (pk_phone_type, description) VALUES (4, 'Outros');
SELECT pg_catalog.setval('contactcenter_phone_type_pk_phone_type_seq', 4, true);
ALTER TABLE ONLY contactcenter_phone_type ADD CONSTRAINT contactcenter_phone_type_pkey PRIMARY KEY (pk_phone_type);

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_phone (
    pk_phone integer NOT NULL,
    fk_contact integer NOT NULL,
    fk_phone_type integer NOT NULL,
    phone character varying(20)
);
CREATE SEQUENCE contactcenter_phone_pk_phone_seq;
ALTER SEQUENCE contactcenter_phone_pk_phone_seq OWNED BY contactcenter_phone.pk_phone;
ALTER TABLE ONLY contactcenter_phone ALTER COLUMN pk_phone SET DEFAULT nextval('contactcenter_phone_pk_phone_seq'::regclass);
ALTER TABLE ONLY contactcenter_phone ADD CONSTRAINT contactcenter_phone_pkey PRIMARY KEY (pk_phone);
ALTER TABLE ONLY contactcenter_phone ADD CONSTRAINT contactcenter_phone_fk_contact_fkey FOREIGN KEY (fk_contact) REFERENCES contactcenter_contact(pk_contact);
ALTER TABLE ONLY contactcenter_phone ADD CONSTRAINT contactcenter_phone_fk_phone_type_fkey FOREIGN KEY (fk_phone_type) REFERENCES contactcenter_phone_type(pk_phone_type);
ALTER TABLE ONLY contactcenter_phone ADD CONSTRAINT contactcenter_phone_check_phone CHECK (phone ~ E'^\\+?[0-9]+([XPW][0-9]+)?$');
ALTER TABLE ONLY contactcenter_contact ADD CONSTRAINT contactcenter_contact_fk_phone_default_fkey FOREIGN KEY (fk_phone_default) REFERENCES contactcenter_phone(pk_phone);
INSERT INTO contactcenter_phone( pk_phone, fk_contact, fk_phone_type, phone )
	SELECT pk_phone, fk_contact, fk_phone_type, '55' || CASE WHEN phone_mask = 'XXXXXXXX' THEN '41' ELSE '' END || phone_nf AS phone
	FROM (
		SELECT
			phpgw_cc_connections.id_connection AS pk_phone,
			phpgw_cc_contact_conns.id_contact AS fk_contact,
			CASE	WHEN lower(phpgw_cc_connections.connection_name) = 'celular' THEN 1
				WHEN lower(phpgw_cc_connections.connection_name) = 'comercial' THEN 2
				WHEN lower(phpgw_cc_connections.connection_name) = 'residencial' THEN 3
				ELSE 4
			END AS fk_phone_type,
			trim( REGEXP_REPLACE(
				REGEXP_REPLACE(
				lower(phpgw_cc_connections.connection_value),
				'[^[:digit:]()]','', 'g' ),
				'[[:digit:]]','X', 'g' )
			) AS phone_mask,
			trim( REGEXP_REPLACE( phpgw_cc_connections.connection_value, '[^[:digit:]]','', 'g' ) ) AS phone_nf
		FROM phpgw_cc_connections
		JOIN phpgw_cc_contact_conns ON phpgw_cc_contact_conns.id_connection = phpgw_cc_connections.id_connection
		JOIN contactcenter_contact ON contactcenter_contact.pk_contact = phpgw_cc_contact_conns.id_contact
		WHERE phpgw_cc_contact_conns.id_typeof_contact_connection = 2
	) AS list
	WHERE phone_mask = '(XX)XXXXXXXX' OR phone_mask = 'XXXXXXXXXX' OR phone_mask = 'XXXXXXXX' OR phone_mask = '(XX)XXXXXXXXX';
UPDATE contactcenter_contact
SET fk_phone_default = sub.fk_phone_default
FROM (
	SELECT phpgw_cc_contact_conns.id_contact AS pk_contact , max(phpgw_cc_connections.id_connection) AS fk_phone_default
	FROM phpgw_cc_connections
	JOIN phpgw_cc_contact_conns ON phpgw_cc_contact_conns.id_connection = phpgw_cc_connections.id_connection
	JOIN contactcenter_phone ON contactcenter_phone.pk_phone = phpgw_cc_connections.id_connection
	WHERE phpgw_cc_contact_conns.id_typeof_contact_connection = 2
		AND connection_is_default = 't'
	GROUP BY phpgw_cc_contact_conns.id_contact
	ORDER BY phpgw_cc_contact_conns.id_contact
) AS sub
WHERE contactcenter_contact.pk_contact = sub.pk_contact;
SELECT pg_catalog.setval('contactcenter_phone_pk_phone_seq', pk_phone, true)
FROM contactcenter_phone
ORDER BY pk_phone DESC
LIMIT 1;

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_email_type (
    pk_email_type integer NOT NULL,
    description character varying(30)
);
CREATE SEQUENCE contactcenter_email_type_pk_email_type_seq;
ALTER SEQUENCE contactcenter_email_type_pk_email_type_seq OWNED BY contactcenter_email_type.pk_email_type;
ALTER TABLE ONLY contactcenter_email_type ALTER COLUMN pk_email_type SET DEFAULT nextval('contactcenter_email_type_pk_email_type_seq'::regclass);
INSERT INTO contactcenter_email_type (pk_email_type, description) VALUES (1, 'Comercial');
INSERT INTO contactcenter_email_type (pk_email_type, description) VALUES (2, 'Particular');
SELECT pg_catalog.setval('contactcenter_email_type_pk_email_type_seq', 2, true);
ALTER TABLE ONLY contactcenter_email_type ADD CONSTRAINT contactcenter_email_type_pkey PRIMARY KEY (pk_email_type);

--////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE contactcenter_email (
    pk_email integer NOT NULL,
    fk_email_type integer NOT NULL,
    fk_contact integer NOT NULL,
    email character varying(150)
);
CREATE SEQUENCE contactcenter_email_pk_email_seq;
ALTER SEQUENCE contactcenter_email_pk_email_seq OWNED BY contactcenter_email.pk_email;
ALTER TABLE ONLY contactcenter_email ALTER COLUMN pk_email SET DEFAULT nextval('contactcenter_email_pk_email_seq'::regclass);
ALTER TABLE ONLY contactcenter_email ADD CONSTRAINT contactcenter_email_pkey PRIMARY KEY (pk_email);
ALTER TABLE ONLY contactcenter_email ADD CONSTRAINT contactcenter_email_fk_contact_fkey FOREIGN KEY (fk_contact) REFERENCES contactcenter_contact(pk_contact);
ALTER TABLE ONLY contactcenter_email ADD CONSTRAINT contactcenter_email_fk_email_type_fkey FOREIGN KEY (fk_email_type) REFERENCES contactcenter_email_type(pk_email_type);
ALTER TABLE ONLY contactcenter_email ADD CONSTRAINT contactcenter_email_email_check CHECK (email::text ~ '^[a-zA-Z0-9_.-]+@([a-zA-Z0-9](-*[a-zA-Z0-9])*)(.([a-zA-Z0-9](-*[a-zA-Z0-9])*))*$'::text);
ALTER TABLE ONLY contactcenter_contact ADD CONSTRAINT contactcenter_contact_fk_email_default_fkey FOREIGN KEY (fk_email_default) REFERENCES contactcenter_email(pk_email);
INSERT INTO contactcenter_email( pk_email, fk_contact, fk_email_type, email )
	SELECT
		phpgw_cc_connections.id_connection AS pk_email,
		phpgw_cc_contact_conns.id_contact AS fk_contact,
		CASE	WHEN lower(phpgw_cc_connections.connection_name) = 'profissional' THEN 1
			WHEN lower(phpgw_cc_connections.connection_name) = 'trabalho' THEN 1
			WHEN lower(phpgw_cc_connections.connection_name) = 'comercial' THEN 1
			ELSE 2
		END AS fk_email_type,
		trim(phpgw_cc_connections.connection_value) AS email
	FROM phpgw_cc_connections
	JOIN phpgw_cc_contact_conns ON phpgw_cc_contact_conns.id_connection = phpgw_cc_connections.id_connection
	JOIN contactcenter_contact ON contactcenter_contact.pk_contact = phpgw_cc_contact_conns.id_contact
	WHERE phpgw_cc_contact_conns.id_typeof_contact_connection = 1
		AND trim(phpgw_cc_connections.connection_value) ~ E'^[a-zA-Z0-9\\.\\_\\-]+@([a-zA-Z0-9\\-]+\\.)+[a-zA-Z]{2,4}$'
	ORDER BY phpgw_cc_connections.id_connection;
UPDATE contactcenter_contact
SET fk_email_default = sub.fk_email_default
FROM (
	SELECT phpgw_cc_contact_conns.id_contact AS pk_contact , max(phpgw_cc_connections.id_connection) AS fk_email_default
	FROM phpgw_cc_connections
	JOIN phpgw_cc_contact_conns ON phpgw_cc_contact_conns.id_connection = phpgw_cc_connections.id_connection
	JOIN contactcenter_email ON contactcenter_email.pk_email = phpgw_cc_connections.id_connection
	WHERE phpgw_cc_contact_conns.id_typeof_contact_connection = 1
		AND connection_is_default = 't'
	GROUP BY phpgw_cc_contact_conns.id_contact
	ORDER BY phpgw_cc_contact_conns.id_contact
) AS sub
WHERE contactcenter_contact.pk_contact = sub.pk_contact;
SELECT pg_catalog.setval('contactcenter_email_pk_email_seq', pk_email, true)
FROM contactcenter_email
ORDER BY pk_email DESC
LIMIT 1;

--////////////////////////////////////////////////////////////////////////////////////////

DO $$
DECLARE row record;
BEGIN
	ALTER TABLE phpgw_cc_connections ADD COLUMN export boolean DEFAULT false;
	LOOP
		-----------------------------------------------------------------------------------
		FOR row IN
			SELECT contactcenter_info_additional.pk_info_additional, min(phpgw_cc_connections.id_connection) AS id_connection
			FROM contactcenter_info_additional
			JOIN contactcenter_contact         ON contactcenter_contact.pk_contact = contactcenter_info_additional.fk_contact
			JOIN phpgw_cc_contact_conns        ON phpgw_cc_contact_conns.id_contact = contactcenter_contact.pk_contact
			JOIN phpgw_cc_connections          ON phpgw_cc_connections.id_connection = phpgw_cc_contact_conns.id_connection AND export = false
			GROUP BY contactcenter_info_additional.pk_info_additional
			LIMIT 100000
		LOOP
			UPDATE contactcenter_info_additional
			SET notes = (
				SELECT ( COALESCE( notes, '--' ) || E'\n' ||
					CASE phpgw_cc_contact_conns.id_typeof_contact_connection WHEN 1 THEN 'email' WHEN 2 THEN 'fone' END ||
					CASE phpgw_cc_connections.connection_is_default WHEN 't' THEN '*' ELSE '' END || ': (' ||
					phpgw_cc_connections.connection_name || ') ' || phpgw_cc_connections.connection_value )
				FROM contactcenter_info_additional
				JOIN contactcenter_contact         ON contactcenter_contact.pk_contact = contactcenter_info_additional.fk_contact
				JOIN phpgw_cc_contact_conns        ON phpgw_cc_contact_conns.id_contact = contactcenter_contact.pk_contact
				JOIN phpgw_cc_connections          ON phpgw_cc_connections.id_connection = phpgw_cc_contact_conns.id_connection
				WHERE phpgw_cc_contact_conns.id_connection = row.id_connection
			)
			WHERE contactcenter_info_additional.pk_info_additional = row.pk_info_additional;
			-----------------------------------------------------------------------------------
			UPDATE phpgw_cc_connections
			SET export = true
			WHERE phpgw_cc_connections.id_connection = row.id_connection;

		END LOOP;
		IF row.pk_info_additional IS NULL THEN
			EXIT;
		END IF;
		-----------------------------------------------------------------------------------
	END LOOP;
	ALTER TABLE phpgw_cc_connections DROP COLUMN export;
END$$;
DO $$
	DECLARE
		r RECORD;
		own TEXT := (
			SELECT u.usename
			FROM information_schema.tables t
			JOIN pg_catalog.pg_class c ON (t.table_name = c.relname)
			JOIN pg_catalog.pg_user u ON (c.relowner = u.usesysid)
			WHERE t.table_schema = 'public' AND t.table_name = 'phpgw_config'
		);
	BEGIN
		FOR r IN
			SELECT tablename AS tname FROM pg_catalog.pg_tables WHERE schemaname = 'public' AND tablename LIKE 'contactcenter_%'
		LOOP
			EXECUTE 'ALTER TABLE ' || quote_ident(r.tname) || ' OWNER TO ' || quote_ident(own);
			EXECUTE 'GRANT ALL ON TABLE ' || quote_ident(r.tname) || ' TO ' || quote_ident(own);
		END LOOP;
	END
$$;



COMMIT;

