/*
Created: 03/11/2009
Modified: 16/01/2013
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.1
*/

// fix per il problema di UTF-8 BOM

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 FUNCTION "cod_fisc"()
RETURNS TRIGGER
 LANGUAGE 'plpgsql'
 VOLATILE
AS
$$
BEGIN
 IF NEW.cod_fisc IS NULL THEN RETURN NEW; END IF;
 IF NOT controlla_cf(NEW.cod_fisc) THEN
  RAISE EXCEPTION 'Formato Codice Fiscale errato.';
 END IF;
 RETURN NEW;
END;
$$
;

CREATE 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 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 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 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 FUNCTION "controlla_pivacf"(text)
RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT controlla_piva($1) OR controlla_cf($1);
$$
;

CREATE 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 FUNCTION "sanitize_ente"()
RETURNS TRIGGER
 LANGUAGE 'plpgsql'
 VOLATILE
AS
$$
BEGIN
 NEW.rag_sociale = trim(NEW.rag_sociale);  
 NEW.indirizzo = trim(NEW.indirizzo);  
 NEW.cap = trim(NEW.cap);  
 IF length(NEW.cap) <> 5 THEN
  RAISE EXCEPTION 'Formato CAP errato.';
 END IF;
 NEW.citta = initcap(trim(NEW.citta)); 
 NEW.provincia = upper(trim(NEW.provincia));
 IF length(NEW.provincia) <> 2 THEN
  RAISE EXCEPTION 'Formato provincia errato.';
 END IF;
 NEW.cf_ente = upper(trim(NEW.cf_ente)); 
 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.tipo_doc = trim(NEW.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 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 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); 
 NEW.cupst = trim(NEW.cupst);  
 NEW.cupartea = trim(NEW.cupartea);
 RETURN NEW;
END;
$$
;

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

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

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

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

CREATE 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 FUNCTION "ultima_modifica"()
RETURNS TRIGGER
 LANGUAGE plpgsql
 VOLATILE
AS
$$
BEGIN
 IF NEW.ente_confermato AND OLD.ente_confermato <> NEW.ente_confermato THEN
  NEW.ultima_modifica = now()::timestamp(0);
 END IF;
 RETURN NEW;
END
$$
;

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

-- Table attivita

CREATE TABLE "attivita"(
 "id_attivita" Serial NOT NULL,
 "descr_attivita" Text NOT NULL
        CONSTRAINT "descr_attivita_length" CHECK (length(descr_attivita)<=200)
)
WITHOUT OIDS
;

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

INSERT INTO attivita(id_attivita, descr_attivita) VALUES (0, 'Generica');


-- Add keys for table attivita

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

ALTER TABLE "attivita" ADD CONSTRAINT "descr_attivita" UNIQUE ("descr_attivita")
;

-- Table linee_progetto

CREATE TABLE "linee_progetto"(
 "id_lineaprogetto" Serial NOT NULL,
 "descr_lineaprogetto" Text NOT NULL
        CONSTRAINT "descr_lineaprogetto_length" CHECK (length(descr_lineaprogetto)<=200)
)
WITHOUT OIDS
;

INSERT INTO linee_progetto(id_lineaprogetto, descr_lineaprogetto) VALUES(0, 'Nessuna');

-- Add keys for table linee_progetto

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

ALTER TABLE "linee_progetto" ADD CONSTRAINT "descr_lineaprogetto" UNIQUE ("descr_lineaprogetto")
;

-- Table livelli

CREATE TABLE "livelli"(
 "user_liv" Serial NOT NULL,
 "descr_liv" Text NOT NULL
        CONSTRAINT "descr_liv_length" CHECK (length(descr_liv)<=200)
)
WITHOUT OIDS
;

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

INSERT INTO livelli(user_liv, descr_liv) VALUES (0, 'Sconosciuto');
INSERT INTO livelli(descr_liv)
SELECT * FROM regexp_split_to_table(
'Amministratore,Attivatore,Controllore,Istruttore,Manager,Presentatore,Supervisore di Provincia'
, ',') descr_liv
WHERE descr_liv NOT IN (SELECT descr_liv FROM livelli);

-- Add keys for table livelli

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

ALTER TABLE "livelli" ADD CONSTRAINT "descr_liv" UNIQUE ("descr_liv")
;

-- Table motivi_versione

CREATE TABLE "motivi_versione"(
 "id_motivoversione" Serial NOT NULL,
 "descr_versione" Text NOT NULL
)
WITHOUT OIDS
;

INSERT INTO motivi_versione(id_motivoversione, descr_versione) VALUES (0, 'Versione');
INSERT INTO motivi_versione(descr_versione)
SELECT * FROM regexp_split_to_table(
'Domanda,Integrazione,Variante,Variante da Decreto'
, ',') descr_versione
WHERE descr_versione NOT IN (SELECT descr_versione FROM motivi_versione);

