-- Migration 050: Zabbix WAN host cache + IT Glue WAN circuit cache
-- Enables local gap analysis and event correlation without live API calls every time.

-- ────────────────────────────────────────────────────────────────
-- Zabbix WAN host cache
-- Populated by /api/zabbix/sync-hosts (syncs from live Zabbix API)
-- ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS zabbix_wan_hosts (
  hostid                  VARCHAR(50)   PRIMARY KEY,
  host_name               VARCHAR(255),               -- technical/sanitized name (host field)
  display_name            VARCHAR(255),               -- human-readable display name
  wan_ip                  VARCHAR(45),                -- ICMP interface IP
  status                  INTEGER       DEFAULT 0,    -- 0=enabled, 1=disabled
  rmm_site_uid            VARCHAR(100),               -- from {$RMM_SITE_UID} macro
  autotask_company_id     INTEGER,                    -- from {$AUTOTASK_COMPANY_ID} macro
  autotask_company_name   VARCHAR(255),               -- from {$AUTOTASK_COMPANY_NAME} macro
  isp_name                VARCHAR(255),               -- from {$ISP_NAME} macro
  asn                     VARCHAR(50),                -- from {$ASN} macro
  is_multi_wan            BOOLEAN       DEFAULT FALSE,
  source                  VARCHAR(50),                -- 'datto-rmm', 'manual', etc.
  tags                    JSONB,
  last_problem_at         TIMESTAMPTZ,                -- most recent Zabbix problem clock
  last_problem_name       TEXT,                       -- most recent Zabbix problem name
  last_synced_at          TIMESTAMPTZ   DEFAULT NOW(),
  created_at              TIMESTAMPTZ   DEFAULT NOW(),
  updated_at              TIMESTAMPTZ   DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_zabbix_wan_hosts_company ON zabbix_wan_hosts (autotask_company_id);
CREATE INDEX IF NOT EXISTS idx_zabbix_wan_hosts_rmm_site ON zabbix_wan_hosts (rmm_site_uid);
CREATE INDEX IF NOT EXISTS idx_zabbix_wan_hosts_ip ON zabbix_wan_hosts (wan_ip);

-- ────────────────────────────────────────────────────────────────
-- IT Glue WAN circuit cache
-- Populated by /api/itglue/sync-wan (parses flexible_asset_type_id=3794)
-- ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS itg_wan_circuits (
  id                      BIGINT        PRIMARY KEY,  -- IT Glue flexible asset ID
  organization_id         BIGINT,                     -- IT Glue org ID
  org_name                VARCHAR(255),
  autotask_company_id     BIGINT,                     -- via itg_organizations.psa_id::bigint
  provider                VARCHAR(255),
  link_type               VARCHAR(100),
  static_ips              TEXT[],                     -- parsed public IPs from traits
  raw_ip_text             TEXT,                       -- original IT Glue text (for reference)
  upload_mbps             NUMERIC,
  download_mbps           NUMERIC,
  location_name           VARCHAR(255),
  location_city           VARCHAR(255),
  notes                   TEXT,
  is_decommissioned       BOOLEAN       DEFAULT FALSE,
  zabbix_hostid           VARCHAR(50),                -- matched Zabbix host (NULL = gap)
  last_synced_at          TIMESTAMPTZ   DEFAULT NOW(),
  created_at              TIMESTAMPTZ   DEFAULT NOW(),
  updated_at              TIMESTAMPTZ   DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_wan_circuits_org ON itg_wan_circuits (organization_id);
CREATE INDEX IF NOT EXISTS idx_itg_wan_circuits_company ON itg_wan_circuits (autotask_company_id);
CREATE INDEX IF NOT EXISTS idx_itg_wan_circuits_zabbix ON itg_wan_circuits (zabbix_hostid);