CREATE SCHEMA catasto_terreni;
CREATE SCHEMA catasto_fabbricati;

CREATE SCHEMA cxf_in;

CREATE EXTENSION postgis;

SHOW search_path;

SET search_path TO schema; -- sostituire con il nome dello schema

SET search_path TO catasto_terreni; -- IMPORTANTE: ABILITARE LO SCHEMA ESATTO, ALTRIMENTI LE TABELLE VERRANNO CREATE NELLO SCHEMA DI DEFAULT public

CREATE TABLE ter(
  pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  codice_amministrativo TEXT,
  sezione TEXT,
  identificativo_immobile INTEGER NOT NULL,
  tipo_immobile TEXT,
  progressivo INTEGER,
  tipo_record INTEGER,
  foglio TEXT,
  numero TEXT,
  denominatore TEXT,
  subalterno TEXT,
  edificialita TEXT,
  qualita TEXT,
  classe TEXT,
  ettari INTEGER,
  are INTEGER,
  centiare INTEGER,
  flag_reddito TEXT,
  flag_porzione TEXT,
  flag_deduzioni TEXT,
  reddito_dominicale_lire TEXT,
  reddito_agrario_lire TEXT,
  reddito_dominicale_euro TEXT,
  reddito_agrario_euro TEXT,
  data_efficacia_valore_atto TEXT,
  data_registrazione_atti_valore_atto TEXT,
  tipo_nota_valore_atto TEXT,
  numero_nota_valore_atto TEXT,
  progressivo_nota_valore_atto TEXT,
  anno_nota_valore_atto TEXT,
  data_efficacia_registrazione_atto TEXT,
  data_registrazione_registrazione_atto TEXT,
  tipo_nota_registrazione_atto TEXT,
  numero_nota_registrazione_atto TEXT,
  progressivo_nota_registrazione_atto TEXT,
  anno_nota_registrazione_atto TEXT,
  partita TEXT,
  annotazione TEXT,
  identificativo_mutazione_iniziale TEXT,
  identificativo_mutazione_finale TEXT,
  codice_causale_atto_generante TEXT,
  descrizione_atto_generante TEXT,
  codice_causale_atto_conclusivo TEXT,
  descrizione_atto_conclusivo TEXT
);

CREATE TABLE tit
(
	pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	codice_amministrativo TEXT,
	sezione TEXT,
	identificativo_soggetto INTEGER NOT NULL,
	tipo_soggetto TEXT,
	identificativo_immobile INTEGER NOT NULL,
	tipo_immobile TEXT,
	codice_diritto TEXT,
	titolo_non_codificato TEXT,
	quota_numeratore_possesso INTEGER,
	quota_denominatore_possesso INTEGER,
	regime TEXT,
	soggetto_riferimento TEXT,
	data_validita_atto_generato TEXT,
	tipo_nota_generato TEXT,
	numero_nota_generato TEXT,
	progressivo_nota_generato TEXT,
	anno_nota_generato TEXT,
	data_registrazione_atti_generato TEXT,
	partita TEXT,
	data_validita_atto_concluso TEXT,
	tipo_nota_concluso TEXT,
	numero_nota_concluso TEXT,
	progessivo_nota_concluso TEXT,
	anno_nota_concluso TEXT,
	data_registrazione_atti_concluso TEXT,
	identificativo_mutazione_iniziale TEXT,
	identificativo_mutazione_finale TEXT,
	identificativo_titolarita INTEGER NOT NULL,
    codice_causale_atto_generante TEXT,
    descrizione_atto_generante TEXT,
    codice_causale_atto_conclusivo TEXT,
    descrizione_atto_conclusivo TEXT
);

CREATE TABLE sogp
(
	pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	codice_amministrativo TEXT,
	sezione	TEXT,
	identificativo_soggetto	INTEGER NOT NULL,
	tipo_soggetto TEXT,
	cognome TEXT,
	nome TEXT,
	sesso TEXT,
	data_nascita TEXT,
	codice_amministratvio_comune_nascita TEXT,
	codice_fiscale	TEXT,
	indicazioni_supplementari TEXT
);

CREATE TABLE sogg
(
	pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	codice_amministrativo TEXT,
	sezione TEXT,
	identificativo_soggetto  integer NOT NULL,
	tipo_soggetto TEXT,
	denominazione TEXT,
	codice_amministrativo_sede TEXT,
	codice_fiscale TEXT
);

CREATE TABLE qualita
(
	pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	id_qualita TEXT,
	descrizione TEXT
);