-- Add keys for table motivi_versione

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

ALTER TABLE "motivi_versione" ADD CONSTRAINT "descr_versione" UNIQUE ("descr_versione")
;

-- Table tipi_doc

CREATE TABLE "tipi_doc"(
 "id_tipo_doc" Serial NOT NULL,
 "descr_tipo_doc" Text NOT NULL
        CONSTRAINT "descr_tipo_doc_length" CHECK (length(descr_tipo_doc)<=200)
)
WITHOUT OIDS
;

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

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


-- Add keys for table tipi_doc

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

ALTER TABLE "tipi_doc" ADD CONSTRAINT "descr_tipo_doc" UNIQUE ("descr_tipo_doc")
;

-- Table tipi_ente

CREATE TABLE "tipi_ente"(
 "id_tipoente" Serial NOT NULL,
 "descr_tipoente" Text NOT NULL
        CONSTRAINT "descr_tipoente_length" CHECK (length(descr_tipoente)<=200)
)
WITHOUT OIDS
;

INSERT INTO tipi_ente(id_tipoente, descr_tipoente) VALUES (0,'Soggetto Generico');
INSERT INTO tipi_ente(descr_tipoente)
SELECT * FROM regexp_split_to_table(
'Comune,Comunità Montana,Consorzio,Impresa,Organismo di Ricerca,Privato,Provincia,Unione di Comuni'
, ',') descr_tipoente
WHERE descr_tipoente NOT IN (SELECT descr_tipoente FROM tipi_ente);


-- Add keys for table tipi_ente

ALTER TABLE "tipi_ente" ADD CONSTRAINT "pk_tipi_ente" PRIMARY KEY ("id_tipoente")
;

ALTER TABLE "tipi_ente" ADD CONSTRAINT "descr_tipoente" UNIQUE ("descr_tipoente")
;

-- Table tipi_progetto

CREATE TABLE "tipi_progetto"(
 "id_tipoprog" Serial NOT NULL,
 "descr_tipoprog" Text NOT NULL
        CONSTRAINT "descr_tipoprog_length" CHECK (length(descr_tipoprog)<=200)
)
WITHOUT OIDS
;

INSERT INTO tipi_progetto(id_tipoprog, descr_tipoprog) VALUES (0, 'Generico');
INSERT INTO tipi_progetto(descr_tipoprog)
SELECT * FROM regexp_split_to_table(
'Aggregazione,Singolo'
, ',') descr_tipoprog
WHERE descr_tipoprog NOT IN (SELECT descr_tipoprog FROM tipi_progetto);


-- Add keys for table tipi_progetto

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

ALTER TABLE "tipi_progetto" ADD CONSTRAINT "descr_tipoprog" UNIQUE ("descr_tipoprog")
;

-- Table tipi_verifica

CREATE TABLE "tipi_verifica"(
 "id_tipoverifica" Serial NOT NULL,
 "descr_tipoverifica" Text NOT NULL
        CONSTRAINT "descr_tipoverifica_length" CHECK (length(descr_tipoverifica)<=200)
)
WITHOUT OIDS
;

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

-- Add keys for table tipi_verifica

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

ALTER TABLE "tipi_verifica" ADD CONSTRAINT "descr_tipoverifica" UNIQUE ("descr_tipoverifica")
;

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

CREATE FUNCTION "attivita"(integer)
RETURNS text
 LANGUAGE sql
 STABLE
AS
$$
SELECT descr_attivita FROM attivita WHERE id_attivita = $1;
$$
;

CREATE FUNCTION "linea_progetto"(integer)
RETURNS text
 LANGUAGE sql
 STABLE
AS
$$
SELECT descr_lineaprogetto FROM linee_progetto WHERE id_lineaprogetto = $1;
$$
;

CREATE FUNCTION "livello"(integer)
RETURNS text
 LANGUAGE sql
 STABLE
AS
$$
SELECT descr_liv FROM livelli WHERE user_liv = $1;
$$
;

CREATE FUNCTION "motivo_versione"(integer)
RETURNS text
 LANGUAGE sql
 STABLE
AS
$$
SELECT descr_versione FROM motivi_versione WHERE id_motivoversione = $1; 
$$
;

CREATE FUNCTION "tipo_doc"(integer)
RETURNS text
 LANGUAGE sql
 STABLE
AS
$$
SELECT descr_tipo_doc FROM tipi_doc WHERE id_tipo_doc = $1;
$$
;

CREATE FUNCTION "tipo_ente"(integer)
RETURNS text
 LANGUAGE sql
 STABLE
AS
$$
SELECT descr_tipoente FROM tipi_ente WHERE id_tipoente = $1;
$$
;

CREATE FUNCTION "tipo_verifica"(integer)
RETURNS text
 LANGUAGE sql
 IMMUTABLE
