# Tracknow WooCommerce Plugin - Enhanced Logging & Migration System

## Overview

The Tracknow WooCommerce plugin has been enhanced with a comprehensive logging system and database migration functionality. This document outlines all the implemented features.

## Implemented Features

### 1. UTC Time Logging ✅

- All timestamps are stored in UTC format using `gmdate('Y-m-d H:i:s')`
- Consistent timezone handling across all log entries
- Database field: `created_at` (DATETIME)

### 2. Pagination System ✅

- Configurable page size (default: 50 records per page)
- Navigation controls with Previous/Next buttons
- Page number display and direct page navigation
- Total records count and pagination info

### 3. Order ID Tracking ✅

- Automatic order ID detection from WooCommerce context
- Manual order ID parameter in logging functions
- Order ID filtering in admin interface
- Database field: `order_id` (INT)

### 4. Advanced Filtering ✅

- **Session ID Filter**: Track logs by user session
- **Request ID Filter**: Group logs by individual requests
- **Order ID Filter**: Filter logs for specific orders
- **Context Filter**: Filter by operation context (checkout, order_processing, etc.)
- **Message Filter**: Search within log messages
- **Time Range Filter**: Filter logs between two dates/times
- All filters can be combined for precise log searching

### 5. CSV Export ✅

- Export filtered logs to CSV format
- Configurable export limits (default: 10,000 records)
- Includes all log fields: ID, Request ID, Session ID, Context, Order ID, Message, Created At
- Direct download with proper headers and filename
- Nonce verification for security

### 6. Database Size Management ✅

- Maximum database size limit (10MB by default)
- Automatic cleanup when size limit is reached
- Removes oldest 25% of logs when cleanup is triggered
- Background size monitoring with each log write
- Configurable via constants: `MAX_DB_SIZE` and `CLEANUP_BATCH_PERCENT`

### 7. Database Migration System ✅

- **Automatic Schema Detection**: Detects existing table structure
- **Version Tracking**: Uses WordPress options to track table schema version
- **Column Addition**: Automatically adds missing columns to existing tables
- **Index Management**: Creates necessary indexes for performance
- **Manual Migration**: Admin interface button for manual schema updates
- **Migration Status**: Visual indicators showing when migration is needed

## Database Schema

### Table: `wp_tracknow_debug_logs`

```sql
CREATE TABLE wp_tracknow_debug_logs (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    request_id varchar(32) DEFAULT NULL,
    session_id varchar(32) DEFAULT NULL,
    context varchar(50) DEFAULT NULL,
    order_id int(11) DEFAULT NULL,
    message longtext NOT NULL,
    created_at datetime NOT NULL,
    PRIMARY KEY (id),
    KEY idx_request_id (request_id),
    KEY idx_session_id (session_id),
    KEY idx_context (context),
    KEY idx_order_id (order_id),
    KEY idx_created_at (created_at)
);
```

### Migration System

- **Current Version**: 1.1.0
- **Version Storage**: WordPress option `tracknow_logs_table_version`
- **Migration Logic**: Compares existing columns with required schema
- **Backward Compatibility**: Works with existing tables from version 1.0.0

## Admin Interface Features

### Logs Display Page

- **Location**: WooCommerce → Tracknow Logs
- **Features**:
  - Table creation/migration status
  - Advanced filtering form
  - Paginated logs display
  - Export functionality
  - Sample logs generation for testing
  - Clear logs functionality

### Migration Interface

- **Migration Status Notice**: Shows when table needs updating
- **Update Schema Button**: Manual migration trigger
- **Success/Error Messages**: Feedback for migration operations
- **Automatic Detection**: Checks schema on page load

## API Methods

### Core Logging

```php
// Basic logging
Tracknow_Logger::write_debug_log($message, $context = null, $order_id = null);

// Get logs with filters
Tracknow_Logger::get_debug_logs($filters = array(), $limit = 50, $offset = 0);

// Count logs with filters
Tracknow_Logger::get_debug_logs_count($filters = array());
```

### Migration Methods

```php
// Check if table exists
Tracknow_Logger::table_exists();

// Check if migration is needed
Tracknow_Logger::needs_migration();

// Force table migration
Tracknow_Logger::force_migrate_table();

// Create new table with current schema
Tracknow_Logger::create_debug_logs_table();
```