INSERT INTO qualita (id_qualita, descrizione)
VALUES
('1','seminativo'),
('2','semin irrig'),
('3','semin arbor'),
('4','sem arb irr'),
('5','sem irr arb'),
('6','sem pez fos'),
('7','sem arb p f'),
('8','prato'),
('9','prato irrig'),
('10','prato arbor'),
('11','prato ir ar'),
('12','prato marc'),
('13','prato mar ar'),
('14','marcita'),
('15','risaia'),
('16','risaia stab'),
('17','orto'),
('18','orto irrig'),
('19','orto arbor'),
('20','orto ar irr'),
('21','orto frutt'),
('22','orto pez fos'),
('23','orto fiori'),
('24','orto ir fi'),
('25','ort viv flo'),
('26','vivaio'),
('27','viv orn fl'),
('28','giardini'),
('29','vigneto'),
('30','vigneto arb'),
('31','vigneto irr'),
('32','vig uva tav'),
('33','vign frutt'),
('34','vign ulivet'),
('35','vign mandor'),
('36','uliveto'),
('37','uliv agrum'),
('38','uliv fichet'),
('39','ul fich man'),
('40','uliv frass'),
('41','uliv frutt'),
('42','uliv sommac'),
('43','uliv vignet'),
('44','uliv sugher'),
('45','uliv mandor'),
('46','ul man pist'),
('47','frutteto'),
('48','frutt irrig'),
('49','agrumeto'),
('50','agrum aranc'),
('51','agrum irrig'),
('52','agrum uliv'),
('53','alpe'),
('54','aranceto'),
('55','canneto'),
('56','cappereto'),
('57','carrubeto'),
('58','castagneto'),
('59','cast frutto'),
('60','cast frass'),
('61','chiusa'),
('62','eucalipteto'),
('63','ficheto'),
('64','fico india'),
('65','fico mandor'),
('66','frassineto'),
('67','gelseto'),
('68','limoneto'),
('69','mandorleto'),
('70','mandor fich'),
('71','mandor fico'),
('72','mandarineto'),
('73','noceto'),
('74','noccioleto'),
('75','nocc vignet'),
('76','palmeto'),
('77','pescheto'),
('78','pioppeto'),
('79','pistacch'),
('80','pometo'),
('81','querceto'),
('82','querc ghian'),
('83','roseto'),
('84','saliceto'),
('85','salceto'),
('86','sommaccheto'),
('87','sommac arb'),
('88','somm mandor'),
('89','sommac uliv'),
('90','sughereto'),
('91','pascolo'),
('92','pascolo arb'),
('93','pasc cespug'),
('94','pascolo bc'),
('95','pascolo bm'),
('96','pascolo ba'),
('97','bosco ceduo'),
('98','bosco misto'),
('99','bosco alto'),
('100','palud spart'),
('101','incolt prod'),
('102','orto irr ar'),
('103','nocciol irr'),
('104','sem car irr'),
('105','pereto'),
('106','sem irr prot'),
('107','bosco rap ac'),
('126','serra'),
('127','funghicoltur'),
('130','arativi'),
('131','seminativi'),
('132','prati'),
('133','orti'),
('134','vigneti'),
('135','pascoli'),
('136','alpi'),
('137','lag pal st'),
('138','boschi'),
('150','incolt ster'),
('151','lago pubbl'),
('152','laguna'),
('153','stagno'),
('180','cava'),
('181','lago pesca'),
('182','miniera'),
('183','salina'),
('184','stagn pesca'),
('185','tonnara'),
('186','torbiera'),
('187','valle pesca'),
('188','acque priv'),
('200','aeroporto d'),
('201','aer fort d'),
('202','autovia sp'),
('203','area dem pp'),
('204','banchina'),
('205','cimitero'),
('206','ferrovia sp'),
('207','fortificaz'),
('208','giard pub'),
('209','giard dem'),
('210','giard com'),
('211','giard prov'),
('212','molo'),
('213','parco pubb'),
('214','parco deman'),
('215','parco comun'),
('216','parco prov'),
('217','p v rimembr'),
('218','pza d armi'),
('219','porto'),
('220','somm arg 2'),
('221','somm arg 3'),
('222','tranvia sp'),
('223','somm arg 1'),
('250','canale bon'),
('251','canale irr'),
('252','gora'),
('270','antichita'),
('271','area fab dm'),
('272','area promis'),
('273','area rurale'),
('274','area urbana'),
('275','corte urban'),
('276','costr no ab'),
('277','fa div sub'),
('278','fabb promis'),
('279','fabb rurale'),
('280','fabb diruto'),
('281','fr div sub'),
('282','ente urbano'),
('283','fu d accert'),
('284','porz acc fr'),
('285','porz acc fu'),
('286','porz di fa'),
('287','porz di fr'),
('288','porz rur fp'),
('290','porz di fu'),
('300','acque esent'),
('301','piazza pubb'),
('302','strade pubb'),
('350','accesso'),
('351','accessorio'),
('352','aia'),
('353','andito'),
('354','androne'),
('355','area'),
('356','ascensore'),
('357','autorimessa'),
('358','ballatoio'),
('359','bindolo'),
('360','bottino'),
('361','bucataio'),
('362','canale priv'),
('363','cantina'),
('364','capanna'),
('365','cappella'),
('366','carbonile'),
('367','casello'),
('368','casotto'),
('369','cisterna'),
('370','concimaia'),
('371','conigliera'),
('372','corridoio'),
('373','corte'),
('374','fontana'),
('375','fontanile'),
('376','forno'),
('377','frantoio'),
('378','garage'),
('379','garitta'),
('380','grotta'),
('381','ingresso'),
('382','latrina'),
('383','lavanderia'),
('384','lavatoio'),
('385','legnaia'),
('386','locale dep'),
('387','loggia'),
('388','luogo dep'),
('389','macero'),
('390','montacarichi'),
('391','muro'),
('392','noria'),
('393','oratorio'),
('394','palmeto'),
('395','passaggio'),
('396','passo'),
('397','piazza'),
('398','piazzale'),
('399','pollaio'),
('400','pompa'),
('401','porcile'),
('402','portico'),
('403','portineria'),
('404','portone'),
('405','pozzo'),
('406','resede'),
('407','rifugio ant'),
('408','rimessa'),
('409','ripostiglio'),
('410','scala'),
('411','scolo acqua'),
('412','seccatoio'),
('413','sedime'),
('414','sentina'),
('415','sorgiva'),
('416','sottoscala'),
('417','sottopassag'),
('418','spazio'),
('419','stalla'),
('420','strada priv'),
('421','terrazzo'),
('422','tettoia'),
('423','tinaia'),
('424','vano'),
('425','vasca'),
('450','rel ente ur'),
('451','rel acc com'),
('452','rel f d sub'),
('453','rel acq es'),
('454','relit strad'),
('455','terr n form'),
('500','casa e corte'),
('501','fabb e corte'),
('504','incolto'),
('505','improdutt'),
('506','sentiero'),
('507','ponte'),
('508','cavalcavia'),
('509','casa stalla'),
('510','chiesa'),
('511','edificabile'),
('512','cortile'),
('513','magazzino'),
('514','fosso'),
('515','casa'),
('516','edificio'),
('517','fabbricato'),
('602','lag pal sta'),
('993','modello 26'),
('997','sop var ter'),
('998','soppresso'),
('999','disponibile');

CREATE TABLE codici_diritto
(
	pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	codice_diritto TEXT,
	descrizione TEXT
);

INSERT INTO codici_diritto (codice_diritto, descrizione)
VALUES
('10','Proprietà'),
('1s','Proprietà  superficiaria'),
('1t','Proprietà  per l''area'),
('20','Nuda proprietà'),
('2s','Nuda proprietà  superficiaria'),
('30','Abitazione'),
('3','Comproprietario'),
('3s','Abitazione su proprietà  superficiaria'),
('40','Diritto del concedente'),
('4','Comproprietario per'),
('50','Enfiteusi'),
('60','Superficie'),
('70','Uso'),
('7','Comproprietario del fabbricato'),
('7s','Uso proprietà  superficiaria'),
('7s','Uso proprietà  superficiaria'),
('80','Usufrutto'),
('8a','Usufrutto con diritto di accrescimento'),
('8e','Usufrutto su enfiteusi'),
('8s','Usufrutto su proprietà  superficiaria'),
('8','Comproprietario per l''area'),
('90','Servità'),
('100','Oneri'),
('12','Concedente in parte'),
('14','Livellario parziale per'),
('15','Usufruttuario parziale per'),
('20','Livellario'),
('21','Livellario per'),
('22','Livellario in parte'),
('25','Enfiteuta in parte'),
('26','Colono perpetuo'),
('27','Colono perpetuo per'),
('28','Colono perpetuo in parte'),
('30','Usufruttuario parziale'),
('33','Cousufruttuario generale'),
('36','Usufruttuario generale di livello'),
('37','Usufruttuario parziale di livello'),
('39','Usufruttuario parziale di enfiteusi'),
('40','Usufruttuario generale di colonia'),
('41','Usufruttuario parziale di colonia'),
('42','Usufruttuario generale di dominio diretto'),
('43','Usufruttuario parziale di dominio diretto'),
('50','Cousufruttuario per'),
('52','Usuario perpetuo'),
('53','Usuario a tempo determinato'),
('60','Cousufruttuario di livello'),
('61','Cousufruttuario generale di livello'),
('62','Usufruttuario di livello di'),
('64','Comproprietario per parte di'),
('70','Usufruttuario di colonia per'),
('71','Usufruttuario di dominio diretto per'),
('72','Cousufruttuario generale con diritto di'),
('16','Utilista della superficie'),
('17','Utilista della superficie per'),
('35','Beneficiario'),
('65','Beneficiario per'),
('54','Beneficiario di dominio diretto'),
('46','Possessore'),
('47','Possessore per'),
('48','Compossessore'),
('49','Compossessore per'),
('55','Contestatario'),
('56','Contestatario per'),
('57','Contestatario per usufrutto'),
('99','Presenza di titolo non codificato'),
('990','Presenza di titolo non codificato'),
('0','Assenza di titolo');

CREATE TABLE partite_speciali_terreni
(
	pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	codice_partita TEXT,
	descrizione TEXT
);

INSERT INTO partite_speciali_terreni (codice_partita, descrizione)
VALUES
('1', 'aree di enti urbani e promiscui'),
('2', 'accessori comuni ad enti rurali e ad enti rurali e urbani'),
('3', 'aree di fabbricati rurali, o urbani da accertare, divisi in subalterni'),
('4', 'acque esenti da estimo'),
('5', 'strade pubbliche'),
('0', 'particelle soppresse')

ALTER TABLE ter
ADD COLUMN descrizione_qualita TEXT;

UPDATE ter as t
SET descrizione_qualita = q.descrizione
FROM qualita as q
WHERE t.qualita = q.id_qualita

