-- Traffic-control tier: mangle, accounting, and tc_* (device/class/filter/
-- interface/priority). Each is owned by a device. (Nested tc option structs on
-- tc_device/tc_class are deferred.)

CREATE TABLE mangle (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device      TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
    action      TEXT NOT NULL,
    chain       TEXT NOT NULL DEFAULT '',
    mark_value  TEXT NOT NULL DEFAULT '',
    source      TEXT NOT NULL DEFAULT '',
    dest        TEXT NOT NULL DEFAULT '',
    proto       TEXT NOT NULL DEFAULT '',
    dport       JSONB NOT NULL DEFAULT '[]'::jsonb,
    sport       JSONB NOT NULL DEFAULT '[]'::jsonb,
    "user"      TEXT NOT NULL DEFAULT '',
    mark        TEXT NOT NULL DEFAULT '',
    length      TEXT NOT NULL DEFAULT '',
    tos         TEXT NOT NULL DEFAULT '',
    helper      TEXT NOT NULL DEFAULT '',
    probability REAL,
    comment     TEXT NOT NULL DEFAULT ''
);

CREATE TABLE accounting (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device  TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
    action  TEXT NOT NULL,
    section TEXT NOT NULL DEFAULT '',
    chain   TEXT NOT NULL DEFAULT '',
    source  TEXT NOT NULL DEFAULT '',
    dest    TEXT NOT NULL DEFAULT '',
    proto   TEXT NOT NULL DEFAULT '',
    dport   JSONB NOT NULL DEFAULT '[]'::jsonb,
    sport   JSONB NOT NULL DEFAULT '[]'::jsonb,
    mark    TEXT NOT NULL DEFAULT '',
    comment TEXT NOT NULL DEFAULT ''
);

CREATE TABLE tc_devices (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device        TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
    interface     TEXT NOT NULL,
    in_bandwidth  TEXT NOT NULL DEFAULT '',
    out_bandwidth TEXT NOT NULL DEFAULT '',
    comment       TEXT NOT NULL DEFAULT ''
);

CREATE TABLE tc_classes (
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device    TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
    interface TEXT NOT NULL,
    mark      INT NOT NULL DEFAULT 0,
    rate      TEXT NOT NULL DEFAULT '',
    ceil      TEXT NOT NULL DEFAULT '',
    priority  INT NOT NULL DEFAULT 0,
    comment   TEXT NOT NULL DEFAULT ''
);

CREATE TABLE tc_filters (
    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device   TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
    class    TEXT NOT NULL,
    source   TEXT NOT NULL DEFAULT '',
    dest     TEXT NOT NULL DEFAULT '',
    proto    TEXT NOT NULL DEFAULT '',
    dport    JSONB NOT NULL DEFAULT '[]'::jsonb,
    sport    JSONB NOT NULL DEFAULT '[]'::jsonb,
    tos      TEXT NOT NULL DEFAULT '',
    length   INT NOT NULL DEFAULT 0,
    priority INT NOT NULL DEFAULT 0,
    comment  TEXT NOT NULL DEFAULT ''
);

CREATE TABLE tc_interfaces (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device        TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
    interface     TEXT NOT NULL,
    type          TEXT NOT NULL DEFAULT '',
    in_bandwidth  TEXT NOT NULL DEFAULT '',
    out_bandwidth TEXT NOT NULL DEFAULT '',
    comment       TEXT NOT NULL DEFAULT ''
);

CREATE TABLE tc_priorities (
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device    TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
    band      INT NOT NULL,
    proto     TEXT NOT NULL DEFAULT '',
    dport     JSONB NOT NULL DEFAULT '[]'::jsonb,
    sport     JSONB NOT NULL DEFAULT '[]'::jsonb,
    address   TEXT NOT NULL DEFAULT '',
    interface TEXT NOT NULL DEFAULT '',
    helper    TEXT NOT NULL DEFAULT '',
    comment   TEXT NOT NULL DEFAULT ''
);
