text: CREATE TABLE IF NOT EXISTS clients (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    clientid TEXT,
    location TEXT,
    issues TEXT, -- JSON dump of checkboxed answers
    assessment TEXT,
    plan TEXT,
    the_rest TEXT, -- if structure changes, keep json dump in here
    time DATETIME DEFAULT (datetime('now', 'localtime'))
  );
  
  CREATE TABLE IF NOT EXISTS scan_res (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    clientid TEXT,
    serial TEXT,
    note TEXT,
    device TEXT,
    device_model TEXT,
    device_manufacturer TEXT,
    device_version TEXT,
    is_rooted INTEGER,
    rooted_reasons TEXT,
    last_full_charge DATETIME,
    device_primary_user TEXT,
    device_access TEXT, -- JSONdump of person, frequency of access / todo
    how_obtained TEXT, -- todo
    time DATETIME DEFAULT (datetime('now', 'localtime')),
    FOREIGN KEY(clientid) REFERENCES clients(clientid)
    --battery_avg_last_charged INTEGER, -- take out maybe
  );
  
  CREATE TABLE IF NOT EXISTS app_info (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    scanid INTEGER,
    appid TEXT,
    flags TEXT,
    remark TEXT,
    action_taken TEXT,
    apk_path TEXT,
    install_date DATETIME,
    last_updated DATETIME,
    app_version TEXT,
    permissions TEXT,
    permissions_reason TEXT,
    permissions_used DATETIME,
    data_usage INTEGER, -- Mobile total received: Wifi-Total
    battery_usage INTEGER, -- in mAh. show "not more than average app on your device" rather than actual amount on front-end.
    time DATETIME DEFAULT (datetime('now', 'localtime')),
    FOREIGN KEY(scanid) REFERENCES scan_res(id)
  );
  -- see also, how battery usage is measured on phone app itself in settings. percent of battery used by app.E
  -- https://stackoverflow.com/questions/45751387/how-do-i-calculate-the-battery-drain-for-a-particular-app-using-dumpsys-batterys
  CREATE INDEX IF NOT EXISTS idx_scan_res_clientid on scan_res  (clientid);
  CREATE INDEX IF NOT EXISTS idx_app_info_scanid on app_info  (scanid);
  --
  -- CREATE TABLE IF NOT EXISTS notes (
  -- 	id INTEGER PRIMARY KEY AUTOINCREMENT,
  -- 	serial TEXT,
  -- 	appid TEXT,
  -- 	note TEXT,
  -- 	time DATETIME DEFAULT (datetime('now', 'localtime')),
  -- 	device TEXT,
  -- 	PRIMARY KEY (id)
  -- );
  --
  -- This is a backup table to keep logs of everything. 
  -- CREATE TABLE IF NOT EXISTS all_logs (
  --        id INTEGER PRIMARY KEY AUTOINCREMENT,
  --        args TEXT,
  --        formargs TEXT,
  --        httpmethod TEXT,
  --        res TEXT,
  --        time DATETIME DEFAULT (datetime('now', 'localtime'))
  -- );
  
  