<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use PhpOffice\PhpSpreadsheet\IOFactory;

class OpdKinerjaController extends BaseController
{
    private $db;

    public function __construct()
    {
        $this->db = \Config\Database::connect();
    }

    public function index()
    {
        $session = session();
        $opdId = $session->get('opd_id');

        $stats = [
            'total_kegiatan' => 0,
            'laporan_dikirim' => 0,
            'perlu_revisi' => 0,
            'disetujui' => 0
        ];

        // Hitung total kegiatan (dummy count for now, based on sub_kegiatan linked to this opd)
        // In real implementation, we would count distinct sub_kegiatan assigned to this OPD
        // For now, let's just count from 'realisasi' table as a proxy for activity
        $stats['laporan_dikirim'] = $this->db->table('realisasi')->where('opd_id', $opdId)->countAllResults();
        $stats['perlu_revisi'] = $this->db->table('realisasi')->where('opd_id', $opdId)->where('status', 'Revisi')->countAllResults();
        $stats['disetujui'] = $this->db->table('realisasi')->where('opd_id', $opdId)->where('status', 'Disetujui')->countAllResults();
        
        // Recent activity
        $recent = $this->db->table('realisasi')
            ->select('realisasi.*, sub_kegiatan.nama_sub_kegiatan')
            ->join('sub_kegiatan', 'sub_kegiatan.id = realisasi.sub_kegiatan_id')
            ->where('realisasi.opd_id', $opdId)
            ->orderBy('realisasi.updated_at', 'DESC')
            ->limit(5)
            ->get()->getResultArray();

        $data = [
            'title' => 'Dashboard OPD',
            'stats' => $stats,
            'recent' => $recent
        ];
        return view('opd/dashboard', $data);
    }

    public function input()
    {
        // Here we should list all Sub Kegiatan available for this OPD to report on.
        // Since we don't have the full Master Data import logic yet, 
        // I'll simulate fetching Sub Kegiatan.
        // In a real app, we would join Programs -> Kegiatan -> SubKegiatan where opd_id = session->opd_id
        
        $session = session();
        $opdId = $session->get('opd_id');

        // Placeholder query - assuming we have some sub_kegiatan linked to programs of this OPD
        // For now, let's just show an empty list or mock data if table is empty
        $subKegiatan = $this->db->table('sub_kegiatan')
            ->select('sub_kegiatan.*, kegiatan.nama_kegiatan, programs.nama_program')
            ->join('kegiatan', 'kegiatan.id = sub_kegiatan.kegiatan_id')
            ->join('programs', 'programs.id = kegiatan.program_id')
            ->where('programs.opd_id', $opdId)
            ->get()->getResultArray();

        $data = [
            'title' => 'Input Kinerja',
            'subKegiatan' => $subKegiatan
        ];
        return view('opd/input', $data);
    }

    public function store()
    {
        $session = session();
        $opdId = $session->get('opd_id');

        $data = [
            'sub_kegiatan_id' => $this->request->getPost('sub_kegiatan_id'),
            'opd_id' => $opdId,
            'tahun' => date('Y'), // Should be dynamic or selected
            'triwulan' => $this->request->getPost('triwulan'),
            'kinerja' => $this->request->getPost('kinerja'),
            'satuan' => $this->request->getPost('satuan'),
            'realisasi_kinerja' => $this->request->getPost('realisasi_kinerja'),
            'realisasi_anggaran' => $this->request->getPost('realisasi_anggaran'),
            'faktor_pendukung' => $this->request->getPost('faktor_pendukung'),
            'faktor_penghambat' => $this->request->getPost('faktor_penghambat'),
            'status' => 'Diajukan',
            'created_at' => date('Y-m-d H:i:s')
        ];

        // Handle File Upload
        $file = $this->request->getFile('file_bukti');
        if ($file && $file->isValid() && !$file->hasMoved()) {
            $newName = $file->getRandomName();
            $file->move('uploads', $newName);
            $data['file_bukti'] = $newName;
        }

        $this->db->table('realisasi')->insert($data);

        return redirect()->to('/opd_kinerja/dashboard')->with('success', 'Laporan kinerja berhasil dikirim');
    }

    public function riwayat()
    {
        $session = session();
        $opdId = $session->get('opd_id');

        $riwayat = $this->db->table('realisasi')
            ->select('realisasi.*, sub_kegiatan.nama_sub_kegiatan')
            ->join('sub_kegiatan', 'sub_kegiatan.id = realisasi.sub_kegiatan_id')
            ->where('realisasi.opd_id', $opdId)
            ->orderBy('realisasi.created_at', 'DESC')
            ->get()->getResultArray();

        $data = [
            'title' => 'Riwayat Laporan',
            'riwayat' => $riwayat
        ];
        return view('opd/riwayat', $data);
    }

    public function importExcel()
    {
        return redirect()->to('/opd_kinerja/dashboard')->with('error', 'OPD tidak diperbolehkan impor Excel. Silakan input progres secara manual.');
    }

