CREATE TABLE product_versions (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
actual BOOLEAN DEFAULT false, -- is this the actual version?
version_major INT NOT NULL,
version_minor INT NOT NULL,
version_patchlevel INT NOT NULL,
version_string VARCHAR(32) DEFAULT NULL,
description TEXT DEFAULT NULL, -- information to be dislayed to the user
major BOOLEAN DEFAULT false, -- whether this version was major
beta BOOLEAN DEFAULT false, -- is this a beta version
notification BOOLEAN DEFAULT false, -- whether to show/hide information about this update
force_display BOOLEAN DEFAULT false, -- force this update info? (only the newest such to be displayed)
release_date DATETIME
);

CREATE TABLE device_types (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(64) NOT NULL
);

CREATE TABLE agents ( -- list of agents available for installation
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
description VARCHAR(256),
minimal_version VARCHAR(32),
dir_name VARCHAR(32),
package_name VARCHAR(64),
dev_type INT,
show_in_shortcut BOOLEAN DEFAULT true,
FOREIGN KEY (dev_type) REFERENCES device_types(id)
);

CREATE TABLE agent_versions ( -- list of all known agent versions (both available and running on devices)
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
dev_type INT DEFAULT NULL,
version_major INT NOT NULL,
version_minor INT NOT NULL,
version_patchlevel INT NOT NULL,
actual BOOLEAN DEFAULT false,
FOREIGN KEY (dev_type) REFERENCES device_types(id)
);

CREATE TRIGGER delban_5_agent_versions
BEFORE DELETE ON agent_versions
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delete not allowed on simpletable';

CREATE TABLE tunnel_op (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL
);

CREATE TABLE tunnel_status (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
description VARCHAR(64) DEFAULT NULL
);

CREATE TABLE tunnel_op_status_map (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
operation_id INT NOT NULL,
status_id INT NOT NULL,
FOREIGN KEY (operation_id) REFERENCES tunnel_op(id),
FOREIGN KEY (status_id) REFERENCES tunnel_status(id)
);

CREATE TABLE product_flags_license_status (
id INT NOT NULL PRIMARY KEY,
description VARCHAR(128)
);

CREATE TABLE product_flags (
id ENUM ('user_devchg_version', 'license_status', 'buildtag', 'about_info') NOT NULL PRIMARY KEY,
version INT,
description TEXT DEFAULT NULL
);

CREATE TABLE device_os_types (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL,
dev_type INT DEFAULT NULL,
directory_name VARCHAR(64) DEFAULT NULL,
file_extension VARCHAR(32) DEFAULT NULL,
FOREIGN KEY (dev_type) REFERENCES device_types(id)
);

CREATE TABLE device_os_versions (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
os_type INT,
description VARCHAR(32),
available BOOLEAN DEFAULT false,
approved BOOLEAN DEFAULT false,
keep BOOLEAN DEFAULT false,
FOREIGN KEY (os_type) REFERENCES device_os_types(id)
);

CREATE UNIQUE INDEX device_os_index
ON device_os_versions (os_type, description);

CREATE TABLE fw_firmware_names (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fwfname VARCHAR(64)
);

CREATE TRIGGER delban_1_fwfname
BEFORE DELETE ON fw_firmware_names
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delete not allowed on simpletable';

CREATE TABLE fw_autoupgrade_policy (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64)
);

CREATE TABLE device_os_autoupgrade_policy (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64)
);

CREATE TABLE typetable_value_types (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(16)
);

CREATE TABLE agent_profiles( -- profiles for agents, configuring their behavior
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
profile_name VARCHAR(64),
int_ping INT DEFAULT 10,
int_regular INT DEFAULT 260,
int_debug INT DEFAULT 10,
int_fallback INT DEFAULT 60,
int_fallback_debug INT DEFAULT 30,
smallcycles INT DEFAULT 5,
connfail_tolerance INT DEFAULT 5,
regprogress INT DEFAULT 100,
fw_autoupgrade INT DEFAULT 5,
os_autoupgrade INT DEFAULT 5,
system BOOLEAN DEFAULT FALSE,
FOREIGN KEY (fw_autoupgrade) REFERENCES fw_autoupgrade_policy(id),
FOREIGN KEY (os_autoupgrade) REFERENCES device_os_autoupgrade_policy(id)
);

CREATE TABLE auth_profiles( -- authentication profiles describing how to auth when connecting using ssh to a device that has this auth_profile assigned
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
profile_name VARCHAR(64) NOT NULL UNIQUE,
username VARCHAR(64) NOT NULL,
ssh_pwd VARBINARY(64) DEFAULT NULL,
pwd_salt INT DEFAULT NULL,
pwd_pepper INT DEFAULT NULL,
pwd_chilli INT DEFAULT NULL,
pwd_version INT DEFAULT NULL,
ssh_key VARBINARY(4096) DEFAULT NULL,
key_salt INT DEFAULT NULL,
key_pepper INT DEFAULT NULL,
key_chilli INT DEFAULT NULL,
key_version INT DEFAULT NULL
);

CREATE TABLE agent_device_type_map (
id INT NOT NULL AUTO_INCREMENT,
agent_id INT NOT NULL,
device_type_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (agent_id) REFERENCES agents(id),
FOREIGN KEY (device_type_id) REFERENCES device_types(id)
);

