-- BeepZone Database Schema v0.0.8 (Consolidated) -- MariaDB/MySQL Compatible -- Created: 2025-12-13 -- Includes: Complete schema with triggers, asset change logging, printing, templates, zones -- -- AUTO-POPULATION TRIGGERS: -- The following fields are auto-populated from @current_user_id if not provided: -- • assets.created_by (on INSERT) -- • assets.last_modified_by (on UPDATE) -- • borrowers.added_by (on INSERT) -- • borrowers.last_unban_by (on UPDATE when unbanning) -- • lending_history.checked_out_by (on INSERT) -- • lending_history.checked_in_by (on UPDATE when returning) -- • issue_tracker.reported_by (on INSERT) -- • physical_audit_logs.audited_by (on INSERT) -- Your API proxy should set @current_user_id before executing queries. -- Drop tables if they exist (in reverse order of dependencies) DROP TABLE IF EXISTS print_history; DROP TABLE IF EXISTS issue_tracker_change_log; DROP TABLE IF EXISTS asset_change_log; DROP TABLE IF EXISTS issue_tracker; DROP TABLE IF EXISTS physical_audit_logs; DROP TABLE IF EXISTS physical_audits; DROP TABLE IF EXISTS lending_history; DROP TABLE IF EXISTS templates; DROP TABLE IF EXISTS assets; DROP TABLE IF EXISTS borrowers; DROP TABLE IF EXISTS audit_tasks; DROP TABLE IF EXISTS suppliers; DROP TABLE IF EXISTS zones; DROP TABLE IF EXISTS categories; DROP TABLE IF EXISTS label_templates; DROP TABLE IF EXISTS printer_settings; DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS roles; -- ============================================ -- Roles Table -- ============================================ CREATE TABLE roles ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, power INT NOT NULL CHECK (power >= 1 AND power <= 100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Users Table -- ============================================ CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, username VARCHAR(100) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, pin_code VARCHAR(8) NULL, login_string VARCHAR(255) NULL, role_id INT NOT NULL, email VARCHAR(255) NULL, phone VARCHAR(50) NULL, notes TEXT NULL, active BOOLEAN DEFAULT TRUE, last_login_date DATETIME NULL, created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, password_reset_token VARCHAR(255) NULL, password_reset_expiry DATETIME NULL, FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT, INDEX idx_username (username), INDEX idx_login_string (login_string) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Categories Table -- ============================================ CREATE TABLE categories ( id INT AUTO_INCREMENT PRIMARY KEY, category_name VARCHAR(200) NOT NULL, category_description TEXT NULL, parent_id INT NULL, category_code VARCHAR(50) NULL, FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE RESTRICT, INDEX idx_parent (parent_id), INDEX idx_code (category_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Zones Table -- ============================================ CREATE TABLE zones ( id INT AUTO_INCREMENT PRIMARY KEY, zone_name VARCHAR(200) NOT NULL, zone_notes TEXT NULL, zone_type ENUM('Building', 'Floor', 'Room', 'Storage Area') NOT NULL, zone_code VARCHAR(50) NOT NULL COMMENT 'Full hierarchical code (e.g., PS52-1-108)', mini_code VARCHAR(50) NOT NULL COMMENT 'Local short code for this node (e.g., PS52, 1, 108)', parent_id INT NULL, include_in_parent BOOLEAN DEFAULT TRUE, audit_timeout_minutes INT DEFAULT 60 COMMENT 'Audit timeout in minutes for this zone', FOREIGN KEY (parent_id) REFERENCES zones(id) ON DELETE RESTRICT, INDEX idx_parent (parent_id), INDEX idx_type (zone_type), UNIQUE INDEX uq_zone_code (zone_code), INDEX idx_parent_type_mini (parent_id, zone_type, mini_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Suppliers Table -- ============================================ CREATE TABLE suppliers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, contact VARCHAR(200) NULL, email VARCHAR(255) NULL, phone VARCHAR(50) NULL, website VARCHAR(255) NULL, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Audit Tasks Table -- ============================================ CREATE TABLE audit_tasks ( id INT AUTO_INCREMENT PRIMARY KEY, task_name VARCHAR(200) NOT NULL, json_sequence JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Borrowers Table -- ============================================ CREATE TABLE borrowers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, email VARCHAR(255) NULL, phone_number VARCHAR(50) NULL, class_name VARCHAR(100) NULL, role VARCHAR(100) NULL, notes TEXT NULL, added_by INT NOT NULL, added_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, banned BOOLEAN DEFAULT FALSE, unban_fine DECIMAL(10, 2) DEFAULT 0.00, last_unban_by INT NULL, last_unban_date DATE NULL, FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE RESTRICT, FOREIGN KEY (last_unban_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_name (name), INDEX idx_banned (banned) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Assets Table (Sexy Edition v2) -- ============================================ CREATE TABLE assets ( id INT AUTO_INCREMENT PRIMARY KEY, asset_tag VARCHAR(200) NULL UNIQUE, asset_numeric_id INT NOT NULL UNIQUE CHECK (asset_numeric_id BETWEEN 10000000 AND 99999999), asset_type ENUM('N', 'B', 'L', 'C') NOT NULL, name VARCHAR(255) NULL, category_id INT NULL, manufacturer VARCHAR(200) NULL, model VARCHAR(200) NULL, serial_number VARCHAR(200) NULL, zone_id INT NULL, zone_plus ENUM('Floating Local', 'Floating Global', 'Clarify') NULL, zone_note TEXT NULL, status ENUM('Good', 'Attention', 'Faulty', 'Missing', 'Retired', 'In Repair', 'In Transit', 'Expired', 'Unmanaged') DEFAULT 'Good', last_audit DATE NULL, last_audit_status VARCHAR(100) NULL, price DECIMAL(12, 2) NULL CHECK (price IS NULL OR price >= 0), purchase_date DATE NULL, warranty_until DATE NULL, expiry_date DATE NULL, quantity_available INT NULL, quantity_total INT NULL, quantity_used INT DEFAULT 0, supplier_id INT NULL, lendable BOOLEAN DEFAULT FALSE, minimum_role_for_lending INT 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') NULL, current_borrower_id INT NULL, due_date DATE NULL, previous_borrower_id INT NULL, audit_task_id INT NULL, label_template_id INT NULL COMMENT 'Label template to use for this asset', no_scan ENUM('Yes', 'Ask', 'No') DEFAULT 'No', notes TEXT NULL, additional_fields JSON NULL, file_attachment MEDIUMBLOB NULL, created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, created_by INT NULL, last_modified_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_modified_by INT NULL, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT, FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE RESTRICT, FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL, FOREIGN KEY (current_borrower_id) REFERENCES borrowers(id) ON DELETE SET NULL, FOREIGN KEY (previous_borrower_id) REFERENCES borrowers(id) ON DELETE SET NULL, FOREIGN KEY (audit_task_id) REFERENCES audit_tasks(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (last_modified_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_asset_tag (asset_tag), INDEX idx_asset_numeric (asset_numeric_id), INDEX idx_type (asset_type), INDEX idx_status (status), INDEX idx_zone (zone_id), INDEX idx_category (category_id), INDEX idx_lendable (lendable), INDEX idx_lending_status (lending_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Templates Table (with new sexy columns) -- ============================================ CREATE TABLE templates ( id INT AUTO_INCREMENT PRIMARY KEY, template_code VARCHAR(50) NULL UNIQUE, asset_tag_generation_string VARCHAR(500) NULL, description TEXT NULL, active BOOLEAN DEFAULT TRUE, asset_type ENUM('N', 'B', 'L', 'C') NULL, name VARCHAR(255) NULL, category_id INT NULL, manufacturer VARCHAR(200) NULL, model VARCHAR(200) NULL, zone_id INT NULL, zone_plus ENUM('Floating Local', 'Floating Global', 'Clarify') NULL, zone_note TEXT NULL, status ENUM('Good', 'Attention', 'Faulty', 'Missing', 'Retired', 'In Repair', 'In Transit', 'Expired', 'Unmanaged') NULL, price DECIMAL(12, 2) NULL CHECK (price IS NULL OR price >= 0), purchase_date DATE NULL COMMENT 'Default purchase date for assets created from this template', purchase_date_now BOOLEAN DEFAULT FALSE COMMENT 'Auto-set purchase date to current date when creating assets', warranty_until DATE NULL, warranty_auto BOOLEAN DEFAULT FALSE COMMENT 'Auto-calculate warranty_until from purchase_date', warranty_auto_amount INT 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 NULL, expiry_auto BOOLEAN DEFAULT FALSE COMMENT 'Auto-calculate expiry_date from purchase_date', expiry_auto_amount INT 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 NULL, quantity_used INT NULL, supplier_id INT NULL, lendable BOOLEAN 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 NULL, audit_task_id INT NULL, label_template_id INT NULL COMMENT 'Default label template for assets created from this template', no_scan ENUM('Yes', 'Ask', 'No') NULL, notes TEXT NULL, additional_fields JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL, FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE SET NULL, FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL, FOREIGN KEY (audit_task_id) REFERENCES audit_tasks(id) ON DELETE SET NULL, INDEX idx_template_code (template_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Lending History Table -- ============================================ CREATE TABLE lending_history ( id INT AUTO_INCREMENT PRIMARY KEY, asset_id INT NOT NULL, borrower_id INT NOT NULL, checkout_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, due_date DATE NULL, return_date DATETIME NULL, checked_out_by INT NULL, checked_in_by INT NULL, notes TEXT NULL, FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE, FOREIGN KEY (borrower_id) REFERENCES borrowers(id) ON DELETE RESTRICT, FOREIGN KEY (checked_out_by) REFERENCES users(id) ON DELETE RESTRICT, FOREIGN KEY (checked_in_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_asset (asset_id), INDEX idx_borrower (borrower_id), INDEX idx_checkout_date (checkout_date), INDEX idx_return_date (return_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Physical Audits Table -- ============================================ CREATE TABLE physical_audits ( id INT AUTO_INCREMENT PRIMARY KEY, audit_type ENUM('full-zone', 'spot-check') NOT NULL, zone_id INT NULL COMMENT 'Zone being audited (NULL for spot-check audits)', audit_name VARCHAR(255) NULL COMMENT 'Custom name for the audit session', started_by INT NOT NULL, started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME NULL, status ENUM('in-progress', 'all-good', 'timeout', 'attention', 'cancelled') DEFAULT 'in-progress', timeout_minutes INT NULL COMMENT 'Timeout setting used for this audit', issues_found JSON NULL COMMENT 'Array of issues: missing_assets, moved_assets, damaged_assets, etc.', assets_expected INT NULL COMMENT 'Total assets expected to be found in zone', assets_found INT DEFAULT 0 COMMENT 'Total assets actually found and scanned', notes TEXT NULL, cancelled_reason TEXT NULL, FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE RESTRICT, FOREIGN KEY (started_by) REFERENCES users(id) ON DELETE RESTRICT, INDEX idx_audit_type (audit_type), INDEX idx_zone (zone_id), INDEX idx_status (status), INDEX idx_started_at (started_at), INDEX idx_started_by (started_by) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Physical Audit Logs Table -- ============================================ CREATE TABLE physical_audit_logs ( id INT AUTO_INCREMENT PRIMARY KEY, physical_audit_id INT NOT NULL COMMENT 'Reference to the audit session', asset_id INT NOT NULL, audit_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, audited_by INT NOT NULL, status_found ENUM('Good', 'Attention', 'Faulty', 'Missing', 'Retired', 'In Repair', 'In Transit', 'Expired', 'Unmanaged') DEFAULT 'Good', audit_task_id INT NULL COMMENT 'Which audit task was run on this asset', audit_task_responses JSON NULL COMMENT 'User responses to the JSON sequence questions', exception_type ENUM('wrong-zone', 'unexpected-asset', 'damaged', 'missing-label', 'other') NULL, exception_details TEXT NULL COMMENT 'Details about the exception found', found_in_zone_id INT NULL COMMENT 'Which zone the asset was actually found in (if different from expected)', auditor_action ENUM('physical-move', 'virtual-update', 'no-action') NULL COMMENT 'What the auditor chose to do about wrong-zone assets', notes TEXT NULL, FOREIGN KEY (physical_audit_id) REFERENCES physical_audits(id) ON DELETE CASCADE, FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE, FOREIGN KEY (audited_by) REFERENCES users(id) ON DELETE RESTRICT, FOREIGN KEY (audit_task_id) REFERENCES audit_tasks(id) ON DELETE SET NULL, FOREIGN KEY (found_in_zone_id) REFERENCES zones(id) ON DELETE SET NULL, INDEX idx_physical_audit (physical_audit_id), INDEX idx_asset (asset_id), INDEX idx_audit_date (audit_date), INDEX idx_audited_by (audited_by), INDEX idx_status_found (status_found), INDEX idx_exception_type (exception_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Issue Tracker Table -- ============================================ CREATE TABLE issue_tracker ( id INT AUTO_INCREMENT PRIMARY KEY, issue_type ENUM('Asset Issue', 'Borrower Issue', 'System Issue', 'Maintenance', 'Other') NOT NULL, asset_id INT NULL, borrower_id INT NULL, title VARCHAR(255) NOT NULL, description TEXT NOT NULL, severity ENUM('Critical', 'High', 'Medium', 'Low') 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') NULL, solution_plus TEXT NULL, replacement_asset_id INT NULL, reported_by INT NOT NULL, assigned_to INT NULL, resolved_by INT NULL, cost DECIMAL(10, 2) NULL, created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, resolved_date DATETIME NULL, notes TEXT NULL, auto_detected BOOLEAN DEFAULT FALSE, detection_trigger VARCHAR(100) NULL, FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE, FOREIGN KEY (borrower_id) REFERENCES borrowers(id) ON DELETE CASCADE, FOREIGN KEY (replacement_asset_id) REFERENCES assets(id) ON DELETE SET NULL, FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE RESTRICT, FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_issue_type (issue_type), INDEX idx_asset (asset_id), INDEX idx_borrower (borrower_id), INDEX idx_severity (severity), INDEX idx_status (status), INDEX idx_created_date (created_date), INDEX idx_auto_detected (auto_detected) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Issue Tracker Change Log Table -- ============================================ CREATE TABLE issue_tracker_change_log ( id INT AUTO_INCREMENT PRIMARY KEY, issue_id INT NOT NULL, change_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL, changed_fields JSON NULL, old_values JSON NULL, new_values JSON NULL, changed_by INT NULL, change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (issue_id) REFERENCES issue_tracker(id) ON DELETE CASCADE, FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_issue (issue_id), INDEX idx_change_type (change_type), INDEX idx_change_date (change_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Asset Change Log Table -- ============================================ CREATE TABLE asset_change_log ( id INT AUTO_INCREMENT PRIMARY KEY, table_name VARCHAR(50) NOT NULL, action ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL, record_id INT NOT NULL, changed_fields JSON NULL COMMENT 'Only fields that actually changed', old_values JSON NULL, new_values JSON NULL, changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, changed_by_id INT NULL, changed_by_username VARCHAR(100) NULL, FOREIGN KEY (changed_by_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_table_action (table_name, action), INDEX idx_timestamp (changed_at), INDEX idx_record (record_id), INDEX idx_user (changed_by_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Label Templates Table -- ============================================ CREATE TABLE label_templates ( id INT AUTO_INCREMENT PRIMARY KEY, template_code VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique code like "CABLE"', template_name VARCHAR(200) NOT NULL COMMENT 'Human readable name', layout_json JSON NOT NULL COMMENT 'Universal label design: graphics, auto-populated field placeholders, styling with space dimensions', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, created_by INT NULL, last_modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_modified_by INT NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (last_modified_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_template_code (template_code), INDEX idx_template_name (template_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Printer Settings Table -- ============================================ CREATE TABLE printer_settings ( id INT AUTO_INCREMENT PRIMARY KEY, printer_name VARCHAR(200) NOT NULL, description TEXT NULL, log BOOLEAN DEFAULT TRUE COMMENT 'Log all print jobs to this printer', can_be_used_for_reports BOOLEAN DEFAULT FALSE COMMENT 'Can this printer be used for printing reports', min_powerlevel_to_use INT 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 JSON NOT NULL COMMENT 'Printer-specific settings: connection, paper size, DPI, margins, etc.', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, created_by INT NULL, last_modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_modified_by INT NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (last_modified_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_printer_name (printer_name), INDEX idx_printer_plugin (printer_plugin), INDEX idx_min_powerlevel (min_powerlevel_to_use), INDEX idx_can_reports (can_be_used_for_reports), CHECK (min_powerlevel_to_use >= 1 AND min_powerlevel_to_use <= 100) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- Print History Table (Labels & Reports) -- ============================================ CREATE TABLE print_history ( id INT AUTO_INCREMENT PRIMARY KEY, entity_type ENUM('Asset', 'Template', 'Borrower', 'Zone', 'Report', 'Custom') NOT NULL, entity_id INT NULL COMMENT 'ID of the asset/template/borrower/zone (NULL for reports)', label_template_id INT NULL, printer_id INT NULL, quantity INT DEFAULT 1, print_status ENUM('Success', 'Failed', 'Cancelled', 'Queued') NOT NULL, error_message TEXT NULL, rendered_data JSON NULL COMMENT 'The actual data that was sent to printer (for debugging)', printed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, printed_by INT NULL, FOREIGN KEY (label_template_id) REFERENCES label_templates(id) ON DELETE SET NULL, FOREIGN KEY (printer_id) REFERENCES printer_settings(id) ON DELETE SET NULL, FOREIGN KEY (printed_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_entity (entity_type, entity_id), INDEX idx_printed_at (printed_at), INDEX idx_printed_by (printed_by), INDEX idx_printer (printer_id), INDEX idx_status (print_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; -- ============================================ -- TRIGGERS FOR ASSETS TABLE -- ============================================ DELIMITER // -- Trigger: Auto-populate created_by on INSERT DROP TRIGGER IF EXISTS assets_before_insert_meta// CREATE 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// -- Trigger: Auto-update last_modified_date and last_modified_by DROP TRIGGER IF EXISTS assets_before_update_meta// CREATE 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// -- Trigger: Log INSERT operations (only non-NULL fields for efficiency) DROP TRIGGER IF EXISTS assets_after_insert_log// CREATE 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// -- Trigger: Log UPDATE operations (only changed fields) DROP TRIGGER IF EXISTS assets_after_update_log// CREATE 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// -- Trigger: Log DELETE operations (only non-NULL fields for efficiency, but preserve all data for restore) DROP TRIGGER IF EXISTS assets_after_delete_log// CREATE 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// -- ============================================ -- BUSINESS LOGIC TRIGGERS -- ============================================ -- Trigger: Prevent lending non-lendable assets DROP TRIGGER IF EXISTS prevent_lend_non_lendable_assets// CREATE 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// -- Trigger: Prevent deleting borrowed items DROP TRIGGER IF EXISTS prevent_delete_borrowed_assets// CREATE 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// -- Trigger: Validate zone_plus requires zone_note for 'Clarify' DROP TRIGGER IF EXISTS validate_zone_plus_insert// CREATE 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// DROP TRIGGER IF EXISTS validate_zone_plus_update// CREATE 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// -- ============================================ -- BORROWERS TABLE TRIGGERS -- ============================================ -- Trigger: Auto-populate added_by on INSERT DROP TRIGGER IF EXISTS borrowers_before_insert_meta// CREATE 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// -- Trigger: Auto-populate last_unban_by on UPDATE when unbanning DROP TRIGGER IF EXISTS borrowers_before_update_meta// CREATE 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// -- ============================================ -- LENDING HISTORY TRIGGERS -- ============================================ -- Trigger: Auto-populate checked_out_by on INSERT DROP TRIGGER IF EXISTS lending_history_before_insert_meta// CREATE 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// -- Trigger: Auto-populate checked_in_by on UPDATE when returning DROP TRIGGER IF EXISTS lending_history_before_update_meta// CREATE 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// -- ============================================ -- ISSUE TRACKER TRIGGERS -- ============================================ -- Trigger: Auto-populate reported_by on INSERT DROP TRIGGER IF EXISTS issue_tracker_before_insert_meta// CREATE 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// -- Trigger: Validate issue_tracker business rules on INSERT DROP TRIGGER IF EXISTS validate_issue_tracker_insert// CREATE 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// -- Trigger: Validate issue_tracker business rules on UPDATE DROP TRIGGER IF EXISTS validate_issue_tracker_update// CREATE 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// -- Trigger: Auto-resolve issue before DELETE DROP TRIGGER IF EXISTS issue_tracker_before_delete// CREATE 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// -- Trigger: Log issue_tracker INSERT operations DROP TRIGGER IF EXISTS issue_tracker_after_insert_log// CREATE 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// -- Trigger: Log issue_tracker UPDATE operations DROP TRIGGER IF EXISTS issue_tracker_after_update_log// CREATE 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// -- Trigger: Log issue_tracker DELETE operations DROP TRIGGER IF EXISTS issue_tracker_after_delete_log// CREATE 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// -- Trigger: Auto-detect asset issues when status becomes problematic DROP TRIGGER IF EXISTS auto_detect_asset_issues// CREATE 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// -- Trigger: Auto-detect borrower issues when borrower is banned DROP TRIGGER IF EXISTS auto_detect_borrower_issues// CREATE 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// -- ============================================ -- PHYSICAL AUDIT TRIGGERS (Simplified) -- ============================================ -- Trigger: Auto-calculate assets_expected when starting full-zone audit DROP TRIGGER IF EXISTS calculate_assets_expected// CREATE 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// -- Trigger: Auto-populate audited_by from session user DROP TRIGGER IF EXISTS physical_audit_logs_before_insert_meta// CREATE 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// -- Trigger: Update assets_found counter when asset is audited DROP TRIGGER IF EXISTS update_assets_found// CREATE 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// -- Trigger: Simple audit issue detection DROP TRIGGER IF EXISTS auto_detect_audit_issues// CREATE 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// -- Trigger: Basic asset audit update DROP TRIGGER IF EXISTS update_asset_from_audit// CREATE 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 ; -- End of clean triggers file -- ============================================ -- USAGE NOTES -- ============================================ /* HOW TO USE FROM YOUR RUST PROXY: Before any INSERT/UPDATE/DELETE operation, set the user context: SET @current_user_id = 123; Then execute your query normally. The triggers will automatically: 1. Auto-populate user tracking fields (if not explicitly provided): • assets.created_by (on INSERT) • assets.last_modified_by (on UPDATE) • borrowers.added_by (on INSERT) • borrowers.last_unban_by (on UPDATE when unbanning) • issue_tracker.reported_by (on INSERT) 2. Log changes to asset_change_log EFFICIENTLY: • INSERT: Only logs fields that were actually set (non-NULL) • UPDATE: Only logs fields that actually changed • DELETE: Only logs fields that had values (non-NULL) for restore capability 3. Include user_id and username in logs 4. Update last_modified_by and last_modified_date automatically 5. Enforce business rules (prevent deleting borrowed items, validate zone_plus, etc.) 6. Auto-calculate quantities for lendable items with quantity tracking 7. Auto-detect and track issues in issue_tracker 8. Manage physical audits with automatic issue detection NOTE: You can still explicitly provide user tracking fields in your queries if needed. The triggers only set them if they are NULL and @current_user_id is available. EFFICIENCY: Change logging only captures non-NULL/changed fields, reducing storage by ~80% for typical operations. The 'changed_fields' JSON array shows exactly what was set/changed/deleted, making audit logs cleaner and more queryable. PHYSICAL AUDIT WORKFLOW: 1. Start audit: INSERT INTO physical_audits (audit_type, zone_id, started_by) VALUES ('full-zone', 1, 123); 2. Scan assets: INSERT INTO physical_audit_logs (physical_audit_id, asset_id, audited_by, status_found, audit_task_id, audit_task_responses, exception_type, found_in_zone_id, auditor_action) VALUES (...); 3. Complete audit: UPDATE physical_audits SET status = 'all-good' (or 'attention') WHERE id = audit_id; 4. System automatically creates issues for missing/moved/damaged assets WRONG-ZONE ASSET HANDLING: When asset found in wrong zone (exception_type = 'wrong-zone'), auditor has 3 options: - auditor_action = 'physical-move': Auditor will physically move item to correct zone (no issue created) - auditor_action = 'virtual-update': Update asset's zone in system to where found (auto-detects label reprinting needs) - auditor_action = NULL: Creates standard follow-up issue for later resolution LABEL REPRINTING DETECTION: System automatically detects if asset_tag contains location info when doing virtual-update: - Checks if asset_tag contains old zone name or room codes - Checks for common label patterns (e.g., "MB101-001", "RoomA-Device") - Creates 'Maintenance' issue with 'Low' priority for label reprinting if needed CROSS-AUDIT RECONCILIATION: System automatically resolves "missing asset" issues from previous audits when assets are found: - When asset is scanned in any audit, checks if it was missing from previous completed audits - Auto-resolves related missing asset issues with solution 'Automatically Fixed' - Logs reconciliation activity in asset_change_log for audit trail - Prevents false "missing" reports when assets are just in different locations ISSUE TRACKER FEATURES: - Auto-creates issues for problematic asset status changes - Auto-resolves issues when assets return to Good status - Tracks borrower ban/unban cycles - Comprehensive audit issue detection and tracking - Intelligent cross-audit reconciliation to prevent false missing asset reports Example queries in asset_change_log: - changed_fields: ["status", "zone_id"] (array of field names that changed) - old_values: {"status": "Good", "zone_id": 5} - new_values: {"status": "Faulty", "zone_id": 7} Example audit_task_responses JSON: {"step_1_answer": "yes", "step_2_answer": "Good", "damage_notes": "Minor scratches on case"} Example issues_found JSON in physical_audits: {"missing_assets": 2, "moved_assets": 5, "damaged_assets": 1, "total_issues": 8} */ -- ============================================ -- End of Schema -- ============================================