-- Camera Inventory Audit System Migration
-- Purpose: Track inventory activities through camera imagery with comprehensive metadata
-- Date: 2025-11-13

-- CAMERA STATIONS
-- Register physical cameras at various locations/machines
CREATE TABLE IF NOT EXISTS camera_stations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    station_code VARCHAR(50) NOT NULL UNIQUE COMMENT 'Unique identifier for camera station',
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    location_id INT UNSIGNED NULL COMMENT 'Physical location where camera is installed',
    machine_id INT UNSIGNED NULL COMMENT 'Equipment/machine this camera monitors',
    camera_type ENUM('fixed','mobile','handheld','drone','automated') NOT NULL DEFAULT 'fixed',
    resolution VARCHAR(20) NULL COMMENT 'e.g. 1920x1080, 4K',
    field_of_view VARCHAR(100) NULL COMMENT 'Description of what camera captures',
    ip_address VARCHAR(45) NULL COMMENT 'Camera IP for network cameras',
    api_endpoint VARCHAR(255) NULL COMMENT 'API endpoint for automated capture',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_capture_at DATETIME NULL COMMENT 'Timestamp of last image capture',
    capture_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Total images captured',
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_location (location_id),
    INDEX idx_machine (machine_id),
    INDEX idx_active (is_active),
    INDEX idx_station_code (station_code),
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CAMERA AUDIT LOGS
-- Visual audit trail of inventory activities
CREATE TABLE IF NOT EXISTS camera_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    camera_station_id INT UNSIGNED NOT NULL,
    
    -- Activity Context
    activity_type VARCHAR(100) NOT NULL COMMENT 'stock_movement, receiving, picking, cycle_count, inspection, loading, unloading, etc.',
    activity_subtype VARCHAR(100) NULL COMMENT 'Additional classification',
    reference_type VARCHAR(100) NULL COMMENT 'Entity type: stock_movement, work_order, purchase_order, sales_order, etc.',
    reference_id INT UNSIGNED NULL COMMENT 'ID of related entity',
    
    -- Location & Machine Context
    location_id INT UNSIGNED NULL COMMENT 'Location where activity occurred',
    machine_id INT UNSIGNED NULL COMMENT 'Machine/equipment involved',
    zone VARCHAR(100) NULL COMMENT 'Specific zone/area within location',
    
    -- Product & Inventory Context
    product_id INT UNSIGNED NULL COMMENT 'Product being handled',
    lot_number VARCHAR(100) NULL COMMENT 'Lot/batch number if applicable',
    serial_number VARCHAR(100) NULL COMMENT 'Serial number if applicable',
    quantity DECIMAL(15,3) NULL COMMENT 'Quantity involved in activity',
    
    -- User Context
    user_id INT UNSIGNED NULL COMMENT 'User performing the activity',
    operator_badge VARCHAR(50) NULL COMMENT 'Operator badge/ID if different from user',
    
    -- Image Data
    image_path VARCHAR(500) NOT NULL COMMENT 'Relative path to stored image',
    image_filename VARCHAR(255) NOT NULL,
    image_size_bytes INT UNSIGNED NULL COMMENT 'File size in bytes',
    image_format VARCHAR(20) NULL COMMENT 'jpg, png, etc.',
    thumbnail_path VARCHAR(500) NULL COMMENT 'Path to thumbnail if generated',
    
    -- Capture Metadata
    captured_at DATETIME NOT NULL COMMENT 'Actual timestamp when image was captured',
    capture_mode ENUM('manual','automatic','triggered','scheduled') NOT NULL DEFAULT 'manual',
    trigger_event VARCHAR(100) NULL COMMENT 'What triggered the capture (barcode_scan, button_press, motion, timer, etc.)',
    
    -- Image Analysis (for future AI/ML integration)
    has_analysis TINYINT(1) NOT NULL DEFAULT 0,
    analysis_data JSON NULL COMMENT 'Object detection, OCR results, anomalies detected, etc.',
    confidence_score DECIMAL(5,4) NULL COMMENT 'AI confidence score if analyzed',
    
    -- Quality & Validation
    image_quality ENUM('excellent','good','acceptable','poor') NULL,
    is_verified TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Has been reviewed/verified by user',
    verified_by INT UNSIGNED NULL,
    verified_at DATETIME NULL,
    
    -- Additional Context
    notes TEXT NULL COMMENT 'User notes or additional context',
    metadata JSON NULL COMMENT 'Additional flexible metadata',
    tags VARCHAR(500) NULL COMMENT 'Comma-separated searchable tags',
    
    -- IP and Device Info
    ip_address VARCHAR(45) NULL COMMENT 'IP of device that initiated capture',
    device_info VARCHAR(255) NULL COMMENT 'Device/browser info',
    
    -- Timestamps
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Indexes for fast searching
    INDEX idx_camera_station (camera_station_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_reference (reference_type, reference_id),
    INDEX idx_location (location_id),
    INDEX idx_machine (machine_id),
    INDEX idx_product (product_id),
    INDEX idx_user (user_id),
    INDEX idx_captured_at (captured_at),
    INDEX idx_capture_mode (capture_mode),
    INDEX idx_lot_number (lot_number),
    INDEX idx_serial_number (serial_number),
    INDEX idx_verified (is_verified),
    INDEX idx_has_analysis (has_analysis),
    
    -- Foreign Keys
    FOREIGN KEY (camera_station_id) REFERENCES camera_stations(id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CAMERA AUDIT ANNOTATIONS
-- Allow users to annotate/mark up images with findings
CREATE TABLE IF NOT EXISTS camera_audit_annotations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    camera_audit_log_id BIGINT UNSIGNED NOT NULL,
    annotation_type ENUM('note','issue','highlight','measurement','count') NOT NULL,
    annotation_data JSON NOT NULL COMMENT 'Coordinates, text, measurements, etc.',
    description TEXT NULL,
    created_by INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_log (camera_audit_log_id),
    INDEX idx_type (annotation_type),
    FOREIGN KEY (camera_audit_log_id) REFERENCES camera_audit_logs(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CAMERA AUDIT EVENTS
-- Track system-generated events and alerts from camera analysis
CREATE TABLE IF NOT EXISTS camera_audit_events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    camera_audit_log_id BIGINT UNSIGNED NOT NULL,
    event_type VARCHAR(100) NOT NULL COMMENT 'anomaly_detected, quality_issue, unauthorized_access, damage_found, etc.',
    severity ENUM('info','warning','critical') NOT NULL DEFAULT 'info',
    event_data JSON NULL COMMENT 'Detailed event information',
    is_resolved TINYINT(1) NOT NULL DEFAULT 0,
    resolved_by INT UNSIGNED NULL,
    resolved_at DATETIME NULL,
    resolution_notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_log (camera_audit_log_id),
    INDEX idx_event_type (event_type),
    INDEX idx_severity (severity),
    INDEX idx_resolved (is_resolved),
    FOREIGN KEY (camera_audit_log_id) REFERENCES camera_audit_logs(id) ON DELETE CASCADE,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add camera audit permissions
INSERT INTO permissions (name, module, description, created_at, updated_at)
VALUES 
    ('camera_audit.view', 'Inventory', 'View camera audit logs and images', NOW(), NOW()),
    ('camera_audit.capture', 'Inventory', 'Capture images and create audit logs', NOW(), NOW()),
    ('camera_audit.verify', 'Inventory', 'Verify and approve audit images', NOW(), NOW()),
    ('camera_audit.annotate', 'Inventory', 'Add annotations to audit images', NOW(), NOW()),
    ('camera_audit.delete', 'Inventory', 'Delete camera audit logs', NOW(), NOW()),
    ('camera_audit.stations', 'Inventory', 'Manage camera stations', NOW(), NOW()),
    ('camera_audit.export', 'Inventory', 'Export camera audit data', NOW(), NOW())
ON DUPLICATE KEY UPDATE name = name;

-- Assign camera audit permissions to relevant roles
SET @warehouse_mgr_role = (SELECT id FROM roles WHERE name = 'Warehouse Manager' LIMIT 1);
SET @inventory_mgr_role = (SELECT id FROM roles WHERE name = 'Inventory Manager' LIMIT 1);
SET @operations_mgr_role = (SELECT id FROM roles WHERE name = 'Operations Manager' LIMIT 1);

SET @view_perm = (SELECT id FROM permissions WHERE name = 'camera_audit.view' LIMIT 1);
SET @capture_perm = (SELECT id FROM permissions WHERE name = 'camera_audit.capture' LIMIT 1);
SET @verify_perm = (SELECT id FROM permissions WHERE name = 'camera_audit.verify' LIMIT 1);
SET @annotate_perm = (SELECT id FROM permissions WHERE name = 'camera_audit.annotate' LIMIT 1);
SET @delete_perm = (SELECT id FROM permissions WHERE name = 'camera_audit.delete' LIMIT 1);
SET @stations_perm = (SELECT id FROM permissions WHERE name = 'camera_audit.stations' LIMIT 1);
SET @export_perm = (SELECT id FROM permissions WHERE name = 'camera_audit.export' LIMIT 1);

-- Warehouse Manager gets full access
INSERT IGNORE INTO role_permissions (role_id, permission_id) 
SELECT @warehouse_mgr_role, id FROM permissions WHERE name LIKE 'camera_audit.%' AND @warehouse_mgr_role IS NOT NULL;

-- Inventory Manager gets full access
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT @inventory_mgr_role, id FROM permissions WHERE name LIKE 'camera_audit.%' AND @inventory_mgr_role IS NOT NULL;

-- Operations Manager gets full access
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT @operations_mgr_role, id FROM permissions WHERE name LIKE 'camera_audit.%' AND @operations_mgr_role IS NOT NULL;

-- Create upload directory structure placeholder
-- Note: Actual directories created by application code
-- Recommended structure: uploads/camera_audit/{YYYY}/{MM}/{DD}/
-- Example: uploads/camera_audit/2025/11/13/station01_20251113153045_abc123.jpg