CREATE OR REPLACE VIEW ter_1 AS
  SELECT * FROM ter
  WHERE tipo_record = '1';

CREATE OR REPLACE VIEW ter_1_clean AS
	SELECT * FROM ter_1
	WHERE ter_1.partita IS DISTINCT FROM '0' AND ter_1.partita IS DISTINCT FROM '4' AND ter_1.partita IS DISTINCT FROM '5' AND subalterno IS NULL;

ALTER TABLE tit
ADD COLUMN descrizione_diritto TEXT;

UPDATE tit as t
SET descrizione_diritto = d.descrizione
FROM codici_diritto as d
WHERE
t.codice_diritto = d.codice_diritto

CREATE OR REPLACE VIEW titp AS
	SELECT * FROM tit
	WHERE tipo_soggetto = 'P';

CREATE OR REPLACE VIEW titg AS
	SELECT * FROM tit
	WHERE tipo_soggetto = 'G';

CREATE OR REPLACE VIEW titp_sogp_aggr AS
SELECT
	row_number() OVER ()::integer AS gid,
	t.identificativo_immobile,
	t.tipo_immobile,
	t.identificativo_soggetto identificativo_soggetto_tit,
	t.descrizione_diritto as diritto,
	concat(t.quota_numeratore_possesso, '/', t.quota_denominatore_possesso) AS quota,
    p.identificativo_soggetto as identificativo_soggetto_sogp,
    p.cognome,
    p.nome,
    p.data_nascita,
    p.codice_fiscale,
    concat(t.identificativo_immobile, '_', t.identificativo_soggetto, '_', t.descrizione_diritto) AS immo_sogp_diritto
	FROM titp t
	JOIN sogp p ON t.identificativo_soggetto = p.identificativo_soggetto

CREATE OR REPLACE VIEW titp_sogp_dist AS
SELECT DISTINCT ON (immo_sogp_diritto) * FROM titp_sogp_aggr

CREATE OR REPLACE VIEW titp_sogp_json AS SELECT
	row_number() OVER ()::integer AS gid,
	identificativo_immobile,
	json_agg
	(
		json_build_object
			(
				'identificativo_soggetto_sogp', identificativo_soggetto_sogp,
            	'cognome', cognome,
            	'nome', nome,
				'codice_fiscale', codice_fiscale,
				'data_nascita', data_nascita,
				'quota', quota,
				'diritto', diritto
			)
	) as soggetto
FROM titp_sogp_dist
GROUP by identificativo_immobile

CREATE OR REPLACE VIEW titg_sogg_aggr AS
SELECT
	row_number() OVER ()::integer AS gid,
	t.identificativo_immobile,
	t.tipo_immobile,
	t.identificativo_soggetto as identificativo_soggetto_tit,
	t.descrizione_diritto as diritto,
	concat(t.quota_numeratore_possesso, '/', t.quota_denominatore_possesso) AS quota,
    g.identificativo_soggetto as identificativo_soggetto_sogg,
	g.denominazione,
	g.codice_amministrativo_sede,
	g.codice_fiscale,
    concat(t.identificativo_immobile, '_', t.identificativo_soggetto, '_', t.descrizione_diritto) AS immo_sogg_diritto
	FROM titg t
	JOIN sogg g ON t.identificativo_soggetto = g.identificativo_soggetto

CREATE OR REPLACE VIEW titg_sogg_dist AS
SELECT DISTINCT ON (immo_sogg_diritto) * FROM titg_sogg_aggr

CREATE OR REPLACE VIEW titg_sogg_json AS SELECT
	row_number() OVER ()::integer AS gid,
	identificativo_immobile,
	json_agg
	(
		json_build_object
			(
				'identificativo_soggetto_sogg', identificativo_soggetto_sogg,
				'denominazione', denominazione,
            	'codice_amministrativo_sede', codice_amministrativo_sede,
				'codice_fiscale', codice_fiscale,
				'quota', quota,
				'diritto', diritto
			)
	) as soggetto
FROM titg_sogg_dist
GROUP by identificativo_immobile;

CREATE OR REPLACE VIEW titp_sogp_ter_persone_fisiche AS
SELECT row_number() OVER ()::integer AS gid,
ter.identificativo_immobile AS identificativo_immobile,
ter.foglio,
ter.numero,
	CASE -- nuova colonna che permette di assegnare un codice univoco per foglio e particella. Servirà per il join con le geometrie del catasto
	WHEN LENGTH(ter.foglio) = 1 THEN concat(ter.codice_amministrativo, '_000', ter.foglio, '_', ter.numero)
    	ELSE
		(
			CASE
		 	WHEN LENGTH(ter.foglio) = 2 THEN concat(ter.codice_amministrativo, '_00', ter.foglio, '_', ter.numero)
		 	ELSE
				(
					CASE
			 		WHEN LENGTH(ter.foglio) = 3 THEN concat(ter.codice_amministrativo, '_0', ter.foglio, '_', ter.numero)
			 		ELSE
			 			(
							CASE
							WHEN LENGTH(ter.foglio) = 4 THEN concat(ter.codice_amministrativo, '_', ter.foglio, '_', ter.numero)
                					END
						)
					END
				)
			END
		)
	END AS com_fg_plla,
ter.descrizione_qualita AS qualita,
ter.classe,
ter.ettari,
ter.are,
ter.centiare,
j.soggetto
FROM ter_1_clean ter
JOIN titp_sogp_json j ON ter.identificativo_immobile = j.identificativo_immobile;

CREATE OR REPLACE VIEW titg_sogg_ter_persone_giuridiche AS
SELECT row_number() OVER ()::integer AS gid,
ter.identificativo_immobile AS identificativo_immobile,
ter.foglio,
ter.numero,
	CASE -- nuova colonna che permette di assegnare un codice univoco per foglio e particella. Servirà per il join con le geometrie del catasto
	WHEN LENGTH(ter.foglio) = 1 THEN concat(ter.codice_amministrativo, '_000', ter.foglio, '_', ter.numero)
    	ELSE
		(
			CASE
		 	WHEN LENGTH(ter.foglio) = 2 THEN concat(ter.codice_amministrativo, '_00', ter.foglio, '_', ter.numero)
		 	ELSE
				(
					CASE
			 		WHEN LENGTH(ter.foglio) = 3 THEN concat(ter.codice_amministrativo, '_0', ter.foglio, '_', ter.numero)
			 		ELSE
			 			(
							CASE
							WHEN LENGTH(ter.foglio) = 4 THEN concat(ter.codice_amministrativo, '_', ter.foglio, '_', ter.numero)
                					END
						)
					END
				)
			END
		)
	END AS com_fg_plla,
ter.descrizione_qualita AS qualita,
ter.classe,
ter.ettari,
ter.are,
ter.centiare,
j.soggetto
FROM ter_1_clean ter
JOIN titg_sogg_json j ON ter.identificativo_immobile = j.identificativo_immobile;