    public function processImportExcel()
    {
        return redirect()->to('/opd_kinerja/dashboard')->with('error', 'OPD tidak diperbolehkan impor Excel.');

        $tahun = (int)$this->request->getPost('tahun');
        $triwulan = (string)$this->request->getPost('triwulan');
        if ($tahun < 2000 || $tahun > 2100) {
            $tahun = (int)date('Y');
        }
        if (!in_array($triwulan, ['1', '2', '3', '4'], true)) {
            return redirect()->to('/opd_kinerja/import_excel')->with('error', 'Triwulan tidak valid.');
        }

        $file = $this->request->getFile('file_excel');
        if (!$file || !$file->isValid()) {
            return redirect()->to('/opd_kinerja/import_excel')->with('error', 'File Excel tidak valid.');
        }

        $ext = strtolower($file->getClientExtension());
        if (!in_array($ext, ['xlsx', 'xls'], true)) {
            return redirect()->to('/opd_kinerja/import_excel')->with('error', 'Format file harus xlsx/xls.');
        }

        $tempName = $file->getRandomName();
        $file->move(WRITEPATH . 'uploads', $tempName);
        $path = WRITEPATH . 'uploads/' . $tempName;

        $importedSub = 0;
        $importedRealisasi = 0;
        $skipped = 0;

        $this->db->transBegin();
        try {
            $spreadsheet = IOFactory::load($path);
            $sheet = $spreadsheet->getSheet(0);
            $highestRow = $sheet->getHighestRow();

            $programId = null;
            $kegiatanId = null;

            $colKinerja = ['1' => 'K', '2' => 'M', '3' => 'O', '4' => 'Q'][$triwulan];
            $colAnggaran = ['1' => 'L', '2' => 'N', '3' => 'P', '4' => 'R'][$triwulan];

            for ($r = 1; $r <= $highestRow; $r++) {
                $segUrusan = trim((string)$sheet->getCell('B' . $r)->getFormattedValue());
                $segBidangUrusan = trim((string)$sheet->getCell('C' . $r)->getFormattedValue());
                $segProgram = trim((string)$sheet->getCell('D' . $r)->getFormattedValue());
                $segKegiatan = trim((string)$sheet->getCell('E' . $r)->getFormattedValue());
                $segSub = trim((string)$sheet->getCell('F' . $r)->getFormattedValue());
                $nama = trim((string)$sheet->getCell('G' . $r)->getFormattedValue());

                if ($nama === '' || $segUrusan === '' || $segBidangUrusan === '' || $segProgram === '') {
                    continue;
                }

                $targetKinerjaAngka = trim((string)$sheet->getCell('H' . $r)->getFormattedValue());
                $satuan = trim((string)$sheet->getCell('I' . $r)->getFormattedValue());
                $pagu = $this->parseCurrency((string)$sheet->getCell('J' . $r)->getFormattedValue());

                if ($segKegiatan === '') {
                    $kodeProgram = $this->buildKode([$segUrusan, $segBidangUrusan, $segProgram]);
                    $program = $this->db->table('programs')
                        ->where('opd_id', $opdId)
                        ->where('kode_program', $kodeProgram)
                        ->get()->getRowArray();

                    if (!$program) {
                        $this->db->table('programs')->insert([
                            'kode_program' => $kodeProgram,
                            'nama_program' => $nama,
                            'opd_id' => $opdId,
                            'created_at' => date('Y-m-d H:i:s'),
                        ]);
                        $programId = (int)$this->db->insertID();
                    } else {
                        $programId = (int)$program['id'];
                    }

                    $kegiatanId = null;
                    continue;
                }

                if ($segSub === '') {
                    if (!$programId) {
                        $skipped++;
                        continue;
                    }

                    $kodeKegiatan = $this->buildKode([$segUrusan, $segBidangUrusan, $segProgram, $segKegiatan]);
                    $kegiatan = $this->db->table('kegiatan')
                        ->where('program_id', $programId)
                        ->where('kode_kegiatan', $kodeKegiatan)
                        ->get()->getRowArray();

                    if (!$kegiatan) {
                        $this->db->table('kegiatan')->insert([
                            'program_id' => $programId,
                            'kode_kegiatan' => $kodeKegiatan,
                            'nama_kegiatan' => $nama,
                            'created_at' => date('Y-m-d H:i:s'),
                        ]);
                        $kegiatanId = (int)$this->db->insertID();
                    } else {
                        $kegiatanId = (int)$kegiatan['id'];
                    }

                    continue;
                }

                if (!$programId || !$kegiatanId) {
                    $skipped++;
                    continue;
                }

                $kodeSub = $this->buildKode([$segUrusan, $segBidangUrusan, $segProgram, $segKegiatan, $segSub]);

                $sub = $this->db->table('sub_kegiatan')
                    ->where('kegiatan_id', $kegiatanId)
                    ->where('kode_sub_kegiatan', $kodeSub)
                    ->get()->getRowArray();

                $subData = [
                    'kegiatan_id' => $kegiatanId,
                    'kode_sub_kegiatan' => $kodeSub,
                    'nama_sub_kegiatan' => $nama,
                    'target_kinerja' => $targetKinerjaAngka !== '' ? $targetKinerjaAngka : null,
                    'satuan' => $satuan !== '' ? $satuan : null,
                    'pagu_anggaran' => $pagu,
                    'updated_at' => date('Y-m-d H:i:s'),
                ];

                if (!$sub) {
                    $subData['created_at'] = date('Y-m-d H:i:s');
                    $this->db->table('sub_kegiatan')->insert($subData);
                    $subId = (int)$this->db->insertID();
                    $importedSub++;
                } else {
                    $subId = (int)$sub['id'];
                    $this->db->table('sub_kegiatan')->where('id', $subId)->update($subData);
                }

                $rk = trim((string)$sheet->getCell($colKinerja . $r)->getFormattedValue());
                $ra = $this->parseCurrency((string)$sheet->getCell($colAnggaran . $r)->getFormattedValue());

                if ($rk === '' || $rk === '-' || $rk === '.') {
                    $rk = null;
                }

                $existing = $this->db->table('realisasi')
                    ->where('sub_kegiatan_id', $subId)
                    ->where('opd_id', $opdId)
                    ->where('tahun', $tahun)
                    ->where('triwulan', $triwulan)
                    ->get()->getRowArray();

                $realisasiData = [
                    'sub_kegiatan_id' => $subId,
                    'opd_id' => $opdId,
                    'tahun' => $tahun,
                    'triwulan' => $triwulan,
                    'satuan' => $satuan !== '' ? $satuan : null,
                    'target_kinerja' => $targetKinerjaAngka !== '' ? $targetKinerjaAngka : null,
                    'target_anggaran' => $pagu,
                    'realisasi_kinerja' => $rk,
                    'realisasi_anggaran' => $ra,
                    'status' => 'Diajukan',
                    'updated_at' => date('Y-m-d H:i:s'),
                ];

                if (!$existing) {
                    $realisasiData['created_at'] = date('Y-m-d H:i:s');
                    $this->db->table('realisasi')->insert($realisasiData);
                    $importedRealisasi++;
                } else {
                    $this->db->table('realisasi')->where('id', $existing['id'])->update($realisasiData);
                    $importedRealisasi++;
                }
            }

            $this->db->transCommit();
        } catch (\Throwable $e) {
            $this->db->transRollback();
            @unlink($path);
            return redirect()->to('/opd_kinerja/import_excel')->with('error', 'Gagal import: ' . $e->getMessage());
        }

        @unlink($path);

        return redirect()->to('/opd_kinerja/dashboard')->with('success', "Import selesai. Sub Kegiatan baru: $importedSub. Laporan diproses: $importedRealisasi. Skip: $skipped.");
    }

