?debugFmt("~nUsing DB Path: ~p~n", [TestDb]),
    {ok, Db} = db:start_link(TestDb),

    %?debugFmt("~nUsing DB Path: ~p~n", [TestDb]),
    {ok, Db} = db:open(TestDb),

        ConnInfo ->
            PeerAddr = maps:get(addr, ConnInfo),
            io:format(
                "[Transport] Sending ~p bytes to ~p via encrypted connection ~p~n",
                [byte_size(Binary), PeerAddr, ConnPid]
            ),

        _ConnInfo ->
            %% PeerAddr = maps:get(addr, ConnInfo),
            %% io:format(
            %%     "[Transport] Sending ~p bytes to ~p via encrypted connection ~p~n",
            %%     [byte_size(Binary), PeerAddr, ConnPid]
            %% ),

        ConnInfo ->
            PeerAddr = maps:get(addr, ConnInfo),
            io:format("[Transport] Received ~p bytes from ~p (conn: ~p)~n", [
                byte_size(Data), PeerAddr, ConnPid
            ]),

        _ConnInfo ->
            %% PeerAddr = maps:get(addr, ConnInfo),
            %% io:format("[Transport] Received ~p bytes from ~p (conn: ~p)~n", [
            %%     byte_size(Data), PeerAddr, ConnPid
            %% ]),

    {ok, Db} = db:start_link(StorePath),

    {ok, Db} = db:open(StorePath),

            io:format("[Wrote] #~p: ~p~n", [Chan, Text]),

            %io:format("[Wrote] #~p: ~p~n", [Chan, Text]),

        "[DEBUG] incoming message  ReqId:~p  Type:~p  Size:~p\tIsActiveOut:~p~n",

        "[Peer/Req  ~p] incoming: Type:~p  Size:~p\tIsActiveOut:~p~n",

        "[Peer] sending hash response(~p) for ~p hashes to conn ~p~n",

        "[Peer/Resp ~p] sending hash ~p hashes to conn ~p~n",

    io:format("[Peer] Sending ~p bytes to connection ~p~n", [byte_size(Binary), ConnPid]),

    %% io:format("[Peer] Sending ~p bytes to connection ~p~n", [byte_size(Binary), ConnPid]),

-behavior(gen_server).
-export([start_link/1, close/1, save_post/3, save_post/2, load_post/2]).
-export([has_post/2, get_text_hashes_by_time_range/5, get_texts_for_channel/2]).

-export([open/1, close/1]).
-export([save_post/3, save_post/2, load_post/2, has_post/2]).
-export([get_text_hashes_by_time_range/5, get_texts_for_channel/2]).

% gen_server things
-export([init/1, handle_call/3, handle_cast/2, handle_info/2, code_change/3, terminate/2]).

%% intended public api
close(Db) ->
    gen_server:stop(Db).

-include_lib("kernel/include/file.hrl").

%% read and write posts
save_post(Db, Post, Data) when is_binary(Data) ->
    gen_server:call(Db, {savePost, Post, Data}).
save_post(Db, Data) when is_binary(Data) ->
    Post = posts:decode(Data),
    gen_server:call(Db, {savePost, Post, Data}).
load_post(Db, IntId) when is_integer(IntId), IntId >= 0 ->
    gen_server:call(Db, {loadPostById, IntId});
load_post(Db, Hash) when is_binary(Hash) ->
    gen_server:call(Db, {loadPostByHash, Hash}).
has_post(Db, Hash) when is_binary(Hash) ->
    gen_server:call(Db, {hasPostByHash, Hash}).
%% TODO: time windows and limit
get_texts_for_channel(Db, Chan) ->
    gen_server:call(Db, {textsForChannel, Chan}).
get_text_hashes_by_time_range(Db, Chan, Start, End, Limit) ->
    ActualEnd =
        case End of
            %% 00:00:00 01.01.2180
            0 -> 6626966400000;
            _ -> End