SET search_path TO catasto_fabbricati;
CREATE TABLE fab1(
  pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  codice_amministrativo TEXT,
  sezione TEXT,
  identificativo_immobile INTEGER,
  tipo_immobile TEXT,
  progressivo INTEGER,
  tipo_record INTEGER,
  zona TEXT,
  categoria TEXT,
  classe TEXT,
  consistenza REAL,
  superficie INTEGER,
  rendita_lire REAL,
  rendita_euro REAL,
  lotto TEXT,
  edificio TEXT,
  scala TEXT,
  interno_1 TEXT,
  interno_2 TEXT,
  piano_1 TEXT,
  piano_2 TEXT,
  piano_3 TEXT,
  piano_4 TEXT,
  data_efficacia_generato TEXT,
  data_registrazione_atti_in_atti_dal TEXT,
  tipo_nota TEXT,
  numero_nota TEXT,
  progressivo_nota TEXT,
  anno_nota  TEXT,
  data_efficacia_concluso TEXT,
  data_resgitrazione_atti_ufficio TEXT,
  tipo_nota_registrazione TEXT,
  numero_nota_registrazione TEXT,
  progressivo_nota_registrazione TEXT,
  anno_nota_registrazione TEXT,
  partita TEXT,
  annotazione TEXT,
  identificativo_mutazione_iniziale TEXT,
  identificativo_mutazione_finale TEXT,
  protocollo_notifica TEXT,
  data_notifica TEXT,
  codice_causale_atto_generante TEXT,
  descrizione_atto_generante TEXT,
  codice_causale_atto_conclusivo TEXT,
  descrizione_atto_conclusivo TEXT,
  flag_classamento TEXT);

CREATE TABLE fab_2(
  pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  codice_amministrativo TEXT,
  sezione TEXT,
  identificativo_immobile INTEGER,
  tipo_immobile TEXT,
  progressivo INTEGER,
  tipo_record INTEGER,
  sezione_urbana TEXT,
  foglio TEXT,
  numero TEXT,
  denominatore TEXT,
  subalterno TEXT,
  edificialita TEXT);

CREATE TABLE fab_3(
  pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  codice_amministrativo TEXT,
  sezione TEXT,
  identificativo_immobile INTEGER,
  tipo_immobile TEXT,
  progressivo INTEGER,
  tipo_record INTEGER,
  toponimo TEXT,
  indirizzo TEXT,
  civico_1 TEXT,
  civico_2 TEXT,
  civico_3 TEXT,
  codice_strada TEXT);

CREATE TABLE fab_4(
  pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  codice_amministrativo TEXT,
  sezione TEXT,
  identificativo_immobile INTEGER,
  tipo_immobile TEXT,
  progressivo INTEGER,
  tipo_record INTEGER,
  foglio TEXT,
  numero TEXT,
  denominatore TEXT,
  subalterno TEXT);

CREATE TABLE fab_5(
  pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  codice_amministrativo TEXT,
  sezione TEXT,
  identificativo_immobile INTEGER,
  tipo_immobile TEXT,
  codice_riserva INTEGER,
  partita_iscrizione_riserva INTEGER);

CREATE TABLE partite_speciali_fabbricati
(
	pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	codice_partita TEXT,
	descrizione TEXT
);

INSERT INTO partite_speciali_fabbricati (codice_partita, descrizione)
VALUES
('0', 'beni comuni censibili'),
('A', 'beni comuni non censibili'),
('R', 'fabbricati rurali'),
('C', 'unità immobiliari soppresse');

CREATE TABLE categoria_catastale
(
    pk_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    codice_categoria TEXT,
	descrizione_categoria TEXT
);
INSERT INTO categoria_catastale (codice_categoria, descrizione_categoria)
VALUES
('A01','abitazioni di tipo signorile'),
('A02','abitazioni di tipo civile'),
('A03','abitazioni di tipo economico'),
('A04','abitazioni di tipo popolare'),
('A05','abitazioni di tipo ultapopolare'),
('A06','abitazioni di tipo rurale'),
('A07','abitazioni in villini'),
('A08','abitazioni in ville'),
('A09','castelli, palazzi di eminenti pregi artistici e storici'),
('A10','uffici e studi privati'),
('A11','abitazioni ed alloggi tipici dei luoghi'),
('B01','collegi, convitti; educandati, ricoveri orfanatrofi, ospizi, conventi, seminari, caserme'),
('B02','case di cura ed ospedali senza fini di lucro'),
('B03','prigioni e riformatori'),
('B04','uffici pubblici'),
('B05','scuole e laboratori scientifici'),
('B06','biblioteche, pinacoteche, musei, gallerie, accademie, circoli ricreativi e culturali senza fine di lucro, che non hanno sede in edifici della categoria A/9'),
('B07','cappelle ed oratori non destinati all’esercizio pubblico dei culti'),
('B08','magazzini sotterranei per deposito derrate'),
('C01','negozi e botteghe'),
('C02','magazzini e locali di deposito; cantine e soffitte se non unite all`unità immobiliare abitativa'),
('C03','laboratori per arti e mestieri'),
('C04','fabbricati e locali per esercizi sportivi'),
('C05','stabilimenti balneari e di acque curative'),
('C06','stalle, scuderie, rimesse ed autorimesse'),
('C07','tettoie; posti auto su aree private; posti auto coperti'),
('D01','opifici'),
('D02','alberghi e pensioni'),
('D03','teatri, cinematografi, sale per concerti e spettacoli; arene, parchi giochi, zoo-safari'),
('D04','case di cura e ospedali'),
('D05','istituti di credito, cambio ed assicurazione'),
('D06','fabbricati, locali, aree attrezzate per esercizi sportivi'),
('D07','fabbricati costruiti o adattati per le speciali esigenze di un`attivita` industriale e non suscettibile di destinazione diversa senza radicali trasformazioni'),
('D08','fabbricati costruiti o adattati per le speciali esigenze di un`attivita` commerciale e non suscettibili di destinazione diversa senza radicali trasformazioni'),
('D09','edifici galleggianti o assicurati a punti fissi del suolo; ponti privati soggetti a pedaggio; aree attrezzate per l’appoggio di palloni aerostatici e dirigibili'),
('D10','fabbricati per funzioni produttive connesse alle attività agricole'),
('E01','stazioni per servizi di trasporto terrestri, marittimi ed aerei; stazioni per metropolitane; stazioni per ferrovie; impianti di risalita in genere'),
('E02','ponti comunali e provinciali soggetti a pedaggio'),
('E03','costruzioni e fabbricati per speciali esigenze pubbliche'),
('E04','recinti chiusi per mercati, fiere, posteggio bestiame e simili'),
('E05','fabbricati costituenti fortificazioni e loro dipendenze'),
('E06','fari, semafori torri per rendere pubblico l’uso dell’orologio comunale'),
('E07','fabbricati per l’esercizio pubblico dei culti'),
('E08','fabbricati e costruzioni nei cimiteri esclusi i colombari, i sepolcri e le tombe di famiglia'),
('E09','edifici a destinazione particolare non compresi nelle categorie precedenti del gruppo E'),
('F01','area urbana'),
('F02','unità collabenti'),
('F03','unità in corso di costruzione'),
('F04','unità in corso di definizione'),
('F05','lastrico solare');

CREATE TABLE codice_toponimo (
pk_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
codice_toponimo INTEGER,
denominazione TEXT
);
INSERT INTO codice_toponimo (codice_toponimo, denominazione) VALUES
(0, ''),
(54, 'Contrada'),
(58, 'Corso'),
(86, 'Largo'),
(90, 'Località'),
(130, 'Piazza'),
(172, 'Salita'),
(185,'Località'),
(210, 'Strada'),
(224, 'Traversa'),
(236, 'Via'),
(240, 'Viale'),
(244, 'Vico'),
(248, 'Vico'),
(250,'Villaggio'),
(347,'Case sparse'),
(477, 'Prato'),
(546, 'Strada comunale'),
(566, 'Strada provinciale'),
(661, 'Vico chiuso'),
(843, 'Monte'),
(894, 'Strada poderale');

CREATE OR REPLACE VIEW fab1 AS SELECT * from fab WHERE field_6 = '1';
CREATE OR REPLACE VIEW fab2 AS SELECT * from fab WHERE field_6 = '2';
CREATE OR REPLACE VIEW fab3 AS SELECT * from fab WHERE field_6 = '3';
CREATE OR REPLACE VIEW fab4 AS SELECT * from fab WHERE field_6 = '4';
CREATE OR REPLACE VIEW fab5 AS SELECT * from fab WHERE field_6 = '5';

