<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ProgramController extends BaseController
{
    private $db;

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

    public function index()
    {
        $role = session()->get('role');
        if ($role === 'admin_bidang') {
            return redirect()->to('/admin_bidang/renja');
        }
        $opdId = $this->request->getGet('opd_id');
        $opdList = $this->db->table('opd')->orderBy('nama_opd', 'ASC')->get()->getResultArray();
        $progBuilder = $this->db->table('programs')
            ->select('programs.id, programs.kode_program, programs.nama_program, opd.nama_opd')
            ->join('opd', 'opd.id = programs.opd_id')
            ->orderBy('opd.nama_opd, programs.nama_program', 'ASC');
        if ($opdId) {
            $progBuilder->where('programs.opd_id', (int)$opdId);
        }
        $programRows = $progBuilder->get()->getResultArray();
        $programs = [];
        foreach ($programRows as $p) {
            $kegiatanRows = $this->db->table('kegiatan')
                ->select('id, kode_kegiatan, nama_kegiatan')
                ->where('program_id', (int)$p['id'])
                ->orderBy('nama_kegiatan', 'ASC')
                ->get()->getResultArray();
            $kegiatan = [];
            foreach ($kegiatanRows as $k) {
                $subRows = $this->db->table('sub_kegiatan')
                    ->select('kode_sub_kegiatan, nama_sub_kegiatan, target_kinerja, satuan, pagu_anggaran')
                    ->where('kegiatan_id', (int)$k['id'])
                    ->orderBy('nama_sub_kegiatan', 'ASC')
                    ->get()->getResultArray();
                $kegiatan[] = [
                    'kode_kegiatan' => $k['kode_kegiatan'],
                    'nama_kegiatan' => $k['nama_kegiatan'],
                    'sub_kegiatan' => $subRows,
                ];
            }
            $programs[] = [
                'kode_program' => $p['kode_program'],
                'nama_program' => $p['nama_program'],
                'nama_opd' => $p['nama_opd'],
                'kegiatan' => $kegiatan,
            ];
        }
        $data = [
            'title' => 'Program & Kegiatan',
            'programs' => $programs,
            'opd_list' => $opdList,
            'selected_opd' => $opdId ? (int)$opdId : null,
        ];
        return view('superadmin/programs/index', $data);
    }

    public function import()
    {
        if (!in_array(session()->get('role'), ['superadmin', 'admin_bidang'], true)) {
            return redirect()->to('/dashboard')->with('error', 'Hanya Super Admin/Admin Bidang yang dapat mengimpor RENJA.');
        }
        $data = [
            'title' => 'Import Program & Kegiatan',
        ];
        return view('superadmin/programs/import', $data);
    }

    public function downloadTemplate()
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $headers = [
            'Bidang',
            'OPD',
            'Kode Program',
            'Nama Program',
            'Kode Kegiatan',
            'Nama Kegiatan',
            'Kode Sub Kegiatan',
            'Nama Sub Kegiatan',
            'Target Kinerja',
            'Satuan',
            'Pagu Anggaran',
        ];

        $sheet->fromArray($headers, null, 'A1');
        $sheet->getStyle('A1:K1')->getFont()->setBold(true);

        $example = [
            'Ketentraman',
            'Satpol PP',
            '1.01.01',
            'Penegakan Perda',
            '1.01.01.2.01',
            'Operasi Yustisi',
            '1.01.01.2.01.01',
            'Razia Tempat Usaha',
            '10 kegiatan',
            'Kegiatan',
            '150000000',
        ];
        $sheet->fromArray($example, null, 'A2');

        foreach (range('A', 'K') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $writer = new Xlsx($spreadsheet);
        $tempPath = WRITEPATH . 'uploads/template_import_program.xlsx';
        $writer->save($tempPath);

        return $this->response
            ->download($tempPath, null)
            ->setFileName('template_import_program.xlsx');
    }

    public function processImport()
    {
        $session = session();
        $role = $session->get('role');
        $sessionBidangId = $session->get('bidang_id');
        if (!in_array($role, ['superadmin', 'admin_bidang'], true)) {
            return redirect()->to('/dashboard')->with('error', 'Hanya Super Admin/Admin Bidang yang dapat mengimpor RENJA.');
        }
        $file = $this->request->getFile('file_excel');
        if (!$file || !$file->isValid()) {
            return redirect()->to('/program/import')->with('error', 'File Excel tidak valid.');
        }

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

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

        $inserted = 0;
        $updated = 0;
        $skipped = 0;
        $errors = 0;

        $this->db->transBegin();
        try {
            $spreadsheet = IOFactory::load($path);
            $sheet = $spreadsheet->getActiveSheet();
            $rows = $sheet->toArray(null, true, true, true);

            $normalize = static function (string $s): string {
                $s = trim($s);
                $s = preg_replace('/\s+/', ' ', $s) ?? $s;
                return mb_strtolower($s);
            };
            // Build caches to avoid duplicate OPD/Bidang due to case/space differences
            $bidangCache = [];
            foreach ($this->db->table('bidang')->get()->getResultArray() as $b) {
                $bidangCache[$normalize((string)$b['nama_bidang'])] = (int)$b['id'];
            }
            $opdCache = []; // key: normalized_name|bidangId
            foreach ($this->db->table('opd')->get()->getResultArray() as $o) {
                $key = $normalize((string)$o['nama_opd']) . '|' . (string)($o['bidang_id'] ?? '');
                $opdCache[$key] = (int)$o['id'];
            }

            foreach ($rows as $rowIndex => $row) {
                if ($rowIndex === 1) {
                    continue;
                }

                $bidangName = trim((string)($row['A'] ?? ''));
                $opdName = trim((string)($row['B'] ?? ''));
                $kodeProgram = trim((string)($row['C'] ?? ''));
                $namaProgram = trim((string)($row['D'] ?? ''));
                $kodeKegiatan = trim((string)($row['E'] ?? ''));
                $namaKegiatan = trim((string)($row['F'] ?? ''));
                $kodeSub = trim((string)($row['G'] ?? ''));
                $namaSub = trim((string)($row['H'] ?? ''));
                $target = trim((string)($row['I'] ?? ''));
                $satuan = trim((string)($row['J'] ?? ''));
                $pagu = $this->parseCurrency((string)($row['K'] ?? ''));

                if ($opdName === '' || $namaSub === '') {
                    $skipped++;
                    continue;
                }

                $bidangId = null;
                if ($bidangName !== '') {
                    $keyB = $normalize($bidangName);
                    if (isset($bidangCache[$keyB])) {
                        $bidangId = $bidangCache[$keyB];
                    } else {
                        $this->db->table('bidang')->insert([
                            'nama_bidang' => $bidangName,
                            'created_at' => date('Y-m-d H:i:s'),
                        ]);
                        $bidangId = (int)$this->db->insertID();
                        $bidangCache[$keyB] = $bidangId;
                    }
                }
                // Jika admin_bidang yang mengimpor, paksa data masuk ke bidang akun ini
                if ($role === 'admin_bidang' && $sessionBidangId) {
                    $bidangId = (int) $sessionBidangId;
                }

                $keyO = $normalize($opdName) . '|' . (string)($bidangId ?? '');
                if (isset($opdCache[$keyO])) {
                    $opdId = $opdCache[$keyO];
                } else {
                    // Try any OPD with same normalized name regardless of bidang when bidang missing
                    $fallbackKey = $normalize($opdName) . '|';
                    $found = null;
                    foreach ($opdCache as $k => $val) {
                        if (str_starts_with($k, $fallbackKey)) {
                            $found = $val;
                            break;
                        }
                    }
                    if ($found !== null && $bidangId === null) {
                        $opdId = $found;
                    } else {
                        $this->db->table('opd')->insert([
                            'nama_opd' => $opdName,
                            'bidang_id' => $bidangId,
                            'created_at' => date('Y-m-d H:i:s'),
                        ]);
                        $opdId = (int)$this->db->insertID();
                        $opdCache[$keyO] = $opdId;
                    }
                }

                $programQuery = $this->db->table('programs')->where('opd_id', $opdId);
                if ($kodeProgram !== '') {
                    $programQuery->where('kode_program', $kodeProgram);
                } else {
                    $programQuery->where('nama_program', $namaProgram);
                }
                $program = $programQuery->get()->getRowArray();

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

                $kegiatanQuery = $this->db->table('kegiatan')->where('program_id', $programId);
                if ($kodeKegiatan !== '') {
                    $kegiatanQuery->where('kode_kegiatan', $kodeKegiatan);
                } else {
                    $kegiatanQuery->where('nama_kegiatan', $namaKegiatan);
                }
                $kegiatan = $kegiatanQuery->get()->getRowArray();

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

                $subQuery = $this->db->table('sub_kegiatan')->where('kegiatan_id', $kegiatanId);
                if ($kodeSub !== '') {
                    $subQuery->where('kode_sub_kegiatan', $kodeSub);
                } else {
                    $subQuery->where('nama_sub_kegiatan', $namaSub);
                }
                $sub = $subQuery->get()->getRowArray();

                $subData = [
                    'kegiatan_id' => $kegiatanId,
                    'kode_sub_kegiatan' => $kodeSub !== '' ? $kodeSub : null,
                    'nama_sub_kegiatan' => $namaSub,
                    'target_kinerja' => $target !== '' ? $target : 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);
                    $inserted++;
                } else {
                    $this->db->table('sub_kegiatan')->where('id', $sub['id'])->update($subData);
                    $updated++;
                }
            }

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

        @unlink($path);

        $message = "Import selesai. Tambah: $inserted, Update: $updated, Skip: $skipped.";
        if ($errors > 0) {
            $message .= " Error: $errors.";
        }
        if ($role === 'admin_bidang') {
            return redirect()->to('/admin_bidang/renja')->with('success', $message);
        }
        return redirect()->to('/program/import')->with('success', $message);
    }

    private function parseCurrency(string $value): float
    {
        $v = trim($value);
        if ($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);
        }
        // Handle scientific notation like "1,75E+09" or "1.75e9"
        $vn = str_replace([' ', 'Rp', 'rp'], '', $value);
        $vn = trim($vn);
        if (preg_match('/^[0-9]+(?:[\\.,][0-9]+)?[eE][+-]?[0-9]+$/', $vn)) {
            $vn = str_replace('.', '', $vn); // remove thousand separators if any
            $vn = str_replace(',', '.', $vn); // normalize decimal
            $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;
    }
}
