XFEServerManager

CREATE TABLE IF NOT EXISTS moderation_cases (
    case_id TEXT PRIMARY KEY,
    subject_id TEXT NOT NULL,
    opened_by TEXT NOT NULL,
    opened_at INTEGER NOT NULL,
    reason_template TEXT NOT NULL,
    evidence_json TEXT NOT NULL DEFAULT '[]',
    staff_notes_json TEXT NOT NULL DEFAULT '[]',
    updated_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS moderation_cases_subject_idx
    ON moderation_cases(subject_id, opened_at DESC);

CREATE TABLE IF NOT EXISTS sanctions (
    action_id TEXT PRIMARY KEY,
    case_id TEXT NOT NULL REFERENCES moderation_cases(case_id) ON DELETE CASCADE,
    sanction_type TEXT NOT NULL
        CHECK (sanction_type IN ('WARN','MUTE','KICK','TEMPBAN','BAN')),
    reason TEXT NOT NULL,
    issued_by TEXT NOT NULL,
    issued_at INTEGER NOT NULL,
    expires_at INTEGER,
    revoked_by TEXT,
    revoked_at INTEGER
);

CREATE INDEX IF NOT EXISTS sanctions_case_idx ON sanctions(case_id, issued_at);
CREATE INDEX IF NOT EXISTS sanctions_expiry_idx ON sanctions(expires_at, revoked_at);

CREATE TABLE IF NOT EXISTS maintenance_state (
    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
    enabled INTEGER NOT NULL CHECK (enabled IN (0, 1)),
    message TEXT NOT NULL,
    allowed_players_json TEXT NOT NULL DEFAULT '[]',
    revision INTEGER NOT NULL DEFAULT 0,
    changed_by TEXT NOT NULL,
    changed_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS scheduled_jobs (
    job_id TEXT PRIMARY KEY,
    execute_at INTEGER NOT NULL,
    action_type TEXT NOT NULL,
    action_json TEXT NOT NULL,
    created_by TEXT NOT NULL,
    status TEXT NOT NULL,
    attempt INTEGER NOT NULL DEFAULT 0,
    lease_until INTEGER,
    completed_at INTEGER,
    failure TEXT
);

CREATE INDEX IF NOT EXISTS scheduled_jobs_due_idx
    ON scheduled_jobs(status, execute_at);

CREATE TABLE IF NOT EXISTS claim_snapshots (
    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
    revision INTEGER NOT NULL,
    snapshot_json TEXT NOT NULL,
    updated_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS rollback_checkpoints (
    operation_id TEXT PRIMARY KEY,
    status TEXT NOT NULL,
    cursor INTEGER NOT NULL,
    operation_json TEXT NOT NULL,
    updated_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS rollback_checkpoints_incomplete_idx
    ON rollback_checkpoints(status, updated_at);