CREATE TABLE liveness_time (
id SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
description VARCHAR(32)
);

CREATE TABLE liveness_misscount (
id SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
description VARCHAR(32)
);

CREATE TABLE fw_freshness (
id SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
description VARCHAR(32)
);

CREATE TABLE device_timeskew_time (
id SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
description VARCHAR(32)
);

CREATE TABLE user_modules ( -- list of all known user modules (router apps)
id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
versionstring VARCHAR(64) DEFAULT NULL,
internal BOOLEAN DEFAULT false,
available BOOLEAN DEFAULT false,
approved BOOLEAN DEFAULT false,
hidden BOOLEAN DEFAULT false
);

CREATE TRIGGER delban_7_ums
BEFORE DELETE ON user_modules
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delete not allowed on simpletable';

CREATE TABLE config_profiles (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -- profile ID
name VARCHAR(64), -- profile name; NULL for profiles from devices (default or automatic)
automatic SMALLINT UNSIGNED DEFAULT NULL, -- if specified, it specifies device to which this profile belongs
defaults BOOLEAN DEFAULT false, -- a defaults profile for OS version * fwfname; not editable
directive BOOLEAN DEFAULT true, -- true: profile is directing values to device; false: profile is updated based on profile
master SMALLINT UNSIGNED DEFAULT NULL, -- if specified, means there is a master device from which the profile is updated (overrides directive)
os_version INT DEFAULT NULL, -- if default, this specifies which OS ver is this for; otherwise it specifies base OS V (full profile)
fwfname INT DEFAULT NULL, -- if default profile, sets for which FWFNAME it is; otherwise like ^^^
deleted BOOLEAN DEFAULT false, -- set row (profile) as deleted by user
FOREIGN KEY (os_version) REFERENCES device_os_versions(id),
FOREIGN KEY (fwfname) REFERENCES fw_firmware_names(id)
);

CREATE TRIGGER delban_4_config_profiles
BEFORE DELETE ON config_profiles
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delete not allowed on simpletable';

CREATE TABLE config_profile_profile_map (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
embracing INT,
included INT,
FOREIGN KEY (embracing) REFERENCES config_profiles(id) ON DELETE CASCADE,
FOREIGN KEY (included) REFERENCES config_profiles(id) ON DELETE CASCADE
);

CREATE TABLE config_key_groups (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
group_name VARCHAR(64) NOT NULL
);

CREATE TRIGGER delban_3_config_groups
BEFORE DELETE ON config_key_groups
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delete not allowed on simpletable';

CREATE TABLE config_keys ( -- list of all known configuration profile keys
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
key_name VARCHAR(64) NOT NULL,
group_name SMALLINT UNSIGNED NOT NULL,
value_type SMALLINT UNSIGNED NOT NULL DEFAULT 3,
FOREIGN KEY (value_type) REFERENCES typetable_value_types(id),
FOREIGN KEY (group_name) REFERENCES config_key_groups(id)
);

CREATE TRIGGER delban_2_config_keys
BEFORE DELETE ON config_keys
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delete not allowed on simpletable';

CREATE TABLE config_values ( -- configuration profile values
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
profile_id INT NOT NULL,
key_name SMALLINT UNSIGNED NOT NULL,
key_value TEXT DEFAULT NULL,
FOREIGN KEY (key_name) REFERENCES config_keys(id),
FOREIGN KEY (profile_id) REFERENCES config_profiles(id) ON DELETE CASCADE
);

CREATE TABLE device_models ( -- holds different device models description, daemon - check + write once, GUI for display only, never delete
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
model VARCHAR(128)
);

CREATE TABLE devices ( -- main devices table, gathering basic information regarding devices
id_device SMALLINT UNSIGNED PRIMARY KEY, -- device numeric ID (1-65536)
devid VARCHAR(4) DEFAULT NULL, -- numeric ID converted into 4 characters
device_token VARCHAR(32) DEFAULT NULL,
cert_token VARCHAR(32) DEFAULT NULL,
deleted BOOLEAN DEFAULT false, -- flag to set if device deleted during runtime. Device is purged from DB on next daemon startup
name VARCHAR(32) DEFAULT NULL, -- custom name for a device
dev_type INT,
snmp_subdevtype INT DEFAULT NULL, -- device type guessed out of SNMP data
os_ver INT,
os_ver_desired INT,
macaddr VARCHAR(12) DEFAULT NULL,
serno VARCHAR(32) DEFAULT NULL,
active_sim VARCHAR(32) DEFAULT NULL, -- text from device saying which SIM card is active
fwfname INT NOT NULL DEFAULT 1,
hostname VARCHAR(64) DEFAULT NULL,
model INT DEFAULT NULL,
snmpname VARCHAR(64) DEFAULT NULL,
hostname_desired VARCHAR(64) DEFAULT NULL,
config_profile INT DEFAULT NULL, -- this is governing profile, 0 (NULL) by default
automatic_profile INT DEFAULT NULL, -- this is automatic profile ID (every device has automatic profile if config values are available)
uuid VARCHAR(16) DEFAULT NULL,
registered DATETIME DEFAULT CURRENT_TIMESTAMP,
gui_version INT DEFAULT 0,
agent_version INT DEFAULT NULL,
auth_profile INT DEFAULT NULL,
agent_profile INT DEFAULT 1,
ignore_alldev_alerts BOOLEAN DEFAULT FALSE,
machine_id VARCHAR(180),
FOREIGN KEY (dev_type) REFERENCES device_types(id),
FOREIGN KEY (snmp_subdevtype) REFERENCES device_types(id),
FOREIGN KEY (os_ver) REFERENCES device_os_versions(id),
FOREIGN KEY (os_ver_desired) REFERENCES device_os_versions(id),
FOREIGN KEY (config_profile) REFERENCES config_profiles(id),
FOREIGN KEY (automatic_profile) REFERENCES config_profiles(id),
FOREIGN KEY (fwfname) REFERENCES fw_firmware_names(id),
FOREIGN KEY (agent_version) REFERENCES agent_versions(id),
FOREIGN KEY (agent_profile) REFERENCES agent_profiles(id),
FOREIGN KEY (auth_profile) REFERENCES auth_profiles(id),
FOREIGN KEY (model) REFERENCES device_models(id)
);

