From b51d33cb373e591d16892bde492616655ac9ec51 Mon Sep 17 00:00:00 2001
From: UMTS at Teleco <crt@teleco.ch>
Date: Sat, 13 Dec 2025 02:59:39 +0100
Subject: committing to insanit

---
 backend/database/dev/beepzone-schema-dump.sql | 2081 +++++++++++++++++++++++++
 1 file changed, 2081 insertions(+)
 create mode 100644 backend/database/dev/beepzone-schema-dump.sql

(limited to 'backend/database/dev/beepzone-schema-dump.sql')

diff --git a/backend/database/dev/beepzone-schema-dump.sql b/backend/database/dev/beepzone-schema-dump.sql
new file mode 100644
index 0000000..3acb5e5
--- /dev/null
+++ b/backend/database/dev/beepzone-schema-dump.sql
@@ -0,0 +1,2081 @@
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `asset_change_log` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `table_name` varchar(50) NOT NULL,
+  `action` enum('INSERT','UPDATE','DELETE') NOT NULL,
+  `record_id` int(11) NOT NULL,
+  `changed_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Only fields that actually changed' CHECK (json_valid(`changed_fields`)),
+  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
+  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
+  `changed_at` timestamp NULL DEFAULT current_timestamp(),
+  `changed_by_id` int(11) DEFAULT NULL,
+  `changed_by_username` varchar(100) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `idx_table_action` (`table_name`,`action`),
+  KEY `idx_timestamp` (`changed_at`),
+  KEY `idx_record` (`record_id`),
+  KEY `idx_user` (`changed_by_id`),
+  CONSTRAINT `asset_change_log_ibfk_1` FOREIGN KEY (`changed_by_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `assets` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `asset_tag` varchar(200) DEFAULT NULL,
+  `asset_numeric_id` int(11) NOT NULL CHECK (`asset_numeric_id` between 10000000 and 99999999),
+  `asset_type` enum('N','B','L','C') NOT NULL,
+  `name` varchar(255) DEFAULT NULL,
+  `category_id` int(11) DEFAULT NULL,
+  `manufacturer` varchar(200) DEFAULT NULL,
+  `model` varchar(200) DEFAULT NULL,
+  `serial_number` varchar(200) DEFAULT NULL,
+  `zone_id` int(11) DEFAULT NULL,
+  `zone_plus` enum('Floating Local','Floating Global','Clarify') DEFAULT NULL,
+  `zone_note` text DEFAULT NULL,
+  `status` enum('Good','Attention','Faulty','Missing','Retired','In Repair','In Transit','Expired','Unmanaged') DEFAULT 'Good',
+  `last_audit` date DEFAULT NULL,
+  `last_audit_status` varchar(100) DEFAULT NULL,
+  `price` decimal(12,2) DEFAULT NULL CHECK (`price` is null or `price` >= 0),
+  `purchase_date` date DEFAULT NULL,
+  `warranty_until` date DEFAULT NULL,
+  `expiry_date` date DEFAULT NULL,
+  `quantity_available` int(11) DEFAULT NULL,
+  `quantity_total` int(11) DEFAULT NULL,
+  `quantity_used` int(11) DEFAULT 0,
+  `supplier_id` int(11) DEFAULT NULL,
+  `lendable` tinyint(1) DEFAULT 0,
+  `minimum_role_for_lending` int(11) DEFAULT 1 CHECK (`minimum_role_for_lending` >= 1 and `minimum_role_for_lending` <= 100),
+  `lending_status` enum('Available','Deployed','Borrowed','Overdue','Illegally Handed Out','Stolen') DEFAULT NULL,
+  `current_borrower_id` int(11) DEFAULT NULL,
+  `due_date` date DEFAULT NULL,
+  `previous_borrower_id` int(11) DEFAULT NULL,
+  `audit_task_id` int(11) DEFAULT NULL,
+  `label_template_id` int(11) DEFAULT NULL,
+  `no_scan` enum('Yes','Ask','No') DEFAULT 'No',
+  `notes` text DEFAULT NULL,
+  `additional_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`additional_fields`)),
+  `file_attachment` mediumblob DEFAULT NULL,
+  `created_date` timestamp NULL DEFAULT current_timestamp(),
+  `created_by` int(11) DEFAULT NULL,
+  `last_modified_date` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `last_modified_by` int(11) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `asset_numeric_id` (`asset_numeric_id`),
+  UNIQUE KEY `asset_tag` (`asset_tag`),
+  KEY `supplier_id` (`supplier_id`),
+  KEY `current_borrower_id` (`current_borrower_id`),
+  KEY `previous_borrower_id` (`previous_borrower_id`),
+  KEY `audit_task_id` (`audit_task_id`),
+  KEY `created_by` (`created_by`),
+  KEY `last_modified_by` (`last_modified_by`),
+  KEY `idx_asset_tag` (`asset_tag`),
+  KEY `idx_asset_numeric` (`asset_numeric_id`),
+  KEY `idx_type` (`asset_type`),
+  KEY `idx_status` (`status`),
+  KEY `idx_zone` (`zone_id`),
+  KEY `idx_category` (`category_id`),
+  KEY `idx_lendable` (`lendable`),
+  KEY `idx_lending_status` (`lending_status`),
+  KEY `idx_label_template` (`label_template_id`),
+  CONSTRAINT `assets_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
+  CONSTRAINT `assets_ibfk_2` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`),
+  CONSTRAINT `assets_ibfk_3` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `assets_ibfk_4` FOREIGN KEY (`current_borrower_id`) REFERENCES `borrowers` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `assets_ibfk_5` FOREIGN KEY (`previous_borrower_id`) REFERENCES `borrowers` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `assets_ibfk_6` FOREIGN KEY (`audit_task_id`) REFERENCES `audit_tasks` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `assets_ibfk_7` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `assets_ibfk_8` FOREIGN KEY (`last_modified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `fk_asset_label_template` FOREIGN KEY (`label_template_id`) REFERENCES `label_templates` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER assets_before_insert_meta
+BEFORE INSERT ON assets
+FOR EACH ROW
+BEGIN
+    IF NEW.created_by IS NULL AND @current_user_id IS NOT NULL THEN
+        SET NEW.created_by = @current_user_id;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER validate_zone_plus_insert
+BEFORE INSERT ON assets
+FOR EACH ROW
+BEGIN
+    IF NEW.zone_plus = 'Clarify' AND (NEW.zone_note IS NULL OR NEW.zone_note = '') THEN
+        SIGNAL SQLSTATE '45000'
+        SET MESSAGE_TEXT = 'zone_note is required when zone_plus is set to Clarify';
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER assets_after_insert_log
+AFTER INSERT ON assets
+FOR EACH ROW
+BEGIN
+    DECLARE username VARCHAR(100);
+    DECLARE set_fields_array JSON;
+    DECLARE new_vals JSON;
+    
+    IF @current_user_id IS NOT NULL THEN
+        SELECT users.username INTO username FROM users WHERE id = @current_user_id;
+    END IF;
+    
+    -- Build JSON objects only with non-NULL fields
+    SET set_fields_array = JSON_ARRAY();
+    SET new_vals = JSON_OBJECT();
+    
+    -- Always log these core fields
+    IF NEW.asset_tag IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'asset_tag');
+        SET new_vals = JSON_SET(new_vals, '$.asset_tag', NEW.asset_tag);
+    END IF;
+    
+    IF NEW.asset_numeric_id IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'asset_numeric_id');
+        SET new_vals = JSON_SET(new_vals, '$.asset_numeric_id', NEW.asset_numeric_id);
+    END IF;
+    
+    IF NEW.asset_type IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'asset_type');
+        SET new_vals = JSON_SET(new_vals, '$.asset_type', NEW.asset_type);
+    END IF;
+    
+    IF NEW.name IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'name');
+        SET new_vals = JSON_SET(new_vals, '$.name', NEW.name);
+    END IF;
+    
+    IF NEW.category_id IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'category_id');
+        SET new_vals = JSON_SET(new_vals, '$.category_id', NEW.category_id);
+    END IF;
+    
+    IF NEW.manufacturer IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'manufacturer');
+        SET new_vals = JSON_SET(new_vals, '$.manufacturer', NEW.manufacturer);
+    END IF;
+    
+    IF NEW.model IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'model');
+        SET new_vals = JSON_SET(new_vals, '$.model', NEW.model);
+    END IF;
+    
+    IF NEW.serial_number IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'serial_number');
+        SET new_vals = JSON_SET(new_vals, '$.serial_number', NEW.serial_number);
+    END IF;
+    
+    IF NEW.zone_id IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'zone_id');
+        SET new_vals = JSON_SET(new_vals, '$.zone_id', NEW.zone_id);
+    END IF;
+    
+    IF NEW.zone_plus IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'zone_plus');
+        SET new_vals = JSON_SET(new_vals, '$.zone_plus', NEW.zone_plus);
+    END IF;
+    
+    IF NEW.zone_note IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'zone_note');
+        SET new_vals = JSON_SET(new_vals, '$.zone_note', NEW.zone_note);
+    END IF;
+    
+    IF NEW.status IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'status');
+        SET new_vals = JSON_SET(new_vals, '$.status', NEW.status);
+    END IF;
+    
+    IF NEW.last_audit IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'last_audit');
+        SET new_vals = JSON_SET(new_vals, '$.last_audit', NEW.last_audit);
+    END IF;
+    
+    IF NEW.last_audit_status IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'last_audit_status');
+        SET new_vals = JSON_SET(new_vals, '$.last_audit_status', NEW.last_audit_status);
+    END IF;
+    
+    IF NEW.price IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'price');
+        SET new_vals = JSON_SET(new_vals, '$.price', NEW.price);
+    END IF;
+    
+    IF NEW.purchase_date IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'purchase_date');
+        SET new_vals = JSON_SET(new_vals, '$.purchase_date', NEW.purchase_date);
+    END IF;
+    
+    IF NEW.warranty_until IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'warranty_until');
+        SET new_vals = JSON_SET(new_vals, '$.warranty_until', NEW.warranty_until);
+    END IF;
+    
+    IF NEW.expiry_date IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'expiry_date');
+        SET new_vals = JSON_SET(new_vals, '$.expiry_date', NEW.expiry_date);
+    END IF;
+    
+    IF NEW.quantity_available IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'quantity_available');
+        SET new_vals = JSON_SET(new_vals, '$.quantity_available', NEW.quantity_available);
+    END IF;
+    
+    IF NEW.quantity_total IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'quantity_total');
+        SET new_vals = JSON_SET(new_vals, '$.quantity_total', NEW.quantity_total);
+    END IF;
+    
+    IF NEW.quantity_used IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'quantity_used');
+        SET new_vals = JSON_SET(new_vals, '$.quantity_used', NEW.quantity_used);
+    END IF;
+    
+    IF NEW.supplier_id IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'supplier_id');
+        SET new_vals = JSON_SET(new_vals, '$.supplier_id', NEW.supplier_id);
+    END IF;
+    
+    IF NEW.lendable IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'lendable');
+        SET new_vals = JSON_SET(new_vals, '$.lendable', NEW.lendable);
+    END IF;
+    
+    IF NEW.minimum_role_for_lending IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'minimum_role_for_lending');
+        SET new_vals = JSON_SET(new_vals, '$.minimum_role_for_lending', NEW.minimum_role_for_lending);
+    END IF;
+    
+    IF NEW.lending_status IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'lending_status');
+        SET new_vals = JSON_SET(new_vals, '$.lending_status', NEW.lending_status);
+    END IF;
+    
+    IF NEW.current_borrower_id IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'current_borrower_id');
+        SET new_vals = JSON_SET(new_vals, '$.current_borrower_id', NEW.current_borrower_id);
+    END IF;
+    
+    IF NEW.due_date IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'due_date');
+        SET new_vals = JSON_SET(new_vals, '$.due_date', NEW.due_date);
+    END IF;
+    
+    IF NEW.previous_borrower_id IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'previous_borrower_id');
+        SET new_vals = JSON_SET(new_vals, '$.previous_borrower_id', NEW.previous_borrower_id);
+    END IF;
+    
+    IF NEW.audit_task_id IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'audit_task_id');
+        SET new_vals = JSON_SET(new_vals, '$.audit_task_id', NEW.audit_task_id);
+    END IF;
+    
+    IF NEW.no_scan IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'no_scan');
+        SET new_vals = JSON_SET(new_vals, '$.no_scan', NEW.no_scan);
+    END IF;
+    
+    IF NEW.notes IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'notes');
+        SET new_vals = JSON_SET(new_vals, '$.notes', NEW.notes);
+    END IF;
+    
+    IF NEW.additional_fields IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'additional_fields');
+        SET new_vals = JSON_SET(new_vals, '$.additional_fields', NEW.additional_fields);
+    END IF;
+    
+    IF NEW.created_by IS NOT NULL THEN
+        SET set_fields_array = JSON_ARRAY_APPEND(set_fields_array, '$', 'created_by');
+        SET new_vals = JSON_SET(new_vals, '$.created_by', NEW.created_by);
+    END IF;
+    
+    -- Log the INSERT with only the fields that were set
+    INSERT INTO asset_change_log (
+        table_name, action, record_id, changed_fields, new_values,
+        changed_by_id, changed_by_username
+    )
+    VALUES (
+        'assets',
+        'INSERT',
+        NEW.id,
+        set_fields_array,
+        new_vals,
+        @current_user_id,
+        username
+    );
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER assets_before_update_meta
+BEFORE UPDATE ON assets
+FOR EACH ROW
+BEGIN
+    SET NEW.last_modified_date = NOW();
+    IF @current_user_id IS NOT NULL THEN
+        SET NEW.last_modified_by = @current_user_id;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER prevent_lend_non_lendable_assets
+BEFORE UPDATE ON assets
+FOR EACH ROW
+BEGIN
+    -- Check if trying to set lending_status to any borrowed state on a non-lendable asset
+    IF (NEW.lendable = FALSE OR NEW.lendable IS NULL) AND 
+       NEW.lending_status IN ('Borrowed', 'Deployed', 'Overdue') AND
+       (OLD.lending_status NOT IN ('Borrowed', 'Deployed', 'Overdue') OR OLD.lending_status IS NULL) THEN
+        SIGNAL SQLSTATE '45000'
+        SET MESSAGE_TEXT = 'Cannot lend asset that is marked as non-lendable. Set lendable=TRUE first.';
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER validate_zone_plus_update
+BEFORE UPDATE ON assets
+FOR EACH ROW
+BEGIN
+    IF NEW.zone_plus = 'Clarify' AND (NEW.zone_note IS NULL OR NEW.zone_note = '') THEN
+        SIGNAL SQLSTATE '45000'
+        SET MESSAGE_TEXT = 'zone_note is required when zone_plus is set to Clarify';
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER assets_after_update_log
+AFTER UPDATE ON assets
+FOR EACH ROW
+BEGIN
+    DECLARE username VARCHAR(100);
+    DECLARE changed_fields_array JSON;
+    DECLARE old_vals JSON;
+    DECLARE new_vals JSON;
+    
+    IF @current_user_id IS NOT NULL THEN
+        SELECT users.username INTO username FROM users WHERE id = @current_user_id;
+    END IF;
+    
+    -- Build JSON objects only with changed fields
+    SET changed_fields_array = JSON_ARRAY();
+    SET old_vals = JSON_OBJECT();
+    SET new_vals = JSON_OBJECT();
+    
+    IF OLD.asset_tag <=> NEW.asset_tag IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'asset_tag');
+        SET old_vals = JSON_SET(old_vals, '$.asset_tag', OLD.asset_tag);
+        SET new_vals = JSON_SET(new_vals, '$.asset_tag', NEW.asset_tag);
+    END IF;
+    
+    IF OLD.asset_numeric_id <=> NEW.asset_numeric_id IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'asset_numeric_id');
+        SET old_vals = JSON_SET(old_vals, '$.asset_numeric_id', OLD.asset_numeric_id);
+        SET new_vals = JSON_SET(new_vals, '$.asset_numeric_id', NEW.asset_numeric_id);
+    END IF;
+    
+    IF OLD.asset_type <=> NEW.asset_type IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'asset_type');
+        SET old_vals = JSON_SET(old_vals, '$.asset_type', OLD.asset_type);
+        SET new_vals = JSON_SET(new_vals, '$.asset_type', NEW.asset_type);
+    END IF;
+    
+    IF OLD.name <=> NEW.name IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'name');
+        SET old_vals = JSON_SET(old_vals, '$.name', OLD.name);
+        SET new_vals = JSON_SET(new_vals, '$.name', NEW.name);
+    END IF;
+    
+    IF OLD.category_id <=> NEW.category_id IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'category_id');
+        SET old_vals = JSON_SET(old_vals, '$.category_id', OLD.category_id);
+        SET new_vals = JSON_SET(new_vals, '$.category_id', NEW.category_id);
+    END IF;
+    
+    IF OLD.manufacturer <=> NEW.manufacturer IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'manufacturer');
+        SET old_vals = JSON_SET(old_vals, '$.manufacturer', OLD.manufacturer);
+        SET new_vals = JSON_SET(new_vals, '$.manufacturer', NEW.manufacturer);
+    END IF;
+    
+    IF OLD.model <=> NEW.model IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'model');
+        SET old_vals = JSON_SET(old_vals, '$.model', OLD.model);
+        SET new_vals = JSON_SET(new_vals, '$.model', NEW.model);
+    END IF;
+    
+    IF OLD.serial_number <=> NEW.serial_number IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'serial_number');
+        SET old_vals = JSON_SET(old_vals, '$.serial_number', OLD.serial_number);
+        SET new_vals = JSON_SET(new_vals, '$.serial_number', NEW.serial_number);
+    END IF;
+    
+    IF OLD.zone_id <=> NEW.zone_id IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'zone_id');
+        SET old_vals = JSON_SET(old_vals, '$.zone_id', OLD.zone_id);
+        SET new_vals = JSON_SET(new_vals, '$.zone_id', NEW.zone_id);
+    END IF;
+    
+    IF OLD.zone_plus <=> NEW.zone_plus IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'zone_plus');
+        SET old_vals = JSON_SET(old_vals, '$.zone_plus', OLD.zone_plus);
+        SET new_vals = JSON_SET(new_vals, '$.zone_plus', NEW.zone_plus);
+    END IF;
+    
+    IF OLD.zone_note <=> NEW.zone_note IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'zone_note');
+        SET old_vals = JSON_SET(old_vals, '$.zone_note', OLD.zone_note);
+        SET new_vals = JSON_SET(new_vals, '$.zone_note', NEW.zone_note);
+    END IF;
+    
+    IF OLD.status <=> NEW.status IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'status');
+        SET old_vals = JSON_SET(old_vals, '$.status', OLD.status);
+        SET new_vals = JSON_SET(new_vals, '$.status', NEW.status);
+    END IF;
+    
+    IF OLD.last_audit <=> NEW.last_audit IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'last_audit');
+        SET old_vals = JSON_SET(old_vals, '$.last_audit', OLD.last_audit);
+        SET new_vals = JSON_SET(new_vals, '$.last_audit', NEW.last_audit);
+    END IF;
+    
+    IF OLD.last_audit_status <=> NEW.last_audit_status IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'last_audit_status');
+        SET old_vals = JSON_SET(old_vals, '$.last_audit_status', OLD.last_audit_status);
+        SET new_vals = JSON_SET(new_vals, '$.last_audit_status', NEW.last_audit_status);
+    END IF;
+    
+    IF OLD.price <=> NEW.price IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'price');
+        SET old_vals = JSON_SET(old_vals, '$.price', OLD.price);
+        SET new_vals = JSON_SET(new_vals, '$.price', NEW.price);
+    END IF;
+    
+    IF OLD.purchase_date <=> NEW.purchase_date IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'purchase_date');
+        SET old_vals = JSON_SET(old_vals, '$.purchase_date', OLD.purchase_date);
+        SET new_vals = JSON_SET(new_vals, '$.purchase_date', NEW.purchase_date);
+    END IF;
+    
+    IF OLD.warranty_until <=> NEW.warranty_until IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'warranty_until');
+        SET old_vals = JSON_SET(old_vals, '$.warranty_until', OLD.warranty_until);
+        SET new_vals = JSON_SET(new_vals, '$.warranty_until', NEW.warranty_until);
+    END IF;
+    
+    IF OLD.expiry_date <=> NEW.expiry_date IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'expiry_date');
+        SET old_vals = JSON_SET(old_vals, '$.expiry_date', OLD.expiry_date);
+        SET new_vals = JSON_SET(new_vals, '$.expiry_date', NEW.expiry_date);
+    END IF;
+    
+    IF OLD.quantity_available <=> NEW.quantity_available IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'quantity_available');
+        SET old_vals = JSON_SET(old_vals, '$.quantity_available', OLD.quantity_available);
+        SET new_vals = JSON_SET(new_vals, '$.quantity_available', NEW.quantity_available);
+    END IF;
+    
+    IF OLD.quantity_total <=> NEW.quantity_total IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'quantity_total');
+        SET old_vals = JSON_SET(old_vals, '$.quantity_total', OLD.quantity_total);
+        SET new_vals = JSON_SET(new_vals, '$.quantity_total', NEW.quantity_total);
+    END IF;
+    
+    IF OLD.quantity_used <=> NEW.quantity_used IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'quantity_used');
+        SET old_vals = JSON_SET(old_vals, '$.quantity_used', OLD.quantity_used);
+        SET new_vals = JSON_SET(new_vals, '$.quantity_used', NEW.quantity_used);
+    END IF;
+    
+    IF OLD.supplier_id <=> NEW.supplier_id IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'supplier_id');
+        SET old_vals = JSON_SET(old_vals, '$.supplier_id', OLD.supplier_id);
+        SET new_vals = JSON_SET(new_vals, '$.supplier_id', NEW.supplier_id);
+    END IF;
+    
+    IF OLD.lendable <=> NEW.lendable IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'lendable');
+        SET old_vals = JSON_SET(old_vals, '$.lendable', OLD.lendable);
+        SET new_vals = JSON_SET(new_vals, '$.lendable', NEW.lendable);
+    END IF;
+    
+    IF OLD.minimum_role_for_lending <=> NEW.minimum_role_for_lending IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'minimum_role_for_lending');
+        SET old_vals = JSON_SET(old_vals, '$.minimum_role_for_lending', OLD.minimum_role_for_lending);
+        SET new_vals = JSON_SET(new_vals, '$.minimum_role_for_lending', NEW.minimum_role_for_lending);
+    END IF;
+    
+    IF OLD.lending_status <=> NEW.lending_status IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'lending_status');
+        SET old_vals = JSON_SET(old_vals, '$.lending_status', OLD.lending_status);
+        SET new_vals = JSON_SET(new_vals, '$.lending_status', NEW.lending_status);
+    END IF;
+    
+    IF OLD.current_borrower_id <=> NEW.current_borrower_id IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'current_borrower_id');
+        SET old_vals = JSON_SET(old_vals, '$.current_borrower_id', OLD.current_borrower_id);
+        SET new_vals = JSON_SET(new_vals, '$.current_borrower_id', NEW.current_borrower_id);
+    END IF;
+    
+    IF OLD.due_date <=> NEW.due_date IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'due_date');
+        SET old_vals = JSON_SET(old_vals, '$.due_date', OLD.due_date);
+        SET new_vals = JSON_SET(new_vals, '$.due_date', NEW.due_date);
+    END IF;
+    
+    IF OLD.previous_borrower_id <=> NEW.previous_borrower_id IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'previous_borrower_id');
+        SET old_vals = JSON_SET(old_vals, '$.previous_borrower_id', OLD.previous_borrower_id);
+        SET new_vals = JSON_SET(new_vals, '$.previous_borrower_id', NEW.previous_borrower_id);
+    END IF;
+    
+    IF OLD.audit_task_id <=> NEW.audit_task_id IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'audit_task_id');
+        SET old_vals = JSON_SET(old_vals, '$.audit_task_id', OLD.audit_task_id);
+        SET new_vals = JSON_SET(new_vals, '$.audit_task_id', NEW.audit_task_id);
+    END IF;
+    
+    IF OLD.no_scan <=> NEW.no_scan IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'no_scan');
+        SET old_vals = JSON_SET(old_vals, '$.no_scan', OLD.no_scan);
+        SET new_vals = JSON_SET(new_vals, '$.no_scan', NEW.no_scan);
+    END IF;
+    
+    IF OLD.notes <=> NEW.notes IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'notes');
+        SET old_vals = JSON_SET(old_vals, '$.notes', OLD.notes);
+        SET new_vals = JSON_SET(new_vals, '$.notes', NEW.notes);
+    END IF;
+    
+    IF OLD.additional_fields <=> NEW.additional_fields IS FALSE THEN
+        SET changed_fields_array = JSON_ARRAY_APPEND(changed_fields_array, '$', 'additional_fields');
+        SET old_vals = JSON_SET(old_vals, '$.additional_fields', OLD.additional_fields);
+        SET new_vals = JSON_SET(new_vals, '$.additional_fields', NEW.additional_fields);
+    END IF;
+    
+    -- Only log if there were actual changes (excluding auto-updated fields)
+    IF JSON_LENGTH(changed_fields_array) > 0 THEN
+        INSERT INTO asset_change_log (
+            table_name, action, record_id, changed_fields, old_values, new_values,
+            changed_by_id, changed_by_username
+        )
+        VALUES (
+            'assets',
+            'UPDATE',
+            NEW.id,
+            changed_fields_array,
+            old_vals,
+            new_vals,
+            @current_user_id,
+            username
+        );
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER auto_detect_asset_issues
+AFTER UPDATE ON assets
+FOR EACH ROW
+BEGIN
+    DECLARE issue_title VARCHAR(255);
+    DECLARE issue_description TEXT;
+    DECLARE issue_severity ENUM('Critical', 'High', 'Medium', 'Low');
+    DECLARE detection_trigger_name VARCHAR(100);
+    
+    -- Check for lending_status changes to problematic states
+    IF (OLD.lending_status IS NULL OR OLD.lending_status != NEW.lending_status) 
+       AND NEW.lending_status IN ('Overdue', 'Illegally Handed Out', 'Stolen') THEN
+        
+        -- Determine issue details based on lending_status
+        CASE NEW.lending_status
+            WHEN 'Overdue' THEN
+                SET issue_title = CONCAT('Asset Overdue: ', COALESCE(NEW.name, NEW.asset_tag, CAST(NEW.asset_numeric_id AS CHAR)));
+                SET issue_description = CONCAT('Asset lending status changed to Overdue. Asset: ', NEW.asset_tag, 
+                    CASE WHEN NEW.name IS NOT NULL THEN CONCAT(' (', NEW.name, ')') ELSE '' END);
+                SET issue_severity = 'High';
+                SET detection_trigger_name = 'LENDING_OVERDUE';
+                
+            WHEN 'Illegally Handed Out' THEN
+                SET issue_title = CONCAT('Asset Illegally Handed Out: ', COALESCE(NEW.name, NEW.asset_tag, CAST(NEW.asset_numeric_id AS CHAR)));
+                SET issue_description = CONCAT('Asset lending status changed to Illegally Handed Out. Asset: ', NEW.asset_tag,
+                    CASE WHEN NEW.name IS NOT NULL THEN CONCAT(' (', NEW.name, ')') ELSE '' END);
+                SET issue_severity = 'Critical';
+                SET detection_trigger_name = 'LENDING_ILLEGAL';
+                
+            WHEN 'Stolen' THEN
+                SET issue_title = CONCAT('Asset Stolen: ', COALESCE(NEW.name, NEW.asset_tag, CAST(NEW.asset_numeric_id AS CHAR)));
+                SET issue_description = CONCAT('Asset lending status changed to Stolen (14+ days overdue). Asset: ', NEW.asset_tag,
+                    CASE WHEN NEW.name IS NOT NULL THEN CONCAT(' (', NEW.name, ')') ELSE '' END);
+                SET issue_severity = 'Critical';
+                SET detection_trigger_name = 'LENDING_STOLEN';
+        END CASE;
+        
+        -- Insert the auto-detected issue
+        INSERT INTO issue_tracker (
+            issue_type, asset_id, title, description, severity, priority, status,
+            reported_by, auto_detected, detection_trigger, created_date
+        )
+        VALUES (
+            'Asset Issue', NEW.id, issue_title, issue_description, issue_severity, 'Urgent', 'Open',
+            COALESCE(@current_user_id, 1), TRUE, detection_trigger_name, NOW()
+        );
+    END IF;
+    
+    -- Check for status changes to problematic states
+    IF OLD.status != NEW.status AND NEW.status IN ('Attention', 'Faulty', 'Missing', 'Retired', 'In Repair', 'Expired') THEN
+        
+        -- Determine issue details based on status
+        CASE NEW.status
+            WHEN 'Attention' THEN
+                SET issue_title = CONCAT('Asset Needs Attention: ', COALESCE(NEW.name, NEW.asset_tag, NEW.asset_numeric_id));
+                SET issue_description = CONCAT('Asset status changed to Attention. Asset: ', NEW.asset_numeric_id, CASE WHEN NEW.name IS NOT NULL THEN CONCAT(' (', NEW.name, ')') ELSE '' END);
+                SET issue_severity = 'Medium';
+                SET detection_trigger_name = 'STATUS_ATTENTION';
+                
+            WHEN 'Faulty' THEN
+                SET issue_title = CONCAT('Asset Faulty: ', COALESCE(NEW.name, NEW.asset_tag, NEW.asset_numeric_id));
+                SET issue_description = CONCAT('Asset status changed to Faulty. Asset: ', NEW.asset_numeric_id, CASE WHEN NEW.name IS NOT NULL THEN CONCAT(' (', NEW.name, ')') ELSE '' END);
+                SET issue_severity = 'High';
+                SET detection_trigger_name = 'STATUS_FAULTY';
+                
+            WHEN 'Missing' THEN
+                SET issue_title = CONCAT('Asset Missing: ', COALESCE(NEW.name, NEW.asset_tag, NEW.asset_numeric_id));
+                SET issue_description = CONCAT('Asset status changed to Missing. Asset: ', NEW.asset_numeric_id, CASE WHEN NEW.name IS NOT NULL THEN CONCAT(' (', NEW.name, ')') ELSE '' END);
+                SET issue_severity = 'Critical';
+                SET detection_trigger_name = 'STATUS_MISSING';
+                
+            WHEN 'Retired' THEN
+                SET issue_title = CONCAT('Asset Retired: ', COALESCE(NEW.name, NEW.asset_tag, NEW.asset_numeric_id));
+                SET issue_description = CONCAT('Asset status changed to Retired. Asset: ', NEW.asset_numeric_id, CASE WHEN NEW.name IS NOT NULL THEN CONCAT(' (', NEW.name, ')') ELSE '' END);
+                SET issue_severity = 'Low';
+                SET detection_trigger_name = 'STATUS_RETIRED';
+                
+            WHEN 'In Repair' THEN
+                SET issue_title = CONCAT('Asset In Repair: ', COALESCE(NEW.name, NEW.asset_tag, NEW.asset_numeric_id));
+                SET issue_description = CONCAT('Asset status changed to In Repair. Asset: ', NEW.asset_numeric_id, CASE WHEN NEW.name IS NOT NULL THEN CONCAT(' (', NEW.name, ')') ELSE '' END);
+                SET issue_severity = 'Medium';
+                SET detection_trigger_name = 'STATUS_IN_REPAIR';
+                
+            WHEN 'Expired' THEN
+                SET issue_title = CONCAT('Asset Expired: ', COALESCE(NEW.name, NEW.asset_tag, NEW.asset_numeric_id));
+                SET issue_description = CONCAT('Asset status changed to Expired. Asset: ', NEW.asset_numeric_id, CASE WHEN NEW.name IS NOT NULL THEN CONCAT(' (', NEW.name, ')') ELSE '' END);
+                SET issue_severity = 'Medium';
+                SET detection_trigger_name = 'STATUS_EXPIRED';
+        END CASE;
+        
+        -- Insert the auto-detected issue
+        INSERT INTO issue_tracker (
+            issue_type, asset_id, title, description, severity, priority, status,
+            reported_by, auto_detected, detection_trigger, created_date
+        )
+        VALUES (
+            'Asset Issue', NEW.id, issue_title, issue_description, issue_severity, 'Normal', 'Open',
+            COALESCE(@current_user_id, 1), TRUE, detection_trigger_name, NOW()
+        );
+    END IF;
+    
+    -- Auto-resolve issues when status becomes Good again
+    IF OLD.status != NEW.status AND NEW.status = 'Good' AND OLD.status IN ('Faulty', 'Missing', 'In Repair', 'Expired') THEN
+        UPDATE issue_tracker 
+        SET status = 'Resolved',
+            solution = 'Automatically Fixed',
+            solution_plus = CONCAT('Asset status automatically changed from ', OLD.status, ' to Good'),
+            resolved_date = NOW(),
+            resolved_by = COALESCE(@current_user_id, 1)
+        WHERE asset_id = NEW.id 
+        AND status IN ('Open', 'In Progress')
+        AND auto_detected = TRUE
+        AND detection_trigger IN ('STATUS_FAULTY', 'STATUS_MISSING', 'STATUS_IN_REPAIR', 'STATUS_EXPIRED');
+    END IF;
+    
+    -- Auto-resolve overdue/stolen/illegal issues when item is returned (lending_status becomes Available)
+    IF (OLD.lending_status IS NULL OR OLD.lending_status != NEW.lending_status) 
+       AND NEW.lending_status = 'Available' 
+       AND OLD.lending_status IN ('Overdue', 'Illegally Handed Out', 'Stolen') THEN
+        UPDATE issue_tracker 
+        SET status = 'Resolved',
+            solution = 'Items Returned',
+            solution_plus = CONCAT('Asset was returned - lending status changed from ', OLD.lending_status, ' to Available'),
+            resolved_date = NOW(),
+            resolved_by = COALESCE(@current_user_id, 1)
+        WHERE asset_id = NEW.id 
+        AND status IN ('Open', 'In Progress')
+        AND auto_detected = TRUE
+        AND detection_trigger IN ('LENDING_OVERDUE', 'LENDING_ILLEGAL', 'LENDING_STOLEN');
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER prevent_delete_borrowed_assets
+BEFORE DELETE ON assets
+FOR EACH ROW
+BEGIN
+    IF OLD.lending_status IN ('Borrowed', 'Deployed', 'Overdue') THEN
+        SIGNAL SQLSTATE '45000'
+        SET MESSAGE_TEXT = 'Cannot delete asset that is currently borrowed or deployed, maybe update to retired or unmanaged before';
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER assets_after_delete_log
+AFTER DELETE ON assets
+FOR EACH ROW
+BEGIN
+    DECLARE username VARCHAR(100);
+    DECLARE deleted_fields_array JSON;
+    DECLARE old_vals JSON;
+    
+    IF @current_user_id IS NOT NULL THEN
+        SELECT users.username INTO username FROM users WHERE id = @current_user_id;
+    END IF;
+    
+    -- Build JSON objects only with non-NULL fields (for restore capability)
+    SET deleted_fields_array = JSON_ARRAY();
+    SET old_vals = JSON_OBJECT();
+    
+    IF OLD.asset_tag IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'asset_tag');
+        SET old_vals = JSON_SET(old_vals, '$.asset_tag', OLD.asset_tag);
+    END IF;
+    
+    IF OLD.asset_numeric_id IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'asset_numeric_id');
+        SET old_vals = JSON_SET(old_vals, '$.asset_numeric_id', OLD.asset_numeric_id);
+    END IF;
+    
+    IF OLD.asset_type IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'asset_type');
+        SET old_vals = JSON_SET(old_vals, '$.asset_type', OLD.asset_type);
+    END IF;
+    
+    IF OLD.name IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'name');
+        SET old_vals = JSON_SET(old_vals, '$.name', OLD.name);
+    END IF;
+    
+    IF OLD.category_id IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'category_id');
+        SET old_vals = JSON_SET(old_vals, '$.category_id', OLD.category_id);
+    END IF;
+    
+    IF OLD.manufacturer IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'manufacturer');
+        SET old_vals = JSON_SET(old_vals, '$.manufacturer', OLD.manufacturer);
+    END IF;
+    
+    IF OLD.model IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'model');
+        SET old_vals = JSON_SET(old_vals, '$.model', OLD.model);
+    END IF;
+    
+    IF OLD.serial_number IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'serial_number');
+        SET old_vals = JSON_SET(old_vals, '$.serial_number', OLD.serial_number);
+    END IF;
+    
+    IF OLD.zone_id IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'zone_id');
+        SET old_vals = JSON_SET(old_vals, '$.zone_id', OLD.zone_id);
+    END IF;
+    
+    IF OLD.zone_plus IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'zone_plus');
+        SET old_vals = JSON_SET(old_vals, '$.zone_plus', OLD.zone_plus);
+    END IF;
+    
+    IF OLD.zone_note IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'zone_note');
+        SET old_vals = JSON_SET(old_vals, '$.zone_note', OLD.zone_note);
+    END IF;
+    
+    IF OLD.status IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'status');
+        SET old_vals = JSON_SET(old_vals, '$.status', OLD.status);
+    END IF;
+    
+    IF OLD.last_audit IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'last_audit');
+        SET old_vals = JSON_SET(old_vals, '$.last_audit', OLD.last_audit);
+    END IF;
+    
+    IF OLD.last_audit_status IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'last_audit_status');
+        SET old_vals = JSON_SET(old_vals, '$.last_audit_status', OLD.last_audit_status);
+    END IF;
+    
+    IF OLD.price IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'price');
+        SET old_vals = JSON_SET(old_vals, '$.price', OLD.price);
+    END IF;
+    
+    IF OLD.purchase_date IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'purchase_date');
+        SET old_vals = JSON_SET(old_vals, '$.purchase_date', OLD.purchase_date);
+    END IF;
+    
+    IF OLD.warranty_until IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'warranty_until');
+        SET old_vals = JSON_SET(old_vals, '$.warranty_until', OLD.warranty_until);
+    END IF;
+    
+    IF OLD.expiry_date IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'expiry_date');
+        SET old_vals = JSON_SET(old_vals, '$.expiry_date', OLD.expiry_date);
+    END IF;
+    
+    IF OLD.quantity_available IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'quantity_available');
+        SET old_vals = JSON_SET(old_vals, '$.quantity_available', OLD.quantity_available);
+    END IF;
+    
+    IF OLD.quantity_total IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'quantity_total');
+        SET old_vals = JSON_SET(old_vals, '$.quantity_total', OLD.quantity_total);
+    END IF;
+    
+    IF OLD.quantity_used IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'quantity_used');
+        SET old_vals = JSON_SET(old_vals, '$.quantity_used', OLD.quantity_used);
+    END IF;
+    
+    IF OLD.supplier_id IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'supplier_id');
+        SET old_vals = JSON_SET(old_vals, '$.supplier_id', OLD.supplier_id);
+    END IF;
+    
+    IF OLD.lendable IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'lendable');
+        SET old_vals = JSON_SET(old_vals, '$.lendable', OLD.lendable);
+    END IF;
+    
+    IF OLD.minimum_role_for_lending IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'minimum_role_for_lending');
+        SET old_vals = JSON_SET(old_vals, '$.minimum_role_for_lending', OLD.minimum_role_for_lending);
+    END IF;
+    
+    IF OLD.lending_status IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'lending_status');
+        SET old_vals = JSON_SET(old_vals, '$.lending_status', OLD.lending_status);
+    END IF;
+    
+    IF OLD.current_borrower_id IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'current_borrower_id');
+        SET old_vals = JSON_SET(old_vals, '$.current_borrower_id', OLD.current_borrower_id);
+    END IF;
+    
+    IF OLD.due_date IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'due_date');
+        SET old_vals = JSON_SET(old_vals, '$.due_date', OLD.due_date);
+    END IF;
+    
+    IF OLD.previous_borrower_id IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'previous_borrower_id');
+        SET old_vals = JSON_SET(old_vals, '$.previous_borrower_id', OLD.previous_borrower_id);
+    END IF;
+    
+    IF OLD.audit_task_id IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'audit_task_id');
+        SET old_vals = JSON_SET(old_vals, '$.audit_task_id', OLD.audit_task_id);
+    END IF;
+    
+    IF OLD.no_scan IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'no_scan');
+        SET old_vals = JSON_SET(old_vals, '$.no_scan', OLD.no_scan);
+    END IF;
+    
+    IF OLD.notes IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'notes');
+        SET old_vals = JSON_SET(old_vals, '$.notes', OLD.notes);
+    END IF;
+    
+    IF OLD.additional_fields IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'additional_fields');
+        SET old_vals = JSON_SET(old_vals, '$.additional_fields', OLD.additional_fields);
+    END IF;
+    
+    -- Always capture metadata fields for restore
+    IF OLD.created_date IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'created_date');
+        SET old_vals = JSON_SET(old_vals, '$.created_date', OLD.created_date);
+    END IF;
+    
+    IF OLD.created_by IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'created_by');
+        SET old_vals = JSON_SET(old_vals, '$.created_by', OLD.created_by);
+    END IF;
+    
+    IF OLD.last_modified_date IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'last_modified_date');
+        SET old_vals = JSON_SET(old_vals, '$.last_modified_date', OLD.last_modified_date);
+    END IF;
+    
+    IF OLD.last_modified_by IS NOT NULL THEN
+        SET deleted_fields_array = JSON_ARRAY_APPEND(deleted_fields_array, '$', 'last_modified_by');
+        SET old_vals = JSON_SET(old_vals, '$.last_modified_by', OLD.last_modified_by);
+    END IF;
+    
+    -- Log the DELETE with only non-NULL fields
+    INSERT INTO asset_change_log (
+        table_name, action, record_id, changed_fields, old_values,
+        changed_by_id, changed_by_username
+    )
+    VALUES (
+        'assets',
+        'DELETE',
+        OLD.id,
+        deleted_fields_array,
+        old_vals,
+        @current_user_id,
+        username
+    );
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `audit_tasks` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `task_name` varchar(200) NOT NULL,
+  `json_sequence` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`json_sequence`)),
+  `created_at` timestamp NULL DEFAULT current_timestamp(),
+  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `borrowers` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `name` varchar(200) NOT NULL,
+  `email` varchar(255) DEFAULT NULL,
+  `phone_number` varchar(50) DEFAULT NULL,
+  `class_name` varchar(100) DEFAULT NULL,
+  `role` varchar(100) DEFAULT NULL,
+  `notes` text DEFAULT NULL,
+  `added_by` int(11) NOT NULL,
+  `added_date` timestamp NULL DEFAULT current_timestamp(),
+  `banned` tinyint(1) DEFAULT 0,
+  `unban_fine` decimal(10,2) DEFAULT 0.00,
+  `last_unban_by` int(11) DEFAULT NULL,
+  `last_unban_date` date DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `added_by` (`added_by`),
+  KEY `last_unban_by` (`last_unban_by`),
+  KEY `idx_name` (`name`),
+  KEY `idx_banned` (`banned`),
+  CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`),
+  CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`last_unban_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER borrowers_before_insert_meta
+BEFORE INSERT ON borrowers
+FOR EACH ROW
+BEGIN
+    IF NEW.added_by IS NULL AND @current_user_id IS NOT NULL THEN
+        SET NEW.added_by = @current_user_id;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER borrowers_before_update_meta
+BEFORE UPDATE ON borrowers
+FOR EACH ROW
+BEGIN
+    IF OLD.banned = TRUE AND NEW.banned = FALSE THEN
+        IF NEW.last_unban_by IS NULL AND @current_user_id IS NOT NULL THEN
+            SET NEW.last_unban_by = @current_user_id;
+        END IF;
+        IF NEW.last_unban_date IS NULL THEN
+            SET NEW.last_unban_date = CURDATE();
+        END IF;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER auto_detect_borrower_issues
+AFTER UPDATE ON borrowers
+FOR EACH ROW
+BEGIN
+    DECLARE issue_title VARCHAR(255);
+    DECLARE issue_description TEXT;
+    
+    -- Auto-detect when borrower gets banned
+    IF OLD.banned = FALSE AND NEW.banned = TRUE THEN
+        SET issue_title = CONCAT('Borrower Banned: ', NEW.name);
+        SET issue_description = CONCAT('Borrower has been banned. Name: ', NEW.name, CASE WHEN NEW.unban_fine > 0 THEN CONCAT(', Unban Fine: $', NEW.unban_fine) ELSE '' END);
+        
+        INSERT INTO issue_tracker (
+            issue_type, borrower_id, title, description, severity, priority, status,
+            reported_by, auto_detected, detection_trigger, created_date
+        )
+        VALUES (
+            'Borrower Issue', NEW.id, issue_title, issue_description, 'High', 'Normal', 'Open',
+            COALESCE(@current_user_id, 1), TRUE, 'BORROWER_BANNED', NOW()
+        );
+    END IF;
+    
+    -- Auto-resolve when borrower gets unbanned
+    IF OLD.banned = TRUE AND NEW.banned = FALSE THEN
+        UPDATE issue_tracker 
+        SET status = 'Resolved',
+            solution = 'Items Returned',
+            solution_plus = CONCAT('Borrower unbanned on ', COALESCE(NEW.last_unban_date, CURDATE()), CASE WHEN NEW.last_unban_by IS NOT NULL THEN CONCAT(' by user ID ', NEW.last_unban_by) ELSE '' END),
+            resolved_date = NOW(),
+            resolved_by = COALESCE(@current_user_id, NEW.last_unban_by, 1)
+        WHERE borrower_id = NEW.id 
+        AND status IN ('Open', 'In Progress')
+        AND auto_detected = TRUE
+        AND detection_trigger = 'BORROWER_BANNED';
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `categories` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `category_name` varchar(200) NOT NULL,
+  `category_description` text DEFAULT NULL,
+  `parent_id` int(11) DEFAULT NULL,
+  `category_code` varchar(50) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `idx_parent` (`parent_id`),
+  KEY `idx_code` (`category_code`),
+  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `issue_tracker` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `issue_type` enum('Asset Issue','Borrower Issue','System Issue','Maintenance','Other') NOT NULL,
+  `asset_id` int(11) DEFAULT NULL,
+  `borrower_id` int(11) DEFAULT NULL,
+  `title` varchar(255) NOT NULL,
+  `description` text NOT NULL,
+  `severity` enum('Critical','High','Medium','Low') DEFAULT NULL,
+  `priority` enum('Urgent','High','Normal','Low') DEFAULT 'Normal',
+  `status` enum('Open','In Progress','Resolved','Closed','On Hold') DEFAULT 'Open',
+  `solution` enum('Fixed','Replaced','Clarify','No Action Needed','Deferred','Items Returned','Automatically Fixed') DEFAULT NULL,
+  `solution_plus` text DEFAULT NULL,
+  `replacement_asset_id` int(11) DEFAULT NULL,
+  `reported_by` int(11) NOT NULL,
+  `assigned_to` int(11) DEFAULT NULL,
+  `resolved_by` int(11) DEFAULT NULL,
+  `cost` decimal(10,2) DEFAULT NULL,
+  `created_date` datetime NOT NULL DEFAULT current_timestamp(),
+  `updated_date` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `resolved_date` datetime DEFAULT NULL,
+  `notes` text DEFAULT NULL,
+  `auto_detected` tinyint(1) DEFAULT 0,
+  `detection_trigger` varchar(100) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `replacement_asset_id` (`replacement_asset_id`),
+  KEY `reported_by` (`reported_by`),
+  KEY `assigned_to` (`assigned_to`),
+  KEY `resolved_by` (`resolved_by`),
+  KEY `idx_issue_type` (`issue_type`),
+  KEY `idx_asset` (`asset_id`),
+  KEY `idx_borrower` (`borrower_id`),
+  KEY `idx_severity` (`severity`),
+  KEY `idx_status` (`status`),
+  KEY `idx_created_date` (`created_date`),
+  KEY `idx_auto_detected` (`auto_detected`),
+  CONSTRAINT `issue_tracker_ibfk_1` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE,
+  CONSTRAINT `issue_tracker_ibfk_2` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`) ON DELETE CASCADE,
+  CONSTRAINT `issue_tracker_ibfk_3` FOREIGN KEY (`replacement_asset_id`) REFERENCES `assets` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `issue_tracker_ibfk_4` FOREIGN KEY (`reported_by`) REFERENCES `users` (`id`),
+  CONSTRAINT `issue_tracker_ibfk_5` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `issue_tracker_ibfk_6` FOREIGN KEY (`resolved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER issue_tracker_before_insert_meta
+BEFORE INSERT ON issue_tracker
+FOR EACH ROW
+BEGIN
+    IF NEW.reported_by IS NULL AND @current_user_id IS NOT NULL THEN
+        SET NEW.reported_by = @current_user_id;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER validate_issue_tracker_insert
+BEFORE INSERT ON issue_tracker
+FOR EACH ROW
+BEGIN
+    -- Clarify solution requires solution_plus
+    IF NEW.solution = 'Clarify' AND (NEW.solution_plus IS NULL OR NEW.solution_plus = '') THEN
+        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'solution_plus is required when solution is set to Clarify';
+    END IF;
+    
+    -- Replacement solution requires replacement_asset_id
+    IF NEW.solution = 'Replaced' AND NEW.replacement_asset_id IS NULL THEN
+        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'replacement_asset_id is required when solution is set to Replaced';
+    END IF;
+    
+    -- Asset Issue requires asset_id
+    IF NEW.issue_type = 'Asset Issue' AND NEW.asset_id IS NULL THEN
+        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'asset_id is required for Asset Issue type';
+    END IF;
+    
+    -- Borrower Issue requires borrower_id
+    IF NEW.issue_type = 'Borrower Issue' AND NEW.borrower_id IS NULL THEN
+        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'borrower_id is required for Borrower Issue type';
+    END IF;
+    
+    -- Auto-set resolved_date when status becomes Resolved or Closed
+    IF NEW.status IN ('Resolved', 'Closed') AND NEW.resolved_date IS NULL THEN
+        SET NEW.resolved_date = NOW();
+    END IF;
+    
+    -- Auto-set resolved_by when status becomes Resolved or Closed
+    IF NEW.status IN ('Resolved', 'Closed') AND NEW.resolved_by IS NULL AND @current_user_id IS NOT NULL THEN
+        SET NEW.resolved_by = @current_user_id;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER issue_tracker_after_insert_log
+AFTER INSERT ON issue_tracker
+FOR EACH ROW
+BEGIN
+    DECLARE set_fields JSON DEFAULT JSON_ARRAY();
+    DECLARE new_vals JSON DEFAULT JSON_OBJECT();
+    
+    -- Build JSON of non-NULL inserted fields
+    IF NEW.issue_type IS NOT NULL THEN
+        SET set_fields = JSON_ARRAY_APPEND(set_fields, '$', 'issue_type');
+        SET new_vals = JSON_SET(new_vals, '$.issue_type', NEW.issue_type);
+    END IF;
+    IF NEW.asset_id IS NOT NULL THEN
+        SET set_fields = JSON_ARRAY_APPEND(set_fields, '$', 'asset_id');
+        SET new_vals = JSON_SET(new_vals, '$.asset_id', NEW.asset_id);
+    END IF;
+    IF NEW.borrower_id IS NOT NULL THEN
+        SET set_fields = JSON_ARRAY_APPEND(set_fields, '$', 'borrower_id');
+        SET new_vals = JSON_SET(new_vals, '$.borrower_id', NEW.borrower_id);
+    END IF;
+    IF NEW.title IS NOT NULL THEN
+        SET set_fields = JSON_ARRAY_APPEND(set_fields, '$', 'title');
+        SET new_vals = JSON_SET(new_vals, '$.title', NEW.title);
+    END IF;
+    IF NEW.severity IS NOT NULL THEN
+        SET set_fields = JSON_ARRAY_APPEND(set_fields, '$', 'severity');
+        SET new_vals = JSON_SET(new_vals, '$.severity', NEW.severity);
+    END IF;
+    IF NEW.status IS NOT NULL THEN
+        SET set_fields = JSON_ARRAY_APPEND(set_fields, '$', 'status');
+        SET new_vals = JSON_SET(new_vals, '$.status', NEW.status);
+    END IF;
+    
+    INSERT INTO issue_tracker_change_log (issue_id, change_type, changed_fields, new_values, changed_by)
+    VALUES (NEW.id, 'INSERT', set_fields, new_vals, COALESCE(@current_user_id, NEW.reported_by));
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER validate_issue_tracker_update
+BEFORE UPDATE ON issue_tracker
+FOR EACH ROW
+BEGIN
+    -- Clarify solution requires solution_plus
+    IF NEW.solution = 'Clarify' AND (NEW.solution_plus IS NULL OR NEW.solution_plus = '') THEN
+        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'solution_plus is required when solution is set to Clarify';
+    END IF;
+    
+    -- Replacement solution requires replacement_asset_id
+    IF NEW.solution = 'Replaced' AND NEW.replacement_asset_id IS NULL THEN
+        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'replacement_asset_id is required when solution is set to Replaced';
+    END IF;
+    
+    -- Auto-set resolved_date when status changes to Resolved or Closed
+    IF OLD.status NOT IN ('Resolved', 'Closed') AND NEW.status IN ('Resolved', 'Closed') THEN
+        SET NEW.resolved_date = NOW();
+        IF @current_user_id IS NOT NULL THEN
+            SET NEW.resolved_by = @current_user_id;
+        END IF;
+    END IF;
+    
+    -- Clear resolved_date when status changes away from Resolved/Closed
+    IF OLD.status IN ('Resolved', 'Closed') AND NEW.status NOT IN ('Resolved', 'Closed') THEN
+        SET NEW.resolved_date = NULL;
+        SET NEW.resolved_by = NULL;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER issue_tracker_after_update_log
+AFTER UPDATE ON issue_tracker
+FOR EACH ROW
+BEGIN
+    DECLARE changed_fields JSON DEFAULT JSON_ARRAY();
+    DECLARE old_vals JSON DEFAULT JSON_OBJECT();
+    DECLARE new_vals JSON DEFAULT JSON_OBJECT();
+    
+    -- Track all changed fields
+    IF OLD.status <=> NEW.status IS FALSE THEN
+        SET changed_fields = JSON_ARRAY_APPEND(changed_fields, '$', 'status');
+        SET old_vals = JSON_SET(old_vals, '$.status', OLD.status);
+        SET new_vals = JSON_SET(new_vals, '$.status', NEW.status);
+    END IF;
+    IF OLD.severity <=> NEW.severity IS FALSE THEN
+        SET changed_fields = JSON_ARRAY_APPEND(changed_fields, '$', 'severity');
+        SET old_vals = JSON_SET(old_vals, '$.severity', OLD.severity);
+        SET new_vals = JSON_SET(new_vals, '$.severity', NEW.severity);
+    END IF;
+    IF OLD.priority <=> NEW.priority IS FALSE THEN
+        SET changed_fields = JSON_ARRAY_APPEND(changed_fields, '$', 'priority');
+        SET old_vals = JSON_SET(old_vals, '$.priority', OLD.priority);
+        SET new_vals = JSON_SET(new_vals, '$.priority', NEW.priority);
+    END IF;
+    IF OLD.solution <=> NEW.solution IS FALSE THEN
+        SET changed_fields = JSON_ARRAY_APPEND(changed_fields, '$', 'solution');
+        SET old_vals = JSON_SET(old_vals, '$.solution', OLD.solution);
+        SET new_vals = JSON_SET(new_vals, '$.solution', NEW.solution);
+    END IF;
+    IF OLD.assigned_to <=> NEW.assigned_to IS FALSE THEN
+        SET changed_fields = JSON_ARRAY_APPEND(changed_fields, '$', 'assigned_to');
+        SET old_vals = JSON_SET(old_vals, '$.assigned_to', OLD.assigned_to);
+        SET new_vals = JSON_SET(new_vals, '$.assigned_to', NEW.assigned_to);
+    END IF;
+    IF OLD.resolved_by <=> NEW.resolved_by IS FALSE THEN
+        SET changed_fields = JSON_ARRAY_APPEND(changed_fields, '$', 'resolved_by');
+        SET old_vals = JSON_SET(old_vals, '$.resolved_by', OLD.resolved_by);
+        SET new_vals = JSON_SET(new_vals, '$.resolved_by', NEW.resolved_by);
+    END IF;
+    
+    -- Only log if something actually changed
+    IF JSON_LENGTH(changed_fields) > 0 THEN
+        INSERT INTO issue_tracker_change_log (issue_id, change_type, changed_fields, old_values, new_values, changed_by)
+        VALUES (NEW.id, 'UPDATE', changed_fields, old_vals, new_vals, COALESCE(@current_user_id, OLD.reported_by));
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER issue_tracker_before_delete
+BEFORE DELETE ON issue_tracker
+FOR EACH ROW
+BEGIN
+    -- If issue is not already resolved/closed, update it before deletion
+    IF OLD.status NOT IN ('Resolved', 'Closed') THEN
+        -- Can't UPDATE in a BEFORE DELETE trigger, so we just ensure it was marked resolved
+        -- This will prevent accidental deletion of open issues
+        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete open issues. Please close or resolve the issue first.';
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER issue_tracker_after_delete_log
+AFTER DELETE ON issue_tracker
+FOR EACH ROW
+BEGIN
+    DECLARE deleted_fields JSON DEFAULT JSON_ARRAY();
+    DECLARE old_vals JSON DEFAULT JSON_OBJECT();
+    
+    -- Log all fields from deleted issue
+    IF OLD.issue_type IS NOT NULL THEN
+        SET deleted_fields = JSON_ARRAY_APPEND(deleted_fields, '$', 'issue_type');
+        SET old_vals = JSON_SET(old_vals, '$.issue_type', OLD.issue_type);
+    END IF;
+    IF OLD.asset_id IS NOT NULL THEN
+        SET deleted_fields = JSON_ARRAY_APPEND(deleted_fields, '$', 'asset_id');
+        SET old_vals = JSON_SET(old_vals, '$.asset_id', OLD.asset_id);
+    END IF;
+    IF OLD.title IS NOT NULL THEN
+        SET deleted_fields = JSON_ARRAY_APPEND(deleted_fields, '$', 'title');
+        SET old_vals = JSON_SET(old_vals, '$.title', OLD.title);
+    END IF;
+    IF OLD.status IS NOT NULL THEN
+        SET deleted_fields = JSON_ARRAY_APPEND(deleted_fields, '$', 'status');
+        SET old_vals = JSON_SET(old_vals, '$.status', OLD.status);
+    END IF;
+    IF OLD.solution IS NOT NULL THEN
+        SET deleted_fields = JSON_ARRAY_APPEND(deleted_fields, '$', 'solution');
+        SET old_vals = JSON_SET(old_vals, '$.solution', OLD.solution);
+    END IF;
+    
+    INSERT INTO issue_tracker_change_log (issue_id, change_type, changed_fields, old_values, changed_by)
+    VALUES (OLD.id, 'DELETE', deleted_fields, old_vals, COALESCE(@current_user_id, OLD.reported_by));
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `issue_tracker_change_log` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `issue_id` int(11) NOT NULL,
+  `change_type` enum('INSERT','UPDATE','DELETE') NOT NULL,
+  `changed_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`changed_fields`)),
+  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
+  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
+  `changed_by` int(11) DEFAULT NULL,
+  `change_date` timestamp NULL DEFAULT current_timestamp(),
+  PRIMARY KEY (`id`),
+  KEY `changed_by` (`changed_by`),
+  KEY `idx_issue` (`issue_id`),
+  KEY `idx_change_type` (`change_type`),
+  KEY `idx_change_date` (`change_date`),
+  CONSTRAINT `issue_tracker_change_log_ibfk_1` FOREIGN KEY (`issue_id`) REFERENCES `issue_tracker` (`id`) ON DELETE CASCADE,
+  CONSTRAINT `issue_tracker_change_log_ibfk_2` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `label_templates` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `template_code` varchar(100) NOT NULL COMMENT 'Unique code like "CABLE"',
+  `template_name` varchar(200) NOT NULL COMMENT 'Human readable name',
+  `layout_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Universal label design: SVG graphics, auto-populated field placeholders, styling' CHECK (json_valid(`layout_json`)),
+  `created_at` timestamp NULL DEFAULT current_timestamp(),
+  `created_by` int(11) DEFAULT NULL,
+  `last_modified_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `last_modified_by` int(11) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `template_code` (`template_code`),
+  KEY `created_by` (`created_by`),
+  KEY `last_modified_by` (`last_modified_by`),
+  KEY `idx_template_code` (`template_code`),
+  KEY `idx_template_name` (`template_name`),
+  CONSTRAINT `label_templates_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `label_templates_ibfk_2` FOREIGN KEY (`last_modified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `lending_history` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `asset_id` int(11) NOT NULL,
+  `borrower_id` int(11) NOT NULL,
+  `checkout_date` datetime NOT NULL DEFAULT current_timestamp(),
+  `due_date` date DEFAULT NULL,
+  `return_date` datetime DEFAULT NULL,
+  `checked_out_by` int(11) DEFAULT NULL,
+  `checked_in_by` int(11) DEFAULT NULL,
+  `notes` text DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `checked_out_by` (`checked_out_by`),
+  KEY `checked_in_by` (`checked_in_by`),
+  KEY `idx_asset` (`asset_id`),
+  KEY `idx_borrower` (`borrower_id`),
+  KEY `idx_checkout_date` (`checkout_date`),
+  KEY `idx_return_date` (`return_date`),
+  CONSTRAINT `lending_history_ibfk_1` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE,
+  CONSTRAINT `lending_history_ibfk_2` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`),
+  CONSTRAINT `lending_history_ibfk_3` FOREIGN KEY (`checked_out_by`) REFERENCES `users` (`id`),
+  CONSTRAINT `lending_history_ibfk_4` FOREIGN KEY (`checked_in_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER lending_history_before_insert_meta
+BEFORE INSERT ON lending_history
+FOR EACH ROW
+BEGIN
+    IF NEW.checked_out_by IS NULL AND @current_user_id IS NOT NULL THEN
+        SET NEW.checked_out_by = @current_user_id;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER lending_history_before_update_meta
+BEFORE UPDATE ON lending_history
+FOR EACH ROW
+BEGIN
+    IF OLD.return_date IS NULL AND NEW.return_date IS NOT NULL THEN
+        IF NEW.checked_in_by IS NULL AND @current_user_id IS NOT NULL THEN
+            SET NEW.checked_in_by = @current_user_id;
+        END IF;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `physical_audit_logs` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `physical_audit_id` int(11) NOT NULL COMMENT 'Reference to the audit session',
+  `asset_id` int(11) NOT NULL,
+  `audit_date` datetime NOT NULL DEFAULT current_timestamp(),
+  `audited_by` int(11) NOT NULL,
+  `status_found` enum('Good','Attention','Faulty','Missing','Retired','In Repair','In Transit','Expired','Unmanaged') DEFAULT 'Good',
+  `audit_task_id` int(11) DEFAULT NULL COMMENT 'Which audit task was run on this asset',
+  `audit_task_responses` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'User responses to the JSON sequence questions' CHECK (json_valid(`audit_task_responses`)),
+  `exception_type` enum('wrong-zone','unexpected-asset','damaged','missing-label','other') DEFAULT NULL,
+  `exception_details` text DEFAULT NULL COMMENT 'Details about the exception found',
+  `found_in_zone_id` int(11) DEFAULT NULL COMMENT 'Which zone the asset was actually found in (if different from expected)',
+  `auditor_action` enum('physical-move','virtual-update','no-action') DEFAULT NULL COMMENT 'What the auditor chose to do about wrong-zone assets',
+  `notes` text DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `audit_task_id` (`audit_task_id`),
+  KEY `found_in_zone_id` (`found_in_zone_id`),
+  KEY `idx_physical_audit` (`physical_audit_id`),
+  KEY `idx_asset` (`asset_id`),
+  KEY `idx_audit_date` (`audit_date`),
+  KEY `idx_audited_by` (`audited_by`),
+  KEY `idx_status_found` (`status_found`),
+  KEY `idx_exception_type` (`exception_type`),
+  CONSTRAINT `physical_audit_logs_ibfk_1` FOREIGN KEY (`physical_audit_id`) REFERENCES `physical_audits` (`id`) ON DELETE CASCADE,
+  CONSTRAINT `physical_audit_logs_ibfk_2` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE,
+  CONSTRAINT `physical_audit_logs_ibfk_3` FOREIGN KEY (`audited_by`) REFERENCES `users` (`id`),
+  CONSTRAINT `physical_audit_logs_ibfk_4` FOREIGN KEY (`audit_task_id`) REFERENCES `audit_tasks` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `physical_audit_logs_ibfk_5` FOREIGN KEY (`found_in_zone_id`) REFERENCES `zones` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER physical_audit_logs_before_insert_meta
+BEFORE INSERT ON physical_audit_logs
+FOR EACH ROW
+BEGIN
+    -- Auto-populate audited_by from session variable if not provided
+    IF NEW.audited_by IS NULL OR NEW.audited_by = 0 THEN
+        SET NEW.audited_by = COALESCE(@current_user_id, 1);
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER update_assets_found
+AFTER INSERT ON physical_audit_logs
+FOR EACH ROW
+BEGIN
+    UPDATE physical_audits 
+    SET assets_found = assets_found + 1
+    WHERE id = NEW.physical_audit_id;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER update_asset_from_audit
+AFTER INSERT ON physical_audit_logs
+FOR EACH ROW
+BEGIN
+    DECLARE current_status VARCHAR(100);
+    
+    -- Update asset's last_audit date
+    UPDATE assets 
+    SET last_audit = DATE(NEW.audit_date),
+        last_audit_status = NEW.status_found
+    WHERE id = NEW.asset_id;
+    
+    -- Compare found status with current asset status
+    SELECT status INTO current_status FROM assets WHERE id = NEW.asset_id LIMIT 1;
+    
+    IF NEW.status_found != current_status THEN
+        UPDATE assets 
+        SET status = NEW.status_found
+        WHERE id = NEW.asset_id;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `physical_audits` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `audit_type` enum('full-zone','spot-check') NOT NULL,
+  `zone_id` int(11) DEFAULT NULL COMMENT 'Zone being audited (NULL for spot-check audits)',
+  `audit_name` varchar(255) DEFAULT NULL COMMENT 'Custom name for the audit session',
+  `started_by` int(11) NOT NULL,
+  `started_at` datetime NOT NULL DEFAULT current_timestamp(),
+  `completed_at` datetime DEFAULT NULL,
+  `status` enum('in-progress','all-good','timeout','attention','cancelled') DEFAULT 'in-progress',
+  `timeout_minutes` int(11) DEFAULT NULL COMMENT 'Timeout setting used for this audit',
+  `issues_found` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Array of issues: missing_assets, moved_assets, damaged_assets, etc.' CHECK (json_valid(`issues_found`)),
+  `assets_expected` int(11) DEFAULT NULL COMMENT 'Total assets expected to be found in zone',
+  `assets_found` int(11) DEFAULT 0 COMMENT 'Total assets actually found and scanned',
+  `notes` text DEFAULT NULL,
+  `cancelled_reason` text DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `idx_audit_type` (`audit_type`),
+  KEY `idx_zone` (`zone_id`),
+  KEY `idx_status` (`status`),
+  KEY `idx_started_at` (`started_at`),
+  KEY `idx_started_by` (`started_by`),
+  CONSTRAINT `physical_audits_ibfk_1` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`),
+  CONSTRAINT `physical_audits_ibfk_2` FOREIGN KEY (`started_by`) REFERENCES `users` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER calculate_assets_expected
+BEFORE INSERT ON physical_audits
+FOR EACH ROW
+BEGIN
+    DECLARE expected_count INT DEFAULT 0;
+    DECLARE v_timeout INT;
+    
+    -- For full-zone audits, calculate expected assets in the zone
+    IF NEW.audit_type = 'full-zone' AND NEW.zone_id IS NOT NULL THEN
+        SELECT COUNT(*) INTO expected_count
+        FROM assets 
+        WHERE zone_id = NEW.zone_id 
+        AND status NOT IN ('Missing', 'Retired');
+        
+        SET NEW.assets_expected = expected_count;
+    END IF;
+    
+    -- Set timeout from zone settings if not specified
+    IF NEW.timeout_minutes IS NULL AND NEW.zone_id IS NOT NULL THEN
+        SELECT audit_timeout_minutes INTO v_timeout
+        FROM zones 
+        WHERE id = NEW.zone_id
+        LIMIT 1;
+        
+        IF v_timeout IS NOT NULL THEN
+            SET NEW.timeout_minutes = v_timeout;
+        END IF;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER auto_detect_audit_issues
+AFTER UPDATE ON physical_audits
+FOR EACH ROW
+BEGIN
+    DECLARE missing_count INT DEFAULT 0;
+    DECLARE zone_name VARCHAR(200);
+    
+    -- Only process when audit status changes to completed states
+    IF OLD.status = 'in-progress' AND NEW.status IN ('all-good', 'attention', 'timeout') THEN
+        
+        -- Get zone name for reporting
+        IF NEW.zone_id IS NOT NULL THEN
+            SELECT zone_name INTO zone_name FROM zones WHERE id = NEW.zone_id;
+        END IF;
+        
+        -- For full-zone audits, check for missing assets
+        IF NEW.audit_type = 'full-zone' AND NEW.assets_expected IS NOT NULL THEN
+            SET missing_count = GREATEST(0, NEW.assets_expected - NEW.assets_found);
+        END IF;
+        
+        -- Create issue for missing assets
+        IF missing_count > 0 THEN
+            INSERT INTO issue_tracker (
+                issue_type, title, description, severity, priority, status,
+                reported_by, auto_detected, detection_trigger, created_date, notes
+            )
+            VALUES (
+                'System Issue', 
+                CONCAT('Audit: Missing Assets in ', COALESCE(zone_name, 'Unknown Zone')),
+                CONCAT('Full zone audit completed with ', missing_count, ' missing assets. Expected: ', NEW.assets_expected, ', Found: ', NEW.assets_found, '. Audit ID: ', NEW.id),
+                CASE WHEN missing_count >= 5 THEN 'Critical' WHEN missing_count >= 2 THEN 'High' ELSE 'Medium' END,
+                'High', 'Open',
+                NEW.started_by, TRUE, 'AUDIT_MISSING_ASSETS', NOW(),
+                CONCAT('Physical Audit ID: ', NEW.id, ' in zone: ', COALESCE(zone_name, NEW.zone_id))
+            );
+        END IF;
+    END IF;
+END */;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `print_history` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `entity_type` enum('Asset','Template','Borrower','Zone','Report','Custom') NOT NULL,
+  `entity_id` int(11) DEFAULT NULL COMMENT 'ID of the asset/template/borrower/zone (NULL for reports)',
+  `label_template_id` int(11) DEFAULT NULL,
+  `printer_id` int(11) DEFAULT NULL,
+  `quantity` int(11) DEFAULT 1,
+  `print_status` enum('Success','Failed','Cancelled','Queued') NOT NULL,
+  `error_message` text DEFAULT NULL,
+  `rendered_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'The actual data that was sent to printer (for debugging)' CHECK (json_valid(`rendered_data`)),
+  `printed_at` timestamp NULL DEFAULT current_timestamp(),
+  `printed_by` int(11) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `label_template_id` (`label_template_id`),
+  KEY `idx_entity` (`entity_type`,`entity_id`),
+  KEY `idx_printed_at` (`printed_at`),
+  KEY `idx_printed_by` (`printed_by`),
+  KEY `idx_printer` (`printer_id`),
+  KEY `idx_status` (`print_status`),
+  CONSTRAINT `print_history_ibfk_1` FOREIGN KEY (`label_template_id`) REFERENCES `label_templates` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `print_history_ibfk_2` FOREIGN KEY (`printer_id`) REFERENCES `printer_settings` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `print_history_ibfk_3` FOREIGN KEY (`printed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `printer_settings` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `printer_name` varchar(200) NOT NULL,
+  `description` text DEFAULT NULL,
+  `log` tinyint(1) DEFAULT 1 COMMENT 'Log all print jobs to this printer',
+  `can_be_used_for_reports` tinyint(1) DEFAULT 0 COMMENT 'Can this printer be used for printing reports',
+  `min_powerlevel_to_use` int(11) NOT NULL DEFAULT 75 COMMENT 'Minimum role power level required to use this printer',
+  `printer_plugin` enum('Ptouch','Brother','Zebra','System','PDF','Network','Custom') NOT NULL COMMENT 'Which printer plugin the client should send printer_settings to',
+  `printer_settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Printer-specific settings: connection, paper size, DPI, margins, etc.' CHECK (json_valid(`printer_settings`)),
+  `created_at` timestamp NULL DEFAULT current_timestamp(),
+  `created_by` int(11) DEFAULT NULL,
+  `last_modified_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `last_modified_by` int(11) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `created_by` (`created_by`),
+  KEY `last_modified_by` (`last_modified_by`),
+  KEY `idx_printer_name` (`printer_name`),
+  KEY `idx_printer_plugin` (`printer_plugin`),
+  KEY `idx_min_powerlevel` (`min_powerlevel_to_use`),
+  KEY `idx_can_reports` (`can_be_used_for_reports`),
+  CONSTRAINT `printer_settings_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `printer_settings_ibfk_2` FOREIGN KEY (`last_modified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `CONSTRAINT_1` CHECK (`min_powerlevel_to_use` >= 1 and `min_powerlevel_to_use` <= 100)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `roles` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `name` varchar(100) NOT NULL,
+  `power` int(11) NOT NULL CHECK (`power` >= 1 and `power` <= 100),
+  `created_at` timestamp NULL DEFAULT current_timestamp(),
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `suppliers` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `name` varchar(200) NOT NULL,
+  `contact` varchar(200) DEFAULT NULL,
+  `email` varchar(255) DEFAULT NULL,
+  `phone` varchar(50) DEFAULT NULL,
+  `website` varchar(255) DEFAULT NULL,
+  `notes` text DEFAULT NULL,
+  `created_at` timestamp NULL DEFAULT current_timestamp(),
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `templates` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `template_code` varchar(50) DEFAULT NULL,
+  `asset_tag_generation_string` varchar(500) DEFAULT NULL,
+  `description` text DEFAULT NULL,
+  `active` tinyint(1) DEFAULT 1,
+  `asset_type` enum('N','B','L','C') DEFAULT NULL,
+  `name` varchar(255) DEFAULT NULL,
+  `category_id` int(11) DEFAULT NULL,
+  `manufacturer` varchar(200) DEFAULT NULL,
+  `model` varchar(200) DEFAULT NULL,
+  `zone_id` int(11) DEFAULT NULL,
+  `zone_plus` enum('Floating Local','Floating Global','Clarify') DEFAULT NULL,
+  `zone_note` text DEFAULT NULL,
+  `status` enum('Good','Attention','Faulty','Missing','Retired','In Repair','In Transit','Expired','Unmanaged') DEFAULT NULL,
+  `price` decimal(12,2) DEFAULT NULL CHECK (`price` is null or `price` >= 0),
+  `purchase_date` date DEFAULT NULL COMMENT 'Default purchase date for assets created from this template',
+  `purchase_date_now` tinyint(1) DEFAULT 0 COMMENT 'Auto-set purchase date to current date when creating assets',
+  `warranty_until` date DEFAULT NULL,
+  `warranty_auto` tinyint(1) DEFAULT 0 COMMENT 'Auto-calculate warranty_until from purchase_date',
+  `warranty_auto_amount` int(11) DEFAULT NULL COMMENT 'Number of days/years for warranty calculation',
+  `warranty_auto_unit` enum('days','years') DEFAULT 'years' COMMENT 'Unit for warranty auto-calculation',
+  `expiry_date` date DEFAULT NULL,
+  `expiry_auto` tinyint(1) DEFAULT 0 COMMENT 'Auto-calculate expiry_date from purchase_date',
+  `expiry_auto_amount` int(11) DEFAULT NULL COMMENT 'Number of days/years for expiry calculation',
+  `expiry_auto_unit` enum('days','years') DEFAULT 'years' COMMENT 'Unit for expiry auto-calculation',
+  `quantity_total` int(11) DEFAULT NULL,
+  `quantity_used` int(11) DEFAULT NULL,
+  `supplier_id` int(11) DEFAULT NULL,
+  `lendable` tinyint(1) DEFAULT NULL,
+  `lending_status` enum('Available','Borrowed','Overdue','Deployed','Illegally Handed Out','Stolen') DEFAULT 'Available' COMMENT 'Default lending status for assets created from this template',
+  `minimum_role_for_lending` int(11) DEFAULT NULL,
+  `audit_task_id` int(11) DEFAULT NULL,
+  `label_template_id` int(11) DEFAULT NULL,
+  `no_scan` enum('Yes','Ask','No') DEFAULT NULL,
+  `notes` text DEFAULT NULL,
+  `additional_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`additional_fields`)),
+  `created_at` timestamp NULL DEFAULT current_timestamp(),
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `template_code` (`template_code`),
+  KEY `category_id` (`category_id`),
+  KEY `zone_id` (`zone_id`),
+  KEY `supplier_id` (`supplier_id`),
+  KEY `audit_task_id` (`audit_task_id`),
+  KEY `idx_template_code` (`template_code`),
+  KEY `idx_label_template` (`label_template_id`),
+  KEY `idx_asset_tag_generation` (`asset_tag_generation_string`),
+  CONSTRAINT `fk_template_label_template` FOREIGN KEY (`label_template_id`) REFERENCES `label_templates` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `templates_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `templates_ibfk_2` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `templates_ibfk_3` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL,
+  CONSTRAINT `templates_ibfk_4` FOREIGN KEY (`audit_task_id`) REFERENCES `audit_tasks` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `users` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `name` varchar(200) NOT NULL,
+  `username` varchar(100) NOT NULL,
+  `password` varchar(255) NOT NULL,
+  `pin_code` varchar(8) DEFAULT NULL,
+  `login_string` varchar(255) DEFAULT NULL,
+  `role_id` int(11) NOT NULL,
+  `email` varchar(255) DEFAULT NULL,
+  `phone` varchar(50) DEFAULT NULL,
+  `notes` text DEFAULT NULL,
+  `active` tinyint(1) DEFAULT 1,
+  `last_login_date` datetime DEFAULT NULL,
+  `created_date` timestamp NULL DEFAULT current_timestamp(),
+  `password_reset_token` varchar(255) DEFAULT NULL,
+  `password_reset_expiry` datetime DEFAULT NULL,
+  `preferences` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'User personalization settings: common (all clients) + client-specific (web, mobile, desktop)' CHECK (json_valid(`preferences`)),
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `username` (`username`),
+  KEY `role_id` (`role_id`),
+  KEY `idx_username` (`username`),
+  KEY `idx_login_string` (`login_string`),
+  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `zones` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `zone_name` varchar(200) NOT NULL,
+  `zone_notes` text DEFAULT NULL,
+  `zone_type` enum('Building','Floor','Room','Storage Area') NOT NULL,
+  `zone_code` varchar(50) DEFAULT NULL,
+  `mini_code` varchar(50) DEFAULT NULL,
+  `parent_id` int(11) DEFAULT NULL,
+  `include_in_parent` tinyint(1) DEFAULT 1,
+  `audit_timeout_minutes` int(11) DEFAULT 60 COMMENT 'Audit timeout in minutes for this zone',
+  PRIMARY KEY (`id`),
+  KEY `idx_parent` (`parent_id`),
+  KEY `idx_type` (`zone_type`),
+  CONSTRAINT `zones_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `zones` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+--
+-- WARNING: can't read the INFORMATION_SCHEMA.libraries table. It's most probably an old server 12.0.2-MariaDB-ubu2404.
+--
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
-- 
cgit v1.2.3-70-g09d2

