Netpulse_SasS/server/migrations/0002_inventory.sql

-- =====================================================================
-- NetPulse :: 0002_inventory.sql
-- Локації, групи, пристрої, інтерфейси, облікові дані, теги.
-- =====================================================================

-- ---------------------------------------------------------------------
-- Локації (сайти) та ієрархічні групи
-- ---------------------------------------------------------------------

CREATE TABLE inv.sites (
  id          uuid PRIMARY KEY DEFAULT core.new_id(),
  tenant_id   uuid NOT NULL REFERENCES core.tenants(id) ON DELETE CASCADE,
  parent_id   uuid REFERENCES inv.sites(id) ON DELETE SET NULL,
  name        text NOT NULL,
  code        text,                         -- KYV-DC1
  address     text,
  -- Геокоординати для OSM-підкладки мапи
  lat         double precision CHECK (lat BETWEEN -90 AND 90),
  lon         double precision CHECK (lon BETWEEN -180 AND 180),
  timezone    text,
  meta        jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, name)
);
CREATE INDEX sites_tenant_idx ON inv.sites (tenant_id);
CREATE TRIGGER trg_sites_touch BEFORE UPDATE ON inv.sites
  FOR EACH ROW EXECUTE FUNCTION core.touch_updated_at();

-- Групи для RBAC-скоупів, масових операцій та авто-групування на мапі
CREATE TYPE inv.group_kind AS ENUM ('static','dynamic');

CREATE TABLE inv.device_groups (
  id          uuid PRIMARY KEY DEFAULT core.new_id(),
  tenant_id   uuid NOT NULL REFERENCES core.tenants(id) ON DELETE CASCADE,
  parent_id   uuid REFERENCES inv.device_groups(id) ON DELETE CASCADE,
  name        text NOT NULL,
  kind        inv.group_kind NOT NULL DEFAULT 'static',
  -- Для dynamic: правило відбору {"all":[{"field":"vendor","op":"eq","value":"mikrotik"}]}
  filter      jsonb,
  color       text,
  icon        text,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, parent_id, name)
);
CREATE INDEX device_groups_tenant_idx ON inv.device_groups (tenant_id);

-- ---------------------------------------------------------------------
-- Пристрої
-- ---------------------------------------------------------------------

CREATE TYPE inv.device_kind AS ENUM (
  'router','switch','firewall','ap','server','vm','printer','ups','pdu',
  'inverter','bms','sensor','camera','olt','onu','cloud','website','other'
);

CREATE TYPE inv.device_status AS ENUM ('up','down','warning','unknown','maintenance','disabled');

CREATE TABLE inv.devices (
  id             uuid PRIMARY KEY DEFAULT core.new_id(),
  tenant_id      uuid NOT NULL REFERENCES core.tenants(id) ON DELETE CASCADE,
  site_id        uuid REFERENCES inv.sites(id) ON DELETE SET NULL,
  agent_id       uuid,                       -- FK додається в 0003 (зонд-опитувач)
  name           text NOT NULL,
  hostname       citext,
  -- Основна адреса опитування
  address        inet,
  fqdn           text,
  kind           inv.device_kind NOT NULL DEFAULT 'other',
  vendor         text,
  model          text,
  os_version     text,
  serial_number  text,
  -- Ідентифікатори для кореляції LLDP/CDP/ARP-виявлення
  chassis_id     text,                       -- LLDP chassis ID
  system_name    text,                       -- sysName / CDP device-id
  base_mac       macaddr,
  mgmt_vlan      int CHECK (mgmt_vlan BETWEEN 1 AND 4094),

  status         inv.device_status NOT NULL DEFAULT 'unknown',
  status_changed_at timestamptz,
  last_seen_at   timestamptz,

  -- Профілі опитування (які плагіни активні для цього пристрою)
  monitoring     jsonb NOT NULL DEFAULT '{}'::jsonb,
  -- Джерело появи: manual | discovery | import | api
  source         text NOT NULL DEFAULT 'manual',
  -- Чи тарифікується (Free-план рахує лише enabled)
  is_billable    boolean NOT NULL DEFAULT true,
  enabled        boolean NOT NULL DEFAULT true,

  notes          text,
  meta           jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now(),
  deleted_at     timestamptz
);
CREATE UNIQUE INDEX devices_tenant_name_uniq ON inv.devices (tenant_id, lower(name))
  WHERE deleted_at IS NULL;