    private function buildKode(array $segments): string
    {
        $parts = [];
        foreach ($segments as $seg) {
            $s = trim((string)$seg);
            if ($s === '') {
                continue;
            }
            $parts[] = $s;
        }
        return implode('.', $parts);
    }

    private function parseCurrency(string $value): float
    {
        $v = trim($value);
        if ($v === '' || $v === '-' || $v === '.') {
            return 0.0;
        }
        $v = str_replace(['Rp', 'rp', ' '], '', $v);
        $negative = false;
        if (str_contains($v, '(') && str_contains($v, ')')) {
            $negative = true;
            $v = str_replace(['(', ')'], '', $v);
        }
        // Scientific notation like "1,75E+09"
        $vn = str_replace([' ', 'Rp', 'rp'], '', $value);
        $vn = trim($vn);
        if (preg_match('/^[0-9]+(?:[\\.,][0-9]+)?[eE][+-]?[0-9]+$/', $vn)) {
            $vn = str_replace('.', '', $vn);
            $vn = str_replace(',', '.', $vn);
            $num = (float)$vn;
            return $negative ? -1 * $num : $num;
        }
        $v = preg_replace('/[^0-9,\\.-]/', '', $v) ?? '0';

        $lastComma = strrpos($v, ',');
        $lastDot = strrpos($v, '.');
        if ($lastComma !== false && $lastDot !== false) {
            if ($lastComma > $lastDot) {
                $v = str_replace('.', '', $v);
                $v = str_replace(',', '.', $v);
            } else {
                $v = str_replace(',', '', $v);
            }
        } elseif ($lastComma !== false && $lastDot === false) {
            $v = str_replace('.', '', $v);
            $v = str_replace(',', '.', $v);
        } else {
            $v = str_replace(',', '', $v);
        }

        $num = (float)$v;
        return $negative ? -1 * $num : $num;
    }
}