ALTER TABLE config_profiles ADD FOREIGN KEY (automatic) REFERENCES devices(id_device) ON DELETE SET NULL;

ALTER TABLE config_profiles ADD FOREIGN KEY (master) REFERENCES devices(id_device) ON DELETE SET NULL;

CREATE TABLE snmp_devices(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
address VARCHAR(128) UNIQUE DEFAULT NULL,
community_string VARCHAR(64) DEFAULT NULL,
protocol_version INT DEFAULT 2,
last_success DATETIME DEFAULT NULL,
last_failure DATETIME DEFAULT NULL,
errors_since_success INT DEFAULT 0,
registered_as SMALLINT UNSIGNED DEFAULT NULL,
registered_as_devid VARCHAR(4) DEFAULT NULL,
devtoken_ag_copy VARCHAR(32) DEFAULT NULL
);

CREATE TABLE sim_cards(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
iccid VARCHAR(32) UNIQUE NOT NULL check(iccid RLIKE '^[0-9]{13,20}$'), -- primary and unique SIM card identifier (worldwide-unique)
msisdn VARCHAR(32) UNIQUE DEFAULT NULL check(msisdn RLIKE '^[0-9]{3,17}$'), -- telephone number
label VARCHAR(64) DEFAULT NULL, -- user label of the SIM card
device_user SMALLINT UNSIGNED, -- id of the device if a device was targeted
device_last_seen SMALLINT UNSIGNED, -- id of the device if a device was targeted
last_seen_date DATETIME DEFAULT NULL, -- timestamp of last change of the device_last_seen
FOREIGN KEY (device_user) REFERENCES devices(id_device) ON DELETE SET NULL,
FOREIGN KEY (device_last_seen) REFERENCES devices(id_device) ON DELETE SET NULL
);

CREATE TABLE sms_templates( -- list of all sms templates
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
target_any BOOLEAN DEFAULT FALSE, -- can this sms template be sent to any MSISDN ?
customizable BOOLEAN DEFAULT FALSE, -- can user customize the SMS text before sending?
text VARCHAR(160), -- sms template text
label VARCHAR(160),
advantech_cfg BOOLEAN DEFAULT FALSE, -- is this SMS Advantech configuration SMS ?
system BOOLEAN DEFAULT FALSE
);

CREATE TABLE sms_history(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
sms_id INT, -- which sms template was used
custom_text VARCHAR(160) DEFAULT NULL, -- if text was customized, stores the actual SMS text
sent DATETIME DEFAULT CURRENT_TIMESTAMP, -- automatically set timestamp (when was it sent)
id_device SMALLINT UNSIGNED, -- id of the device if a device was targeted
destination VARCHAR(20) DEFAULT NULL, -- destination number if user provided arbitrary destination
FOREIGN KEY (sms_id) REFERENCES sms_templates(id) ON DELETE SET NULL,
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE SET NULL
);

CREATE TABLE device_user_modules ( -- mapping table to show which UM is installed on which device
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
id_device SMALLINT UNSIGNED NOT NULL, -- id of the device the UM is installed for
module SMALLINT UNSIGNED NOT NULL,
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE,
FOREIGN KEY (module) REFERENCES user_modules(id)
);