AS
$$
SELECT descr_tipoverifica FROM tipi_verifica WHERE id_tipoverifica = $1;
$$
;

CREATE FUNCTION "tipo_progetto"(integer)
RETURNS text
 LANGUAGE sql
 STABLE
AS
$$
SELECT descr_tipoprog FROM tipi_progetto WHERE id_tipoprog = $1;
$$
;

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

-- Table utenti

CREATE TABLE "utenti"(
 "id_utente" Serial NOT NULL,
 "id_ente" Integer DEFAULT 0 NOT NULL,
 "id_prog" Integer DEFAULT 0 NOT NULL,
 "username" Character varying(40) NOT NULL,
 "passwd" Character(32) NOT NULL,
 "user_liv" Integer DEFAULT 1 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)
)
WITHOUT OIDS
;

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, user_liv, attivo, nome, cognome, email) VALUES (0, 'webservice', '', 0, TRUE, 'Web', 'Service', 'srvinfo@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('ebelloni', '', (SELECT user_liv FROM livelli WHERE descr_liv = 'Amministratore'), TRUE, 'Enea', 'Belloni', 'BLLNBR68S28B832Z', '0585798232', 'ebelloni@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('andrea', '', (SELECT user_liv FROM livelli WHERE descr_liv = 'Amministratore'), TRUE, 'Andrea', 'Montagnani', 'MNTNDR70D01E463C', '0585798231', 'amontagnani@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('maurizio', '', (SELECT user_liv FROM livelli WHERE descr_liv = 'Amministratore'), TRUE, 'Maurizio', 'Ascione', 'SCNMRZ76R18B832C', '0585798230', 'mascione@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('alessandro', '', (SELECT user_liv FROM livelli WHERE descr_liv = 'Amministratore'), TRUE, 'Alessandro', 'Fiorenza', 'FRNLSN83T23A345Z', '0585798230', 'afiorenza@sviluppo.toscana.it');

INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('mpgiorgi', md5('mpg2012rf'), (SELECT user_liv FROM livelli WHERE descr_liv = 'Manager'), TRUE, 'Maria Paola', 'Giorgi', 'GRGMPL68R48H501R', '0585798207', 'mpgiorgi@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('florenzini', md5('chiaretta02'), (SELECT user_liv FROM livelli WHERE descr_liv = 'Manager'), TRUE, 'Francesca', 'Lorenzini', 'LRNFNC72A60B832T', '0585798207', 'florenzini@sviluppo.toscana.it');

INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('mmontemurro', md5('mmist012'), (SELECT user_liv FROM livelli WHERE descr_liv = 'Istruttore'), TRUE, 'Maria', 'Montemurro', 'MNTMRA69T50B832H', '0585798', 'mmontemurro@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('mbongiorni', md5('mbist012'), (SELECT user_liv FROM livelli WHERE descr_liv = 'Istruttore'), TRUE, 'Michela', 'Bongiorni', 'BNGMHL70H41F023R', '0585798', 'mbongiorni@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('lsforzini', md5('lsist012'), (SELECT user_liv FROM livelli WHERE descr_liv = 'Istruttore'), TRUE, 'Lidia', 'Sforzini', 'SFRLDI75E66L872P', '0585798', 'lsforzini@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('slombardi', md5('slist012'), (SELECT user_liv FROM livelli WHERE descr_liv = 'Istruttore'), TRUE, 'Serena', 'Lombardi', 'LMBSRN77C57B832E', '0585798', 'slombardi@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('mvalsega', md5('mvist012'), (SELECT user_liv FROM livelli WHERE descr_liv = 'Istruttore'), TRUE, 'Manuela', 'Valsega', 'VLSMNL69C69B832E', '0585798', 'mvalsega@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('fbracciali', md5('fbist012'), (SELECT user_liv FROM livelli WHERE descr_liv = 'Istruttore'), TRUE, 'Fulvia', 'Bracciali', 'BRCFLV67L49A390Q', '0585798', 'fbracciali@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('igiagnoni', md5('Ilist012'), (SELECT user_liv FROM livelli WHERE descr_liv = 'Istruttore'), TRUE, 'Ilaria', 'Giagnoni', 'GGNLRI69E65D612O', '0585798', 'igiagnoni@sviluppo.toscana.it');
INSERT INTO utenti(username, passwd, user_liv, attivo, nome, cognome, cod_fisc, telefono, email) VALUES ('gbarre', md5('gbist012'), (SELECT user_liv FROM livelli WHERE descr_liv = 'Istruttore'), TRUE, 'Gaelle', 'Barrè', 'BRRGLF72M55Z110S', '0585798', 'gbarre@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 enti

CREATE TABLE "enti"(
 "id_ente" Serial NOT NULL,
 "id_tipoente" Integer DEFAULT 0 NOT NULL,
 "rag_sociale" Text NOT NULL,
 "indirizzo" Text,
 "cap" Character(5),
 "citta" Text,
 "provincia" Character(2),
 "cf_ente" Character varying(16) NOT NULL,
 "ente_confermato" Boolean DEFAULT true NOT NULL,
 "ente_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,
 "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_ente" 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,
 CONSTRAINT "rag_sociale_meaningful" CHECK (length(rag_sociale)>1),
 CONSTRAINT "coerenza_autorizzazione" CHECK (NOT ente_autorizzato OR (ente_autorizzato AND ente_confermato))
)
WITHOUT OIDS
;

COMMENT ON TABLE "enti" IS 'Tabella degli enti in relazione agli utenti'
;
COMMENT ON COLUMN "enti"."partecipante" IS 'flag che determina se il soggetto partecipa al parternariato.'
;
COMMENT ON COLUMN "enti"."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 enti(id_ente, rag_sociale, cf_ente, capofila) VALUES (0, 'Nessun Soggetto', '00000000000', FALSE);
INSERT INTO enti(rag_sociale, indirizzo, cap, citta, provincia, cf_ente, capofila, nome_rappr, cognome_rappr, cf_rappr, tel_rappr, fax_rappr, email_rappr) VALUES ('Servizi Informatici - Sviluppo Toscana S.p.A.', 'Via Dorsale, 13', '54100', 'Massa', 'MS', '00566850459', FALSE, 'Geris', 'Musetti', 'MSTGRS50E13E463I', '05857981', '0585798320', 'srvinfo@sviluppo.toscana.it');
INSERT INTO enti(rag_sociale, indirizzo, cap, citta, provincia, cf_ente, capofila, nome_rappr, cognome_rappr, cf_rappr, tel_rappr, fax_rappr, email_rappr) VALUES ('Sviluppo Toscana S.p.A.', 'Via Dorsale, 13', '54100', 'Massa', 'MS', '00566850459', FALSE, 'Geris', 'Musetti', 'MSTGRS50E13E463I', '05857981', '0585798320', 'gmusetti@sviluppo.toscana.it');

-- Add keys for table enti

ALTER TABLE "enti" ADD CONSTRAINT "pk_enti" PRIMARY KEY ("id_ente")
;

-- Create triggers for table enti

CREATE TRIGGER "trigger_sanitize_ente"
  BEFORE INSERT OR UPDATE
  ON "enti" FOR EACH ROW
 EXECUTE PROCEDURE "sanitize_ente"()
;

CREATE TRIGGER "trigger_cf_ente"
  BEFORE INSERT OR UPDATE
  ON "enti" FOR EACH ROW
 EXECUTE PROCEDURE "cf_ente"()
;

CREATE TRIGGER "trigger_cf_rappr"
  BEFORE INSERT OR UPDATE
  ON "enti" FOR EACH ROW
 EXECUTE PROCEDURE "cf_rappr"()
;

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

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

CREATE TRIGGER "trigger_ultima_modifica"
  BEFORE UPDATE
  ON "enti" 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
)
WITHOUT OIDS
;

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,
 "id_utente" Integer NOT NULL,
 "nome_doc" Text NOT NULL,
 "id_tipo_doc" Integer DEFAULT 0 NOT NULL,
 "filesize" Integer NOT NULL,
 "filetype" Text NOT NULL,
 "data_upl" Timestamp NOT NULL,
 "nota" Text,
 "hash" Text NOT NULL,
 "versione" Integer NOT NULL,
 "deprecato" Boolean DEFAULT FALSE NOT NULL
)
WITHOUT OIDS
;

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,
 "id_attivita" Integer DEFAULT 0 NOT NULL,
 "id_tipoprog" Integer DEFAULT 0 NOT NULL,
 "id_lineaprogetto" Integer 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,
 "ammesso" Boolean DEFAULT FALSE NOT NULL,
 "numero_decreto_ammissione" Smallint,
 "data_decreto_ammissione" Date,
 CONSTRAINT "meaningful_titolo_prog" CHECK (length(titolo_prog) > 1)
)
WITHOUT OIDS
;
COMMENT ON COLUMN "progetti"."cupst" IS 'Eccezioni alla regola dei CUP.'
;
COMMENT ON COLUMN "progetti"."ammesso" IS 'Il progetto è ammesso a contributo?'
;

INSERT INTO progetti(id_prog, id_attivita, id_tipoprog, id_lineaprogetto, titolo_prog) VALUES (0, 0, 0, 0, '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_ente" 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
)
WITHOUT OIDS
;

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_ente","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_ente","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,
 "id_utente" Integer NOT NULL,
 "id_tipoverifica" Integer NOT NULL,
 "richiesta_versione" Boolean DEFAULT FALSE NOT NULL,
 "sospesa" Boolean DEFAULT false NOT NULL
)
WITHOUT OIDS
;

-- Create indexes for table verifiche

CREATE UNIQUE INDEX "unique_assignment_for_project" ON "verifiche" ("id_prog","id_tipoverifica")
WHERE NOT sospesa AND tipo_verifica(id_tipoverifica) <> 'Supervisione' AND tipo_verifica(id_tipoverifica) <> '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","id_utente","id_tipoverifica")
;
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,
 "id_motivoversione" Integer DEFAULT 0 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)
)
WITHOUT OIDS
;
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, id_motivoversione, inizio_versione) VALUES (0, 0, 1, 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 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 FUNCTION "elemento"(integer, integer, integer, integer, integer)
RETURNS SETOF elementi
 LANGUAGE sql
 STABLE
AS
$$
SELECT * FROM elemento($1, $2, $3, $4, $5, 0);
$$
;

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

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

-- Function: modificato(integer, integer, integer, integer, integer, integer, integer, integer, integer)

-- DROP FUNCTION modificato(integer, integer, integer, integer, integer, integer, integer, integer, integer);

CREATE OR REPLACE FUNCTION modificato(integer, integer, integer, integer, integer, integer, integer, integer, integer)
  RETURNS boolean AS
$BODY$
  
  DECLARE
      result RECORD;
          vers RECORD;
          BEGIN
          
          select max(versione) INTO vers from versioni where id_prog=$1;

      IF vers.max = 0 THEN
        return FALSE;
      END IF;
          
          select valore from elementi where id_prog=$1 and id_utente=$2 AND id_ente=$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=(vers.max-1) 
          intersect 
          select valore from elementi where id_prog=$1 and id_utente=$2 AND id_ente=$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=vers.max
          INTO result;
          
          IF result.valore IS NULL THEN
            RETURN TRUE;
            ELSE
              RETURN FALSE;
              END IF;    
              
              END; 
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

;

CREATE 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
   $1
 , old.versione + 1
 , id_mv
 , dua
FROM versioni
NATURAL JOIN (SELECT MAX(versione) AS versione FROM versioni WHERE id_prog = $1 GROUP BY id_prog) v
WHERE id_prog = $1;

INSERT INTO elementi(
   id_prog
 , id_ente
 , id_utente
 , id_quadro
 , id_sezione
 , id_modulo
 , id_elemento
 , posizione
 , istanza
 , referer
 , tipo
 , valore
 , versione
) SELECT
   id_prog
 , id_ente
 , 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
WHERE id_prog = $1
  AND CASE COALESCE($3, 'si')
        WHEN 'tutti' THEN true
        WHEN 'si' THEN true
        WHEN 'no' THEN false
        ELSE true
    END;

END;
$$
;

CREATE 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 FUNCTION "incrementa_versione"(integer, text)
RETURNS void
 LANGUAGE sql
 VOLATILE
AS
$$
SELECT incrementa_versione($1, $2, 'si');
$$
;

-- 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
)
WITHOUT OIDS
;