CREATE OR REPLACE VIEW fab1_colnames AS
SELECT
field_1 AS codice_amministrativo,
field_2 AS sezione,
field_3 AS identificativo_immobile,
field_4 AS tipo_immboile,
field_5 AS progressivo,
field_6 AS tipo_record,
field_7 AS zona,
field_8 AS categoria,
c.descrizione_categoria AS descrizione_categoria,
field_9 AS classe,
field_10 AS consistenza,
CASE WHEN field_8 LIKE 'A%' THEN 'vani' ELSE
	(CASE WHEN field_8 LIKE 'B%' THEN 'metri cubi' ELSE
	 	(CASE WHEN field_8 LIKE 'C%' THEN 'metri quadri' END)
	END)
END
AS unita_misura_consistenza,
field_11 AS superficie,
field_12 AS rendita_lire,
field_13 AS rendita_euro,
field_14 AS lotto,
field_15 AS edificio,
field_16 AS scala,
field_17 AS interno_1,
field_18 AS interno_2,
CASE WHEN field_17 IS NOT NULL THEN
concat ('Int. ',
concat_ws('-', NULLIF(trim(REGEXP_REPLACE(fab1.field_17, '^0+', '')), ''), NULLIF(trim(REGEXP_REPLACE(fab1.field_18, '^0+', '')), ''))) END
AS interno_concat,
field_19 AS piano_1,
field_20 AS piano_2,
field_21 AS piano_3,
field_22 AS piano_4,
CASE WHEN field_19 IS NOT NULL THEN
concat ('Piano ',
concat_ws('--', NULLIF(trim(REGEXP_REPLACE(fab1.field_19, '^0+', '')), ''), NULLIF(trim(REGEXP_REPLACE(fab1.field_20, '^0+', '')), ''), NULLIF(trim(REGEXP_REPLACE(fab1.field_21, '^0+', '')), ''),
		  NULLIF(trim(REGEXP_REPLACE(fab1.field_22, '^0+', '')), ''))) END
AS piano_concat,
field_35 as partita
FROM fab1
LEFT JOIN categoria_catastale c ON fab1.field_8 = c.codice_categoria

CREATE OR REPLACE VIEW fab2_colnames AS
SELECT
field_1 AS codice_amministrativo,
field_2 AS sezione,
field_3 AS identificativo_immobile,
field_4 AS tipo_immboile,
field_5 AS progressivo,
field_6 AS tipo_record,
field_7 AS sezione_urbana,
field_8 AS foglio,
field_9 AS numero,
field_10 AS denominatore,
field_11 AS subalterno,
field_12 AS edificialita,
CASE WHEN field_14 IS NOT NULL THEN 'si' ELSE 'no' END AS immobili_graffati
FROM fab2

CREATE OR REPLACE VIEW fab3_colnames AS
SELECT
field_1 AS codice_amministrativo,
field_2 AS sezione,
field_3 AS identificativo_immobile,
field_4 AS tipo_immboile,
field_5 AS progressivo,
field_6 AS tipo_record,
field_7 AS toponimo,
field_8 AS indirizzo,
field_9 AS civico_1,
field_10 AS civico_2,
field_11 AS civico_3,
field_12 AS codice_strada,
field_13 AS toponimo_a,
field_14 AS indirizzo_a,
field_15 AS civico_1_a,
field_16 AS civico_2_a,
field_17 AS civico_3_a,
field_18 AS codice_strada_a,
field_19 AS toponimo_b,
field_20 AS indirizzo_b,
field_21 AS civico_1_b,
field_22 AS civico_2_b,
field_23 AS civico_3_b,
field_24 AS codice_strada_b,
field_25 AS toponimo_c,
field_26 AS indirizzo_c,
field_27 AS civico_1_c,
field_28 AS civico_2_c,
field_29 AS civico_3_c,
field_30 AS codice_strada_c,
CASE WHEN field_11 IS NULL AND field_10 IS NULL THEN concat_ws (' ', NULLIF(trim(t.denominazione), ''), NULLIF(trim(field_8), ''), NULLIF(trim(REGEXP_REPLACE(field_9, '^0+', '')), '')) ELSE
	(CASE WHEN field_11 IS NULL AND field_10 IS NOT NULL THEN concat_ws (' ', NULLIF(trim(t.denominazione), ''), NULLIF(trim(field_8), ''), NULLIF(trim(REGEXP_REPLACE(field_9, '^0+', '')), '')) ELSE
	 	(CASE WHEN field_11 IS NOT NULL AND field_10 IS NOT NULL THEN concat_ws (' ', NULLIF(trim(t.denominazione), ''), NULLIF(trim(field_8), ''), NULLIF(trim(REGEXP_REPLACE(field_10, '^0+', '')), '')) END)
	 END)
END AS indirizzo_completo,
CASE WHEN field_17 IS NULL AND field_16 IS NULL THEN concat_ws (' ', NULLIF(trim(a.denominazione), ''), NULLIF(trim(field_14), ''), NULLIF(trim(REGEXP_REPLACE(field_15, '^0+', '')), '')) ELSE
	(CASE WHEN field_17 IS NULL AND field_16 IS NOT NULL THEN concat_ws (' ', NULLIF(trim(a.denominazione), ''), NULLIF(trim(field_14), ''), NULLIF(trim(REGEXP_REPLACE(field_15, '^0+', '')), '')) ELSE
	 	(CASE WHEN field_17 IS NOT NULL AND field_16 IS NOT NULL THEN concat_ws (' ', NULLIF(trim(a.denominazione), ''), NULLIF(trim(field_14), ''), NULLIF(trim(REGEXP_REPLACE(field_16, '^0+', '')), ''))END)
	 END)
END AS indirizzo_completo_a,

CASE WHEN field_23 IS NULL AND field_22 IS NULL THEN concat_ws (' ', NULLIF(trim(b.denominazione), ''), NULLIF(trim(field_20), ''), NULLIF(trim(REGEXP_REPLACE(field_21, '^0+', '')), '')) ELSE
	(CASE WHEN field_23 IS NULL AND field_22 IS NOT NULL THEN concat_ws (' ', NULLIF(trim(b.denominazione), ''), NULLIF(trim(field_20), ''), NULLIF(trim(REGEXP_REPLACE(field_21, '^0+', '')), '')) ELSE
	 	(CASE WHEN field_23 IS NOT NULL AND field_22 IS NOT NULL THEN concat_ws (' ', NULLIF(trim(b.denominazione), ''), NULLIF(trim(field_20), ''), NULLIF(trim(REGEXP_REPLACE(field_22, '^0+', '')), ''))END)
	 END)
END AS indirizzo_completo_b,
CASE WHEN field_29 IS NULL AND field_28 IS NULL THEN concat_ws (' ', NULLIF(trim(c.denominazione), ''), NULLIF(trim(field_26), ''), NULLIF(trim(REGEXP_REPLACE(field_27, '^0+', '')), '')) ELSE
	(CASE WHEN field_29 IS NULL AND field_28 IS NOT NULL THEN concat_ws (' ', NULLIF(trim(c.denominazione), ''), NULLIF(trim(field_26), ''), NULLIF(trim(REGEXP_REPLACE(field_27, '^0+', '')), '')) ELSE
	 	(CASE WHEN field_29 IS NOT NULL AND field_28 IS NOT NULL THEN concat_ws (' ', NULLIF(trim(c.denominazione), ''), NULLIF(trim(field_26), ''), NULLIF(trim(REGEXP_REPLACE(field_28, '^0+', '')), ''))END)
	 END)
