-- Create schema on pmm database
-- Assumes roles and database have already been created
-- lpsql pmm -U pmm.dataman -d pmm -f thisfile

CREATE TABLE nodetype (
    type_id smallint NOT NULL,
    name character varying(20) NOT NULL,
    description character(40)
);
GRANT ALL ON nodetype TO "pmm.app";
GRANT SELECT ON nodetype TO "pmm.read";
ALTER TABLE ONLY nodetype
    ADD CONSTRAINT pk_nodetype PRIMARY KEY (type_id);
ALTER TABLE ONLY nodetype
    ADD CONSTRAINT unique_name UNIQUE (name);

CREATE SEQUENCE nodetype_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
GRANT ALL ON nodetype_seq TO "pmm.app";
GRANT SELECT ON nodetype_seq TO "pmm.read";

CREATE TABLE subnet (
    subnet_id smallint NOT NULL,
    gateway character varying(15) NOT NULL,
    description character varying(40),
    target integer
);
GRANT ALL ON subnet TO "pmm.app";
GRANT SELECT ON subnet TO "pmm.read";
ALTER TABLE ONLY subnet
    ADD CONSTRAINT pk_subnet PRIMARY KEY (subnet_id);
ALTER TABLE ONLY subnet
    ADD CONSTRAINT unique_gateway UNIQUE (gateway);

CREATE SEQUENCE subnet_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
GRANT ALL ON subnet_seq TO "pmm.app";
GRANT SELECT ON subnet_seq TO "pmm.read";

CREATE TABLE node (
    node_id integer NOT NULL,
    mac_address character varying(17) NOT NULL,
    date timestamp without time zone NOT NULL,
    type_id smallint DEFAULT 1 NOT NULL,
    subnet_id smallint NOT NULL,
    name character varying(80)
);
GRANT ALL ON node TO "pmm.app";
GRANT SELECT ON node TO "pmm.read";
ALTER TABLE ONLY node
    ADD CONSTRAINT pk_node PRIMARY KEY (node_id);
ALTER TABLE ONLY node
    ADD CONSTRAINT unique_mac_subnet UNIQUE (mac_address, subnet_id);
ALTER TABLE ONLY node
    ADD CONSTRAINT nodetype_node FOREIGN KEY (type_id) REFERENCES nodetype(type_id);
ALTER TABLE ONLY node
    ADD CONSTRAINT subnet_node FOREIGN KEY (subnet_id) REFERENCES subnet(subnet_id);

CREATE SEQUENCE node_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
GRANT ALL ON node_seq TO "pmm.app";
GRANT SELECT ON node_seq TO "pmm.read";

CREATE TABLE recordtime (
    recordtime_id integer NOT NULL,
    datetime timestamp without time zone NOT NULL
);
GRANT ALL ON recordtime TO "pmm.app";
GRANT SELECT ON recordtime TO "pmm.read";
ALTER TABLE ONLY recordtime
    ADD CONSTRAINT pk_recordtime PRIMARY KEY (recordtime_id);

CREATE SEQUENCE recordtime_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
GRANT ALL ON recordtime_seq TO "pmm.app";
GRANT SELECT ON recordtime_seq TO "pmm.read";

CREATE TABLE record (
    record_id bigint NOT NULL,
    node_id integer NOT NULL,
    recordtime_id integer NOT NULL
);
GRANT ALL ON record TO "pmm.app";
GRANT SELECT ON record TO "pmm.read";
ALTER TABLE ONLY record
    ADD CONSTRAINT pk_record PRIMARY KEY (record_id);
ALTER TABLE ONLY record
    ADD CONSTRAINT unique_node_time UNIQUE (node_id, recordtime_id);
CREATE INDEX record_time_idx ON record USING btree (recordtime_id);
ALTER TABLE ONLY record
    ADD CONSTRAINT node_record FOREIGN KEY (node_id) REFERENCES node(node_id);
ALTER TABLE ONLY record
    ADD CONSTRAINT recordtime_record FOREIGN KEY (recordtime_id) REFERENCES recordtime(recordtime_id);

CREATE SEQUENCE record_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
GRANT ALL ON record_seq TO "pmm.app";
GRANT SELECT ON record_seq TO "pmm.read";