alrahma_sunday_school/app/Controllers/View/InventoryController.php

<?php

namespace App\Controllers\View;

use App\Controllers\BaseController;
use App\Models\ClassSectionModel;
use App\Models\InventoryItemModel;
use App\Models\InventoryCategoryModel;
use App\Models\ConfigurationModel;
use App\Models\TeacherClassModel;
use App\Models\StudentClassModel;
use App\Models\UserModel;
use App\Models\InventoryMovementModel;
use App\Models\TeacherModel;

class InventoryController extends BaseController
{
    protected $itemModel;
    protected $teacherModel;
    protected $catModel;
    protected $configModel;
    protected $schoolYear;
    protected $semester;
    protected $movModel;
    protected $teacherClassModel;
    protected $studentClassModel;
    protected $userModel;
    protected $db;
    protected $classSectionModel;
    private array $types = ['classroom', 'book', 'office', 'kitchen'];
    protected array $movementTypes = ['initial', 'in', 'out', 'adjust', 'distribution'];
    protected array $semesters     = ['Spring', 'Fall'];


    public function __construct()
    {
        // Init models 
        $this->movModel = new InventoryMovementModel();
        $this->userModel = new UserModel();
        $this->configModel = new ConfigurationModel();
        $this->itemModel = new InventoryItemModel();
        $this->catModel  = new InventoryCategoryModel();
        $this->teacherClassModel = new TeacherClassModel();
        $this->studentClassModel = new StudentClassModel();
        $this->schoolYear = $this->configModel->getConfig('school_year');
        $this->semester = $this->configModel->getConfig('semester');
        $this->classSectionModel = new ClassSectionModel();
        $this->db = \Config\Database::connect();
        $this->teacherModel = new TeacherModel();
        helper(['form', 'text']);
    }

    // app/Controllers/InventoryController.php  (only the changed bits)

    private function viewForIndex(string $type): string
    {
        // inventory/classroom/index, inventory/book/index, inventory/office/index, inventory/kitchen/index
        return "inventory/{$type}/index";
    }

    private function viewForForm(string $type): string
    {
        // inventory/classroom/form, inventory/book/form, inventory/office/form, inventory/kitchen/form
        return "inventory/{$type}/form";
    }

    public function index(string $type = 'classroom')
    {
        $type = $this->normalizeType($type);

        $schoolYears = $this->getSchoolYearsForType($type);
        $requested     = trim((string) $this->request->getGet('school_year'));
        $selectedYear  = $requested !== '' ? $requested : $this->schoolYear;
        $selectedSemRaw = $this->request->getGet('semester');
        $selectedSem    = $selectedSemRaw === null ? (string) $this->semester : trim((string) $selectedSemRaw);

        $builder = $this->itemModel->where('type', $type);
        if (strtolower($selectedYear) !== 'all') {
            $builder->where('school_year', $selectedYear);
        }
        if ($selectedSem !== '') {
            $builder->where('semester', $selectedSem);
        }

        $items = $builder->orderBy('name', 'ASC')->findAll();
        $categories = $this->catModel->optionsForType($type);

        // NEW: build UpdatedBy name map
        $userIds   = array_values(array_unique(array_map(fn($i) => (int)($i['updated_by'] ?? 0), $items)));
        $userIds   = array_values(array_filter($userIds));
        $userNames = $this->userNamesByIds($userIds);

        return view($this->viewForIndex($type), [
            'type'            => $type,
            'items'           => $items,
            'categories'      => $categories,
            'conditionOptions' => ['good' => 'Good condition', 'needs_repair' => 'Needs repair', 'need_replace' => 'Need replace', 'cannot_find' => 'Cannot find'],
            'schoolYears'     => $schoolYears,
            'selectedYear'    => $selectedYear,
            'currentYear'     => $this->schoolYear,
            'semester'        => $selectedSem,
            'userNames'       => $userNames,   // <-- pass to views
        ]);
    }

    public function edit(int $id)
    {
        $item = $this->itemModel->find($id);
        if (!$item) return redirect()->back()->with('error', 'Item not found.');

        // Load categories for THIS item type
        $categories = $this->catModel->optionsForType($item['type']);

        return view($this->viewForForm($item['type']), [
            'type'        => $item['type'],
            'item'        => $item,
            'categories'  => $categories,
            'conditionOptions' => [
                'good' => 'Good condition',
                'needs_repair' => 'Needs repair',
                'need_replace' => 'Need replace',
                'cannot_find' => 'Cannot find'
            ],
            'isEdit'      => true, // <- tell the view we're editing
        ]);
    }

    public function update(int $id)
    {
        $item = $this->itemModel->find($id);
        if (!$item) return redirect()->back()->with('error', 'Item not found.');

        // lock to original type so users can’t switch books -> office, etc.
        $data = $this->filterItemData($this->request->getPost(), $item['type']);

        // Do not allow changing the PK via payload
        unset($data['id']);

        if (!$this->itemModel->update($id, $data)) {
            return redirect()->back()->withInput()->with('error', implode(', ', $this->itemModel->errors()));
        }

        return redirect()->to(site_url('inventory/' . $item['type']))->with('success', 'Item updated.');
    }

    public function store()
    {
        $data = $this->request->getPost();

        $itemData = $this->filterItemData($data);
        // Use insert() to get ID cleanly
        $itemId = $this->itemModel->insert($itemData, true);
        if ($itemId) {
            $initialQty = (int) ($itemData['quantity'] ?? 0);
            if ($initialQty !== 0) {
                $this->recordMovement($itemId, $initialQty, 'initial', 'Initial stock');
            } else {
                // ensure quantity is synced even if zero
                $this->recalcQuantity($itemId);
            }
            return redirect()->to(site_url("inventory/{$itemData['type']}"))->with('success', 'Item added.');
        }
        return redirect()->back()->withInput()->with('error', implode(', ', $this->itemModel->errors()));
    }

    // Lazily ensure the very first movement = current on-hand, for legacy items
    private function ensureInitialMovement(int $itemId): void
    {
        // Any movement already?
        $hasMov = $this->movModel->where('item_id', $itemId)->limit(1)->countAllResults();
        if ($hasMov > 0) return; // legacy seed only

        $item = $this->itemModel->select('quantity')->find($itemId);
        $initialQty = (int)($item['quantity'] ?? 0);

        $this->movModel->insert([
            'item_id'       => $itemId,
            'qty_change'    => $initialQty,
            'movement_type' => 'initial',
            'semester'      => $this->semester,
            'school_year'   => $this->schoolYear,
            'performed_by'  => $this->currentUserId(),
        ], false);
    }

    public function delete(int $id)
    {
        $item = $this->itemModel->find($id);
        if ($item) {
            $type = $item['type'];
            $this->itemModel->delete($id);
            return redirect()->to(site_url("inventory/{$type}"))->with('success', 'Item deleted.');
        }
        return redirect()->back()->with('error', 'Item not found.');
    }