END AS indirizzo_completo_c
FROM fab3
LEFT JOIN toponimo t ON CAST(fab3.field_7 AS INTEGER) = t.codice_toponimo
LEFT JOIN toponimo a ON CAST(fab3.field_13 AS INTEGER) = a.codice_toponimo
LEFT JOIN toponimo b ON CAST(fab3.field_19 AS INTEGER) = b.codice_toponimo
LEFT JOIN toponimo c ON CAST(fab3.field_25 AS INTEGER) = c.codice_toponimo

CREATE OR REPLACE VIEW fab4_colnames AS
SELECT
field_1 AS codice_amministrativo,
field_2 AS sezione,
field_3 AS identificativo_immobile,
field_4 AS tipo_immboile,
field_5 AS progressivo,
field_6 AS tipo_record,
field_7 AS sezione_urbana,
field_8 AS foglio,
field_9 AS numero,
field_10 AS denominatore,
field_11 AS subalterno
FROM fab4

CREATE OR REPLACE VIEW tit_colnames AS
SELECT DISTINCT ON (field_28)
field_1 AS codice_amministrativo,
field_2 AS sezione,
field_3 AS identificativo_soggetto,
field_4 AS tipo_soggetto,
field_5 AS identificativo_immobile,
field_6 AS tipo_immobile,
field_7 AS codice_diritto,
field_8 AS titolo_non_codificato,
field_9 AS quota_numeratore_possesso,
field_10 AS quota_denominatore_possesso,
field_11 AS regime,
field_12 AS soggetto_riferimento,
field_13 AS data_validita_atto_generato,
field_14 AS tipo_nota_generato,
field_15 AS numero_nota_generato,
field_16 AS progressivo_nota_generato,
field_17 AS anno_nota_generato,
field_18 AS data_registrazione_atti_generato,
field_19 AS partita,
field_20 AS data_validita_atto_concluso,
field_21 AS tipo_nota_concluso,
field_22 AS numero_nota_concluso,
field_23 AS progessivo_nota_concluso,
field_24 AS anno_nota_concluso,
field_25 AS data_registrazione_atti_concluso,
field_26 AS identificativo_mutazione_iniziale,
field_27 AS identificativo_mutazione_finale,
field_28 AS identificativo_titolarita,
field_29 AS codice_causale_atto_generante,
field_30 AS descrizione_atto_generante,
d.descrizione AS descrizione_diritto
FROM tit
LEFT JOIN catasto_terreni.codici_diritto d ON tit.field_7 = d.codice_diritto

CREATE OR REPLACE VIEW titp AS
	SELECT * FROM tit_colnames
	WHERE tipo_soggetto = 'P';

  CREATE OR REPLACE VIEW titg AS
	SELECT * FROM tit_colnames
	WHERE tipo_soggetto = 'G';

CREATE OR REPLACE VIEW sogp AS
SELECT
field_1 AS codice_amministrativo,
field_2 AS sezione,
field_3 AS identificativo_soggetto,
field_4 AS tipo_soggetto,
field_5 AS cognome,
field_6 AS nome,
field_7 AS sesso,
field_8 AS data_nascita,
field_9 AS codice_amministratvio_comune_nascita,
field_10 AS codice_fiscale,
field_11 AS indicazioni_supplementari
FROM sog
WHERE field_4 = 'P';

CREATE OR REPLACE VIEW sogg AS
SELECT
field_1 AS codice_amministrativo,
field_2 AS sezione,
field_3 AS identificativo_soggetto,
field_4 AS tipo_soggetto,
field_5 AS denominazione,
field_6 AS codice_amministrativo_sede,
field_7 AS codice_fiscale
FROM sog
WHERE field_4 = 'G';

CREATE OR REPLACE VIEW titp_sogp AS
SELECT
	t.identificativo_immobile,
	t.tipo_immobile,
	t.identificativo_soggetto identificativo_soggetto_tit,
	t.descrizione_diritto as diritto,
	concat(t.quota_numeratore_possesso, '/', t.quota_denominatore_possesso) AS quota,
    p.identificativo_soggetto as identificativo_soggetto_sogp,
    p.cognome,
    p.nome,
    p.data_nascita,
    p.codice_fiscale,
	FROM titp t
	JOIN sogp p ON t.identificativo_soggetto = p.identificativo_soggetto

CREATE OR REPLACE VIEW titg_sogg AS
SELECT
	t.identificativo_immobile,
	t.tipo_immobile,
	t.identificativo_soggetto identificativo_soggetto_tit,
	t.descrizione_diritto as diritto,
	concat(t.quota_numeratore_possesso, '/', t.quota_denominatore_possesso) AS quota,
    g.identificativo_soggetto as identificativo_soggetto_sogg,
    g.denominazione,
    g.codice_amministrativo_sede,
    g.codice_fiscale
	FROM titg t
	JOIN sogg g ON t.identificativo_soggetto = g.identificativo_soggetto

CREATE OR REPLACE VIEW fab1_2_3_4_5 AS
SELECT
fab1.codice_amministrativo,
fab1.sezione,
fab1.identificativo_immobile,
fab1.progressivo,
fab1.tipo_record,
fab1.zona,
fab1.categoria,
fab1.descrizione_categoria,
fab1.classe,
fab1.consistenza,
fab1.unita_misura_consistenza,
fab1.superficie,
fab1.rendita_lire,
fab1.rendita_euro,
fab1.lotto,
fab1.edificio,
fab1.scala,
fab1.interno_1,
fab1.interno_2,
fab1.interno_concat,
fab1.piano_1,
fab1.piano_2,
fab1.piano_3,
fab1.piano_4,
fab1.piano_concat,
concat_ws ('-', NULLIF(trim(fab1.partita), ''), NULLIF(trim(psf.descrizione), '')) as partita,
fab2.foglio,
fab2.numero,
fab2.denominatore,
fab2.subalterno,
fab2.edificialita,
fab2.immobili_graffati,
CASE WHEN fab3.indirizzo_completo IS NOT NULL AND fab3.indirizzo_completo_a = '' AND fab3.indirizzo_completo_b = '' AND indirizzo_completo_c = '' THEN indirizzo_completo ELSE
	(CASE WHEN fab3.indirizzo_completo_a IS NOT NULL AND fab3.indirizzo_completo_b = '' AND fab3.indirizzo_completo_c = '' THEN fab3.indirizzo_completo_a ELSE
		(CASE WHEN fab3.indirizzo_completo_b IS NOT NULL AND fab3.indirizzo_completo_c = '' THEN fab3.indirizzo_completo_b ELSE
			(CASE WHEN fab3.indirizzo_completo_c IS NOT NULL THEN fab3.indirizzo_completo_c END)
		 END)
	 END)
END AS indirizzo_completo,
CASE WHEN fab4.identificativo_immobile = fab1.identificativo_immobile THEN 'utilità comuni dell''unità immobiliare' END AS utilita_comuni,
CASE WHEN fab5.field_3 = fab1.identificativo_immobile THEN 'riserva dell''unità immobiliare' END AS riserva
from
fab1_colnames fab1
LEFT JOIN fab2_colnames fab2 ON fab1.identificativo_immobile = fab2.identificativo_immobile
LEFT JOIN fab3_colnames fab3 ON fab1.identificativo_immobile = fab3.identificativo_immobile
LEFT JOIN fab4_colnames fab4 ON fab1.identificativo_immobile = fab4.identificativo_immobile
LEFT JOIN fab5 fab5 ON fab1.identificativo_immobile = fab5.field_3
LEFT JOIN partite_speciali_fabbricati psf ON fab1.partita = psf.codice_partita

