-- Shifa MikroTik Dashboard — Phase 2 database migration
-- Run this once in cPanel > phpMyAdmin > your existing dashboard database.
-- The tables are snapshots replaced atomically by each successful router sync.

CREATE TABLE IF NOT EXISTS hotspot_users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    router_id VARCHAR(64) NOT NULL,
    username VARCHAR(128) NOT NULL,
    profile VARCHAR(128) NULL,
    disabled TINYINT(1) NOT NULL DEFAULT 0,
    comment VARCHAR(255) NULL,
    limit_uptime VARCHAR(64) NULL,
    server_name VARCHAR(128) NULL,
    synced_at DATETIME NOT NULL,
    UNIQUE KEY uq_hotspot_router_user (router_id, username),
    KEY idx_hotspot_profile (router_id, profile),
    CONSTRAINT fk_hotspot_router FOREIGN KEY (router_id)
        REFERENCES routers(router_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ppp_secrets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    router_id VARCHAR(64) NOT NULL,
    username VARCHAR(128) NOT NULL,
    service VARCHAR(32) NULL,
    profile VARCHAR(128) NULL,
    disabled TINYINT(1) NOT NULL DEFAULT 0,
    comment VARCHAR(255) NULL,
    caller_id VARCHAR(64) NULL,
    local_address VARCHAR(64) NULL,
    remote_address VARCHAR(64) NULL,
    limit_uptime VARCHAR(64) NULL,
    synced_at DATETIME NOT NULL,
    UNIQUE KEY uq_ppp_router_user (router_id, username),
    KEY idx_ppp_profile (router_id, profile),
    CONSTRAINT fk_ppp_router FOREIGN KEY (router_id)
        REFERENCES routers(router_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hotspot_active_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    router_id VARCHAR(64) NOT NULL,
    username VARCHAR(128) NOT NULL,
    address VARCHAR(64) NULL,
    mac_address VARCHAR(32) NULL,
    uptime VARCHAR(64) NULL,
    bytes_in BIGINT UNSIGNED NOT NULL DEFAULT 0,
    bytes_out BIGINT UNSIGNED NOT NULL DEFAULT 0,
    server_name VARCHAR(128) NULL,
    synced_at DATETIME NOT NULL,
    KEY idx_hotspot_active_router (router_id),
    KEY idx_hotspot_active_user (router_id, username),
    CONSTRAINT fk_hotspot_active_router FOREIGN KEY (router_id)
        REFERENCES routers(router_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ppp_active_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    router_id VARCHAR(64) NOT NULL,
    username VARCHAR(128) NOT NULL,
    service VARCHAR(32) NULL,
    caller_id VARCHAR(64) NULL,
    address VARCHAR(64) NULL,
    uptime VARCHAR(64) NULL,
    encoding VARCHAR(32) NULL,
    session_id VARCHAR(128) NULL,
    bytes_in BIGINT UNSIGNED NOT NULL DEFAULT 0,
    bytes_out BIGINT UNSIGNED NOT NULL DEFAULT 0,
    synced_at DATETIME NOT NULL,
    KEY idx_ppp_active_router (router_id),
    KEY idx_ppp_active_user (router_id, username),
    CONSTRAINT fk_ppp_active_router FOREIGN KEY (router_id)
        REFERENCES routers(router_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;