-- DROP SCHEMA public;

CREATE SCHEMA public AUTHORIZATION pg_database_owner;

COMMENT ON SCHEMA public IS 'standard public schema';

-- DROP SEQUENCE public.actions_id_seq;

CREATE SEQUENCE public.actions_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.actions_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.actions_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.actions_id_seq TO system_reports;

-- DROP SEQUENCE public.audit_trail_id_seq;

CREATE SEQUENCE public.audit_trail_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.audit_trail_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.audit_trail_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.audit_trail_id_seq TO system_reports;

-- DROP SEQUENCE public.client_versions_id_seq;

CREATE SEQUENCE public.client_versions_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.client_versions_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.client_versions_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.client_versions_id_seq TO system_reports;

-- DROP SEQUENCE public.ips_id_seq;

CREATE SEQUENCE public.ips_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.ips_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.ips_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.ips_id_seq TO system_reports;

-- DROP SEQUENCE public.locations_id_seq;

CREATE SEQUENCE public.locations_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.locations_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.locations_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.locations_id_seq TO system_reports;

-- DROP SEQUENCE public.network_devices_id_seq;

CREATE SEQUENCE public.network_devices_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.network_devices_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.network_devices_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.network_devices_id_seq TO system_reports;

-- DROP SEQUENCE public.networks_id_seq;

CREATE SEQUENCE public.networks_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.networks_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.networks_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.networks_id_seq TO system_reports;

-- DROP SEQUENCE public.racks_id_seq;

CREATE SEQUENCE public.racks_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.racks_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.racks_id_seq TO postgres;

-- DROP SEQUENCE public.server_actions_id_seq;

CREATE SEQUENCE public.server_actions_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.server_actions_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.server_actions_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.server_actions_id_seq TO system_reports;

-- DROP SEQUENCE public.server_container_details_id_seq;

CREATE SEQUENCE public.server_container_details_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.server_container_details_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.server_container_details_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.server_container_details_id_seq TO system_reports;

-- DROP SEQUENCE public.server_containers_id_seq;

CREATE SEQUENCE public.server_containers_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.server_containers_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.server_containers_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.server_containers_id_seq TO system_reports;

-- DROP SEQUENCE public.server_site_certs_id_seq;

CREATE SEQUENCE public.server_site_certs_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.server_site_certs_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.server_site_certs_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.server_site_certs_id_seq TO system_reports;

-- DROP SEQUENCE public.server_sites_id_seq;

CREATE SEQUENCE public.server_sites_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.server_sites_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.server_sites_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.server_sites_id_seq TO system_reports;

-- DROP SEQUENCE public.server_versions_id_seq;

CREATE SEQUENCE public.server_versions_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.server_versions_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.server_versions_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.server_versions_id_seq TO system_reports;

-- DROP SEQUENCE public.server_vm_details_id_seq;

CREATE SEQUENCE public.server_vm_details_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.server_vm_details_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.server_vm_details_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.server_vm_details_id_seq TO system_reports;

-- DROP SEQUENCE public.server_vm_interface_ips_id_seq;

CREATE SEQUENCE public.server_vm_interface_ips_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.server_vm_interface_ips_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.server_vm_interface_ips_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.server_vm_interface_ips_id_seq TO system_reports;

-- DROP SEQUENCE public.server_vm_interfaces_id_seq;

CREATE SEQUENCE public.server_vm_interfaces_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.server_vm_interfaces_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.server_vm_interfaces_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.server_vm_interfaces_id_seq TO system_reports;

-- DROP SEQUENCE public.server_vms_id_seq;

CREATE SEQUENCE public.server_vms_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.server_vms_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.server_vms_id_seq TO postgres;
GRANT USAGE, SELECT ON SEQUENCE public.server_vms_id_seq TO system_reports;

-- DROP SEQUENCE public.snmp_walks_id_seq;

CREATE SEQUENCE public.snmp_walks_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 2147483647
    START 1
    CACHE 1
    NO CYCLE;

-- Permissions

ALTER SEQUENCE public.snmp_walks_id_seq OWNER TO system_reports;
GRANT ALL ON SEQUENCE public.snmp_walks_id_seq TO system_reports;
-- public.actions definition

-- Drop table

-- DROP TABLE public.actions;

CREATE TABLE public.actions (
                                id bigserial NOT NULL,
                                receiver varchar(36) NOT NULL,
                                payload jsonb NOT NULL,
                                created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                "result" text NULL,
                                creator varchar NULL,
                                active bool DEFAULT true NULL,
                                "uuid" varchar NULL,
                                CONSTRAINT actions_pkey PRIMARY KEY (id)
);
CREATE INDEX actions_payload_idx ON public.actions USING gin (payload);
CREATE INDEX actions_uuid_idx ON public.actions USING btree (uuid);
CREATE INDEX idx_receiver ON public.actions USING btree (receiver);

-- Permissions

ALTER TABLE public.actions OWNER TO postgres;
GRANT ALL ON TABLE public.actions TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.actions TO system_reports;


-- public.audit_trail definition

-- Drop table

-- DROP TABLE public.audit_trail;

