PRAGMA foreign_keys = ON;

-- ============ 用户 / 会话 / 设置 ============
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 'admin',          -- admin | viewer
    created_at    TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS sessions (
    id          TEXT PRIMARY KEY,                         -- 32 字节随机 base64url
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    csrf_token  TEXT NOT NULL,
    ip_addr     TEXT,
    user_agent  TEXT,
    expires_at  TEXT NOT NULL,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS settings (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

-- ============ 服务器节点 ============
CREATE TABLE IF NOT EXISTS nodes (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    name              TEXT NOT NULL UNIQUE,
    is_local          INTEGER NOT NULL DEFAULT 0,          -- 1 = Master 本机
    addr              TEXT,
    public_host       TEXT,                                -- 对外域名/IP，用于生成订阅
    api_token         TEXT NOT NULL,
    enabled           INTEGER NOT NULL DEFAULT 1,
    meta              TEXT,                                -- JSON：地区、运营商、备注
    version           TEXT,
    last_heartbeat_at TEXT,
    last_load         TEXT,                                -- JSON：{cpu,mem,conns}
    online_state      TEXT NOT NULL DEFAULT 'unknown',     -- unknown | online | offline（离线告警用）
    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at        TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============ 分组 / 标签 / 输出模板 ============
CREATE TABLE IF NOT EXISTS entry_groups (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT NOT NULL UNIQUE,
    color       TEXT NOT NULL DEFAULT '#3b82f6',
    description TEXT,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS tags (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL UNIQUE,
    color      TEXT NOT NULL DEFAULT '#64748b',
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS sub_templates (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT NOT NULL UNIQUE,
    description TEXT,
    format      TEXT NOT NULL,                             -- clash | singbox
    content     TEXT NOT NULL,                             -- Clash=YAML，Sing-box=JSON，含 @ 标记
    is_builtin  INTEGER NOT NULL DEFAULT 0,                -- 内置模板不可删除/修改，可复制
    created_at  TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============ 导入记录（必须先于条目表创建） ============
CREATE TABLE IF NOT EXISTS import_records (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id        INTEGER REFERENCES users(id) ON DELETE SET NULL,
    source_url     TEXT,
    source_type    TEXT NOT NULL,                          -- clash | singbox | base64 | uri_list | paste
    target_node_id INTEGER REFERENCES nodes(id) ON DELETE SET NULL,
    parsed_count   INTEGER NOT NULL DEFAULT 0,
    imported_count INTEGER NOT NULL DEFAULT 0,
    created_at     TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============ 三类条目 ============
CREATE TABLE IF NOT EXISTS proxy_rules (                   -- 落地代理
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    node_id          INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    name             TEXT NOT NULL,
    listen_addr      TEXT,                                 -- 本地监听，可空（纯导入条目无需监听）
    upstream_type    TEXT NOT NULL,                        -- http | https | socks5 | ss | vmess | vless | trojan | hysteria2 | tuic ...
    upstream_addr    TEXT NOT NULL,                        -- host:port
    auth_user        TEXT,
    auth_pass        TEXT,
    export_host      TEXT,                                 -- 订阅导出地址；空则用 upstream 的 host
    export_port      INTEGER,
    extra            TEXT,                                 -- JSON：原始节点完整配置（Clash 风格字典）
    source_import_id INTEGER REFERENCES import_records(id) ON DELETE SET NULL,
    group_id         INTEGER REFERENCES entry_groups(id) ON DELETE SET NULL,
    expires_at       TEXT,                                 -- UTC；NULL = 不过期
    enabled          INTEGER NOT NULL DEFAULT 1,
    disabled_reason  TEXT,                                 -- manual | expired | quota
    remark           TEXT,
    created_at       TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at       TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS port_forwards (                 -- 端口转发
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    node_id          INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    name             TEXT NOT NULL,
    listen_ip        TEXT NOT NULL DEFAULT '0.0.0.0',
    listen_port      INTEGER NOT NULL,
    target_ip        TEXT NOT NULL,
    target_port      INTEGER NOT NULL,
    protocol         TEXT NOT NULL DEFAULT 'tcp',          -- tcp | udp | both
    export_host      TEXT,
    export_port      INTEGER,
    extra            TEXT,
    source_import_id INTEGER REFERENCES import_records(id) ON DELETE SET NULL,
    group_id         INTEGER REFERENCES entry_groups(id) ON DELETE SET NULL,
    expires_at       TEXT,
    enabled          INTEGER NOT NULL DEFAULT 1,
    disabled_reason  TEXT,
    remark           TEXT,
    created_at       TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at       TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(node_id, listen_ip, listen_port, protocol)
);

CREATE TABLE IF NOT EXISTS tunnels (                       -- 中转隧道
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    node_id          INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    name             TEXT NOT NULL,
    tunnel_type      TEXT NOT NULL,                        -- tcp | ws | wss | reverse
    local_addr       TEXT NOT NULL,
    remote_addr      TEXT NOT NULL,
    token            TEXT,
    export_host      TEXT,
    export_port      INTEGER,
    extra            TEXT,
    source_import_id INTEGER REFERENCES import_records(id) ON DELETE SET NULL,
    group_id         INTEGER REFERENCES entry_groups(id) ON DELETE SET NULL,
    expires_at       TEXT,
    enabled          INTEGER NOT NULL DEFAULT 1,
    disabled_reason  TEXT,
    remark           TEXT,
    created_at       TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at       TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============ 多态关联表（删除条目时由应用层同事务清理） ============
CREATE TABLE IF NOT EXISTS entry_tags (
    entry_type TEXT NOT NULL,                              -- proxy | forward | tunnel
    entry_id   INTEGER NOT NULL,
    tag_id     INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (entry_type, entry_id, tag_id)
);

CREATE TABLE IF NOT EXISTS entry_quotas (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    entry_type   TEXT NOT NULL,
    entry_id     INTEGER NOT NULL,
    quota_bytes  INTEGER NOT NULL,                         -- 0 = 无限制（等同于删除配额）
    used_bytes   INTEGER NOT NULL DEFAULT 0,
    period       TEXT NOT NULL DEFAULT 'total',            -- total | monthly
    reset_day    INTEGER NOT NULL DEFAULT 1,               -- monthly 时每月几号重置，1~28
    period_start TEXT NOT NULL DEFAULT (datetime('now')),
    warn_level   INTEGER NOT NULL DEFAULT 0,               -- 本周期已发出的最高预警百分比
    status       TEXT NOT NULL DEFAULT 'ok',               -- ok | exceeded
    exceeded_at  TEXT,
    created_at   TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at   TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE (entry_type, entry_id)
);

-- ============ 日志 / 统计 / 监控 ============
CREATE TABLE IF NOT EXISTS audit_logs (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    actor_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    actor_name    TEXT,                                    -- 系统动作为 'system'
    action        TEXT NOT NULL,
    resource_type TEXT,
    resource_id   INTEGER,
    node_id       INTEGER,
    detail        TEXT,                                    -- JSON
    ip_addr       TEXT,
    created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS access_logs (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    node_id    INTEGER NOT NULL,
    rule_type  TEXT NOT NULL,                              -- proxy | forward | tunnel
    rule_id    INTEGER NOT NULL,
    client_ip  TEXT,
    started_at TEXT NOT NULL,
    ended_at   TEXT,
    bytes_up   INTEGER NOT NULL DEFAULT 0,
    bytes_down INTEGER NOT NULL DEFAULT 0,
    status     TEXT
);

CREATE TABLE IF NOT EXISTS stats_hourly (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stat_time TEXT NOT NULL,                               -- 2026-09-30T14
    node_id INTEGER NOT NULL,
    rule_type TEXT NOT NULL,
    rule_id INTEGER NOT NULL,
    total_connections INTEGER NOT NULL DEFAULT 0,
    total_bytes_up INTEGER NOT NULL DEFAULT 0,
    total_bytes_down INTEGER NOT NULL DEFAULT 0,
    UNIQUE(stat_time, node_id, rule_type, rule_id)
);

CREATE TABLE IF NOT EXISTS stats_daily (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stat_date TEXT NOT NULL,                               -- 2026-09-30
    node_id INTEGER NOT NULL,
    rule_type TEXT NOT NULL,
    rule_id INTEGER NOT NULL,
    total_connections INTEGER NOT NULL DEFAULT 0,
    total_bytes_up INTEGER NOT NULL DEFAULT 0,
    total_bytes_down INTEGER NOT NULL DEFAULT 0,
    UNIQUE(stat_date, node_id, rule_type, rule_id)
);

CREATE TABLE IF NOT EXISTS metrics_snapshots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    node_id INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    collected_at TEXT NOT NULL,
    cpu REAL, mem REAL, conns INTEGER, net_up_bps INTEGER, net_down_bps INTEGER
);

-- ============ 订阅 ============
CREATE TABLE IF NOT EXISTS subscriptions (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER REFERENCES users(id) ON DELETE SET NULL,
    token           TEXT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    description     TEXT,
    scope           TEXT NOT NULL DEFAULT 'custom',        -- single | custom | all | rule
    default_format  TEXT NOT NULL DEFAULT 'clash',         -- clash | singbox | base64 | json
    template_id     INTEGER REFERENCES sub_templates(id) ON DELETE SET NULL,
    enabled         INTEGER NOT NULL DEFAULT 1,
    disabled_reason TEXT,                                  -- manual | expired
    expires_at      TEXT,                                  -- NULL = 永久；UTC
    max_ips         INTEGER,                               -- 24h 内不同来源 IP 数上限，NULL = 不限
    access_count    INTEGER NOT NULL DEFAULT 0,
    last_access_at  TEXT,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS subscription_entries (          -- 手工勾选的条目（single / custom，及 rule 的例外项）
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    subscription_id INTEGER NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
    entry_type      TEXT NOT NULL,
    entry_id        INTEGER NOT NULL,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(subscription_id, entry_type, entry_id)
);

CREATE TABLE IF NOT EXISTS subscription_rules (            -- 动态规则
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    subscription_id INTEGER NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
    kind            TEXT NOT NULL,                         -- group | tag | node | type
    value           TEXT NOT NULL,                         -- group/tag 存 id；node 存 node_id；type 存 proxy|forward|tunnel
    mode            TEXT NOT NULL DEFAULT 'include',       -- include | exclude
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS subscription_logs (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    subscription_id INTEGER NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
    client_ip       TEXT,
    user_agent      TEXT,
    format          TEXT,
    result          TEXT NOT NULL,                         -- ok | expired | disabled | ip_limit
    accessed_at     TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============ 告警 ============
CREATE TABLE IF NOT EXISTS alert_channels (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL UNIQUE,
    kind       TEXT NOT NULL,                              -- webhook | telegram | email
    config     TEXT NOT NULL,                              -- JSON，见第 14 章
    enabled    INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS alert_rules (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    name          TEXT NOT NULL,
    event_type    TEXT NOT NULL,                           -- 见 14.3 事件清单
    params        TEXT NOT NULL DEFAULT '{}',              -- JSON 阈值参数
    channel_ids   TEXT NOT NULL DEFAULT '[]',              -- JSON 数组
    cooldown_secs INTEGER NOT NULL DEFAULT 3600,
    enabled       INTEGER NOT NULL DEFAULT 1,
    created_at    TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS alert_events (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    rule_id       INTEGER REFERENCES alert_rules(id) ON DELETE SET NULL,
    event_type    TEXT NOT NULL,
    dedup_key     TEXT NOT NULL,
    severity      TEXT NOT NULL DEFAULT 'warning',         -- info | warning | critical
    title         TEXT NOT NULL,
    body          TEXT NOT NULL,
    resource_type TEXT,
    resource_id   INTEGER,
    status        TEXT NOT NULL DEFAULT 'pending',         -- pending | sent | failed
    attempts      INTEGER NOT NULL DEFAULT 0,
    next_retry_at TEXT,
    last_error    TEXT,
    created_at    TEXT NOT NULL DEFAULT (datetime('now')),
    sent_at       TEXT
);

-- ============ 索引 ============
CREATE INDEX IF NOT EXISTS idx_sessions_exp      ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_proxy_node        ON proxy_rules(node_id);
CREATE INDEX IF NOT EXISTS idx_proxy_import      ON proxy_rules(source_import_id);
CREATE INDEX IF NOT EXISTS idx_proxy_group       ON proxy_rules(group_id);
CREATE INDEX IF NOT EXISTS idx_proxy_exp         ON proxy_rules(expires_at)   WHERE expires_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_forward_node      ON port_forwards(node_id);
CREATE INDEX IF NOT EXISTS idx_forward_import    ON port_forwards(source_import_id);
CREATE INDEX IF NOT EXISTS idx_forward_group     ON port_forwards(group_id);
CREATE INDEX IF NOT EXISTS idx_forward_exp       ON port_forwards(expires_at) WHERE expires_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_tunnel_node       ON tunnels(node_id);
CREATE INDEX IF NOT EXISTS idx_tunnel_import     ON tunnels(source_import_id);
CREATE INDEX IF NOT EXISTS idx_tunnel_group      ON tunnels(group_id);
CREATE INDEX IF NOT EXISTS idx_tunnel_exp        ON tunnels(expires_at)       WHERE expires_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_entry_tags_tag    ON entry_tags(tag_id);
CREATE INDEX IF NOT EXISTS idx_audit_created     ON audit_logs(created_at);
CREATE INDEX IF NOT EXISTS idx_audit_action      ON audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_access_rule       ON access_logs(rule_type, rule_id, started_at);
CREATE INDEX IF NOT EXISTS idx_access_node_time  ON access_logs(node_id, started_at);
CREATE INDEX IF NOT EXISTS idx_stats_h_time      ON stats_hourly(stat_time);
CREATE INDEX IF NOT EXISTS idx_stats_d_date      ON stats_daily(stat_date);
CREATE INDEX IF NOT EXISTS idx_metrics_node      ON metrics_snapshots(node_id, collected_at);
CREATE INDEX IF NOT EXISTS idx_sub_token         ON subscriptions(token);
CREATE INDEX IF NOT EXISTS idx_sub_entries_sub   ON subscription_entries(subscription_id);
CREATE INDEX IF NOT EXISTS idx_sub_rules_sub     ON subscription_rules(subscription_id);
CREATE INDEX IF NOT EXISTS idx_sub_logs_sub      ON subscription_logs(subscription_id, accessed_at);
CREATE INDEX IF NOT EXISTS idx_alert_ev_status   ON alert_events(status, next_retry_at);
CREATE INDEX IF NOT EXISTS idx_alert_ev_dedup    ON alert_events(rule_id, dedup_key, created_at);