<?php

namespace App\Http\Controllers;

use PDF;
use Carbon\Carbon;
use App\Models\User;
use App\Models\Sidang;
use App\Models\Pejabat;
use App\Models\Kategori;
use App\Models\Mahasiswa;
use Illuminate\Http\Request;
use App\Exports\ClcardExport;
use App\Models\KategoriJawab;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\Storage;
use Yajra\DataTables\Facades\DataTables;

class ClearancecardController extends Controller
{

    private $activeMenu = 'Home/Sidang/Clearancecard';

    // Admin & verifikator
    public function index()
    {
        return view('pages.portal.clearancecard.index', [
            'breadcrumb' => [
                'Home' => '/',
                'Sidang' => '/',
                'Clearance Card' => '/clearancecard'
            ],
            'activeMenu' => $this->activeMenu,
            'prodis' => $this->getProdiCombo(),
            'kelass' =>  $this->getKelasCombo(),
            'periodes' => $this->getPeriodeSidangCombo(),
            'bulans' => $this->getBulanSidangCombo(),
        ]);
    }

    public function getData($status = '')
    {

        $where = '';
        if ($status != '') {
            $where = 'AND status="' . $status . '"';
        }

        $ktgr = Sidang::where('periode', session('periode_sidang'))
            ->where('bulan', session('bulan_sidang'))
            ->where('sls_pbb1', 'Selesai')
            ->where('sls_pbb2', 'Selesai')
            ->where('nilai_pbb1', 'IS NOT NULL')
            ->where('nilai_pbb2', 'IS NOT NULL')
            ->count();

        if ($ktgr < 1) {
            $ktgr = 0;
        }

        return $ktgr;
    }

