-- =====================================================================
-- NetPulse :: smoke.sql
-- Функціональна перевірка схеми на чистій БД після всіх міграцій.
-- Запуск:  psql -d netpulse -v ON_ERROR_STOP=1 -f db/tests/smoke.sql
-- Виконувати від суперкористувача (частина перевірок робить SET ROLE).
-- =====================================================================

\set ON_ERROR_STOP on
\timing off

BEGIN;

-- ---------------------------------------------------------------------
-- 1. Два тенанти
-- ---------------------------------------------------------------------
INSERT INTO core.tenants (id, slug, name, status) VALUES
  ('11111111-1111-1111-1111-111111111111', 'acme',  'ACME ISP',   'active'),
  ('22222222-2222-2222-2222-222222222222', 'globex','Globex MSP', 'active');

INSERT INTO bill.entitlements (tenant_id, plan_key, max_devices, max_maps, max_map_nodes, features)
SELECT '11111111-1111-1111-1111-111111111111', key, max_devices, max_maps, max_map_nodes, features
FROM bill.plans WHERE key = 'free';

INSERT INTO bill.entitlements (tenant_id, plan_key, max_devices, max_maps, max_map_nodes, features)
SELECT '22222222-2222-2222-2222-222222222222', key, max_devices, max_maps, max_map_nodes, features
FROM bill.plans WHERE key = 'pro';

-- ---------------------------------------------------------------------
-- 2. Сайт, агент, пристрої, інтерфейси
-- ---------------------------------------------------------------------
INSERT INTO inv.sites (id, tenant_id, name, code, lat, lon) VALUES
  ('33333333-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111',
   'Kyiv DC1', 'KYV-DC1', 50.4501, 30.5234);

INSERT INTO core.agents (id, tenant_id, site_id, name, token_hash, status, enabled_modules) VALUES
  ('44444444-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111',
   '33333333-0000-0000-0000-000000000001', 'probe-kyv-01',
   digest('agent-token-1','sha256'), 'online', '{icmp,snmp,topology}');

INSERT INTO inv.devices (id, tenant_id, site_id, agent_id, name, address, kind, vendor, chassis_id, system_name) VALUES
  ('55555555-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111',
   '33333333-0000-0000-0000-000000000001', '44444444-0000-0000-0000-000000000001',
   'core-sw-01', '10.0.0.1', 'switch', 'cisco', '0011.2233.4455', 'core-sw-01'),
  ('55555555-0000-0000-0000-000000000002', '11111111-1111-1111-1111-111111111111',
   '33333333-0000-0000-0000-000000000001', '44444444-0000-0000-0000-000000000001',
   'edge-rtr-01', '10.0.0.2', 'router', 'mikrotik', '0011.2233.6677', 'edge-rtr-01'),
  ('55555555-0000-0000-0000-000000000003', '11111111-1111-1111-1111-111111111111',
   '33333333-0000-0000-0000-000000000001', '44444444-0000-0000-0000-000000000001',
   'ups-01', '10.0.0.3', 'ups', 'apc', NULL, NULL);

INSERT INTO inv.interfaces (id, tenant_id, device_id, if_index, name, speed_bps, oper_status, is_uplink) VALUES
  ('66666666-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111',
   '55555555-0000-0000-0000-000000000001', 1, 'GigabitEthernet0/1', 1000000000, 'up', true),
  ('66666666-0000-0000-0000-000000000002', '11111111-1111-1111-1111-111111111111',
   '55555555-0000-0000-0000-000000000002', 1, 'ether1', 1000000000, 'up', true);

-- ---------------------------------------------------------------------
-- 3. Автовиявлення: LLDP-сусід → зведений лінк
-- ---------------------------------------------------------------------
INSERT INTO topo.neighbors (tenant_id, device_id, interface_id, proto,
                            remote_chassis_id, remote_system_name, remote_port_id,
                            resolved_device_id, resolved_interface_id, confidence)
VALUES ('11111111-1111-1111-1111-111111111111',
        '55555555-0000-0000-0000-000000000001', '66666666-0000-0000-0000-000000000001', 'lldp',
        '0011.2233.6677', 'edge-rtr-01', 'ether1',
        '55555555-0000-0000-0000-000000000002', '66666666-0000-0000-0000-000000000002', 95);

INSERT INTO topo.links (id, tenant_id, a_device_id, a_interface_id, b_device_id, b_interface_id,
                        kind, capacity_bps, discovered_by, status)
