<?php

namespace App\Controllers\View;

use App\Controllers\BaseController;
use App\Models\StudentClassModel;
use App\Models\ClassPreparationLogModel;
use App\Models\ConfigurationModel;
use App\Models\ClassSectionModel;
use App\Models\ClassPrepAdjustmentModel;
use App\Models\UserModel;

class ClassPreparationController extends BaseController
{
    protected $studentClassModel;
    protected $prepLogModel;
    protected $classSectionModel;
    protected $configModel;
    protected $userModel;
    protected $db;
    protected $schoolYear;
    protected $semester;
    protected $adjustmentModel;
    /** Cache for roster presence per school year. */
    private array $rosterPresenceCache = [];
    /** Cache table-column checks to avoid repeated schema queries. */
    private array $columnExistsCache = [];
    // Inside ClassPreparationController (class scope, not inside a method)
    private array $allowedPrepCategories = [
        'Grade Box',
        'Large Table',
        'Regular Chair',
        'Small Chair',
        'Small Table',
        'Teacher Chair',
        'Trash Bin',
        'White Board',
    ];


    public function __construct()
    {
        $this->studentClassModel = new StudentClassModel();
        $this->prepLogModel      = new ClassPreparationLogModel();
        $this->classSectionModel = new ClassSectionModel();
        $this->configModel = new ConfigurationModel();
        $this->adjustmentModel = new ClassPrepAdjustmentModel();

        $this->userModel = new UserModel();
        $this->db = \Config\Database::connect();

        $this->schoolYear = $this->configModel->getConfig('school_year');
        $this->semester = $this->configModel->getConfig('semester');
    }

    public function index()
    {
        $schoolYear = (string)($this->request->getGet('school_year') ?? $this->schoolYear);
        $semParam   = $this->request->getGet('semester');
        $semester   = (is_string($semParam) && $semParam !== '') ? (string)$semParam : (string)$this->semester;
        $allowed    = $this->allowedPrepCategories;
        $hasRoster  = $this->hasRosterForSchoolYear($schoolYear);

        // 1) Get student count per class-section (distinct students, correct semester)
        $scQ = $this->db->table('student_class sc')
            ->select('sc.class_section_id, COUNT(DISTINCT sc.student_id) AS student_count', false)
            ->join('students s', 's.id = sc.student_id', 'inner')
            ->where('s.is_active', 1)
            ->where('sc.school_year', $schoolYear);
        $classSections = $hasRoster
            ? $scQ->groupBy('sc.class_section_id')->get()->getResultArray()
            : [];

        // 2) Inventory availability — prefer good_qty when present, else condition='good' quantity.
        $inventoryMap = $this->buildInventoryAvailability($schoolYear, $semester, $allowed);

        // Seed totals with allowed categories for stable ordering
        $requiredTotals = array_fill_keys($allowed, 0);
        $prepResults    = [];

        // 3) Build prep per section (once!)
        foreach ($classSections as $section) {
            $classSectionId = $section['class_section_id'];
            $studentCount   = (int)$section['student_count'];
            $classLevel     = $this->getClassLevelBySection($classSectionId);
            $className      = $this->classSectionModel->getClassSectionNameBySectionId($classSectionId);

            // Calculate required items (whitelist inside)
            $baseItems = $this->calculatePrepItems($studentCount, $classLevel, $classSectionId, $schoolYear);

            // --- Apply adjustments (only Small/Large Table), clamp >= 0 ---
            $rawAdjustments = $this->getAdjustments((string) $classSectionId, (string) $schoolYear);

            $adjMap = ['Large Table' => 0, 'Small Table' => 0];

            foreach ($rawAdjustments as $a) {
                $item  = $a['item_name'];
                $delta = (int)$a['adjustment'];

                if (array_key_exists($item, $adjMap)) {
                    $adjMap[$item] = $delta;
                }

                if (isset($baseItems[$item])) {
                    $baseItems[$item] = max(0, (int)$baseItems[$item] + $delta);
                } elseif (in_array($item, $allowed, true)) {
                    $baseItems[$item] = max(0, $delta);
                }
            }

            // 4) Accumulate global totals (allowed only)
            foreach ($allowed as $cat) {
                $requiredTotals[$cat] += (int)($baseItems[$cat] ?? 0);
            }

            // 5) Compare with last snapshot; do not save here — only on print or explicit API
            $oldSnap = $this->getLatestPrepSnapshot((string) $classSectionId, (string) $schoolYear);

            $oldPrep    = $oldSnap ? json_decode($oldSnap['prep_data'], true) : [];
            $hasChanged = $this->hasPrepChanged($baseItems, $oldPrep);

            // 6) Push exactly ONCE
            $prepResults[] = [
                'class_section'    => $className,
                'class_section_id' => $classSectionId,
                'student_count'    => $studentCount,
                'class_level'      => $classLevel,
                'prep_items'       => $baseItems,
                'needs_print'      => $hasChanged,
                'last_printed_at'  => $oldSnap['created_at'] ?? null,
                'adjustments'      => $adjMap,
            ];
        }

        // 7) Shortages (compare totals vs inventory)
        $shortages = [];
        foreach ($requiredTotals as $item => $reqQty) {
            $have = (int)($inventoryMap[$item] ?? 0);
            if ($have < (int)$reqQty) {
                $shortages[$item] = (int)$reqQty - $have;
            }
        }

        return view('class_prep/list', [
            'prepResults' => $prepResults,
            'schoolYear'  => $schoolYear,
            'semester'    => $semester,
            'shortages'   => $shortages,
            'totalNeeded' => $requiredTotals,
            'available'   => $inventoryMap,
            // For temporary debugging, you can pass these too:
            // 'joinGood' => $joinGood, 'nameGood' => $nameGood, 'joinCond' => $joinCond, 'nameCond' => $nameCond,
        ]);
    }

