CREATE TABLE users (
    id                  INTEGER PRIMARY KEY,
    telegram_username   TEXT,
    first_name          TEXT NOT NULL,
    role                TEXT NOT NULL DEFAULT 'user'
                        CHECK (role IN ('user', 'admin', 'banned')),
    accepted_terms_at   DATETIME,
    created_at          DATETIME NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE contents (
    id                  TEXT PRIMARY KEY,
    user_id             INTEGER NOT NULL REFERENCES users(id),
    status              TEXT NOT NULL DEFAULT 'active'
                        CHECK (status IN ('staged', 'active', 'deleted', 'blacklisted')),
    view_count          INTEGER NOT NULL DEFAULT 0,
    max_views           INTEGER,
    allow_download      INTEGER NOT NULL DEFAULT 1,
    password_hash       TEXT,
    created_at          DATETIME NOT NULL DEFAULT (datetime('now')),
    deleted_at          DATETIME
);

CREATE TABLE content_files (
    content_id              TEXT NOT NULL REFERENCES contents(id),
    file_index              INTEGER NOT NULL DEFAULT 0,
    original_name           TEXT NOT NULL,
    stored_path             TEXT NOT NULL UNIQUE,
    mime_type               TEXT NOT NULL,
    size_bytes              INTEGER NOT NULL,
    ciphertext_size_bytes   INTEGER NOT NULL,
    encrypted_key_wrapped   BLOB NOT NULL,
    encrypted_hash          BLOB NOT NULL,
    render_flags            INTEGER NOT NULL DEFAULT 0,
    created_at              DATETIME NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (content_id, file_index)
);

CREATE TABLE reports (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    content_id      TEXT NOT NULL REFERENCES contents(id),
    reporter_user_id INTEGER NOT NULL REFERENCES users(id),
    reason          TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'open'
                    CHECK (status IN ('open', 'dismissed', 'actioned')),
    created_at      DATETIME NOT NULL DEFAULT (datetime('now')),
    resolved_at     DATETIME,
    resolver_id     INTEGER REFERENCES users(id)
);

CREATE TABLE admin_actions (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_user_id   INTEGER NOT NULL REFERENCES users(id),
    target_type     TEXT NOT NULL CHECK (target_type IN ('content', 'user')),
    target_id       TEXT NOT NULL,
    action          TEXT NOT NULL CHECK (action IN ('delete', 'blacklist', 'ignore')),
    created_at      DATETIME NOT NULL DEFAULT (datetime('now'))
);