    // Categories
    public function saveCategory()
    {
        // Normalize inputs
        $rawType = (string)($this->request->getPost('type') ?? '');
        $type = strtolower(trim($rawType));
        $id   = $this->request->getPost('id');

        // Collapse multiple spaces in the name and trim
        $name = preg_replace('/\s+/', ' ', trim((string)$this->request->getPost('name')));

        $data = [
            'type'        => in_array($type, ['classroom', 'book', 'office', 'kitchen'], true) ? $type : 'office',
            'name'        => $name,
            'description' => (string)$this->request->getPost('description'),
        ];

        // Only books have grade range
        if ($data['type'] === 'book') {
            $gmin = $this->request->getPost('grade_min');
            $gmax = $this->request->getPost('grade_max');

            $gmin = ($gmin === '' || $gmin === null) ? null : max(0, (int)$gmin);
            $gmax = ($gmax === '' || $gmax === null) ? null : max(0, (int)$gmax);

            if ($gmin !== null && $gmax !== null && $gmin > $gmax) {
                return redirect()->back()->withInput()->with('error', 'Grade Min cannot be greater than Grade Max.');
            }
            if ($gmin !== null && $gmin > 13) $gmin = 13;
            if ($gmax !== null && $gmax > 13) $gmax = 13;

            $data['grade_min'] = $gmin;
            $data['grade_max'] = $gmax;
        } else {
            $data['grade_min'] = null;
            $data['grade_max'] = null;
        }

        // --- Duplicate guard for (type, name) pair ---
        $existing = $this->catModel
            ->where('type', $data['type'])
            ->where('name', $data['name'])
            ->first();

        try {
            if ($id) {
                // Updating: allow if the found record is THIS record, block otherwise
                $pk = $this->catModel->primaryKey ?? 'id';
                if ($existing && (int)$existing[$pk] !== (int)$id) {
                    return redirect()->back()->withInput()->with(
                        'error',
                        'A category with this Type & Name already exists. Please choose a different name or edit the existing one.'
                    );
                }
                $ok = $this->catModel->update((int)$id, $data);
            } else {
                // Creating: block if already exists
                if ($existing) {
                    // If you PREFER auto-converting this into an update, uncomment the two lines below:
                    // $pk = $this->catModel->primaryKey ?? 'id';
                    // return $this->catModel->update((int)$existing[$pk], $data)
                    //     ? redirect()->back()->with('success', 'Category already existed; details updated.')
                    //     : redirect()->back()->withInput()->with('error', 'Failed to update existing category.');

                    return redirect()->back()->withInput()->with(
                        'error',
                        'This category already exists: ' . $data['type'] . ' — ' . $data['name'] . '.'
                    );
                }
                $ok = (bool) $this->catModel->insert($data);
            }
        } catch (\Throwable $e) {
            // Handle duplicate at DB level (safety net)
            $msg = $e->getMessage();
            if (strpos($msg, 'Duplicate entry') !== false && strpos($msg, 'type_name') !== false) {
                return redirect()->back()->withInput()->with(
                    'error',
                    'Duplicate Type/Name. A category with this combination already exists.'
                );
            }
            log_message('critical', 'Failed to save category: {msg}', ['msg' => $msg]);
            return redirect()->back()->withInput()->with('error', 'Failed to save category.');
        }

        if (!$ok) {
            return redirect()->back()->withInput()->with('error', 'Failed to save category.');
        }

        return redirect()->back()->with('success', 'Category saved.');
    }

    public function optionsForType(string $type): array
    {
        return $this->asArray()
            ->select('id, name, description, grade_min, grade_max')
            ->where('type', $type)
            ->orderBy('name', 'ASC')
            ->findAll();
    }

    private function normalizeType(?string $type): string
    {
        $type = strtolower((string)$type);
        return in_array($type, $this->types, true) ? $type : 'classroom';
    }

    private function currentUserId(): ?int
    {
        foreach (['user_id', 'id', 'uid', 'userId'] as $k) {
            $v = session()->get($k);
            if ($v !== null && $v !== '') return (int)$v;
        }
        return null;
    }


    // add a tiny helper (optional, but keeps things tidy)
    private function post(string $key, $default = null)
    {
        $v = $this->request->getPost($key);
        if ($v === '' || $v === null) return $default;
        return is_string($v) ? trim($v) : $v;
    }

    private function filterItemData(array $data, ?string $lockedType = null): array
    {
        $type = $lockedType ?? $this->normalizeType($this->post('type', 'classroom'));

        $qtyRaw   = $this->request->getPost('quantity');
        $quantity = is_numeric($qtyRaw) ? (int)$qtyRaw : 0;

        $base = [
            // 'id' should NOT be required; we pass PK separately on update()
            'type'        => $type,
            'category_id' => ($this->post('category_id') !== null && $this->post('category_id') !== '') ? (int)$this->post('category_id') : null,
            'name'        => (string) $this->post('name', ''),
            'description' => $this->post('description'),
            'quantity'    => $quantity,
            'unit'        => $this->post('unit'),
            'sku'         => $this->post('sku'),
            'notes'       => $this->post('notes'),
            'semester'    => $this->semester,
            'school_year' => $this->schoolYear,
            'updated_by'  => $this->currentUserId(),
        ];

        $base['condition'] = ($type === 'classroom') ? $this->post('condition') : null;

        if ($type === 'book') {
            $base['isbn']    = $this->post('isbn');
            $base['edition'] = $this->post('edition');
        } else {
            $base['isbn'] = $base['edition'] = null;
        }

        return $base;
    }

    public function auditClassroomForm(int $itemId)
    {
        $item = $this->itemModel->find($itemId);
        if (!$item || $item['type'] !== 'classroom') {
            return redirect()->back()->with('error', 'Invalid classroom item.');
        }

        // Defaults if columns were just added
        $item['good_qty']         = (int)($item['good_qty']         ?? 0);
        $item['needs_repair_qty'] = (int)($item['needs_repair_qty'] ?? 0);
        $item['need_replace_qty'] = (int)($item['need_replace_qty'] ?? 0);
        $item['cannot_find_qty']  = (int)($item['cannot_find_qty']  ?? 0);

        return view('inventory/classroom/audit_form', ['item' => $item]);
    }

    public function auditClassroomStore(int $itemId)
    {
        $item = $this->itemModel->find($itemId);
        if (!$item || $item['type'] !== 'classroom') {
            return redirect()->back()->with('error', 'Invalid classroom item.');
        }

        // Previous buckets
        $prevGood   = (int)($item['good_qty']         ?? 0);
        $prevRepair = (int)($item['needs_repair_qty'] ?? 0);
        $prevLoss   = (int)($item['need_replace_qty'] ?? 0);
        $prevMiss   = (int)($item['cannot_find_qty']  ?? 0);

        // New buckets (non-negative ints)
        $newRepair = max(0, (int)$this->request->getPost('needs_repair_qty'));
        $newLoss   = max(0, (int)$this->request->getPost('need_replace_qty'));
        $newMiss   = max(0, (int)$this->request->getPost('cannot_find_qty'));

        // Good is derived: keep total physical = good + repair (loss/missing are not on hand)
        // We base it on current on-hand (item.quantity) and newRepair.
        // If you want to enter "good" directly, you can switch to reading it from POST.
        $onHandNow = (int)$item['quantity'];
        $newGood   = max(0, $onHandNow - $newRepair);

        // Movements for loss/missing deltas (affects physical on-hand)
        $deltaLoss = $newLoss - $prevLoss;
        $deltaMiss = $newMiss - $prevMiss;

        // Positive deltas mean more items are now total-loss/missing → stock OUT
        $totalOut = 0;
        if ($deltaLoss > 0) $totalOut += $deltaLoss;
        if ($deltaMiss > 0) $totalOut += $deltaMiss;

        // Negative deltas mean we recovered some previously lost/missing → stock IN
        $totalIn = 0;
        if ($deltaLoss < 0) $totalIn += -$deltaLoss;
        if ($deltaMiss < 0) $totalIn += -$deltaMiss;

        // Apply stock movements
        if ($totalOut > 0) {
            if (!$this->recordMovement($itemId, -$totalOut, 'out', 'Audit: loss/missing increased')) {
                return redirect()->back()->withInput(); // error flashed inside recordMovement
            }
        }
        if ($totalIn > 0) {
            $this->recordMovement($itemId, +$totalIn, 'in', 'Audit: loss/missing decreased');
        }

        // Recalc on-hand now that movements applied
        $this->recalcQuantity($itemId);

        // Persist buckets
        $this->itemModel->update($itemId, [
            'good_qty'         => $newGood,
            'needs_repair_qty' => $newRepair,
            'need_replace_qty' => $newLoss,
            'cannot_find_qty'  => $newMiss,
            // also stamp audit user/time
            'updated_by'       => $this->currentUserId(),
            'updated_at'       => utc_now(),
        ]);

        return redirect()->to(site_url('inventory/classroom'))->with('success', 'Audit saved.');
    }

