PRAGMA foreign_keys = on;

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

-- -- links to messages, jabber users, stores jid history, etc.
-- create table identities(
--     id text primary key not null
-- );

-- create table identities_users(
--     id text not null,
--     jid text not null,
--     -- whichever has the newest timestamp is the active one.
--     -- 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(activated timestamp, id, jid)
-- );

create table resources(
    bare_jid text not null,
    resource text not null,
    foreign key(bare_jid) references users(jid),
    primary key(bare_jid, resource)
);

-- enum for subscription state
create table 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');

-- a roster contains users, with client-set nickname
CREATE TABLE 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 groups(
    group_name text primary key not null
);

create table 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 chats (
    id text primary key not null,
    correspondent text not null unique,
    foreign key(correspondent) references users(jid)
);

-- messages include reference to chat they are in, and who sent them.
create table messages (
    id text primary key not null,
    body 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)),
    -- user is the current "owner" of the message
    -- TODO: queued messages offline
    -- TODO: timestamp
    timestamp text not null,

    -- TODO: icky
    -- the user to show it coming from (not necessarily the original sender)
    -- from_identity text not null,
    -- original sender details (only from jabber supported for now)
    from_jid text not null,
    -- resource can be null
    from_resource text,
    -- check (from_jid != original_sender),

    -- TODO: from can be either a jid, a moved jid (for when a contact moves, save original sender jid/user but link to new user), or imported (from another service (save details), linked to new user)
    -- TODO: read bool not null,
    foreign key(chat_id) references chats(id) on delete cascade,
    -- foreign key(from_identity) references identities(id),
    foreign key(from_jid) references users(jid),
    foreign key(from_jid, from_resource) references resources(bare_jid, resource)
);

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

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

create table 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);