CREATE DATABASE /**IF NOT EXIST*/ iwlocator;

-- USE iwlocator;
\c iwlocator;

-- DROP TABLE locations CASCADE;
CREATE TABLE locations (
	location_id	SERIAL not null,
	city		VARCHAR(30) DEFAULT 'Bremen',
	area		VARCHAR(30) DEFAULT 'University',
	building	VARCHAR(60) DEFAULT 'MZH',
	level		VARCHAR(30) DEFAULT 'Ebene 5',
	room		VARCHAR(30) DEFAULT 'MZH 5380',
	comment		VARCHAR(120) DEFAULT NULL,
	lon		NUMERIC(11,8) DEFAULT '0',
	lat		NUMERIC(11,8) DEFAULT '0',
	elev		NUMERIC(9,4) DEFAULT '0',
	poly 		polygon DEFAULT NULL,
	box_ll		point DEFAULT NULL,
	box_ur		point DEFAULT NULL,
constraint PK_LOCATIONS primary key (location_id)
);
/*
constraint FK_LOCATIONS_POLYGONS foreign key (polygon_id)
	references polygons (polygon_id)
	on delete set null*/

-- DROP TABLE /**IF EXIST*/ ap_info CASCADE;
CREATE TABLE ap_info (
	bssid		CHAR(17) not null,
	essid		VARCHAR(32) not null,
	location_id	INT DEFAULT NULL,
	positioning	INT DEFAULT 1,
constraint PK_AP_INFO primary key (bssid),
constraint FK_AP_INFO_LOC foreign key (location_id)
    references locations (location_id)
    on delete set null
);


	
-- To be able to update we drop
--DROP TABLE /**IF EXIST*/ range_info;
CREATE TABLE range_info (
	location_id	INT	not null,
	bssid		CHAR(17) not null,
	first		numeric(6,2) not null,
	second		numeric(6,2) default null,
constraint PK_RANGE_INFO primary key (location_id,bssid),
constraint FK_RANGE_LOCATION_ID foreign key (location_id)
    references locations (location_id)
    on delete cascade,
constraint FK_RANGE_AP_ID foreign key (bssid)
    references ap_info (bssid)
    on delete cascade
);

CREATE index INDEX_RANGE_INFO_LOC on range_info (location_id);
CREATE index INDEX_RANGE_INFO_APS on range_info (bssid);

--DROP TABLE /**IF EXIST*/ location_graph;
CREATE TABLE location_graph (
	location_id1	INT not null,
	location_id2	INT not null,
constraint PK_LOCATION_GRAPH primary key (location_id1,location_id2),
constraint FK_LOCATION_LOCATION1 foreign key (location_id1)
    references locations (location_id)
    on delete cascade,
constraint FK_LOCATION_LOCATION2 foreign key (location_id2)
    references locations (location_id)
    on delete cascade
);

CREATE index INDEX_LOCATION_GRAPH_LOC1 on location_graph (location_id1);
CREATE index INDEX_LOCATION_GRAPH_LOC2 on location_graph (location_id2);

--DROP TABLE calibrations;

CREATE TABLE calibrations (
	card_bssid	CHAR(17) not null,
	ap_bssid	CHAR(17) not null,
	level		NUMERIC(16,8) not null,
constraint PK_CALIBRATIONS primary key( card_bssid, ap_bssid )
);