    private function getTeacherCountForSection(string $classSectionId, ?string $schoolYear = null): int
    {
        $schoolYear = $schoolYear ?? $this->schoolYear;

        // positions: 'main' and 'ta' (per your new schema)
        $row = $this->db->table('teacher_class')
            ->select('COUNT(*) AS cnt')
            ->where('class_section_id', $classSectionId)
            ->whereIn('position', ['main', 'ta'])
            ->where('school_year', $schoolYear)
            ->get()->getRowArray();

        return (int)($row['cnt'] ?? 0);
    }

    private function calculatePrepItems(int $students, int $classLevel, string $classSectionId, ?string $schoolYear = null): array
    {
        // 1) Stable keys: your whitelist, all zero to start
        $allowed = $this->allowedPrepCategories;
        $items   = array_fill_keys($allowed, 0);

        // 2) Fetch only the classroom categories you care about (optionally with grade bounds)
        $categories = $this->db->table('inventory_categories')
            ->select('name, grade_min, grade_max')
            ->where('type', 'classroom')
            ->whereIn('name', $allowed)
            ->orderBy('name')
            ->get()
            ->getResultArray();

        // 3) Rules
        $isLowerGrades = in_array($classLevel, [1, 2], true);
        $teacherCount  = $this->getTeacherCountForSection($classSectionId, $schoolYear ?? $this->schoolYear);

        foreach ($categories as $cat) {
            $name     = $cat['name'];      // e.g., 'Small Table'
            $gradeMin = $cat['grade_min']; // may be null
            $gradeMax = $cat['grade_max']; // may be null

            // Respect optional bounds
            if ($gradeMin !== null && $classLevel < (int)$gradeMin) {
                $items[$name] = 0;
                continue;
            }
            if ($gradeMax !== null && $classLevel > (int)$gradeMax) {
                $items[$name] = 0;
                continue;
            }

            $qty = 0;
            switch (strtolower($name)) {
                case 'small table':
                    $qty = $isLowerGrades ? (int)ceil($students / 3) : 0;
                    break;
                case 'large table':
                    $qty = $isLowerGrades ? 0 : (int)ceil($students / 4);
                    break;
                case 'small chair':
                    $qty = $isLowerGrades ? $students : 0;
                    break;
                case 'regular chair':
                    $qty = $isLowerGrades ? 0 : $students;
                    break;
                case 'teacher chair':
                    $qty = (int)$teacherCount;
                    break;
                case 'trash bin':
                case 'white board':
                case 'grade box':
                    $qty = 1;
                    break;
                default:
                    $qty = 0;
            }

            // Only set for allowed keys (guards against unexpected DB rows)
            if (array_key_exists($name, $items)) {
                $items[$name] = $qty;
            }
        }

        return $items;
    }


