/*
 * Automatically generated by Magic.
 */
CREATE TABLE users (
  username varchar(256) not null primary key collate nocase,
  password varchar(256) not null,
  created timestamp not null default current_timestamp
);

CREATE TABLE roles (
  name varchar(45) not null primary key collate nocase,
  description varchar(256) null collate nocase
);

CREATE TABLE users_roles (
  role varchar(45) not null collate nocase,
  user varchar(256) not null collate nocase,
  constraint roles_fky foreign key (role) references roles (name) on delete cascade,
  constraint users_fky foreign key (user) references users (username) on delete cascade,
  primary key(role, user)
);

CREATE TABLE tasks (
  id varchar(256) not null primary key collate nocase,
  description text null collate nocase,
  hyperlambda text not null collate nocase,
  created timestamp not null default current_timestamp
);

CREATE TABLE log_entries (
  id integer not null primary key autoincrement,
  created timestamp not null default current_timestamp,
  type varchar(10) not null collate nocase,
  content text not null collate nocase,
  exception text null collate nocase,
  meta text null
);

CREATE TABLE questionnaires (
  name varchar(40) not null primary key,
  type varchar(40) not null default 'single-shot', action varchar(40) null
);

CREATE TABLE ml_blocked_ips(
    ml_blocked_ip_id integer not null primary key autoincrement,
    ip_address text not null,
    created timestamp not null default current_timestamp, user_id text
);

CREATE TABLE ml_permalinks (
  id text not null primary key,
  created timestamp not null default current_timestamp,
  messages text not null,
  title text,
  description text
);

CREATE TABLE task_due (
  id integer not null primary key autoincrement,
  task varchar(256) not null collate nocase,
  due timestamp not null,
  repeats text null,
  constraint task_due_task_fky foreign key (task) references tasks (id) on delete cascade
);

CREATE TABLE users_extra (
  user varchar(256) not null collate nocase,
  type varchar(45) not null collate nocase,
  value varchar(1024) not null collate nocase,
  constraint users_extra_fky foreign key (user) references users (username) on delete cascade,
  primary key(user, type)
);

CREATE TABLE ml_types(
  type varchar(256) not null primary key,
  model varchar(1024) not null,
  max_tokens integer not null,
  temperature real not null,
  recaptcha real not null default 0,
  auth text null,
  supervised int not null default 0,
  cached integer not null default 0,
  prefix text null,
  use_embeddings integer not null default 0,
  threshold real not null default 0.8,
  vector_model varchar(1024) not null default 'text-embedding-3-small',
  max_context_tokens integer,
  max_request_tokens integer,
  greeting text null,
  base_url text null,
  contact_us text null,
  lead_email text null,
  api_key text null,
  twilio_account_sid text null,
  twilio_account_id text null,
  webhook_incoming text null,
  webhook_outgoing text null,
  webhook_incoming_url text null,
  webhook_outgoing_url text null,
  system_message text null,
  initial_questionnaire varchar(40) null references questionnaires(name),
  no_requests int not null default 0,
  max_requests int not null default -1,
  session_timeout int default 1200,
  search_postfix text default '',
  conversation_starters text,
  max_function_invocations int default 5,
  max_session_items int default 15,
  completion_slot text not null default 'magic.ai.chat'
);

CREATE TABLE ml_training_snippets(
  id integer not null primary key autoincrement,
  created timestamp not null default current_timestamp,
  type varchar(256) not null default 'hl',
  pushed int not null default 0,
  uri varchar(1024) null,
  prompt text not null,
  completion text not null,
  filename varchar(1024) null,
  cached integer null,
  meta varchar(40) null,
  embeddings blob,
  constraint ml_types_fky foreign key (type) references ml_types (type) on delete cascade
);

CREATE TABLE ml_requests(
  id integer not null primary key autoincrement,
  created timestamp not null default current_timestamp,
  type varchar(256) not null,
  prompt text not null,
  completion text not null,
  finish_reason varchar(100) not null,
  cached integer null,
  session text null,
  questionnaire int not null default 0,
  user_id varchar(40) null,
  context int not null default 0,
  referrer text,
  constraint ml_requests_types_fky foreign key (type) references ml_types (type) on delete cascade
);

CREATE TABLE questions(
  question_id integer not null primary key autoincrement,
  question text not null,
  questionnaire varchar(40) not null references questionnaires(name) on delete cascade,
  type varchar(40) not null default 'question',
  context int not null default 0,
  name varchar(40) null
);

CREATE TABLE ml_history (
  session_id varchar(256) not null collate nocase,
  username varchar(256) not null collate nocase,
  content TEXT not null collate nocase,
  created timestamp not null default current_timestamp,
  name text null,
  primary key(session_id)
);


/*
 * Making sure we insert default records.
 */
insert into roles (name, description) values ('root', 'Primary root account, has access to EVERYTHING in system!');
insert into roles (name, description) values ('service', 'Service acount for web services and API integrations');
insert into roles (name, description) values ('admin', 'Admin account');