-- 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
)
WITHOUT OIDS
;

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.'
;

-- 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_ente" 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
)
WITHOUT OIDS
;

-- Add keys for table pagamenti

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

-- 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)
)
WITHOUT OIDS
;

-- 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
)
WITHOUT OIDS
;

-- Add keys for table firme

ALTER TABLE "firme" ADD CONSTRAINT "Key1" 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_ente_esterno" Integer NOT NULL
)
WITHOUT OIDS
;

-- 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")
;

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

CREATE VIEW "progetti_prima_presentazione"
 AS /*select_statement*/

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

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

;

CREATE VIEW "utenti_mdl"
 AS /*select_statement*/
SELECT
   id_utente
 , id_ente
 , id_prog
 , username
 , passwd
 , livello(user_liv)
 , 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 VIEW "progetti_mdl"
 AS /*select_statement*/

SELECT
   id_prog
 , versione
 , attivita(id_attivita)
 , tipo_progetto(id_tipoprog)
 , linea_progetto(id_lineaprogetto)
 , 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
 , (SELECT termine FROM versioni WHERE id_prog = progetti.id_prog AND versione = 0) AS prima_presentazione
 , attivo AS progetto_attivo
 , ammesso
 , cupst
 , cupartea
 , dua
 , motivo_versione(id_motivoversione) AS tipo_versione
 , versione || ') ' || motivo_versione(id_motivoversione) || ' (dal ' || to_char(inizio_versione, 'DD/MM/YYYY HH24:MI:SS') || COALESCE(' al ' || to_char(inoltro, 'DD/MM/YYYY HH24:MI:SS'),'') || ')' AS tipo_versione_long
