-- database schema version 1

pragma foreign_keys = on;

create table Meta (
  version integer not null
);
insert into Meta(version) values (1);

create table Users (
  id integer primary key,
  username text unique not null,
  passhash blob not null
);

create table Logins (
  user integer not null,
  token text unique not null,
  expire integer not null  -- unix timestamp, token invalid >= this time
);
create index Logins_user on Logins (user);

create table Files (
  id integer primary key,
  owner integer not null,
  path text unique not null,  -- names separated with single '/', neither start nor end with '/'

  foreign key (owner) references Users(id) on delete cascade
);

-- Undo history of a file
create table Updates (
  id integer primary key,
  file integer not null,
  idx integer not null,  -- index in the update history of this file; first is 0

  position integer not null,
  -- If old_value is null & new_value exists:
  --   insert item _before_ the given position; the new item has itemid.
  -- If old_value exists & new_value is null:
  --   delete item at the given position; that item had itemid.
  -- If old_value exists & new_value exists:
  --   modify the item at the given position, which has itemid.
  -- If old_value is null & new_value is null:
  --   <disallowed>.
  itemid integer not null,
  old_value blob null,
  new_value blob null,

  foreign key (file) references Files(id) on delete cascade,
  unique (file, idx)
);

-- Current state of a file
create table Chunks (
  id integer primary key,
  file integer not null,
  position integer not null,
  contents blob not null,

  unique (file, position)
);