open(Path) ->
    DbPath =
        case file:read_file_info(Path) of
            {ok, #file_info{type = regular}} -> Path;
            _ -> filename:join(Path, "sqlite.db")

    ActualLimit =
        case Limit of
            0 -> 9001;
            _ -> Limit
        end,
    gen_server:call(Db, {hashesByTimeRange, Chan, Start, ActualEnd, ActualLimit}).
%% manage our active channels
channels_join(Db, Name) ->
    gen_server:call(Db, {channelsJoin, Name}).
channels_leave(Db, Name) ->
    gen_server:call(Db, {channelsLeave, Name}).
channels_list(Db) ->
    gen_server:call(Db, {channelsList}).
channels_known(Db) ->
    gen_server:call(Db, {channelsKnown}).
%% returns a map of the members
get_channel_members(Db, Chan) ->
    gen_server:call(Db, {channelsMembers, Chan}).
%% helps with channel state requests
get_channel_state(Db, Chan) ->
    gen_server:call(Db, {channelsState, Chan}).
%% helps with creating links for new posts
get_channel_heads(Db, Chan) ->
    gen_server:call(Db, {channelsHeads, Chan}).
%% manage persistent peers
peer_add(Db, Address) ->
    gen_server:call(Db, {peerAdd, Address}).
peer_update(Db, Address, Updates) ->
    gen_server:call(Db, {peerUpdate, Address, Updates}).
peer_list(Db) ->
    gen_server:call(Db, {peerList}).
peer_delete(Db, Address) ->
    gen_server:call(Db, {peerDelete, Address}).
peer_get(Db, Address) ->
    gen_server:call(Db, {peerGet, Address}).
%% unfetched hash queries (internal - called by peer module only)
unfetched_get(Db, Channel, Limit) ->
    gen_server:call(Db, {unfetchedGet, Channel, Limit}).
unfetched_count(Db, Channel) ->
    gen_server:call(Db, {unfetchedCount, Channel}).
%% Note: unfetched_add and unfetched_delete are internal operations
%% called directly via gen_server:call from peer module, no wrapper functions
%% server things
init(Args) ->
    process_flag(trap_exit, true),
    Path = proplists:get_value(dbPath, Args),
    open_db(Path).
open_db(Path) ->
    {ok, Db} = sqlite3:open(list_to_atom(Path), [{file, Path}]),

    {ok, Db} = sqlite3:open(list_to_atom(DbPath), [{file, DbPath}]),

    io:format("[DB] Path: ~p~n", [Path]),

    io:format("[DB] Path: ~p~n", [DbPath]),

    {ok, [{sql, Db}, {dbPath, Path}]}.

    {ok, Db}.

start_link(DbFolder) ->
    DbPath = filename:join(DbFolder, "sqlite.db"),
    gen_server:start_link(?MODULE, [{dbPath, DbPath}], []).

close(Db) ->
    sqlite3:close(Db).

handle_call({savePost, Post, Binary}, _From, [{sql, Db}, _] = State) ->

save_post(Db, Data) when is_binary(Data) ->
    Post = posts:decode(Data),
    save_post(Db, Post, Data).
save_post(Db, Post, Data) when is_binary(Data) ->

        [{user_id, UserId}, {raw_post, Binary}] ++ HeaderTrimmed ++

        [{user_id, UserId}, {raw_post, Data}] ++ HeaderTrimmed ++

    {reply, {ok, Id, PostHash}, State};
handle_call({loadPostByHash, Hash}, _From, [{sql, Db}, _] = State) when is_binary(Hash) ->

    {ok, Id, PostHash}.
load_post(Db, IntId) when is_integer(IntId), IntId >= 0 ->
    Res = sqlite3:read(Db, posts, {id, IntId}),
    unpackPost(Db, Res);
load_post(Db, Hash) when is_binary(Hash) ->

    {reply, unpackPost(Db, Res), State};
handle_call({loadPostById, IntId}, _From, [{sql, Db}, _] = State) when
    is_integer(IntId), IntId >= 0
->
    Res = sqlite3:read(Db, posts, {id, IntId}),
    {reply, unpackPost(Db, Res), State};
handle_call({hasPostByHash, Hash}, _From, [{sql, Db}, _] = State) when is_binary(Hash) ->

    unpackPost(Db, Res).
has_post(Db, Hash) when is_binary(Hash) ->

    {reply, Result, State};
handle_call({textsForChannel, Chan}, _From, [{sql, Db}, _] = State) ->

    Result.
%% TODO: time windows and limit
get_texts_for_channel(Db, Chan) ->

    {reply, {ok, Result}, State};
handle_call({hashesByTimeRange, Chan, Start, End, Limit}, _From, [{sql, Db}, _] = State) ->

    {ok, Result}.
get_text_hashes_by_time_range(Db, Chan, Start, End, Limit) ->
    ActualEnd =
        case End of
            %% 00:00:00 01.01.2180
            0 -> 6626966400000;
            _ -> End
        end,
    ActualLimit =
        case Limit of
            0 -> 9001;
            _ -> Limit
        end,

    [{columns, ["hash"]}, {rows, Rows}] = sqlite3:sql_exec(Db, Qry, [QryChan, Start, End, Limit]),

    [{columns, ["hash"]}, {rows, Rows}] = sqlite3:sql_exec(Db, Qry, [
        QryChan,
        Start,
        ActualEnd,
        ActualLimit
    ]),

    {reply, {ok, Result}, State};

    {ok, Result}.

channels_join(Db, Name) ->
    case sqlite3:write(Db, channels, [{name, Name}, {topic, ""}]) of
        {rowid, _RowId} -> ok;
        %% constraint violation => already joined
        {error, 19, _Msg} -> ok
    end.

%% TODO: add tests
handle_call({channelsHeads, Chan}, _, [{sql, Db}, _] = State) ->
    Qry =
        "SELECT DISTINCT source FROM links " ++
            "WHERE source NOT IN (" ++
            "  SELECT DISTINCT parent FROM links" ++
            "    WHERE channel = ? or channel is null)",
    QryChan =
        case Chan of
            null -> null;
            _ when is_list(Chan) -> {blob, list_to_binary(Chan)}

channels_leave(Db, Name) ->
    sqlite3:delete(Db, channels, {name, Name}).
channels_list(Db) ->
    [{columns, ["name", "topic"]}, {rows, Rows}] = sqlite3:read_all(Db, channels),
    Chans = lists:map(
        fun({Name, _Topic}) ->
            binary_to_list(Name)

    [{columns, ["source"]}, {rows, Rows}] = sqlite3:sql_exec(Db, Qry, [QryChan]),
    Result = [Hash || {{blob, Hash}} <- Rows],
    {reply, {ok, Result}, State};
handle_call({channelsState, Chan}, _, [{sql, Db}, _] = State) ->
    Qry = "SELECT hash, user_id from posts where channel = ? and type > 2 order by timestamp",
    QryChan = {blob, list_to_binary(Chan)},
    [{columns, ["hash", "user_id"]}, {rows, Rows}] = sqlite3:sql_exec(Db, Qry, [QryChan]),
    JoinLeaveHashes = [Hash || {{blob, Hash}, _} <- Rows],

        Rows
    ),
    {ok, Chans}.

    %% add info posts for all users involved
    InfoPostQry =
        "SELECT hash from posts where user_id = ? and type = 2 order by timestamp desc limit 1",
    InfosHashes = lists:foldl(
        fun({_, UserId}, Acc) ->
            [
                {columns, ["hash"]},
                {rows, InfoRows}
            ] = sqlite3:sql_exec(Db, InfoPostQry, [UserId]),
            case InfoRows of
                [{{blob, H}}] -> sets:add_element(H, Acc);
                [] -> Acc
            end

channels_known(Db) ->
    %% Get all unique channels from posts table where channel is not null
    Query = "SELECT DISTINCT channel FROM posts WHERE channel IS NOT NULL ORDER BY channel",
    [{columns, ["channel"]}, {rows, Rows}] = sqlite3:sql_exec(Db, Query),
    Chans = lists:map(
        fun({ChannelData}) ->
            %% Handle both {blob, Bin} and plain Binary
            ChannelBin =
                case ChannelData of
                    {blob, B} -> B;
                    B when is_binary(B) -> B
                end,
            binary_to_list(ChannelBin)

        sets:new(),

    {ok, Chans}.

    Result = JoinLeaveHashes ++ sets:to_list(InfosHashes),
    {reply, {ok, Result}, State};

handle_call({channelsMembers, Chan}, _, [{sql, Db}, _] = State) ->

get_channel_members(Db, Chan) ->

    {reply, {ok, Result}, State};
handle_call({channelsLeave, Name}, _From, [{sql, Db}, _] = State) ->
    ok = sqlite3:delete(Db, channels, {name, Name}),
    {reply, ok, State};
handle_call({channelsJoin, Name}, _From, [{sql, Db}, _] = State) ->
    Res =
        case sqlite3:write(Db, channels, [{name, Name}, {topic, ""}]) of
            {rowid, _RowId} -> ok;
            %% constraint violation => already joined
            {error, 19, _Msg} -> ok
        end,
    {reply, Res, State};
handle_call({channelsList}, _From, [{sql, Db}, _] = State) ->
    [{columns, ["name", "topic"]}, {rows, Rows}] = sqlite3:read_all(Db, channels),
    Chans = lists:map(
        fun({Name, _Topic}) ->
            binary_to_list(Name)

    {ok, Result}.
%% helps with channel state requests
get_channel_state(Db, Chan) ->
    Qry = "SELECT hash, user_id from posts where channel = ? and type > 2 order by timestamp",
    QryChan = {blob, list_to_binary(Chan)},
    [{columns, ["hash", "user_id"]}, {rows, Rows}] = sqlite3:sql_exec(Db, Qry, [QryChan]),
    JoinLeaveHashes = [Hash || {{blob, Hash}, _} <- Rows],
    %% add info posts for all users involved
    InfoPostQry =
        "SELECT hash from posts where user_id = ? and type = 2 order by timestamp desc limit 1",
    InfosHashes = lists:foldl(
        fun({_, UserId}, Acc) ->
            [
                {columns, ["hash"]},
                {rows, InfoRows}
            ] = sqlite3:sql_exec(Db, InfoPostQry, [UserId]),
            case InfoRows of
                [{{blob, H}}] -> sets:add_element(H, Acc);
                [] -> Acc
            end

        sets:new(),

    %% io:format("[DEBUG] ChannelsList: ~p~n", [Chans]),
    {reply, {ok, Chans}, State};
handle_call({channelsKnown}, _From, [{sql, Db}, _] = State) ->
    %% Get all unique channels from posts table where channel is not null
    Query = "SELECT DISTINCT channel FROM posts WHERE channel IS NOT NULL ORDER BY channel",
    [{columns, ["channel"]}, {rows, Rows}] = sqlite3:sql_exec(Db, Query),
    Chans = lists:map(
        fun({ChannelData}) ->
            %% Handle both {blob, Bin} and plain Binary
            ChannelBin =
                case ChannelData of
                    {blob, B} -> B;
                    B when is_binary(B) -> B
                end,
            binary_to_list(ChannelBin)

    Result = JoinLeaveHashes ++ sets:to_list(InfosHashes),
    {ok, Result}.
%% helps with creating links for new posts
%% TODO: add tests
get_channel_heads(Db, Chan) ->
    Qry =
        "SELECT DISTINCT source FROM links " ++
            "WHERE source NOT IN (" ++
            "  SELECT DISTINCT parent FROM links" ++
            "    WHERE channel = ? or channel is null)",
    QryChan =
        case Chan of
            null -> null;
            _ when is_list(Chan) -> {blob, list_to_binary(Chan)}

        Rows
    ),
    {reply, {ok, Chans}, State};

    [{columns, ["source"]}, {rows, Rows}] = sqlite3:sql_exec(Db, Qry, [QryChan]),
    Result = [Hash || {{blob, Hash}} <- Rows],
    {ok, Result}.

handle_call({peerAdd, Address}, _From, [{sql, Db}, _] = State) ->

peer_add(Db, Address) ->

    Res =
        case sqlite3:write(Db, peers, Row) of
            {rowid, RowId} -> {ok, RowId};
            {error, 19, _Msg} -> {error, already_exists}
        end,
    {reply, Res, State};
handle_call({peerUpdate, Address, Updates}, _From, [{sql, Db}, _] = State) ->
    %% Updates is a proplist like [{score, 10}, {last_seen, Timestamp}]
    Res =
        case sqlite3:update(Db, peers, {address, Address}, Updates) of
            ok -> ok;
            Error -> {error, Error}
        end,
    {reply, Res, State};
handle_call({peerList}, _From, [{sql, Db}, _] = State) ->

    case sqlite3:write(Db, peers, Row) of
        {rowid, RowId} -> {ok, RowId};
        {error, 19, _Msg} -> {error, already_exists}
    end.
%% Updates is a proplist like [{score, 10}, {last_seen, Timestamp}]
peer_update(Db, Address, Updates) ->
    case sqlite3:update(Db, peers, {address, Address}, Updates) of
        ok -> ok;
        Error -> {error, Error}
    end.
peer_list(Db) ->

        fun({Id, Address, Score, LastSeen, LastAttempt, AttemptCount, CreatedAt, Notes}) ->
            #{
                id => Id,
                address => binary_to_list(Address),
                score =>
                    case Score of
                        null -> 0;
                        S -> S
                    end,
                last_seen =>
                    case LastSeen of
                        null -> 0;
                        LS -> LS
                    end,
                last_attempt =>
                    case LastAttempt of
                        null -> 0;
                        LA -> LA
                    end,
                attempt_count =>
                    case AttemptCount of
                        null -> 0;
                        AC -> AC
                    end,
                created_at => CreatedAt,
                notes =>
                    case Notes of
                        null -> undefined;
                        N -> binary_to_list(N)
                    end
            }
        end,

        unpack_peer,

    {reply, {ok, Peers}, State};
handle_call({peerDelete, Address}, _From, [{sql, Db}, _] = State) ->
    ok = sqlite3:delete(Db, peers, {address, Address}),
    {reply, ok, State};
handle_call({peerGet, Address}, _From, [{sql, Db}, _] = State) ->
    Result =
        case sqlite3:read(Db, peers, {address, Address}) of
            [
                {columns, _Cols},
                {rows, [{Id, Addr, Score, LastSeen, LastAttempt, AttemptCount, CreatedAt, Notes}]}
            ] ->
                {ok, #{
                    id => Id,
                    address => binary_to_list(Addr),
                    score =>
                        case Score of
                            null -> 0;
                            S -> S
                        end,
                    last_seen =>
                        case LastSeen of
                            null -> 0;
                            LS -> LS
                        end,
                    last_attempt =>
                        case LastAttempt of
                            null -> 0;
                            LA -> LA
                        end,
                    attempt_count =>
                        case AttemptCount of
                            null -> 0;
                            AC -> AC
                        end,
                    created_at => CreatedAt,
                    notes =>
                        case Notes of
                            null -> undefined;
                            N -> binary_to_list(N)
                        end
                }};
            _ ->
                not_found
        end,
    {reply, Result, State};

    {ok, Peers}.
peer_delete(Db, Address) ->
    sqlite3:delete(Db, peers, {address, Address}).
peer_get(Db, Address) ->
    case sqlite3:read(Db, peers, {address, Address}) of
        [
            {columns, _Cols},
            {rows, [Row]}
        ] ->
            {ok, unpack_peer(Row)};
        _ ->
            not_found
    end.

%% unfetched hash queries (internal - called by peer module only)
unfetched_get(Db, Channel, Limit) ->
    ChannelBin = list_to_binary(Channel),
    Query =
        "SELECT hash FROM unfetched_hashes WHERE channel = ? ORDER BY discovered_at ASC LIMIT ?",
    [{columns, ["hash"]}, {rows, Rows}] = sqlite3:sql_exec(Db, Query, [ChannelBin, Limit]),
    Hashes = [Hash || {{blob, Hash}} <- Rows],
    {ok, Hashes}.

handle_call({unfetchedAdd, Hashes, Channel, Source}, _From, [{sql, Db}, _] = State) ->

unfetched_count(Db, Channel) ->
    ChannelBin = list_to_binary(Channel),
    Query = "SELECT count(*) as count FROM unfetched_hashes WHERE channel = ?",
    [{columns, ["count"]}, {rows, [{Count}]}] = sqlite3:sql_exec(Db, Query, [ChannelBin]),
    {ok, Count}.
unfetched_add(Db, Hashes, Channel, Source) ->

    {reply, {ok, Results}, State};
handle_call({unfetchedGet, Channel, Limit}, _From, [{sql, Db}, _] = State) ->
    ChannelBin = list_to_binary(Channel),
    Query =
        "SELECT hash FROM unfetched_hashes WHERE channel = ? ORDER BY discovered_at ASC LIMIT ?",
    [{columns, ["hash"]}, {rows, Rows}] = sqlite3:sql_exec(Db, Query, [ChannelBin, Limit]),
    Hashes = [Hash || {{blob, Hash}} <- Rows],
    {reply, {ok, Hashes}, State};
handle_call({unfetchedCount, Channel}, _From, [{sql, Db}, _] = State) ->
    ChannelBin = list_to_binary(Channel),
    Query = "SELECT count(*) as count FROM unfetched_hashes WHERE channel = ?",
    [{columns, ["count"]}, {rows, [{Count}]}] = sqlite3:sql_exec(Db, Query, [ChannelBin]),
    {reply, {ok, Count}, State};
handle_call({unfetchedDelete, Hashes}, _From, [{sql, Db}, _] = State) ->

    {ok, Results}.
unfetched_delete(Db, Hashes) ->

    {reply, ok, State}.
%%%%%%%%%%%%%%%%%%%%%%%
%% gen_server things %%
%%%%%%%%%%%%%%%%%%%%%%%
handle_cast(Msg, State) ->
    io:format("Unexpected async call: ~p~n", [Msg]),
    {noreply, State}.
handle_info(Msg, State) ->
    io:format("Unexpected message: ~p~n", [Msg]),
    {noreply, State}.
terminate(_Reason, [{sql, Db}, _] = _State) ->
    sqlite3:close(Db),

code_change(_OldVsn, State, _Extra) ->
    %% let's re-open the Db, just in case the driver changed.
    [{sql, Db}, {dbPath, Path}] = State,
    sqlite3:close(Db),
    open_db(Path).

unpack_peer(Row) ->
    {Id, Addr, Score, LastSeen, LastAttempt, AttemptCount, CreatedAt, Notes} = Row,
    #{
        id => Id,
        address => binary_to_list(Addr),
        score =>
            case Score of
                null -> 0;
                S -> S
            end,
        last_seen =>
            case LastSeen of
                null -> 0;
                LS -> LS
            end,
        last_attempt =>
            case LastAttempt of
                null -> 0;
                LA -> LA
            end,
        attempt_count =>
            case AttemptCount of
                null -> 0;
                AC -> AC
            end,
        created_at => CreatedAt,
        notes =>
            case Notes of
                null -> undefined;
                N -> binary_to_list(N)
            end
    }.

        <<"\n"
        "create table IF NOT EXISTS posts (\n"
        "  -- these exist for all posts\n"
        "  --\n"
        "  id integer primary key AUTOINCREMENT,\n"
        "  hash blob unique not null check (length(hash) = 32),\n"
        "  type integer not null check (type >= 0 AND type <= 5 ),\n"
        "  -- milliseconds since UNIX Epoch\n"
        "  timestamp integer not null check (timestamp > 0),\n"
        "\n"
        "  user_id integer not null,\n"
        "\n"
        "  raw_post blob not null,\n"
        "\n"
        "\n"
        "  -- depends on postType\n"
        "  channel text,          -- all but delete have this one\n"
        "  -- join & leave don't have other fields then channel\n"
        "  text text,\n"
        "  topic text,\n"
        "  deletedHashes blob,    -- concatenated\n"
        "  infos blob,            -- encoded as (json?) map {k=>v, k=>v}\n"
        "\n"
        "  -- `post/text`  | a textual chat message, posted to a channel |\n"
        "  CONSTRAINT is_text check (\n"
        "    type != 0 OR (channel is not null AND text is not null)\n"
        "  ),\n"
        "  -- `post/delete`| the deletion of a previously published post |\n"
        "  CONSTRAINT is_delete check (\n"
        "    type != 1 OR (deletedHashes is not null)\n"
        "  ),\n"
        "  -- `post/info`  | set or clear informative key/value pairs on a user\n"
        "  CONSTRAINT is_info check (\n"
        "    type != 2 OR (infos is not null)\n"
        "  ),\n"
        "  -- `post/topic` | set or clear a channel's topic string\n"
        "  CONSTRAINT is_topic check (\n"
        "    type != 3 OR (channel is not null AND topic is not null)\n"
        "  ),\n"
        "  -- `post/join`  | announce membership to a channel\n"
        "  CONSTRAINT is_join check (\n"
        "    type != 4 OR (channel is not null)\n"
        "  ),\n"
        "  -- `post/leave` | announce cessation of membership to a channel\n"
        "  CONSTRAINT is_leave check (\n"
        "    type != 5 OR (channel is not null)\n"
        "  ),\n"
        "\n"
        "  foreign key(user_id) references users(id)\n"
        ");\n"
        "\n"
        "create table IF NOT EXISTS links (\n"
        "    channel text,\n"
        "    source blob not null,\n"
        "    parent blob not null\n"
        "\n"
        "    -- we will get posts for which we don't have the parent's (yet)\n"
        "    -- foreign key(source) references posts(hash),\n"
        "    -- foreign key(parent) references posts(hash),\n"
        ");\n"
        "CREATE INDEX IF NOT EXISTS parent_idx ON links(parent);\n"
        "\n"
        "create table IF NOT EXISTS channel_members (\n"
        "    id integer primary key AUTOINCREMENT,\n"
        "    channel text not null,\n"
        "    user_id integer not null,\n"
        "    last_hash blob not null,\n"
        "    UNIQUE(channel, user_id)\n"
        ");\n">>,

        <<
            "\n"
            "create table IF NOT EXISTS posts (\n"
            "  -- these exist for all posts\n"
            "  --\n"
            "  id integer primary key AUTOINCREMENT,\n"
            "  hash blob unique not null check (length(hash) = 32),\n"
            "  type integer not null check (type >= 0 AND type <= 5 ),\n"
            "  -- milliseconds since UNIX Epoch\n"
            "  timestamp integer not null check (timestamp > 0),\n"
            "\n"
            "  user_id integer not null,\n"
            "\n"
            "  raw_post blob not null,\n"
            "\n"
            "\n"
            "  -- depends on postType\n"
            "  channel text,          -- all but delete have this one\n"
            "  -- join & leave don't have other fields then channel\n"
            "  text text,\n"
            "  topic text,\n"
            "  deletedHashes blob,    -- concatenated\n"
            "  infos blob,            -- encoded as (json?) map {k=>v, k=>v}\n"
            "\n"
            "  -- `post/text`  | a textual chat message, posted to a channel |\n"
            "  CONSTRAINT is_text check (\n"
            "    type != 0 OR (channel is not null AND text is not null)\n"
            "  ),\n"
            "  -- `post/delete`| the deletion of a previously published post |\n"
            "  CONSTRAINT is_delete check (\n"
            "    type != 1 OR (deletedHashes is not null)\n"
            "  ),\n"
            "  -- `post/info`  | set or clear informative key/value pairs on a user\n"
            "  CONSTRAINT is_info check (\n"
            "    type != 2 OR (infos is not null)\n"
            "  ),\n"
            "  -- `post/topic` | set or clear a channel's topic string\n"
            "  CONSTRAINT is_topic check (\n"
            "    type != 3 OR (channel is not null AND topic is not null)\n"
            "  ),\n"
            "  -- `post/join`  | announce membership to a channel\n"
            "  CONSTRAINT is_join check (\n"
            "    type != 4 OR (channel is not null)\n"
            "  ),\n"
            "  -- `post/leave` | announce cessation of membership to a channel\n"
            "  CONSTRAINT is_leave check (\n"
            "    type != 5 OR (channel is not null)\n"
            "  ),\n"
            "\n"
            "  foreign key(user_id) references users(id)\n"
            ");\n"
            "\n"
            "create table IF NOT EXISTS links (\n"
            "    channel text,\n"
            "    source blob not null,\n"
            "    parent blob not null\n"
            "\n"
            "    -- we will get posts for which we don't have the parent's (yet)\n"
            "    -- foreign key(source) references posts(hash),\n"
            "    -- foreign key(parent) references posts(hash),\n"
            ");\n"
            "CREATE INDEX IF NOT EXISTS parent_idx ON links(parent);\n"
            "\n"
            "create table IF NOT EXISTS channel_members (\n"
            "    id integer primary key AUTOINCREMENT,\n"
            "    channel text not null,\n"
            "    user_id integer not null,\n"
            "    last_hash blob not null,\n"
            "    UNIQUE(channel, user_id)\n"
            ");\n"
        >>,