import { Database } from "jsr:@db/sqlite@0.11";

export function setupSystemDb(db: Database) {
  const createTableINE = "CREATE TABLE IF NOT EXISTS";
  const createIndexINE = "CREATE INDEX IF NOT EXISTS";

  db.exec(`
    -- Master list of all users known to the system
    ${createTableINE} users (
      did TEXT PRIMARY KEY NOT NULL,
      role TEXT,
      registrationdate TEXT,
      onboardingstatus TEXT
    );

    -- Cache of profiles for *other* users, prevents storing this in every user's DB
    ${createTableINE} remoteprofileview (
      did TEXT PRIMARY KEY NOT NULL,
      handle TEXT,
      displayname TEXT,
      description TEXT,
      avatar TEXT,
      banner TEXT
    );

    -- Central DID document cache and handle resolution
    ${createTableINE} did (
      did TEXT PRIMARY KEY NOT NULL,
      method TEXT,
      string TEXT,
      doc TEXT,
      pds TEXT,
      handle TEXT
    );
    ${createIndexINE} idx_did_handle ON did(handle);
  `);
}