<?php
ini_set('date.timezone','Etc/GMT-4');
$dir=__DIR__.'/photos';
$files=scandir($dir);
$files=array_diff(scandir($dir),array('.','..'));
usort($files,function($a,$b) use ($dir){
	return filemtime($dir.'/'.$a)<=>filemtime($dir.'/'.$b);
});
$outputFile='insert_photos.sql';
$sql="BEGIN TRANSACTION;\n";
$pk=200;
$z_ent=15;
$z_opt=1;
$sql.="DELETE FROM \"ZGENERICASSET\" WHERE Z_PK>=".$pk.";\n";
$sql.="DELETE FROM \"ZADDITIONALASSETATTRIBUTES\" WHERE Z_PK>=".$pk.";\n";
$sql.="DELETE FROM \"Z_8ASSETS\" WHERE Z_14ASSETS>=".$pk.";\n";
$pk+=100;
foreach($files as $file){
	$path=$dir.'/'.$file;
	$ext=strtolower(pathinfo($file,PATHINFO_EXTENSION));
	$isVideo=($ext==='mov');
	$isImage=($ext==='jpg'||$ext==='jpeg');
	if(!$isVideo&&!$isImage)continue;
	$filename=$file;
	$title=pathinfo($file,PATHINFO_FILENAME);
	$directory='DCIM/100APPLE';
	$zuuid="X'".bin2hex(random_bytes(16))."'";
	if($isImage){
		$width=getimagesize($path)[0];
		$height=getimagesize($path)[1];
		$ZUNIFORMTYPEIDENTIFIER='public.jpeg';
		$orientation=($width>$height)?3:6;
		$add_orientation=$orientation;
		$ZKIND=0;
		$ZHIGHDYNAMICRANGETYPE=1;
		$ZORIGINALHEIGHT=($width>$height)?$height:$width;
		$ZORIGINALWIDTH=($width>$height)?$width:$height;
		$ZDURATION='0.0';
		$exif=@exif_read_data($path);
		if(!empty($exif['DateTimeOriginal'])){
			$shootTimeStr=$exif['DateTimeOriginal'];
		}
		elseif(!empty($exif['CreateDate'])){
			$shootTimeStr=$exif['CreateDate'];
		}
		elseif(!empty($exif['DateTimeDigitized'])){
			$shootTimeStr=$exif['DateTimeDigitized'];
		}
		else{
			$shootTimeStr=null;
		}
		if($shootTimeStr){
			$shootUnix=strtotime(str_replace(':','-',substr($shootTimeStr,0,10)).substr($shootTimeStr,10));
		}
		else{
			$shootUnix=filemtime($path);
		}
	}
	else{
		$width=640;
		$height=480;
		$ZUNIFORMTYPEIDENTIFIER='com.apple.quicktime-movie';
		$orientation=1;
		$add_orientation=0;
		$ZKIND=1;
		$ZHIGHDYNAMICRANGETYPE=null;
		$ZORIGINALHEIGHT=0;
		$ZORIGINALWIDTH=0;
		$ZDURATION='4.0';
		$shootUnix=filemtime($path);
	}
	touch($path,$shootUnix,$shootUnix);
	$macEpoch=strtotime('2001-01-01 00:00:00');
	$macTime=round(($shootUnix-$macEpoch)+(rand(10,99999)/100000),5);
	$originalSize=filesize($path);
	$zgenericasset=[
		'Z_PK'=>$pk,
		'Z_ENT'=>$z_ent,
		'Z_OPT'=>$z_opt,
		'ZCLOUDHASCOMMENTSBYME'=>NULL,
		'ZCLOUDHASCOMMENTSCONVERSATION'=>NULL,
		'ZCLOUDHASUNSEENCOMMENTS'=>NULL,
		'ZCLOUDPLACEHOLDERKIND'=>0,
		'ZCOMPLETE'=>1,
		'ZFLAGGED'=>0,
		'ZHEIGHT'=>$height,
		'ZKIND'=>$ZKIND,
		'ZORIENTATION'=>$orientation,
		'ZSAVEDASSETTYPE'=>3,
		'ZTHUMBNAILINDEX'=>1,
		'ZWIDTH'=>$width,
		'ZADDITIONALATTRIBUTES'=>$pk,
		'ZCLOUDBATCHPUBLISHDATE'=>NULL,
		'ZCLOUDLASTVIEWEDCOMMENTDATE'=>NULL,
		'ZDATECREATED'=>$macTime,
		'ZMODIFICATIONDATE'=>ceil($macTime),
		'ZSORTTOKEN'=>$macTime,
		'ZCLOUDASSETGUID'=>NULL,
		'ZCLOUDASSETKIND'=>NULL,
		'ZCLOUDBATCHID'=>NULL,
		'ZCLOUDCOLLECTIONGUID'=>NULL,
		'ZDIRECTORY'=>$directory,
		'ZFILENAME'=>$filename,
		'ZTITLE'=>$title,
		'ZUNIFORMTYPEIDENTIFIER'=>$ZUNIFORMTYPEIDENTIFIER,
		'ZCLOUDMETADATA'=>NULL,
		'ZFACERECTANGLES'=>NULL,
		'ZUUID'=>$zuuid,
		'ZLOCATIONDATA'=>NULL,
		'ZIMAGEURLDATA'=>NULL,
		'ZTHUMBNAILURLDATA'=>NULL,
	];
	$zadditional=[
		'Z_PK'=>$pk,
		'Z_ENT'=>1,
		'Z_OPT'=>2,
		'ZEMBEDDEDTHUMBNAILHEIGHT'=>0,
		'ZEMBEDDEDTHUMBNAILLENGTH'=>0,
		'ZEMBEDDEDTHUMBNAILOFFSET'=>0,
		'ZEMBEDDEDTHUMBNAILWIDTH'=>0,
		'ZHIGHDYNAMICRANGETYPE'=>$ZHIGHDYNAMICRANGETYPE,
		'ZORIGINALFILESIZE'=>$originalSize,
		'ZORIGINALHEIGHT'=>$ZORIGINALHEIGHT,
		'ZORIGINALORIENTATION'=>$orientation,
		'ZORIGINALWIDTH'=>$ZORIGINALWIDTH,
		'ZASSET'=>$pk,
		'Z14_ASSET'=>15,
		'ZDURATION'=>$ZDURATION,
		'ZCREATORBUNDLEID'=>NULL,
		'ZORIGINALFILENAME'=>$filename,
		'ZORIGINALPATH'=>$directory,
		'ZIMPORTSESSIONID'=>NULL,
		'ZORIGINALASSETSUUID'=>NULL,
	];
	$album=[
		'Z_8ALBUMS'=>4,
		'Z_14ASSETS'=>$pk,
		'Z_FOK_14ASSETS'=>2048,
	];
	$columns=implode(',',array_keys($zgenericasset));
	$values=implode(',',array_map(fn($v)=>is_null($v)?'NULL':(is_numeric($v)?$v:"'$v'"),$zgenericasset));
	$sql.=str_replace("'',NULL","',NULL",str_replace("NULL,'X'","NULL,X'","INSERT INTO \"ZGENERICASSET\"($columns)VALUES($values);"))."\n";
	$columns=implode(',',array_keys($zadditional));
	$values=implode(',',array_map(fn($v)=>is_null($v)?'NULL':(is_numeric($v)?$v:"'$v'"),$zadditional));
	$sql.="INSERT INTO \"ZADDITIONALASSETATTRIBUTES\"($columns)VALUES($values);\n";
	$columns=implode(',',array_keys($album));
	$values=implode(',',array_map(fn($v)=>is_null($v)?'NULL':(is_numeric($v)?$v:"'$v'"),$album));
	$sql.="INSERT INTO \"Z_8ASSETS\"($columns)VALUES($values);\n";
	$pk++;
}
$sql.="UPDATE \"Z_PRIMARYKEY\" SET Z_MAX='".$pk."' WHERE Z_NAME='AdditionalAssetAttributes';\n";
$sql.="UPDATE \"Z_PRIMARYKEY\" SET Z_MAX='".$pk."' WHERE Z_NAME='GenericAsset';\n";
$sql.="COMMIT;\n";
file_put_contents($outputFile,$sql);
echo "SQL file generated: $outputFile\n";
?>

