-- Product Activity Log Table
-- Comprehensive logging for all product-related activities

CREATE TABLE IF NOT EXISTS product_activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Activity Classification
    activity_type ENUM(
        'product_create', 'product_update', 'product_delete', 'product_status_change',
        'category_create', 'category_update', 'category_delete', 'category_assign',
        'pricing_create', 'pricing_update', 'pricing_delete',
        'stock_adjustment', 'stock_reservation', 'stock_confirmation', 'stock_release',
        'purchase_create', 'purchase_update', 'purchase_cancel', 'purchase_refund',
        'bulk_operation', 'import_data', 'export_data', 'system_maintenance'
    ) NOT NULL,
    
    -- Entity References
    product_id INT NULL,
    category_id INT NULL,
    pricing_id INT NULL,
    registration_id INT NULL,
    
    -- User Information
    user_id INT NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    user_role VARCHAR(100) NOT NULL,
    
    -- Activity Details
    action_description TEXT NOT NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    
    -- Context Information
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    session_id VARCHAR(255) NULL,
    
    -- Additional Metadata
    affected_count INT DEFAULT 1,
    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    tags JSON NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Indexes for performance
    INDEX idx_activity_type (activity_type),
    INDEX idx_product_id (product_id),
    INDEX idx_category_id (category_id),
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at),
    INDEX idx_severity (severity),
    
    -- Foreign key constraints
    FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES product_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create a view for easy querying of recent activities
CREATE OR REPLACE VIEW recent_product_activities AS
SELECT 
    pal.*,
    p.name as product_name,
    p.product_code,
    pc.name as category_name,
    CASE 
        WHEN pal.created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN 'just_now'
        WHEN pal.created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 'today'
        WHEN pal.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 'this_week'
        ELSE 'older'
    END as time_category
FROM product_activity_log pal
LEFT JOIN products p ON pal.product_id = p.id
LEFT JOIN product_categories pc ON pal.category_id = pc.id
ORDER BY pal.created_at DESC;

-- Create indexes for the view
CREATE INDEX idx_activity_time_category ON product_activity_log (created_at DESC);
