/*
Created: 03/11/2009
Modified: 26/05/2014
Project: Sistema Informatico Gestione Domande
Model: Generico
Company: Sviluppo Toscana S.p.A.
Author: E. Belloni, A. Montagnani, S. Coppedé, M. Ascione
Version: 1.0
Database: PostgreSQL 8.4
*/


BEGIN;

CREATE OR REPLACE FUNCTION make_plpgsql()
RETURNS VOID
LANGUAGE SQL
AS $$
CREATE LANGUAGE plpgsql;
$$;
 
SELECT
    CASE
    WHEN EXISTS(
        SELECT 1
        FROM pg_catalog.pg_language
        WHERE lanname='plpgsql'
    )
    THEN NULL
    ELSE make_plpgsql() END;
 
DROP FUNCTION make_plpgsql();


-- Create functions section -------------------------------------------------

CREATE OR REPLACE FUNCTION "cod_fisc"()
RETURNS TRIGGER
 LANGUAGE 'plpgsql'
 VOLATILE
AS
$$
BEGIN
 IF NEW.cod_fisc IS NULL OR controlla_cf(NEW.cod_fisc) THEN RETURN NEW; END IF;
 RAISE EXCEPTION 'Formato Codice Fiscale errato.';
END;
$$
;

CREATE OR REPLACE FUNCTION "text2date"(text)
RETURNS date
 LANGUAGE sql
 VOLATILE
AS
$$
SELECT CASE
    WHEN $1 ~E'^(19|20)\\d\\d-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])$' THEN $1::date
--    WHEN $1 ~E'^(19|20)[0-9]{2}-[0-9]{2}-[0-9]{2}$' THEN $1::date
    WHEN $1 ~E'^(0[1-9]|[12][0-9]|3[01])[/-](0[1-9]|1[012])[/-][0-9]{2}$' THEN to_date($1,'DD-MM-YY')::date
    WHEN $1 ~E'^(0[1-9]|[12][0-9]|3[01])[/-](0[1-9]|1[012])[/-][0-9]{4}$' THEN to_date($1,'DD-MM-YYYY')::date
--    WHEN $1 ~E'^[0-9]{1,2}[/-][0-9]{1,2}[/-][0-9]{2}([0-9][0-9])?$' THEN $1::date
    ELSE NULL
    END AS valore;
$$
;

CREATE OR REPLACE FUNCTION "text2float"(text)
RETURNS float8
 LANGUAGE sql
 VOLATILE
AS
$$
SELECT CASE
    WHEN $1~E'^[ ]*[-]?[0-9]{1,3}([\\.][0-9]{3})[ ]*$'  THEN float8(replace($1,'.',''))
    WHEN $1~E'^[ ]*[-]?[0-9]{1,3}([,][0-9]{3})[ ]*$'    THEN float8(replace($1,',',''))
    WHEN $1~E'^[ ]*[-]?[0-9]+(\\.[0-9]*)?[ ]*$'         THEN float8($1)
    WHEN $1~E'^[ ]*[-]?[0-9]+(,[0-9]*)?[ ]*$'           THEN float8(replace($1,',','.'))
    WHEN $1~E'^[ ]*[-]?[0-9,]+(\\.[0-9]*)[ ]*$'         THEN float8(replace($1,',',''))
    WHEN $1~E'^[ ]*[-]?[0-9\\.]+(,[0-9]*)[ ]*$'         THEN float8(replace(replace($1,'.',''),',','.'))
    ELSE 0.0
    END AS valore;
$$
;

CREATE OR REPLACE FUNCTION "controlla_cf"(text)
RETURNS boolean
 LANGUAGE plpgsql
 IMMUTABLE
AS
$$
/*
 CREATE TEMPORARY TABLE r (i smallint, c char);
 INSERT INTO r VALUES(0,'A');
 INSERT INTO r VALUES(1,'B');
 INSERT INTO r VALUES(2,'C');
 INSERT INTO r VALUES(3,'D');
 INSERT INTO r VALUES(4,'E');
 INSERT INTO r VALUES(5,'F');
 INSERT INTO r VALUES(6,'G');
 INSERT INTO r VALUES(7,'H');
 INSERT INTO r VALUES(8,'I');
 INSERT INTO r VALUES(9,'J');
 INSERT INTO r VALUES(10,'K');
 INSERT INTO r VALUES(11,'L');
 INSERT INTO r VALUES(12,'M');
 INSERT INTO r VALUES(13,'N');
 INSERT INTO r VALUES(14,'O');
 INSERT INTO r VALUES(15,'P');
 INSERT INTO r VALUES(16,'Q');
 INSERT INTO r VALUES(17,'R');
 INSERT INTO r VALUES(18,'S');
 INSERT INTO r VALUES(19,'T');
 INSERT INTO r VALUES(20,'U');
 INSERT INTO r VALUES(21,'V');
 INSERT INTO r VALUES(22,'W');
 INSERT INTO r VALUES(23,'X');
 INSERT INTO r VALUES(24,'Y');
 INSERT INTO r VALUES(25,'Z');

 CREATE TEMPORARY TABLE tbl (m smallint, c char, n smallint);
 INSERT INTO tbl VALUES(1,'0',1);
 INSERT INTO tbl VALUES(1,'1',0);
 INSERT INTO tbl VALUES(1,'2',5);
 INSERT INTO tbl VALUES(1,'3',7);
 INSERT INTO tbl VALUES(1,'4',9);
 INSERT INTO tbl VALUES(1,'5',13);
 INSERT INTO tbl VALUES(1,'6',15);
 INSERT INTO tbl VALUES(1,'7',17);
 INSERT INTO tbl VALUES(1,'8',19);
 INSERT INTO tbl VALUES(1,'9',21);
 INSERT INTO tbl VALUES(1,'A',1);
 INSERT INTO tbl VALUES(1,'B',0);
 INSERT INTO tbl VALUES(1,'C',5);
 INSERT INTO tbl VALUES(1,'D',7);
 INSERT INTO tbl VALUES(1,'E',9);
 INSERT INTO tbl VALUES(1,'F',13);
 INSERT INTO tbl VALUES(1,'G',15);
 INSERT INTO tbl VALUES(1,'H',17);
 INSERT INTO tbl VALUES(1,'I',19);
 INSERT INTO tbl VALUES(1,'J',21);
 INSERT INTO tbl VALUES(1,'K',2);
 INSERT INTO tbl VALUES(1,'L',4);
 INSERT INTO tbl VALUES(1,'M',18);
 INSERT INTO tbl VALUES(1,'N',20);
 INSERT INTO tbl VALUES(1,'O',11);
 INSERT INTO tbl VALUES(1,'P',3);
 INSERT INTO tbl VALUES(1,'Q',6);
 INSERT INTO tbl VALUES(1,'R',8);
 INSERT INTO tbl VALUES(1,'S',12);
 INSERT INTO tbl VALUES(1,'T',14);
 INSERT INTO tbl VALUES(1,'U',16);
 INSERT INTO tbl VALUES(1,'V',10);
 INSERT INTO tbl VALUES(1,'W',22);
 INSERT INTO tbl VALUES(1,'X',25);
 INSERT INTO tbl VALUES(1,'Y',24);
 INSERT INTO tbl VALUES(1,'Z',23);
 INSERT INTO tbl VALUES(0,'0',0);
 INSERT INTO tbl VALUES(0,'1',1);
 INSERT INTO tbl VALUES(0,'2',2);
 INSERT INTO tbl VALUES(0,'3',3);
 INSERT INTO tbl VALUES(0,'4',4);
 INSERT INTO tbl VALUES(0,'5',5);
 INSERT INTO tbl VALUES(0,'6',6);
 INSERT INTO tbl VALUES(0,'7',7);
 INSERT INTO tbl VALUES(0,'8',8);
 INSERT INTO tbl VALUES(0,'9',9);
 INSERT INTO tbl VALUES(0,'A',0);
 INSERT INTO tbl VALUES(0,'B',1);
 INSERT INTO tbl VALUES(0,'C',2);
 INSERT INTO tbl VALUES(0,'D',3);
 INSERT INTO tbl VALUES(0,'E',4);
 INSERT INTO tbl VALUES(0,'F',5);
 INSERT INTO tbl VALUES(0,'G',6);
 INSERT INTO tbl VALUES(0,'H',7);
 INSERT INTO tbl VALUES(0,'I',8);
 INSERT INTO tbl VALUES(0,'J',9);
 INSERT INTO tbl VALUES(0,'K',10);
 INSERT INTO tbl VALUES(0,'L',11);
 INSERT INTO tbl VALUES(0,'M',12);
 INSERT INTO tbl VALUES(0,'N',13);
 INSERT INTO tbl VALUES(0,'O',14);
 INSERT INTO tbl VALUES(0,'P',15);
 INSERT INTO tbl VALUES(0,'Q',16);
 INSERT INTO tbl VALUES(0,'R',17);
 INSERT INTO tbl VALUES(0,'S',18);
 INSERT INTO tbl VALUES(0,'T',19);
 INSERT INTO tbl VALUES(0,'U',20);
 INSERT INTO tbl VALUES(0,'V',21);
 INSERT INTO tbl VALUES(0,'W',22);
 INSERT INTO tbl VALUES(0,'X',23);
 INSERT INTO tbl VALUES(0,'Y',24);
 INSERT INTO tbl VALUES(0,'Z',25);
 */
 DECLARE
 x character(16) := $1::character(16);
 control character := '';
 t smallint := 0;
 ret boolean := FALSE;
 tbl text[] := ARRAY['ABCDEFGHIJABCDEFGHIJKLMNOPQRSTUVWXYZ','BAFHJNPRTVBAFHJNPRTVCESULDGIMOQKWZYX'];
