<?php
$ROOT=''; //save root of web directory
//NOTES WAL NOT the problem; problem is primary key goes back to 0 does not globally increment
//thus need cascade delete otherwise will have images in blog_image that have no doc id until create
//orphan files in image table also!!

//need for transactions also, what if cascade delete fails? 
//need for transactions also, imagine a primary key table, multiple readers will read same value and increment; let db handle primary key; or mark items immutable; never delete but cancel
$db = new SQLite3($ROOT.'word_v5.sqlite3');//NOT pdo create sqlite table
$db->busyTimeout(60000);//60 seconds
$db->query('PRAGMA journal_mode = WAL;'); //multiple readers, https://sqlite.org/wal.html 
$db->query('PRAGMA busy_timeout = 60000;');//PRAGMA busy_timeout = milliseconds before retry;
$db->query('PRAGMA cell_size_check = ON;'); //database corruption is detected earlier
$db->query("PRAGMA encoding = 'UTF-8';"); //watch php WHERE clause will not work since ISO encoding vs UTF 8,
$db->query("PRAGMA foreign_keys = ON;");  //off by default , each connection must call this

//https://www.sqlite.org/foreignkeys.html  FOREIGN KEY(thistablename) REFERENCES othertable(unique_or_primary_key)
//not no need to declare type INTEGER if using short hand notation REFERENCES
//create index always of foreign key
//Foreign key actions are similar to triggers in many ways. CAN USE TRIGGER TO CASCADE DELETE
//ALL Foreign key references last items in table  otherwise syntax error
$db->exec( 'CREATE TABLE IF NOT EXISTS accounts ( user_id INTEGER PRIMARY KEY, username TEXT NOT NULL UNIQUE COLLATE NOCASE, profile_image_base64 TEXT, profile_image_mimetype TEXT,  last_session_hashid TEXT, word_number INTEGER, number_tried INTEGER, calc_score REAL, last_word_id INTEGER, currently_playing INTEGER DEFAULT 0 );');
$db->exec( 'CREATE TABLE IF NOT EXISTS words ( word_id INTEGER PRIMARY KEY, word_text TEXT COLLATE NOCASE, word_length INTEGER );');
$db->exec( 'CREATE TABLE IF NOT EXISTS attempts (attempt_id INTEGER PRIMARY KEY, user_id INTEGER, word_id INTEGER, attempt_word TEXT, character_state TEXT, attempt_sequence INTEGER, FOREIGN KEY(user_id) REFERENCES accounts(user_id) ON DELETE CASCADE, FOREIGN KEY(word_id) REFERENCES words(word_id) ON DELETE CASCADE );');

////create index always of foreign key
$db->exec( 'CREATE INDEX IF NOT EXISTS attempt_user_id ON attempts(user_id);');
$db->exec( 'CREATE INDEX IF NOT EXISTS attempt_word_id ON attempts(word_id);');

//image is orphanned if no reference, ggood to prevent multiple uploads IF CHECKED, but since not checked waste of database space
//https://www.sqlite.org/lang_createtrigger.html
//INSERT 	NEW. references are valid
//UPDATE 	NEW. and OLD. references are valid
//DELETE 	OLD. references are valid
//NEED TO DO this or will create orphans
//open text file
$filename = "popular-words.txt";
$handle = fopen($filename, "r");
$dictionary = fread($handle, filesize($filename));
fclose($handle);

//separated by new line
$words = explode("\n", $dictionary);
$num_of_6 = 0;
$num_of_5 = 0;
echo count($words)." ";
$words = array_slice($words,0,-1); //remove last item
echo count($words)." "; //25322
$word_count = 0;
foreach ($words as $word) {

	if(strlen($word) == 5) {
		
		$insert_word = $db->prepare( 'INSERT INTO words (word_text, word_length) VALUES (?,?)');
		$insert_word->bindValue(1, $word, SQLITE3_TEXT);
		$insert_word->bindValue(2, strlen($word), SQLITE3_INTEGER);
		$insert_word->execute();
		$word_count+=1;
	}

}
echo $word_count." "; //25322
//echo $words[mt_rand(0, 25321)];
//MUST use sqlite PDO doesn't do text comparison well
?>