VALUES ('77777777-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111',
        '55555555-0000-0000-0000-000000000001', '66666666-0000-0000-0000-000000000001',
        '55555555-0000-0000-0000-000000000002', '66666666-0000-0000-0000-000000000002',
        'physical', 1000000000, 'lldp', 'up');

-- Перевірка нормалізації пари: дзеркальний лінк B→A має впертись в unique index
DO $$
BEGIN
  INSERT INTO topo.links (tenant_id, a_device_id, a_interface_id, b_device_id, b_interface_id)
  VALUES ('11111111-1111-1111-1111-111111111111',
          '55555555-0000-0000-0000-000000000002', '66666666-0000-0000-0000-000000000002',
          '55555555-0000-0000-0000-000000000001', '66666666-0000-0000-0000-000000000001');
  RAISE EXCEPTION 'FAIL: дзеркальний лінк B->A створився (нормалізація пари не працює)';
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'PASS: дзеркальний лінк B->A відхилено (links_pair_uniq)';
END $$;

-- ---------------------------------------------------------------------
-- 4. Мапа з підкладкою, вузлами та ребром port->port
-- ---------------------------------------------------------------------
INSERT INTO topo.maps (id, tenant_id, site_id, name, slug, kind, layout_algo) VALUES
  ('88888888-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111',
   '33333333-0000-0000-0000-000000000001', 'Kyiv DC1 — основна', 'kyiv-dc1', 'floor_plan', 'manual');

INSERT INTO topo.map_backgrounds (tenant_id, map_id, kind, storage_key, mime_type,
                                  natural_width, natural_height, width, height, opacity)
VALUES ('11111111-1111-1111-1111-111111111111', '88888888-0000-0000-0000-000000000001',
        'image', 's3://netpulse/acme/floorplan-dc1.svg', 'image/svg+xml',
        2400, 1600, 2400, 1600, 0.6);

INSERT INTO topo.map_nodes (id, tenant_id, map_id, kind, device_id, label, x, y, style) VALUES
  ('99999999-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111',
   '88888888-0000-0000-0000-000000000001', 'device', '55555555-0000-0000-0000-000000000001',
   'core-sw-01', 400, 300, '{"icon":"switch","color":"#22c55e"}'),
  ('99999999-0000-0000-0000-000000000002', '11111111-1111-1111-1111-111111111111',
   '88888888-0000-0000-0000-000000000001', 'device', '55555555-0000-0000-0000-000000000002',
   'edge-rtr-01', 800, 300, '{"icon":"router"}'),
  ('99999999-0000-0000-0000-000000000003', '11111111-1111-1111-1111-111111111111',
   '88888888-0000-0000-0000-000000000001', 'text', NULL,
   'Серверна №2', 400, 120, '{"fontSize":18}');

-- Нода kind='device' без device_id має впертись у CHECK
DO $$
BEGIN
  INSERT INTO topo.map_nodes (tenant_id, map_id, kind, x, y)
  VALUES ('11111111-1111-1111-1111-111111111111','88888888-0000-0000-0000-000000000001','device',0,0);
  RAISE EXCEPTION 'FAIL: device-нода без device_id створилась';
EXCEPTION WHEN check_violation THEN
  RAISE NOTICE 'PASS: device-нода без device_id відхилена (map_nodes_kind_ref_chk)';
END $$;

INSERT INTO topo.map_edges (tenant_id, map_id, source_node_id, target_node_id,
                            source_interface_id, target_interface_id, link_id,
                            label, style, animation, thresholds)
VALUES ('11111111-1111-1111-1111-111111111111', '88888888-0000-0000-0000-000000000001',
        '99999999-0000-0000-0000-000000000001', '99999999-0000-0000-0000-000000000002',
        '66666666-0000-0000-0000-000000000001', '66666666-0000-0000-0000-000000000002',
        '77777777-0000-0000-0000-000000000001',
        'Gi0/1 → ether1', 'smoothstep',
        '{"enabled":true,"speed_source":"utilization","direction":"a_to_b","max_speed":3}',
        '{"warn_pct":70,"crit_pct":90}');

-- ---------------------------------------------------------------------
-- 5. Телеметрія
-- ---------------------------------------------------------------------
INSERT INTO ts.icmp_samples (ts, device_id, tenant_id, agent_id, rtt_avg_ms, rtt_min_ms, rtt_max_ms,
                             jitter_ms, loss_pct, packets_sent, packets_recv, reachable)
SELECT now() - (g || ' minutes')::interval,
       '55555555-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111',
       '44444444-0000-0000-0000-000000000001',
       1.2 + g * 0.05, 1.0, 2.5, 0.3, CASE WHEN g = 0 THEN 0 ELSE 0 END, 3, 3, true
