PRAGMA foreign_keys = on;

-- a user jid will never change, only a chat user will change
create table if not exists users(
    -- bare jid
    jid text primary key not null,
    nick text,
    avatar text,
    -- can receive presence status from non-contacts
    cached_status_message text
    -- TODO: last_seen
);

-- links to messages, jabber users, stores jid history, etc.
-- every identity requires a user, so imported messages must be assigned to an existing jabber account.
create table if not exists identities(
    id text primary key not null,
    -- each time a user moves, their identity's primary jid is updated, and the new jid is added to the identities log
    primary_jid text not null unique,
    foreign key(primary_jid) references users(jid)
);
-- this also allows e.g. mucs with imported histories to assign messages temporarily to muc-generated temporary users, then send user move events to assign message ownership once that person joins the new group chat with a jabber account.

-- identities log basically.
create table if not exists identities_users(
    id text not null,
    jid text not null,
    -- what to do when somebody moves, but then the old jid is used again without having explicitly moved back? create new identity to assign ownership to?
    -- merging of identities?
    activated_timestamp not null,
    foreign key(id) references identities(id),
    foreign key(jid) references users(jid),
    primary key(id, jid)
);

-- enum for subscription state
create table if not exists subscription(
    state text primary key not null
);

insert into subscription ( state ) values ('none'), ('pending-out'), ('pending-in'), ('pending-in-pending-out'), ('only-out'), ('only-in'), ('out-pending-in'), ('in-pending-out'), ('buddy') on conflict do nothing;

-- a roster contains users, with client-set nickname
CREATE TABLE if not exists roster( 
    user_jid text primary key not null,
    name TEXT,
    subscription text not null,
    foreign key(subscription) references subscription(state),
    foreign key(user_jid) references users(jid)
);

create table if not exists groups(
    group_name text primary key not null
);

create table if not exists groups_roster(
    group_name text not null,
    contact_jid text not null,
    foreign key(group_name) references groups(group_name),
    foreign key(contact_jid) references roster(user_jid) on delete cascade,
    primary key(group_name, contact_jid)
);

-- chat includes reference to user jid chat is with
-- specifically for dms, groups should be different
-- can send chat message to user (creating a new chat if not already exists)
create table if not exists chats (
    id text primary key not null,
    have_chatted bool not null,
    correspondent text not null unique,
    -- TODO: how many messages to store locally/rely on mam, whether to auto-download/store media too.
    foreign key(correspondent) references identities(id)
);

-- enum for subscription state
create table if not exists delivery(
    state text primary key not null
);

insert into delivery ( state ) values ('sending'), ('written'), ('sent'), ('delivered'), ('read'), ('failed'), ('queued') on conflict do nothing;

-- messages include reference to chat they are in, and who sent them.
create table if not exists messages (
    id text primary key not null,
    body text,
    -- delivery is nullable as only messages sent by the user are markable
    delivery text,
    chat_id text not null,
    -- TODO: channel stuff 
    -- channel_id uuid,
    -- check ((chat_id == null) <> (channel_id == null)),
    -- check ((chat_id == null) or (channel_id == null)),
    -- TODO: queued messages offline
    timestamp text not null,

    -- the identity to show it coming from (not necessarily the original sender)
    from_identity text not null,

    -- TODO: unread message count, read bool not null,

    foreign key(delivery) references delivery(state),
    foreign key(chat_id) references chats(id) on delete cascade,
    foreign key(from_identity) references identities(id)
);

-- enum for subscription state
create table if not exists show (
    state text primary key not null
);

insert into show ( state ) values ('away'), ('chat'), ('do-not-disturb'), ('extended-away') on conflict do nothing;

create table if not exists cached_status (
    id integer primary key not null,
    show text,
    message text,
    foreign key(show) references show(state)
);

insert into cached_status (id) values (0) on conflict do nothing;

create table if not exists capability_hash_nodes (
    node text primary key not null,
    timestamp text,
    -- TODO: normalization
    capabilities text not null
);

insert into capability_hash_nodes ( node, capabilities ) values ('https://bunny.garden/filamento#mSavc/SLnHm8zazs5RlcbD/iXoc=', 'aHR0cDovL2phYmJlci5vcmcvcHJvdG9jb2wvY2Fwcx9odHRwOi8vamFiYmVyLm9yZy9wcm90b2NvbC9kaXNjbyNpbmZvH2h0dHA6Ly9qYWJiZXIub3JnL3Byb3RvY29sL2Rpc2NvI2l0ZW1zH2h0dHA6Ly9qYWJiZXIub3JnL3Byb3RvY29sL25pY2sfaHR0cDovL2phYmJlci5vcmcvcHJvdG9jb2wvbmljaytub3RpZnkfHGNsaWVudB9wYx8fZmlsYW1lbnRvIDAuMS4wHx4cHA==') on conflict do nothing;

-- TODO: later maybe just a full stanza log period?
create table if not exists message_stanzas (
    id integer primary key not null,
    raw text not null,
    timestamp text not null,
    -- should be 1 to many as the same original stanza could technically have multiple envelopes...(e.g. message sync, mam), also makes making dangling stanzas not possible easier when modeling the relationship this way.
    envelope_of integer,
    linked_message text,
    -- check that stanza is either linked to another stanza, or a message (no dangling stanzas)
    check ((envelope_of == null) <> (linked_message == null)),
    foreign key(linked_message) references messages(id),
    foreign key(envelope_of) references message_stanzas(id)
)