DROP TABLE IF EXISTS tunnels; -- table for tunnels (WEB to HTTPS/443 SSH to 22 port / localhost of the device)
CREATE TABLE tunnels (
id INT NOT NULL PRIMARY KEY,
id_device SMALLINT UNSIGNED DEFAULT NULL, -- id of the device where does the tunnel lead to, INTERNAL
url VARCHAR(128) DEFAULT NULL, -- the subdomain part of the URL, e.g. https://<this_part>.<regular DG server address>
device_port INT DEFAULT 0, -- port which does the tunnel to the device listens at - AUTOCFG 127.0.0.1:<this_port>; INTERNAL
request_time DATETIME DEFAULT NULL, -- timestamp of the request
setup_time DATETIME DEFAULT NULL, -- when the tunnel was set up
expected_time DATETIME DEFAULT NULL, -- when is the device expected to process the pending operation
destroy_time DATETIME DEFAULT NULL, -- last-resort destruction time
last_transfer DATETIME DEFAULT NULL, -- statistics
status INT DEFAULT 0, -- last known status of the tunnel
requested_op INT DEFAULT 0, -- operation requested by user
ssh BOOLEAN DEFAULT FALSE, -- is this an ssh tunnel request?
proxy_url VARCHAR(128) DEFAULT NULL, -- information for the user how to set the SSH proxy. Useful for ssh tunnels only.
password VARCHAR(16) DEFAULT NULL, -- if set, it is a password protecting access to the ssh web gateway (webssh or sshwifty)
FOREIGN KEY (status) REFERENCES tunnel_status(id),
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE,
FOREIGN KEY (requested_op) REFERENCES tunnel_op(id)
);

CREATE TABLE device_sync_statuses (
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(32)
);

CREATE TABLE device_stats ( -- various device data (primarily the volatile ones)
-- when adding field here, update mapping table to device types below
id_device SMALLINT UNSIGNED PRIMARY KEY,
livetime_class SMALLINT UNSIGNED DEFAULT 1,
livemiss_class SMALLINT UNSIGNED DEFAULT 1,
timeskew INT DEFAULT 0,
voltage DOUBLE DEFAULT NULL,
temperature INT DEFAULT NULL,
sync SMALLINT UNSIGNED DEFAULT 1,
uptime INT UNSIGNED DEFAULT 0, -- device uptime (in seconds)
mem_total INT UNSIGNED DEFAULT 0, -- total device memory in KiB
mem_avail INT UNSIGNED DEFAULT 0, -- available memory (free+swap) in KiB
processes SMALLINT UNSIGNED DEFAULT 0, -- number of running processes
root_avail INT UNSIGNED DEFAULT 0, -- available space on root FS - in MiBs
loadavg1 DOUBLE DEFAULT 0, -- last minute average load
loadavg5 DOUBLE DEFAULT 0, -- last 5 minutes average load
loadavg15 DOUBLE DEFAULT 0, -- last 15 minutes average load
sim_ipaddr VARCHAR(16) DEFAULT NULL,
sim_ipmask SMALLINT UNSIGNED DEFAULT NULL,
sim_ip6addr VARCHAR(48) DEFAULT NULL,
sim_ip6pref SMALLINT UNSIGNED DEFAULT NULL,
last_reboot TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- timestamp of last reboot
reboot_counter INT UNSIGNED DEFAULT 0, -- total # of reboots
signal_strength SMALLINT DEFAULT NULL, -- strength of signal, typically between -0 .. -110
signal_quality SMALLINT DEFAULT NULL, -- signal quality, typically between -0 .. -30
signal_csq SMALLINT DEFAULT NULL, -- another signal strength/quality metric
signal_rssi SMALLINT DEFAULT NULL, -- another signal strength/quality metric
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE,
FOREIGN KEY (livetime_class) REFERENCES liveness_time(id),
FOREIGN KEY (livemiss_class) REFERENCES liveness_misscount(id),
FOREIGN KEY (sync) REFERENCES device_sync_statuses(id)
);

CREATE TABLE device_stats_device_type_map (
id_type INT,
livetime_class BOOLEAN DEFAULT false,
livemiss_class BOOLEAN DEFAULT false,
timeskew BOOLEAN DEFAULT false,
voltage BOOLEAN DEFAULT false,
temperature BOOLEAN DEFAULT false,
sync BOOLEAN DEFAULT false,
uptime BOOLEAN DEFAULT false,
mem_total BOOLEAN DEFAULT false,
mem_avail BOOLEAN DEFAULT false,
processes BOOLEAN DEFAULT false,
root_avail BOOLEAN DEFAULT false,
loadavg1 BOOLEAN DEFAULT false,
loadavg5 BOOLEAN DEFAULT false,
loadavg15 BOOLEAN DEFAULT false,
last_reboot BOOLEAN DEFAULT false,
reboot_counter BOOLEAN DEFAULT false,
FOREIGN KEY (id_type) REFERENCES device_types(id)
);

CREATE TABLE device_netdevs( -- network interfaces of the devices
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_device SMALLINT UNSIGNED,
version INT DEFAULT 0,
label VARCHAR(32),
active BOOLEAN DEFAULT true,
is_cellular BOOLEAN DEFAULT FALSE,
is_linkable BOOLEAN DEFAULT TRUE,
mac VARCHAR(20) DEFAULT NULL,
ipaddr VARCHAR(16) DEFAULT NULL,
ipmask SMALLINT UNSIGNED DEFAULT NULL,
ip6addr VARCHAR(48) DEFAULT NULL,
ip6pref SMALLINT UNSIGNED DEFAULT NULL,
collisions BIGINT UNSIGNED DEFAULT 0,
rx_packets BIGINT UNSIGNED DEFAULT 0,
rx_bytes BIGINT UNSIGNED DEFAULT 0,
rx_errors BIGINT UNSIGNED DEFAULT 0,
tx_packets BIGINT UNSIGNED DEFAULT 0,
tx_bytes BIGINT UNSIGNED DEFAULT 0,
tx_errors BIGINT UNSIGNED DEFAULT 0,
time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE
);

