diff options
Diffstat (limited to 'backend/database/dev/beepzone-schema-consolidated-backup.sql')
| -rw-r--r-- | backend/database/dev/beepzone-schema-consolidated-backup.sql | 1882 |
1 files changed, 1882 insertions, 0 deletions
diff --git a/backend/database/dev/beepzone-schema-consolidated-backup.sql b/backend/database/dev/beepzone-schema-consolidated-backup.sql new file mode 100644 index 0000000..e91043c --- /dev/null +++ b/backend/database/dev/beepzone-schema-consolidated-backup.sql @@ -0,0 +1,1882 @@ +-- 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 +-- ============================================
\ No newline at end of file |