-- Wormhole schema (also applied via GORM AutoMigrate)

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'user',
    status INTEGER NOT NULL DEFAULT 1,
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE IF NOT EXISTS clients (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    verify_key TEXT NOT NULL UNIQUE,
    owner_user_id INTEGER NOT NULL,
    rate_limit INTEGER DEFAULT 0,
    max_conn INTEGER DEFAULT 0,
    expire_at DATETIME,
    status INTEGER NOT NULL DEFAULT 1,
    inlet_flow INTEGER DEFAULT 0,
    export_flow INTEGER DEFAULT 0,
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE IF NOT EXISTS tunnels (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    name TEXT,
    mode TEXT NOT NULL,
    listen_ip TEXT DEFAULT '0.0.0.0',
    listen_port INTEGER NOT NULL,
    target_addr TEXT NOT NULL,
    status INTEGER NOT NULL DEFAULT 1,
    run_status INTEGER NOT NULL DEFAULT 0,
    inlet_flow INTEGER DEFAULT 0,
    export_flow INTEGER DEFAULT 0,
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE IF NOT EXISTS hosts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    host TEXT NOT NULL,
    location TEXT DEFAULT '/',
    scheme TEXT DEFAULT 'all',
    target_addr TEXT NOT NULL,
    cert_path TEXT,
    key_path TEXT,
    auto_https INTEGER DEFAULT 0,
    status INTEGER NOT NULL DEFAULT 1,
    inlet_flow INTEGER DEFAULT 0,
    export_flow INTEGER DEFAULT 0,
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE IF NOT EXISTS ip_rules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    scope TEXT NOT NULL,
    resource_id INTEGER DEFAULT 0,
    type TEXT NOT NULL,
    pattern TEXT NOT NULL,
    pattern_kind TEXT NOT NULL DEFAULT 'exact',
    priority INTEGER DEFAULT 0,
    remark TEXT,
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE IF NOT EXISTS auth_policies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    resource_type TEXT NOT NULL,
    resource_id INTEGER NOT NULL,
    type TEXT NOT NULL,
    password TEXT,
    callback_url TEXT,
    enabled INTEGER NOT NULL DEFAULT 1,
    ttl_seconds INTEGER DEFAULT 7200,
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE IF NOT EXISTS flow_stats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    resource_type TEXT NOT NULL,
    resource_id INTEGER NOT NULL,
    inlet_bytes INTEGER DEFAULT 0,
    export_bytes INTEGER DEFAULT 0,
    updated_at DATETIME,
    UNIQUE(resource_type, resource_id)
);

CREATE TABLE IF NOT EXISTS request_ips (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    resource_type TEXT NOT NULL,
    resource_id INTEGER NOT NULL,
    ip TEXT NOT NULL,
    hit_count INTEGER DEFAULT 0,
    last_seen_at DATETIME,
    UNIQUE(resource_type, resource_id, ip)
);

CREATE TABLE IF NOT EXISTS sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token TEXT NOT NULL UNIQUE,
    user_id INTEGER NOT NULL,
    expires_at DATETIME,
    created_at DATETIME
);