CREATE TABLE netdevs_daily_traffic (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
netdev INT NOT NULL,
time DATE DEFAULT CURRENT_DATE,
tx_bytes DECIMAL(30,0) UNSIGNED DEFAULT 0,
tx_bytes_totals DECIMAL(30,0) UNSIGNED DEFAULT 0,
rx_bytes DECIMAL(30,0) UNSIGNED DEFAULT 0,
rx_bytes_totals DECIMAL(30,0) UNSIGNED DEFAULT 0,
FOREIGN KEY (netdev) REFERENCES device_netdevs(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX netdev_daily_index
ON netdevs_daily_traffic (netdev, time);

CREATE TABLE netdevs_monthly_traffic (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
netdev INT NOT NULL,
time DATE DEFAULT CURRENT_DATE,
tx_bytes DECIMAL(32,0) UNSIGNED DEFAULT 0,
rx_bytes DECIMAL(32,0) UNSIGNED DEFAULT 0,
FOREIGN KEY (netdev) REFERENCES device_netdevs(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX netdev_monthly_index
ON netdevs_monthly_traffic (netdev, time);

CREATE TABLE device_notes ( -- custom notes
id_device SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
note1 VARCHAR(64) DEFAULT NULL,
note2 VARCHAR(64) DEFAULT NULL,
note3 VARCHAR(64) DEFAULT NULL,
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE
);

CREATE TABLE device_custom_fields ( -- custom fields from device reported by custom bash code
id_device SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
num1 INT DEFAULT NULL,
num2 INT DEFAULT NULL,
num3 INT DEFAULT NULL,
num4 INT DEFAULT NULL,
num5 INT DEFAULT NULL,
num6 INT DEFAULT NULL,
num7 INT DEFAULT NULL,
num8 INT DEFAULT NULL,
num9 INT DEFAULT NULL,
txt1 VARCHAR(1024) DEFAULT NULL,
txt2 VARCHAR(1024) DEFAULT NULL,
txt3 VARCHAR(1024) DEFAULT NULL,
txt4 VARCHAR(1024) DEFAULT NULL,
txt5 VARCHAR(1024) DEFAULT NULL,
txt6 VARCHAR(1024) DEFAULT NULL,
txt7 VARCHAR(1024) DEFAULT NULL,
txt8 VARCHAR(1024) DEFAULT NULL,
txt9 VARCHAR(1024) DEFAULT NULL,
flo1 DOUBLE DEFAULT NULL,
flo2 DOUBLE DEFAULT NULL,
flo3 DOUBLE DEFAULT NULL,
flo4 DOUBLE DEFAULT NULL,
flo5 DOUBLE DEFAULT NULL,
flo6 DOUBLE DEFAULT NULL,
flo7 DOUBLE DEFAULT NULL,
flo8 DOUBLE DEFAULT NULL,
flo9 DOUBLE DEFAULT NULL,
last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE
);

-- MariaDB bug - trigger breaks multi-row insert via mysqlclib for PREPARED statements, unusable ATM
-- CREATE TRIGGER newdevice_create_custfields
-- AFTER INSERT ON devices
-- FOR EACH ROW
-- INSERT INTO device_custom_fields (id_device) VALUES (new.id_device);

CREATE TABLE custom_scripts ( -- scripts to be executed on device to provide custom fields
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
description VARCHAR(512) DEFAULT NULL,
code VARCHAR(4096) DEFAULT NULL, -- NOTE: needs to match MAX_CUSTSCRIPT_LENGTH
version INT NOT NULL DEFAULT 1,
deleted BOOLEAN DEFAULT FALSE,
last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TRIGGER inc_version_custom_scripts
BEFORE UPDATE ON custom_scripts
FOR EACH ROW
SET new.version = old.version + 1;

CREATE TABLE custom_scripts_devtype_map ( -- for which device type is the custom script valid? NOTE: not implemented in daemon yet
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
script INT NOT NULL,
dev_type INT NOT NULL,
FOREIGN KEY (script) REFERENCES custom_scripts(id) ON DELETE CASCADE,
FOREIGN KEY (dev_type) REFERENCES device_types(id),
last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT cs_devt_map UNIQUE (script,dev_type)
);

CREATE TABLE custom_scripts_aprof_map ( -- what custom scripts are included in which agent profile
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
script INT NOT NULL,
agent_profile INT NOT NULL,
last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (agent_profile) REFERENCES agent_profiles(id) ON DELETE CASCADE,
FOREIGN KEY (script) REFERENCES custom_scripts(id) ON DELETE CASCADE,
CONSTRAINT cs_aprof_map UNIQUE (agent_profile,script)
);

CREATE TABLE system_labels ( -- the system labels
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
label VARCHAR(32),
description TEXT DEFAULT NULL,
mass_update BOOLEAN DEFAULT FALSE,
UNIQUE (label)
);

CREATE TABLE labels ( -- the custom labels
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
label VARCHAR(32),
UNIQUE (label)
);

CREATE TABLE labels_map ( -- mapping table - which device has which labels
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_label INT DEFAULT NULL,
id_device SMALLINT UNSIGNED,
CONSTRAINT device_label UNIQUE (id_label,id_device),
FOREIGN KEY (id_label) REFERENCES labels(id),
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE
);

CREATE TABLE system_labels_map ( -- mapping table - which device has which system labels
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_system_label INT DEFAULT NULL,
id_device SMALLINT UNSIGNED,
CONSTRAINT device_syslabel UNIQUE (id_system_label,id_device),
FOREIGN KEY (id_system_label) REFERENCES system_labels(id),
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE
);

CREATE TABLE monitoring_groups ( -- list of all monitoring groups
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64),
UNIQUE (name)
);

CREATE TABLE monitoring_group_device_map ( -- mapping table - which device belong to which monitoring group
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_device SMALLINT UNSIGNED,
mongrp_id SMALLINT UNSIGNED,
CONSTRAINT device_monitoring_group UNIQUE (id_device, mongrp_id),
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE,
FOREIGN KEY (mongrp_id) REFERENCES monitoring_groups(id)
);

-- following is currently replaced by dashboard/webservice and not stored in DB
-- the table will be reforged for future mongrps statistics if some persistence is needed
-- DROP TABLE IF EXISTS monitoring_group_statistics;
-- CREATE TABLE monitoring_group_statistics (
-- id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-- name VARCHAR(32),
-- val1 INT DEFAULT 0,
-- val2 INT DEFAULT 0,
-- val3 INT DEFAULT 0,
-- val4 INT DEFAULT 0,
-- val5 INT DEFAULT 0,
-- mongrp_id SMALLINT UNSIGNED,
-- FOREIGN KEY (mongrp_id) REFERENCES monitoring_groups(id)
-- );
-- 

CREATE TABLE typetable_time_units (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(32)
);

CREATE TABLE device_history_timeslot (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(32),
unit SMALLINT UNSIGNED NOT NULL,
amount SMALLINT UNSIGNED NOT NULL DEFAULT 0,
FOREIGN KEY (unit) REFERENCES typetable_time_units(id)
);

CREATE TABLE device_history_bit_types (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(32)
);

CREATE TABLE device_history_types (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
customizable BOOLEAN DEFAULT false,
visible_dataparts INT DEFAULT 0,
description VARCHAR(64)
);

CREATE TABLE device_history_type_map (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
history_type SMALLINT UNSIGNED,
customizable BOOLEAN DEFAULT false,
visible BOOLEAN DEFAULT true,
bit_type SMALLINT UNSIGNED,
timeslot SMALLINT UNSIGNED,
bit_order SMALLINT UNSIGNED NOT NULL,
FOREIGN KEY (history_type) REFERENCES device_history_types(id),
FOREIGN KEY (bit_type) REFERENCES device_history_bit_types(id),
FOREIGN KEY (timeslot) REFERENCES device_history_timeslot(id)
);

CREATE TABLE graph_types (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(64)
);

CREATE TABLE graph_type_selections (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
graph_type SMALLINT UNSIGNED NOT NULL DEFAULT 1,
base INTEGER DEFAULT 0,
description VARCHAR(128),
FOREIGN KEY (graph_type) REFERENCES graph_types(id)
);

CREATE TABLE device_history_value_descriptions (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(128),
primary_value_type SMALLINT UNSIGNED NOT NULL,
history_type SMALLINT UNSIGNED NOT NULL,
usual_min INT DEFAULT 0,
usual_max INT DEFAULT 100,
unit VARCHAR(16),
default_ui_order SMALLINT UNSIGNED UNIQUE NOT NULL,
default_visibility BOOLEAN DEFAULT true,
default_graph_type_selection SMALLINT UNSIGNED DEFAULT 1,
actual_graph_type_selection SMALLINT UNSIGNED DEFAULT 1,
FOREIGN KEY (primary_value_type) REFERENCES typetable_value_types(id),
FOREIGN KEY (history_type) REFERENCES device_history_types(id),
FOREIGN KEY (default_graph_type_selection) REFERENCES graph_type_selections(id),
FOREIGN KEY (actual_graph_type_selection) REFERENCES graph_type_selections(id)
);

CREATE TABLE device_history (
history_type SMALLINT UNSIGNED, -- type of history
id_device SMALLINT UNSIGNED NOT NULL, -- id of device (if the stored value is associated with device)
mongrp_id SMALLINT UNSIGNED, -- id of group target (if the stored value is associated with group)
value_id SMALLINT UNSIGNED NOT NULL, -- id of stored value
last_used SMALLINT UNSIGNED DEFAULT 0, -- last value that is actually filled with data (subsequent data columns are not yet populated
data_1 DOUBLE DEFAULT 0,
data_2 DOUBLE DEFAULT 0,
data_3 DOUBLE DEFAULT 0,
data_4 DOUBLE DEFAULT 0,
data_5 DOUBLE DEFAULT 0,
data_6 DOUBLE DEFAULT 0,
data_7 DOUBLE DEFAULT 0,
data_8 DOUBLE DEFAULT 0,
data_9 DOUBLE DEFAULT 0,
data_10 DOUBLE DEFAULT 0,
data_11 DOUBLE DEFAULT 0,
data_12 DOUBLE DEFAULT 0,
data_13 DOUBLE DEFAULT 0,
data_14 DOUBLE DEFAULT 0,
data_15 DOUBLE DEFAULT 0,
data_16 DOUBLE DEFAULT 0,
data_17 DOUBLE DEFAULT 0,
data_18 DOUBLE DEFAULT 0,
data_19 DOUBLE DEFAULT 0,
data_20 DOUBLE DEFAULT 0,
data_21 DOUBLE DEFAULT 0,
data_22 DOUBLE DEFAULT 0,
data_23 DOUBLE DEFAULT 0,
data_24 DOUBLE DEFAULT 0,
data_25 DOUBLE DEFAULT 0,
data_26 DOUBLE DEFAULT 0,
data_27 DOUBLE DEFAULT 0,
data_28 DOUBLE DEFAULT 0,
data_29 DOUBLE DEFAULT 0,
data_30 DOUBLE DEFAULT 0,
data_31 DOUBLE DEFAULT 0,
data_32 DOUBLE DEFAULT 0,
data_33 DOUBLE DEFAULT 0,
PRIMARY KEY (id_device, value_id),
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE,
FOREIGN KEY (mongrp_id) REFERENCES monitoring_groups(id),
FOREIGN KEY (value_id) REFERENCES device_history_value_descriptions(id),
FOREIGN KEY (history_type) REFERENCES device_history_types(id)
);

CREATE TABLE log_event_source (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32)
);

CREATE TABLE log_event_type (
id INT NOT NULL PRIMARY KEY,
source INT NOT NULL,
name VARCHAR(32),
FOREIGN KEY (source) REFERENCES log_event_source(id)
);

CREATE TABLE log_events ( -- logging table
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
evtype INT NOT NULL,
value INT,
source INT NOT NULL DEFAULT 1,
extra_id INT,
msg VARCHAR(256),
time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (evtype) REFERENCES log_event_type(id),
FOREIGN KEY (source) REFERENCES log_event_source(id)
);

CREATE TABLE typetable_operators (
id SMALLINT PRIMARY KEY,
name VARCHAR(32),
oper_txt VARCHAR(10),
oper_txt2 VARCHAR(10),
oper_txt3 VARCHAR(10),
operands SMALLINT DEFAULT 1
);

CREATE TABLE alert_target_type (
id SMALLINT PRIMARY KEY,
name VARCHAR(32)
);

CREATE TABLE alert_table_selection (
id SMALLINT PRIMARY KEY,
table_selection VARCHAR(32)
);

CREATE TABLE alert_target_spec (
id SMALLINT PRIMARY KEY,
name VARCHAR(32),
valtype SMALLINT UNSIGNED NOT NULL DEFAULT 1,
seltype SMALLINT DEFAULT 0,
hint VARCHAR(64),
custom BOOLEAN DEFAULT FALSE,
FOREIGN KEY (valtype) REFERENCES typetable_value_types(id),
FOREIGN KEY (seltype) REFERENCES alert_table_selection(id)
);

CREATE TABLE alert_target_map (
id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
target_spec SMALLINT,
target_type SMALLINT,
FOREIGN KEY (target_type) REFERENCES alert_target_type(id),
FOREIGN KEY (target_spec) REFERENCES alert_target_spec(id)
);

CREATE TABLE alerts ( -- table listing all alerts
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64),
target_type SMALLINT DEFAULT 0, -- e.g. device, group, # devices in group, ...
target_spec SMALLINT DEFAULT 0, -- which metric/statistic
target_amount SMALLINT UNSIGNED DEFAULT 1, -- how many items out of a set (e.g. how many devices in a group)
target_device_id SMALLINT UNSIGNED DEFAULT 0, -- id of device target (if target is device)
target_mongrp_id SMALLINT UNSIGNED DEFAULT 0, -- id of group target (if target is group)
evaluate_interval INT DEFAULT 60, -- how many seconds (at least) to wait between evaluations
oper_first SMALLINT DEFAULT 0,
oper_second SMALLINT DEFAULT 0,
num_first DOUBLE DEFAULT 0,
num_second DOUBLE DEFAULT 0,
last_trigger_time DATETIME DEFAULT NULL,
last_notification_time DATETIME,
last_review_time DATETIME,
enabled SMALLINT UNSIGNED DEFAULT 0, -- if user want this to be checked
active SMALLINT UNSIGNED DEFAULT 0, -- dwarfgd: I am tracking actual version of the alert
counter SMALLINT UNSIGNED DEFAULT 0, -- how many times was this alert version triggered (by dwarfgd)
triggered SMALLINT UNSIGNED DEFAULT 0, -- is this triggered at the moment?
version SMALLINT UNSIGNED DEFAULT 0, -- version set by GUI
deleted BOOLEAN DEFAULT false, -- this alert was deleted
FOREIGN KEY (target_device_id) REFERENCES devices(id_device) ON DELETE CASCADE,
FOREIGN KEY (target_mongrp_id) REFERENCES monitoring_groups(id),
FOREIGN KEY (target_type) REFERENCES alert_target_type(id),
FOREIGN KEY (target_spec) REFERENCES alert_target_spec(id),
FOREIGN KEY (oper_first) REFERENCES typetable_operators(id),
FOREIGN KEY (oper_second) REFERENCES typetable_operators(id)
);

CREATE TABLE product_conf (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name ENUM ('ExternalURL', 'DB version', 'isFirstRun', 'EnabledEmailNotifications', 'EnableEmailNotificationToAddress', 'NotificationEmail', 'SlackToken', 'SlackChannel', 'SenderEmail', 'SmtpServer', 'SmtpPort', 'SmtpSecurity', 'SmtpLogin', 'SmtpPassword', 'ServiceCoreName', 'WebTunnelURL', 'SysctlPath', 'ProductRootDir', 'ProductSrvDir', 'SAASMode', 'LocalPort', 'AggregatedLogsFile', 'SupportLink', 'DevicesTableColumns', 'SNMPGatewayThreads', 'SNMPGatewayBatch', 'SNMPGatewayTimeout', 'SNMPGatewayRetries', 'SNMPGatewayInterval', 'SNMPGatewayLoglevel', 'ServiceSNMPGatewayName', 'SNMPSSLOverride') NOT NULL,
valtype SMALLINT UNSIGNED DEFAULT 1,
system BOOLEAN DEFAULT false,
intval INT,
floatval DOUBLE,
strval VARCHAR(512),
version INT DEFAULT 0,
time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (valtype) REFERENCES typetable_value_types(id)
);

DELIMITER //
CREATE TRIGGER upd_snmp_config BEFORE UPDATE ON product_conf
FOR EACH ROW
BEGIN
  IF NEW.name = 'SNMPGatewayThreads' THEN BEGIN
    IF NEW.intval > 32 THEN SET NEW.intval = 32;
    ELSEIF NEW.intval < 1 THEN SET NEW.intval = 1;
    END IF;
    END;
  ELSEIF NEW.name = 'SNMPGatewayBatch' THEN BEGIN
    IF NEW.intval > 256 THEN SET NEW.intval = 256;
    ELSEIF NEW.intval < 8 THEN SET NEW.intval = 8;
    END IF;
    END;
  ELSEIF NEW.name = 'SNMPGatewayTimeout' THEN BEGIN
    IF NEW.intval > 60 THEN SET NEW.intval = 60;
    ELSEIF NEW.intval < 2 THEN SET NEW.intval = 2;
    END IF;
    END;
  ELSEIF NEW.name = 'SNMPGatewayRetries' THEN BEGIN
    IF NEW.intval > 6 THEN SET NEW.intval = 6;
    ELSEIF NEW.intval < 1 THEN SET NEW.intval = 1;
    END IF;
    END;
  ELSEIF NEW.name = 'SNMPGatewayInterval' THEN BEGIN
    IF NEW.intval > 3600 THEN SET NEW.intval = 3600;
    ELSEIF NEW.intval < 120 THEN SET NEW.intval = 120;
    END IF;
    END;
  ELSEIF NEW.name = 'SNMPSSLOverride' THEN BEGIN
    IF NEW.intval > 1 THEN SET NEW.intval = 1;
    ELSEIF NEW.intval < -1 THEN SET NEW.intval = -1;
    END IF;
    END;
  ELSEIF NEW.name = 'SNMPGatewayLoglevel' THEN BEGIN
    IF NEW.intval > 50 THEN SET NEW.intval = 50;
    ELSEIF NEW.intval < 15 THEN SET NEW.intval = 15;
    END IF;
    END;
  END IF;
END; //
DELIMITER ;

CREATE TABLE data_change_source (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL
);

CREATE TABLE data_atom_nature (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL
);

CREATE TABLE data_atom_context (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL
);

CREATE TABLE data_atom (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL,
internal_type SMALLINT UNSIGNED NOT NULL DEFAULT 3,
nature SMALLINT UNSIGNED NOT NULL,
context SMALLINT UNSIGNED NOT NULL,
visible BOOLEAN DEFAULT True,
log_stream BOOLEAN DEFAULT True,
log_db BOOLEAN DEFAULT True,
log_file BOOLEAN DEFAULT True,
FOREIGN KEY (internal_type) REFERENCES typetable_value_types(id),
FOREIGN KEY (nature) REFERENCES data_atom_nature(id),
FOREIGN KEY (context) REFERENCES data_atom_context(id)
);

CREATE TABLE data_change_log ( -- DCL
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
source SMALLINT UNSIGNED NOT NULL, -- where did the change originated from
atom SMALLINT UNSIGNED NOT NULL, -- which data atom was changed
id_device SMALLINT UNSIGNED DEFAULT NULL, -- id of the device if value belongs to device
extra_id SMALLINT UNSIGNED DEFAULT NULL, -- another identificator if value does not belong to device
extra_txt TINYTEXT DEFAULT NULL, -- rendered textual representation of extra_id (e.g. user name)
value_txt TINYTEXT DEFAULT NULL, -- textual representation of the change
change_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (source) REFERENCES data_change_source(id),
FOREIGN KEY (atom) REFERENCES data_atom(id),
FOREIGN KEY (id_device) REFERENCES devices(id_device) ON DELETE CASCADE
);

CREATE TRIGGER delban_8_device_models
BEFORE DELETE ON device_models
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delete not allowed on simpletable';
