5ducky/migrations/0001_init.sql

-- 4ducky schema, ported from the original MySQL dump (legacy/dumb.sql).
-- Times are unix timestamps (seconds) unless noted.

CREATE TABLE users (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    registered  INTEGER NOT NULL,
    name        TEXT    NOT NULL UNIQUE COLLATE NOCASE,
    pass        TEXT    NOT NULL,              -- argon2id PHC string
    valid       INTEGER NOT NULL DEFAULT 1,
    admin       INTEGER NOT NULL DEFAULT 0,
    score       INTEGER NOT NULL DEFAULT 0,
    images      INTEGER NOT NULL DEFAULT 0,
    avatar      TEXT    NOT NULL DEFAULT 'data/avatars/default.png',
    website     TEXT    NOT NULL DEFAULT '',
    email       TEXT    NOT NULL DEFAULT '' COLLATE NOCASE,
    color       TEXT    NOT NULL DEFAULT '0',
    style       TEXT    NOT NULL DEFAULT 'black',
    spamblocked INTEGER NOT NULL DEFAULT 0,
    time        INTEGER NOT NULL DEFAULT 0,   -- spam block until (0 = permanent)
    spam_reason TEXT    NOT NULL DEFAULT '',
    invite      TEXT    NOT NULL DEFAULT ''
);
CREATE INDEX users_email ON users(email);

CREATE TABLE images (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    logged   INTEGER NOT NULL,
    views    INTEGER NOT NULL DEFAULT 0,
    user     INTEGER NOT NULL,
    score    REAL    NOT NULL DEFAULT 0,
    votes    INTEGER NOT NULL DEFAULT 0,
    keyword  TEXT    NOT NULL UNIQUE,          -- YYYY/MM/slug
    tags     TEXT    NOT NULL DEFAULT '',
    image    TEXT    NOT NULL,                 -- file name inside data/images/YYYY/MM/
    thumb    TEXT    NOT NULL,                 -- file name inside data/thumbs/YYYY/MM/<size>/
    hash     TEXT    NOT NULL DEFAULT '',
    source   TEXT    NOT NULL DEFAULT '',
    fame     INTEGER NOT NULL DEFAULT 0,
    comment  INTEGER NOT NULL DEFAULT 0        -- 1 = thread closed
);
CREATE INDEX images_user ON images(user);
CREATE INDEX images_hash ON images(hash);
CREATE INDEX images_fame ON images(fame);

CREATE TABLE imagecolors (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    imageId INTEGER NOT NULL,
    r       INTEGER NOT NULL,
    g       INTEGER NOT NULL,
    b       INTEGER NOT NULL
);
CREATE INDEX imagecolors_image ON imagecolors(imageId);

CREATE TABLE comments (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    imageId INTEGER NOT NULL,
    userId  INTEGER NOT NULL,
    created INTEGER NOT NULL,
    content TEXT    NOT NULL,
    thumb   INTEGER NOT NULL DEFAULT 0,
    c_like  INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX comments_image ON comments(imageId);
CREATE INDEX comments_user ON comments(userId, created);
CREATE INDEX comments_created ON comments(created);

CREATE TABLE commentuploads (
    c_id       INTEGER PRIMARY KEY,
    image_link TEXT NOT NULL,
    thumb_link TEXT NOT NULL
);

CREATE TABLE fav (
    imageId INTEGER NOT NULL,
    user    INTEGER NOT NULL,
    PRIMARY KEY (imageId, user)
);
CREATE INDEX fav_user ON fav(user);

CREATE TABLE abuse (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    imageid   INTEGER NOT NULL DEFAULT 0,
    reason    TEXT    NOT NULL,
    email     TEXT    NOT NULL,
    read      INTEGER NOT NULL DEFAULT 0,
    logged    INTEGER NOT NULL,
    imagelink TEXT    NOT NULL DEFAULT '',
    user      INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE banned (
    id     INTEGER PRIMARY KEY,
    reason TEXT NOT NULL DEFAULT ''
);

CREATE TABLE bannedimages (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    hash TEXT NOT NULL UNIQUE
);

-- The PHP version stored a serialized array of lowercased names per user.
CREATE TABLE blocked (
    user_id    INTEGER NOT NULL,   -- who blocks
    blocked_id INTEGER NOT NULL,   -- who is blocked
    PRIMARY KEY (user_id, blocked_id)
);

CREATE TABLE invites (
    id     INTEGER PRIMARY KEY AUTOINCREMENT,
    owner  INTEGER NOT NULL,
    code   TEXT    NOT NULL UNIQUE,
    used   INTEGER NOT NULL DEFAULT 0,
    usedby INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX invites_owner ON invites(owner);

CREATE TABLE messages (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    from_m    INTEGER NOT NULL,
    to_m      INTEGER NOT NULL,
    title_m   TEXT    NOT NULL DEFAULT '',
    message_m TEXT    NOT NULL DEFAULT '',
    read_m    INTEGER NOT NULL DEFAULT 0,
    created   INTEGER NOT NULL
);
CREATE INDEX messages_to ON messages(to_m, read_m);
CREATE INDEX messages_from ON messages(from_m);

CREATE TABLE registerlock (
    ip      TEXT    PRIMARY KEY,
    created INTEGER NOT NULL
);

CREATE TABLE settings (
    id       INTEGER PRIMARY KEY CHECK (id = 1),
    register INTEGER NOT NULL,
    upload   INTEGER NOT NULL,
    message  TEXT    NOT NULL
);
INSERT INTO settings (id, register, upload, message)
VALUES (1, 1, 1, 'Dolan: "Viel Spaß mit dem Script!"');

CREATE TABLE taglog (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    tagged  INTEGER NOT NULL,
    userId  INTEGER NOT NULL,
    imageId INTEGER NOT NULL,
    locked  INTEGER NOT NULL
);
CREATE INDEX taglog_image ON taglog(imageId);

CREATE TABLE unread (
    imageid   INTEGER NOT NULL,
    userid    INTEGER NOT NULL,
    keyword   TEXT    NOT NULL,
    createdby INTEGER NOT NULL,
    PRIMARY KEY (imageid, userid)
);
CREATE INDEX unread_user ON unread(userid);

CREATE TABLE uploadlock (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    ip   TEXT    NOT NULL,
    ts   INTEGER NOT NULL,
    user INTEGER NOT NULL
);
CREATE INDEX uploadlock_ip ON uploadlock(ip, ts);

-- New: server-side sessions (token stored as SHA-256 hash only).
CREATE TABLE sessions (
    id        TEXT    PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    created   INTEGER NOT NULL,
    expires   INTEGER NOT NULL,
    remember  INTEGER NOT NULL,
    last_seen INTEGER NOT NULL
);
CREATE INDEX sessions_user ON sessions(user_id);

-- New: self-hosted captcha challenges (replaces reCAPTCHA v1).
CREATE TABLE captchas (
    id      TEXT    PRIMARY KEY,
    answer  TEXT    NOT NULL,
    expires INTEGER NOT NULL
);