<?php

namespace App\Controllers;

use CodeIgniter\Controller;
use App\Models\MenuModel;

class Library extends Controller
{
    /**
     * Show the Library (Database Explorer) page, only if the user is logged in.
     */
    public function index()
    {
        // 1. Ensure user is logged in
        if (! service('auth')->loggedIn()) {
            return redirect()->to('/login');
        }

        // 2. Navbar menu items
        $menuModel = new MenuModel();
        $menuItems = $menuModel->orderBy('order', 'ASC')->findAll();

        // 3. Connect DB
        $db = db_connect();

        // 4. All table names
        $tables = $db->listTables();

        // 5. Check which table is selected
        $tableName = $this->request->getVar('table_name');

        // 6. Pagination & search variables
        $limit       = $this->request->getVar('limit') ?? '500';
        $currentPage = (int) ($this->request->getVar('page') ?? 1);
        $searchTerm  = $this->request->getVar('search');
        $searchField = $this->request->getVar('search_field') ?? 'ALL';

        // Prepare placeholders
        $fieldNames = [];
        $results    = [];
        $totalRows  = 0;
        $totalPages = 1;

        if ($tableName) {
            $escapedTable = $db->escapeIdentifier($tableName);

            // Get column names
            $fieldNames = $db->getFieldNames($db->escapeString($tableName));

            // Build WHERE for search
            $whereClause = '';
            if (! empty($searchTerm)) {
                $conditions = [];
                $safeTerm   = $db->escapeLikeString($searchTerm);

                if ($searchField !== 'ALL') {
                    $escCol = $db->escapeIdentifier($searchField);
                    $conditions[] = "{$escCol} LIKE '%{$safeTerm}%'";
                } else {
                    foreach ($fieldNames as $col) {
                        $escCol = $db->escapeIdentifier($col);
                        $conditions[] = "{$escCol} LIKE '%{$safeTerm}%'";
                    }
                }

                if (! empty($conditions)) {
                    $whereClause = 'WHERE ' . implode(' OR ', $conditions);
                }
            }

            // Count rows
            $countSql   = "SELECT COUNT(*) AS cnt FROM {$escapedTable} {$whereClause}";
            $countQuery = $db->query($countSql);
            $countRow   = $countQuery->getRow();
            $totalRows  = (int) $countRow->cnt;

            // Pagination
            if ($limit !== 'ALL') {
                $limit = (int) $limit;
                if ($limit <= 0) {
                    $limit = 500;
                }

                $totalPages = max(1, ceil($totalRows / $limit));
                if ($currentPage < 1) {
                    $currentPage = 1;
                } elseif ($currentPage > $totalPages) {
                    $currentPage = $totalPages;
                }

                $offset = ($currentPage - 1) * $limit;
            }

            // Fetch rows
            if ($limit !== 'ALL') {
                $sql = "SELECT * FROM {$escapedTable} {$whereClause} LIMIT {$offset}, {$limit}";
            } else {
                $sql = "SELECT * FROM {$escapedTable} {$whereClause}";
            }

            $query   = $db->query($sql);
            $results = $query->getResultArray();
        }

        // Pass data to view
        return view('library/index', [
            'menuItems'   => $menuItems,
            'tables'      => $tables,
            'tableName'   => $tableName,
            'fieldNames'  => $fieldNames,
            'results'     => $results,
            'limit'       => ($limit === 'ALL' ? 'ALL' : (string) $limit),
            'totalRows'   => $totalRows,
            'totalPages'  => $totalPages,
            'currentPage' => $currentPage,
            'searchTerm'  => $searchTerm,
            'searchField' => $searchField,
        ]);
    }

    /**
     * Insert a new row into the selected table.
     * Must have at least one column to insert.
     */
    public function addRow()
    {
        $table = $this->request->getPost('table');
        if (empty($table)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'No table selected.',
            ]);
        }

        $db           = db_connect();
        $escapedTable = $db->escapeIdentifier($table);

        try {
            $builder = $db->table($escapedTable);

            /**
             * Insert at least one column to avoid the
             * "You must use the set method to insert an entry" error.
             *
             * For example, let's insert a "created_at" column with the current timestamp.
             * If your table has different columns or constraints, update this as needed.
             *
             * If your table truly allows all columns to be NULL, you can still do
             * $builder->insert(['any_column' => null]), but it must be at least one key.
             */
            $builder->insert([
                'created_at' => date('Y-m-d H:i:s'), 
            ]);

            // Get the ID of the newly inserted row (works if there's an AUTO_INCREMENT PK)
            $insertId = $db->insertID();

            return $this->response->setJSON([
                'success'   => true,
                'message'   => 'Row added successfully.',
                'insert_id' => $insertId,
            ]);
        } catch (\Exception $e) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Exception: ' . $e->getMessage(),
            ]);
        }
    }

    /**
     * Update a single field in the selected table.
     */
    public function updateField()
    {
        $db      = db_connect();
        $table   = $this->request->getPost('table');
        $id      = $this->request->getPost('id');
        $field   = $this->request->getPost('field');
        $value   = $this->request->getPost('value');

        if (empty($table) || empty($id) || empty($field)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Missing table, id, or field.',
            ]);
        }

        $escapedTable = $db->escapeIdentifier($table);
        $escapedField = $db->escapeIdentifier($field);

        try {
            $builder = $db->table($escapedTable);
            $builder->where('id', $id);
            $builder->update([$field => $value]);

            if ($db->affectedRows() >= 0) {
                return $this->response->setJSON([
                    'success' => true,
                    'message' => 'Field updated successfully.',
                ]);
            } else {
                return $this->response->setJSON([
                    'success' => false,
                    'message' => 'No rows updated.',
                ]);
            }
        } catch (\Exception $e) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Exception: ' . $e->getMessage(),
            ]);
        }
    }
}
