-- set GLOBAL time_zone = '+00:00';
pragma foreign_keys = on;

-- user / organization data
drop table if exists users;
create table users (
   -- id of the user / org
   id integer not null primary key autoincrement,
   -- creation time
   created_at timestamp not null default current_timestamp,
   -- last update
   updated_at timestamp,
   -- is this user an organization?
   org boolean not null,
   -- email address
   email text not null unique,
   -- user / organization name
   name text not null unique,
   -- password
   pass text not null,
   -- metadata
   meta text not null default '{}'
);

-- organization level access control lists
drop table if exists org_acls;
create table org_acls (
   -- org acl id
   id integer not null primary key autoincrement,
   -- user id (user.organization == true)
   org integer not null,
   -- user id (member of the organization)
   member integer not null,
   -- ACL bits (what the member can do in the organization)
   rights integer not null,
   -- relationships
   foreign key (org) references users (id),
   foreign key (member) references users (id)
);

-- engines
drop table if exists engines;
create table engines (
   -- id of the engine
   id integer not null primary key autoincrement,
   -- engine name
   name text not null,
   -- extra metadata
   meta text not null default '{}'
);

-- products
drop table if exists products;
create table products (
   -- id of the product
   id integer not null primary key autoincrement,
   -- creation time
   created_at timestamp not null default current_timestamp,
   -- last update
   updated_at timestamp,
   -- engine id
   engine integer not null,
   -- supported platforms
   platforms integer not null,
   -- price
   price integer not null,
   -- title
   title text not null,
   -- description
   body text not null,
   -- short description
   short text not null,
   -- header image
   header text not null,
   -- extra metadata
   meta text not null default '{}',
   -- relationships
   foreign key (engine) references engines (id)
);

-- tags
drop table if exists tags;
create table tags (
   -- id of the tag
   id integer not null primary key autoincrement,
   -- tag name
   name text not null,
   -- extra metadata
   meta text not null default '{}'
);

-- product tags
drop table if exists product_tags;
create table product_tags (
   -- product tag id
   id integer not null primary key autoincrement,
   -- product id
   product integer not null,
   -- tag id
   tag integer not null,
   -- relationships
   foreign key (product) references products (id),
   foreign key (tag) references roles (id)
);

-- product level access control lists
drop table if exists product_acls;
create table product_acls (
   -- product acl id
   id integer not null primary key autoincrement,
   -- product id
   product integer not null,
   -- user id
   member integer not null,
   -- ACL bits (what the member can do with the product)
   rights integer not null,
   -- relationships
   foreign key (member) references users (id),
   foreign key (product) references products (id)
);

-- roles
drop table if exists roles;
create table roles (
   -- id of the role
   id integer not null primary key autoincrement,
   -- name of the role
   name text not null
);

-- creators
drop table if exists creators;
create table creators (
   -- id of the creator
   id integer not null primary key autoincrement,
   -- creation time
   created_at timestamp not null default current_timestamp,
   -- last update
   updated_at timestamp,
   -- name of the creator
   name text not null,
   -- extra metadata
   meta text not null default '{}'
);

-- product creators
drop table if exists product_creators;
create table product_creators (
   -- product creator id
   id integer not null primary key autoincrement,
   -- creator id
   creator integer not null,
   -- product id
   product integer not null,
   -- role id
   role integer not null,
   -- relationships
   foreign key (creator) references creators (id),
   foreign key (product) references products (id),
   foreign key (role) references roles (id)
);

-- product reviews
drop table if exists reviews;
create table reviews (
   -- review id
   id integer not null primary key autoincrement,
   -- creation time
   created_at timestamp not null default current_timestamp,
   -- last update
   updated_at timestamp,
   -- product id
   product integer not null,
   -- rating
   rating integer not null,
   -- title
   title text not null,
   -- body
   body text not null,
   -- relationships
   foreign key (product) references products (id)
);