-- Copyright (c) 1997-now, NeoNova BV, The Netherlands. All rights Reserved.
--
-- This software is the copyrighted work of NeoNova BV.
-- Use of the software is subject to the general business terms and conditions of NeoNova BV
-- and its related transaction documents like license sheets or other written notices.#
-- Any reproduction or redistribution of the software not in accordance with the license terms
-- is expressly prohibited by law, and may result in severe civil and criminally penalties.
-- Violators will be prosecuted to the maximum extent possible.

CREATE TABLE nas (
  id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

  nasname varchar(50) default NULL,
--HELP_EN_INFO:Name of this nas
--HELP_EN_LONG:The name of this nas.
--HELP_NL_INFO:Naam van deze machine
--HELP_NL_LONG:De volledige naam van dit apparaat.

  shortname varchar(32) NOT NULL default '',
--HELP_EN_INFO:subnet
--HELP_EN_LONG:The subnet this nas runs on.
--HELP_NL_INFO:subnet
--HELP_NL_LONG:Het subnet waar deze machine deel van uit maakt.

  type varchar(30) NOT NULL default 'other'
   CHECK (type IN ('other', 'cisco', 'computone', 'livingston', 'max40xx', 'multitech', 'netserver', 'pathras', 'patton', 'portslave', 'tc', 'usrhiper')),
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

  ports integer NOT NULL default '0',
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

  secret varchar(60) NOT NULL default '',
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

  server varchar(64) NOT NULL default '',
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

  community varchar(50) NOT NULL default '',
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

  description varchar(255) NOT NULL default '',
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

  status varchar(8) NOT NULL default 'active'
   CHECK (status IN ('active', 'inactive', 'deleted')),
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 notes text,
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 PRIMARY KEY  (id)
);

create index nas_nasname_index on nas (nasname);

GRANT UPDATE, SELECT, INSERT, DELETE ON nas TO astium;
GRANT UPDATE, SELECT ON nas_id_seq TO astium;
INSERT INTO nas (nasname, status) VALUES ('empty', 'deleted');

DROP VIEW radcheck;
CREATE VIEW radcheck AS SELECT id, lower(mac) as UserName, 'Cleartext-Password'::text as Attribute, lower(mac) as Value, ':='::text as Op from device_whitelist where company = '3' UNION select id, lower(mac) as UserName, 'Cleartext-Password'::text as Attribute, lower(mac) as Value, ':='::text as Op from phone where company = '3' and status != 'deleted' UNION SELECT id, login as UserName, 'Cleartext-Password'::text as Attribute, pin as Value, ':='::text as Op from contact where company = '3';
GRANT UPDATE, SELECT, INSERT, DELETE ON radcheck TO astium;

DROP VIEW radreply;
CREATE VIEW radreply AS SELECT id, mac as UserName, 'Cleartext-Password'::text as Attribute, mac as Value, ':='::text as Op from device_whitelist where company = '-1';
GRANT UPDATE, SELECT, INSERT, DELETE ON radreply TO astium;

DROP VIEW radusergroup;
CREATE VIEW radusergroup AS SELECT mac as UserName, mac as GroupName, id as Priority from device_whitelist where company = '-1';
GRANT UPDATE, SELECT, INSERT, DELETE ON radusergroup TO astium;