    public function saveAdjustment()
    {
        $data = $this->request->getPost();
        $sectionId = $data['class_section_id'];
        $schoolYear = $data['school_year'];
        $adjustments = $data['adjustments'] ?? [];

        foreach ($adjustments as $itemName => $adjustment) {
            $adjustmentQuery = $this->adjustmentModel
                ->where('class_section_id', $sectionId)
                ->where('item_name', $itemName);

            $adjustmentTable = $this->adjustmentModel->getTable();

            if ($schoolYear !== '' && $this->tableHasColumn($adjustmentTable, 'school_year')) {
                $adjustmentQuery->where('school_year', $schoolYear);
            }

            $row = $adjustmentQuery->first();

            if ($row) {
                // Update
                $this->adjustmentModel->update($row['id'], ['adjustment' => (int)$adjustment, 'adjustable' => 1]);
            } else {
                // Insert
                $insertData = [
                    'class_section_id' => $sectionId,
                    'item_name'        => $itemName,
                    'adjustment'       => (int) $adjustment,
                    'adjustable'       => 1,
                ];

                if ($schoolYear !== '' && $this->tableHasColumn($adjustmentTable, 'school_year')) {
                    $insertData['school_year'] = $schoolYear;
                }

                $this->adjustmentModel->insert($insertData);
            }
        }

        return redirect()->to(site_url('class-prep?school_year=' . urlencode($schoolYear)))
            ->with('success', 'Adjustments saved successfully.');
    }



    /* ---------- helpers ---------- */

    private function hasPrepChanged(array $new, array $old): bool
    {
        ksort($new);
        ksort($old);

        return json_encode($new) !== json_encode($old);
    }

    public function print($classSectionId, $schoolYear)
    {
        // Friendly label (if you have this helper)
        $className = $this->classSectionModel->getClassSectionNameBySectionId($classSectionId) ?? $classSectionId;

        // Distinct student count for this term
        $studentQ = $this->db->table('student_class sc')
            ->select('COUNT(DISTINCT sc.student_id) AS cnt', false)
            ->join('students s', 's.id = sc.student_id', 'inner')
            ->where('s.is_active', 1)
            ->where('sc.class_section_id', $classSectionId)
            ->where('sc.school_year', $schoolYear);
        $studentRow = $studentQ->get()->getRowArray();
        $studentCount = (int)($studentRow['cnt'] ?? 0);

        // Live calc + adjustments
        $classLevel = $this->getClassLevelBySection((string)$classSectionId);
        $items      = $this->calculatePrepItems($studentCount, $classLevel, (string)$classSectionId, (string)$schoolYear);

        $rawAdjustments = $this->adjustmentModel
            ->where('class_section_id', $classSectionId)
            ->where('school_year', $schoolYear)
            ->where('adjustable', 1)
            ->findAll();
        foreach ($rawAdjustments as $a) {
            $item  = $a['item_name'];
            $delta = (int)$a['adjustment'];
            if (isset($items[$item])) $items[$item] = max(0, (int)$items[$item] + $delta);
        }

        // Record a snapshot at print time as the new baseline
        $this->prepLogModel->insert(
            $this->buildPrepLogData(
                (string) $classSectionId,
                (string) $className,
                (string) $schoolYear,
                $items,
                utc_now()
            )
        );

        // Return PRINT view (HTML) that auto-opens the print dialog
        return view('class_prep/print', [
            'classSectionId' => $classSectionId,
            'classSection'   => $className,
            'schoolYear'     => $schoolYear,
            'prepItems'      => $items,
        ]);
    }

