BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "certs" (
	"hash"	TEXT NOT NULL UNIQUE,
	"user_id"	INTEGER,
	PRIMARY KEY("hash"),
	FOREIGN KEY("user_id") REFERENCES "users"("id")
);
CREATE TABLE IF NOT EXISTS "users" (
	"id"	INTEGER NOT NULL UNIQUE,
	"name" TEXT DEFAULT "noname",
	"add_key_code" TEXT,
	"game_id"	INTEGER,	
	"score" INTEGER DEFAULT 0,
	PRIMARY KEY("id" AUTOINCREMENT),
	FOREIGN KEY("game_id") REFERENCES "games"("id")
);
CREATE TABLE IF NOT EXISTS "games" (
	"id" INTEGER NOT NULL UNIQUE,
	"board"	TEXT,
	"words"	TEXT,
	"graph"	TEXT,
	"stats" TEXT,
	"stime" DATETIME DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "plays" (
	"game_id"	INTEGER,
	"user_id"	INTEGER,
	"words"	TEXT,
	PRIMARY KEY("game_id","user_id"),
	FOREIGN KEY("game_id") REFERENCES "games"("id"),
	FOREIGN KEY("user_id") REFERENCES "users"("id")
);
COMMIT;