CREATE TABLE area (
areaaddr                 INT8 DEFAULT 0 NOT NULL,
descrip                  VARCHAR(80) NOT NULL,
areaindex                SERIAL,
customer                 INT4 DEFAULT 0 NOT NULL,
                 PRIMARY KEY (areaindex)
);


CREATE UNIQUE INDEX area_customer ON area (customer, descrip);


CREATE UNIQUE INDEX area_areaaddr ON area (areaaddr, customer);


CREATE TABLE auditlog (
userid                   VARCHAR(40),
action                   VARCHAR(254) NOT NULL,
dt                       TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);


CREATE INDEX auditlog_dt ON auditlog (dt);


CREATE TABLE base (
baseaddr                 INT8 DEFAULT 0 NOT NULL,
subnetsize               INT8 DEFAULT 0 NOT NULL,
descrip                  VARCHAR(80) NOT NULL,
baseindex                SERIAL,
admingrp                 VARCHAR(40) NOT NULL,
customer                 INT4 DEFAULT 0 NOT NULL,
lastmod                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
userid                   VARCHAR(40),
baseopt                  INT8 DEFAULT 0,
swipmod                  TIMESTAMP,
                 PRIMARY KEY (baseindex)
);


CREATE UNIQUE INDEX base_baseaddr ON base (baseaddr, customer);


CREATE INDEX base_customer ON base (customer);


CREATE INDEX base_admingrp ON base (admingrp);


CREATE TABLE baseadd (
baseindex                INT8 DEFAULT 0 NOT NULL,
info                     TEXT,
infobin                  BYTEA,
infobinfn                VARCHAR(255),
                 PRIMARY KEY (baseindex)
);


CREATE TABLE custinfo (
customer                 INT4 DEFAULT 0 NOT NULL,
org                      VARCHAR(100),
street                   VARCHAR(255),
city                     VARCHAR(80),
state                    VARCHAR(2),
zipcode                  VARCHAR(10),
cntry                    VARCHAR(2),
maint                    VARCHAR(80),
nichandl                 VARCHAR(80),
lname                    VARCHAR(80),
fname                    VARCHAR(80),
mname                    VARCHAR(80),
torg                     VARCHAR(100),
tstreet                  VARCHAR(255),
tcity                    VARCHAR(80),
tstate                   VARCHAR(2),
tzipcode                 VARCHAR(10),
tcntry                   VARCHAR(2),
phne                     VARCHAR(20),
mbox                     VARCHAR(100),
                 PRIMARY KEY (customer)
);


CREATE TABLE custadd (
customer                 INT4 DEFAULT 0 NOT NULL,
info                     TEXT,
infobinfn                VARCHAR(255),
                 PRIMARY KEY (customer)
);


CREATE TABLE customer (
custdescrip              VARCHAR(80) NOT NULL,
customer                 SERIAL,
crm                      VARCHAR(20),
admingrp                 VARCHAR(40) NOT NULL,
                 PRIMARY KEY (customer)
);


CREATE UNIQUE INDEX customer_custdescrip ON customer (custdescrip);


CREATE INDEX customer_crm ON customer (crm);


CREATE INDEX customer_admingrp ON customer (admingrp);


CREATE TABLE grp (
grpdescrip               VARCHAR(80) NOT NULL,
grp                      VARCHAR(40) NOT NULL,
createcust               VARCHAR(1) DEFAULT 'N' NOT NULL,
resaddr                  INT4 DEFAULT 0,
grpopt                   INT8 DEFAULT 0 NOT NULL,
                 PRIMARY KEY (grp)
);


CREATE UNIQUE INDEX grp_grpdescrip ON grp (grpdescrip);


CREATE TABLE ipaddr (
ipaddr                   INT8 DEFAULT 0 NOT NULL,
userinf                  VARCHAR(80),
location                 VARCHAR(80),
telno                    VARCHAR(15),
descrip                  VARCHAR(80),
hname                    VARCHAR(100),
macaddr                  VARCHAR(12),
baseindex                INT8 DEFAULT 0 NOT NULL,
lastmod                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
lastpol                  TIMESTAMP,
userid                   VARCHAR(40),
                 PRIMARY KEY (ipaddr, baseindex)
);