CREATE TABLE public.audit_trail (
                                    id bigserial NOT NULL,
                                    username varchar(100) NOT NULL,
                                    "section" varchar(20) DEFAULT 'other'::character varying NOT NULL,
                                    "action" text NOT NULL,
                                    detail jsonb NULL,
                                    created_at timestamptz DEFAULT now() NOT NULL,
                                    CONSTRAINT audit_trail_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_audit_created_at ON public.audit_trail USING btree (created_at);

-- Permissions

ALTER TABLE public.audit_trail OWNER TO postgres;
GRANT ALL ON TABLE public.audit_trail TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.audit_trail TO system_reports;


-- public.client_versions definition

-- Drop table

-- DROP TABLE public.client_versions;

CREATE TABLE public.client_versions (
                                        id bigserial NOT NULL,
                                        "version" varchar(50) NOT NULL,
                                        arch varchar(32) NOT NULL,
                                        download_url varchar(255) NOT NULL,
                                        released_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                        CONSTRAINT client_versions_pkey PRIMARY KEY (id)
);
CREATE INDEX client_versions_arch_idx ON public.client_versions USING btree (arch);
CREATE INDEX client_versions_version_idx ON public.client_versions USING btree (version);

-- Permissions

ALTER TABLE public.client_versions OWNER TO postgres;
GRANT ALL ON TABLE public.client_versions TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.client_versions TO system_reports;


-- public.config definition

-- Drop table

-- DROP TABLE public.config;

CREATE TABLE public.config (
                               "name" varchar(100) NOT NULL,
                               value jsonb NOT NULL,
                               CONSTRAINT config_pkey PRIMARY KEY (name)
);

-- Permissions

ALTER TABLE public.config OWNER TO postgres;
GRANT ALL ON TABLE public.config TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.config TO system_reports;


-- public.desktops definition

-- Drop table

-- DROP TABLE public.desktops;

CREATE TABLE public.desktops (
                                 system_id varchar(36) NOT NULL,
                                 "timestamp" timestamptz NULL,
                                 os varchar(100) NOT NULL,
                                 hostname varchar(255) NOT NULL,
                                 cores int8 NOT NULL,
                                 total_memory int8 NOT NULL,
                                 all_users jsonb NOT NULL,
                                 active_user varchar(100) NOT NULL,
                                 installed_apps jsonb NOT NULL,
                                 running_procs jsonb NOT NULL,
                                 network_interfaces jsonb NOT NULL,
                                 primary_ip varchar(45) NULL,
                                 primary_mac varchar(17) NULL,
                                 client_version varchar(50) NOT NULL,
                                 vpnconfig varchar(256) NULL,
                                 CONSTRAINT desktops_pkey PRIMARY KEY (system_id)
);
CREATE INDEX desktops_active_user_idx ON public.desktops USING btree (active_user);
CREATE INDEX desktops_all_users_idx ON public.desktops USING gin (all_users);
CREATE INDEX desktops_client_version_idx ON public.desktops USING btree (client_version);
CREATE INDEX desktops_cores_idx ON public.desktops USING btree (cores);
CREATE INDEX desktops_hostname_idx ON public.desktops USING btree (hostname);
CREATE INDEX desktops_installed_apps_idx ON public.desktops USING gin (installed_apps);
CREATE INDEX desktops_network_interfaces_idx ON public.desktops USING gin (network_interfaces);
CREATE INDEX desktops_os_idx ON public.desktops USING btree (os);
CREATE INDEX desktops_primary_ip_idx ON public.desktops USING btree (primary_ip);
CREATE INDEX desktops_primary_mac_idx ON public.desktops USING btree (primary_mac);
CREATE INDEX desktops_running_procs_idx ON public.desktops USING gin (running_procs);
CREATE INDEX desktops_timestamp_idx ON public.desktops USING btree ("timestamp");
CREATE INDEX desktops_total_memory_idx ON public.desktops USING btree (total_memory);
CREATE INDEX desktops_vpnconfig_idx ON public.desktops USING btree (vpnconfig);

-- Permissions

ALTER TABLE public.desktops OWNER TO postgres;
GRANT ALL ON TABLE public.desktops TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.desktops TO system_reports;


-- public.locations definition

-- Drop table

-- DROP TABLE public.locations;

CREATE TABLE public.locations (
                                  id bigserial NOT NULL,
                                  "name" varchar NOT NULL,
                                  description varchar NULL,
                                  street varchar NULL,
                                  city varchar NULL,
                                  postcode varchar NULL,
                                  country varchar NULL,
                                  shortname varchar NULL,
                                  CONSTRAINT locations_pk PRIMARY KEY (id)
);

-- Permissions

ALTER TABLE public.locations OWNER TO postgres;
GRANT ALL ON TABLE public.locations TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.locations TO system_reports;


-- public.racks definition

-- Drop table

-- DROP TABLE public.racks;

CREATE TABLE public.racks (
                              id bigserial NOT NULL,
                              location_id int8 NOT NULL,
                              total_ru int4 NULL,
                              aisle varchar NULL,
                              racknumber int4 NULL,
                              cage varchar NULL,
                              "name" varchar NULL,
                              description varchar NULL,
                              CONSTRAINT racks_pk PRIMARY KEY (id)
);

-- Permissions

ALTER TABLE public.racks OWNER TO postgres;
GRANT ALL ON TABLE public.racks TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.racks TO system_reports;


-- public.racks_systems definition

-- Drop table

-- DROP TABLE public.racks_systems;

CREATE TABLE public.racks_systems (
                                      racks_id int8 NOT NULL,
                                      system_id varchar NOT NULL,
                                      rack_unit int4 NOT NULL
);
CREATE UNIQUE INDEX racks_systems_racks_id_idx ON public.racks_systems USING btree (racks_id, system_id, rack_unit);

-- Permissions

ALTER TABLE public.racks_systems OWNER TO postgres;
GRANT ALL ON TABLE public.racks_systems TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.racks_systems TO system_reports;


-- public.server_actions definition

-- Drop table

-- DROP TABLE public.server_actions;

CREATE TABLE public.server_actions (
                                       id bigserial NOT NULL,
                                       receiver varchar(36) NOT NULL,
                                       payload jsonb NOT NULL,
                                       created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                       "result" text NULL,
                                       creator varchar NULL,
                                       active bool DEFAULT true NULL,
                                       "uuid" varchar NULL,
                                       CONSTRAINT server_actions_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_server_receiver ON public.server_actions USING btree (receiver);
CREATE INDEX server_actions_payload_idx ON public.server_actions USING gin (payload);
CREATE INDEX server_actions_uuid_idx ON public.server_actions USING btree (uuid);

-- Permissions

ALTER TABLE public.server_actions OWNER TO postgres;
GRANT ALL ON TABLE public.server_actions TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.server_actions TO system_reports;


-- public.server_versions definition

-- Drop table

-- DROP TABLE public.server_versions;

CREATE TABLE public.server_versions (
                                        id bigserial NOT NULL,
                                        "version" varchar(50) NOT NULL,
                                        arch varchar(32) NOT NULL,
                                        download_url varchar(255) NOT NULL,
                                        released_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                        CONSTRAINT server_versions_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_server_versions_download_url ON public.server_versions USING btree (download_url);
CREATE INDEX idx_server_versions_released_at ON public.server_versions USING btree (released_at);
CREATE INDEX server_versions_arch_idx ON public.server_versions USING btree (arch);
CREATE INDEX server_versions_version_idx ON public.server_versions USING btree (version);

-- Permissions

ALTER TABLE public.server_versions OWNER TO postgres;
GRANT ALL ON TABLE public.server_versions TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.server_versions TO system_reports;


-- public.servers definition

-- Drop table

-- DROP TABLE public.servers;

CREATE TABLE public.servers (
                                system_id varchar(36) NOT NULL,
                                "timestamp" timestamptz NULL,
                                hostname varchar(255) NULL,
                                os varchar(255) NULL,
                                platform varchar(255) NULL,
                                cores int4 NULL,
                                cpu_info jsonb NULL,
                                total_memory int8 NULL,
                                available_memory int8 NULL,
                                memory_slots int4 NULL,
                                memory_type varchar(100) NULL,
                                total_disk int8 NULL,
                                available_disk int8 NULL,
                                file_systems jsonb NULL,
                                hwinfo jsonb NULL,
                                dmidecode jsonb NULL,
                                lspci json NULL,
                                lsusb json NULL,
                                proc_cpuinfo json NULL,
                                proc_meminfo json NULL,
                                proc_mounts json NULL,
                                motherboard_manufacturer varchar(255) NULL,
                                motherboard_product varchar(255) NULL,
                                ipmi_info json NULL,
                                network_interfaces jsonb NULL,
                                users jsonb NULL,
                                databases jsonb NULL,
                                domains jsonb NULL,
                                vhosts jsonb NULL,
                                vms jsonb NULL,
                                containers jsonb NULL,
                                classification varchar(50) NULL,
                                mailname varchar(256) NULL,
                                lshw json NULL,
                                outstanding_updates json NULL,
                                restic_cron varchar(20) NULL,
                                disk_types json NULL,
                                crontab json NULL,
                                ftp_users json NULL,
                                mail_domains json NULL,
                                ssh_pubkeys json NULL,
                                whitelisted_keys json NULL,
                                monitoring json NULL,
                                fqdn varchar NULL,
                                CONSTRAINT servers_pkey PRIMARY KEY (system_id)
);
CREATE INDEX servers_available_disk_idx ON public.servers USING btree (available_disk);
CREATE INDEX servers_available_memory_idx ON public.servers USING btree (available_memory);
CREATE INDEX servers_classification_idx ON public.servers USING btree (classification);
CREATE INDEX servers_containers_idx ON public.servers USING gin (containers);
CREATE INDEX servers_cores_idx ON public.servers USING btree (cores);
CREATE INDEX servers_cpu_info_idx ON public.servers USING gin (cpu_info);
CREATE INDEX servers_crontab_idx ON public.servers USING gin (((crontab)::jsonb));
CREATE INDEX servers_databases_idx ON public.servers USING gin (databases);
CREATE INDEX servers_disk_types_idx ON public.servers USING gin (((disk_types)::jsonb));
CREATE INDEX servers_dmidecode_idx ON public.servers USING gin (dmidecode);
CREATE INDEX servers_domains_idx ON public.servers USING gin (domains);
CREATE INDEX servers_file_systems_idx ON public.servers USING gin (file_systems);
CREATE INDEX servers_fqdn_idx ON public.servers USING btree (fqdn);
CREATE INDEX servers_ftp_users_idx ON public.servers USING gin (((ftp_users)::jsonb));
CREATE INDEX servers_hostname_idx ON public.servers USING btree (hostname);
CREATE INDEX servers_hwinfo_idx ON public.servers USING gin (hwinfo);
CREATE INDEX servers_ipmi_info_idx ON public.servers USING gin (((ipmi_info)::jsonb));
CREATE INDEX servers_ipmi_info_idx1 ON public.servers USING gin (((ipmi_info)::jsonb));
CREATE INDEX servers_lshw_idx ON public.servers USING gin (((lshw)::jsonb));
CREATE INDEX servers_lshw_idx1 ON public.servers USING gin (((lshw)::jsonb));
CREATE INDEX servers_lspci_idx ON public.servers USING gin (((lspci)::jsonb));
CREATE INDEX servers_lsusb_idx ON public.servers USING gin (((lsusb)::jsonb));
CREATE INDEX servers_mail_domains_idx ON public.servers USING gin (((mail_domains)::jsonb));
CREATE INDEX servers_mailname_idx ON public.servers USING btree (mailname);
CREATE INDEX servers_memory_slots_idx ON public.servers USING btree (memory_slots);
CREATE INDEX servers_memory_type_idx ON public.servers USING btree (memory_type);
CREATE INDEX servers_motherboard_manufacturer_idx ON public.servers USING btree (motherboard_manufacturer);
CREATE INDEX servers_motherboard_product_idx ON public.servers USING btree (motherboard_product);
CREATE INDEX servers_network_interfaces_idx ON public.servers USING gin (network_interfaces);
CREATE INDEX servers_os_idx ON public.servers USING btree (os);
CREATE INDEX servers_outstanding_updates_idx ON public.servers USING gin (((outstanding_updates)::jsonb));
CREATE INDEX servers_outstanding_updates_idx1 ON public.servers USING gin (((outstanding_updates)::jsonb));
CREATE INDEX servers_platform_idx ON public.servers USING btree (platform);
CREATE INDEX servers_proc_cpuinfo_idx ON public.servers USING gin (((proc_cpuinfo)::jsonb));
CREATE INDEX servers_proc_meminfo_idx ON public.servers USING gin (((proc_meminfo)::jsonb));
CREATE INDEX servers_proc_mounts_idx ON public.servers USING gin (((proc_mounts)::jsonb));
CREATE INDEX servers_proc_mounts_idx1 ON public.servers USING gin (((proc_mounts)::jsonb));
CREATE INDEX servers_ssh_pubkeys_idx ON public.servers USING gin (((ssh_pubkeys)::jsonb));
CREATE INDEX servers_timestamp_idx ON public.servers USING btree ("timestamp");
CREATE INDEX servers_total_disk_idx ON public.servers USING btree (total_disk);
CREATE INDEX servers_total_memory_idx ON public.servers USING btree (total_memory);
CREATE INDEX servers_users_idx ON public.servers USING gin (users);
CREATE INDEX servers_vhosts_idx ON public.servers USING gin (vhosts);
CREATE INDEX servers_vms_idx ON public.servers USING gin (vms);
CREATE INDEX servers_whitelisted_keys_idx ON public.servers USING gin (((whitelisted_keys)::jsonb));

-- Permissions

ALTER TABLE public.servers OWNER TO postgres;
GRANT ALL ON TABLE public.servers TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.servers TO system_reports;


-- public.snmp_walks definition

-- Drop table

-- DROP TABLE public.snmp_walks;

CREATE TABLE public.snmp_walks (
                                   id serial4 NOT NULL,
                                   device_id text NULL,
                                   walk_data text NULL,
                                   gathered_at timestamp DEFAULT now() NULL,
                                   CONSTRAINT snmp_walks_pkey PRIMARY KEY (id)
);

-- Permissions

ALTER TABLE public.snmp_walks OWNER TO system_reports;
GRANT ALL ON TABLE public.snmp_walks TO system_reports;


-- public.users definition

-- Drop table

-- DROP TABLE public.users;

CREATE TABLE public.users (
                              "name" varchar(100) NOT NULL,
                              ip varchar(100) NULL,
                              client_id varchar(100) NULL,
                              system_id varchar NULL,
                              "system_user" varchar NULL,
                              CONSTRAINT users_pkey PRIMARY KEY (name)
);
CREATE INDEX users_system_id_idx ON public.users USING btree (system_id);
CREATE INDEX users_system_user_idx ON public.users USING btree (system_user);

-- Permissions

ALTER TABLE public.users OWNER TO postgres;
GRANT ALL ON TABLE public.users TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.users TO system_reports;


-- public.network_devices definition

-- Drop table

-- DROP TABLE public.network_devices;

CREATE TABLE public.network_devices (
                                        id bigserial NOT NULL,
                                        ip varchar(265) NULL,
                                        manufacturer varchar(265) NULL,
                                        model varchar(265) NULL,
                                        "version" varchar(265) NULL,
                                        firmware varchar(100) NULL,
                                        status varchar(20) NULL,
                                        vlans jsonb NULL,
                                        description text NULL,
                                        "lag" varchar(100) NULL,
                                        ips json NULL,
                                        access_type varchar DEFAULT 'snmp'::character varying NULL,
                                        snmp_user varchar NULL,
                                        snmp_password varchar NULL,
                                        snmp_community varchar NULL,
                                        snmp_version varchar NULL,
                                        location_id int8 NULL,
                                        operating_mode varchar DEFAULT 'L2'::character varying NULL,
                                        uptime varchar NULL,
                                        lastreboot varchar NULL,
                                        system_name varchar(265) NULL,
                                        serial varchar NULL,
                                        terminal_user varchar NULL,
                                        terminal_password varchar NULL,
                                        terminal_protocol varchar DEFAULT 'ssh'::character varying NULL,
                                        ssh_key_path varchar NULL,
                                        last_reboot date NULL,
                                        CONSTRAINT network_devices_pkey PRIMARY KEY (id),
                                        CONSTRAINT network_devices_locations_fk FOREIGN KEY (location_id) REFERENCES public.locations(id)
);
CREATE UNIQUE INDEX network_devices_ip_idx ON public.network_devices USING btree (ip);
CREATE INDEX network_devices_manufacturer_idx ON public.network_devices USING btree (manufacturer);
CREATE INDEX network_devices_model_idx ON public.network_devices USING btree (model);
CREATE INDEX network_devices_status_idx ON public.network_devices USING btree (status);
CREATE UNIQUE INDEX network_devices_system_name_idx ON public.network_devices USING btree (system_name);
CREATE INDEX network_devices_vlans_idx ON public.network_devices USING gin (vlans);

-- Permissions

ALTER TABLE public.network_devices OWNER TO postgres;
GRANT ALL ON TABLE public.network_devices TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.network_devices TO system_reports;


-- public.network_devices_ports definition

-- Drop table

-- DROP TABLE public.network_devices_ports;

CREATE TABLE public.network_devices_ports (
                                              network_device_id int8 NOT NULL,
                                              port varchar NOT NULL,
                                              arptable jsonb NULL,
                                              ips jsonb NULL,
                                              remote_address varchar NULL,
                                              remote_hostname varchar NULL,
                                              remote_port varchar NULL,
                                              speed int8 NULL,
                                              status varchar DEFAULT 'down'::character varying NULL,
                                              mtu int8 NULL,
                                              alias varchar NULL,
                                              in_octets int8 NULL,
                                              out_octets int8 NULL,
                                              in_errors int8 NULL,
                                              out_errors int8 NULL,
                                              "lag" varchar NULL,
                                              vlans jsonb NULL,
                                              system_id varchar NULL,
                                              source_table varchar NULL,
                                              admin_status varchar NULL,
                                              CONSTRAINT network_devices_ports_unique UNIQUE (network_device_id, port),
                                              CONSTRAINT network_devices_ports_network_devices_fk FOREIGN KEY (network_device_id) REFERENCES public.network_devices(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE UNIQUE INDEX network_devices_ports_network_device_id_idx ON public.network_devices_ports USING btree (network_device_id, port);

-- Permissions

ALTER TABLE public.network_devices_ports OWNER TO postgres;
GRANT ALL ON TABLE public.network_devices_ports TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.network_devices_ports TO system_reports;


-- public.networks definition

-- Drop table

-- DROP TABLE public.networks;

CREATE TABLE public.networks (
                                 id bigserial NOT NULL,
                                 cidr varchar(50) NOT NULL,
                                 vlan varchar(50) NULL,
                                 description text NULL,
                                 network_mgmt_lan bool DEFAULT false NULL,
                                 parent_id int8 NULL,
                                 gateway varchar(50) NULL,
                                 broadcast varchar(50) NULL,
                                 location_id int8 NULL,
                                 CONSTRAINT networks_pkey PRIMARY KEY (id),
                                 CONSTRAINT networks_locations_fk FOREIGN KEY (location_id) REFERENCES public.locations(id),
                                 CONSTRAINT networks_networks_fk FOREIGN KEY (parent_id) REFERENCES public.networks(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX networks_cidr_idx ON public.networks USING btree (cidr);
CREATE INDEX networks_vlan_idx ON public.networks USING btree (vlan);

-- Permissions

ALTER TABLE public.networks OWNER TO postgres;
GRANT ALL ON TABLE public.networks TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.networks TO system_reports;


-- public.server_container_details definition

-- Drop table

-- DROP TABLE public.server_container_details;

CREATE TABLE public.server_container_details (
                                                 id bigserial NOT NULL,
                                                 system_id varchar(36) NOT NULL,
                                                 "name" varchar(255) NULL,
                                                 vmid int8 NULL,
                                                 "lock" varchar(50) NULL,
                                                 swap int8 NULL,
                                                 cores int8 NULL,
                                                 memory int8 NULL,
                                                 onboot bool NULL,
                                                 ostype varchar(50) NULL,
                                                 cpulimit int4 NULL,
                                                 cpuunits int4 NULL,
                                                 hostname varchar(255) NULL,
                                                 unprivileged bool NULL,
                                                 status varchar(50) NULL,
                                                 froxlor bool NULL,
                                                 rootfs_size varchar(100) NULL,
                                                 net0_gw varchar(100) NULL,
                                                 net0_ip varchar(100) NULL,
                                                 net0_name varchar(100) NULL,
                                                 net0_type varchar(20) NULL,
                                                 net0_bridge varchar(100) NULL,
                                                 net0_hwaddr varchar(100) NULL,
                                                 lxc_mount_entry text NULL,
                                                 lxc_cgroup_devices_allow text NULL,
                                                 CONSTRAINT server_container_details_pkey PRIMARY KEY (id),
                                                 CONSTRAINT server_container_details_system_id_fkey FOREIGN KEY (system_id) REFERENCES public.servers(system_id) ON DELETE CASCADE
);
CREATE INDEX idx_scd_cores ON public.server_container_details USING btree (cores);
CREATE INDEX idx_scd_cpulimit ON public.server_container_details USING btree (cpulimit);
CREATE INDEX idx_scd_cpuunits ON public.server_container_details USING btree (cpuunits);
CREATE INDEX idx_scd_froxlor ON public.server_container_details USING btree (froxlor);
CREATE INDEX idx_scd_hostname ON public.server_container_details USING btree (hostname);
CREATE INDEX idx_scd_lock ON public.server_container_details USING btree (lock);
CREATE INDEX idx_scd_lxc_cgroup ON public.server_container_details USING btree (lxc_cgroup_devices_allow);
CREATE INDEX idx_scd_lxc_mount ON public.server_container_details USING btree (lxc_mount_entry);
CREATE INDEX idx_scd_memory ON public.server_container_details USING btree (memory);
CREATE INDEX idx_scd_name ON public.server_container_details USING btree (name);
CREATE INDEX idx_scd_net0_bridge ON public.server_container_details USING btree (net0_bridge);
CREATE INDEX idx_scd_net0_gw ON public.server_container_details USING btree (net0_gw);
CREATE INDEX idx_scd_net0_hwaddr ON public.server_container_details USING btree (net0_hwaddr);
CREATE INDEX idx_scd_net0_ip ON public.server_container_details USING btree (net0_ip);
CREATE INDEX idx_scd_net0_name ON public.server_container_details USING btree (net0_name);
CREATE INDEX idx_scd_net0_type ON public.server_container_details USING btree (net0_type);
CREATE INDEX idx_scd_onboot ON public.server_container_details USING btree (onboot);
CREATE INDEX idx_scd_ostype ON public.server_container_details USING btree (ostype);
CREATE INDEX idx_scd_rootfs_size ON public.server_container_details USING btree (rootfs_size);
CREATE INDEX idx_scd_status ON public.server_container_details USING btree (status);
CREATE INDEX idx_scd_swap ON public.server_container_details USING btree (swap);
CREATE INDEX idx_scd_system_id ON public.server_container_details USING btree (system_id);
CREATE INDEX idx_scd_unprivileged ON public.server_container_details USING btree (unprivileged);
CREATE INDEX idx_scd_vmid ON public.server_container_details USING btree (vmid);

-- Permissions

ALTER TABLE public.server_container_details OWNER TO postgres;
GRANT ALL ON TABLE public.server_container_details TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.server_container_details TO system_reports;


-- public.server_containers definition

-- Drop table

-- DROP TABLE public.server_containers;

CREATE TABLE public.server_containers (
                                          id bigserial NOT NULL,
                                          system_id varchar(36) NOT NULL,
                                          CONSTRAINT server_containers_pkey PRIMARY KEY (id),
                                          CONSTRAINT server_containers_system_id_fkey FOREIGN KEY (system_id) REFERENCES public.servers(system_id) ON DELETE CASCADE
);
CREATE INDEX server_containers_system_id_idx ON public.server_containers USING btree (system_id);

-- Permissions

ALTER TABLE public.server_containers OWNER TO postgres;
GRANT ALL ON TABLE public.server_containers TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.server_containers TO system_reports;


-- public.server_sites definition

-- Drop table

-- DROP TABLE public.server_sites;

CREATE TABLE public.server_sites (
                                     id bigserial NOT NULL,
                                     system_id varchar(36) NOT NULL,
                                     "domain" varchar(255) NOT NULL,
                                     directory text NULL,
                                     CONSTRAINT server_sites_pkey PRIMARY KEY (id),
                                     CONSTRAINT server_sites_system_id_fkey FOREIGN KEY (system_id) REFERENCES public.servers(system_id) ON DELETE CASCADE
);
CREATE INDEX server_sites_directory_idx ON public.server_sites USING btree (directory);
CREATE INDEX server_sites_domain_idx ON public.server_sites USING btree (domain);
CREATE INDEX server_sites_system_id_idx ON public.server_sites USING btree (system_id);

-- Permissions

ALTER TABLE public.server_sites OWNER TO postgres;
GRANT ALL ON TABLE public.server_sites TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.server_sites TO system_reports;


-- public.server_vm_details definition

-- Drop table

-- DROP TABLE public.server_vm_details;

CREATE TABLE public.server_vm_details (
                                          id bigserial NOT NULL,
                                          system_id varchar(36) NOT NULL,
                                          "name" varchar(255) NULL,
                                          vmid int8 NULL,
                                          pid int8 NULL,
                                          mem_mb int8 NULL,
                                          status varchar(50) NULL,
                                          froxlor bool NULL,
                                          bootdisk_gb numeric(10, 2) NULL,
                                          boot varchar(255) NULL,
                                          ide2_media varchar(50) NULL,
                                          net0_tag int8 NULL,
                                          net0_bridge varchar(50) NULL,
                                          net0_mac varchar(20) NULL,
                                          numa int8 NULL,
                                          agent bool NULL,
                                          cores int8 NULL,
                                          memory int8 NULL,
                                          ostype varchar(50) NULL,
                                          scsihw varchar(50) NULL,
                                          smbios1 varchar(100) NULL,
                                          sockets int8 NULL,
                                          virtio0_size varchar(50) NULL,
                                          virtio0_discard varchar(10) NULL,
                                          vmgenid uuid NULL,
                                          description text NULL,
                                          osinfo_id varchar(50) NULL,
                                          osinfo_name varchar(100) NULL,
                                          osinfo_machine varchar(50) NULL,
                                          osinfo_version varchar(100) NULL,
                                          osinfo_version_id varchar(50) NULL,
                                          osinfo_pretty_name varchar(100) NULL,
                                          kernel_release varchar(100) NULL,
                                          kernel_version text NULL,
                                          CONSTRAINT server_vm_details_pkey PRIMARY KEY (id),
                                          CONSTRAINT server_vm_details_system_id_fkey FOREIGN KEY (system_id) REFERENCES public.servers(system_id) ON DELETE CASCADE
);
CREATE INDEX idx_svd_agent ON public.server_vm_details USING btree (agent);
CREATE INDEX idx_svd_boot ON public.server_vm_details USING btree (boot);
CREATE INDEX idx_svd_bootdisk_gb ON public.server_vm_details USING btree (bootdisk_gb);
CREATE INDEX idx_svd_cores ON public.server_vm_details USING btree (cores);
CREATE INDEX idx_svd_description ON public.server_vm_details USING btree (description);
CREATE INDEX idx_svd_froxlor ON public.server_vm_details USING btree (froxlor);
CREATE INDEX idx_svd_ide2_media ON public.server_vm_details USING btree (ide2_media);
CREATE INDEX idx_svd_kernel_release ON public.server_vm_details USING btree (kernel_release);
CREATE INDEX idx_svd_kernel_version ON public.server_vm_details USING btree (kernel_version);
CREATE INDEX idx_svd_mem_mb ON public.server_vm_details USING btree (mem_mb);
CREATE INDEX idx_svd_memory ON public.server_vm_details USING btree (memory);
CREATE INDEX idx_svd_name ON public.server_vm_details USING btree (name);
CREATE INDEX idx_svd_net0_bridge ON public.server_vm_details USING btree (net0_bridge);
CREATE INDEX idx_svd_net0_mac ON public.server_vm_details USING btree (net0_mac);
CREATE INDEX idx_svd_net0_tag ON public.server_vm_details USING btree (net0_tag);
CREATE INDEX idx_svd_numa ON public.server_vm_details USING btree (numa);
CREATE INDEX idx_svd_osinfo_id ON public.server_vm_details USING btree (osinfo_id);
CREATE INDEX idx_svd_osinfo_machine ON public.server_vm_details USING btree (osinfo_machine);
CREATE INDEX idx_svd_osinfo_name ON public.server_vm_details USING btree (osinfo_name);
CREATE INDEX idx_svd_osinfo_pretty ON public.server_vm_details USING btree (osinfo_pretty_name);
CREATE INDEX idx_svd_osinfo_version ON public.server_vm_details USING btree (osinfo_version);
CREATE INDEX idx_svd_osinfo_version_id ON public.server_vm_details USING btree (osinfo_version_id);
CREATE INDEX idx_svd_ostype ON public.server_vm_details USING btree (ostype);
CREATE INDEX idx_svd_pid ON public.server_vm_details USING btree (pid);
CREATE INDEX idx_svd_scsihw ON public.server_vm_details USING btree (scsihw);
CREATE INDEX idx_svd_smbios1 ON public.server_vm_details USING btree (smbios1);
CREATE INDEX idx_svd_sockets ON public.server_vm_details USING btree (sockets);
CREATE INDEX idx_svd_status ON public.server_vm_details USING btree (status);
CREATE INDEX idx_svd_system_id ON public.server_vm_details USING btree (system_id);
CREATE INDEX idx_svd_virtio0_discard ON public.server_vm_details USING btree (virtio0_discard);
CREATE INDEX idx_svd_virtio0_size ON public.server_vm_details USING btree (virtio0_size);
CREATE INDEX idx_svd_vmgenid ON public.server_vm_details USING btree (vmgenid);
CREATE INDEX idx_svd_vmid ON public.server_vm_details USING btree (vmid);

-- Permissions

ALTER TABLE public.server_vm_details OWNER TO postgres;
GRANT ALL ON TABLE public.server_vm_details TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.server_vm_details TO system_reports;


-- public.server_vm_interfaces definition

-- Drop table

-- DROP TABLE public.server_vm_interfaces;

CREATE TABLE public.server_vm_interfaces (
                                             id bigserial NOT NULL,
                                             vm_id int8 NOT NULL,
                                             "name" varchar(50) NULL,
                                             hardware_address varchar(20) NULL,
                                             rx_errs int8 NULL,
                                             tx_errs int8 NULL,
                                             rx_bytes int8 NULL,
                                             tx_bytes int8 NULL,
                                             rx_dropped int8 NULL,
                                             rx_packets int8 NULL,
                                             tx_dropped int8 NULL,
                                             tx_packets int8 NULL,
                                             CONSTRAINT server_vm_interfaces_pkey PRIMARY KEY (id),
                                             CONSTRAINT server_vm_interfaces_vm_id_fkey FOREIGN KEY (vm_id) REFERENCES public.server_vm_details(id) ON DELETE CASCADE
);
CREATE INDEX idx_svi_hardware_address ON public.server_vm_interfaces USING btree (hardware_address);
CREATE INDEX idx_svi_name ON public.server_vm_interfaces USING btree (name);
CREATE INDEX idx_svi_rx_bytes ON public.server_vm_interfaces USING btree (rx_bytes);
CREATE INDEX idx_svi_rx_dropped ON public.server_vm_interfaces USING btree (rx_dropped);
CREATE INDEX idx_svi_rx_errs ON public.server_vm_interfaces USING btree (rx_errs);
CREATE INDEX idx_svi_rx_packets ON public.server_vm_interfaces USING btree (rx_packets);
CREATE INDEX idx_svi_tx_bytes ON public.server_vm_interfaces USING btree (tx_bytes);
CREATE INDEX idx_svi_tx_dropped ON public.server_vm_interfaces USING btree (tx_dropped);
CREATE INDEX idx_svi_tx_errs ON public.server_vm_interfaces USING btree (tx_errs);
CREATE INDEX idx_svi_tx_packets ON public.server_vm_interfaces USING btree (tx_packets);
CREATE INDEX idx_svi_vm_id ON public.server_vm_interfaces USING btree (vm_id);

-- Permissions

ALTER TABLE public.server_vm_interfaces OWNER TO postgres;
GRANT ALL ON TABLE public.server_vm_interfaces TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.server_vm_interfaces TO system_reports;


-- public.server_vms definition

-- Drop table

-- DROP TABLE public.server_vms;

CREATE TABLE public.server_vms (
                                   id bigserial NOT NULL,
                                   system_id varchar(36) NOT NULL,
                                   "data" text NULL,
                                   CONSTRAINT server_vms_pkey PRIMARY KEY (id),
                                   CONSTRAINT server_vms_system_id_fkey FOREIGN KEY (system_id) REFERENCES public.servers(system_id) ON DELETE CASCADE
);
CREATE INDEX server_vms_system_id_idx ON public.server_vms USING btree (system_id);

-- Permissions

ALTER TABLE public.server_vms OWNER TO postgres;
GRANT ALL ON TABLE public.server_vms TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.server_vms TO system_reports;


-- public.ips definition

-- Drop table

-- DROP TABLE public.ips;

CREATE TABLE public.ips (
                            id bigserial NOT NULL,
                            ip inet NOT NULL,
                            network_id int8 NOT NULL,
                            system_id varchar(36) NULL,
                            assigned_by varchar(100) DEFAULT 'system-agent'::character varying NULL,
                            description text NULL,
                            system_source varchar NULL,
                            system_type varchar DEFAULT 'server'::character varying NULL,
                            CONSTRAINT ips_pkey PRIMARY KEY (id),
                            CONSTRAINT ips_network_id_fkey FOREIGN KEY (network_id) REFERENCES public.networks(id) ON DELETE CASCADE
);
CREATE INDEX ips_assigned_by_idx ON public.ips USING btree (assigned_by);
CREATE INDEX ips_ip_idx ON public.ips USING btree (ip);
CREATE INDEX ips_system_id_idx ON public.ips USING btree (system_id);
CREATE INDEX ips_system_source_table_idx ON public.ips USING btree (system_source);

-- Permissions

ALTER TABLE public.ips OWNER TO postgres;
GRANT ALL ON TABLE public.ips TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.ips TO system_reports;


-- public.server_site_certs definition

-- Drop table

-- DROP TABLE public.server_site_certs;

CREATE TABLE public.server_site_certs (
                                          id bigserial NOT NULL,
                                          site_id int8 NOT NULL,
                                          "path" text NULL,
                                          certificate_path text NULL,
                                          issuer text NULL,
                                          serial text NULL,
                                          "version" varchar(20) NULL,
                                          not_before timestamptz NULL,
                                          not_after timestamptz NULL,
                                          signature varchar(255) NULL,
                                          common_name varchar(255) NULL,
                                          CONSTRAINT server_site_certs_pkey PRIMARY KEY (id),
                                          CONSTRAINT server_site_certs_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.server_sites(id) ON DELETE CASCADE
);
CREATE INDEX idx_ssc_certificate_path ON public.server_site_certs USING btree (certificate_path);
CREATE INDEX idx_ssc_common_name ON public.server_site_certs USING btree (common_name);
CREATE INDEX idx_ssc_issuer ON public.server_site_certs USING btree (issuer);
CREATE INDEX idx_ssc_not_after ON public.server_site_certs USING btree (not_after);
CREATE INDEX idx_ssc_not_before ON public.server_site_certs USING btree (not_before);
CREATE INDEX idx_ssc_path ON public.server_site_certs USING btree (path);
CREATE INDEX idx_ssc_serial ON public.server_site_certs USING btree (serial);
CREATE INDEX idx_ssc_signature ON public.server_site_certs USING btree (signature);
CREATE INDEX idx_ssc_site_id ON public.server_site_certs USING btree (site_id);
CREATE INDEX idx_ssc_version ON public.server_site_certs USING btree (version);

-- Permissions

ALTER TABLE public.server_site_certs OWNER TO postgres;
GRANT ALL ON TABLE public.server_site_certs TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.server_site_certs TO system_reports;


-- public.server_vm_interface_ips definition

-- Drop table

-- DROP TABLE public.server_vm_interface_ips;

CREATE TABLE public.server_vm_interface_ips (
                                                id bigserial NOT NULL,
                                                interface_id int8 NOT NULL,
                                                ip_address varchar(100) NULL,
                                                ip_address_type varchar(10) NULL,
                                                prefix int8 NULL,
                                                CONSTRAINT server_vm_interface_ips_pkey PRIMARY KEY (id),
                                                CONSTRAINT server_vm_interface_ips_iface_fk FOREIGN KEY (interface_id) REFERENCES public.server_vm_interfaces(id) ON DELETE CASCADE
);
CREATE INDEX idx_svii_interface_id ON public.server_vm_interface_ips USING btree (interface_id);
CREATE INDEX idx_svii_ip_address ON public.server_vm_interface_ips USING btree (ip_address);
CREATE INDEX idx_svii_ip_address_type ON public.server_vm_interface_ips USING btree (ip_address_type);
CREATE INDEX idx_svii_prefix ON public.server_vm_interface_ips USING btree (prefix);


create user system_reports with password 'soobie0uCh9thii5aixa3theireichoo';
-- Permissions

ALTER TABLE public.server_vm_interface_ips OWNER TO postgres;
GRANT ALL ON TABLE public.server_vm_interface_ips TO postgres;
GRANT DELETE, SELECT, UPDATE, INSERT ON TABLE public.server_vm_interface_ips TO system_reports;




-- Permissions

GRANT ALL ON SCHEMA public TO pg_database_owner;
GRANT USAGE ON SCHEMA public TO public;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT DELETE, SELECT, UPDATE, INSERT ON TABLES TO system_reports;