FROM progetti
NATURAL JOIN versioni
--NATURAL JOIN progetti_prima_presentazione
;

CREATE VIEW "soggetti_mdl"
 AS /*select_statement*/

SELECT
   id_ente
 , tipo_ente(id_tipoente)
 , rag_sociale
 , indirizzo
 , cap
 , citta
 , provincia
 , cf_ente
 , ente_confermato AS soggetto_confermato
 , ente_autorizzato AS soggetto_autorizzato
 , dimensione
 , capofila
 , nome_rappr
 , cognome_rappr
 , cf_rappr
 , tel_rappr
 , fax_rappr
 , email_rappr
 , email_pec_rappr
 , nato_a
 , data_nascita
 , tipo_doc AS ente_tipo_doc
 , num_doc
 , rilasciato_doc
 , scadenza_doc
 , procura_rappr
 , ateco_2002
 , ateco_2007
 , associate
 , collegate
 , conti_consolidati
 , dipartimento
 , piva_ente
 , inps
 , inail
 , cciaa
 , ritenuta_acconto
 , data_creazione
 , ultima_modifica AS ultima_modifica_soggetto
 , partecipante
 , cancellato
FROM enti
;

CREATE VIEW "documenti_mdl"
 AS /*select_statement*/

SELECT
   id_doc
 , id_prog
 , id_utente
 , nome_doc
 , tipo_doc(id_tipo_doc)
 , filesize
 , filetype
 , data_upl
 , nota
 , hash
 , versione
 , motivo_versione(id_motivoversione) AS tipo_versione
 , versione || ') ' || motivo_versione(id_motivoversione) || ' (dal ' || to_char(inizio_versione, 'DD/MM/YYYY HH24:MI:SS') || COALESCE(' al ' || to_char(inoltro, 'DD/MM/YYYY HH24:MI:SS'),'') || ')' AS tipo_versione_long
 , deprecato