FROM generate_series(0, 59) g;

INSERT INTO ts.if_counters (ts, interface_id, device_id, tenant_id,
                            in_octets, out_octets, in_bps, out_bps,
                            util_in_pct, util_out_pct, oper_up)
SELECT now() - (g || ' minutes')::interval,
       '66666666-0000-0000-0000-000000000001', '55555555-0000-0000-0000-000000000001',
       '11111111-1111-1111-1111-111111111111',
       1000000 * g, 900000 * g,
       420000000, 780000000,
       42.0, 78.0, true
FROM generate_series(0, 59) g;

-- Узагальнені метрики через series/samples
INSERT INTO ts.series (tenant_id, device_id, plugin_key, metric_key, unit, labels)
VALUES ('11111111-1111-1111-1111-111111111111','55555555-0000-0000-0000-000000000001',
        'snmp','cpu.util','pct','{"core":"0"}');

INSERT INTO ts.samples (ts, series_id, value)
SELECT now() - (g || ' minutes')::interval, s.id, 30 + (g % 17)
FROM generate_series(0, 59) g,
     ts.series s
WHERE s.metric_key = 'cpu.util';

-- ---------------------------------------------------------------------
-- 6. NCM: репо, конфіг, diff
-- ---------------------------------------------------------------------
INSERT INTO ncm.repos (id, tenant_id, name, storage_path) VALUES
  ('aaaaaaaa-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111',
   'acme', '/var/lib/netpulse/git/acme.git');

INSERT INTO ncm.configs (id, tenant_id, device_id, repo_id, commit_sha, blob_sha, branch, path,
                         size_bytes, line_count, content_hash, is_change, lines_added, lines_removed)
VALUES
  ('bbbbbbbb-0000-0000-0000-000000000001','11111111-1111-1111-1111-111111111111',
   '55555555-0000-0000-0000-000000000001','aaaaaaaa-0000-0000-0000-000000000001',
   repeat('a',40), repeat('b',40), 'device/core-sw-01', 'kyv-dc1/core-sw-01/running.cfg',
   4096, 180, digest('config-v1','sha256'), false, 0, 0),
  ('bbbbbbbb-0000-0000-0000-000000000002','11111111-1111-1111-1111-111111111111',
   '55555555-0000-0000-0000-000000000001','aaaaaaaa-0000-0000-0000-000000000001',
   repeat('c',40), repeat('d',40), 'device/core-sw-01', 'kyv-dc1/core-sw-01/running.cfg',
   4130, 183, digest('config-v2','sha256'), true, 4, 1);

UPDATE ncm.configs SET prev_config_id = 'bbbbbbbb-0000-0000-0000-000000000001'
WHERE id = 'bbbbbbbb-0000-0000-0000-000000000002';

-- ---------------------------------------------------------------------
-- 7. Алерт
-- ---------------------------------------------------------------------
INSERT INTO alr.rules (id, tenant_id, name, source, severity, condition, for_seconds) VALUES
  ('cccccccc-0000-0000-0000-000000000001','11111111-1111-1111-1111-111111111111',
   'Uplink > 90%', 'interface', 'high',
   '{"metric":"util_out_pct","op":">","value":90}', 600);

INSERT INTO alr.alerts (tenant_id, rule_id, device_id, interface_id, link_id,
                        severity, title, dedup_key, value, threshold)
VALUES ('11111111-1111-1111-1111-111111111111','cccccccc-0000-0000-0000-000000000001',
        '55555555-0000-0000-0000-000000000001','66666666-0000-0000-0000-000000000001',
        '77777777-0000-0000-0000-000000000001',
        'high','Uplink Gi0/1 завантажений на 94%','rule:uplink90:if:6666...0001', 94.0, 90.0);

-- Дедуплікація: другий активний алерт із тим самим ключем не пройде
DO $$
BEGIN
  INSERT INTO alr.alerts (tenant_id, rule_id, device_id, severity, title, dedup_key)
  VALUES ('11111111-1111-1111-1111-111111111111','cccccccc-0000-0000-0000-000000000001',
          '55555555-0000-0000-0000-000000000001','high','дубль','rule:uplink90:if:6666...0001');
  RAISE EXCEPTION 'FAIL: дубль активного алерту створився';
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'PASS: дубль активного алерту відхилено (alerts_active_dedup_uniq)';
END $$;

COMMIT;

