<?php
# Fill our vars and run on cli
# $ php -f db-connect-test.php

$dbname =  getenv('WIQUADRO_DB');
$dbuser =  getenv('WIQUADRO_USERNAME');
$dbpass =  getenv('WIQUADRO_PASSWORD');
$dbhost =  getenv('WIQUADRO_HOST');

$connect = mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'");
echo "Conexão estabelecida\n";
mysql_select_db($dbname) or die("Could not open the db '$dbname'");

echo "inicializando...\n";
$alunos = getAllAlunos();

foreach($alunos as $aluno) {
	echo 'id alunoturma...' . $aluno . "\n";
	$licoes = getEdcAluno($aluno);
	foreach($licoes as $licao) {
		$edcsmidia = getEdcsMidia($licao);
		checkAluno($licao, $edcsmidia[0], $edcsmidia[1]);
	}
}

echo "finalizando....\n";

function getAllAlunos() {

	$sql = "SELECT at.Id FROM alunoturma at INNER JOIN turma t ON at.IdTurma = t.Id INNER JOIN
	projetoescola pe ON pe.Id = t.IdProjetoEscola INNER JOIN projeto p ON p.Id = pe.IdProjeto
	WHERE p.Id = 57";

	echo "fetching alunos...\n";

	$result = mysql_query($sql) or die(mysql_error());
    $arr = [];

	while ($row = mysql_fetch_array($result)) {
    	$arr[] = $row[0];
	}

	return  $arr;
}

//lições em que está matriculado
function getEdcAluno($idAlunoTurma){
	$sql = "SELECT * FROM alunoturmaedc WHERE IdAlunoTurma =".$idAlunoTurma;

	$result = mysql_query($sql) or die(mysql_error());

	$licoes = [];

	while ($row = mysql_fetch_array($result)) {
    	$licoes[] = $row['IdEdc'];
	}

	return $licoes;
}

function getEdcsMidia($idEdc) {

	$sql = "SELECT Id, Ordem FROM edcmidia WHERE ultimo = 1 and IdEdc = {$idEdc}";
	$result = mysql_query($sql) or die(mysql_error());
	$ultima = mysql_fetch_assoc($result);

	$sql2 = "SELECT Id FROM edcmidia edcm WHERE IdEdc = $idEdc and edcm.Ordem =  {$ultima['Ordem']} - 1";
	$res = mysql_query($sql2) or die(mysql_error());
	$penultima = mysql_fetch_assoc($res);

	$arr[0] = $ultima['Id'];
	$arr[1] = $penultima['Id'];
	return $arr;
}


function checkAluno($IdAlunoTurmaEdc,$idEdcMidiaUltima, $idEdcMidiaPenultima) {

	$today = date('Y-m-d H:i:s');

	$sql = "SELECT * FROM alunoturmaedcmidia WHERE IdAlunoTurmaEdc = {$IdAlunoTurmaEdc}
			AND IdEdcMidia = {$idEdcMidiaUltima} AND DataConclusao IS NULL";

	$result = mysql_query($sql);

	if(!mysql_num_rows($result) == 0) {
		$sqlinsert = "INSERT INTO alunoturmaedcmidia (IdAlunoTurmaEdc,IdEdcMidia,DataConclusao)
						VALUES({$IdAlunoTurmaEdc}, {$idEdcMidiaUltima}, {$today})";

		echo $sqlinsert."\n";

		if (mysql_query($sql)) {
		    echo "New record created successfully IdAlunoTurmaEdc = {$IdAlunoTurmaEdc} idEdcMidiaUltima = {$idEdcMidiaUltima} ";
		} else {
	    	echo "Error: " . $sql . "<br>" . mysql_error($connect);
		}

	} else {

		$sql = "SELECT * FROM alunoturmaedcmidia WHERE IdAlunoTurmaEdc = {$IdAlunoTurmaEdc}
			AND IdEdcMidia = $idEdcMidiaPenultima AND DataConclusao IS NOT NULL";

		$result = mysql_query($sql);

		if(mysql_num_rows($result) == 0) {
			$sqlinsert = "INSERT INTO alunoturmaedcmidia (IdAlunoTurmaEdc,IdEdcMidia,DataInicio,DataConclusao)
							VALUES('{$IdAlunoTurmaEdc}', '{$idEdcMidiaUltima}','{$today}', '{$today}')";
			$sqlinsert2 = "INSERT INTO alunoturmaedcmidia (IdAlunoTurmaEdc,IdEdcMidia,DataConclusao)
							VALUES('{$IdAlunoTurmaEdc}', '{$idEdcMidiaPenultima}', '{$today}')";
			echo $sqlinsert."\n".$sqlinsert2."\n";

			mysql_query($sqlinsert) or die(mysql_error());

			mysql_query($sqlinsert2) or die(mysql_error());
		}
	}
}