-- Need to do this or our triggers don't work.  Why do we need to do this?
-- CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;
-- CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";

-- Table for VRF list
CREATE TABLE vrfs (
    vrf varchar(32) DEFAULT '' NOT NULL,
    comment text DEFAULT '' NOT NULL,
    location varchar(4) DEFAULT '' NOT NULL
);

COPY vrfs (vrf, comment, location) FROM stdin;
DEFAULT	Initial default VRF	
\.

ALTER TABLE ONLY vrfs
    ADD CONSTRAINT vrfs_pkey PRIMARY KEY (vrf);

-- Table for tracking active netblock assignments and containers.
-- Note that maskbits is obsolete but left in for a release cycle or two
-- so that legacy dumps can still be loaded, in the unlikely event.
CREATE TABLE allocations (
    cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
    "type" character(2) DEFAULT ''::bpchar,
    city character varying(30) DEFAULT ''::character varying,
    description character varying(128) DEFAULT ''::character varying,
    notes text DEFAULT ''::text,
    maskbits integer DEFAULT 128,
    circuitid character varying(128) DEFAULT ''::character varying,
    createstamp timestamp with time zone DEFAULT now(),
    modifystamp timestamp with time zone DEFAULT now(),
    privdata text DEFAULT ''::text NOT NULL,
    custid character varying(16) DEFAULT ''::character varying,
    swip character(1) DEFAULT 'n'::bpchar,
    vrf text DEFAULT ''::text NOT NULL,
    vlan text DEFAULT ''::text NOT NULL,
    rdns text DEFAULT ''::text NOT NULL,
    parent_id integer DEFAULT 0 NOT NULL,
    master_id integer DEFAULT 0 NOT NULL,
    backup_id integer DEFAULT 0 NOT NULL,
    id serial NOT NULL
);

ALTER TABLE ONLY allocations
    ADD CONSTRAINT allocations_pkey PRIMARY KEY (id);
CREATE UNIQUE INDEX allocations_skey ON allocations (cidr,vrf,type);

-- Customer or POP site cities or locations.
CREATE TABLE cities (
    id serial NOT NULL,
    city character varying(30) DEFAULT ''::character varying NOT NULL,
    routing character(1) DEFAULT 'n'::bpchar NOT NULL
);

ALTER TABLE ONLY cities
    ADD CONSTRAINT cities_pkey PRIMARY KEY (id);

-- Local table for rWHOIS customer data
CREATE TABLE customers (
    custid character varying(16) DEFAULT ''::character varying NOT NULL,
    name character varying(64),
    street character varying(25),
    street2 character varying(25),
    city character varying(30),
    province character(2),
    country character(2),
    pocode character varying(7),
    phone character varying(15),
    tech_handle character varying(50),
    abuse_handle character varying(50),
    admin_handle character varying(50),
    def_rdns character varying(40),
    special text
);

ALTER TABLE ONLY customers
    ADD CONSTRAINT customers_pkey PRIMARY KEY (custid);

-- Flag table for deciding if we can usefully do rDNS RPC calls.
CREATE TABLE dnsavail (
    "zone" cidr,
    "location" varchar(4) DEFAULT '',
    parent_alloc integer NOT NULL
);

ALTER TABLE ONLY dnsavail
    ADD CONSTRAINT dnsavail_pkey PRIMARY KEY ("zone",parent_alloc);

-- Store backup fields in a separate table
CREATE TABLE backuplist (
    backup_id serial NOT NULL,
    bkbrand text,
    bkmodel text,
    bktype text,
    bkport integer,
    bksrc text,
    bkuser text,
    bkvpass text,
    bkepass text,
    bkip inet
);

-- Table for tracking netblocks available for assignment
CREATE TABLE freeblocks (
    cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
    maskbits integer DEFAULT 128,
    city character varying(30) DEFAULT ''::character varying,
    routed character(1) DEFAULT 'n'::bpchar,
    vrf text DEFAULT ''::text NOT NULL,
    parent_id integer DEFAULT 0 NOT NULL,
    master_id integer DEFAULT 0 NOT NULL,
    reserve_for integer DEFAULT 0 NOT NULL,
    id serial NOT NULL
);

ALTER TABLE ONLY freeblocks
    ADD CONSTRAINT freeblocks_pkey PRIMARY KEY (cidr, parent_id);

-- Network nodes - allows finding customers affected by a broken <x> quickly
CREATE TABLE noderef (
    block inet NOT NULL PRIMARY KEY,
    node_id integer
);

CREATE TABLE nodes (
    node_id serial NOT NULL PRIMARY KEY,
    node_type character varying(2),
    node_name character varying(40),
    node_ip inet
);


-- Email notifications on <action>
CREATE TABLE notify (
    action varchar(5) NOT NULL PRIMARY KEY,
    reciplist varchar(500)
);


