path: root/backend/database/dev/beepzone-schema-consolidated-backup.sql

-- 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
-- ============================================