    private function userNamesByIds(array $ids): array
    {
        $ids = array_values(array_unique(array_filter($ids, fn($v) => !empty($v))));
        if (!$ids) return [];

        if (!$this->db->tableExists('users')) return [];

        // Detect available columns to build a safe SELECT
        $cols = $this->db->getFieldNames('users'); // returns [] if empty
        $hasFirst   = in_array('first_name', $cols, true) || in_array('firstname', $cols, true);
        $hasLast    = in_array('last_name',  $cols, true) || in_array('lastname',  $cols, true);
        $firstCol   = in_array('first_name', $cols, true) ? 'first_name' : (in_array('firstname', $cols, true) ? 'firstname' : null);
        $lastCol    = in_array('last_name',  $cols, true) ? 'last_name'  : (in_array('lastname',  $cols, true)  ? 'lastname'  : null);
        $nameCol    = in_array('name',       $cols, true) ? 'name'       : null;
        $emailCol   = in_array('email',      $cols, true) ? 'email'      : null;

        if ($firstCol || $lastCol) {
            $select = "id, TRIM(CONCAT(IFNULL($firstCol,''),' ',IFNULL($lastCol,''))) AS display_name";
        } elseif ($nameCol) {
            $select = "id, $nameCol AS display_name";
        } elseif ($emailCol) {
            $select = "id, $emailCol AS display_name";
        } else {
            // Last resort: just return IDs as labels
            $out = [];
            foreach ($ids as $id) $out[(int)$id] = 'User #' . $id;
            return $out;
        }

        $rows = $this->db->table('users')->select($select)->whereIn('id', $ids)->get()->getResultArray();
        $map  = [];
        foreach ($rows as $r) {
            $disp = trim((string)($r['display_name'] ?? ''));
            $map[(int)$r['id']] = $disp !== '' ? $disp : ('User #' . $r['id']);
        }
        return $map;
    }

    // Distinct school years for a given inventory type (newest first).
    private function getSchoolYearsForType(string $type): array
    {
        // Pull distinct non-null school_year values for this type
        $years = $this->itemModel
            ->select('school_year')
            ->where('type', $type)
            ->where('school_year IS NOT NULL', null, false)
            ->groupBy('school_year')
            ->orderBy('school_year', 'DESC')
            ->findColumn('school_year') ?? [];

        // Normalize & de-dup
        $years = array_values(array_unique(array_filter(array_map('trim', $years))));

        // Ensure the current year (from config) appears as an option, even if no rows yet
        $currentYear = $this->schoolYear;
        if ($currentYear && !in_array($currentYear, $years, true)) {
            array_unshift($years, $currentYear);
            $years = array_values(array_unique($years));
        }

        return $years;
    }

    private function recordMovement(
        int $itemId,
        int $qtyChange,
        string $type,        // initial|in|out|adjust|distribution
        ?string $reason = null,
        ?string $note = null,
        ?int $classSectionId = null,
        ?int $studentId = null
    ): bool {
        $me = $this->currentUserId();
        $isInitial = ($type === 'initial');

        // ✅ Only seed legacy/year opening for NON-initial inserts
        if (!$isInitial) {
            // Seed a one-time legacy opening movement if *no* movement exists for this item
            $this->ensureInitialMovement($itemId);

            // Ensure there is an opening for *this* school year
            $this->ensureInitialMovementForYear($itemId, $this->schoolYear);

            // Sync on-hand before validating outflow
            $this->recalcQuantity($itemId);
        }

        // Normalize sign for out/distribution
        if (in_array($type, ['out', 'distribution'], true) && $qtyChange > 0) {
            $qtyChange = -abs($qtyChange);
        }

        // Prevent going negative on out/distribution
        if (in_array($type, ['out', 'distribution'], true)) {
            $onHand = (int) ($this->itemModel->select('quantity')->where('id', $itemId)->first()['quantity'] ?? 0);
            if ($onHand + $qtyChange < 0) {
                session()->setFlashdata('error', 'Not enough stock to complete this deduction.');
                return false;
            }
        }

        $ok = $this->movModel->insert([
            'item_id'          => $itemId,
            'qty_change'       => $qtyChange,
            'movement_type'    => $type,
            'reason'           => $reason,
            'note'             => $note,
            'semester'         => $this->semester,
            'school_year'      => $this->schoolYear,
            'performed_by'     => $me,
            'teacher_id'       => $me,
            'student_id'       => $studentId,
            'class_section_id' => $classSectionId,
        ], false);

        if ($ok) {
            $this->recalcQuantity($itemId);
            return true;
        }
        return false;
    }

    private function recalcQuantity(int $itemId): void
    {
        // Sum movements
        $sumRow = $this->movModel
            ->selectSum('qty_change')
            ->where('item_id', $itemId)
            ->first();

        $sum = (int)($sumRow['qty_change'] ?? 0);
        if ($sum < 0) $sum = 0;

        // Fetch current values we compare against
        $item = $this->itemModel
            ->select('id, type, quantity, needs_repair_qty, good_qty')
            ->find($itemId);

        if (!$item) return;

        $updates = [];

        // (optional) if you want quantity to mirror movement sum, keep this block
        // If you prefer to leave quantity as entered, just delete this IF.
        if ((int)$item['quantity'] !== $sum) {
            $updates['quantity'] = $sum;
        }

        // Keep classroom Good = OnHand − NeedsRepair (derived)
        if (($item['type'] ?? null) === 'classroom') {
            $needsRepair = (int)($item['needs_repair_qty'] ?? 0);
            $goodCalc    = max(0, $sum - $needsRepair);
            if (!array_key_exists('good_qty', $item) || (int)$item['good_qty'] !== $goodCalc) {
                $updates['good_qty'] = $goodCalc;
            }
        }

        // ✅ Only update if there is something to update
        if (!empty($updates)) {
            try {
                $this->itemModel->update($itemId, $updates);
            } catch (\CodeIgniter\Database\Exceptions\DataException $e) {
                // This would happen if allowedFields stripped everything
                log_message('error', 'recalcQuantity() skipped update: ' . $e->getMessage());
            }
        }
    }

