<?php

include '../conf/koneksiPDO.php';
include '../conf/function.php';

header('Content-Type: application/json');
$conn = new createCon();
$dbh = $conn->connect();
$dbhSpota = $conn->connectSpota();

$tahun = date('Y');
$tahunTerakhir = $tahun - 7;

$sql = 'SELECT idMahasiswa FROM mahasiswa';
$stmt = $dbh->prepare($sql);
$stmt->execute();

$listMahasiswaDb = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $idDb = $row['idMahasiswa'];
    $listMahasiswaDb[$idDb] = true;
}

//$data = file_get_contents('http://spota.untan.ac.id/steven/API/getListMahasiswa.php?secret=in14d4lahP4ssWordSP0TA!12!');
//$url = "http://spota.untan.ac.id/steven/API/getListMahasiswa.php?withHP&mulaiTahun=$tahunTerakhir&secret=in14d4lahP4ssWordSP0TA!12!";
/*
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$data = curl_exec($ch);
curl_close($ch);
*/

//$data = file_get_contents($url);
//$decoded = json_decode($data, 1);

$whereThnMasuk = " WHERE thnmasuk >= $tahunTerakhir ";

$sql = "SELECT * FROM tbmhs $whereThnMasuk";
$stmt = $dbhSpota->prepare($sql);
$stmt->execute();

$data = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $id = $row['idmhs'];
    $nim = $row['nim'];
    $nama = $row['nmLengkap'];
    $email = $row['email'];
    $foto = $row['foto'];
    $angkatan = $row['thnmasuk'];
    $urlFoto = "http://spota.untan.ac.id/img/$foto";

    $finalData = [
        'id' => $id,
        'nim' => $nim,
        'nama' => $nama,
        'email' => $email,
        'urlFoto' => $urlFoto,
        'angkatan' => $angkatan,
    ];

    $finalData['hp'] = $row['noHP'];
    $finalData['hpOrtu'] = $row['noHPOrtu'];
    
    array_push($data, $finalData);
}

$decoded = ['data' => $data];
$banyakData = sizeof($decoded['data']);

for ($i = 0; $i < $banyakData; ++$i) {
    $mahasiswa = $decoded['data'][$i];
    $id = $mahasiswa['id'];
    $nip = $mahasiswa['nim'];
    $nama = $mahasiswa['nama'];
    $email = $mahasiswa['email'];
    $urlFoto = $mahasiswa['urlFoto'];
    $angkatan = $mahasiswa['angkatan'];
    $hp = $mahasiswa['hp'];
    $hpOrtu = $mahasiswa['hpOrtu'];

    if (!isset($listMahasiswaDb[$id])) {
        $sql = 'INSERT INTO mahasiswa(idMahasiswa, nim, nama, email, urlFoto, angkatan, hp, hpOrtu) VALUES (:id, :nip, :nama, :email, :urlFoto, :angkatan, :hp, :hpOrtu)';
        $stmt = $dbh->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':nip', $nip);
        $stmt->bindParam(':nama', $nama);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':angkatan', $angkatan);
        $stmt->bindParam(':hp', $hp);
        $stmt->bindParam(':hpOrtu', $hpOrtu);
        $stmt->bindParam(':urlFoto', $urlFoto);
        $stmt->execute();
    } else {
        $sql = 'UPDATE mahasiswa SET nim = :nip, nama = :nama, email = :email, urlFoto = :urlFoto, angkatan = :angkatan, hp = :hp, hpOrtu = :hpOrtu WHERE idMahasiswa = :id';
        $stmt = $dbh->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':nip', $nip);
        $stmt->bindParam(':nama', $nama);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':angkatan', $angkatan);
        $stmt->bindParam(':urlFoto', $urlFoto);
        $stmt->bindParam(':hp', $hp);
        $stmt->bindParam(':hpOrtu', $hpOrtu);
        $stmt->execute();
    }
}

echo json_encode(['status' => 1, 'msg' => 'ok']);