-- Table for tracking single IP assignments.  Single static IP assignments
-- need somewhat stronger clustering than provided by other types.
CREATE TABLE poolips (
    pool cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
    ip cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
    city character varying(30) DEFAULT ''::character varying NOT NULL,
    "type" character(2) DEFAULT ''::bpchar NOT NULL,
    available character(1) DEFAULT 'y'::bpchar NOT NULL,
    notes text DEFAULT ''::text NOT NULL,
    description character varying(128) DEFAULT ''::character varying NOT NULL,
    circuitid character varying(128) DEFAULT ''::character varying NOT NULL,
    privdata text DEFAULT ''::text NOT NULL,
    custid character varying(16) DEFAULT ''::character varying,
    createstamp timestamp with time zone DEFAULT now(),
    modifystamp timestamp with time zone DEFAULT now(),
    vrf text DEFAULT ''::text NOT NULL,
    vlan text DEFAULT ''::text NOT NULL,
    rdns text DEFAULT ''::text NOT NULL,
    parent_id integer DEFAULT 0 NOT NULL,
    master_id integer DEFAULT 0 NOT NULL,
    backup_id integer DEFAULT 0 NOT NULL,
    id serial NOT NULL,
    CONSTRAINT poolips_available_check CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
);

ALTER TABLE ONLY poolips
    ADD CONSTRAINT poolips_pkey PRIMARY KEY (ip, parent_id);


-- Combined netblock+IP view for searches
CREATE VIEW searchme AS
    SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city,
      allocations.description, allocations.notes, allocations.circuitid, allocations.vrf,
      allocations.vlan, allocations.id, allocations.parent_id, allocations.master_id, 'n' AS available
    FROM allocations
  UNION
    SELECT poolips.ip AS cidr, poolips.custid, poolips."type", poolips.city,
      poolips.description, poolips.notes, poolips.circuitid, poolips.vrf,
      poolips.vlan, poolips.id, poolips.parent_id, poolips.master_id, poolips.available
    FROM poolips;


-- Type list for assignments
CREATE TABLE alloctypes (
    "type" character(2) DEFAULT ''::bpchar NOT NULL,
    listname character varying(40) DEFAULT ''::character varying,
    dispname character varying(40) DEFAULT ''::character varying,
    listorder integer DEFAULT 0,
    def_custid character varying(16) DEFAULT ''::character varying,
    arin_netname character varying(20) DEFAULT 'ISP'::character varying
);

ALTER TABLE ONLY alloctypes
    ADD CONSTRAINT alloctypes_pkey PRIMARY KEY ("type");


-- Initial/standard allocation types.  Update def_custid and arin_netname as appropriate.
COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin;
cn	Customer netblock	Customer netblock	0		ISPCUST
si	Static IP - Server pool	Server pool IP	20		ISP
ci	Static IP - Cable	Static cable IP	21		ISP
di	Static IP - DSL	Static DSL IP	22		ISP
mi	Static IP - Dialup	Static dialup IP	23		ISP
wi	Static IP - Wireless	Static wireless IP	24		ISP
sd	Static Pool - Servers	Server pool	40	5554242	ISP
cd	Static Pool - Cable	Cable pool	41	CBL-BUS	ISP-STATIC-CABLE
dp	Static Pool - DSL	DSL pool	42	DSL-BUS	ISP-STATIC-DSL
mp	Static Pool - Dialup	Static dialup pool	43	DIAL-BUS	ISP-STATIC-DIAL
wp	Static Pool - Wireless	Static wireless pool	44	WL-BUS	ISP-STATIC-WIFI
en	End-use netblock	End-use netblock	100	5554242	ISP
me	Dialup netblock	Dialup netblock	101	DIAL-RES	ISP-DIAL
de	Dynamic DSL block	Dynamic DSL block	102	DSL-RES	ISP-DSL
ce	Dynamic cable block	Dynamic cable block	103	CBL-RES	ISP-CABLE
we	Dynamic WiFi block	Dynamic WiFi block	104	WL-RES	ISP-WIFI
ve	Dynamic VoIP block	Dynamic VoIP block	105	DYN-VOIP	ISP
li	Static IP - LAN/POP	Static LAN/POP IP	190	NOC-VPN	ISP
ai	Static IP - Management	Static management IP	192	NOC-VPN	ISP
bi	Static IP - Wifi CPE	Wifi CPE IP	193		ISP
ld	Static Pool - LAN/POP	LAN pool	195	NOC-VPN	ISP
ad	Static Pool - Management	Management pool	196	NOC-VPN	ISP
bd	Static pool - Wifi CPE	Wifi CPE pool	197		ISP
in	Internal netblock	Internal netblock	199	5554242	ISP
wc	Reserve for CORE/WAN blocks	CORE/WAN blocks	200	5554242	ISP
pc	Reserve for dynamic-route DSL netblocks	Dynamic-route netblocks	201	5554242	ISP-STATIC-DSL
ac	Reserve for ATM	ATM blocks	202	5554242	ISP
fc	Reserve for fibre	Fibre blocks	203	5554242	ISP
wr	CORE/WAN block	CORE/WAN block	220	5554242	ISP
pr	Dynamic-route DSL netblock (cust)	Dynamic-route DSL (cust)	221		ISPCUST
ar	ATM block	ATM block	222		ISP
fr	Fibre	Fibre	223		ISP
rm	Routing aggregation	Routing aggregation	500	5554242	ISP
mm	Master block	Master block	999	5554242	ISP
\.

-- User data table - required for proper ACLs
CREATE TABLE users (
    username character varying(256) NOT NULL,
    "password" character varying(256) DEFAULT ''::character varying,
    acl character varying(256) DEFAULT 'b'::character varying
);

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (username);

-- Default password is admin
INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsAm');


-- Trigger and matching function to update modifystamp on allocations, poolips
CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
    BEGIN
	NEW.modifystamp := ''now'';
	RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
    FOR EACH ROW EXECUTE PROCEDURE up_modtime();

CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
    FOR EACH ROW EXECUTE PROCEDURE up_modtime();