<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateWithdrawalRefundInventoryFoundation extends Migration
{
    public function up(): void
    {
        $this->addSchoolYearPolicyColumns();
        $this->addInventoryCatalogColumns();
        $this->createInventoryItemYears();
        $this->extendInventoryMovements();
        $this->protectInventoryHistoryForeignKey();
        $this->createBookClassAssignments();
        $this->createStudentBookIssues();
        $this->createWithdrawalCalculations();
        $this->extendRefunds();
        $this->seedActiveYearPolicy();
        $this->seedActiveBookYears();
    }

    public function down(): void
    {
        $this->forge->dropTable('withdrawal_financial_calculations', true);
        $this->forge->dropTable('student_book_issues', true);
        $this->forge->dropTable('inventory_book_class_assignments', true);

        if ($this->db->tableExists('inventory_movements')) {
            foreach ([
                'item_year_id', 'idempotency_key', 'reversal_of_movement_id', 'status',
                'reversed_at', 'reversed_by', 'source_type', 'source_id',
            ] as $column) {
                if ($this->db->fieldExists($column, 'inventory_movements')) {
                    $this->forge->dropColumn('inventory_movements', $column);
                }
            }
        }

        $this->forge->dropTable('inventory_item_years', true);
        $this->restoreInventoryHistoryForeignKey();

        if ($this->db->tableExists('inventory_items')) {
            foreach (['author', 'is_active', 'retired_at', 'isbn_edition_key', 'sku_normalized'] as $column) {
                if ($this->db->fieldExists($column, 'inventory_items')) {
                    $this->forge->dropColumn('inventory_items', $column);
                }
            }
        }

        if ($this->db->tableExists('school_years')) {
            foreach (['total_instructional_weeks', 'annual_fee_includes_books', 'withdrawal_policy_version'] as $column) {
                if ($this->db->fieldExists($column, 'school_years')) {
                    $this->forge->dropColumn('school_years', $column);
                }
            }
        }

        if ($this->db->tableExists('refunds') && $this->db->fieldExists('withdrawal_calculation_id', 'refunds')) {
            $this->forge->dropColumn('refunds', 'withdrawal_calculation_id');
        }
    }

    private function addSchoolYearPolicyColumns(): void
    {
        if (! $this->db->tableExists('school_years')) {
            return;
        }

        $columns = [];
        if (! $this->db->fieldExists('total_instructional_weeks', 'school_years')) {
            $columns['total_instructional_weeks'] = [
                'type' => 'SMALLINT', 'constraint' => 5, 'unsigned' => true, 'null' => true,
            ];
        }
        if (! $this->db->fieldExists('annual_fee_includes_books', 'school_years')) {
            $columns['annual_fee_includes_books'] = [
                'type' => 'TINYINT', 'constraint' => 1, 'default' => 1, 'null' => false,
            ];
        }
        if (! $this->db->fieldExists('withdrawal_policy_version', 'school_years')) {
            $columns['withdrawal_policy_version'] = [
                'type' => 'VARCHAR', 'constraint' => 40, 'default' => 'studied_weeks_v1', 'null' => false,
            ];
        }
        if ($columns !== []) {
            $this->forge->addColumn('school_years', $columns);
        }
    }

    private function addInventoryCatalogColumns(): void
    {
        if (! $this->db->tableExists('inventory_items')) {
            return;
        }

        $columns = [];
        if (! $this->db->fieldExists('author', 'inventory_items')) {
            $columns['author'] = ['type' => 'VARCHAR', 'constraint' => 190, 'null' => true];
        }
        if (! $this->db->fieldExists('is_active', 'inventory_items')) {
            $columns['is_active'] = ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1, 'null' => false];
        }
        if (! $this->db->fieldExists('retired_at', 'inventory_items')) {
            $columns['retired_at'] = ['type' => 'DATETIME', 'null' => true];
        }
        if (! $this->db->fieldExists('isbn_edition_key', 'inventory_items')) {
            $columns['isbn_edition_key'] = ['type' => 'VARCHAR', 'constraint' => 190, 'null' => true];
        }
        if (! $this->db->fieldExists('sku_normalized', 'inventory_items')) {
            $columns['sku_normalized'] = ['type' => 'VARCHAR', 'constraint' => 120, 'null' => true];
        }

        if ($columns !== []) {
            $this->forge->addColumn('inventory_items', $columns);
        }
        // Existing legacy rows remain NULL until an admin reconciles/edits them,
        // so installing this migration cannot fail because of historical duplicates.
        $this->addIndexIfMissing('inventory_items', 'uq_inventory_book_isbn_edition', ['isbn_edition_key'], true);
        $this->addIndexIfMissing('inventory_items', 'uq_inventory_sku_normalized', ['sku_normalized'], true);
    }

    private function createInventoryItemYears(): void
    {
        if ($this->db->tableExists('inventory_item_years')) {
            return;
        }

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'inventory_item_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'school_year_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'school_year' => ['type' => 'VARCHAR', 'constraint' => 16, 'null' => false],
            'opening_quantity' => ['type' => 'INT', 'constraint' => 11, 'default' => 0, 'null' => false],
            'charge_price_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 0, 'null' => false],
            'currency' => ['type' => 'CHAR', 'constraint' => 3, 'default' => 'USD', 'null' => false],
            'price_confirmed' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0, 'null' => false],
            'system_closing_quantity' => ['type' => 'INT', 'constraint' => 11, 'null' => true],
            'counted_closing_quantity' => ['type' => 'INT', 'constraint' => 11, 'null' => true],
            'variance_quantity' => ['type' => 'INT', 'constraint' => 11, 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'open', 'null' => false],
            'source_item_year_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'closing_batch_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'updated_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['inventory_item_id', 'school_year'], 'uq_inventory_item_year');
        $this->forge->addKey(['school_year_id', 'status'], false, false, 'idx_inventory_item_year_status');
        $this->forge->addKey('closing_batch_id');
        $this->forge->createTable('inventory_item_years', true, ['ENGINE' => 'InnoDB']);
    }

    private function extendInventoryMovements(): void
    {
        if (! $this->db->tableExists('inventory_movements')) {
            return;
        }

        $definitions = [
            'item_year_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'idempotency_key' => ['type' => 'VARCHAR', 'constraint' => 120, 'null' => true],
            'reversal_of_movement_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'posted', 'null' => false],
            'reversed_at' => ['type' => 'DATETIME', 'null' => true],
            'reversed_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'source_type' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'source_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
        ];

        $columns = [];
        foreach ($definitions as $name => $definition) {
            if (! $this->db->fieldExists($name, 'inventory_movements')) {
                $columns[$name] = $definition;
            }
        }
        if ($columns !== []) {
            $this->forge->addColumn('inventory_movements', $columns);
        }
        $this->addIndexIfMissing('inventory_movements', 'uq_inventory_movement_idempotency', ['idempotency_key'], true);
        $this->addIndexIfMissing('inventory_movements', 'idx_inventory_movement_item_year_status', ['item_year_id', 'status']);
        $this->addIndexIfMissing('inventory_movements', 'idx_inventory_movement_source', ['source_type', 'source_id']);
    }

    private function createBookClassAssignments(): void
    {
        if ($this->db->tableExists('inventory_book_class_assignments')) {
            return;
        }

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'inventory_item_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'school_year' => ['type' => 'VARCHAR', 'constraint' => 16, 'null' => false],
            'class_number' => ['type' => 'SMALLINT', 'constraint' => 5, 'unsigned' => true, 'null' => false],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['inventory_item_id', 'school_year', 'class_number'], 'uq_book_year_class');
        $this->forge->addKey(['school_year', 'class_number'], false, false, 'idx_book_class_year');
        $this->forge->createTable('inventory_book_class_assignments', true, ['ENGINE' => 'InnoDB']);
    }

    private function protectInventoryHistoryForeignKey(): void
    {
        if (! $this->db->tableExists('inventory_movements') || ! $this->db->tableExists('inventory_items')) {
            return;
        }
        try {
            $rows = $this->db->query(
                "SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
                  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inventory_movements'
                    AND COLUMN_NAME = 'item_id' AND REFERENCED_TABLE_NAME = 'inventory_items'"
            )->getResultArray();
            foreach ($rows as $row) {
                $name = str_replace('`', '', (string) ($row['CONSTRAINT_NAME'] ?? ''));
                if ($name !== '') {
                    $this->db->query('ALTER TABLE inventory_movements DROP FOREIGN KEY `' . $name . '`');
                }
            }
            $this->db->query(
                'ALTER TABLE inventory_movements ADD CONSTRAINT fk_inventory_movements_item_restrict '
                . 'FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE RESTRICT ON UPDATE CASCADE'
            );
        } catch (\Throwable $e) {
            log_message('warning', 'Unable to replace inventory movement cascade with RESTRICT: ' . $e->getMessage());
        }
    }

    private function restoreInventoryHistoryForeignKey(): void
    {
        if (! $this->db->tableExists('inventory_movements') || ! $this->db->tableExists('inventory_items')) {
            return;
        }
        try {
            $rows = $this->db->query(
                "SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
                  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inventory_movements'
                    AND COLUMN_NAME = 'item_id' AND REFERENCED_TABLE_NAME = 'inventory_items'"
            )->getResultArray();
            foreach ($rows as $row) {
                $name = str_replace('`', '', (string) ($row['CONSTRAINT_NAME'] ?? ''));
                if ($name !== '') {
                    $this->db->query('ALTER TABLE inventory_movements DROP FOREIGN KEY `' . $name . '`');
                }
            }
            $this->db->query(
                'ALTER TABLE inventory_movements ADD CONSTRAINT fk_inventory_movements_item_cascade '
                . 'FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE ON UPDATE CASCADE'
            );
        } catch (\Throwable $e) {
            log_message('warning', 'Unable to restore inventory movement cascade during rollback: ' . $e->getMessage());
        }
    }

    private function createStudentBookIssues(): void
    {
        if ($this->db->tableExists('student_book_issues')) {
            return;
        }

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'student_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'enrollment_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'parent_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'inventory_item_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'inventory_item_year_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'school_year' => ['type' => 'VARCHAR', 'constraint' => 16, 'null' => false],
            'class_section_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'quantity' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 1, 'null' => false],
            'unit_charge_price_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'total_charge_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'distribution_movement_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'idempotency_key' => ['type' => 'VARCHAR', 'constraint' => 160, 'null' => false],
            'status' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'issued', 'null' => false],
            'issued_at' => ['type' => 'DATETIME', 'null' => false],
            'issued_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'reversed_at' => ['type' => 'DATETIME', 'null' => true],
            'reversed_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'reversal_reason' => ['type' => 'TEXT', 'null' => true],
            'reversal_quantity' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'reversal_movement_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('idempotency_key', 'uq_student_book_issue_key');
        $this->forge->addKey(['student_id', 'school_year', 'status'], false, false, 'idx_student_book_issue_student');
        $this->forge->addKey(['inventory_item_year_id', 'status'], false, false, 'idx_student_book_issue_item_year');
        $this->forge->addKey('distribution_movement_id');
        $this->forge->createTable('student_book_issues', true, ['ENGINE' => 'InnoDB']);
    }

    private function createWithdrawalCalculations(): void
    {
        if ($this->db->tableExists('withdrawal_financial_calculations')) {
            return;
        }

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'version' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 1, 'null' => false],
            'enrollment_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'student_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'parent_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'invoice_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'school_year' => ['type' => 'VARCHAR', 'constraint' => 16, 'null' => false],
            'policy_version' => ['type' => 'VARCHAR', 'constraint' => 40, 'null' => false],
            'annual_fee_includes_books' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1, 'null' => false],
            'school_year_start_date' => ['type' => 'DATE', 'null' => false],
            'enrollment_date' => ['type' => 'DATE', 'null' => false],
            'withdrawal_request_date' => ['type' => 'DATE', 'null' => false],
            'total_instructional_weeks' => ['type' => 'SMALLINT', 'constraint' => 5, 'unsigned' => true, 'null' => false],
            'total_chargeable_days' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'studied_calendar_days' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'studied_weeks' => ['type' => 'SMALLINT', 'constraint' => 5, 'unsigned' => true, 'null' => false],
            'annual_fee_allocation_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'issued_book_charge_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'annual_instruction_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'earned_tuition_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'other_charge_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 0, 'null' => false],
            'retained_charge_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'original_invoice_charge_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 0, 'null' => false],
            'invoice_adjustment_cents' => ['type' => 'INT', 'constraint' => 11, 'default' => 0, 'null' => false],
            'adjusted_invoice_charge_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 0, 'null' => false],
            'valid_payment_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 0, 'null' => false],
            'completed_payout_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 0, 'null' => false],
            'open_reservation_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 0, 'null' => false],
            'refundable_credit_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 0, 'null' => false],
            'new_refund_request_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 0, 'null' => false],
            'balance_due_cents' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'default' => 0, 'null' => false],
            'book_evidence_json' => ['type' => 'LONGTEXT', 'null' => true],
            'explanation_json' => ['type' => 'LONGTEXT', 'null' => false],
            'calculation_hash' => ['type' => 'CHAR', 'constraint' => 64, 'null' => false],
            'active_posted_key' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'books_discount_eligible' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0, 'null' => false],
            'status' => ['type' => 'VARCHAR', 'constraint' => 24, 'default' => 'preview', 'null' => false],
            'superseded_by_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'override_reason' => ['type' => 'TEXT', 'null' => true],
            'overridden_at' => ['type' => 'DATETIME', 'null' => true],
            'overridden_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'calculated_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'posted_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'calculated_at' => ['type' => 'DATETIME', 'null' => false],
            'posted_at' => ['type' => 'DATETIME', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['enrollment_id', 'version'], 'uq_withdrawal_calc_version');
        $this->forge->addUniqueKey('active_posted_key', 'uq_withdrawal_calc_active_posted');
        $this->forge->addKey('calculation_hash');
        $this->forge->addKey(['enrollment_id', 'status'], false, false, 'idx_withdrawal_calc_status');
        $this->forge->addKey(['invoice_id', 'status'], false, false, 'idx_withdrawal_calc_invoice');
        $this->forge->createTable('withdrawal_financial_calculations', true, ['ENGINE' => 'InnoDB']);
    }

    private function extendRefunds(): void
    {
        if (! $this->db->tableExists('refunds') || $this->db->fieldExists('withdrawal_calculation_id', 'refunds')) {
            return;
        }

        $this->forge->addColumn('refunds', [
            'withdrawal_calculation_id' => [
                'type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true, 'after' => 'invoice_id',
            ],
        ]);
        $this->addIndexIfMissing('refunds', 'idx_refunds_withdrawal_calculation', ['withdrawal_calculation_id']);
    }

    private function seedActiveYearPolicy(): void
    {
        if (! $this->db->tableExists('school_years') || ! $this->db->tableExists('configuration')) {
            return;
        }

        $row = $this->db->table('configuration')
            ->select('config_value')
            ->where('config_key', 'total_instructional_weeks')
            ->orderBy('id', 'DESC')
            ->get(1)
            ->getRowArray();
        $weeks = filter_var($row['config_value'] ?? null, FILTER_VALIDATE_INT);
        if ($weeks === false || $weeks <= 0) {
            return;
        }

        $this->db->table('school_years')
            ->where('status', 'active')
            ->where('total_instructional_weeks IS NULL', null, false)
            ->update([
                'total_instructional_weeks' => $weeks,
                'annual_fee_includes_books' => 1,
                'withdrawal_policy_version' => 'studied_weeks_v1',
            ]);
    }

    private function seedActiveBookYears(): void
    {
        if (! $this->db->tableExists('inventory_item_years') || ! $this->db->tableExists('inventory_items')) {
            return;
        }

        $year = $this->db->table('school_years')
            ->select('id, name')
            ->where('status', 'active')
            ->orderBy('id', 'DESC')
            ->get(1)
            ->getRowArray();
        if ($year === null || trim((string) ($year['name'] ?? '')) === '') {
            return;
        }

        $books = $this->db->table('inventory_items')
            ->select('id, quantity')
            ->where('type', 'book')
            ->get()
            ->getResultArray();
        $now = date('Y-m-d H:i:s');
        foreach ($books as $book) {
            $exists = $this->db->table('inventory_item_years')
                ->where('inventory_item_id', (int) $book['id'])
                ->where('school_year', (string) $year['name'])
                ->countAllResults();
            if ($exists > 0) {
                continue;
            }
            $movementRow = $this->db->table('inventory_movements')
                ->selectSum('qty_change', 'year_movement_total')
                ->where('item_id', (int) $book['id'])
                ->where('school_year', (string) $year['name'])
                ->get()->getRowArray();
            // inventory_items.quantity is the legacy current physical balance.
            // Reconstruct the opening so linking existing movements to this
            // item-year does not count those movements a second time.
            $openingQuantity = (int) ($book['quantity'] ?? 0) - (int) ($movementRow['year_movement_total'] ?? 0);
            $this->db->table('inventory_item_years')->insert([
                'inventory_item_id' => (int) $book['id'],
                'school_year_id' => (int) $year['id'],
                'school_year' => (string) $year['name'],
                'opening_quantity' => $openingQuantity,
                'charge_price_cents' => 0,
                'price_confirmed' => 0,
                'status' => 'open',
                'created_at' => $now,
                'updated_at' => $now,
            ]);
            $itemYearId = (int) $this->db->insertID();
            $this->db->table('inventory_movements')
                ->where('item_id', (int) $book['id'])
                ->where('school_year', (string) $year['name'])
                ->where('item_year_id', null)
                ->update(['item_year_id' => $itemYearId, 'updated_at' => $now]);
        }
    }

    /** @param list<string> $columns */
    private function addIndexIfMissing(string $table, string $index, array $columns, bool $unique = false): void
    {
        try {
            $exists = $this->db->query(
                'SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ? LIMIT 1',
                [$table, $index]
            )->getRowArray();
            if ($exists !== null) {
                return;
            }
            $columnSql = implode(', ', array_map(static fn (string $column): string => '`' . str_replace('`', '', $column) . '`', $columns));
            $this->db->query(sprintf(
                'CREATE %s INDEX `%s` ON `%s` (%s)',
                $unique ? 'UNIQUE' : '',
                str_replace('`', '', $index),
                str_replace('`', '', $table),
                $columnSql
            ));
        } catch (\Throwable $e) {
            log_message('warning', 'Unable to create inventory index {index}: {message}', [
                'index' => $index,
                'message' => $e->getMessage(),
            ]);
        }
    }
}