    /**
     * API: Return class prep data and change flags for all sections in a year.
     * GET: school_year
     * Response: { ok, schoolYear, results:[{ class_section_id, class_section, student_count, class_level, prep_items, needs_print, last_printed_at }], totals, shortages }
     */
    public function apiList()
    {
        $schoolYear = (string)($this->request->getGet('school_year') ?? $this->schoolYear);
        $semParam   = $this->request->getGet('semester');
        $semester   = (is_string($semParam) && $semParam !== '') ? (string)$semParam : (string)$this->semester;
        $allowed    = $this->allowedPrepCategories;
        $hasRoster  = $this->hasRosterForSchoolYear($schoolYear);

        // Student counts
        $scQ = $this->db->table('student_class sc')
            ->select('sc.class_section_id, COUNT(DISTINCT sc.student_id) AS student_count', false)
            ->join('students s', 's.id = sc.student_id', 'inner')
            ->where('s.is_active', 1)
            ->where('sc.school_year', $schoolYear);
        $classSections = $hasRoster
            ? $scQ->groupBy('sc.class_section_id')->get()->getResultArray()
            : [];

        // Build inventory availability maps
        $inventoryMap = $this->buildInventoryAvailability($schoolYear, $semester, $allowed);

        $requiredTotals = array_fill_keys($allowed, 0);
        $results = [];

        foreach ($classSections as $row) {
            $classSectionId = (string)$row['class_section_id'];
            $studentCount   = (int)$row['student_count'];
            $classLevel     = $this->getClassLevelBySection($classSectionId);
            $className      = $this->classSectionModel->getClassSectionNameBySectionId($classSectionId);

            $baseItems = $this->calculatePrepItems($studentCount, $classLevel, $classSectionId, $schoolYear);
            $rawAdjustments = $this->getAdjustments((string) $classSectionId, (string) $schoolYear);
            $adjMap = ['Large Table' => 0, 'Small Table' => 0];
            foreach ($rawAdjustments as $a) {
                $item  = $a['item_name'];
                $delta = (int)$a['adjustment'];
                if (array_key_exists($item, $adjMap)) $adjMap[$item] = $delta;
                if (isset($baseItems[$item])) $baseItems[$item] = max(0, (int)$baseItems[$item] + $delta);
            }

            foreach ($allowed as $cat) {
                $requiredTotals[$cat] += (int)($baseItems[$cat] ?? 0);
            }

            $oldSnap = $this->getLatestPrepSnapshot((string) $classSectionId, (string) $schoolYear);
            $oldPrep    = $oldSnap ? json_decode($oldSnap['prep_data'], true) : [];
            $hasChanged = $this->hasPrepChanged($baseItems, $oldPrep);

            $results[] = [
                'class_section'    => $className,
                'class_section_id' => $classSectionId,
                'student_count'    => $studentCount,
                'class_level'      => $classLevel,
                'prep_items'       => $baseItems,
                'needs_print'      => $hasChanged,
                'last_printed_at'  => $oldSnap['created_at'] ?? null,
                'adjustments'      => $adjMap,
            ];
        }

        // Shortages
        $shortages = [];
        foreach ($requiredTotals as $item => $reqQty) {
            $have = (int)($inventoryMap[$item] ?? 0);
            if ($have < (int)$reqQty) $shortages[$item] = (int)$reqQty - $have;
        }

        return $this->response->setJSON([
            'ok'          => true,
            'schoolYear'  => $schoolYear,
            'results'     => $results,
            'totals'      => $requiredTotals,
            'shortages'   => $shortages,
            'csrf_token'  => csrf_token(),
            'csrf_hash'   => csrf_hash(),
        ]);
    }