CREATE INDEX ipaddr_baseindex ON ipaddr (baseindex);


CREATE TABLE ipaddradd (
ipaddr                   INT8 DEFAULT 0 NOT NULL,
baseindex                INT8 DEFAULT 0 NOT NULL,
info                     TEXT,
infobin                  BYTEA,
infobinfn                VARCHAR(255),
                 PRIMARY KEY (ipaddr, baseindex)
);


CREATE INDEX ipaddradd_baseindex ON ipaddradd (baseindex);


CREATE TABLE ipaddrlnk (
ipaddr                   INT8 DEFAULT 0 NOT NULL,
baseindex                INT8 DEFAULT 0 NOT NULL,
customer                 INT4 DEFAULT 0 NOT NULL,
dstipaddr                INT8 DEFAULT 0 NOT NULL,
dstcustomer              INT4 DEFAULT 0 NOT NULL
);


CREATE UNIQUE INDEX ipaddrlnk_uniq ON ipaddrlnk (baseindex, ipaddr, customer, dstipaddr, dstcustomer);


CREATE INDEX ipaddrlnk_dst ON ipaddrlnk (dstipaddr, dstcustomer);


CREATE INDEX ipaddrlnk_src ON ipaddrlnk (ipaddr, customer);


CREATE TABLE requestip (
requestindex             SERIAL,
customer                 INT4 DEFAULT 0 NOT NULL,
requestdesc              VARCHAR(80),
userinf                  VARCHAR(80),
location                 VARCHAR(80),
telno                    VARCHAR(15),
descrip                  VARCHAR(80),
hname                    VARCHAR(100),
macaddr                  VARCHAR(12),
lastmod                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
info                     TEXT,
                 PRIMARY KEY (requestindex)
);


CREATE UNIQUE INDEX requestip_desc ON requestip (customer, requestdesc);


CREATE TABLE netrange (
rangeaddr                INT8 DEFAULT 0 NOT NULL,
rangesize                INT8 DEFAULT 0 NOT NULL,
descrip                  VARCHAR(80) NOT NULL,
rangeindex               SERIAL,
areaindex                INT8 DEFAULT 0 NOT NULL,
customer                 INT4 DEFAULT 0 NOT NULL,
                 PRIMARY KEY (rangeindex)
);


CREATE INDEX range_rangeaddr ON netrange (rangeaddr, customer);


CREATE UNIQUE INDEX range_customer ON netrange (customer, descrip);


CREATE INDEX range_areaindex ON netrange (areaindex);


CREATE TABLE revdns (
customer                 INT4 DEFAULT 0 NOT NULL,
hname                    VARCHAR(100) NOT NULL,
ipaddr                   VARCHAR(15) NOT NULL,
horder                   SMALLINT DEFAULT 0 NOT NULL
);


CREATE INDEX revdns_customer ON revdns (customer);


CREATE TABLE schema (
version                  INT4 DEFAULT 0 NOT NULL
);


CREATE TABLE users (
userid                   VARCHAR(40) NOT NULL,
userdescrip              VARCHAR(80) NOT NULL,
useremail                VARCHAR(64),
password                 VARCHAR(40) NOT NULL,
                 PRIMARY KEY (userid)
);


CREATE TABLE usergrp (
userid                   VARCHAR(40) NOT NULL,
grp                      VARCHAR(40) NOT NULL
);


CREATE UNIQUE INDEX usergrp_userid ON usergrp (userid, grp);


CREATE INDEX usergrp_grp ON usergrp (grp);


CREATE TABLE bounds (
boundsaddr               INT8 DEFAULT 0 NOT NULL,
boundssize               INT8 DEFAULT 0 NOT NULL,
grp                      VARCHAR(40) NOT NULL
);


CREATE INDEX bounds_grp ON bounds (grp);


CREATE INDEX bounds_boundsaddr ON bounds (boundsaddr);


