<?php

namespace App\Controllers;

use CodeIgniter\Controller;
use App\Models\GenericModel;
use Config\Database;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class CrudController extends Controller
{
    protected $helpers = ['url', 'form', 'filesystem'];
    protected $db;

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

    // =============================================================
    // GENERIC MODEL
    // =============================================================
    protected function newModel(string $table): GenericModel
    {
        $model = new GenericModel();
        $model->setTable($table);
        return $model;
    }

    // =============================================================
    // INDEX / SIDEBAR / ACTIVE VIEW
    // =============================================================
    public function index($table = null)
    {
        $session = session();
        $model   = new GenericModel();
        $db      = $this->db;

        $userRole  = $session->get('user_role');
        $lastTable = $session->get('lastSidebarTable');

        if (!$table && $lastTable) {
            $table = $lastTable;
        }

        // Ambil tabel yang diizinkan untuk role ini
        $tablesQuery = $db->table('table_settings')
            ->select('table_name, icon')
            ->where('role', $userRole)
            ->where('menu', 'aktif')
            ->get()
            ->getResultArray();

        $tables = [];
        $icons  = [];
        foreach ($tablesQuery as $row) {
            $tables[] = $row['table_name'];
            $icons[strtolower($row['table_name'])] = $row['icon'];
        }

        // Ambil menu dan kategori
        $menuQuery = $db->table('menu')
            ->select('table_name, kategori, nomor, menu_order')
            ->whereIn('table_name', $tables)
            ->orderBy('nomor', 'ASC')
            ->orderBy('menu_order', 'ASC')
            ->get()
            ->getResultArray();

        $groupedTables = [];
        foreach ($menuQuery as $row) {
            $kategori = $row['kategori'] ?: 'Tanpa Kategori';
            $groupedTables[$kategori][] = $row['table_name'];
        }

        $activeTable  = $session->get('activeViewTable');
        $isActiveView = !empty($activeTable);

        $data = [
            'tables'          => $tables,
            'icons'           => $icons,
            'groupedTables'   => $groupedTables,
            'table'           => $table ?? null,
            'fields'          => $table ? $model->getTableFields($table) : [],
            'meta'            => $table ? $model->getTableMeta($table) : [],
            'user'            => $session->get(),
            'isActiveView'    => $isActiveView,
            'activeViewTable' => $activeTable,
        ];

        if (empty($tables)) {
            $data['noAccess'] = 'Tidak ada tabel yang diizinkan untuk role ini.';
        }

        return view('crud/content', $data);
    }

    // =============================================================
    // LOAD TABLE CONTENT
    // =============================================================
    public function loadTableContent(string $table)
    {
        $session     = session();
        $activeTable = $session->get('activeViewTable');

        return !empty($activeTable)
            ? $this->loadActiveView($table)
            : $this->loadNormal($table);
    }

    // =============================================================
    // ACTIVE VIEW
    // =============================================================
    protected function loadActiveView(string $table)
    {
        $session = session();
        $db      = $this->db;
        $model   = new GenericModel();

        $userRole        = $session->get('user_role') ?? $session->get('role');
        $sessionTable    = $session->get('activeViewTable');
        $lastSidebarTable = $session->get('lastSidebarTable');

        if (!$sessionTable) {
            return 'Tabel aktif tidak ditemukan di session.';
        }

        $fields = $model->getTableFields($sessionTable);
        $meta   = $model->getTableMeta($sessionTable);

        $rows = $db->tableExists($sessionTable)
            ? $db->table($sessionTable)->where('table_name', $lastSidebarTable)->get()->getResultArray()
            : [];

        $buttons      = $this->getButtonSettings($sessionTable, $userRole);
        $visibleFields = $this->getVisibleFields($sessionTable, $fields, $userRole);
        $toggleFields  = $this->getToggleFields($sessionTable, $userRole);
        $icon          = $this->getTableIcon($sessionTable, $userRole);
        $info          = $this->getInfoTabel($sessionTable, $userRole);

        return view('crud/activeView', [
            'table'           => $lastSidebarTable,
            'activeViewTable' => $sessionTable,
            'fields'          => $visibleFields,
            'meta'            => $meta,
            'rows'            => $rows,
            'buttons'         => $buttons,
            'toggleFields'    => $toggleFields,
            'icon'            => $icon,
            'infoTabel'       => $info['infoTabel'],
            'infoUser'        => $info['infoUser'],
            'isActiveView'    => true,
            'sessionTable'    => $sessionTable,
            'keysortir'       => $sessionTable,
        ]);
    }

    // =============================================================
    // NORMAL VIEW
    // =============================================================
    protected function loadNormal(string $table)
    {
        $session = session();
        $db      = $this->db;
        $model   = new GenericModel();

        $userRole = $session->get('user_role') ?? $session->get('role') ?? null;

        $fields = $model->getTableFields($table);
        $meta   = $model->getTableMeta($table);
        $rows   = $model->getTableData($table);

        $buttons       = $this->getButtonSettings($table, $userRole);
        $visibleFields = $this->getVisibleFields($table, $fields, $userRole);
        $toggleFields  = $this->getToggleFields($table, $userRole);
        $icon          = $this->getTableIcon($table, $userRole);
        $info          = $this->getInfoTabel($table, $userRole);

        return view('crud/table_content', [
            'table'           => $table,
            'fields'          => $visibleFields,
            'meta'            => $meta,
            'rows'            => $rows,
            'buttons'         => $buttons,
            'toggleFields'    => $toggleFields,
            'icon'            => $icon,
            'infoTabel'       => $info['infoTabel'],
            'infoUser'        => $info['infoUser'],
            'isActiveView'    => false,
            'activeViewTable' => null,
        ]);
    }

    // =============================================================
    // SAVE DATA (Tambah/Edit)
    // =============================================================
    public function save(string $table)
    {
        $session = session();
        $table   = $table ?? $this->request->getGet('table');
        $model   = new GenericModel();
        $data    = $this->request->getPost();

        $activeViewTable = $session->get('activeViewTable');
        $isActiveView    = !empty($activeViewTable);
        $targetTable     = $isActiveView ? $activeViewTable : $table;

        $model->setTable($targetTable);
        $meta = $model->getTableMeta($targetTable);

        $primaryKey = $this->getPrimaryKey($meta);
        $cleanData  = [];

        foreach ($meta as $col) {
            $colName = $col['name'];
            if (array_key_exists($colName, $data)) {
                $cleanData[$colName] = $this->castValue($data[$colName], $col);
            }
        }

        if (!$primaryKey) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Primary key tidak ditemukan.']);
        }

        $builder = $model->db->table($targetTable);
        $lastId  = null;

        if (!empty($data[$primaryKey])) {
            $id = $data[$primaryKey];
            unset($cleanData[$primaryKey]);
            $builder->where($primaryKey, $id)->update($cleanData);
            $lastId = $id;
        } else {
            unset($cleanData[$primaryKey]);
            $builder->insert($cleanData);
            $lastId = $model->db->insertID();
        }

        $modeLabel = $isActiveView ? "(Active View: $activeViewTable)" : "(Normal)";
        return $this->response->setJSON([
            'status'   => 'success',
            'message'  => "Data berhasil disimpan {$modeLabel}",
            'last_id'  => $lastId,
            'saved_to' => $targetTable
        ]);
    }

    // =============================================================
    // GET DATA
    // =============================================================
    public function get(string $table, $id)
    {
        $model      = new GenericModel();
        $meta       = $model->getTableMeta($table);
        $primaryKey = $this->getPrimaryKey($meta);

        if (!$primaryKey) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Primary key tidak ditemukan.']);
        }

        $row = $model->db->table($table)->where($primaryKey, $id)->get()->getRowArray();
        return $this->response->setJSON(
            $row
                ? ['status' => 'success', 'data' => $row]
                : ['status' => 'error', 'message' => 'Data tidak ditemukan.']
        );
    }

    // =============================================================
    // DELETE & DELETE ALL
    // =============================================================
    public function delete(string $table, int $id)
    {
        $model      = new GenericModel();
        $meta       = $model->getTableMeta($table);
        $primaryKey = $this->getPrimaryKey($meta);

        if (!$primaryKey) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Primary key tidak ditemukan.']);
        }

        $builder = $model->db->table($table);
        $builder->where($primaryKey, $id)->delete();

        return $this->response->setJSON(
            $model->db->affectedRows() > 0
                ? ['status' => 'success', 'message' => 'Data berhasil dihapus.']
                : ['status' => 'error', 'message' => 'Data gagal dihapus.']
        );
    }

    public function deleteAll(string $table)
    {
        $db              = $this->db;
        $protectedTables = ['user'];

        if (in_array($table, $protectedTables)) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Tabel ini dilindungi.']);
        }

        if (!$db->tableExists($table)) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Tabel tidak ditemukan.']);
        }

        try {
            $db->table($table)->truncate();
            return $this->response->setJSON(['status' => 'success', 'message' => "Semua data di tabel '{$table}' berhasil dihapus."]);
        } catch (\Throwable $e) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Gagal hapus: ' . $e->getMessage()]);
        }
    }

    // =============================================================
    // STRUCTURE
    // =============================================================
    public function structure(string $table)
    {
        $model = new GenericModel();
        $meta  = $model->getTableMeta($table);

        return $this->response->setJSON(['status' => 'success', 'meta' => $meta]);
    }

    // =============================================================
    // UPDATE TOGGLE
    // =============================================================
    public function updateToggle()
    {
        $request = service('request');
        $table   = $request->getPost('table');
        $column  = $request->getPost('column');
        $id      = $request->getPost('id');
        $status  = $request->getPost('status');

        if (!$table || !$column || !$id) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Data tidak lengkap.']);
        }

        $db      = $this->db;
        $builder = $db->table($table);

        if (preg_match('/([0-9]+)$/', $id, $matches)) {
            $id = $matches[1];
        }

        $fields     = $db->getFieldData($table);
        $primaryKey = 'id';
        foreach ($fields as $f) {
            if (!empty($f->primary_key)) {
                $primaryKey = $f->name;
                break;
            }
        }

        $exists = $builder->where($primaryKey, $id)->countAllResults(false);
        if ($exists == 0) {
            return $this->response->setJSON(['status' => 'error', 'message' => "Data tidak ditemukan."]);
        }

        $builder->where($primaryKey, $id)->update([$column => $status]);

        if ($db->affectedRows() > 0) {
            return $this->response->setJSON(['status' => 'success', 'message' => "Status '{$column}' berhasil diubah menjadi '{$status}'."]);
        }

        return $this->response->setJSON(['status' => 'warning', 'message' => "Tidak ada perubahan, status sudah '{$status}'."]);
    }

    // =============================================================
    // ACTIVE VIEW SESSION
    // =============================================================
    public function activeView(string $table)
    {
        $session = session();
        $key     = 'activeViewTable';
        $current = $session->get($key);

        if ($current === $table) {
            $session->remove($key);
            return $this->response->setJSON(['success' => true, 'active' => false, 'message' => "Active view untuk '$table' dinonaktifkan."]);
        }

        $session->set($key, $table);
        $session->set('isActiveViewMode', true);

        return $this->response->setJSON(['success' => true, 'active' => true, 'message' => "Active view diatur ke tabel '$table'."]);
    }

    public function clearActiveView()
    {
        $session = session();
        $session->remove('activeViewTable');
        return $this->response->setJSON(['success' => true, 'message' => 'Session activeViewTable telah dihapus.']);
    }

    public function saveLastTable()
    {
        $session = session();
        $table   = $this->request->getPost('table');

        if ($table) {
            $session->set('lastSidebarTable', $table);
            return $this->response->setJSON(['success' => true]);
        }

        return $this->response->setJSON(['success' => false, 'message' => 'Table kosong']);
    }

    public function setActiveViewSession()
    {
        $session = session();
        $table   = $this->request->getPost('table');

        if (!empty($table)) {
            $session->set('activeViewTable', $table);
            return $this->response->setJSON(['success' => true, 'message' => "ActiveView diset ke {$table}"]);
        }

        $session->remove('activeViewTable');
        return $this->response->setJSON(['success' => true, 'message' => 'ActiveView dihapus']);
    }

    // =============================================================
    // GET LAST TABLE
    // =============================================================
    public function getLastTable()
    {
        $session = session();
        $table   = $session->get('lastSidebarTable');

        if ($table) {
            return $this->response->setJSON(['success' => true, 'table' => $table]);
        }

        return $this->response->setJSON(['success' => false, 'table' => null]);
    }

    // =============================================================
    // EXPORT & IMPORT
    // =============================================================
    public function export(string $table)
    {
        $model  = new GenericModel();
        $fields = $model->getTableFields($table);
        $rows   = $model->getTableData($table);

        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();

        foreach ($fields as $col => $field) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col + 1) . '1', ucfirst($field));
        }

        foreach ($rows as $rIndex => $row) {
            foreach ($fields as $cIndex => $field) {
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($cIndex + 1) . ($rIndex + 2), $row[$field] ?? '');
            }
        }

        $filename = $table . '_export_' . date('Ymd_His') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }

    public function import(string $table)
    {
        $file = $this->request->getFile('file');
        if (!$file->isValid()) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'File tidak valid.']);
        }

        $ext = $file->getClientExtension();
        if (!in_array($ext, ['xls', 'xlsx'])) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Format file harus .xls atau .xlsx']);
        }

        $model  = new GenericModel();
        $fields = $model->getTableFields($table);
        $meta   = $model->getTableMeta($table);
        $primaryKey = $this->getPrimaryKey($meta);

        $spreadsheet = IOFactory::load($file->getTempName());
        $sheetData   = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        array_shift($sheetData);

        $db          = $this->db;
        $builder     = $db->table($table);
        $inserted    = $updated = 0;
        $insertedIds = [];

        foreach ($sheetData as $row) {
            $data = [];
            foreach ($fields as $i => $f) {
                $data[$f] = $row[chr(65 + $i)] ?? null;
            }

            if ($primaryKey) {
                $id = $data[$primaryKey] ?? null;
                if ($id && $builder->where($primaryKey, $id)->countAllResults() > 0) {
                    $builder->where($primaryKey, $id)->update($data);
                    $updated++;
                } else {
                    unset($data[$primaryKey]);
                    $builder->insert($data);
                    $inserted++;
                    $insertedIds[] = $db->insertID();
                }
            } else {
                $builder->insert($data);
                $inserted++;
                $insertedIds[] = $db->insertID();
            }
        }

        return $this->response->setJSON([
            'status'       => 'success',
            'message'      => "Import selesai. $inserted data baru ditambahkan, $updated data diperbarui.",
            'inserted_ids' => $insertedIds,
            'inserted'     => $inserted,
            'updated'      => $updated,
        ]);
    }

    // =============================================================
    // UTILITY INTERNAL
    // =============================================================
    private function getPrimaryKey(array $meta): ?string
    {
        foreach ($meta as $col) {
            if ($col['key'] === 'PRI') {
                return $col['name'];
            }
        }
        return null;
    }

    private function castValue($value, array $col)
    {
        if (in_array($col['type'], ['int', 'tinyint', 'bigint'])) {
            return ($value === '' ? null : (int)$value);
        } elseif (in_array($col['type'], ['decimal', 'float', 'double'])) {
            return ($value === '' ? null : (float)$value);
        } elseif ($col['type'] === 'date' && $value === '') {
            return null;
        } elseif (in_array($col['type'], ['enum', 'set'])) {
            $allowed = $this->extractEnumValues($col['type_raw']);
            return in_array($value, $allowed) ? $value : null;
        }
        return $value;
    }

    private function extractEnumValues(string $typeRaw): array
    {
        preg_match_all("/'([^']+)'/", $typeRaw, $matches);
        return $matches[1] ?? [];
    }

    private function getButtonSettings(string $table, ?string $role): array
    {
        $db      = $this->db;
        $buttons = array_fill_keys(['tambah', 'print', 'export', 'import', 'view', 'edit', 'delete', 'generate', 'delete_all', 'activeView'], 'nonaktif');

        if ($role) {
            $query = $db->table('table_settings')
                ->select(implode(',', array_keys($buttons)))
                ->where('role', $role)
                ->where('table_name', $table)
                ->get()
                ->getRowArray();

            foreach ($buttons as $k => $v) {
                if (isset($query[$k]) && $query[$k] === 'aktif') {
                    $buttons[$k] = 'aktif';
                }
            }
        }

        return $buttons;
    }

    private function getVisibleFields(string $table, array $fields, ?string $role): array
    {
        $db = $this->db;
        if ($role) {
            $visibleFields = array_column($db->table('field_settings')
                ->select('field_name')
                ->where('table_name', $table)
                ->where('role', $role)
                ->where('visible', 'aktif')
                ->get()
                ->getResultArray(), 'field_name');

            return !empty($visibleFields) ? $visibleFields : $fields;
        }
        return $fields;
    }

    private function getToggleFields(string $table, ?string $role): array
    {
        $db = $this->db;
        if ($role) {
            return array_column($db->table('toggle_fields')
                ->select('field_name')
                ->where('table_name', $table)
                ->where('role', $role)
                ->where('toggle_on', 'aktif')
                ->get()
                ->getResultArray(), 'field_name');
        }
        return [];
    }

    private function getTableIcon(string $table, ?string $role): string
    {
        $db = $this->db;
        $icon = 'fa-database';
        if ($role) {
            $row = $db->table('table_settings')
                ->select('icon')
                ->where('role', $role)
                ->where('table_name', $table)
                ->get()
                ->getRowArray();
            if (!empty($row['icon'])) $icon = $row['icon'];
        }
        return $icon;
    }

    private function getInfoTabel(string $table, ?string $role): array
    {
        $db = $this->db;
        $infoTabel = $infoUser = '';
        if ($role) {
            $row = $db->table('infotabel')
                ->select('info_tabel, info_user')
                ->where('nama_tabel', $table)
                ->where('role', $role)
                ->get()
                ->getRowArray();
            $infoTabel = $row['info_tabel'] ?? '';
            $infoUser  = $row['info_user'] ?? '';
        }
        return ['infoTabel' => $infoTabel, 'infoUser' => $infoUser];
    }
}