CREATE INDEX devices_tenant_status_idx ON inv.devices (tenant_id, status) WHERE deleted_at IS NULL;
CREATE INDEX devices_site_idx     ON inv.devices (site_id);
CREATE INDEX devices_agent_idx    ON inv.devices (agent_id);
CREATE INDEX devices_address_idx  ON inv.devices (tenant_id, address);
CREATE INDEX devices_chassis_idx  ON inv.devices (tenant_id, chassis_id) WHERE chassis_id IS NOT NULL;
CREATE INDEX devices_basemac_idx  ON inv.devices (tenant_id, base_mac)   WHERE base_mac IS NOT NULL;
CREATE INDEX devices_name_trgm    ON inv.devices USING gin (name gin_trgm_ops);
CREATE TRIGGER trg_devices_touch BEFORE UPDATE ON inv.devices
  FOR EACH ROW EXECUTE FUNCTION core.touch_updated_at();

CREATE TABLE inv.device_group_members (
  group_id   uuid NOT NULL REFERENCES inv.device_groups(id) ON DELETE CASCADE,
  device_id  uuid NOT NULL REFERENCES inv.devices(id) ON DELETE CASCADE,
  PRIMARY KEY (group_id, device_id)
);
CREATE INDEX dgm_device_idx ON inv.device_group_members (device_id);

CREATE TABLE inv.tags (
  id         uuid PRIMARY KEY DEFAULT core.new_id(),
  tenant_id  uuid NOT NULL REFERENCES core.tenants(id) ON DELETE CASCADE,
  key        text NOT NULL,
  value      text,
  color      text,
  UNIQUE (tenant_id, key, value)
);

CREATE TABLE inv.device_tags (
  device_id  uuid NOT NULL REFERENCES inv.devices(id) ON DELETE CASCADE,
  tag_id     uuid NOT NULL REFERENCES inv.tags(id) ON DELETE CASCADE,
  PRIMARY KEY (device_id, tag_id)
);

