<?php

namespace App\Controllers\Panel\MasterData;

use App\Controllers\Panel\BC;
use App\Entities\Mobile\User;
use CodeIgniter\API\ResponseTrait;
use ZipArchive;

class Import extends BC
{
    use ResponseTrait;
    private $siswaPhoto = [];
    private $isZip = false;
    private $path = WRITEPATH . 'uploads/dump';
    private $masterData, $spreadsheet, $db;
    public function __construct()
    {
        parent::__construct();
    }
    public function index()
    {
        return view('Panel/MasterData/Import/index', ['page_title' => "Import Data"]);
    }
    public function run()
    {
        set_time_limit(0);
        if (!$this->request->isAJAX() && ENVIRONMENT != 'development') {
            exit('No direct script access allowed');
        }
        $allowedFile = [
            'application/vnd.ms-excel',
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',

            'application/zip',
            'application/x-zip-compressed',
            'multipart/x-zip',
            'application/octet-stream'
        ];
        $file = $this->request->getFile('file');
        if ($file->hasMoved()) return $this->fail(['message' => "File Tidak Ditemukan", 'csrf' => csrf_hash()], 404);
        if (!in_array($file->getMimeType(), $allowedFile)) return $this->fail(['message' => "Format Tidak Diizinkan", 'csrf' => csrf_hash()], 403);
        $this->masterData = $file->getClientName();
        $this->isZip = $file->getMimeType() != 'application/vnd.ms-excel' && $file->getMimeType() != 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
        if ($this->isZip) {
            $za = new ZipArchive();
            $filePath = $file->getPath() . "/" . $file->getFilename();
            $this->path = "$this->path/{$file->getFilename()}";
            if (!file_exists("$this->path")) mkdir("$this->path", 0777, true);
            $za->open($filePath);
            $za->extractTo($this->path);
            $za->close();
            foreach (scandir("$this->path") as $key => $value) {
                if (in_array($value, ['.', '..'])) continue;
                if (is_dir("$this->path/$value") && $value != 'siswa') continue;
                $n = explode('.', $value);
                if (end($n) == 'xlsx' || end($n) == 'xls') {
                    $this->masterData = $value;
                } else if (is_dir("$this->path/$value") && $value == 'siswa') {
                    foreach (scandir("$this->path/$value") as $sk => $sv) {
                        if (in_array($value, ['.', '..'])) continue;
                        if (is_dir("$this->path/$value/$sv")) continue;
                        $n = explode('.', $sv);
                        if (count($n) < 2) continue;
                        if (!in_array(end($n), ['jpg', 'png', 'jpeg'])) continue;
                        unset($n[count($n) - 1]);
                        $n = explode('-', implode('.', $n));
                        $n = $n[0];
                        $this->siswaPhoto[$n] = $sv;
                    }
                }
            }
        } else {
            $file->move($this->path, null, true);
            rmbfe('php', $this->path);
        }
        if (!$this->masterData) return $this->fail(['message' => "File Tidak Ditemukan", 'csrf' => csrf_hash()], 404);
        if ($this->generateData()) return $this->respond(['message' => 'Berhasil Mengimport']);
        return $this->fail(['message' => "Gagal Mengimport,Periksa Lagi", 'csrf' => csrf_hash()], 500);
    }
    private function generateData()
    {
        $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify("$this->path/$this->masterData");
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
        $this->spreadsheet = $reader->load("$this->path/$this->masterData");
        $this->db = db_connect();
        $this->db->transStart();
        if ($this->spreadsheet->sheetNameExists("RUANG")) {
            foreach ($this->spreadsheet->getSheetByName("RUANG")->toArray() as $key => $value) {
                if ($key == 0 || !$value[0]) continue;
                $ruang = "INSERT INTO m_ruangujian (kode,nama) SELECT * FROM (SELECT ? kode,? nama) AS tmp WHERE NOT EXISTS (SELECT kode FROM m_ruangujian WHERE kode = ?) LIMIT 1";
                $this->db->query($ruang, [$value[0], $value[1], $value[0]]);
            }
        }
        if ($this->spreadsheet->sheetNameExists("JURUSAN")) {
            $data['m_jurusan'] = [];
            foreach ($this->spreadsheet->getSheetByName("JURUSAN")->toArray() as $key => $value) {
                if ($key == 0 || !$value[0]) continue;
                $jurusan = "INSERT INTO m_jurusan (kode,nama) SELECT * FROM (SELECT ? kode,? nama) AS tmp WHERE NOT EXISTS (SELECT kode FROM m_jurusan WHERE kode = ?) LIMIT 1";
                $this->db->query($jurusan, [$value[0], $value[1], $value[0]]);
            }
        }
        if ($this->spreadsheet->sheetNameExists("JENIS_UJIAN")) {
            $data['m_jenisujian'] = [];
            foreach ($this->spreadsheet->getSheetByName("JENIS_UJIAN")->toArray() as $key => $value) {
                if ($key == 0 || !$value[0]) continue;
                $jenisUjian = "INSERT INTO m_jenisujian (kode,nama) SELECT * FROM (SELECT ? kode,? nama) AS tmp WHERE NOT EXISTS (SELECT kode FROM m_jenisujian WHERE kode = ?) LIMIT 1";
                $this->db->query($jenisUjian, [$value[0], $value[1], $value[0]]);
            }
        }
        if ($this->spreadsheet->sheetNameExists("TINGKAT")) {
            $data['m_tingkat'] = [];
            foreach ($this->spreadsheet->getSheetByName("TINGKAT")->toArray() as $key => $value) {
                if ($key == 0 || !$value[0]) continue;
                $tingkat = "INSERT INTO m_tingkat (kode) SELECT * FROM (SELECT ?) AS tmp WHERE NOT EXISTS (SELECT kode FROM m_tingkat WHERE kode = ?) LIMIT 1";
                $this->db->query($tingkat, [$value[0], $value[0]]);
            }
        }
        if ($this->spreadsheet->sheetNameExists("MATA_PELAJARAN")) {
            $data['m_mapel'] = [];
            foreach ($this->spreadsheet->getSheetByName("MATA_PELAJARAN")->toArray() as $key => $value) {
                if ($key == 0 || !$value[1]) continue;
                $mapel = "INSERT INTO m_mapel (kode,nama) SELECT * FROM (SELECT ? kode,? nama) AS tmp WHERE NOT EXISTS (SELECT kode FROM m_mapel WHERE kode = ?) LIMIT 1";
                $this->db->query($mapel, [$value[1], $value[2], $value[1]]);
            }
        }
        if ($this->spreadsheet->sheetNameExists("KELAS")) {
            $data['m_kelas'] = [];
            foreach ($this->spreadsheet->getSheetByName("KELAS")->toArray() as $key => $value) {
                $value[0] = cleanString($value[0] ?? '');
                if ($key == 0 || !$value[0]) continue;
                $kelas = "INSERT INTO m_kelas (kode,nama) SELECT * FROM (SELECT ? kode,? nama) AS tmp WHERE NOT EXISTS (SELECT kode FROM m_kelas WHERE kode = ?) LIMIT 1";
                $this->db->query($kelas, [$value[0], $value[1], $value[0]]);
                if ($this->db->affectedRows() > 0) {
                    $escData = [$value[0]];
                    $kelas_data = "INSERT INTO kelas_data SET kelas_id=(SELECT id FROM m_kelas WHERE kode = ? LIMIT 1)";
                    if (isset($value[2])) {
                        if ($value[2] != null) {
                            $escData[] = $value[2];
                            $kelas_data .= ",tingkat_id=(SELECT id FROM m_tingkat WHERE kode = ? LIMIT 1)";
                        }
                    }
                    if (isset($value[3])) {
                        if ($value[3] != null) {
                            $escData[] = $value[3];
                            $kelas_data .= ",jurusan_id=(SELECT id FROM m_jurusan WHERE kode = ? LIMIT 1)";
                        }
                    }
                    $this->db->query($kelas_data, $escData);
                }
            }
        }
        $this->import_siswa();
        $this->db->transComplete();
        return !($this->db->transStatus() === FALSE);
    }