CREATE OR REPLACE VIEW tit_sogp_sogg AS
SELECT
g.identificativo_immobile as identificativo_immobile,
g.tipo_immobile as tipo_immobile,
'soggetto giuridico' as tipo_soggetto,
g.diritto as diritto,
g.quota as quota,
g.identificativo_soggetto_tit as identificativo_soggetto_tit,
g.identificativo_soggetto_sogg as identificativo_soggetto,
g.denominazione as denominazione,
g.codice_amministrativo_sede as codice_amministrativo_sede,
NULL as data_nascita,
g.codice_fiscale as codice_fiscale
FROM catasto_fabbricati.titg_sogg g
UNION ALL
SELECT
p.identificativo_immobile as identificativo_immobile,
p.tipo_immobile as tipo_immobile,
'persona fisica' as tipo_soggetto,
p.diritto as diritto,
p.quota as quota,
p.identificativo_soggetto_tit as identificativo_soggetto_tit,
p.identificativo_soggetto_sogp as identificativo_soggetto,
concat(p.cognome, ' ', p.nome) as denominazione,
NULL as codice_amministrativo_sede,
p.data_nascita as data_nascita,
p.codice_fiscale as codice_fiscale
FROM catasto_fabbricati.titp_sogp p

CREATE OR REPLACE VIEW dati_censuari_fab AS
SELECT row_number() OVER ()::integer AS gid,
fab.codice_amministrativo,
fab.sezione,
fab.identificativo_immobile AS identificativo_immobile_fab,
fab.progressivo,
fab.tipo_record,
fab.zona,
fab.categoria,
fab.descrizione_categoria,
fab.classe,
fab.consistenza,
fab.unita_misura_consistenza,
fab.superficie,
fab.rendita_lire,
fab.rendita_euro,
fab.lotto,
fab.edificio,
fab.scala,
fab.interno_1,
fab.interno_2,
fab.piano_1,
fab.piano_2,
fab.piano_3,
fab.piano_4,
fab.partita,
fab.foglio,
fab.numero,
fab.denominatore,
fab.subalterno,
fab.edificialita,
fab.immobili_graffati,
fab.indirizzo_completo AS indirizzo,
concat_ws (' ', NULLIF(trim(fab.indirizzo_completo), ''), NULLIF(trim(fab.piano_concat), ''), NULLIF(trim(fab.interno_concat), '') ) AS indirizzo_completo,
fab.utilita_comuni,
fab.riserva,
	CASE -- nuova colonna che permette di assegnare un codice univoco per foglio e particella. Servirà per la relazione con le geometrie del catasto
	WHEN LENGTH(fab.foglio) = 1 THEN concat(fab.codice_amministrativo, '_000', fab.foglio, '_', REGEXP_REPLACE(fab.numero, '^0+', ''))
    	ELSE
		(
			CASE
		 	WHEN LENGTH(fab.foglio) = 2 THEN concat(fab.codice_amministrativo, '_00', fab.foglio, '_', REGEXP_REPLACE(fab.numero, '^0+', ''))
		 	ELSE
				(
					CASE
			 		WHEN LENGTH(fab.foglio) = 3 THEN concat(fab.codice_amministrativo, '_0', fab.foglio, '_', REGEXP_REPLACE(fab.numero, '^0+', ''))
			 		ELSE
			 			(
							CASE
							WHEN LENGTH(fab.foglio) = 4 THEN concat(fab.codice_amministrativo, '_', fab.foglio, '_', REGEXP_REPLACE(fab.numero, '^0+', ''))
                					END
						)
					END
				)
			END
		)
	END AS com_fg_plla,
t.*
FROM fab1_2_3_4_5 as fab
RIGHT JOIN tit_sogp_sogg t ON fab.identificativo_immobile = t.identificativo_immobile;

CREATE TABLE schema.acque AS (SELECT * FROM cxf_in.acque); -- sostituire il nome dello schema con quello desiderato (es. public).
CREATE TABLE schema.confine AS (SELECT * FROM cxf_in.confine);
CREATE TABLE schema.fabbricati AS (SELECT * FROM cxf_in.fabbricati);
CREATE TABLE schema.fiduciali AS (SELECT * FROM cxf_in.fiduciali);
CREATE TABLE schema.linee AS (SELECT * FROM cxf_in.linee);
CREATE TABLE schema.particelle AS (SELECT * FROM cxf_in.particelle);
CREATE TABLE schema.selezione AS (SELECT * FROM cxf_in.selezione);
CREATE TABLE schema.simboli AS (SELECT * FROM cxf_in.simboli);
CREATE TABLE schema.strade AS (SELECT * FROM cxf_in.strade);
CREATE TABLE schema.testi AS (SELECT * FROM cxf_in.testi);

SET search_path TO cxf_in; -- IMPORTANTE: ABILITARE LO SCHEMA ESATTO, ALTRIMENTI LE TABELLE VERRANNO CREATE NELLO SCHEMA DI DEFAULT public;

ALTER TABLE Particelle
ADD COLUMN com_fg_plla TEXT;

UPDATE Particelle
SET com_fg_plla = CONCAT(codice_comune, '_',fg,'_', mappale);

CREATE OR REPLACE VIEW particellare_persone_fisiche AS -- Per questioni di performance sostituire con una Materialized View
SELECT row_number() OVER ()::integer AS gid,
	p.codice_comune AS codice_comune,
	p.fg AS foglio,
	p.mappale as particella,
	CONCAT(p.codice_comune,'_', p.fg,'_', p.mappale) as fg_plla,
	j.identificativo_immobile as identificativo_immobile,
	j.qualita,
	j.classe,
	j.ettari,
	j.are,
	j.centiare,
	j.soggetto,
	p.geom as geom
FROM
	Particelle p
	JOIN catasto_terreni.titp_sogp_ter_persone_fisiche j ON p.com_fg_plla = j.com_fg_plla

CREATE MATERIALIZED VIEW particellare_persone_fisiche_MV AS
SELECT row_number() OVER ()::integer AS gid,
	p.codice_comune AS codice_comune,
	p.fg AS foglio,
	p.mappale as particella,
	CONCAT(p.codice_comune,'_', p.fg,'_', p.mappale) as fg_plla,
	j.identificativo_immobile as identificativo_immobile,
	j.qualita,
	j.classe,
	j.ettari,
	j.are,
	j.centiare,
	j.soggetto,
	p.geom as geom
FROM
	Particelle p
	JOIN catasto_terreni.titp_sogp_ter_persone_fisiche j ON p.com_fg_plla = j.com_fg_plla
WITH DATA

CREATE OR REPLACE VIEW particellare_persone_giuridiche AS -- Per questioni di performance sostituire con una Materialized View
SELECT row_number() OVER ()::integer AS gid,
	p.codice_comune AS codice_comune,
	p.fg AS foglio,
	p.mappale as particella,
	CONCAT(p.codice_comune,'_', p.fg,'_', p.mappale) as fg_plla,
	j.identificativo_immobile as identificativo_immobile,
	j.qualita,
	j.classe,
	j.ettari,
	j.are,
	j.centiare,
	j.soggetto,
	p.geom as geom
FROM
	Particelle p
	JOIN catasto_terreni.titg_sogg_ter_persone_giuridiche j ON p.com_fg_plla = j.com_fg_plla

CREATE MATERIALIZED VIEW particellare_persone_giuridiche_MV AS
SELECT row_number() OVER ()::integer AS gid,
	p.codice_comune AS codice_comune,
	p.fg AS foglio,
	p.mappale as particella,
	CONCAT(p.codice_comune,'_', p.fg,'_', p.mappale) as fg_plla,
	j.identificativo_immobile as identificativo_immobile,
	j.qualita,
	j.classe,
	j.ettari,
	j.are,
	j.centiare,
	j.soggetto,
	p.geom as geom
