<?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 PDO('sqlite:'.$ROOT.'store_mp3_v1.sqlite3');//pdo create sqlite table watch php is iso but sqllite utf-8 TEXT collate 
$db->query('PRAGMA journal_mode = WAL;'); //multiple readers, https://sqlite.org/wal.html synchro problems?? two commits?? locks??
$db->query('PRAGMA busy_timeout = 60000;');//PRAGMA busy_timeout = milliseconds before retry; will it work on PDO vs SQLITE3?
$db->query('PRAGMA cell_size_check = ON;'); //database corruption is detected earlier and is less likely to "spread"
$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 audio ( audio_id INTEGER PRIMARY KEY, audio_base64 TEXT, word_name TEXT);');

//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

$dir="./mp3";
$audio_files = scandir($dir);
echo print_r($audio_files);
foreach ($audio_files  as $file_path) {
		$full_path=$dir."/".$file_path;
		if(is_file($full_path)) {
			$word_name = explode(".", $file_path)[0];
			$audio_base64 = base64_encode(file_get_contents($full_path));
			$insert_audio = $db->prepare( 'INSERT INTO audio (audio_base64, word_name) VALUES (?,?)');
			$insert_audio->bindValue(1, $audio_base64, SQLITE3_TEXT);
			$insert_audio->bindValue(2, $word_name,  SQLITE3_TEXT);
			$insert_audio->execute();
		}
}
//took 26 megabytes of audio and converted it to 146 megabytes of base 64 text

?>