    /**
     * API: Mark selected classes as printed (save baseline snapshot).
     * POST: school_year, class_section_ids[]
     */
    public function apiMarkPrinted()
    {
        $schoolYear = (string)($this->request->getPost('school_year') ?? $this->schoolYear);
        $semParam   = $this->request->getPost('semester');
        $semester   = (is_string($semParam) && $semParam !== '') ? (string)$semParam : (string)$this->semester;
        $ids = $this->request->getPost('class_section_ids') ?? [];
        if (!is_array($ids)) $ids = $ids ? [$ids] : [];
        $ids = array_values(array_unique(array_filter(array_map('strval', $ids))));

        $now = utc_now();
        $count = 0;
        foreach ($ids as $classSectionId) {
            $studentQ = $this->db->table('student_class sc')
                ->select('COUNT(DISTINCT sc.student_id) AS cnt', false)
                ->join('students s', 's.id = sc.student_id', 'inner')
                ->where('s.is_active', 1)
                ->where('sc.class_section_id', $classSectionId)
                ->where('sc.school_year', $schoolYear);
            $studentRow = $studentQ->get()->getRowArray();
            $studentCount = (int)($studentRow['cnt'] ?? 0);
            $classLevel   = $this->getClassLevelBySection((string)$classSectionId);
            $className    = $this->classSectionModel->getClassSectionNameBySectionId($classSectionId) ?? $classSectionId;

            $items = $this->calculatePrepItems($studentCount, $classLevel, (string)$classSectionId, (string)$schoolYear);

            $rawAdjustments = $this->getAdjustments((string) $classSectionId, (string) $schoolYear);
            foreach ($rawAdjustments as $a) {
                $item  = $a['item_name'];
                $delta = (int)$a['adjustment'];
                if (isset($items[$item])) $items[$item] = max(0, (int)$items[$item] + $delta);
            }

            try {
                $this->prepLogModel->insert(
                    $this->buildPrepLogData(
                        (string) $classSectionId,
                        (string) $className,
                        (string) $schoolYear,
                        $items,
                        $now
                    )
                );
                $count++;
            } catch (\Throwable $e) {
                // ignore and continue
            }
        }

        return $this->response->setJSON([
            'ok'         => true,
            'updated'    => $count,
            'csrf_token' => csrf_token(),
            'csrf_hash'  => csrf_hash(),
        ]);
    }


    private function getClassLevelBySection(string $classSectionId): int
    {
        // Prefer the human-readable section name for grade parsing.
        $sectionName = $this->classSectionModel->getClassSectionNameBySectionId($classSectionId);
        $label = $sectionName !== null && $sectionName !== '' ? (string) $sectionName : $classSectionId;

        // If it's clearly Kindergarten (KG/K)
        if (preg_match('/^(kg|k)(\b|[^a-z0-9])/i', $label)) {
            return 1; // treat Kindergarten as lower grade
        }

        // Remove non-digits, then infer by leading digit
        $numValue = (int) preg_replace('/\D/', '', $label);

        if ($numValue > 0 && $numValue < 30) {
            $firstDigit = (int) substr((string) $numValue, 0, 1);
            return $firstDigit === 1 ? 1 : ($firstDigit === 2 ? 2 : 3);
        }

        // Default to upper grades (3+)
        return 3;
    }

    /**
     * Returns true when student_class contains at least one assignment
     * for the selected school year.
     */
    private function hasRosterForSchoolYear(string $schoolYear): bool
    {
        $year = trim($schoolYear);
        if ($year === '') {
            return false;
        }

        if (array_key_exists($year, $this->rosterPresenceCache)) {
            return $this->rosterPresenceCache[$year];
        }

        $exists = $this->db->table('student_class')
            ->where('school_year', $year)
            ->limit(1)
            ->countAllResults() > 0;

        $this->rosterPresenceCache[$year] = $exists;

        return $exists;
    }

    private function tableHasColumn(string $table, string $column): bool
    {
        $key = $table . '.' . $column;

        if (!array_key_exists($key, $this->columnExistsCache)) {
            $this->columnExistsCache[$key] = $this->db->fieldExists($column, $table);
        }

        return $this->columnExistsCache[$key];
    }

    private function getAdjustments(string $classSectionId, string $schoolYear): array
    {
        $query = $this->adjustmentModel
            ->where('class_section_id', $classSectionId)
            ->where('adjustable', 1);

        $table = $this->adjustmentModel->getTable();

        if ($schoolYear !== '' && $this->tableHasColumn($table, 'school_year')) {
            $query->where('school_year', $schoolYear);
        }

        return $query->findAll();
    }

    private function getLatestPrepSnapshot(string $classSectionId, string $schoolYear): ?array
    {
        $query = $this->prepLogModel
            ->where('class_section_id', $classSectionId);

        $table = $this->prepLogModel->getTable();

        if ($schoolYear !== '' && $this->tableHasColumn($table, 'school_year')) {
            $query->where('school_year', $schoolYear);
        }

        return $query
            ->orderBy('created_at', 'DESC')
            ->first();
    }