FROM documenti
NATURAL JOIN versioni
;

CREATE VIEW "verifiche_mdl"
 AS /*select_statement*/

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

;

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

CREATE RULE "insert" AS ON INSERT
 TO "utenti_mdl"
 DO INSTEAD
 INSERT INTO utenti(
   id_utente
 , id_ente
 , id_prog
 , username
 , passwd
 , user_liv
 , attivo
 , nome
 , cognome
 , cod_fisc
 , telefono
 , email
 , data_compilato
 , data_presentato
) VALUES (
   COALESCE(NEW.id_utente, nextval('utenti_id_utente_seq'))
 , COALESCE(NEW.id_ente, 0)
 , COALESCE(NEW.id_prog, 0)
 , NEW.username
 , NEW.passwd
 , COALESCE((SELECT user_liv FROM livelli WHERE descr_liv = NEW.livello), (SELECT user_liv FROM livelli WHERE descr_liv = 'Presentatore'))
 , COALESCE(NEW.attivo, TRUE)
 , NEW.nome
 , NEW.cognome
 , NEW.cod_fisc
 , NEW.telefono
 , NEW.email
 , NEW.data_compilato
 , NEW.data_presentato
)
;

CREATE RULE "update" AS ON UPDATE
 TO "utenti_mdl"
 DO INSTEAD
 UPDATE utenti