### Utility Methods

```php
// Export logs to CSV
Tracknow_Logger::export_logs_csv($filters = array(), $limit = 10000);

// Clear all logs
Tracknow_Logger::clear_debug_logs();

// Get available contexts
Tracknow_Logger::get_contexts();

// Get current request/session IDs
Tracknow_Logger::get_request_id();
Tracknow_Logger::get_session_id();
```

## Filter Examples

### Filter Array Structure

```php
$filters = array(
    'session_id' => 'abc123def456',      // Exact session match
    'request_id' => 'req789xyz012',      // Exact request match
    'order_id' => 12345,                 // Exact order ID
    'context' => 'checkout',             // Exact context match
    'message' => 'conversion',           // Message contains text
    'date_from' => '2024-01-01',         // Logs after date
    'date_to' => '2024-01-31'            // Logs before date
);
```

## Settings Configuration

### WooCommerce Settings

Navigate to **WooCommerce → Settings → Tracknow** to configure logging:

1. **Enable Debug Logs** - Writes logs to WordPress error_log
2. **Enable Debug Logging to Database** - Writes logs to database with advanced features

### Setting Storage

- Unified logging setting: `woocommerce_tracknowfwc_debug_logs` (controls both error_log and database logging)
- Error log setting: `woocommerce_tracknowfwc_debug_logs`

## Usage Examples

### Basic Logging

```php
// Simple message (only logged if database logging is enabled)
Tracknow_Logger::write_debug_log('User started checkout process');

// With context
Tracknow_Logger::write_debug_log('Payment processed successfully', 'checkout');

// With context and order ID
Tracknow_Logger::write_debug_log('Order completed', 'order_processing', 12345);

// System logs (always logged regardless of setting)
Tracknow_Logger::write_debug_log('Plugin activated', 'system');
```

### Filtered Retrieval

```php
// Get checkout logs for specific order
$filters = array('context' => 'checkout', 'order_id' => 12345);
$logs = Tracknow_Logger::get_debug_logs($filters, 20, 0);

// Get logs from last week
$filters = array('date_from' => date('Y-m-d', strtotime('-7 days')));
$logs = Tracknow_Logger::get_debug_logs($filters);
```

## Migration Process

### Automatic Migration

1. Plugin checks table version on admin page load
2. If version is outdated, shows migration notice
3. User clicks "Update Table Schema" button
4. System compares current schema with required schema
5. Missing columns and indexes are added
6. Version is updated to current version

### Manual Migration

```php
// Force migration regardless of version
$result = Tracknow_Logger::force_migrate_table();

// Check if migration is needed
if (Tracknow_Logger::needs_migration()) {
    // Perform migration
}
```

## Security Features

- **Nonce Verification**: All form actions use WordPress nonces
- **Capability Checks**: Admin functions require proper permissions
- **Input Sanitization**: All user inputs are sanitized
- **SQL Injection Protection**: Uses WordPress $wpdb prepared statements
- **XSS Protection**: All output is escaped using WordPress functions

## Performance Considerations

- **Database Indexes**: Strategic indexes on frequently queried columns
- **Automatic Cleanup**: Prevents database from growing too large
- **Efficient Queries**: Optimized SQL with proper WHERE clauses and LIMIT
- **Pagination**: Prevents loading too many records at once

## Testing

A test script is provided at `test-migration.php` for manual testing of:

- Table creation
- Migration detection
- Schema comparison
- Migration execution
- Version tracking

## File Structure

```
includes/
  class-tracknow-for-woocommerce-logger.php  # Core logging and migration logic

admin/partials/
  tracknow-for-woocommerce-logs-display.php  # Admin interface template

test-migration.php                           # Testing script (optional)
```

## Upgrade Path

### From Version 1.0.0 to 1.1.0

- Existing tables are automatically detected
- Missing columns (`request_id`, `session_id`, `context`, `order_id`) are added
- New indexes are created for performance
- No data loss during migration
- Backward compatibility maintained

This comprehensive logging and migration system provides a robust foundation for tracking and debugging WooCommerce operations while ensuring smooth updates and maintenance.