    private function import_siswa()
    {
        if ($this->spreadsheet->sheetNameExists("DATA_SISWA")) {
            helper('Mobile/auth');
            $query = "INSERT INTO m_siswa";
            $query .= "(";
            $query .= "nomor_induk,attendance_solution_id,rfid,nama,agama,jenis_kelamin,tempat_lahir,";
            $query .= "tanggal_lahir,alamat,email,kelas_id,ruang_id,password_hash,phone,photo,created_at,updated_at";
            $query .= ")";
            $query .= "VALUES";
            $binding = [];
            $imgPath = config('app')->imgPath . "/users";
            foreach ($this->spreadsheet->getSheetByName("DATA_SISWA")->toArray() as $key => $value) {
                if ($key == 0) continue;
                if (!$value[1] || $value[15] != 'YA') continue;
                $query .= "(";
                // nomor_induk
                $query .= "?,";
                $binding[] = $value[1];
                // attendance_solution_id
                $query .= "?,";
                $binding[] = $value[2];
                // rfid
                $query .= "?,";
                $binding[] = $value[3];
                // nama
                $query .= "?,";
                $binding[] = $value[4];
                // agama
                $query .= "?,";
                $binding[] = strtolower($value[5]);
                // jenis_kelamin
                $query .= "?,";
                $binding[] = $value[6];
                // tempat_lahir
                $query .= "?,";
                $binding[] = $value[7];
                // tanggal_lahir
                $query .= "?,";
                $binding[] = $value[8];
                // alamat
                $query .= "?,";
                $binding[] = $value[9];
                // email
                $query .= "?,";
                $binding[] = $value[10];
                //kelas_id
                $query .= "(SELECT id FROM m_kelas WHERE kode=? LIMIT 1),";
                $binding[] = cleanString($value[11]);
                // ruang_id
                $query .= "(SELECT id FROM m_ruangujian WHERE kode=? LIMIT 1),";
                $binding[] = $value[12];
                // password_hash
                $query .= "BINARY ?,";
                $binding[] = (new User())->generatePassword($value[13]);
                // phone
                $query .= "?,";
                $binding[] = $value[14];
                // photo
                if ($this->isZip && isset($this->siswaPhoto[$value[1]])) {
                    $photoSource = "$this->path/siswa/" . $this->siswaPhoto[$value[1]];
                    $pn = explode('.', basename($photoSource));
                    $pn = uniqid() . '.' . end($pn);
                    $query .= '?';
                    $binding[] = "$pn";
                    rename($photoSource, "$imgPath/$pn");
                } else {
                    $query .= "IFNULL((SELECT (CASE WHEN photo IS NULL OR photo ='' THEN 'profile-photo.png' ELSE photo END) photo FROM (SELECT DISTINCT photo FROM m_siswa WHERE nomor_induk = ?) tmp),'profile-photo.png')";
                    $binding[] = $value[1];
                }
                // created_at
                $query .= ",?,";
                $binding[] = time();
                // updated_at
                $query .= "?";
                $binding[] = time();
                $query .= "),";
            }
            $query = rtrim($query, ',');
            $query .= " ON DUPLICATE KEY UPDATE ";
            $query .= "nomor_induk=VALUES(nomor_induk),attendance_solution_id=VALUES(attendance_solution_id),rfid=VALUES(rfid),";
            $query .= "nama=VALUES(nama),agama=VALUES(agama),jenis_kelamin=VALUES(jenis_kelamin),tempat_lahir=VALUES(tempat_lahir),";
            $query .= "tanggal_lahir=VALUES(tanggal_lahir),alamat=VALUES(alamat),email=VALUES(email),kelas_id=VALUES(kelas_id),";
            $query .= "ruang_id=VALUES(ruang_id),password_hash=VALUES(password_hash),phone=VALUES(phone),photo=VALUES(photo),";
            $query .= "created_at=VALUES(created_at),updated_at=VALUES(updated_at)";
            if (count($binding) > 0) $this->db->query($query, $binding);
        }
    }

    public function example($type)
    {
        switch ($type) {
            case 'excel':
                return $this->response->download($this->fileLocation['DATA_MASTER.xlsx'], null);
                break;
            case 'zip':
                return $this->response->download($this->fileLocation['DATA_MASTER.zip'], null);
                break;
        }
    }
}
