<?php
/**
 * Ensures traffic tables + optional columns exist.
 */

function ensure_traffic_tables(mysqli $conn): void
{
    // Snapshots for per-run raw values
    $conn->query(
        "CREATE TABLE IF NOT EXISTS traffic_snapshots (
            id INT AUTO_INCREMENT PRIMARY KEY,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            subscriber_id INT NULL,
            username VARCHAR(100) NULL,
            ip_address VARCHAR(64) NULL,
            rx_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
            tx_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
            source VARCHAR(32) NOT NULL DEFAULT 'simple_queue',
            note VARCHAR(255) NULL,
            KEY idx_created_at (created_at),
            KEY idx_subscriber_id (subscriber_id),
            KEY idx_ip (ip_address)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
    );

    // Aggregated daily totals (optional usage)
    $conn->query(
        "CREATE TABLE IF NOT EXISTS traffic_daily (
            day DATE NOT NULL,
            subscriber_id INT NOT NULL,
            username VARCHAR(100) NULL,
            ip_address VARCHAR(64) NULL,
            rx_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
            tx_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
            PRIMARY KEY (day, subscriber_id),
            KEY idx_day (day),
            KEY idx_ip (ip_address)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
    );

    // Add optional columns to mikrotik_settings for interface selection (WAN / subscribers)
    $cols = [];
    $res = $conn->query("SHOW COLUMNS FROM mikrotik_settings");
    if ($res) {
        while ($r = $res->fetch_assoc()) {
            $cols[$r['Field']] = true;
        }
    }
    if (!isset($cols['subscriber_interface'])) {
        $conn->query("ALTER TABLE mikrotik_settings ADD COLUMN subscriber_interface VARCHAR(64) NULL DEFAULT ''");
    }
    if (!isset($cols['wan_interface'])) {
        $conn->query("ALTER TABLE mikrotik_settings ADD COLUMN wan_interface VARCHAR(64) NULL DEFAULT ''");
    }
}
