-- JJServiceManagerRelay 스키마 v1

CREATE TABLE IF NOT EXISTS server_settings (
    key   text PRIMARY KEY,
    value text NOT NULL
);

CREATE TABLE IF NOT EXISTS users (
    id                   bigserial PRIMARY KEY,
    username             text NOT NULL,
    password_hash        text NOT NULL,
    role                 text NOT NULL CHECK (role IN ('admin', 'user')),
    must_change_password boolean NOT NULL DEFAULT false,
    enabled              boolean NOT NULL DEFAULT true,
    note                 text NOT NULL DEFAULT '',
    created_at           timestamptz NOT NULL DEFAULT now(),
    last_login_at        timestamptz
);
CREATE UNIQUE INDEX IF NOT EXISTS users_username_lower ON users (lower(username));

CREATE TABLE IF NOT EXISTS user_sessions (
    token_hash  bytea PRIMARY KEY,
    user_id     bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at  timestamptz NOT NULL DEFAULT now(),
    expires_at  timestamptz NOT NULL,
    client_ip   text,
    client_host text
);
CREATE INDEX IF NOT EXISTS user_sessions_user ON user_sessions (user_id);

CREATE TABLE IF NOT EXISTS devices (
    id             uuid PRIMARY KEY,
    name           text NOT NULL,
    hostname       text NOT NULL,
    key_hash       bytea NOT NULL,
    enabled        boolean NOT NULL DEFAULT true,
    note           text NOT NULL DEFAULT '',
    enrolled_at    timestamptz NOT NULL DEFAULT now(),
    enrolled_by    bigint REFERENCES users(id) ON DELETE SET NULL,
    last_seen_at   timestamptz,
    last_public_ip text,
    lan_ips        jsonb NOT NULL DEFAULT '[]',
    active_listen  jsonb,
    app_version    text,
    os_version     text
);

CREATE TABLE IF NOT EXISTS enrollment_codes (
    id          bigserial PRIMARY KEY,
    code_hash   bytea NOT NULL UNIQUE,
    created_by  bigint REFERENCES users(id) ON DELETE SET NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    expires_at  timestamptz NOT NULL,
    used_at     timestamptz,
    used_device uuid REFERENCES devices(id) ON DELETE SET NULL,
    revoked     boolean NOT NULL DEFAULT false,
    note        text NOT NULL DEFAULT ''
);

CREATE TABLE IF NOT EXISTS user_device_permissions (
    user_id            bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    device_id          uuid   NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    can_view           boolean NOT NULL DEFAULT true,
    can_control        boolean NOT NULL DEFAULT false,
    can_screen_view    boolean NOT NULL DEFAULT false,
    can_screen_control boolean NOT NULL DEFAULT false,
    PRIMARY KEY (user_id, device_id)
);

-- 감사 로그: 지우는 기능 없음
CREATE TABLE IF NOT EXISTS audit_logs (
    id          bigserial PRIMARY KEY,
    at          timestamptz NOT NULL DEFAULT now(),
    user_id     bigint,
    username    text NOT NULL DEFAULT '',
    device_id   uuid,
    device_name text NOT NULL DEFAULT '',
    action      text NOT NULL,
    target      text NOT NULL DEFAULT '',
    result      text NOT NULL,
    detail      text NOT NULL DEFAULT '',
    source_ip   text NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS audit_logs_at ON audit_logs (at DESC);
CREATE INDEX IF NOT EXISTS audit_logs_user ON audit_logs (user_id, at DESC);
CREATE INDEX IF NOT EXISTS audit_logs_device ON audit_logs (device_id, at DESC);

-- 연결 기록: 지우는 기능 없음
CREATE TABLE IF NOT EXISTS connections (
    id                bigserial PRIMARY KEY,
    session_id        uuid NOT NULL UNIQUE,
    user_id           bigint,
    username          text NOT NULL DEFAULT '',
    device_id         uuid,
    device_name       text NOT NULL DEFAULT '',
    started_at        timestamptz NOT NULL DEFAULT now(),
    ended_at          timestamptz,
    mode              text,
    bytes_to_device   bigint NOT NULL DEFAULT 0,
    bytes_from_device bigint NOT NULL DEFAULT 0,
    end_reason        text NOT NULL DEFAULT '',
    manager_ip        text NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS connections_started ON connections (started_at DESC);