CREATE TABLE openvpn_certificates (
       depth         integer      NOT NULL,
       digest        varchar(64)  NOT NULL,
       common_name   varchar(64)  NOT NULL,
       organisation  varchar(64)  NOT NULL,
       email         varchar(256) NOT NULL,
       registered    timestamp    DEFAULT CURRENT_TIMESTAMP,
       certid        integer      PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX opevpn_certificates_digest ON openvpn_certificates(digest);

CREATE TABLE openvpn_users (
       username      varchar(32)  NOT NULL,
       password      varchar(128)  NOT NULL,
       activated     timestamp    ,
       deactivated   timestamp    ,
       last_accessed timestamp    ,
       uid           integer      PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_users_uname ON openvpn_users(username);

CREATE TABLE openvpn_usercerts (
       uid           integer      NOT NULL,
       certid        integer      NOT NULL,
       accessprofile integer      ,
       registered    timestamp    DEFAULT CURRENT_TIMESTAMP,
       uicid         integer      PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX openvpn_usercerts_uid ON openvpn_usercerts(uid);
CREATE INDEX openvpn_usercerts_certid ON openvpn_usercerts(certid);

CREATE TABLE openvpn_accesses (
       access_descr   varchar(128) ,
       fw_profile     varchar(64)  NOT NULL,
       accessprofile  integer      PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE openvpn_lastlog (
       uid              integer      ,
       certid           integer      ,
       protocol         varchar(4)   NOT NULL,
       remotehost       varchar(128) NOT NULL,
       remoteport       integer      NOT NULL,
       macaddr          varchar(20)  ,
       vpnipaddr        varchar(32)  NOT NULL,
       vpnipmask        varchar(32)  NOT NULL,
       sessionstatus    integer      NOT NULL DEFAULT 0,
       sessionkey       varchar(128) ,
       login            timestamp    ,
       logout           timestamp    ,
       session_deleted  timestamp    ,
       session_duration timestamp,
       bytes_sent       integer      ,
       bytes_received   integer      ,
       llid             integer      PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_lastlog_sessionkey ON openvpn_lastlog(sessionkey);

CREATE TABLE openvpn_macaddr_history (
       sessionkey     varchar(64)  NOT NULL,
       macaddr        varchar(20)  NOT NULL,
       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
       semaid         integer      PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX openvpn_macaddr_hist_sessionkey ON openvpn_macaddr_history(sessionkey);
CREATE INDEX openvpn_macaddr_hist_macaddr ON openvpn_macaddr_history(macaddr);

CREATE TABLE openvpn_sessions (
       sessionkey     varchar(128) NOT NULL,
       datakey        varchar(256) NOT NULL,
       dataval        text         ,
       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
       sessid         integer      PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX openvpn_sessions_sessionkey ON openvpn_sessions(sessionkey);
CREATE UNIQUE INDEX openvpn_sessions_sess_datakey ON openvpn_sessions(sessionkey, datakey);

CREATE TABLE openvpn_blacklist (
       digest         varchar(64)  ,
       username       varchar(32)  ,
       remoteip       varchar(32)  ,
       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
       last_accessed  timestamp    ,
       blid           integer      PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_blacklist_digest ON openvpn_blacklist(digest);
CREATE UNIQUE INDEX openvpn_blacklist_username ON openvpn_blacklist(username);
CREATE UNIQUE INDEX openvpn_blacklist_remoteip ON openvpn_blacklist(remoteip);

CREATE TABLE openvpn_attempts (
       username       varchar(32)  ,
       digest         varchar(64)  ,
       remoteip       varchar(32)  ,
       attempts       integer      DEFAULT 0,
       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
       last_attempt   timestamp    ,
       atpid          integer      PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_attempts_username ON openvpn_attempts(username);
CREATE UNIQUE INDEX openvpn_attempts_digest ON openvpn_attempts(digest);
CREATE UNIQUE INDEX openvpn_attempts_remoteip ON openvpn_attempts(remoteip);

CREATE TABLE openvpn_sessionkeys (
       sessionseed      varchar(128)  NOT NULL,
       sessionkey       varchar(128)  NOT NULL,
       PRIMARY KEY(sessionkey)
);
CREATE INDEX opevpn_sessionkeys_seed ON openvpn_sessionkeys(sessionseed);

CREATE TABLE openvpn_config (
       datakey        varchar(64)  NOT NULL,
       dataval        text         ,
       cfgid          integer      PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_config_key ON openvpn_config(datakey);

CREATE TABLE eurephia_adminlog (
       uid            integer      NOT NULL,
       interface      char         NOT NULL, -- C-onsole, W-eb
       status         integer      NOT NULL,
       login          timestamp    NOT NULL,
       last_action    timestamp    NOT NULL,
       logout         timestamp    ,
       sessionkey     varchar(128) NOT NULL,
       ealid          integer      PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX eurephia_adminlog_uid ON eurephia_adminlog(uid);
CREATE INDEX eurephia_adminlog_sesskey ON eurephia_adminlog(sessionkey);

CREATE TABLE eurephia_adminaccess (
       uid            integer     NOT NULL,
       interface      char        NOT NULL,  -- C-onsole, W-eb
       access         varchar(64) NOT NULL
);
CREATE INDEX eurephia_adminacc_uid ON eurephia_adminaccess (uid);
CREATE INDEX eurephia_adminacc_uid_intf ON eurephia_adminaccess (uid,interface);