<?php
$csvFile='calls.csv';
$outputFile='insert_calls.sql';
$handle=fopen($csvFile,'r');
if(!$handle){
	die("Cannot open CSV file.\n");
}
$sql="BEGIN TRANSACTION;\n";
$header=fgetcsv($handle);
$rowId=1;
ini_set('date.timezone','Etc/GMT-3');
while(($data=fgetcsv($handle))!==false){
	list($from,$to,$startDate,$duration)=$data;
	$from=trim($from,'"');
	$to=trim($to,'"');
	$startDate=trim($startDate,'"');
	$duration=trim($duration,'"');
	preg_match('/(\d+)s$/',$duration,$matches);
	$totalSeconds=isset($matches[1])?intval($matches[1]):0;
	if($from==='Me'){
		$flags=5;
		$address=$to;
	}
	else{
		$flags=4;
		$address=$from;
	}
	$timestamp=strtotime($startDate);
	$mcc='255';
	$mnc='01';
	$sql.="INSERT INTO call VALUES($rowId,'$address',$timestamp,$totalSeconds,$flags,-1,'','$mcc','$mnc',1,0,NULL,NULL);\n";
	$rowId++;
}
$sql.="COMMIT;\n";
fclose($handle);
file_put_contents($outputFile,$sql);
echo "SQL file generated: $outputFile\n";
?>