-- ============================================================
--  ALTER v4 — Voucher Generator + ISP-Grade Live Monitoring
--  Tekeleza: mysql -u root -p hotspot_v3 < alter_v4.sql
-- ============================================================

-- 1. Batch tracking kwenye vouchers (Voucher Generator)
ALTER TABLE vouchers
  ADD COLUMN batch_id VARCHAR(20) NULL AFTER radius_synced,
  ADD COLUMN batch_month VARCHAR(7) NULL AFTER batch_id;

CREATE INDEX idx_vouchers_batch ON vouchers(admin_id, batch_id);
CREATE INDEX idx_vouchers_month ON vouchers(admin_id, batch_month);

-- 2. Live monitoring fields kwenye hotspot_sessions
ALTER TABLE hotspot_sessions
  ADD COLUMN device_type VARCHAR(30) DEFAULT NULL AFTER device_ip,
  ADD COLUMN last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER disconnected_at;

-- 3. Speed snapshots table (kwa kuhesabu Mbps ya sasa)
CREATE TABLE IF NOT EXISTS speed_snapshots (
  username     VARCHAR(60) PRIMARY KEY,
  bytes_down   BIGINT DEFAULT 0,
  bytes_up     BIGINT DEFAULT 0,
  snapshot_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT 'alter_v4.sql imetekelezwa — Voucher Generator + Live Monitor zimewezeshwa.' AS status;