-- ---------------------------------------------------------------------
-- Інтерфейси (потрібні для зв'язків на мапі "port -> port")
-- ---------------------------------------------------------------------

CREATE TYPE inv.if_admin_status AS ENUM ('up','down','testing','unknown');
CREATE TYPE inv.if_oper_status  AS ENUM ('up','down','testing','unknown','dormant','notPresent','lowerLayerDown');

CREATE TABLE inv.interfaces (
  id            uuid PRIMARY KEY DEFAULT core.new_id(),
  tenant_id     uuid NOT NULL REFERENCES core.tenants(id) ON DELETE CASCADE,
  device_id     uuid NOT NULL REFERENCES inv.devices(id) ON DELETE CASCADE,
  if_index      bigint,                     -- SNMP ifIndex
  name          text NOT NULL,              -- GigabitEthernet0/1, ether1, eth0
  alias         text,                       -- ifAlias / description
  mac           macaddr,
  mtu           int,
  type          text,                       -- ethernetCsmacd, ieee8023adLag, l3ipvlan...
  speed_bps     bigint,                     -- номінальна швидкість (для % завантаження)
  duplex        text,
  admin_status  inv.if_admin_status NOT NULL DEFAULT 'unknown',
  oper_status   inv.if_oper_status  NOT NULL DEFAULT 'unknown',
  last_change_at timestamptz,
  -- LAG/стек: посилання на батьківський агрегат
  parent_if_id  uuid REFERENCES inv.interfaces(id) ON DELETE SET NULL,
  is_uplink     boolean NOT NULL DEFAULT false,
  monitored     boolean NOT NULL DEFAULT true,
  meta          jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX interfaces_device_ifindex_uniq
  ON inv.interfaces (device_id, if_index) WHERE if_index IS NOT NULL;
CREATE UNIQUE INDEX interfaces_device_name_uniq ON inv.interfaces (device_id, lower(name));
CREATE INDEX interfaces_tenant_idx ON inv.interfaces (tenant_id);
CREATE INDEX interfaces_mac_idx    ON inv.interfaces (tenant_id, mac) WHERE mac IS NOT NULL;
CREATE TRIGGER trg_interfaces_touch BEFORE UPDATE ON inv.interfaces
  FOR EACH ROW EXECUTE FUNCTION core.touch_updated_at();

-- IP-адреси інтерфейсів (для ARP-кореляції та L3-топології)
CREATE TABLE inv.interface_addresses (
  id            uuid PRIMARY KEY DEFAULT core.new_id(),
  tenant_id     uuid NOT NULL REFERENCES core.tenants(id) ON DELETE CASCADE,
  interface_id  uuid NOT NULL REFERENCES inv.interfaces(id) ON DELETE CASCADE,
  address       inet NOT NULL,
  is_primary    boolean NOT NULL DEFAULT false,
  vrf           text,
  UNIQUE (interface_id, address)
);
CREATE INDEX if_addr_lookup_idx ON inv.interface_addresses (tenant_id, address);

-- Підмережі — для авто-групування пристроїв на мапі
CREATE TABLE inv.subnets (
  id          uuid PRIMARY KEY DEFAULT core.new_id(),
  tenant_id   uuid NOT NULL REFERENCES core.tenants(id) ON DELETE CASCADE,
  site_id     uuid REFERENCES inv.sites(id) ON DELETE SET NULL,
  cidr        cidr NOT NULL,
  vlan_id     int CHECK (vlan_id BETWEEN 1 AND 4094),
  name        text,
  description text,
  UNIQUE (tenant_id, cidr)
);

-- ---------------------------------------------------------------------
-- Облікові дані для опитування / NCM (посилання на core.secrets)
-- ---------------------------------------------------------------------

CREATE TYPE inv.credential_proto AS ENUM ('ssh','telnet','snmp_v2c','snmp_v3','http','https','api','modbus');

CREATE TABLE inv.credentials (
  id            uuid PRIMARY KEY DEFAULT core.new_id(),
  tenant_id     uuid NOT NULL REFERENCES core.tenants(id) ON DELETE CASCADE,
  name          text NOT NULL,
  proto         inv.credential_proto NOT NULL,
  username      text,
  port          int CHECK (port BETWEEN 1 AND 65535),
  -- Секрети (пароль, приватний ключ, snmp v3 auth/priv) — лише шифровані
  secret_id     uuid REFERENCES core.secrets(id) ON DELETE SET NULL,
  enable_secret_id uuid REFERENCES core.secrets(id) ON DELETE SET NULL,
  -- SNMPv3: {"sec_level":"authPriv","auth_proto":"SHA256","priv_proto":"AES128","context":""}
  options       jsonb NOT NULL DEFAULT '{}'::jsonb,
  is_default    boolean NOT NULL DEFAULT false,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, name)
);
CREATE INDEX credentials_tenant_idx ON inv.credentials (tenant_id, proto);

-- Прив'язка кредів до пристроїв (пристрій може мати SSH + SNMP одночасно)
CREATE TABLE inv.device_credentials (
  device_id     uuid NOT NULL REFERENCES inv.devices(id) ON DELETE CASCADE,
  credential_id uuid NOT NULL REFERENCES inv.credentials(id) ON DELETE CASCADE,
  priority      int NOT NULL DEFAULT 100,
  PRIMARY KEY (device_id, credential_id)
);