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', '29');

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

create index PaNode_typeName_idx on PaNode (typeName);
create index PaNode_parentId_idx on PaNode (parentId);
create index PaNode_publishDate_idx on PaNode (publishDate);

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 index PaPartNode_documentNodeId_idx on PaPartNode (documentNodeId);

create table PaLNode (
  nodeId integer not null references PaNode (id),
  language varchar(6) not null,
  ready tinyint(1) not null default 0,
  updatedAt timestamp not null default current_timestamp,
  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),
  overrideLanguage varchar(6),
  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,
  plugin varchar(100),
  primary key (field, nodeId, termId)
);

create index PaFieldLabeling_nodeId_idx on PaFieldLabeling (nodeId);
create index PaFieldLabeling_termId_idx on PaFieldLabeling (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),
  plugin varchar(100),
  primary key (field, nodeId, language)
);

create index PaFieldVarchar_nodeId_idx on PaFieldVarchar (nodeId);

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),
  plugin varchar(100),
  primary key (field, nodeId, language)
);

create index PaFieldText_nodeId_idx on PaFieldText (nodeId);

create index PaFieldVarchar_plugin_idx on PaFieldVarchar (plugin);
create index PaFieldText_plugin_idx on PaFieldText (plugin);
create index PaFieldLabeling_plugin_idx on PaFieldLabeling (plugin);

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),
  loginMethod varchar(50),
  active tinyint(1) not null default 1
);

create table PaAccountRole (
  accountId integer not null references PaAccount (id) on delete cascade,
  role varchar(100) not null,
  primary key (accountId, role)
);

create table PaAuthorAccount (
  authorNodeId integer not null references PaNode (id) on delete cascade,
  accountId integer not null references PaAccount (id) on delete cascade,
  primary key (authorNodeId, accountId)
);

create index PaAuthorAccount_accountId_idx on PaAuthorAccount (accountId);

create table PaEventLog (
  id integer not null primary key autoincrement,
  eventType varchar(100) not null,
  actorId integer references PaAccount (id),
  targetType varchar(50) not null,
  targetId varchar(100) not null,
  eventData text,
  createdAt timestamp not null default current_timestamp
);

create index PaEventLog_eventType_idx on PaEventLog (eventType);
create index PaEventLog_actorId_idx on PaEventLog (actorId);
create index PaEventLog_targetType_targetId_idx on PaEventLog (targetType, targetId);
create index PaEventLog_createdAt_idx on PaEventLog (createdAt);

create table PaPersonalAccessToken (
  id integer not null primary key autoincrement,
  accountId integer not null references PaAccount (id) on delete cascade,
  tokenHash varchar(64) not null unique,
  tokenPreview varchar(20) not null,
  description varchar(200),
  createdAt timestamp not null default current_timestamp,
  lastUsedAt timestamp,
  expiresAt timestamp,
  active tinyint(1) not null default 1
);

create index PaPersonalAccessToken_accountId_idx on PaPersonalAccessToken (accountId);
create index PaPersonalAccessToken_tokenHash_idx on PaPersonalAccessToken (tokenHash);

create table PaHistoryEntry (
  id varchar(36) not null primary key,
  nodeId integer not null references PaNode (id) on delete cascade,
  language varchar(6) not null,
  fieldValues text not null,
  documentValues text,
  changeSummary text,
  updatedAt timestamp not null,
  expireAt timestamp,
  timeTier varchar(10) not null
);

create index PaHistoryEntry_nodeId_language_timeTier_idx on PaHistoryEntry (nodeId, language, timeTier, id desc);
create index PaHistoryEntry_expireAt_idx on PaHistoryEntry (expireAt);