    public function summary(string $type = 'classroom')
    {
        $type = $this->normalizeType($type);
        $currentSem = $this->semester;
        $currentYear = $this->schoolYear;
        $selectedYear = trim((string) $this->request->getGet('school_year')) ?: $currentYear;

        // Items of this type
        $items = $this->itemModel->where('type', $type)->orderBy('name', 'ASC')->findAll();
        $itemIds = array_map(fn($i) => (int)$i['id'], $items);

        // Aggregate movements for the selected year (or all)
        $agg = [];
        if ($itemIds) {
            $qb = $this->movModel->select('item_id,
             SUM(CASE WHEN qty_change>0 THEN qty_change ELSE 0 END) AS received,
             SUM(CASE WHEN qty_change<0 THEN -qty_change ELSE 0 END) AS issued')
                ->whereIn('item_id', $itemIds)
                ->groupBy('item_id');

            if (strtolower($selectedYear) !== 'all') {
                $qb->where('school_year', $selectedYear);
            }

            foreach ($qb->findAll() as $row) {
                $agg[(int)$row['item_id']] = [
                    'received' => (int)$row['received'],
                    'issued'   => (int)$row['issued'],
                ];
            }
        }

        // school year options
        $schoolYears = $this->getSchoolYearsForType($type);

        return view('inventory/summary', [
            'type'         => $type,
            'items'        => $items,
            'agg'          => $agg,
            'selectedYear' => $selectedYear,
            'currentYear'  => $currentYear,
            'schoolYears'  => $schoolYears,
        ]);
    }

    public function adjustForm(int $itemId)
    {
        $item = $this->itemModel->find($itemId);
        if (!$item) return redirect()->back()->with('error', 'Item not found.');
        return view('inventory/adjust_form', ['item' => $item, 'type' => $item['type']]);
    }

    public function adjustStore(int $itemId)
    {
        $item = $this->itemModel->find($itemId);
        if (!$item) return redirect()->back()->with('error', 'Item not found.');

        $mode   = $this->request->getPost('mode'); // 'in' or 'out' or 'adjust'
        $qty    = (int) $this->request->getPost('quantity');
        $reason = (string) $this->request->getPost('reason');
        $note   = (string) $this->request->getPost('note');

        if ($qty <= 0) return redirect()->back()->with('error', 'Quantity must be > 0.');

        $delta = ($mode === 'out') ? -abs($qty) : (($mode === 'in') ? abs($qty) : $qty);
        $type  = ($mode === 'out') ? 'out' : (($mode === 'in') ? 'in' : 'adjust');

        if (!$this->recordMovement((int)$itemId, $delta, $type, $reason, $note)) {
            return redirect()->back()->withInput(); // error is flashed inside recordMovement
        }

        return redirect()->to(site_url('inventory/summary/' . $item['type']))->with('success', 'Stock updated.');
    }

    /**
     * Teacher book distribution page:
     * - Locks to teacher’s class section if only one, else uses ?class_section_id
     * - Filters BOOKS by category grade range (grade_min/grade_max) against class grade (class_id)
     * - Groups books by CATEGORY (with grade range in the group label) for an <optgroup>-style select
     * - Shows per-student prior distribution history for the selected book in the current school year
     */
    public function teacherDistributeForm()
    {
        // 1) Build teacher/class context (auth/role/no-class handled inside)
        $ctx = $this->buildTeacherClassContext();
        if ($ctx instanceof \CodeIgniter\HTTP\RedirectResponse) {
            return $ctx; // early exit on redirect (not logged in / no class / invalid role)
        }

        // 2) Decide selected class section (lock if teacher has only one)
        $lockClassSection = \is_array($ctx['classSectionIds'] ?? null) && \count($ctx['classSectionIds']) === 1;
        $classSectionId   = $lockClassSection
            ? (int) ($ctx['defaultClassSectionId'] ?? 0)
            : (int) ($this->request->getGet('class_section_id') ?? ($ctx['defaultClassSectionId'] ?? 0));

        // 3) Convert class section -> class grade (class_id as integer grade level)
        $classID = null;
        if (!empty($classSectionId)) {
            $classID = $this->classSectionModel->getClassId($classSectionId); // e.g., 3 for Grade 3
            if ($classID !== null) $classID = (int) $classID;
        }

        // 4) Selected book (optional, to render student history and on-hand)
        $itemId = (int) ($this->request->getGet('item_id') ?? 0);

        // 5) Fetch BOOKS filtered by category grade range and GROUP BY CATEGORY
        $db = \Config\Database::connect();
        $builder = $db->table('inventory_items i')
            ->select('i.id,i.name,i.isbn,i.edition,i.quantity,i.category_id,i.type,
                  c.name AS category_name, c.grade_min, c.grade_max')
            ->join('inventory_categories c', 'c.id = i.category_id', 'left')
            ->where('i.type', 'book');

        if (!empty($classID)) {
            $builder->groupStart()
                // Case A: both bounds set and classID between [min..max]
                ->groupStart()
                ->where('c.grade_min IS NOT NULL', null, false)
                ->where('c.grade_max IS NOT NULL', null, false)
                ->where('c.grade_min <=', (int)$classID)
                ->where('c.grade_max >=', (int)$classID)
                ->groupEnd()

                // Case B: only min set AND equals min
                ->orGroupStart()
                ->where('c.grade_min IS NOT NULL', null, false)
                ->where('c.grade_max', null)               // IS NULL
                ->where('c.grade_min', (int)$classID)      // =
                ->groupEnd()

                // Case C: only max set AND equals max
                ->orGroupStart()
                ->where('c.grade_max IS NOT NULL', null, false)
                ->where('c.grade_min', null)               // IS NULL
                ->where('c.grade_max', (int)$classID)      // =
                ->groupEnd()
                ->groupEnd();
        }

        $rows = $builder->orderBy('i.name', 'ASC')->get()->getResultArray();

        // Build CATEGORY groups keyed by category_id
        $labelFor = static function (?array $r): string {
            $name = $r['category_name'] ?? 'Uncategorized';
            $gmin = $r['grade_min'] ?? null;
            $gmax = $r['grade_max'] ?? null;
            if ($gmin === null && $gmax === null) return $name;
            if ($gmin !== null && $gmax !== null) return $name . ' (G' . (int)$gmin . '–G' . (int)$gmax . ')';
            if ($gmin !== null) return $name . ' (G' . (int)$gmin . '+)';
            return $name . ' (≤G' . (int)$gmax . ')';
        };

        // booksGrouped: [catId => ['label'=>string, 'items'=>[['id'=>..,'display'=>..,'quantity'=>..], ...]]]
        $booksGrouped = [];
        foreach ($rows as $r) {
            $catId = (int)($r['category_id'] ?? 0);
            if (!isset($booksGrouped[$catId])) {
                $booksGrouped[$catId] = [
                    'label' => $labelFor($r),
                    'items' => [],
                ];
            }
            $display = $r['name'];
            if (!empty($r['isbn']))    $display .= ' — ISBN ' . $r['isbn'];
            if (!empty($r['edition'])) $display .= ' (' . $r['edition'] . ')';

            $booksGrouped[$catId]['items'][] = [
                'id'       => (int)$r['id'],
                'display'  => $display,
                'quantity' => (int)($r['quantity'] ?? 0),
            ];
        }

        // Sort groups by label, and each group’s items by display
        uasort($booksGrouped, fn($a, $b) => strnatcasecmp($a['label'], $b['label']));
        foreach ($booksGrouped as &$grp) {
            usort($grp['items'], fn($x, $y) => strnatcasecmp($x['display'], $y['display']));
        }
        unset($grp);

        // 6) Students of the selected class section (provided by context)
        $students = $ctx['studentsData'][$classSectionId] ?? [];

        // 7) History map: how many of the selected book each student already received this school year
        $already = [];
        if ($itemId && $classSectionId) {
            $rowsHist = $this->movModel
                ->select('student_id, SUM(CASE WHEN qty_change < 0 THEN -qty_change ELSE 0 END) AS qty')
                ->where([
                    'item_id'          => $itemId,
                    'movement_type'    => 'distribution',
                    'class_section_id' => $classSectionId,
                    'school_year'      => $ctx['schoolYear'],
                ])
                ->groupBy('student_id')
                ->findAll();

            foreach ($rowsHist as $r) {
                $sid = (int) ($r['student_id'] ?? 0);
                if ($sid) $already[$sid] = (int) ($r['qty'] ?? 0);
            }
        }

        // 8) On-hand for the selected book (to show live available stock)
        $onHand = 0;
        if ($itemId) {
            $book = $this->itemModel->find($itemId);
            if ($book && ($book['type'] ?? '') === 'book') {
                $onHand = (int) ($book['quantity'] ?? 0);
            }
        }

        // 9) Render view
        return view('inventory/teacher_distribute', [
            'booksGrouped'     => $booksGrouped,     // grouped by CATEGORY for <optgroup>
            'items'            => $rows,             // raw list if needed elsewhere (optional)

            'classes'          => $ctx['classes'],
            'class_section_id' => $classSectionId,
            'lockClassSection' => $lockClassSection,

            'item_id'          => $itemId,
            'students'         => $students,
            'already'          => $already,
            'onHand'           => $onHand,

            'schoolYear'       => $ctx['schoolYear'],
            'semester'         => $ctx['semester'],
        ]);
    }

    public function teacherDistributeStore()
    {
        $ctx = $this->buildTeacherClassContext();
        if ($ctx instanceof \CodeIgniter\HTTP\RedirectResponse) {
            return $ctx;
        }

        $itemId        = (int)$this->request->getPost('item_id');
        $postedClassId = (int)$this->request->getPost('class_section_id');
        $selectedIds   = (array)$this->request->getPost('student_ids');
        $note          = (string)$this->request->getPost('note');

        // Enforce teacher’s classes
        $classIds = $ctx['classSectionIds'];
        $classSectionId = (count($classIds) === 1)
            ? (int)$classIds[0]
            : (in_array($postedClassId, $classIds, true) ? $postedClassId : 0);

        if (!$classSectionId) {
            return redirect()->back()->with('error', 'Invalid class selection.');
        }

        // Validate book
        $book = $this->itemModel->find($itemId);
        if (!$book || $book['type'] !== 'book') {
            return redirect()->back()->with('error', 'Invalid book.');
        }

        // Validate students belong to the class
        $students = $ctx['studentsData'][$classSectionId] ?? [];
        $validIds = [];
        foreach ($students as $st) {
            $sid = $st['student_id'] ?? ($st['id'] ?? ($st['user_id'] ?? null));
            if ($sid) $validIds[(int)$sid] = true;
        }
        $selectedIds = array_values(array_unique(array_map('intval', $selectedIds)));
        $selectedIds = array_values(array_filter($selectedIds, fn($sid) => isset($validIds[$sid])));

        // Already distributed this year for this class/book
        $already = [];
        $rows = $this->movModel
            ->select('student_id, SUM(CASE WHEN qty_change < 0 THEN -qty_change ELSE 0 END) AS qty')
            ->where([
                'item_id'          => $itemId,
                'movement_type'    => 'distribution',
                'class_section_id' => $classSectionId,
                'school_year'      => $ctx['schoolYear'],
            ])->groupBy('student_id')->findAll();
        foreach ($rows as $r) {
            $sid = (int)($r['student_id'] ?? 0);
            if ($sid) $already[$sid] = (int)$r['qty'];
        }

        // Only assign to students who don't already have it
        $toGive = [];
        foreach ($selectedIds as $sid) {
            if (($already[$sid] ?? 0) < 1) $toGive[] = $sid;
        }

        // Stock check
        $needed = count($toGive);
        $onHand = (int)$book['quantity'];
        if ($needed > $onHand) {
            return redirect()->back()->withInput()->with('error', 'Not enough stock: need ' . $needed . ', on hand ' . $onHand . '.');
        }

        // Record one movement per student
        $okCount = 0;
        foreach ($toGive as $sid) {
            $ok = $this->recordMovement($itemId, -1, 'distribution', 'Teacher distribution', $note, $classSectionId, $sid);
            if ($ok) $okCount++;
        }

        if ($okCount === 0) {
            return redirect()
                ->to(site_url('inventory/books/distribute?class_section_id=' . $classSectionId . '&item_id=' . $itemId))
                ->with('warning', 'No changes (everyone selected already has this book).');
        }

        return redirect()
            ->to(site_url('inventory/books/distribute?class_section_id=' . $classSectionId . '&item_id=' . $itemId))
            ->with('success', 'Distributed to ' . $okCount . ' student(s).');
    }


    /**
     * Builds the same context your TeacherController::classView() creates.
     * Returns an array with: user, classes, roleNames, classSectionIds, studentsData, taNames, defaultClassSectionId
     * Throws a redirect Response (return it) when access is invalid (not logged in / no classes / invalid role).
     */
    private function buildTeacherClassContext()
    {
        // 1) Logged-in user?
        $user_id = session()->get('user_id');
        if (!$user_id) {
            return redirect()->to('/login')->with('error', 'Please log in first');
        }

        // 2) All assignments (main or TA) for current school year
        $classes = $this->teacherClassModel->getClassAssignmentsByUserId($user_id, $this->schoolYear);

        if (empty($classes)) {
            return redirect()->to('no-classes')->with('message', 'You do not have an assigned class yet. Please contact the administration.');
        }

        // 3) Identify user & role
        $user = $this->userModel->find($user_id);
        if (!$user) {
            return redirect()->to('/error')->with('error', 'User not found');
        }

        $roles = $this->db->table('user_roles ur')
            ->select('r.name')
            ->join('roles r', 'r.id = ur.role_id')
            ->where('ur.user_id', $user_id)
            ->get()->getResultArray();

        $roleNames = array_map(fn($r) => $r['name'], $roles);

        if (in_array('teacher', $roleNames, true)) {
            $user['role_name'] = 'teacher';
        } elseif (in_array('teacher_assistant', $roleNames, true)) {
            $user['role_name'] = 'teacher_assistant';
        } else {
            return redirect()->to('/error')->with('error', 'Access denied');
        }

        // 4) Collect class_section IDs
        $classSectionIds = array_column($classes, 'class_section_id');
        $studentsData = [];

        // 5) Group students by class_section_id
        if (!empty($classSectionIds)) {
            $students = $this->studentClassModel->getStudentsByClassSectionIds($classSectionIds);
            foreach ($students as $student) {
                $studentsData[$student['class_section_id']][] = $student;
            }
        }

        // 6) TA names per class_section (same as your code)
        $taAssignments = $this->teacherClassModel
            ->whereIn('class_section_id', $classSectionIds)
            ->where('school_year', $this->schoolYear)
            ->where('position', 'ta')
            ->findAll();

        $taNames = [];
        foreach ($taAssignments as $ta) {
            $taUser = $this->userModel->find($ta['teacher_id']);
            if ($taUser) {
                $csid = $ta['class_section_id'];
                $taNames[$csid][] = ($taUser['firstname'] ?? '') . ' ' . ($taUser['lastname'] ?? '');
            }
        }

        // 7) Default selection = active session choice (if valid) or first class
        $sessionId = (int)(session()->get('class_section_id') ?? 0);
        if ($sessionId && in_array($sessionId, $classSectionIds, true)) {
            $defaultClassSectionId = $sessionId;
        } else {
            $defaultClassSectionId = $classSectionIds[0] ?? null;
            if ($defaultClassSectionId) {
                session()->set('class_section_id', $defaultClassSectionId);
            }
        }

        return [
            'user'                  => $user,
            'roleNames'             => $roleNames,
            'classes'               => $classes,
            'classSectionIds'       => $classSectionIds,
            'studentsData'          => $studentsData,
            'taNames'               => $taNames,
            'defaultClassSectionId' => $defaultClassSectionId,
            'schoolYear'            => $this->schoolYear,
            'semester'              => $this->semester,
            'user_id'               => $user_id,
        ];
    }

    // Ensure an initial movement exists for THIS school year, even if we started mid-year
    private function ensureInitialMovementForYear(int $itemId, string $schoolYear): void
    {
        // If we already have any movement for this item in this year, make sure an 'initial' exists; else create one.
        $hasAnyThisYear = $this->movModel
            ->where('item_id', $itemId)
            ->where('school_year', $schoolYear)
            ->limit(1)->countAllResults();

        // Already has any movement this year?
        if ($hasAnyThisYear === 0) {
            // No movement at all this year → opening = current on-hand
            $onHand = (int) ($this->itemModel->select('quantity')->where('id', $itemId)->first()['quantity'] ?? 0);
            $this->movModel->insert([
                'item_id'       => $itemId,
                'qty_change'    => $onHand,
                'movement_type' => 'initial',
                'school_year'   => $schoolYear,
                'semester'      => $this->semester ?? null,
                'performed_by'  => $this->currentUserId(),
            ], false);
            return;
        }

        // We have some movement this year. If there's no 'initial' yet, backfill a correct opening.
        $hasInitialThisYear = $this->movModel
            ->where('item_id', $itemId)
            ->where('school_year', $schoolYear)
            ->where('movement_type', 'initial')
            ->limit(1)->countAllResults();

        if ($hasInitialThisYear === 0) {
            // Opening = current_on_hand - net_year_movements
            $onHand  = (int) ($this->itemModel->select('quantity')->where('id', $itemId)->first()['quantity'] ?? 0);
            $netYear = (int) ($this->movModel->selectSum('qty_change')
                ->where('item_id', $itemId)
                ->where('school_year', $schoolYear)
                ->first()['qty_change'] ?? 0);

            $opening = $onHand - $netYear; // reconstruct beginning-of-year truth
            $this->movModel->insert([
                'item_id'       => $itemId,
                'qty_change'    => $opening,
                'movement_type' => 'initial',
                'school_year'   => $schoolYear,
                'semester'      => $this->semester ?? null,
                'performed_by'  => $this->currentUserId(),
            ], false);
        }
    }

    public function create(string $type = 'classroom')
    {
        $categories = $this->catModel->optionsForType($type);
        return view("inventory/{$type}/form", [
            'type'             => $type,
            'item'             => [], // important for create
            'categories'       => $categories,
            'conditionOptions' => [
                'good' => 'Good condition',
                'needs_repair' => 'Needs repair',
                'need_replace' => 'Need replace',
                'cannot_find' => 'Cannot find',
            ],
        ]);
    }

    public function summaryAll()
    {
        // -------- Inputs --------
        $selectedYear = trim((string) $this->request->getGet('school_year')) ?: (string) $this->schoolYear;
        $selectedType = strtolower((string) ($this->request->getGet('type') ?? 'all')); // optional: all|book|classroom|office|kitchen
        $isAllYears   = (strtolower($selectedYear) === 'all');

        // -------- Items query (single source of truth) --------
        $qbItems = $this->db->table('inventory_items i')
            ->select('i.*, c.name AS category_name, CONCAT(u.firstname, " ", u.lastname) AS updated_by_name')
            ->join('inventory_categories c', 'c.id = i.category_id', 'left')
            ->join('users u', 'u.id = i.updated_by', 'left')
            ->orderBy('i.name', 'ASC');

        // Filter by school year unless "all"
        if (!$isAllYears) {
            $qbItems->where('i.school_year', $selectedYear);
        }

        // Optional: filter by type if provided
        if (in_array($selectedType, ['book', 'classroom', 'office', 'kitchen'], true)) {
            $qbItems->where('i.type', $selectedType);
        }

        $items = $qbItems->get()->getResultArray();

        // -------- Prepare output structures --------
        $rows   = [];
        $totals = [
            'opening'     => 0,
            'received'    => 0,
            'distributed' => 0,
            'other_out'   => 0,
            'adjust_net'  => 0,
            'ending'      => 0,
            'onhand'      => 0,
            'variance'    => 0,
        ];

        // If no items, render an empty table gracefully
        if (empty($items)) {
            return view('inventory/summary', [
                'selectedYear' => $selectedYear,
                'currentYear'  => $this->schoolYear,
                'schoolYears'  => $this->getSchoolYearsForType('book') ?: [$this->schoolYear, 'All'],
                'rows'         => $rows,
                'totals'       => $totals,
                // optionally pass the selected type if your view supports it
                'selectedType' => $selectedType,
            ]);
        }

        // -------- Aggregate movements for the fetched items --------
        $itemIds = array_map('intval', array_column($items, 'id'));

        $aggById = [];
        $qb = $this->db->table('inventory_movements m')
            ->select("
                m.item_id,
                SUM(CASE WHEN m.movement_type = 'initial' THEN m.qty_change ELSE 0 END) AS opening,
                SUM(CASE WHEN m.qty_change > 0 AND m.movement_type IN ('in','adjust') THEN m.qty_change ELSE 0 END) AS received,
                SUM(CASE WHEN m.qty_change < 0 AND m.movement_type = 'distribution' THEN -m.qty_change ELSE 0 END) AS distributed,
                SUM(CASE WHEN m.qty_change < 0 AND m.movement_type = 'out' THEN -m.qty_change ELSE 0 END) AS other_out,
                SUM(CASE WHEN m.movement_type = 'adjust' THEN m.qty_change ELSE 0 END) AS adjust_net
            ")
            ->whereIn('m.item_id', $itemIds)
            ->groupBy('m.item_id');

        if (!$isAllYears) {
            $qb->where('m.school_year', $selectedYear);
        }

        foreach ($qb->get()->getResultArray() as $r) {
            $aggById[(int)$r['item_id']] = [
                'opening'     => (int)($r['opening'] ?? 0),
                'received'    => (int)($r['received'] ?? 0),
                'distributed' => (int)($r['distributed'] ?? 0),
                'other_out'   => (int)($r['other_out'] ?? 0),
                'adjust_net'  => (int)($r['adjust_net'] ?? 0),
            ];
        }

        // -------- Build rows + totals --------
        foreach ($items as $it) {
            $id = (int)$it['id'];
            $a  = $aggById[$id] ?? ['opening' => 0, 'received' => 0, 'distributed' => 0, 'other_out' => 0, 'adjust_net' => 0];

            $opening     = (int)$a['opening'];
            $received    = (int)$a['received'];
            $distributed = (int)$a['distributed'];
            $otherOut    = (int)$a['other_out'];
            $adjustNet   = (int)$a['adjust_net'];

            $ending   = $opening + $received - $distributed - $otherOut + $adjustNet;
            $onhand   = (int)$it['quantity'];
            $variance = $onhand - $ending;

            $rows[] = [
                'id'          => $id,
                'name'        => (string)$it['name'],
                'type'        => (string)$it['type'],
                'category'    => (string)($it['category_name'] ?? ''),
                'opening'     => $opening,
                'received'    => $received,
                'distributed' => $distributed,
                'other_out'   => $otherOut,
                'adjust_net'  => $adjustNet,
                'ending'      => $ending,
                'onhand'      => $onhand,
                'variance'    => $variance,
            ];

            $totals['opening']     += $opening;
            $totals['received']    += $received;
            $totals['distributed'] += $distributed;
            $totals['other_out']   += $otherOut;
            $totals['adjust_net']  += $adjustNet;
            $totals['ending']      += $ending;
            $totals['onhand']      += $onhand;
            $totals['variance']    += $variance;
        }

        // -------- Year dropdown options --------
        $schoolYears = $this->getSchoolYearsForType('book') ?: [$this->schoolYear, 'All'];

        return view('inventory/summary', [
            'selectedYear' => $selectedYear,
            'currentYear'  => $this->schoolYear,
            'schoolYears'  => $schoolYears,
            'rows'         => $rows,
            'totals'       => $totals,
            'selectedType' => $selectedType,
        ]);
    }

    /** =========================
     *  LIST
     *  ========================= */
    public function movementsIndex()
    {
        // Academic filters
        $selectedYearRaw = $this->request->getGet('school_year');
        $selectedSemRaw  = $this->request->getGet('semester');
        $selectedYear = $selectedYearRaw === null ? (string) $this->schoolYear : trim((string) $selectedYearRaw);
        $selectedSem  = $selectedSemRaw  === null ? (string) $this->semester   : trim((string) $selectedSemRaw);

        // Pull movements with friendly names when possible
        $builder = $this->db->table('inventory_movements m')
            ->select([
                'm.*',
                'ii.name AS item_name',

                // Performed by (users)
                "CONCAT(pb.firstname, ' ', pb.lastname) AS performed_by_name",

                // Teacher (users)
                "CONCAT(t.firstname, ' ', t.lastname) AS teacher_name",

                // Student (students)
                "CONCAT(s.firstname, ' ', s.lastname) AS student_name",

                // Class section
                'cs.class_section_name',
            ])
            ->join('inventory_items ii', 'ii.id = m.item_id', 'left')
            ->join('users pb', 'pb.id = m.performed_by', 'left')
            ->join('users t',  't.id  = m.teacher_id',  'left')
            ->join('students s', 's.id = m.student_id',  'left')
            ->join('classSection cs', 'cs.class_section_id = m.class_section_id', 'left')
            ->orderBy('m.id', 'DESC');

        if ($selectedYear !== '') $builder->where('m.school_year', $selectedYear);
        if ($selectedSem  !== '') $builder->where('m.semester',    $selectedSem);

        $movements = $builder->get()->getResultArray();

        return view('inventory/movements/index', [
            'movements' => $movements,
            'schoolYear'=> $selectedYear,
            'semester'  => $selectedSem,
        ]);
    }

    /** =========================
     *  CREATE (form)
     *  ========================= */
    public function movementsCreate()
    {
        return view('inventory/movements/form', [
            'movement'      => $this->blankMovement(),
            'movementTypes' => $this->movementTypes,
            'semesters'     => $this->semesters,
            'items'         => $this->getItems(),
            'classSections' => $this->getClassSections(),
            'teachers'      => $this->getTeachers(),
            'students'      => $this->getStudents(),
            'action'        => site_url('inventory/movements/store'),
            'title'         => 'Create Inventory Movement',
            'isCreate'      => true,
        ]);
    }

    /** =========================
     *  STORE (POST)
     *  ========================= */
    public function movementsStore()
    {
        $rules = [
            'item_id'       => 'required|is_natural_no_zero',
            'qty_change'    => 'required|integer',
            'movement_type' => 'required|in_list[' . implode(',', $this->movementTypes) . ']',
            'reason'        => 'permit_empty|max_length[120]',
            'note'          => 'permit_empty|string',
            'semester'      => 'permit_empty|in_list[' . implode(',', $this->semesters) . ']',
            'school_year'   => 'permit_empty|max_length[16]',
            'teacher_id'    => 'permit_empty|is_natural_no_zero',
            'student_id'    => 'permit_empty|is_natural_no_zero',
            'class_section_id' => 'permit_empty|is_natural_no_zero',
        ];

        if (! $this->validate($rules)) {
            return redirect()->back()->withInput()->with('status', 'error')
                ->with('message', implode(' ', $this->validator->getErrors()));
        }

        $userId = (int) (session('user_id') ?? 0);
        $itemId = (int) $this->request->getPost('item_id');
        $movementType = (string) $this->request->getPost('movement_type');

        $item = $this->itemModel->find($itemId);
        if (!$item) {
            return redirect()->back()->withInput()->with('status', 'error')
                ->with('message', 'Selected item not found.');
        }

        $qtyChange = $this->normalizeMovementQty($movementType, (int) $this->request->getPost('qty_change'));
        if ($this->wouldGoNegative($itemId, $qtyChange)) {
            return redirect()->back()->withInput();
        }

        $semester = trim((string) $this->request->getPost('semester'));
        $schoolYear = trim((string) $this->request->getPost('school_year'));
        if ($semester === '') $semester = (string) $this->semester;
        if ($schoolYear === '') $schoolYear = (string) $this->schoolYear;

        $data = [
            'item_id'         => $itemId,
            'qty_change'      => $qtyChange,
            'movement_type'   => $movementType,
            'reason'          => trim((string)$this->request->getPost('reason')),
            'note'            => trim((string)$this->request->getPost('note')),
            'semester'        => $semester ?: null,
            'school_year'     => $schoolYear ?: null,
            'performed_by'    => $userId ?: null,
            'teacher_id'      => $this->nullableInt($this->request->getPost('teacher_id')),
            'student_id'      => $this->nullableInt($this->request->getPost('student_id')),
            'class_section_id' => $this->nullableInt($this->request->getPost('class_section_id')),
            'created_at'      => utc_now(),
            'updated_at'      => utc_now(),
        ];

        $ok = $this->db->table('inventory_movements')->insert($data);

        if (! $ok) {
            return redirect()->back()->withInput()->with('status', 'error')
                ->with('message', 'Could not save movement.');
        }

        $this->recalcQuantity($itemId);

        return redirect()->to(site_url('inventory/movements'))
            ->with('status', 'success')
            ->with('message', 'Movement created.');
    }

    /** =========================
     *  EDIT (form)
     *  ========================= */
    public function movementsEdit(int $id)
    {
        $movement = $this->db->table('inventory_movements')->where('id', $id)->get()->getRowArray();
        if (!$movement) {
            return redirect()->to(site_url('inventory/movements'))
                ->with('status', 'error')->with('message', 'Movement not found.');
        }

        // enrich for display (names)
        $movement = $this->hydrateNames($movement);

        return view('inventory/movements/form', [
            'movement'      => $movement, // already hydrated by your helper
            'movementTypes' => $this->movementTypes,
            'semesters'     => $this->semesters,
            'items'         => $this->getItems(),
            'classSections' => $this->getClassSections(),
            'teachers'      => $this->getTeachers(),
            'students'      => $this->getStudents(),
            'action'        => site_url('inventory/movements/update/' . $id),
            'title'         => 'Edit Inventory Movement',
            'isCreate'      => false,
        ]);
    }

    /** =========================
     *  UPDATE (POST)
     *  ========================= */
    public function movementsUpdate(int $id)
    {
        $rules = [
            'qty_change'    => 'required|integer',
            'movement_type' => 'required|in_list[' . implode(',', $this->movementTypes) . ']',
            'reason'        => 'permit_empty|max_length[120]',
            'note'          => 'permit_empty|string',
            'semester'      => 'permit_empty|in_list[' . implode(',', $this->semesters) . ']',
            'school_year'   => 'permit_empty|max_length[16]',
            'teacher_id'    => 'permit_empty|is_natural_no_zero',
            'student_id'    => 'permit_empty|is_natural_no_zero',
            'class_section_id' => 'permit_empty|is_natural_no_zero',
        ];

        if (! $this->validate($rules)) {
            return redirect()->back()->withInput()->with('status', 'error')
                ->with('message', implode(' ', $this->validator->getErrors()));
        }

        $existing = $this->db->table('inventory_movements')->where('id', $id)->get()->getRowArray();
        if (!$existing) {
            return redirect()->to(site_url('inventory/movements'))
                ->with('status', 'error')->with('message', 'Movement not found.');
        }

        $movementType = (string) $this->request->getPost('movement_type');
        $qtyChange = $this->normalizeMovementQty($movementType, (int) $this->request->getPost('qty_change'));

        $itemId = (int) ($existing['item_id'] ?? 0);
        if ($itemId > 0) {
            $delta = $qtyChange - (int) ($existing['qty_change'] ?? 0);
            if ($delta !== 0 && $this->wouldGoNegative($itemId, $delta)) {
                return redirect()->back()->withInput();
            }
        }

        $semester = trim((string) $this->request->getPost('semester'));
        $schoolYear = trim((string) $this->request->getPost('school_year'));
        if ($semester === '') $semester = (string) $this->semester;
        if ($schoolYear === '') $schoolYear = (string) $this->schoolYear;

        $data = [
            // item_id is often immutable after posting history; include if you want to allow editing:
            // 'item_id'         => (int) $this->request->getPost('item_id'),
            'qty_change'      => $qtyChange,
            'movement_type'   => $movementType,
            'reason'          => trim((string)$this->request->getPost('reason')),
            'note'            => trim((string)$this->request->getPost('note')),
            'semester'        => $semester ?: null,
            'school_year'     => $schoolYear ?: null,
            'teacher_id'      => $this->nullableInt($this->request->getPost('teacher_id')),
            'student_id'      => $this->nullableInt($this->request->getPost('student_id')),
            'class_section_id' => $this->nullableInt($this->request->getPost('class_section_id')),
            'updated_at'      => utc_now(),
        ];

        $ok = $this->db->table('inventory_movements')->where('id', $id)->update($data);

        if (! $ok) {
            return redirect()->back()->withInput()->with('status', 'error')
                ->with('message', 'Could not update movement.');
        }

        if ($itemId > 0) {
            $this->recalcQuantity($itemId);
        }

        return redirect()->to(site_url('inventory/movements'))
            ->with('status', 'success')
            ->with('message', 'Movement updated.');
    }

    /** =========================
     *  DELETE (POST)
     *  ========================= */
    public function movementsDelete(int $id)
    {
        // Optional: authorization checks here
        log_message('info', 'Inventory: delete one attempt', ['id' => $id, 'user' => (int)(session('user_id') ?? 0)]);
        $movement = $this->db->table('inventory_movements')->select('id, item_id')->where('id', $id)->get()->getRowArray();
        if (!$movement) {
            return redirect()->back()->with('status', 'error')->with('message', 'Movement not found.');
        }

        $ok = $this->db->table('inventory_movements')->where('id', $id)->delete();
        log_message('info', 'Inventory: delete one result', ['id' => $id, 'ok' => (bool)$ok, 'affected' => $this->db->affectedRows()]);

        if (! $ok) {
            return redirect()->back()->with('status', 'error')
                ->with('message', 'Could not delete movement.');
        }

        if (!empty($movement['item_id'])) {
            $this->recalcQuantity((int) $movement['item_id']);
        }
        return redirect()->to(site_url('inventory/movements'))
            ->with('status', 'success')->with('message', 'Movement deleted.');
    }

    /* ===========================
     *  Helpers
     * =========================== */

    /** Default structure for create form */
    private function blankMovement(): array
    {
        return [
            'id'               => null,
            'item_id'          => null,
            'qty_change'       => 0,
            'movement_type'    => 'adjust',
            'reason'           => '',
            'note'             => '',
            'semester'         => null,
            'school_year'      => null,
            'performed_by'     => session('user_id') ?? null,
            'teacher_id'       => null,
            'student_id'       => null,
            'class_section_id' => null,
            'created_at'       => null,
            'updated_at'       => null,
        ];
    }

    /** Coerce to nullable int */
    private function nullableInt($val): ?int
    {
        if ($val === null || $val === '' || $val === '0') {
            return null;
        }
        return (int) $val;
    }

    private function normalizeMovementQty(string $movementType, int $qtyChange): int
    {
        if (in_array($movementType, ['out', 'distribution'], true) && $qtyChange > 0) {
            return -abs($qtyChange);
        }
        return $qtyChange;
    }

    private function wouldGoNegative(int $itemId, int $delta): bool
    {
        $onHand = (int) ($this->itemModel->select('quantity')->where('id', $itemId)->first()['quantity'] ?? 0);
        if ($onHand + $delta < 0) {
            session()->setFlashdata('status', 'error');
            session()->setFlashdata('message', 'Not enough stock to apply this movement.');
            return true;
        }
        return false;
    }

    /** Hydrate display names for a row (used in edit) */
    private function hydrateNames(array $m): array
    {
        // Item
        if (!empty($m['item_id'])) {
            $row = $this->db->table('inventory_items')->select('name')->where('id', $m['item_id'])->get()->getRowArray();
            $m['item_name'] = $row['name'] ?? null;
        }

        // Performed By (users)
        if (!empty($m['performed_by'])) {
            $row = $this->db->table('users')
                ->select("CONCAT(firstname, ' ', lastname) AS fullname")
                ->where('id', $m['performed_by'])
                ->get()->getRowArray();
            $m['performed_by_name'] = $row['fullname'] ?? null;
        }

        // Teacher (users)
        if (!empty($m['teacher_id'])) {
            $row = $this->db->table('users')
                ->select("CONCAT(firstname, ' ', lastname) AS fullname")
                ->where('id', $m['teacher_id'])
                ->get()->getRowArray();
            $m['teacher_name'] = $row['fullname'] ?? null;
        }

        // Student (students)
        if (!empty($m['student_id'])) {
            $row = $this->db->table('students')
                ->select("CONCAT(firstname, ' ', lastname) AS fullname")
                ->where('id', $m['student_id'])
                ->get()->getRowArray();
            $m['student_name'] = $row['fullname'] ?? null;
        }

        // Class section (by CODE)
        if (!empty($m['class_section_id'])) {
            $row = $this->db->table('classSection')
                ->select('class_section_name')
                ->where('class_section_id', $m['class_section_id'])
                ->get()->getRowArray();
            $m['class_section_name'] = $row['class_section_name'] ?? null;
        }

        return $m;
    }

    private function getTeachers(): array
    {
        $rows = $this->teacherModel->getTeachersAndTAs();

        // Normalize to include a ready-to-use fullname
        return array_map(static function ($r) {
            $fullname = trim(($r['firstname'] ?? '') . ' ' . ($r['lastname'] ?? ''));
            return [
                'id'        => (int) $r['id'],
                'firstname' => $r['firstname'] ?? '',
                'lastname'  => $r['lastname'] ?? '',
                'fullname'  => $fullname,
                'email'     => $r['email'] ?? '',
                'cellphone' => $r['cellphone'] ?? '',
                'role'      => $r['role'] ?? '',
            ];
        }, $rows);
    }


    /** Dropdowns */
    private function getItems(): array
    {
        return $this->db->table('inventory_items')->select('id, name')->orderBy('name', 'ASC')->get()->getResultArray();
    }
    private function getClassSections(): array
    {
        return $this->db->table('classSection')
            ->select('class_section_id, class_section_name')
            ->orderBy('class_section_name', 'ASC')->get()->getResultArray();
    }


    private function getStudents(): array
{
    // Returns: id, fullname
    return $this->db->table('students')
        ->select("id, CONCAT(firstname, ' ', lastname) AS fullname", false)
        ->orderBy('lastname', 'ASC')
        ->orderBy('firstname', 'ASC')
        ->get()
        ->getResultArray();
}

    public function movementsBulkDelete()
    {
    $ids = (array) $this->request->getPost('ids');

    // sanitize to ints and drop invalids
    $ids = array_values(array_filter(array_map('intval', $ids), fn($v) => $v > 0));

    if (empty($ids)) {
        log_message('warning', 'Inventory: bulk delete called with empty ids', ['user' => (int)(session('user_id') ?? 0)]);
        return redirect()->back()->with('status','error')->with('message','No movements selected.');
    }

    log_message('info', 'Inventory: bulk delete attempt', ['count' => count($ids), 'ids' => $ids, 'user' => (int)(session('user_id') ?? 0)]);
    $itemIds = $this->db->table('inventory_movements')
        ->select('item_id')
        ->whereIn('id', $ids)
        ->get()->getResultArray();
    $itemIds = array_values(array_unique(array_filter(array_map(
        static fn($r) => (int) ($r['item_id'] ?? 0),
        $itemIds
    ))));

    $ok = $this->db->table('inventory_movements')->whereIn('id', $ids)->delete();
    log_message('info', 'Inventory: bulk delete result', ['ok' => (bool)$ok, 'affected' => $this->db->affectedRows()]);

    if (! $ok) {
        return redirect()->back()->with('status','error')->with('message','Bulk delete failed.');
    }

    foreach ($itemIds as $itemId) {
        $this->recalcQuantity($itemId);
    }

    return redirect()->to(site_url('inventory/movements'))
        ->with('status','success')
        ->with('message', count($ids) . ' movement(s) deleted.');
}

}