    public function list(Request $request)
    {
        if ($request->ajax()) {
            $where = '';

            if (!empty($request->periode)) {
                $where .= ' AND sidangs.periode ="' . $request->periode . '"';
            }

            if (!empty($request->bulan)) {
                $where .= ' AND sidangs.bulan ="' . $request->bulan . '"';
            }

            if (!empty($request->prodi)) {
                $where .= ' AND t.prodi_id LIKE "%' . $request->prodi . '%"';
            }

            if (!empty($request->kelas)) {
                $where .= ' AND t.kelas LIKE "%' . $request->kelas . '%"';
            }

            $data = Sidang::query()
                ->leftJoin('tors as t', 't.id', 'sidangs.tor_id')
                ->leftJoin('mahasiswas as m', 'm.id', 't.mahasiswa_id')
                ->leftJoin('prodis as p', 'p.id', 't.prodi_id')
                ->selectRaw('
                    sidangs.*,
                    t.kelas,
                    m.nim,
                    m.nama,
                    p.kode as kodeprodi,
                    p.prodi as prodi
                ')
                ->where('sls_pbb1', 'Selesai')
                ->whereNotNull('nilai_pbb1')
                ->where('sls_pbb2', 'Selesai')
                ->whereNotNull('nilai_pbb2')
                ->whereRaw('1 ' . $where)
                ->orderBy('t.prodi_id')
                ->orderBy('t.kelas')
                ->orderBy('m.nim');

            return DataTables::of($data)
                ->addIndexColumn()
                ->editColumn('nama', function (Sidang $sdg) {
                    return '<a href="javascript:void(0)" id="' . $sdg->id . '" class="detail text-primary" data-nim="' . $sdg->nim . '" data-nama="' . $sdg->nama . '">' . $sdg->nama . '</a>';
                })
                ->addColumn('totalMhs', function (Sidang $sidang, Request $request) {
                    $html = '';

                    /*   $where = " AND verifikator='" . auth()->id() . "'";

                    if (auth()->user()->role == 'kaprodi') {
                        $where = " AND verifikator=0";
                    }

                    if (auth()->user()->role == 'admin' || auth()->user()->role == 'admin adak' || auth()->user()->role == 'pejabat' || auth()->user()->role == 'admin prodi') {
                        $where = '';
                    } */

                    $totKtgr = Kategori::where('periode', $request->periode)
                        ->where('bulan', $request->bulan)
                        // ->whereRaw('1 ' . $where)
                        ->count();

                    $totMhs = KategoriJawab::where('periode', $request->periode)
                        ->where('bulan', $request->bulan)
                        ->where('mahasiswa_id', $sidang->mahasiswa_id)
                        // ->whereRaw('1 ' . $where)
                        ->count();

                    $html = $totKtgr . '/' . $totMhs;
                    return $html;
                })
                ->addColumn('totalVerifikator', function (Sidang $sidang, Request $request) {
                    $html = '';

                    /*  $where = " AND verifikator='" . auth()->id() . "'";

                    if (auth()->user()->role == 'kaprodi') {
                        $where = " AND verifikator=0";
                    }

                    if (auth()->user()->role == 'admin' || auth()->user()->role == 'admin adak' || auth()->user()->role == 'pejabat' || auth()->user()->role == 'admin prodi') {
                        $where = '';
                    } */

                    $totKtgr = Kategori::where('periode', $request->periode)
                        ->where('bulan', $request->bulan)
                        // ->whereRaw('1 ' . $where)
                        ->count();

                    $totVer = KategoriJawab::where('periode', $request->periode)
                        ->where('bulan', $request->bulan)
                        ->where('mahasiswa_id', $sidang->mahasiswa_id)
                        ->where('status', 'Diterima')
                        // ->whereRaw('1 ' . $where)
                        ->count();

                    $html = $totKtgr . '/' . $totVer;
                    return $html;
                })
                ->addColumn('fnstatus', function (Sidang $sidang, Request $request) {
                    $html = '-';

                    /* $where = " AND verifikator='" . auth()->id() . "'";

                    if (auth()->user()->role == 'kaprodi') {
                        $where = " AND verifikator=0";
                    }

                    if (auth()->user()->role == 'admin' || auth()->user()->role == 'admin adak' || auth()->user()->role == 'pejabat' || auth()->user()->role == 'admin prodi') {
                        $where = '';
                    } */

                    $totKtgr = Kategori::where('periode', $request->periode)
                        ->where('bulan', $request->bulan)
                        // ->whereRaw('1 ' . $where)
                        ->count();

                    $totVer = KategoriJawab::where('periode', $request->periode)
                        ->where('bulan', $request->bulan)
                        ->where('mahasiswa_id', $sidang->mahasiswa_id)
                        ->where('status', 'Diterima')
                        // ->whereRaw('1 ' . $where)
                        ->count();
                    if (
                        $totVer > 0 && $totKtgr > 0 && $totVer ==
                        $totKtgr
                    ) {
                        $html = 'Selesai';
                        Sidang::find($sidang->id)
                            ->update([
                                'clcard_status' => 'Selesai'
                            ]);
                    } else {
                        Sidang::find($sidang->id)
                            ->update([
                                'clcard_status' => NULL
                            ]);
                    }
                    return $html;
                })
                ->addColumn('cetak', function (Sidang $sidang, Request $request) {
                    $html = '-';
                    if (auth()->user()->role == 'admin' || auth()->user()->role == 'admin adak' || auth()->user()->role == 'admin prodi' || auth()->user()->role == 'kaprodi' || auth()->user()->role == 'pejabat') {
                        $html = '<a href="' . url("/clearancecard/print/" . Crypt::encryptString($sidang->id)) . '"  target="_blank" class="btn btn-xs btn-primary "><i class="fas fa-print  fa-sm"></i></a>';
                    }
                    return $html;
                })
                ->rawColumns(['nama', 'totalMhs', 'totalVerifikator', 'fnstatus', 'cetak'])
                ->make(true);
        }
    }

    public function detail(Request $request)
    {
        if ($request->ajax()) {
            $where = " AND kategoris.verifikator='" . auth()->id() . "'";

            if (auth()->user()->role == 'kaprodi') {
                $where = " AND kategoris.verifikator=0";
            }

            if (auth()->user()->role == 'admin' || auth()->user()->role == 'admin adak' || auth()->user()->role == 'pejabat' || auth()->user()->role == 'admin prodi') {
                $where = '';
            }

            $data = Kategori::query()
                ->leftJoin('kategori_jawabs as kajab', function ($q) use ($request) {
                    $q->on('kajab.kategori_id', 'kategoris.id');
                    $q->where('kajab.sidang_id', $request->id);
                })
                ->leftJoin('users as usr', 'usr.id', 'kategoris.verifikator')
                ->selectRaw('
                    kategoris.id,
                    kategoris.periode,
                    kategoris.bulan,
                    kategoris.nama,
                    kategoris.deskripsi,
                    kategoris.upload,
                    kategoris.template,
                    kategoris.tipe,
                    kategoris.size,
                    kategoris.verifikator,
                    kategoris.updated_at,
                    kajab.id as id_jawab,
                    kajab.status,
                    kajab.status as statusAsli,
                    kajab.file,
                    kajab.catatan,
                    kajab.verifikasi_at,
                    kajab.upload_at,
                    usr.name as vername,
                    usr.role as verole
                ')
                ->where('kategoris.periode', session('periode_sidang'))
                ->where('kategoris.bulan', session('bulan_sidang'))
                ->whereRaw('1 ' . $where)
                ->orderBy('kategoris.id', 'asc');

            return DataTables::of($data)
                ->addIndexColumn()
                ->editColumn('file', function (Kategori $ktg) {
                    $html = '';
                    if ($ktg->upload == '' && $ktg->file == '') {
                        $html = '-';
                    } else if ($ktg->file == '' || $ktg->file == NULL || !$ktg->file) {
                        $html = '-';
                    } else {
                        $html = '<a href="' . asset('storage/' . $ktg->file) . '" target="_blank" class="text-primary"><i class="far fa-file text-primary"></i> Uploaded file</a><br><small class="text-muted"><i class="far fa-calendar-alt text-muted mr-1"></i>' . Carbon::parse($ktg->upload_at)->format('Y-m-d') . '</small>';
                    }
                    return $html;
                })
                ->editColumn('status', function (Kategori $ktg) {
                    $html = $ktg->status;
                    if ($ktg->upload == '' && $ktg->status == '') {
                        $html = 'Menunggu<br>Verifikasi';
                    } else if ($ktg->status == '' || $ktg->status == NULL || !$ktg->status) {
                        $html = 'Belum Upload';
                    } else if ($ktg->status == 'Diterima') {
                        $html = '<span class="">Diterima <i class="fas fa-check-circle text-success" ></i></span><br><small class="text-muted"><i class="far fa-calendar-alt text-muted mr-1"></i>' . $ktg->verifikasi_at . '</small>';
                    } else if ($ktg->status == 'Ditolak') {
                        $html = '<span class="text-danger">Ditolak</span><br><small class="text-muted"><i class="far fa-calendar-alt text-muted mr-1"></i>' . $ktg->verifikasi_at . '</small><br><div style="white-space:normal;"><i class="fas fa-info-circle fa-sm text-warning"></i> ' . $ktg->catatan . '</div>';
                    }
                    return $html;
                })
                ->rawColumns(['file', 'status'])
                ->make(true);
        }
    }

    public function detailStore(Request $request)
    {
        $data = [
            'status' => 'Data tidak terkirim ke server'
        ];

        if ($request->ajax()) {
            $sid = $request->sid;
            $mid = Sidang::find($sid);
            $mid = $mid->mahasiswa_id;
            $periode = $request->periode;
            $bulan = $request->bulan;
            $kategori = $request->kategori;
            $idjawab = $request->idjawab;
            $status = $request->status;
            $catatan = $request->catatan;
            $verifikator = auth()->id();

            if ($idjawab == '' || $idjawab == null) {
                // Simpan baru
                KategoriJawab::create([
                    'periode' => $periode,
                    'bulan' => $bulan,
                    'mahasiswa_id' => $mid,
                    'sidang_id' => $sid,
                    'kategori_id' => $kategori,
                    'status' => $status,
                    'catatan' => $catatan,
                    'verifikator' => $verifikator,
                    'upload_at' => date('Y-m-d'),
                    'verifikasi_at' => date('Y-m-d')
                ]);
            } else {
                KategoriJawab::find($idjawab)
                    ->update([
                        'status' => $status,
                        'catatan' => $catatan,
                        'verifikator' => $verifikator,
                        'verifikasi_at' => date('Y-m-d')
                    ]);
            }

            $data = [
                'status' => 'Data berhasil disimpan'
            ];
        }

        return response()->json($data);
    }

    // Mahasiswa
    public function clearancecard()
    {
        $sid = Mahasiswa::where('nim', auth()->user()->username)
            ->first();

        return view('pages.portal.clearancecard.mahasiswa', [
            'breadcrumb' => [
                'Home' => '/',
                'Sidang' => '/',
                'Clearance Card' => '/clearancecard/mahasiswa'
            ],
            'activeMenu' => $this->activeMenu,
            'sid' => $sid,
            'totKategori' => $this->getTotKategori(),
            'totJawab' => $this->getTotJawab(),
            'totIsi' => $this->getTotKategoriStatus(),
            'totMenunggu' => $this->getTotKategoriStatus('Menunggu Verifikasi'),
            'totDiterima' => $this->getTotKategoriStatus('Diterima'),
            'totDitolak' => $this->getTotKategoriStatus('Ditolak'),
        ]);
    }

    public function getTotKategori()
    {
        $ktgr = Kategori::where('periode', session('periode_sidang'))
            ->where('bulan', session('bulan_sidang'))
            ->count();

        if ($ktgr < 1) {
            $ktgr = 0;
        }

        return $ktgr;
    }

    public function getTotKategoriStatus($status = '')
    {
        $usr = User::find(auth()->id());

        $where = '';
        if ($status != '') {
            $where = 'AND status="' . $status . '"';
        }

        $ktgr = Kategori::where('periode', session('periode_sidang'))
            ->where('bulan', session('bulan_sidang'))
            ->whereRaw('id IN (SELECT kategori_id FROM kategori_jawabs WHERE periode="' . session('periode_sidang') . '" AND bulan="' . session('bulan_sidang') . '" AND mahasiswa_id=' . $usr->mahasiswa->id . ' ' . $where . ')')
            ->count();

        if ($ktgr < 1) {
            $ktgr = 0;
        }

        return $ktgr;
    }

    public function getTotJawab()
    {
        $usr = User::find(auth()->id());

        $ktgr = KategoriJawab::where('periode', session('periode_sidang'))
            ->where('bulan', session('bulan_sidang'))
            ->where('mahasiswa_id', $usr->mahasiswa->id)
            ->where('status', 'Diterima')
            ->count();

        if ($ktgr < 1) {
            $ktgr = 0;
        }

        return $ktgr;
    }

    public function mahasiswaList(Request $request)
    {
        if ($request->ajax()) {

            $data = Kategori::query()
                ->leftJoin('kategori_jawabs as kajab', function ($q) {
                    $usr = User::find(auth()->id());
                    $q->on('kajab.kategori_id', 'kategoris.id');
                    $q->where('kajab.mahasiswa_id', $usr->mahasiswa->id);
                })
                ->leftJoin('users as usr', 'usr.id', 'kategoris.verifikator')
                ->selectRaw('
                    kategoris.id,
                    kategoris.periode,
                    kategoris.bulan,
                    kategoris.nama,
                    kategoris.deskripsi,
                    kategoris.upload,
                    kategoris.template,
                    kategoris.tipe,
                    kategoris.size,
                    kategoris.verifikator,
                    kategoris.updated_at,
                    kajab.id as id_jawab,
                    kajab.status,
                    kajab.file,
                    kajab.catatan,
                    kajab.verifikasi_at,
                    kajab.upload_at,
                    usr.name as vername,
                    usr.role as verole
                ')
                ->where('kategoris.periode', session('periode_sidang'))
                ->where('kategoris.bulan', session('bulan_sidang'))
                ->orderBy('kategoris.id', 'asc');

            /*  foreach ($data->get() as $d ) {
                    # code...
                } */
            return DataTables::of($data)
                ->addIndexColumn()
                ->editColumn('insert', function (Kategori $ktg) {
                    if ($ktg->upload == '' || $ktg->upload == NULL) {
                        $usr = User::find(auth()->id());

                        $sid = Sidang::select('id')->where('mahasiswa_id', $usr->mahasiswa->id)->where('periode', session('periode_sidang'))->where('bulan', session('bulan_sidang'))->first();

                        $kajabs = KategoriJawab::where('periode', session('periode_sidang'))
                            ->where('bulan', session('bulan_sidang'))
                            ->where('mahasiswa_id', $usr->mahasiswa->id)
                            ->where('kategori_id', $ktg->id)
                            ->count();
                        if ($kajabs == 0) {
                            // Simpan baru
                            KategoriJawab::create([
                                'periode' => session('periode_sidang'),
                                'bulan' => session('bulan_sidang'),
                                'mahasiswa_id' => $usr->mahasiswa->id,
                                'sidang_id' => $sid['id'],
                                'kategori_id' => $ktg->id,
                                'status' => 'Menunggu Verifikasi',
                                'verifikator' => $ktg->verifikator,
                                'upload_at' => date('Y-m-d'),
                            ]);
                        } else {
                            // Update
                            KategoriJawab::where('kategori_id', $ktg->id)->update([
                                'verifikator' => $ktg->verifikator
                            ]);
                        }
                    }

                    return '';
                })
                ->rawColumns(['insert'])
                ->make(true);
        }
    }

    public function mahasiswaStore(Request $request)
    {
        $usr = User::find(auth()->id());

        $id = $request->id;
        $periode = session('periode_sidang');
        $bulan = session('bulan_sidang');
        $mid = $usr->mahasiswa->id;
        $nama = $usr->name;
        $sid = Sidang::select('id')->where('mahasiswa_id', $usr->mahasiswa->id)->where('periode', session('periode_sidang'))->where('bulan', session('bulan_sidang'))->first();

        $verifikator = $request->verifikator;


        $path = 'file/clearancecard';
        $files =  $request->file('file');
        $filename = $periode . '_' . $bulan . '_sidang_' . $nama;
        $ext = $files->getClientOriginalExtension();
        $file = $filename . '.' . $ext;

        $kajab = KategoriJawab::where('periode', $periode)
            ->where('bulan', $bulan)
            ->where('mahasiswa_id', $mid)
            ->where('kategori_id', $id)
            ->first();

        if ($kajab == '') {
            // Memindahkan file upload
            $file = $files->storeAs($path, $file, 'public');

            // Simpan baru
            KategoriJawab::create([
                'periode' => $periode,
                'bulan' => $bulan,
                'mahasiswa_id' => $mid,
                'sidang_id' => $sid['id'],
                'kategori_id' => $id,
                'file' => $file,
                'status' => 'Menunggu Verifikasi',
                'verifikator' => $verifikator,
                'upload_at' => date('Y-m-d'),
            ]);
        } else {

            // Hapus file sebelumnya
            if ($request->hasFile('file')) {
                Storage::delete('public/' . $kajab->file);
            }

            // Memindahkan file upload
            $file = $files->storeAs($path, $file, 'public');

            // Update data
            KategoriJawab::find($kajab->id)->update([
                'file' => $file,
                'status' => 'Menunggu Verifikasi',
                'catatan' => '',
                'upload_at' => date('Y-m-d'),
            ]);
        }

        return back()->with('success', 'Data berhasil disimpan');
    }

    public function print($id)
    {
        $id = Crypt::decryptString($id);
        $data = [];

        $sidang = Sidang::find($id);

        $ktg = Kategori::query()
            ->leftJoin('kategori_jawabs as kajab', function ($q) use ($id) {
                $q->on('kajab.kategori_id', 'kategoris.id');
                $q->where('kajab.sidang_id', $id);
            })
            ->leftJoin('users as usr', 'usr.id', 'kategoris.verifikator')
            ->selectRaw('
        kategoris.id,
        kategoris.periode,
        kategoris.bulan,
        kategoris.nama,
        kategoris.deskripsi,
        kategoris.upload,
        kategoris.template,
        kategoris.tipe,
        kategoris.size,
        kategoris.verifikator,
        kategoris.updated_at,
        kajab.id as id_jawab,
        kajab.status,
        kajab.status as statusAsli,
        kajab.file,
        kajab.catatan,
        kajab.verifikasi_at,
        kajab.upload_at,
        usr.name as vername,
        usr.role as verole
    ')
            ->orderBy('kategoris.id', 'asc')
            ->get();

        $kasubag = Pejabat::where('kode', 'kasubag')
            ->where('bagian', 'kks')->first();

        $data = [
            'data' => $sidang,
            'ktgs' => $ktg,
            'kasubag' => $kasubag,
            'tgl' => 'Bandung, ' . Carbon::parse(date('d M Y'))->translatedFormat('d F Y')
        ];

        $pdf = PDF::loadView('pages.portal.clearancecard.cetak', $data);
        $fileName =  'Clearancecard-' . $sidang->mahasiswa->nim . '-' . $sidang->mahasiswa->nama . '.pdf';
        return $pdf->stream($fileName);
    }

    function export($params)
    {
        $params = explode('/', $params);
        $periode = $params[0];
        $bulan = $params[1];
        $prodi = '';
        $kelas = '';
        $status = '';

        if (!empty($params[2]))
            $prodi = $params[2];

        if (!empty($params[3]))
            $kelas = $params[3];

        if (!empty($params[4]))
            $status = $params[4];

        return Excel::download(new ClcardExport($periode, $bulan, $prodi, $kelas, $status), 'Sidang-Data-Clearancecard-' . date('Ymd') . '.xlsx');
    }
}