SET
   id_ente		= NEW.id_ente
 , id_prog		= NEW.id_prog
 , username	= NEW.username
 , passwd		= NEW.passwd
 , user_liv		= COALESCE((SELECT user_liv FROM livelli WHERE descr_liv = NEW.livello), (SELECT user_liv FROM livelli WHERE descr_liv = 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 RULE "insert" AS ON INSERT
 TO "soggetti_mdl"
 DO INSTEAD
 INSERT INTO enti(
   id_ente
 , id_tipoente
 , rag_sociale
 , indirizzo
 , cap
 , citta
 , provincia
 , cf_ente
 , ente_confermato
 , ente_autorizzato
 , dimensione
 , capofila
 , nome_rappr
 , cognome_rappr
 , cf_rappr
 , tel_rappr
 , fax_rappr
 , email_rappr
 , email_pec_rappr
 , nato_a
 , data_nascita
 , tipo_doc
 , num_doc
 , rilasciato_doc
 , scadenza_doc
 , procura_rappr
 , ateco_2002
 , ateco_2007
 , associate
 , collegate
 , conti_consolidati
 , dipartimento
 , piva_ente
 , inps
 , inail
 , cciaa
 , ritenuta_acconto
 , partecipante
)VALUES (
   COALESCE(NEW.id_ente, nextval('enti_id_ente_seq'))
 , COALESCE((SELECT id_tipoente FROM tipi_ente WHERE descr_tipoente = NEW.tipo_ente), (SELECT id_tipoente FROM tipi_ente WHERE descr_tipoente = 'Soggetto Sconosciuto'))
 , NEW.rag_sociale
 , NEW.indirizzo
 , NEW.cap
 , NEW.citta
 , NEW.provincia
 , NEW.cf_ente
 , 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.ente_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_ente
 , NEW.inps
 , NEW.inail
 , NEW.cciaa
 , COALESCE(NEW.ritenuta_acconto, TRUE)
 , COALESCE(NEW.partecipante, TRUE)
)
;

CREATE RULE "update" AS ON UPDATE
 TO "soggetti_mdl"
 DO INSTEAD
 UPDATE enti
SET
   id_tipoente	= COALESCE((SELECT id_tipoente FROM tipi_ente WHERE descr_tipoente = NEW.tipo_ente), (SELECT id_tipoente FROM tipi_ente WHERE descr_tipoente = 'Soggetto Generico'))
 , rag_sociale	= NEW.rag_sociale
 , indirizzo		= NEW.indirizzo
 , cap		= NEW.cap
 , citta		= NEW.citta
 , provincia	= NEW.provincia
 , cf_ente		= NEW.cf_ente
 , ente_confermato	= NEW.soggetto_confermato
 , ente_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
 , tipo_doc	= NEW.ente_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_ente	= NEW.piva_ente
 , inps		= NEW.inps
 , inail		= NEW.inail
 , cciaa		= NEW.cciaa
 , ritenuta_acconto	= NEW.ritenuta_acconto
 , partecipante	= NEW.partecipante
WHERE id_ente	= NEW.id_ente

;

CREATE RULE "insert" AS ON INSERT
 TO "documenti_mdl"
 DO INSTEAD
 INSERT INTO documenti(
   id_doc
 , id_prog
 , id_utente
 , nome_doc
 , id_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((SELECT id_tipo_doc FROM tipi_doc WHERE descr_tipo_doc = NEW.tipo_doc), (SELECT id_tipo_doc FROM tipi_doc WHERE descr_tipo_doc = 'Sconosciuto'))
 , NEW.filesize
 , NEW.filetype
 , NEW.data_upl
 , NEW.nota
 , NEW.hash
 , last_ver(NEW.id_prog)
 , COALESCE(NEW.deprecato, FALSE)
)
;

CREATE 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
 , id_tipo_doc	= COALESCE((SELECT id_tipo_doc FROM tipi_doc WHERE descr_tipo_doc = NEW.tipo_doc), (SELECT id_tipo_doc FROM tipi_doc WHERE descr_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 RULE "insert" AS ON INSERT
 TO "progetti_mdl"
 DO INSTEAD
 (
INSERT INTO progetti(
   id_prog
 , id_attivita
 , id_tipoprog
 , id_lineaprogetto
 , id_prog_padre
 , titolo_prog
 , titolo_prog_esteso
 , attivo
 , ammesso
 , cupst
 , cupartea
) VALUES (
   COALESCE(NEW.id_prog, nextval('progetti_id_prog_seq'))
 , COALESCE((SELECT id_attivita FROM attivita WHERE descr_attivita = NEW.attivita), 0)
 , COALESCE((SELECT id_tipoprog FROM tipi_progetto WHERE descr_tipoprog = NEW.tipo_progetto), 1)
 , COALESCE((SELECT id_lineaprogetto FROM linee_progetto WHERE descr_lineaprogetto = NEW.linea_progetto), (SELECT id_lineaprogetto FROM linee_progetto WHERE descr_lineaprogetto = 'Nessuna'))
 , NEW.id_prog_padre
 , NEW.titolo_prog
 , NEW.titolo_prog_esteso
 , COALESCE(NEW.progetto_attivo, FALSE)
 , COALESCE(NEW.ammesso, FALSE)
 , NEW.cupst
 , NEW.cupartea
);

INSERT INTO versioni(
   id_prog
 , versione
 , id_motivoversione
 , inizio_versione
 , dua
) VALUES (
   COALESCE(NEW.id_prog, currval('progetti_id_prog_seq'))
 , 0
 , COALESCE((SELECT id_motivoversione FROM motivi_versione WHERE descr_versione = NEW.tipo_versione), (SELECT id_motivoversione FROM motivi_versione WHERE descr_versione = 'Domanda'))
 , now()::timestamp(0)
 , NEW.dua
);
)
;

CREATE RULE "update" AS ON UPDATE
 TO "progetti_mdl"
 DO INSTEAD
 (
UPDATE progetti
SET
   id_prog			= NEW.id_prog
 , id_attivita		= COALESCE((SELECT id_attivita FROM attivita WHERE descr_attivita = NEW.attivita), (SELECT id_attivita FROM attivita WHERE descr_attivita = 'Generica'))
 , id_tipoprog		= COALESCE((SELECT id_tipoprog FROM tipi_progetto WHERE descr_tipoprog = NEW.tipo_progetto), (SELECT id_tipoprog FROM tipi_progetto WHERE descr_tipoprog = 'Singolo'))
 , id_lineaprogetto		= COALESCE((SELECT id_lineaprogetto FROM linee_progetto WHERE descr_lineaprogetto = NEW.linea_progetto), (SELECT id_lineaprogetto FROM linee_progetto WHERE descr_lineaprogetto = 'Nessuna'))
 , titolo_prog		= NEW.titolo_prog
 , titolo_prog_esteso		= NEW.titolo_prog_esteso
 , attivo			= COALESCE(NEW.progetto_attivo, FALSE)
 , cupst			= NEW.cupst
 , cupartea		= NEW.cupartea
WHERE id_prog		= NEW.id_prog
;

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

CREATE RULE "insert" AS ON INSERT
 TO "verifiche_mdl"
 DO INSTEAD
 (
INSERT INTO verifiche(
   id_verifica
 , id_prog
 , id_utente
 , id_tipoverifica
)VALUES (
   COALESCE(NEW.id_verifica, nextval('verifiche_id_verifica_seq'))
 , NEW.id_prog
 , NEW.id_utente
 , (SELECT id_tipoverifica FROM tipi_verifica WHERE descr_tipoverifica = NEW.tipo_verifica)
);

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 RULE "update" AS ON UPDATE
 TO "verifiche_mdl"
 DO INSTEAD
 (
UPDATE verifiche
SET
   id_utente	= NEW.id_utente
 , id_prog		= NEW.id_prog
 , id_tipoverifica	= (SELECT id_tipoverifica FROM tipi_verifica WHERE descr_tipoverifica = NEW.tipo_verifica)
 , richiesta_versione	= NEW.richiesta_versione
 , sospesa	= NEW.sospesa
WHERE id_verifica	= NEW.id_verifica
;

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_ente_fkey_utenti" ON "utenti" ("id_ente")
;
ALTER TABLE "utenti" ADD CONSTRAINT "utenti_id_ente_fkey" FOREIGN KEY ("id_ente") REFERENCES "enti" ("id_ente") ON DELETE SET DEFAULT ON UPDATE CASCADE
;

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

CREATE INDEX "IX_documenti_id_tipo_doc_fkey_documenti" ON "documenti" ("id_tipo_doc")
;
ALTER TABLE "documenti" ADD CONSTRAINT "documenti_id_tipo_doc_fkey" FOREIGN KEY ("id_tipo_doc") REFERENCES "tipi_doc" ("id_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" ("id_attivita")
;
ALTER TABLE "progetti" ADD CONSTRAINT "progetti_id_attivita_fkey" FOREIGN KEY ("id_attivita") REFERENCES "attivita" ("id_attivita") ON DELETE RESTRICT ON UPDATE CASCADE
;

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

CREATE INDEX "IX_enti_id_tipoente_fkey_enti" ON "enti" ("id_tipoente")
;
ALTER TABLE "enti" ADD CONSTRAINT "enti_id_tipoente_fkey" FOREIGN KEY ("id_tipoente") REFERENCES "tipi_ente" ("id_tipoente") 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_ente_fkey_elementi" ON "elementi" ("id_ente")
;
ALTER TABLE "elementi" ADD CONSTRAINT "elementi_id_ente_fkey" FOREIGN KEY ("id_ente") REFERENCES "enti" ("id_ente") 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_verifiche_id_utente_fkey_verifiche" ON "verifiche" ("id_utente")
;
ALTER TABLE "verifiche" ADD CONSTRAINT "verifiche_id_utente_fkey" FOREIGN KEY ("id_utente") REFERENCES "utenti" ("id_utente") 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" ("id_motivoversione")
;
ALTER TABLE "versioni" ADD CONSTRAINT "versioni_id_motivoversione_fkey" FOREIGN KEY ("id_motivoversione") REFERENCES "motivi_versione" ("id_motivoversione") 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_ente_fkey_pagamenti" ON "pagamenti" ("id_ente")
;
ALTER TABLE "pagamenti" ADD CONSTRAINT "pagamenti_id_ente_fkey" FOREIGN KEY ("id_ente") REFERENCES "enti" ("id_ente") 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" ("id_tipoverifica")
;
ALTER TABLE "verifiche" ADD CONSTRAINT "verifiche_id_tipoverifica_fkey" FOREIGN KEY ("id_tipoverifica") REFERENCES "tipi_verifica" ("id_tipoverifica") ON DELETE NO ACTION ON UPDATE NO ACTION
;

CREATE INDEX "IX_progetti_id_lineaprogetto_fkey_progetti" ON "progetti" ("id_lineaprogetto")
;
ALTER TABLE "progetti" ADD CONSTRAINT "progetti_id_lineaprogetto_fkey" FOREIGN KEY ("id_lineaprogetto") REFERENCES "linee_progetto" ("id_lineaprogetto") 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 "enti" ("id_ente") 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
;



COMMIT;
