CREATE TABLE IF NOT EXISTS infolib (
  id  INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT,
  content TEXT,
  time DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS moongate (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  description TEXT,
  comment TEXT,
  filename TEXT,
  date TEXT CHECK(date(date) IS NOT NULL),
  time DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS dpw (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  description TEXT,
  category TEXT,
  comment TEXT,
  filename TEXT,
  date TEXT CHECK(date(date) IS NOT NULL),
  retained TEXT,
  time DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR NOT NULL UNIQUE,
    password VARCHAR NOT NULL
);

CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    description TEXT UNIQUE
);

INSERT OR IGNORE INTO categories (description) VALUES
	('car'), ('computer'), ('medical'), ('domestic'), ('moongate'), ('photography')
;

/* password generated by: perl -MCrypt::Passphrase -E  \
   'my $auth=Crypt::Passphrase->new(encoder=>"Argon2"); \
   say $auth->hash_password("$pwd")'
*/

INSERT OR IGNORE INTO users (username, password) VALUES
	('raj',
	  '$argon2id$v=19$m=262144,t=3,p=1$A9XGCiy4/FhyQcYz9Ef5oA$d8nnPhDhS6AtDG6L1EAXHUx/vr0qOzGJsRoOyOaw5is'),
  ('test', /* t3st */
    '$argon2id$v=19$m=262144,t=3,p=1$qh2PEMMDRgIoDDrYhbF9sA$rp4HWtiOKdcq42q7pPRlxhDa7MZrGOAhKyvl0rxK0xg')
;