PRAGMA journal_mode = TRUNCATE;
PRAGMA foreign_keys = 1;

create table PaMetadata (
  dbSchema varchar(100) not null,
  k varchar(100) not null,
  val varchar(250) not null,
  primary key (dbSchema, k)
);

insert into PaMetadata (dbSchema, k, val) values ('main', 'dbSchemaVersion', '14');

create table PaAccount (
  id integer not null primary key autoincrement,
  email varchar(200) not null unique,
  name varchar(200),
  preferences text,
  passwordHash varchar(200),
  passwordResetToken varchar(200)
);

create table PaNode (
  id integer not null primary key autoincrement,
  typeName varchar(100) not null,
  parentId integer references PaNode (id),
  depth smallint not null,
  relativeId varchar(100) not null,
  publishDate timestamp,
  unique (parentId, relativeId)
);

create index PaNode_typeName_idx on PaNode (typeName);

create table PaOrderedNode (
  nodeId integer not null primary key references PaNode (id) on delete cascade,
  orderNum integer not null
);

create table PaPartNode (
  nodeId integer not null primary key references PaNode (id),
  listName varchar(100) not null,
  documentNodeId integer not null references PaNode (id)
);

create index PaPartNode_listName_idx on PaPartNode (listName);

create table PaLNode (
  nodeId integer not null references PaNode (id),
  language varchar(6) not null,
  ready tinyint(1) not null default 0,
  primary key (nodeId, language)
);

create table PaDocument (
  nodeId integer not null,
  language varchar(6) not null,
  title varchar(500),
  slug varchar(200),
  metaDescription varchar(500),
  metaKeywords varchar(500),
  primary key (nodeId, language),
  foreign key (nodeId, language) references PaLNode (nodeId, language)
);

create table PaFieldLabeling (
  field varchar(100) not null,
  nodeId integer not null references PaNode (id) on delete cascade,
  termId integer not null references PaNode (id),
  orderNum integer,
  primary key (field, nodeId, termId)
);

create table PaFieldVarchar (
  field varchar(100) not null,
  nodeId integer not null references PaNode (id) on delete cascade,
  -- the value '.' represents any language
  language varchar(6) not null,
  dataType varchar(100) not null,
  val varchar(5000),
  primary key (field, nodeId, language)
);

create table PaFieldText (
  field varchar(100) not null,
  nodeId integer not null references PaNode (id) on delete cascade,
  -- the value '.' represents any language
  language varchar(6) not null,
  dataType varchar(100) not null,
  val text,
  excerpt varchar(500),
  primary key (field, nodeId, language)
);