-- =====================================================================
-- ПЕРЕВІРКИ
-- =====================================================================

\echo ''
\echo '--- 1. Стан полотна мапи одним запитом ---'
SELECT n.label,
       n.kind,
       n.x, n.y,
       d.status                       AS device_status,
       round(i.rtt_avg_ms::numeric,2) AS rtt_ms,
       i.loss_pct
FROM topo.map_nodes n
LEFT JOIN inv.devices d       ON d.id = n.device_id
LEFT JOIN ts.device_last_icmp i ON i.device_id = n.device_id
WHERE n.map_id = '88888888-0000-0000-0000-000000000001'
ORDER BY n.x, n.y;

\echo ''
\echo '--- 2. Ребро мапи з портами і живим завантаженням (джерело анімації) ---'
SELECT e.label,
       si.name AS source_port,
       ti.name AS target_port,
       lv.status,
       round(lv.util_pct::numeric,1) AS util_pct,
       e.animation->>'speed_source'  AS speed_source,
       e.thresholds->>'crit_pct'     AS crit_pct
FROM topo.map_edges e
LEFT JOIN inv.interfaces si ON si.id = e.source_interface_id
LEFT JOIN inv.interfaces ti ON ti.id = e.target_interface_id
LEFT JOIN topo.link_live lv ON lv.link_id = e.link_id
WHERE e.map_id = '88888888-0000-0000-0000-000000000001';

\echo ''
\echo '--- 3. Continuous aggregate: 5-хвилинні роллапи трафіку ---'
CALL refresh_continuous_aggregate('ts.if_counters_5m', now() - interval '2 hours', now());
SELECT bucket, round(in_bps_avg::numeric/1e6,1) AS in_mbps, round(util_out_max::numeric,1) AS util_out_max
FROM ts.if_counters_5m
WHERE interface_id = '66666666-0000-0000-0000-000000000001'
ORDER BY bucket DESC LIMIT 5;

\echo ''
\echo '--- 4. Ліміт пристроїв тарифу Free (15) ---'
DO $$
DECLARE i int;
BEGIN
  FOR i IN 4..15 LOOP
    INSERT INTO inv.devices (tenant_id, name, address, kind)
    VALUES ('11111111-1111-1111-1111-111111111111', 'filler-' || i,
            ('10.0.1.' || i)::inet, 'other');
  END LOOP;
  BEGIN
    INSERT INTO inv.devices (tenant_id, name, address, kind)
    VALUES ('11111111-1111-1111-1111-111111111111', 'over-limit', '10.0.9.9', 'other');
    RAISE EXCEPTION 'FAIL: 16-й пристрій створився попри ліміт 15';
  EXCEPTION WHEN check_violation THEN
    RAISE NOTICE 'PASS: 16-й пристрій відхилено тригером bill.assert_device_limit';
  END;
END $$;

\echo ''
\echo '--- 5. RLS: ізоляція тенантів (від імені netpulse_app) ---'
SET ROLE netpulse_app;

SET app.tenant_id = '11111111-1111-1111-1111-111111111111';
SELECT 'acme бачить пристроїв' AS check, count(*) FROM inv.devices;
SELECT 'acme бачить мап'       AS check, count(*) FROM topo.maps;

SET app.tenant_id = '22222222-2222-2222-2222-222222222222';
SELECT 'globex бачить пристроїв ACME' AS check, count(*) FROM inv.devices;
SELECT 'globex бачить мап ACME'       AS check, count(*) FROM topo.maps;

RESET app.tenant_id;
SELECT 'без app.tenant_id бачить пристроїв' AS check, count(*) FROM inv.devices;

RESET ROLE;

\echo ''
\echo '--- 6. Зведення по об''єктах схеми ---'
SELECT 'таблиць'              AS obj, count(*) FROM pg_tables
  WHERE schemaname IN ('core','inv','topo','ts','ncm','alr','bill')
UNION ALL SELECT 'hypertables', count(*) FROM timescaledb_information.hypertables
UNION ALL SELECT 'continuous aggregates', count(*) FROM timescaledb_information.continuous_aggregates
UNION ALL SELECT 'фонових job-ів', count(*) FROM timescaledb_information.jobs WHERE job_id >= 1000
UNION ALL SELECT 'RLS-політик', count(*) FROM pg_policies
  WHERE schemaname IN ('core','inv','topo','ts','ncm','alr','bill')
UNION ALL SELECT 'індексів', count(*) FROM pg_indexes
  WHERE schemaname IN ('core','inv','topo','ts','ncm','alr','bill');