CREATE TABLE fwdzone (
data_id                  SERIAL,
domain                   VARCHAR(254) NOT NULL,
createmod                DATE,
lastmod                  TIMESTAMP,
regmod                   DATE,
expiremod                TIMESTAMP,
engineer                 VARCHAR(254),
error_message            VARCHAR(254),
responsiblemail          VARCHAR(64),
serialdate               VARCHAR(8) NOT NULL,
serialnum                INT4 DEFAULT 0,
ttl                      INT4 DEFAULT 0,
refresh                  INT4 DEFAULT 0,
retry                    INT4 DEFAULT 0,
expire                   INT4 DEFAULT 0,
minimum                  INT4 DEFAULT 0,
userid                   VARCHAR(40),
lastexp                  TIMESTAMP,
slaveonly                VARCHAR(1) DEFAULT 'N' NOT NULL,
zonefilepath1            VARCHAR(254),
zonefilepath2            VARCHAR(254),
customer                 INT4 DEFAULT 0 NOT NULL,
admingrp                 VARCHAR(40),
                 PRIMARY KEY (data_id)
);


CREATE INDEX fwdzone_customer ON fwdzone (customer);


CREATE INDEX fwdzone_domain ON fwdzone (domain);


CREATE TABLE fwdzoneadd (
customer                 INT4 DEFAULT 0 NOT NULL,
data_id                  INT8 DEFAULT 0 NOT NULL,
info                     TEXT,
infobinfn                VARCHAR(255)
);


CREATE UNIQUE INDEX fwdzoneadd_customer ON fwdzoneadd (customer, data_id);


CREATE TABLE fwdzonerec (
recidx                   SERIAL,
data_id                  INT8 DEFAULT 0 NOT NULL,
lastmod                  TIMESTAMP,
host                     VARCHAR(254) NOT NULL,
recordtype               VARCHAR(5) NOT NULL,
ip_hostname              VARCHAR(254) NOT NULL,
error_message            VARCHAR(254),
sortorder                INT4 DEFAULT 0 NOT NULL,
userid                   VARCHAR(40) NOT NULL,
customer                 INT4 DEFAULT 0 NOT NULL,
                 PRIMARY KEY (recidx)
);


CREATE INDEX fwdzonerec_data_id ON fwdzonerec (data_id);


CREATE INDEX fwdzonerec_sortorder ON fwdzonerec (sortorder);


CREATE INDEX fwdzonerec_customer ON fwdzonerec (customer);


CREATE TABLE fwddns (
id                       INT8 DEFAULT 0 NOT NULL,
hname                    VARCHAR(100) NOT NULL,
horder                   SMALLINT DEFAULT 0 NOT NULL
);


CREATE INDEX fwddns_id ON fwddns (id);


CREATE TABLE zones (
id                       SERIAL,
zoneip                   INT8 DEFAULT 0 NOT NULL,
zonesize                 INT8 DEFAULT 0 NOT NULL,
zone                     VARCHAR(254) NOT NULL,
error_message            VARCHAR(254),
lastmod                  TIMESTAMP,
serialdate               VARCHAR(8) NOT NULL,
serialnum                INT4 DEFAULT 0,
ttl                      INT4 DEFAULT 0,
refresh                  INT4 DEFAULT 0,
retry                    INT4 DEFAULT 0,
expire                   INT4 DEFAULT 0,
minimum                  INT4 DEFAULT 0,
userid                   VARCHAR(40),
lastexp                  TIMESTAMP,
slaveonly                VARCHAR(1) DEFAULT 'N' NOT NULL,
zonefilepath1            VARCHAR(254),
zonefilepath2            VARCHAR(254),
responsiblemail          VARCHAR(64),
customer                 INT4 DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);


CREATE INDEX zones_customer ON zones (customer);


CREATE INDEX zones_zoneip ON zones (zoneip);


CREATE TABLE zonedns (
id                       INT8 DEFAULT 0 NOT NULL,
hname                    VARCHAR(100) NOT NULL,
horder                   SMALLINT DEFAULT 0 NOT NULL
);


CREATE INDEX zonedns_id ON zonedns (id);


BEGIN;


INSERT INTO schema (version) VALUES (22);


COMMIT;