    private function buildPrepLogData(
        string $classSectionId,
        string $className,
        string $schoolYear,
        array $items,
        string $createdAt
    ): array {
        $data = [
            'class_section_id' => $classSectionId,
            'class_section'    => $className,
            'prep_data'        => json_encode($items),
            'created_at'       => $createdAt,
        ];

        $table = $this->prepLogModel->getTable();

        if ($schoolYear !== '' && $this->tableHasColumn($table, 'school_year')) {
            $data['school_year'] = $schoolYear;
        }

        return $data;
    }

    private function buildInventoryAvailability(string $schoolYear, string $semester, array $allowed): array
    {
        $inventoryMap = array_fill_keys($allowed, 0);

        $hasName       = $this->tableHasColumn('inventory_items', 'name');

        $joinRows = $this->db->table('inventory_items ii')
            ->select('ic.name AS item_name, COALESCE(SUM(CASE WHEN ii.good_qty IS NOT NULL THEN ii.good_qty WHEN ii.`condition` = "good" THEN ii.quantity ELSE 0 END), 0) AS available', false)
            ->join('inventory_categories ic', 'ic.id = ii.category_id', 'left')
            ->where('ii.type', 'classroom')
            ->whereIn('ic.name', $allowed);

        $periodClauses = ['im.item_id = ii.id'];
        if ($schoolYear !== '') {
            $periodClauses[] = 'im.school_year = ' . $this->db->escape($schoolYear);
        }
        if ($semester !== '') {
            $periodClauses[] = 'im.semester = ' . $this->db->escape($semester);
        }
        if (count($periodClauses) > 1) {
            $joinRows->where(
                'EXISTS (SELECT 1 FROM inventory_movements im WHERE ' . implode(' AND ', $periodClauses) . ')',
                null,
                false
            );
        }

        $joinRows = $joinRows
            ->groupBy('ic.name')
            ->get()
            ->getResultArray();

        foreach ($joinRows as $row) {
            $name = (string) ($row['item_name'] ?? '');

            if ($name !== '' && array_key_exists($name, $inventoryMap)) {
                $inventoryMap[$name] = max(
                    $inventoryMap[$name],
                    (int) ($row['available'] ?? 0)
                );
            }
        }

        /*
         * Some older schemas store the item name directly on inventory_items.
         * Only run this fallback when that column actually exists.
         */
        if ($hasName) {
            $nameRows = $this->db->table('inventory_items ii_name')
                ->select('ii_name.name AS item_name, COALESCE(SUM(CASE WHEN ii_name.good_qty IS NOT NULL THEN ii_name.good_qty WHEN ii_name.`condition` = "good" THEN ii_name.quantity ELSE 0 END), 0) AS available', false)
                ->where('ii_name.type', 'classroom')
                ->whereIn('ii_name.name', $allowed);

            $fallbackPeriodClauses = ['im.item_id = ii_name.id'];
            if ($schoolYear !== '') {
                $fallbackPeriodClauses[] = 'im.school_year = ' . $this->db->escape($schoolYear);
            }
            if ($semester !== '') {
                $fallbackPeriodClauses[] = 'im.semester = ' . $this->db->escape($semester);
            }
            if (count($fallbackPeriodClauses) > 1) {
                $nameRows->where(
                    'EXISTS (SELECT 1 FROM inventory_movements im WHERE ' . implode(' AND ', $fallbackPeriodClauses) . ')',
                    null,
                    false
                );
            }

            $nameRows = $nameRows
                ->groupBy('ii_name.name')
                ->get()
                ->getResultArray();

            foreach ($nameRows as $row) {
                $name = (string) ($row['item_name'] ?? '');

                if ($name !== '' && array_key_exists($name, $inventoryMap)) {
                    $inventoryMap[$name] = max(
                        $inventoryMap[$name],
                        (int) ($row['available'] ?? 0)
                    );
                }
            }
        }

        return $inventoryMap;
    }
}