BEGIN
 SELECT (regexp_matches(upper($1), '^([0-9A-Z]{15}[A-Z])$'))[1] INTO x;
 IF NOT FOUND THEN RETURN FALSE; END IF;
 SELECT substr(x, 16, 1) INTO control;
 FOR i IN 1..15 LOOP
  t := t + ascii(translate(substr(x, i, 1), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', tbl[i % 2 + 1])) - ascii('A');
 END LOOP;
 ret := chr(ascii('A') + t % 26) = control;
 RETURN ret;
END;
$$
;

CREATE OR REPLACE FUNCTION "controlla_piva"(text)
RETURNS boolean
 LANGUAGE plpgsql
 IMMUTABLE
AS
$$
DECLARE
 x character(11) := $1::character(11);
 control character := '';
 l smallint := 0;
 m smallint := 0;
 n smallint := 0;
 t smallint := 0;
 c smallint := 0;
 ret boolean := FALSE;
BEGIN
 SELECT (regexp_matches(upper($1), '^([0-9]{11})$'))[1] INTO x;
 IF NOT FOUND THEN RETURN FALSE; END IF;
 SELECT substr(x, 11, 1) INTO control;
 FOR i IN 0..4 LOOP
  l := l + substr(x, i * 2 + 1, 1)::smallint;
  m := m + substr(x, i * 2 + 2, 1)::smallint * 2;
  n := n + CASE substr(x, i * 2 + 2, 1)::smallint >= 5 WHEN TRUE THEN 1 ELSE 0 END;
 END LOOP;
 t := ( l + m + n ) % 10;
 c := ( 10 - t ) % 10;
 RETURN c = control::smallint;
END;
$$
;

CREATE OR REPLACE FUNCTION "controlla_pivacf"(text)
RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT controlla_piva($1) OR controlla_cf($1);
$$
;

CREATE OR REPLACE FUNCTION "cup"(integer, timestamp, integer, integer)
RETURNS text
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT
    $1::text
 || '.'
 || to_char($2, 'DDMMYYYY')
 || '.'
 || lpad($3::text, 3, '0')
 || lpad($4::text, 6, '0')
$$
;

CREATE OR REPLACE FUNCTION "sanitize_soggetto"()
RETURNS TRIGGER
 LANGUAGE 'plpgsql'
 VOLATILE
AS
$$
BEGIN
 NEW.rag_sociale = trim(NEW.rag_sociale);  
 NEW.sl_indirizzo = trim(NEW.sl_indirizzo);  
 NEW.sl_cap = trim(NEW.sl_cap);  
 IF length(NEW.sl_cap) <> 5 THEN
  RAISE EXCEPTION 'Formato CAP errato.';
 END IF;
 NEW.sl_comune = initcap(trim(NEW.sl_comune)); 
 NEW.sl_provincia = upper(trim(NEW.sl_provincia));
 IF length(NEW.sl_provincia) <> 2 THEN
  RAISE EXCEPTION 'Formato provincia errato.';
 END IF;
 NEW.ul_indirizzo = trim(NEW.ul_indirizzo);  
 NEW.ul_cap = trim(NEW.ul_cap);  
 IF length(NEW.ul_cap) <> 5 THEN
  RAISE EXCEPTION 'Formato CAP errato.';
 END IF;
 NEW.ul_comune = initcap(trim(NEW.ul_comune)); 
 NEW.ul_provincia = upper(trim(NEW.ul_provincia));
 IF length(NEW.ul_provincia) <> 2 THEN
  RAISE EXCEPTION 'Formato provincia errato.';
 END IF;
 NEW.cf_soggetto = upper(trim(NEW.cf_soggetto)); 
 NEW.dimensione = initcap(trim(NEW.dimensione));  
 NEW.nome_rappr = initcap(trim(NEW.nome_rappr)); 
 NEW.cognome_rappr = initcap(trim(NEW.cognome_rappr)); 
 NEW.cf_rappr = upper(trim(NEW.cf_rappr)); 
 NEW.tel_rappr = trim(NEW.tel_rappr);  
 NEW.fax_rappr = trim(NEW.fax_rappr);  
 NEW.email_rappr = lower(trim(NEW.email_rappr)); 
 NEW.email_pec_rappr = lower(trim(NEW.email_pec_rappr)); 
 NEW.nato_a = initcap(trim(NEW.nato_a));
 NEW.soggetto_tipo_doc = trim(NEW.soggetto_tipo_doc); 
 NEW.num_doc = trim(NEW.num_doc); 
 NEW.ateco_2002 = upper(trim(NEW.ateco_2002)); 
 NEW.ateco_2007 = upper(trim(NEW.ateco_2007)); 
 NEW.dipartimento = trim(NEW.dipartimento); 
 RETURN NEW;
END;
$$
;

CREATE OR REPLACE FUNCTION "sanitize_utente"()
RETURNS TRIGGER
 LANGUAGE 'plpgsql'
 VOLATILE
AS
$$
BEGIN
 NEW.nome = initcap(trim(NEW.nome)); 
 NEW.cognome = initcap(trim(NEW.cognome)); 
 NEW.cod_fisc = upper(trim(NEW.cod_fisc));
 NEW.telefono = trim(NEW.telefono);  
 NEW.email = lower(trim(NEW.email)); 
 RETURN NEW;
END;
$$
;

CREATE OR REPLACE FUNCTION "sanitize_progetto"()
RETURNS TRIGGER
 LANGUAGE plpgsql
 VOLATILE
AS
$$
BEGIN
 NEW.titolo_prog = trim(NEW.titolo_prog); 
 NEW.titolo_prog_esteso = trim(NEW.titolo_prog_esteso);
 if(NEW.titolo_prog_esteso == NEW.titolo_prog) THEN NEW.titolo_prog_esteso = NULL;
 NEW.cupst = trim(NEW.cupst);  
 NEW.cupartea = trim(NEW.cupartea);
 RETURN NEW;
END;
$$
;

CREATE OR REPLACE FUNCTION "sanitize_versione"()
RETURNS TRIGGER
 LANGUAGE plpgsql
 VOLATILE
AS
$$
BEGIN
 NEW.dua = upper(trim(NEW.dua)); 
 RETURN NEW;
END;
$$
;

CREATE OR REPLACE FUNCTION "cf_rappr"()
RETURNS TRIGGER
 LANGUAGE 'plpgsql'
 VOLATILE
AS
$$
BEGIN
 IF NEW.cf_rappr IS NULL OR controlla_cf(NEW.cf_rappr) THEN RETURN NEW; END IF;
 RAISE EXCEPTION 'Formato Codice Fiscale errato.';
END;
$$
;

CREATE OR REPLACE FUNCTION "cf_soggetto"()
RETURNS TRIGGER
 LANGUAGE 'plpgsql'
 VOLATILE
AS
$$
BEGIN
 IF controlla_pivacf(NEW.cf_soggetto) THEN RETURN NEW; END IF;
 RAISE EXCEPTION 'Formato Codice Fiscale errato.';
END;
$$
;

CREATE OR REPLACE FUNCTION "capofila"()
RETURNS TRIGGER
 LANGUAGE plpgsql
 VOLATILE
AS
$$
BEGIN
 IF (NEW.capofila) THEN
  UPDATE soggetti
  SET capofila = false
  WHERE id_soggetto <> NEW.id_soggetto
    AND id_soggetto IN (
    SELECT a.id_soggetto
    FROM utenti a
    JOIN utenti b USING(id_prog)
    WHERE id_prog <> 0 AND b.id_soggetto = NEW.id_soggetto
  );
 END IF;
 RETURN NEW;
END
$$
;

CREATE OR REPLACE FUNCTION "data_creazione"()
RETURNS TRIGGER
 LANGUAGE plpgsql
 STABLE
AS
$$
BEGIN
 IF (NEW.data_creazione = OLD.data_creazione) THEN RETURN NEW; END IF;
 RETURN NULL;
END
$$
;

CREATE OR REPLACE FUNCTION "ultima_modifica"()
RETURNS TRIGGER
 LANGUAGE plpgsql
 VOLATILE
AS
$$
BEGIN
 IF NEW.soggetto_confermato AND OLD.soggetto_confermato <> NEW.soggetto_confermato THEN
  NEW.ultima_modifica = now()::timestamp(0);
 END IF;
 RETURN NEW;
END
$$
;

-- Create user data types section -------------------------------------------------

CREATE TYPE "atto" AS
 ( 
numero integer,
data date
 )
;

-- Create tables section -------------------------------------------------

-- Table attivita

CREATE TABLE "attivita"(
 "attivita" Text NOT NULL
        CONSTRAINT "attivita_length" CHECK (length(attivita) <= 200)
)
WITH (OIDS=FALSE)
;

COMMENT ON TABLE "attivita" IS 'Tabella che descrive le attivita dei progetti'
;

INSERT INTO attivita VALUES ('Generica');

-- Add keys for table attivita

ALTER TABLE "attivita" ADD CONSTRAINT "pk_attivita" PRIMARY KEY ("attivita")
;

-- Table linee_progetto

CREATE TABLE "linee_progetto"(
 "linea_progetto" Text NOT NULL
        CONSTRAINT "linea_progetto_length" CHECK (length(linea_progetto) <= 200)
)
WITH (OIDS=FALSE)
;

INSERT INTO linee_progetto VALUES('Nessuna');

-- Add keys for table linee_progetto

ALTER TABLE "linee_progetto" ADD CONSTRAINT "pk_linee_progetto" PRIMARY KEY ("linea_progetto")
;

-- Table livelli

CREATE TABLE "livelli"(
 "livello" Text NOT NULL
        CONSTRAINT "livello_length" CHECK (length(livello) <= 200)
)
WITH (OIDS=FALSE)
;

COMMENT ON TABLE "livelli" IS 'Tabella per la definizione dei livelli di accesso degli utenti'
;

INSERT INTO livelli VALUES ('Sconosciuto');
INSERT INTO livelli SELECT * FROM unnest(ARRAY[
   'Amministratore'
 , 'Attivatore'
 , 'Controllore'
 , 'Istruttore'
 , 'Manager'
 , 'Presentatore'
 , 'Supervisore di Provincia'
]) x WHERE x NOT IN (SELECT * FROM livelli);

-- Add keys for table livelli

ALTER TABLE "livelli" ADD CONSTRAINT "pk_livelli" PRIMARY KEY ("livello")
;

-- Table motivi_versione

CREATE TABLE "motivi_versione"(
 "motivo_versione" Text NOT NULL
        CONSTRAINT "motivo_versione_length" CHECK (length(motivo_versione) <= 200)
)
WITH (OIDS=FALSE)
;

INSERT INTO motivi_versione VALUES ('Versione');
INSERT INTO motivi_versione SELECT * FROM unnest(ARRAY[
   'Domanda'
 , 'Integrazione'
 , 'Variante'
 , 'Variante da Decreto'
]) x WHERE x NOT IN (SELECT * FROM motivi_versione);

-- Add keys for table motivi_versione

ALTER TABLE "motivi_versione" ADD CONSTRAINT "pk_motivi_versione" PRIMARY KEY ("motivo_versione")
;

-- Table tipi_doc

CREATE TABLE "tipi_doc"(
 "tipo_doc" Text NOT NULL
        CONSTRAINT "tipo_doc_length" CHECK (length(tipo_doc) <= 200)
)
WITH (OIDS=FALSE)
;

COMMENT ON TABLE "tipi_doc" IS 'Tabella che descrive la tipologia dei documenti allegati'
;

INSERT INTO tipi_doc VALUES ('Sconosciuto');
INSERT INTO tipi_doc SELECT * FROM unnest(ARRAY[
   'Altro'
 , 'Atto di Nomina'
 , 'Atto di Procura'
 , E'Carta d''identità'
 , 'Cert. Poteri firma'
 , 'Codice fiscale'
 , 'Comunicazione di rinuncia'
 , 'Contratto'
 , 'Documento generato'
 , 'Domanda firmata'
 , 'Istanza di riesame'
 , 'Lettera di rinuncia'
 , 'Procedimento di revoca'
 , 'Proroga'
 , 'Processo Istruttorio'
 , 'Richiesta di annullamento'
 , 'Statuto'
]) x WHERE x NOT IN (SELECT * FROM tipi_doc);

-- Add keys for table tipi_doc

ALTER TABLE "tipi_doc" ADD CONSTRAINT "pk_tipi_doc" PRIMARY KEY ("tipo_doc")
;

-- Table tipi_soggetto

CREATE TABLE "tipi_soggetto"(
 "tipo_soggetto" Text NOT NULL
        CONSTRAINT "tipo_soggetto_length" CHECK (length(tipo_soggetto) <= 200)
)
WITH (OIDS=FALSE)
;

INSERT INTO tipi_soggetto VALUES ('Soggetto Generico');
INSERT INTO tipi_soggetto SELECT * FROM unnest(ARRAY[
   'Comune'
 , 'Comunità Montana'
 , 'Consorzio'
 , 'Impresa'
 , 'Organismo di Ricerca'
 , 'Privato'
 , 'Provincia'
 , 'Unione di Comuni'
]) x WHERE x NOT IN (SELECT * FROM tipi_soggetto);

-- Add keys for table tipi_soggetto

ALTER TABLE "tipi_soggetto" ADD CONSTRAINT "pk_tipi_soggetto" PRIMARY KEY ("tipo_soggetto")
;

-- Table tipi_progetto

CREATE TABLE "tipi_progetto"(
 "tipo_progetto" Text NOT NULL
        CONSTRAINT "tipo_progetto_length" CHECK (length(tipo_progetto) <= 200)
)
WITH (OIDS=FALSE)
;

INSERT INTO tipi_progetto VALUES ('Generico');
INSERT INTO tipi_progetto SELECT * FROM unnest(ARRAY[
   'Aggregazione'
 , 'Singolo'
]) x WHERE x NOT IN (SELECT * FROM tipi_progetto);

-- Add keys for table tipi_progetto

ALTER TABLE "tipi_progetto" ADD CONSTRAINT "pk_tipi_progetto" PRIMARY KEY ("tipo_progetto")
;

-- Table tipi_verifica

CREATE TABLE "tipi_verifica"(
 "tipo_verifica" Text NOT NULL
        CONSTRAINT "tipo_verifica_length" CHECK (length(tipo_verifica) <= 200)
)
WITH (OIDS=FALSE)
;

INSERT INTO tipi_verifica SELECT * FROM unnest(ARRAY[
   'Ammissibilità'
 , 'Archiviazione'
 , 'Coordinazione Valutazione'
 , 'Esito'
 , 'Erogazione'
 , 'Piano Finanziario'
 , 'Premialità'
 , 'Rendicontazione'
 , 'Supervisione'
 , 'Tagli (nome provvisorio)'
 , 'Valutazione'
 , 'Variante da Decreto'
 , 'Variazione'
]) x WHERE x NOT IN (SELECT * FROM tipi_verifica);

-- Add keys for table tipi_verifica

ALTER TABLE "tipi_verifica" ADD CONSTRAINT "pk_tipi_verifica" PRIMARY KEY ("tipo_verifica")
;

-- Create functions section -------------------------------------------------

CREATE OR REPLACE FUNCTION "tipo_versione_long"(integer, text, timestamp, timestamp)
RETURNS text
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT $1 || ') ' || $2 || ' (dal ' || to_char($3, 'DD/MM/YYYY HH24:MI:SS') || COALESCE(' al ' || to_char($4, 'DD/MM/YYYY HH24:MI:SS'),'') || ')'
$$
;

-- Create tables section -------------------------------------------------

-- Table utenti

CREATE TABLE "utenti"(
 "id_utente" Serial NOT NULL,
 "id_soggetto" Integer DEFAULT 0 NOT NULL,
 "id_prog" Integer DEFAULT 0 NOT NULL,
 "username" Character varying(40) NOT NULL,
 "passwd" Character(32) NOT NULL,
 "livello" Text NOT NULL,
 "attivo" Boolean DEFAULT true NOT NULL,
 "nome" Text NOT NULL,
 "cognome" Text NOT NULL,
 "cod_fisc" Character(16),
 "telefono" Text,
 "email" Text NOT NULL,
 "data_compilato" Timestamp,
 "data_presentato" Timestamp,
  CONSTRAINT "nome_non_vuoto" CHECK (length(nome) > 0),
  CONSTRAINT "cognome_non_vuoto" CHECK (length(cognome) > 0),
  CONSTRAINT "ordine_chiusura" CHECK (data_presentato IS NULL OR ( data_compilato IS NOT NULL AND data_presentato IS NOT NULL )),
  CONSTRAINT "causalita_chiusura" CHECK (data_compilato <= data_presentato)
)
WITH (OIDS=FALSE)
;

COMMENT ON TABLE "utenti" IS 'Tabella degli utenti di sistema'
;
COMMENT ON COLUMN "utenti"."passwd" IS 'MD5 representation'
;

INSERT INTO utenti(id_utente, username, passwd, livello, attivo, nome, cognome, email) VALUES (0, 'webservice', '', 'Sconosciuto', TRUE, 'Web', 'Service', 'srvinfo@sviluppo.toscana.it');



-- Add keys for table utenti

ALTER TABLE "utenti" ADD CONSTRAINT "pk_utenti" PRIMARY KEY ("id_utente")
;

ALTER TABLE "utenti" ADD CONSTRAINT "username" UNIQUE ("username")
;

-- Create triggers for table utenti

CREATE TRIGGER "trigger_sanitize_utente"
  BEFORE INSERT OR UPDATE
  ON "utenti" FOR EACH ROW
 EXECUTE PROCEDURE "sanitize_utente"()
;

CREATE TRIGGER "trigger_cod_fisc"
  BEFORE INSERT OR UPDATE
  ON "utenti" FOR EACH ROW
 EXECUTE PROCEDURE "cod_fisc"()
;

-- Table soggetti

CREATE TABLE "soggetti"(
 "id_soggetto" Serial NOT NULL,
 "tipo_soggetto" Text DEFAULT 'Soggetto Generico' NOT NULL,
 "rag_sociale" Text NOT NULL,
 "sl_indirizzo" Text,
 "sl_cap" Character(5),
 "sl_comune" Text,
 "sl_provincia" Character(2),
 "cf_soggetto" Character varying(16) NOT NULL,
 "soggetto_confermato" Boolean DEFAULT true NOT NULL,
 "soggetto_autorizzato" Boolean DEFAULT false NOT NULL,
 "dimensione" Text,
 "capofila" Boolean DEFAULT TRUE NOT NULL,
 "nome_rappr" Text,
 "cognome_rappr" Text,
 "cf_rappr" Text,
 "tel_rappr" Text,
 "fax_rappr" Text,
 "email_rappr" Text,
 "email_pec_rappr" Text,
 "nato_a" Text,
 "data_nascita" Date,
 "soggetto_tipo_doc" Text,
 "num_doc" Text,
 "rilasciato_doc" Date,
 "scadenza_doc" Date,
 "procura_rappr" Boolean DEFAULT FALSE NOT NULL,
 "ateco_2002" Text,
 "ateco_2007" Text,
 "associate" Boolean DEFAULT FALSE NOT NULL,
 "collegate" Boolean DEFAULT FALSE NOT NULL,
 "conti_consolidati" Boolean DEFAULT FALSE NOT NULL,
 "dipartimento" Text,
 "piva_soggetto" Text,
 "inps" Character(17),
 "inail" Text,
 "cciaa" Text,
 "ritenuta_acconto" Boolean DEFAULT true NOT NULL,
 "data_creazione" Timestamp DEFAULT now()::timestamp(0) NOT NULL,
 "ultima_modifica" Timestamp DEFAULT now()::timestamp(0) NOT NULL,
 "partecipante" Boolean DEFAULT TRUE NOT NULL,
 "cancellato" Boolean DEFAULT false NOT NULL,
 "ul_indirizzo" Text,
 "ul_cap" Character(5),
 "ul_comune" Text,
 "ul_provincia" Character(2),
  CONSTRAINT "rag_sociale_significativo" CHECK (length(rag_sociale)>1),
  CONSTRAINT "coerenza_autorizzazione" CHECK (NOT soggetto_autorizzato OR (soggetto_autorizzato AND soggetto_confermato)),
  CONSTRAINT "cf_soggetto" CHECK (controlla_pivacf(cf_soggetto)),
  CONSTRAINT "cf_rappr" CHECK (   cf_rappr IS NULL
OR controlla_cf(cf_rappr))
)
WITH (OIDS=FALSE)
;

COMMENT ON TABLE "soggetti" IS 'Tabella dei soggetti in relazione agli utenti'
;
COMMENT ON COLUMN "soggetti"."partecipante" IS 'flag che determina se il soggetto partecipa al parternariato.'
;
COMMENT ON COLUMN "soggetti"."cancellato" IS 'flag che determina se il soggetto è stato rimosso per ragioni gestionali di piattaforma.

Questo flag NON indica che il soggetto è stato cancellato dal parternariato o dalle schede.'
;

INSERT INTO soggetti(id_soggetto, rag_sociale, cf_soggetto, capofila) VALUES (0, 'Nessun Soggetto', '00000000000', FALSE);
INSERT INTO soggetti(dipartimento, rag_sociale, tipo_soggetto, sl_indirizzo, sl_cap, sl_comune, sl_provincia, cf_soggetto, capofila, nome_rappr, cognome_rappr, cf_rappr, tel_rappr, fax_rappr, email_rappr) VALUES ('Servizi Informatici', 'Sviluppo Toscana S.p.A.', 'Impresa', 'Via Dorsale, 13', '54100', 'Massa', 'MS', '00566850459', FALSE, 'Orazio', 'Figura', '', '05857981', '0585798320', 'srvinfo@sviluppo.toscana.it');
INSERT INTO soggetti(rag_sociale, tipo_soggetto, sl_indirizzo, sl_cap, sl_comune, sl_provincia, cf_soggetto, capofila, nome_rappr, cognome_rappr, cf_rappr, tel_rappr, fax_rappr, email_rappr) VALUES ('Sviluppo Toscana S.p.A.', 'Impresa', 'Via Dorsale, 13', '54100', 'Massa', 'MS', '00566850459', FALSE, 'Orazio', 'Figura', '', '05857981', '0585798320', 'ofigura@sviluppo.toscana.it');

-- Add keys for table soggetti

ALTER TABLE "soggetti" ADD CONSTRAINT "pk_soggetti" PRIMARY KEY ("id_soggetto")
;

-- Create triggers for table soggetti

CREATE TRIGGER "trigger_sanitize_soggetto"
  BEFORE INSERT OR UPDATE
  ON "soggetti" FOR EACH ROW
 EXECUTE PROCEDURE "sanitize_soggetto"()
;

CREATE TRIGGER "trigger_capofila"
  BEFORE INSERT OR UPDATE
  ON "soggetti" FOR EACH ROW
 EXECUTE PROCEDURE "capofila"()
;

CREATE TRIGGER "trigger_data_creazione"
  BEFORE UPDATE
  ON "soggetti" FOR EACH ROW
 EXECUTE PROCEDURE "data_creazione"()
;

CREATE TRIGGER "trigger_ultima_modifica"
  BEFORE UPDATE
  ON "soggetti" FOR EACH ROW
 EXECUTE PROCEDURE "ultima_modifica"()
;

-- Table log

CREATE TABLE "log"(
 "id_log" Serial NOT NULL,
 "id_utente" Integer NOT NULL,
 "descr_ev" Character varying(2048) NOT NULL,
 "data_ev" Timestamp NOT NULL,
 "riferimento" Character varying(512) NOT NULL,
 "errore" Boolean DEFAULT false NOT NULL
)
WITH (OIDS=FALSE)
;

COMMENT ON TABLE "log" IS 'Tabella per il log delle operazioni degli utenti'
;
COMMENT ON COLUMN "log"."errore" IS 'specifica se l''evento è stato generato da un errore del sistema o un log di funzionamento.'
;

-- Add keys for table log

ALTER TABLE "log" ADD CONSTRAINT "pk_log" PRIMARY KEY ("id_log")
;

-- Table documenti

CREATE TABLE "documenti"(
 "id_doc" Serial NOT NULL,
 "id_prog" Integer NOT NULL,
 "versione" Integer NOT NULL,
 "id_utente" Integer NOT NULL,
 "nome_doc" Text NOT NULL,
 "tipo_doc" Text NOT NULL,
 "filesize" Integer NOT NULL,
 "filetype" Text NOT NULL,
 "data_upl" Timestamp NOT NULL,
 "nota" Text,
 "hash" Text NOT NULL,
 "deprecato" Boolean DEFAULT FALSE NOT NULL
)
WITH (OIDS=FALSE)
;

COMMENT ON TABLE "documenti" IS 'Tabella dei documenti allegati'
;
COMMENT ON COLUMN "documenti"."nota" IS 'Campo per eventuali note sui file ad opera degli utenti'
;

-- Add keys for table documenti

ALTER TABLE "documenti" ADD CONSTRAINT "pk_documenti" PRIMARY KEY ("id_doc")
;

-- Table progetti

CREATE TABLE "progetti"(
 "id_prog" Serial NOT NULL,
 "attivita" Text NOT NULL,
 "tipo_progetto" Text NOT NULL,
 "linea_progetto" Text NOT NULL,
 "id_prog_padre" Integer,
 "titolo_prog" Text NOT NULL
        CONSTRAINT "titolo_prog_length" CHECK (length(titolo_prog)<=200),
 "titolo_prog_esteso" Text,
 "attivo" Boolean DEFAULT FALSE NOT NULL,
 "cupst" Text,
 "cupartea" Text,
 "atto_ammissione" "atto",
 "atto_finanziamento" "atto",
  CONSTRAINT "titolo_prog_significativo" CHECK (length(titolo_prog) > 1)
)
WITH (OIDS=FALSE)
;
COMMENT ON COLUMN "progetti"."cupst" IS 'Eccezioni alla regola dei CUP.'
;

INSERT INTO progetti(id_prog, attivita, tipo_progetto, linea_progetto, titolo_prog) VALUES (0, 'Generica', 'Generico', 'Nessuna', 'Nessun Progetto');

-- Add keys for table progetti

ALTER TABLE "progetti" ADD CONSTRAINT "pk_progetti" PRIMARY KEY ("id_prog")
;

-- Create triggers for table progetti

CREATE TRIGGER "trigger_sanitize_progetto"
  BEFORE INSERT OR UPDATE
  ON "progetti" FOR EACH ROW
 EXECUTE PROCEDURE "sanitize_progetto"()
;

-- Table elementi

CREATE TABLE "elementi"(
 "id_prog" Integer NOT NULL,
 "id_soggetto" Integer NOT NULL,
 "id_utente" Integer NOT NULL,
 "id_quadro" Integer NOT NULL,
 "id_sezione" Integer NOT NULL,
 "id_modulo" Integer NOT NULL,
 "id_elemento" Integer NOT NULL,
 "posizione" Integer DEFAULT 0 NOT NULL,
 "istanza" Integer DEFAULT 0 NOT NULL,
 "versione" Integer NOT NULL,
 "referer" Integer NOT NULL,
 "tipo" Text NOT NULL,
 "valore" Text
)
WITH (OIDS=FALSE)
;

COMMENT ON TABLE "elementi" IS 'Tabella degli elementi dei vari moduli'
;

-- Create indexes for table elementi

CREATE INDEX "IX_elementi_address" ON "elementi" ("id_quadro","id_sezione","id_modulo","id_elemento","posizione","istanza")
;

CREATE INDEX "IX_elementi_full_address" ON "elementi" ("id_prog","versione","id_soggetto","id_utente","id_quadro","id_sezione","id_modulo","id_elemento","posizione","istanza")
;

-- Add keys for table elementi

ALTER TABLE "elementi" ADD CONSTRAINT "pk_elementi" PRIMARY KEY ("id_prog","id_soggetto","id_utente","id_quadro","id_sezione","id_modulo","id_elemento","posizione","istanza","versione")
;

-- Table verifiche

CREATE TABLE "verifiche"(
 "id_verifica" Serial NOT NULL,
 "id_prog" Integer NOT NULL
        CONSTRAINT "vero_progetto" CHECK (id_prog <> 0),
 "tipo_verifica" Text NOT NULL,
 "richiesta_versione" Boolean DEFAULT FALSE NOT NULL,
 "sospesa" Boolean DEFAULT false NOT NULL
)
WITH (OIDS=FALSE)
;

-- Create indexes for table verifiche

CREATE UNIQUE INDEX "unique_assignment_for_project" ON "verifiche" ("id_prog","tipo_verifica")
WHERE NOT sospesa AND tipo_verifica <> 'Supervisione' AND tipo_verifica <> 'Valutazione'
;

-- Add keys for table verifiche

ALTER TABLE "verifiche" ADD CONSTRAINT "pk_verifiche" PRIMARY KEY ("id_verifica")
;

ALTER TABLE "verifiche" ADD CONSTRAINT "unique_assignment_for_user" UNIQUE ("id_prog","tipo_verifica")
;
COMMENT ON CONSTRAINT "unique_assignment_for_user" ON "verifiche" IS 'Un istruttore non può essere assegnato a fare la stessa scheda più volte sullo stesso progetto.'
;

-- Table versioni

CREATE TABLE "versioni"(
 "id_prog" Integer NOT NULL,
 "versione" Integer NOT NULL,
 "motivo_versione" Text NOT NULL,
 "inizio_versione" Timestamp DEFAULT now()::timestamp(0) NOT NULL,
 "inoltro" Timestamp,
 "termine" Timestamp,
 "valida" Boolean DEFAULT false NOT NULL,
 "dua" Text,
  CONSTRAINT "ordine_versione" CHECK (inoltro IS NULL OR ( inizio_versione IS NOT NULL AND inoltro IS NOT NULL )),
  CONSTRAINT "causalita_versione" CHECK (inizio_versione <= inoltro),
  CONSTRAINT "valida_versione" CHECK (NOT valida OR (inoltro IS NOT NULL AND termine IS NOT NULL))
)
WITH (OIDS=FALSE)
;
COMMENT ON COLUMN "versioni"."inoltro" IS 'Il timestamp di quando il presentatore capofila ha chiuso la compilazione.

A tutti gli effetti inoltro = "chiusura" del progetto.'
;
COMMENT ON COLUMN "versioni"."termine" IS 'Il timestamp di quando il presentatore capofila ha presentato la domanda.

A tutti gli effetti termine = "presentazione" del progetto.'
;
COMMENT ON COLUMN "versioni"."valida" IS 'flag che attesta se la versione è valida per lo step successivo.

L''iter della domanda prosegue alla fase successiva.'
;

INSERT INTO versioni(id_prog, versione, motivo_versione, inizio_versione) VALUES (0, 0, 'Domanda', now()::timestamp(0));

-- Add keys for table versioni

ALTER TABLE "versioni" ADD CONSTRAINT "pk_versioni" PRIMARY KEY ("id_prog","versione")
;

-- Create triggers for table versioni

CREATE TRIGGER "trigger_sanitize_versione"
  BEFORE INSERT OR UPDATE
  ON "versioni" FOR EACH ROW
 EXECUTE PROCEDURE "sanitize_versione"()
;

-- Create functions section -------------------------------------------------

CREATE OR REPLACE FUNCTION "elemento"(integer, integer, integer, integer, integer, integer)
RETURNS SETOF elementi
 LANGUAGE sql
 STABLE
AS
$$
SELECT *
FROM elementi
WHERE
     CASE WHEN $1 != 0 THEN id_quadro=$1 ELSE TRUE END
 AND CASE WHEN $2 != 0 THEN id_sezione=$2 ELSE TRUE END
 AND CASE WHEN $3 != 0 THEN id_modulo=$3 ELSE TRUE END
 AND CASE WHEN $4 != 0 THEN id_elemento=$4 ELSE TRUE END
 AND CASE WHEN $5 != 0 THEN posizione=$5 ELSE TRUE END
 AND CASE WHEN $6 != 0 THEN istanza=$6 ELSE TRUE END
;

$$
;

CREATE OR REPLACE FUNCTION "elemento"(integer, integer, integer, integer, integer)
RETURNS SETOF elementi
 LANGUAGE sql
 STABLE
AS
$$
SELECT * FROM elemento($1, $2, $3, $4, $5, 0);
$$
;

CREATE OR REPLACE FUNCTION "elemento"(integer, integer, integer, integer)
RETURNS SETOF elementi
 LANGUAGE sql
 STABLE
AS
$$
SELECT * FROM elemento($1, $2, $3, $4, 0, 0);
$$
;

CREATE OR REPLACE FUNCTION "last_ver"(integer)
RETURNS integer
 LANGUAGE sql
 STABLE
AS
$$
SELECT
   max(versione)
FROM versioni
WHERE id_prog = $1
GROUP BY id_prog;
$$
;

CREATE OR REPLACE FUNCTION "modificato"(integer, integer, integer, integer, integer, integer, integer, integer, integer)
RETURNS boolean
 LANGUAGE plpgsql
 VOLATILE
AS
$$
BEGIN
 PERFORM
    *
 FROM elementi_modificati
 WHERE id_prog = $1
   AND id_utente = $2
   AND id_soggetto = $3
   AND id_quadro = $4
   AND id_sezione = $5
   AND id_modulo = $6
   AND id_elemento = $7
   AND posizione = $8
   AND istanza = $9
   AND versione = last_ver(id_prog);
 RETURN FOUND;
END;
$$
;

CREATE OR REPLACE FUNCTION "incrementa_versione"(integer, text, text)
RETURNS void
 LANGUAGE plpgsql
 VOLATILE
AS
$$
DECLARE
	old RECORD;
	id_mv integer;
BEGIN

SELECT id_motivoversione INTO id_mv FROM motivi_versione WHERE descr_versione = $2;

IF id_mv IS NULL THEN
    RAISE EXCEPTION 'Motivo versione errato.';
END IF;

SELECT INTO STRICT old
   MAX(versione) AS versione
FROM versioni
WHERE id_prog = $1;

INSERT INTO versioni(
   id_prog
 , versione
 , id_motivoversione
 , dua
)
SELECT
   id_prog
 , versione + 1
 , id_mv
 , dua
FROM versioni
WHERE (id_prog,versione) = ($1,old.versione);

INSERT INTO elementi(
   id_prog
 , id_soggetto
 , id_utente
 , id_quadro
 , id_sezione
 , id_modulo
 , id_elemento
 , posizione
 , istanza
 , referer
 , tipo
 , valore
 , versione
) SELECT
   id_prog
 , id_soggetto
 , id_utente
 , id_quadro
 , id_sezione
 , id_modulo
 , id_elemento
 , posizione
 , istanza
 , referer
 , tipo
 , valore
 , old.versione + 1 AS versione
FROM elementi
WHERE id_prog = $1
  AND versione = old.versione;

INSERT INTO verifiche_versioni(
   id_verifica
 , id_prog
 , versione
 , conclusa_il
) SELECT
   id_verifica
 , id_prog
 , old.versione + 1 AS versione
 , NULL
FROM verifiche
WHERE richiesta_versione
  AND id_prog = $1;

INSERT INTO verifiche_versioni(
   id_verifica
 , id_prog
 , versione
 , conclusa_il
) SELECT
   id_verifica
 , id_prog
 , versione + 1
 , NULL
FROM verifiche_versioni
NATURAL JOIN verifiche
WHERE conclusa_il IS NULL
  AND NOT richiesta_versione
  AND id_prog = $1
  AND versione = old.versione;

UPDATE verifiche
SET richiesta_versione = FALSE
WHERE richiesta_versione
  AND id_prog = $1;

UPDATE utenti
   SET data_compilato = null, data_presentato = null
FROM enti
WHERE id_prog = $1
  AND utenti.id_ente = enti.id_ente
  AND CASE COALESCE($3, 'si')
        WHEN 'si' THEN true
        WHEN 'tutti' THEN true
        WHEN 'capofila' THEN capofila
        WHEN 'no' THEN false
        ELSE true
    END;

END;
$$
;

CREATE OR REPLACE FUNCTION "trasferimento_coerenza_id"()
RETURNS TRIGGER
 LANGUAGE plpgsql
 VOLATILE
AS
$$
BEGIN
 IF (NEW.id_prog, NEW.id_doc) NOT IN (SELECT id_prog, id_doc FROM documenti) THEN
  RAISE EXCEPTION 'Documento non appartenente al progetto.';
 END IF;
 RETURN NEW;
END
$$
;

CREATE OR REPLACE FUNCTION "incrementa_versione"(integer, text)
RETURNS void
 LANGUAGE sql
 VOLATILE
AS
$$
SELECT incrementa_versione($1, $2, 'si');
$$
;

CREATE OR REPLACE FUNCTION "prima_presentazione"(integer)
RETURNS timestamp
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT
   termine
FROM versioni
WHERE id_prog = $1
  AND versione = 0
;
$$
;

CREATE OR REPLACE FUNCTION "atto_greater"(atto, atto)
RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT ($1).data > ($2).data OR (($1).data = ($2).data AND ($1).numero > ($2).numero)
$$
;

CREATE OR REPLACE FUNCTION "atto_greater"(atto, date)
RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT ($1).data > $2
$$
;

CREATE OR REPLACE FUNCTION "atto_max"(atto, atto)
RETURNS atto
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT
   CASE
    WHEN atto_greater($1,$2) THEN $1
    ELSE $2 END
$$
;

CREATE OR REPLACE FUNCTION "atto_min"(atto, atto)
RETURNS atto
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT
   CASE
    WHEN atto_greater($1,$2) THEN $2
    ELSE $1 END
$$
;

CREATE OR REPLACE FUNCTION "atto_prettyprint"(atto)
RETURNS text
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT 'D.D. ' || ($1).numero || ' del ' || to_char(($1).data, 'DD/MM/YYYY')
$$
;

CREATE OR REPLACE FUNCTION "controlla_iban"(text)
RETURNS boolean
 LANGUAGE plpgsql
 IMMUTABLE
AS
$$
DECLARE
 iban text = upper($1); -- iban
 len_iban integer = length(iban);
 c text; -- carattere corrente della stringa iban
 n text = ''; -- numeric to be tested mod 97
BEGIN
 iban := substr(iban,5) || substr(iban,1,4);
 FOR i IN 1..len_iban LOOP
  c := substr(iban,i,1);
  n := n || CASE WHEN c ~ '[0-9]' THEN c ELSE (ascii(c)-55)::text END;
 END LOOP;
 RETURN n::numeric%97 = 1;
END;

$$
;

-- Create tables section -------------------------------------------------

-- Table verifiche_versioni

CREATE TABLE "verifiche_versioni"(
 "id_verifica" Integer NOT NULL,
 "id_prog" Integer NOT NULL,
 "versione" Integer NOT NULL,
 "inizio_verifica" Timestamp DEFAULT now()::timestamp(0) NOT NULL,
 "conclusa_il" Timestamp
)
WITH (OIDS=FALSE)
;
COMMENT ON COLUMN "verifiche_versioni"."inizio_verifica" IS 'timestamp della creazione del record.'
;

-- Add keys for table verifiche_versioni

ALTER TABLE "verifiche_versioni" ADD CONSTRAINT "pk_verifiche_versioni" PRIMARY KEY ("id_prog","versione","id_verifica")
;

-- Table trasferimenti

CREATE TABLE "trasferimenti"(
 "id_trasferimento" Serial NOT NULL,
 "id_prog" Integer NOT NULL,
 "versione" Integer NOT NULL,
 "id_doc" Integer,
 "tentativo" Integer DEFAULT 1 NOT NULL,
 "registrato_trasferimento" Timestamp DEFAULT now()::timestamp(0) NOT NULL,
 "inizio_trasferimento" Timestamp,
 "fine_trasferimento" Timestamp,
 "status" Text,
 "sospeso" Boolean DEFAULT false NOT NULL
)
WITH (OIDS=FALSE)
;

COMMENT ON TABLE "trasferimenti" IS 'Ogni riga è un trasferimento da eseguire.

Un trasferimento '
;
COMMENT ON COLUMN "trasferimenti"."status" IS 'Lo stato del trasferimento:
NULL non è stato ancora tentato
altrimenti la risposta del WebService
Il WebService ritorna la stringa ''OK'' quando il trasferimento è riuscito.'
;

-- Create indexes for table trasferimenti

CREATE UNIQUE INDEX "tentativi_progressivi" ON "trasferimenti" ("id_doc","id_prog","versione","tentativo")
;

CREATE UNIQUE INDEX "tentativi_in_attesa" ON "trasferimenti" ("id_doc","id_prog","versione")
WHERE sospeso
;

-- Add keys for table trasferimenti

ALTER TABLE "trasferimenti" ADD CONSTRAINT "pk_trasferimenti" PRIMARY KEY ("id_trasferimento")
;

-- Create triggers for table trasferimenti

CREATE TRIGGER "trasferimento_coerenza_id"
  BEFORE UPDATE
  ON "trasferimenti" FOR EACH ROW
 EXECUTE PROCEDURE "trasferimento_coerenza_id"()
;

-- Table pagamenti

CREATE TABLE "pagamenti"(
 "id_pagamento" Serial NOT NULL,
 "id_prog" Integer NOT NULL,
 "id_soggetto" Integer NOT NULL,
 "data" Timestamp NOT NULL,
 "cro" Integer,
 "iban" Character varying(32),
 "importo_netto" Double precision NOT NULL,
 "accredito" Timestamp,
 "causale" Text,
 "revocato" Boolean DEFAULT false NOT NULL,
  CONSTRAINT "iban_pagamento" CHECK (   iban IS NULL
OR controlla_iban(iban))
)
WITH (OIDS=FALSE)
;

-- Add keys for table pagamenti

ALTER TABLE "pagamenti" ADD CONSTRAINT "pk_pagamenti" PRIMARY KEY ("id_pagamento")
;

-- Create triggers for table pagamenti

-- Table feedback

CREATE TABLE "feedback"(
 "id_utente" Integer NOT NULL,
 "oggetto" Text NOT NULL
        CONSTRAINT "lunghezza_oggetto" CHECK (length(oggetto) < 4096),
 "testo" Text NOT NULL
        CONSTRAINT "lunghezza_testo" CHECK (length(testo) < 1048576),
 "voto_sistema" Smallint NOT NULL
        CONSTRAINT "voto_sistema" CHECK (voto_sistema >= 1 AND voto_sistema <= 5),
 "voto_supporto" Smallint NOT NULL
        CONSTRAINT "voto_supporto" CHECK (voto_supporto >= 1 AND voto_supporto <= 5)
)
WITH (OIDS=FALSE)
;

-- Add keys for table feedback

ALTER TABLE "feedback" ADD CONSTRAINT "pk_feedback" PRIMARY KEY ("id_utente")
;

-- Table firme

CREATE TABLE "firme"(
 "id_doc" Integer NOT NULL,
 "hash" Text NOT NULL,
 "certificato" Text NOT NULL,
 "firmatario" Text,
 "issuer" Text,
 "validfrom" Timestamp,
 "validto" Timestamp
)
WITH (OIDS=FALSE)
;

-- Add keys for table firme

ALTER TABLE "firme" ADD CONSTRAINT "pk_firme" PRIMARY KEY ("id_doc")
;

-- Table progetti_esterni

CREATE TABLE "progetti_esterni"(
 "id_relazione" Serial NOT NULL,
 "id_utente" Integer NOT NULL,
 "id_utente_esterno" Integer NOT NULL,
 "id_prog_esterno" Integer NOT NULL,
 "linea_esterna" Text NOT NULL,
 "id_soggetto_esterno" Integer NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table progetti_esterni

ALTER TABLE "progetti_esterni" ADD CONSTRAINT "pk_progetti_esterni" PRIMARY KEY ("id_relazione")
;

ALTER TABLE "progetti_esterni" ADD CONSTRAINT "utente_linea" UNIQUE ("id_utente","linea_esterna")
;

-- Table lavori

CREATE TABLE "lavori"(
 "id_verifica" Integer NOT NULL,
 "id_utente" Integer NOT NULL
)
WITH (OIDS=FALSE)
;

-- Create indexes for table lavori

CREATE UNIQUE INDEX "utenti_lavori_unici" ON "lavori" ("id_verifica","id_utente")
;

-- Add keys for table lavori

ALTER TABLE "lavori" ADD CONSTRAINT "pk_lavori" PRIMARY KEY ("id_verifica","id_utente")
;

-- Create aggregates section -------------------------------------------------

CREATE AGGREGATE "max"(atto)
(
 SFUNC = "atto_max",
 STYPE = atto,
 INITCOND = '(0,1970-01-01)'
)
;

CREATE AGGREGATE "min"(atto)
(
 SFUNC = "atto_min",
 STYPE = atto,
 INITCOND = '(0,9999-01-01)'
)
;

-- Create views section -------------------------------------------------

CREATE OR REPLACE VIEW "progetti_prima_presentazione"
 AS /*select_statement*/

SELECT
   id_prog
 , termine AS prima_presentazione
FROM progetti
NATURAL JOIN versioni
WHERE versione = 0
;

CREATE OR REPLACE VIEW "elementi_modificati"
 AS
SELECT
   e.*
 , prev.valore AS prev
FROM elementi e
LEFT JOIN elementi prev USING(id_prog, id_soggetto, id_utente, id_quadro, id_sezione, id_modulo, id_elemento, posizione, istanza)
WHERE prev.versione = last_ver(id_prog) - 1
  AND prev.valore <> e.valore
;

CREATE OR REPLACE VIEW "utenti_mdl"
 AS /*select_statement*/
SELECT
   id_utente
 , id_soggetto
 , id_prog
 , username
 , passwd
 , livello
 , attivo
 , nome
 , cognome
 , cod_fisc
 , telefono
 , email
 , data_compilato IS NOT NULL AS compilato
 , data_compilato
 , data_presentato IS NOT NULL AS presentato
 , data_presentato
 , (SELECT id_utente FROM feedback WHERE id_utente = utenti.id_utente LIMIT 1) IS NOT NULL AS feedback_lasciato
 , (SELECT id_utente FROM elementi WHERE id_utente = utenti.id_utente LIMIT 1) IS NOT NULL AS ha_elementi
FROM utenti
;

CREATE OR REPLACE VIEW "progetti_mdl"
 AS /*select_statement*/

SELECT
   id_prog
 , versione
 , attivita
 , tipo_progetto
 , linea_progetto
 , id_prog_padre
 , titolo_prog
 , titolo_prog_esteso
 , inoltro IS NOT NULL AS chiusopr
 , inoltro AS data_chiusopr
 , termine IS NOT NULL AS progetto_presentato
 , termine AS progetto_data_presentato
 , inizio_versione
 , prima_presentazione(id_prog)
 , attivo AS progetto_attivo
 , atto_ammissione
 , atto_ammissione IS NOT NULL AS ammesso
 , (atto_ammissione).numero AS numero_atto_ammissione
 , (atto_ammissione).data AS data_atto_ammissione
 , atto_finanziamento
 , atto_finanziamento IS NOT NULL AS finanziato
 , (atto_finanziamento).numero AS numero_atto_finanziamento
 , (atto_finanziamento).data AS data_atto_finanziamento
 , cupst
 , cupartea
 , dua
 , motivo_versione AS tipo_versione
 , tipo_versione_long(versione, motivo_versione, inizio_versione, inoltro)
 , valida
FROM progetti
NATURAL JOIN versioni
;

CREATE OR REPLACE VIEW "soggetti_mdl"
 AS /*select_statement*/

SELECT
   id_soggetto
 , tipo_soggetto
 , rag_sociale
 , sl_indirizzo
 , sl_cap
 , sl_comune
 , sl_provincia
 , cf_soggetto
 , soggetto_confermato
 , soggetto_autorizzato
 , dimensione
 , capofila
 , nome_rappr
 , cognome_rappr
 , cf_rappr
 , tel_rappr
 , fax_rappr
 , email_rappr
 , email_pec_rappr
 , nato_a
 , data_nascita
 , soggetto_tipo_doc
 , num_doc
 , rilasciato_doc
 , scadenza_doc
 , procura_rappr
 , ateco_2002
 , ateco_2007
 , associate
 , collegate
 , conti_consolidati
 , dipartimento
 , piva_soggetto
 , inps
 , inail
 , cciaa
 , ritenuta_acconto
 , data_creazione
 , ultima_modifica AS ultima_modifica_soggetto
 , partecipante
 , cancellato
 , ul_indirizzo
 , ul_cap
 , ul_comune
 , ul_provincia
FROM soggetti

;

CREATE OR REPLACE VIEW "documenti_mdl"
 AS /*select_statement*/

SELECT
   id_doc
 , id_prog
 , id_utente
 , nome_doc
 , tipo_doc
 , filesize
 , filetype
 , data_upl
 , nota
 , hash
 , versione
 , motivo_versione AS tipo_versione
 , tipo_versione_long(versione, motivo_versione, inizio_versione, inoltro)
 , deprecato
FROM documenti
NATURAL JOIN versioni
;

CREATE OR REPLACE VIEW "verifiche_mdl"
 AS /*select_statement*/

SELECT
   id_verifica
 , id_prog
 , id_utente
 , tipo_verifica
 , richiesta_versione
 , versione
 , inizio_versione
 , inoltro AS data_chiusopr
 , conclusa_il
 , sospesa
FROM verifiche
NATURAL LEFT JOIN lavori
NATURAL LEFT JOIN verifiche_versioni
NATURAL LEFT JOIN versioni

;

-- Create rewrite rules section -------------------------------------------------

CREATE OR REPLACE RULE "insert" AS ON INSERT
 TO "utenti_mdl"
 DO INSTEAD
 INSERT INTO utenti(
   id_utente
 , id_soggetto
 , id_prog
 , username
 , passwd
 , livello
 , attivo
 , nome
 , cognome
 , cod_fisc
 , telefono
 , email
 , data_compilato
 , data_presentato
) VALUES (
   COALESCE(NEW.id_utente, nextval('utenti_id_utente_seq'))
 , COALESCE(NEW.id_soggetto, 0)
 , COALESCE(NEW.id_prog, 0)
 , NEW.username
 , NEW.passwd
 , COALESCE(NEW.livello, 'Presentatore')
 , COALESCE(NEW.attivo, TRUE)
 , NEW.nome
 , NEW.cognome
 , NEW.cod_fisc
 , NEW.telefono
 , NEW.email
 , NEW.data_compilato
 , NEW.data_presentato
)

;

CREATE OR REPLACE RULE "update" AS ON UPDATE
 TO "utenti_mdl"
 DO INSTEAD
 UPDATE utenti
SET
   id_soggetto	= NEW.id_soggetto
 , id_prog		= NEW.id_prog
 , username	= NEW.username
 , passwd		= NEW.passwd
 , livello		= COALESCE(NEW.livello, OLD.livello)
 , attivo		= NEW.attivo
 , nome		= NEW.nome
 , cognome	= NEW.cognome
 , cod_fisc	= NEW.cod_fisc
 , telefono		= NEW.telefono
 , email		= NEW.email
 , data_compilato	= NEW.data_compilato
 , data_presentato	= NEW.data_presentato
WHERE id_utente	= NEW.id_utente
;

CREATE OR REPLACE RULE "insert" AS ON INSERT
 TO "soggetti_mdl"
 DO INSTEAD
 INSERT INTO soggetti(
   id_soggetto
 , tipo_soggetto
 , rag_sociale
 , sl_indirizzo
 , sl_cap
 , sl_comune
 , sl_provincia
 , cf_soggetto
 , soggetto_confermato
 , soggetto_autorizzato
 , dimensione
 , capofila
 , nome_rappr
 , cognome_rappr
 , cf_rappr
 , tel_rappr
 , fax_rappr
 , email_rappr
 , email_pec_rappr
 , nato_a
 , data_nascita
 , soggetto_tipo_doc
 , num_doc
 , rilasciato_doc
 , scadenza_doc
 , procura_rappr
 , ateco_2002
 , ateco_2007
 , associate
 , collegate
 , conti_consolidati
 , dipartimento
 , piva_soggetto
 , inps
 , inail
 , cciaa
 , ritenuta_acconto
 , partecipante
 , ul_indirizzo
 , ul_cap
 , ul_comune
 , ul_provincia
)VALUES (
   COALESCE(NEW.id_soggetto, nextval('soggetti_id_soggetto_seq'))
 , COALESCE(NEW.tipo_soggetto, 'Soggetto Generico')
 , NEW.rag_sociale
 , NEW.sl_indirizzo
 , NEW.sl_cap
 , NEW.sl_comune
 , NEW.sl_provincia
 , NEW.cf_soggetto
 , COALESCE(NEW.soggetto_confermato, FALSE)
 , COALESCE(NEW.soggetto_autorizzato, FALSE)
 , NEW.dimensione
 , COALESCE(NEW.capofila, TRUE)
 , NEW.nome_rappr
 , NEW.cognome_rappr
 , NEW.cf_rappr
 , NEW.tel_rappr
 , NEW.fax_rappr
 , NEW.email_rappr
 , NEW.email_pec_rappr
 , NEW.nato_a
 , NEW.data_nascita
 , NEW.soggetto_tipo_doc
 , NEW.num_doc
 , NEW.rilasciato_doc
 , NEW.scadenza_doc
 , COALESCE(NEW.procura_rappr, FALSE)
 , NEW.ateco_2002
 , NEW.ateco_2007
 , COALESCE(NEW.associate, FALSE)
 , COALESCE(NEW.collegate, FALSE)
 , COALESCE(NEW.conti_consolidati, FALSE)
 , NEW.dipartimento
 , NEW.piva_soggetto
 , NEW.inps
 , NEW.inail
 , NEW.cciaa
 , COALESCE(NEW.ritenuta_acconto, TRUE)
 , COALESCE(NEW.partecipante, TRUE)
 , NEW.ul_indirizzo
 , NEW.ul_cap
 , NEW.ul_comune
 , NEW.ul_provincia
)

;

CREATE OR REPLACE RULE "update" AS ON UPDATE
 TO "soggetti_mdl"
 DO INSTEAD
 UPDATE soggetti
SET
   tipo_soggetto		= COALESCE(NEW.tipo_soggetto, 'Soggetto Generico')
 , rag_sociale		= NEW.rag_sociale
 , sl_indirizzo		= NEW.sl_indirizzo
 , sl_cap			= NEW.sl_cap
 , sl_comune		= NEW.sl_comune
 , sl_provincia		= NEW.sl_provincia
 , cf_soggetto		= NEW.cf_soggetto
 , soggetto_confermato	= NEW.soggetto_confermato
 , soggetto_autorizzato	= NEW.soggetto_autorizzato
 , dimensione		= NEW.dimensione
 , capofila			= NEW.capofila
 , nome_rappr		= NEW.nome_rappr
 , cognome_rappr		= NEW.cognome_rappr
 , cf_rappr		= NEW.cf_rappr
 , tel_rappr		= NEW.tel_rappr
 , fax_rappr		= NEW.fax_rappr
 , email_rappr		= NEW.email_rappr
 , email_pec_rappr		= NEW.email_pec_rappr
 , nato_a			= NEW.nato_a
 , data_nascita		= NEW.data_nascita
 , soggetto_tipo_doc	= NEW.soggetto_tipo_doc
 , num_doc		= NEW.num_doc
 , rilasciato_doc		= NEW.rilasciato_doc
 , scadenza_doc		= NEW.scadenza_doc
 , procura_rappr		= NEW.procura_rappr
 , ateco_2002		= NEW.ateco_2002
 , ateco_2007		= NEW.ateco_2007
 , associate		= NEW.associate
 , collegate		= NEW.collegate
 , conti_consolidati		= NEW.conti_consolidati
 , dipartimento		= NEW.dipartimento
 , piva_soggetto		= NEW.piva_soggetto
 , inps			= NEW.inps
 , inail			= NEW.inail
 , cciaa			= NEW.cciaa
 , ritenuta_acconto		= NEW.ritenuta_acconto
 , partecipante		= NEW.partecipante
 , ul_indirizzo		= NEW.ul_indirizzo
 , ul_cap			= NEW.ul_cap
 , ul_comune		= NEW.ul_comune
 , ul_provincia		= NEW.ul_provincia
WHERE id_soggetto	= NEW.id_soggetto
;

CREATE OR REPLACE RULE "insert" AS ON INSERT
 TO "documenti_mdl"
 DO INSTEAD
 INSERT INTO documenti(
   id_doc
 , id_prog
 , id_utente
 , nome_doc
 , tipo_doc
 , filesize
 , filetype
 , data_upl
 , nota
 , hash
 , versione
 , deprecato
) VALUES (
   COALESCE(NEW.id_doc, nextval('documenti_id_doc_seq'))
 , NEW.id_prog
 , NEW.id_utente
 , NEW.nome_doc
 , COALESCE(NEW.tipo_doc, 'Sconosciuto')
 , NEW.filesize
 , NEW.filetype
 , NEW.data_upl
 , NEW.nota
 , NEW.hash
 , last_ver(NEW.id_prog)
 , COALESCE(NEW.deprecato, FALSE)
)

;

CREATE OR REPLACE RULE "update" AS ON UPDATE
 TO "documenti_mdl"
 DO INSTEAD
 UPDATE documenti
SET
   id_prog		= NEW.id_prog
 , id_utente	= NEW.id_utente
 , nome_doc	= NEW.nome_doc
 , tipo_doc	= COALESCE(NEW.tipo_doc, 'Sconosciuto')
 , filesize		= NEW.filesize
 , filetype		= NEW.filetype
 , data_upl	= NEW.data_upl
 , nota		= NEW.nota
 , hash		= NEW.hash
 , versione	= NEW.versione
 , deprecato	= NEW.deprecato
WHERE id_doc	= NEW.id_doc
;

CREATE OR REPLACE RULE "insert" AS ON INSERT
 TO "progetti_mdl"
 DO INSTEAD
 (
INSERT INTO progetti(
   id_prog
 , attivita
 , tipo_progetto
 , linea_progetto
 , id_prog_padre
 , titolo_prog
 , titolo_prog_esteso
 , attivo
 , atto_ammissione
 , cupst
 , cupartea
) VALUES (
   COALESCE(NEW.id_prog, nextval('progetti_id_prog_seq'))
 , COALESCE(NEW.attivita, 'Generica')
 , COALESCE(NEW.tipo_progetto, 'Generico')
 , COALESCE(NEW.linea_progetto, 'Nessuna')
 , NEW.id_prog_padre
 , NEW.titolo_prog
 , NULLIF(NEW.titolo_prog_esteso, NEW.titolo_prog)
 , COALESCE(NEW.progetto_attivo, FALSE)
 , (NEW.numero_atto_ammissione, NEW.data_atto_ammissione)::atto
 , NEW.cupst
 , NEW.cupartea
);

INSERT INTO versioni(
   id_prog
 , versione
 , motivo_versione
 , inizio_versione
 , valida
 , dua
) VALUES (
   COALESCE(NEW.id_prog, currval('progetti_id_prog_seq'))
 , 0
 , COALESCE(NEW.tipo_versione, 'Domanda')
 , now()::timestamp(0)
 , COALESCE(NEW.valida, false)
 , NEW.dua
);
)

;

CREATE OR REPLACE RULE "update" AS ON UPDATE
 TO "progetti_mdl"
 DO INSTEAD
 (
UPDATE progetti
SET
   id_prog			= NEW.id_prog
 , attivita			= COALESCE(NEW.attivita, 'Generica')
 , tipo_progetto		= COALESCE(NEW.tipo_progetto, 'Singolo')
 , linea_progetto		= COALESCE(NEW.linea_progetto, 'Nessuna')
 , titolo_prog		= NEW.titolo_prog
 , titolo_prog_esteso		= NULLIF(NEW.titolo_prog_esteso, NEW.titolo_prog)
 , attivo			= COALESCE(NEW.progetto_attivo, FALSE)
 , atto_ammissione		= (NEW.numero_atto_ammissione, NEW.data_atto_ammissione)::atto
 , cupst			= NEW.cupst
 , cupartea		= NEW.cupartea
WHERE id_prog		= NEW.id_prog
;

UPDATE versioni
SET
   inoltro		= NEW.data_chiusopr
 , termine		= NEW.progetto_data_presentato
 , valida		= COALESCE(NEW.valida, OLD.valida)
 , dua		= NEW.dua
WHERE id_prog	= NEW.id_prog
      AND versione	= last_ver(id_prog);
)

;

CREATE OR REPLACE RULE "insert" AS ON INSERT
 TO "verifiche_mdl"
 DO INSTEAD
 (
INSERT INTO verifiche(
   id_verifica
 , id_prog
 , tipo_verifica
) VALUES (
   COALESCE(NEW.id_verifica, nextval('verifiche_id_verifica_seq'))
 , NEW.id_prog
 , NEW.tipo_verifica
);

INSERT INTO lavori(id_verifica, id_utente)
SELECT
   COALESCE(NEW.id_verifica, currval('verifiche_id_verifica_seq'))
 , NEW.id_utente
WHERE NEW.id_utente IS NOT NUL
;

INSERT INTO verifiche_versioni(
   id_verifica
 , id_prog
 , versione
 , conclusa_il
) VALUES (
   COALESCE(NEW.id_verifica, currval('verifiche_id_verifica_seq'))
 , NEW.id_prog
 , last_ver(NEW.id_prog)
 , NEW.conclusa_il
);
)

;

CREATE OR REPLACE RULE "update" AS ON UPDATE
 TO "verifiche_mdl"
 DO INSTEAD
 (
UPDATE verifiche
SET
   id_prog		= NEW.id_prog
 , tipo_verifica	= NEW.tipo_verifica
 , richiesta_versione	= NEW.richiesta_versione
 , sospesa	= NEW.sospesa
WHERE id_verifica	= NEW.id_verifica
;

UPDATE lavori
SET
   id_utente	= NEW.id_utente
WHERE id_verifica	= NEW.id_verifica AND NEW.id_utente IS NOT NULL
;

UPDATE verifiche_versioni
SET
   id_prog		= NEW.id_prog
 , conclusa_il	= NEW.conclusa_il
WHERE id_verifica	= NEW.id_verifica
  AND versione	= last_ver(NEW.id_prog)
;
)

;

-- Create relationships section ------------------------------------------------- 

CREATE INDEX "IX_log_id_utente_fkey_log" ON "log" ("id_utente")
;
ALTER TABLE "log" ADD CONSTRAINT "log_id_utente_fkey" FOREIGN KEY ("id_utente") REFERENCES "utenti" ("id_utente") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_documenti_id_utente_fkey_documenti" ON "documenti" ("id_utente")
;
ALTER TABLE "documenti" ADD CONSTRAINT "documenti_id_utente_fkey" FOREIGN KEY ("id_utente") REFERENCES "utenti" ("id_utente") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_utenti_id_soggetto_fkey_utenti" ON "utenti" ("id_soggetto")
;
ALTER TABLE "utenti" ADD CONSTRAINT "utenti_id_soggetto_fkey" FOREIGN KEY ("id_soggetto") REFERENCES "soggetti" ("id_soggetto") ON DELETE SET DEFAULT ON UPDATE CASCADE
;

CREATE INDEX "IX_utenti_user_liv_fkey_utenti" ON "utenti" ("livello")
;
ALTER TABLE "utenti" ADD CONSTRAINT "utenti_user_liv_fkey" FOREIGN KEY ("livello") REFERENCES "livelli" ("livello") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_documenti_id_tipo_doc_fkey_documenti" ON "documenti" ("tipo_doc")
;
ALTER TABLE "documenti" ADD CONSTRAINT "documenti_id_tipo_doc_fkey" FOREIGN KEY ("tipo_doc") REFERENCES "tipi_doc" ("tipo_doc") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_utenti_id_prog_fkey_utenti" ON "utenti" ("id_prog")
;
ALTER TABLE "utenti" ADD CONSTRAINT "utenti_id_prog_fkey" FOREIGN KEY ("id_prog") REFERENCES "progetti" ("id_prog") ON DELETE SET DEFAULT ON UPDATE CASCADE
;

CREATE INDEX "IX_progetti_id_attivita_fkey_progetti" ON "progetti" ("attivita")
;
ALTER TABLE "progetti" ADD CONSTRAINT "progetti_id_attivita_fkey" FOREIGN KEY ("attivita") REFERENCES "attivita" ("attivita") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_progetti_id_tipoprog_fkey_progetti" ON "progetti" ("tipo_progetto")
;
ALTER TABLE "progetti" ADD CONSTRAINT "progetti_id_tipoprog_fkey" FOREIGN KEY ("tipo_progetto") REFERENCES "tipi_progetto" ("tipo_progetto") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_soggetti_tipo_soggetto_fkey_soggetti" ON "soggetti" ("tipo_soggetto")
;
ALTER TABLE "soggetti" ADD CONSTRAINT "soggetti_tipo_soggetto_fkey" FOREIGN KEY ("tipo_soggetto") REFERENCES "tipi_soggetto" ("tipo_soggetto") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_elementi_id_utente_fkey_elementi" ON "elementi" ("id_utente")
;
ALTER TABLE "elementi" ADD CONSTRAINT "elementi_id_utente_fkey" FOREIGN KEY ("id_utente") REFERENCES "utenti" ("id_utente") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_elementi_id_soggetto_fkey_elementi" ON "elementi" ("id_soggetto")
;
ALTER TABLE "elementi" ADD CONSTRAINT "elementi_id_soggetto_fkey" FOREIGN KEY ("id_soggetto") REFERENCES "soggetti" ("id_soggetto") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_verifiche_id_prog_fkey_verifiche" ON "verifiche" ("id_prog")
;
ALTER TABLE "verifiche" ADD CONSTRAINT "verifiche_id_prog_fkey" FOREIGN KEY ("id_prog") REFERENCES "progetti" ("id_prog") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_versioni_id_prog_fkey_versioni" ON "versioni" ("id_prog")
;
ALTER TABLE "versioni" ADD CONSTRAINT "versioni_id_prog_fkey" FOREIGN KEY ("id_prog") REFERENCES "progetti" ("id_prog") ON DELETE CASCADE ON UPDATE CASCADE
;

CREATE INDEX "IX_versioni_id_motivoversione_fkey_versioni" ON "versioni" ("motivo_versione")
;
ALTER TABLE "versioni" ADD CONSTRAINT "versioni_id_motivoversione_fkey" FOREIGN KEY ("motivo_versione") REFERENCES "motivi_versione" ("motivo_versione") ON DELETE RESTRICT ON UPDATE CASCADE
;

CREATE INDEX "IX_elementi_id_prog_versione_pfkey_elementi" ON "elementi" ("id_prog", "versione")
;
ALTER TABLE "elementi" ADD CONSTRAINT "elementi_id_prog_versione_pfkey" FOREIGN KEY ("id_prog", "versione") REFERENCES "versioni" ("id_prog", "versione") ON DELETE CASCADE ON UPDATE CASCADE
;

CREATE INDEX "IX_verifiche_versioni_id_prog_versione_pfkey_verifiche_versioni" ON "verifiche_versioni" ("id_prog", "versione")
;
ALTER TABLE "verifiche_versioni" ADD CONSTRAINT "verifiche_versioni_id_prog_versione_pfkey" FOREIGN KEY ("id_prog", "versione") REFERENCES "versioni" ("id_prog", "versione") ON DELETE CASCADE ON UPDATE CASCADE
;

CREATE INDEX "IX_verifiche_versioni_id_verifica_fkey_verifiche_versioni" ON "verifiche_versioni" ("id_verifica")
;
ALTER TABLE "verifiche_versioni" ADD CONSTRAINT "verifiche_versioni_id_verifica_fkey" FOREIGN KEY ("id_verifica") REFERENCES "verifiche" ("id_verifica") ON DELETE CASCADE ON UPDATE CASCADE
;

CREATE INDEX "IX_documenti_id_prog_versione_fkey_documenti" ON "documenti" ("id_prog", "versione")
;
ALTER TABLE "documenti" ADD CONSTRAINT "documenti_id_prog_versione_fkey" FOREIGN KEY ("id_prog", "versione") REFERENCES "versioni" ("id_prog", "versione") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_pagamenti_id_prog_fkey_pagamenti" ON "pagamenti" ("id_prog")
;
ALTER TABLE "pagamenti" ADD CONSTRAINT "pagamenti_id_prog_fkey" FOREIGN KEY ("id_prog") REFERENCES "progetti" ("id_prog") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_pagamenti_id_soggetto_fkey_pagamenti" ON "pagamenti" ("id_soggetto")
;
ALTER TABLE "pagamenti" ADD CONSTRAINT "pagamenti_id_soggetto_fkey" FOREIGN KEY ("id_soggetto") REFERENCES "soggetti" ("id_soggetto") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_trasferimenti_id_doc_fkey_trasferimenti" ON "trasferimenti" ("id_doc")
;
ALTER TABLE "trasferimenti" ADD CONSTRAINT "trasferimenti_id_doc_fkey" FOREIGN KEY ("id_doc") REFERENCES "documenti" ("id_doc") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_progetti_id_prog_fkey_progetti" ON "progetti" ("id_prog_padre")
;
ALTER TABLE "progetti" ADD CONSTRAINT "progetti_id_prog_fkey" FOREIGN KEY ("id_prog_padre") REFERENCES "progetti" ("id_prog") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_verifiche_id_tipoverifica_fkey_verifiche" ON "verifiche" ("tipo_verifica")
;
ALTER TABLE "verifiche" ADD CONSTRAINT "verifiche_id_tipoverifica_fkey" FOREIGN KEY ("tipo_verifica") REFERENCES "tipi_verifica" ("tipo_verifica") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_progetti_id_lineaprogetto_fkey_progetti" ON "progetti" ("linea_progetto")
;
ALTER TABLE "progetti" ADD CONSTRAINT "progetti_id_lineaprogetto_fkey" FOREIGN KEY ("linea_progetto") REFERENCES "linee_progetto" ("linea_progetto") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_trasferimenti_id_prog_versione_fkey_trasferimenti" ON "trasferimenti" ("id_prog", "versione")
;
ALTER TABLE "trasferimenti" ADD CONSTRAINT "trasferimenti_id_prog_versione_fkey" FOREIGN KEY ("id_prog", "versione") REFERENCES "versioni" ("id_prog", "versione") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_feedback_id_utente_pfkey_feedback" ON "feedback" ("id_utente")
;
ALTER TABLE "feedback" ADD CONSTRAINT "feedback_id_utente_pfkey" FOREIGN KEY ("id_utente") REFERENCES "utenti" ("id_utente") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_elementi_referer_fkey_elementi" ON "elementi" ("referer")
;
ALTER TABLE "elementi" ADD CONSTRAINT "elementi_referer_fkey" FOREIGN KEY ("referer") REFERENCES "soggetti" ("id_soggetto") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_firme_id_doc_fkey_firme" ON "firme" ("id_doc")
;
ALTER TABLE "firme" ADD CONSTRAINT "firme_id_doc_fkey" FOREIGN KEY ("id_doc") REFERENCES "documenti" ("id_doc") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_progetti_esterni_id_utente_fkey_progetti_esterni" ON "progetti_esterni" ("id_utente")
;
ALTER TABLE "progetti_esterni" ADD CONSTRAINT "progetti_esterni_id_utente_fkey" FOREIGN KEY ("id_utente") REFERENCES "utenti" ("id_utente") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_lavori_id_verifica_fkey_lavori" ON "lavori" ("id_verifica")
;
ALTER TABLE "lavori" ADD CONSTRAINT "lavori_id_verifica_fkey" FOREIGN KEY ("id_verifica") REFERENCES "verifiche" ("id_verifica") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_lavori_id_utente_fkey_lavori" ON "lavori" ("id_utente")
;
ALTER TABLE "lavori" ADD CONSTRAINT "lavori_id_utente_fkey" FOREIGN KEY ("id_utente") REFERENCES "utenti" ("id_utente") ON DELETE NO ACTION ON UPDATE NO ACTION
;






CREATE OR REPLACE FUNCTION grant_srvinfo()
  RETURNS void AS
$BODY$
BEGIN
EXECUTE 'GRANT ' || current_user || ' TO srvinfo';
END;
$BODY$
LANGUAGE plpgsql
;

SELECT grant_srvinfo() FROM pg_roles WHERE rolname = 'srvinfo';
DROP FUNCTION grant_srvinfo();

COMMIT;
