drop view commune_intensity;
drop table commune_scores;
drop table event;
drop table commune;

create table event (
	id serial primary key,
	ads_id_event int8,
	event_date timestamp,
	lat float8,
	lon float8,
	depth float8, 
	zone_name text, 
	n_quests int8,
	mag_pref_type text,
	mag_pref_value float8
);
SELECT AddGeometryColumn ('','event','epicenter',4326,'POINT',2);

CREATE TABLE commune(
	id		serial primary key,
	name		text,
	istat_code	int8,
	lat		float8,
	lon		float8
);
SELECT AddGeometryColumn ('','commune','municipality',4326,'POINT',2);

CREATE TABLE commune_scores(
	id		serial primary key,
	id_event	int8 references event,
	id_commune	int8 references commune,
	intensity_type	int8,
	intensity_value	float8,
	n_valid_quests	int8
);

create or replace view commune_intensity as 
	select cs.*,
		e.ads_id_event, e.event_date, 
		c.municipality
		from commune_scores as cs
		inner join event as e on (cs.id_event = e.id)
		inner join commune as c on (cs.id_commune = c.id)
		order by intensity_value;

create or replace view list_event as
	select * from event order by mag_pref_type;
