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 ('site-generator', 'dbSchemaVersion', '2');

create table PgSession (
  id varchar(100) not null primary key,
  createdAt timestamp not null default current_timestamp,
  expireAt timestamp not null,
  language varchar(6) not null
);

create table PgStep (
  stepNumber integer not null,
  sessionId varchar(100) not null references PgSession(id),
  startedAt timestamp not null default current_timestamp,
  kind varchar(100) not null,
  status varchar(100) not null,
  durationMs integer,
  currentActivity varchar(100),
  explanation text,
  primary key (stepNumber, sessionId)
);

create table PgSchemaStep (
  stepNumber integer not null,
  sessionId varchar(100) not null,
  siteSchema text not null,
  l10n text not null,
  localizedValues text not null,
  nodeTypeCount integer not null,
  inputTokenCount integer not null,
  outputTokenCount integer not null,
  promptTitle varchar(200),
  primary key (stepNumber, sessionId),
  foreign key (stepNumber, sessionId) references PgStep(stepNumber, sessionId)
);

create table PgGeneratedSiteStep (
  siteId varchar(100) not null primary key,
  stepNumber integer not null,
  sessionId varchar(100) not null,
  siteUrl varchar(200) not null,
  loginEmail varchar(100) not null,
  loginPassword varchar(100) not null,
  localizedValues text not null,
  contentEntryCount integer,
  contentInputTokenCount integer,
  contentOutputTokenCount integer,
  contentErrors text,
  unique (stepNumber, sessionId),
  foreign key (stepNumber, sessionId) references PgStep(stepNumber, sessionId)
);

create table PgIssueEvent(
  id integer not null primary key autoincrement,
  sessionId varchar(100) not null references PgSession(id),
  createdAt timestamp not null default current_timestamp,
  eventType varchar(100) not null,
  issueMessage text,
  llmTaskName varchar(100),
  stepNumber integer
);
