-- PostgreSQL table structure for NEC SMDR (Call Accounting) Data
--
-- $Id: nec_smdr.sql 772 2006-10-28 13:25:28Z jhealy $
-- 
-- Created by Jason Healy <jhealy@suffieldacademy.org>
--
-- See http://web.suffieldacademy.org/ils/netadmin/docs/software/smdr/
-- for more information about this file.
--

--
-- Table for all calls
--

CREATE TABLE calls (
	id SERIAL PRIMARY KEY,
	log_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	start_time TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
	end_time TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
	duration INTERVAL(0) NOT NULL,
	direction CHAR(1) NOT NULL,
	station INTEGER NOT NULL,
	originator VARCHAR,
	terminator VARCHAR,
	pin VARCHAR,
	line INTEGER,
	prefix INTEGER,
	transfer BOOLEAN,
	designation INTEGER,
	route INTEGER,
	route_in INTEGER,
	route_out INTEGER,
	UNIQUE (start_time, end_time, station)
);

CREATE INDEX calls_start_time_index ON calls (start_time);
CREATE INDEX calls_end_time_index ON calls (end_time);
CREATE INDEX calls_duration_index ON calls (duration);
CREATE INDEX calls_station_index ON calls (station);
CREATE INDEX calls_originator_index ON calls (originator);
CREATE INDEX calls_terminator_index ON calls (terminator);
CREATE INDEX calls_pin_index ON calls (pin);

--
-- Link call logs to specific loggers
--

CREATE TABLE loggers (
	calls_id INTEGER REFERENCES calls(id),
	log_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	logger VARCHAR NOT NULL
);