FROM
	Particelle p
	JOIN catasto_terreni.titg_sogg_ter_persone_giuridiche j ON p.com_fg_plla = j.com_fg_plla
WITH DATA

SET search_path TO catasto_terreni; -- IMPORTANTE: ABILITARE LO SCHEMA ESATTO, ALTRIMENTI LE TABELLE VERRANNO CREATE NELLO SCHEMA DI DEFAULT public;

CREATE OR REPLACE VIEW particelle_partite_speciali_terreni AS
SELECT ter_1.*,
	CASE -- nuova colonna che permette di assegnare un codice univoco per foglio e particella. Servirà per il join con le geometrie del catasto
	WHEN LENGTH(ter_1.foglio) = 1 THEN concat(ter_1.codice_amministrativo, '_000', ter_1.foglio, '_', ter_1.numero)
    	ELSE
		(
			CASE
		 	WHEN LENGTH(ter_1.foglio) = 2 THEN concat(ter_1.codice_amministrativo, '_00', ter_1.foglio, '_', ter_1.numero)
		 	ELSE
				(
					CASE
			 		WHEN LENGTH(ter_1.foglio) = 3 THEN concat(ter_1.codice_amministrativo, '_0', ter_1.foglio, '_', ter_1.numero)
			 		ELSE
			 			(
							CASE
							WHEN LENGTH(ter_1.foglio) = 4 THEN concat(ter_1.codice_amministrativo, '_', ter_1.foglio, '_', ter_1.numero)
                					END
						)
					END
				)
			END
		)
	END AS com_fg_plla,
p.descrizione as descrizione_partita
FROM ter_1
JOIN partite_speciali_terreni p ON ter_1.partita = p.codice_partita
WHERE ter_1.partita IN ('1', '2', '3', '4', '5', '0')

SET search_path TO cxf_in; -- IMPORTANTE: ABILITARE LO SCHEMA ESATTO, ALTRIMENTI LE TABELLE VERRANNO CREATE NELLO SCHEMA DI DEFAULT public;

CREATE MATERIALIZED VIEW particellare_partite_speciali_mv AS
SELECT row_number() OVER ()::integer AS gid,
	p.codice_comune AS codice_comune,
	p.fg AS fg,
	p.mappale as plla,
	CONCAT(p.codice_comune,'_', p.fg,'_', p.mappale) as fg_plla,
	j.*,
	p.geom as geom
FROM
	Particelle p
	JOIN catasto_terreni.particelle_partite_speciali_terreni j ON p.com_fg_plla = j.com_fg_plla
WITH DATA

SELECT * FROM particellare_persone_giuridiche_mv WHERE (soggetto::jsonb @> '[{"denominazione": "VALORE"}]'); -- sotituire a VALORE il valore desiderato (es. COMUNE DI XXXX)

CREATE MATERIALIZED VIEW particellare_SOGGETTO_mv AS
SELECT * FROM particellare_persone_giuridiche_mv WHERE (soggetto::jsonb @> '[{"denominazione": "VALORE"}]') -- sotituire a VALORE il valore desiderato (es. COMUNE DI XXXX)
WITH DATA

SELECT DISTINCT identificativo_immobile FROM tit WHERE tipo_immobile = 'T'

SELECT DISTINCT identificativo_immobile FROM tit WHERE tipo_immobile = 'T' AND tipo_soggetto = 'P'

SELECT DISTINCT identificativo_immobile FROM tit WHERE tipo_immobile = 'T' AND tipo_soggetto = 'G'
```sql
La somma del numero delle particelle soggetti fisici e del numero delle particelle soggetti giuridici non è sempre uguale al numero totale degli immobili poiché alcune particelle potrebbero essere in comune tra i due gruppi.

set search_path TO catasto_terreni;
CREATE OR REPLACE VIEW titolarita_partite_speciali_union AS
SELECT
g.identificativo_immobile as identificativo_immobile,
g.tipo_immobile as tipo_immobile,
'soggetto giuridico' as tipo_soggetto,
g.diritto as diritto,
g.quota as quota,
g.identificativo_soggetto_sogg as identificativo_soggetto,
g.denominazione as denominazione,
g.codice_amministrativo_sede as codice_amministrativo_sede,
NULL as data_nascita,
g.codice_fiscale as codice_fiscale,
g.immo_sogg_diritto as idimm_idsog_diritto
FROM catasto_terreni.titg_sogg_dist g
UNION ALL
SELECT
p.identificativo_immobile as identificativo_immobile,
p.tipo_immobile as tipo_immobile,
'persona fisica' as tipo_soggetto,
p.diritto as diritto,
p.quota as quota,
p.identificativo_soggetto_sogp as identificativo_soggetto,
concat(p.cognome, ' ', p.nome) as denominazione,
NULL as codice_amministrativo_sede,
p.data_nascita as data_nascita,
p.codice_fiscale as codice_fiscale,
p.immo_sogp_diritto as idimm_idsog_diritto
FROM catasto_terreni.titp_sogp_dist p
UNION ALL
SELECT
s.identificativo_immobile as identificativo_immobile,
s.tipo_immobile as tipo_immobile,
'partita speciale' as tipo_soggetto,
NULL as diritto,
NULL as quota,
NULL as identificativo_soggetto,
s.descrizione_partita as denominazione,
NULL as codice_amministrativo_sede,
NULL as data_nascita,
NULL as codice_fiscale,
NULL as idimm_idsog_diritto
FROM catasto_terreni.particelle_partite_speciali_terreni s
WHERE s.descrizione_partita NOT IN ('particelle soppresse');

CREATE OR REPLACE VIEW titolarita_partite_speciali_union_ter AS
SELECT row_number() OVER ()::integer AS gid,
ter.identificativo_immobile AS identificativo_immobile_ter,
ter.foglio,
ter.numero,
	CASE -- nuova colonna che permette di assegnare un codice univoco per foglio e particella. Servirà per la relazione con le goemtrie del catasto
	WHEN LENGTH(ter.foglio) = 1 THEN concat(ter.codice_amministrativo, '_000', ter.foglio, '_', ter.numero)
    	ELSE
		(
			CASE
		 	WHEN LENGTH(ter.foglio) = 2 THEN concat(ter.codice_amministrativo, '_00', ter.foglio, '_', ter.numero)
		 	ELSE
				(
					CASE
			 		WHEN LENGTH(ter.foglio) = 3 THEN concat(ter.codice_amministrativo, '_0', ter.foglio, '_', ter.numero)
			 		ELSE
			 			(
							CASE
							WHEN LENGTH(ter.foglio) = 4 THEN concat(ter.codice_amministrativo, '_', ter.foglio, '_', ter.numero)
                					END
						)
					END
				)
			END
		)
	END AS com_fg_plla,
ter.descrizione_qualita AS qualita,
ter.classe,
ter.ettari,
ter.are,
ter.centiare,
t.*,
concat(ter.identificativo_immobile, '_',t.identificativo_immobile,'_', t.identificativo_soggetto, '_', t.diritto) as univoco
FROM ter_1_clean ter
RIGHT JOIN titolarita_partite_speciali_union t ON ter.identificativo_immobile = t.identificativo_immobile;

SELECT ST_SRID(geom) FROM particellare_proprieta_comunale LIMIT 1;

CREATE MATERIALIZED VIEW particellare_proprieta_comunale_4326 AS
SELECT gid, codice_comune, foglio, particella, fg_plla, qualita, classe, ettari, are, centiare, soggetto,
	ST_Transform(geom,4326)
FROM
	particellare_proprieta_comunale
WITH DATA