# Legacy Database Setup Guide

## Overview

This guide explains how to configure the Laravel CRM application to work with legacy MySQL/MariaDB servers (version < 5.5.3) that do not support utf8mb4 character set and JSON column types.

## Production Server Specifications

- **Host**: 172.16.2.97
- **Server**: MariaDB 5.1.69
- **OS**: Red Hat Linux GNU
- **Port**: 3306
- **Supported Charset**: utf8 (not utf8mb4)
- **Supported Collation**: utf8_general_ci
- **JSON Support**: No (use TEXT columns instead)

## Configuration Steps

### 1. Update Environment File

Copy `.env.example` to `.env` and update the following values:

```env
# Database Connection
DB_CONNECTION=mysql
DB_HOST=172.16.2.97
DB_PORT=3306
DB_DATABASE=your_database_name
DB_USERNAME=your_username
DB_PASSWORD=your_password
DB_PREFIX=

# Character Set Configuration (REQUIRED for MariaDB 5.1.69)
DB_CHARSET=utf8
DB_COLLATION=utf8_general_ci
```

### 2. Run Database Migrations

Execute the migrations to create the database schema:

```bash
php artisan migrate
```

All migrations have been updated to be compatible with MariaDB 5.1.69:
- No JSON column types (replaced with TEXT)
- No utf8mb4 charset requirements
- Compatible with MySQL 5.1+ syntax

### 3. Verify Connection

Test the database connection:

```bash
php artisan tinker
```

Then run:

```php
DB::connection()->getPdo();
DB::select('SELECT @@character_set_database, @@collation_database');
```

Expected output:
```
@@character_set_database: utf8
@@collation_database: utf8_general_ci
```

## Character Set Limitations

### UTF-8 vs UTF-8MB4

**utf8** (3-byte encoding):
- ✅ Supports Basic Multilingual Plane (BMP) characters
- ✅ Covers most languages: English, Spanish, French, German, Chinese, Japanese, Korean, Arabic, etc.
- ❌ Does NOT support 4-byte characters like emoji (😀, 🎉, etc.)
- ❌ Does NOT support some rare Chinese/Japanese characters

**utf8mb4** (4-byte encoding):
- ✅ Supports all Unicode characters including emoji
- ✅ Full UTF-8 standard compliance
- ❌ NOT supported on MariaDB 5.1.69

### Handling Emoji and 4-byte Characters

If users try to input emoji or 4-byte UTF-8 characters:

1. **Option 1**: Strip them before saving
2. **Option 2**: Show validation error to user
3. **Option 3**: Replace with text equivalents (e.g., ":smile:")

The application will handle this gracefully without crashing.

## JSON Data Storage

### How It Works

Since MariaDB 5.1.69 doesn't support JSON column type, we use TEXT columns with Laravel's array casting:

**Migration**:
```php
$table->text('tags')->nullable();
```

**Model**:
```php
protected $casts = [
    'tags' => 'array',
];
```

**Usage** (transparent to developers):
```php
// Storing
$ticket->tags = ['urgent', 'customer-support'];
$ticket->save();

// Retrieving
$tags = $ticket->tags; // Returns array: ['urgent', 'customer-support']
```

Laravel automatically handles JSON encoding/decoding.

### Limitations

- No native JSON query functions (e.g., `JSON_CONTAINS`, `JSON_EXTRACT`)
- Must retrieve full column value to query JSON data
- Slightly slower than native JSON columns on modern servers

## Troubleshooting

### Error: "Unknown collation: 'utf8mb4_unicode_ci'"

**Cause**: Application is trying to use utf8mb4 charset

**Solution**: 
1. Check `.env` file has `DB_CHARSET=utf8` and `DB_COLLATION=utf8_general_ci`
2. Clear config cache: `php artisan config:clear`
3. Retry migration

### Error: "You have an error in your SQL syntax... near 'json'"

**Cause**: Migration file contains JSON column type

**Solution**:
1. All migrations have been updated to use TEXT instead
2. If you see this error, check for custom migrations
3. Replace `$table->json('field')` with `$table->text('field')`
4. Add array casting in the model

### Error: "Incorrect string value"

**Cause**: Data contains 4-byte UTF-8 characters (emoji)

**Solution**:
1. Remove emoji from input data
2. Implement input validation to reject emoji
3. Or upgrade to a modern MySQL/MariaDB server with utf8mb4 support

### Connection Timeout

**Cause**: Cannot reach database server

**Solution**:
1. Verify server IP: `ping 172.16.2.97`
2. Check port is open: `telnet 172.16.2.97 3306`
3. Verify firewall rules allow connection
4. Confirm database server is running

### Authentication Failed

**Cause**: Invalid credentials

**Solution**:
1. Verify username and password in `.env`
2. Check user has permissions: `GRANT ALL ON database.* TO 'user'@'%'`
3. Confirm user can connect from your IP address

## Migration from Development to Production

### Development Environment (Modern MySQL)

```env
DB_CHARSET=utf8mb4
DB_COLLATION=utf8mb4_unicode_ci
```

### Production Environment (Legacy MariaDB 5.1.69)

```env
DB_CHARSET=utf8
DB_COLLATION=utf8_general_ci
```

### Data Migration Considerations

When migrating data from utf8mb4 to utf8:

1. **Backup your data** before migration
2. **Check for 4-byte characters**:
   ```sql
   SELECT * FROM table WHERE column REGEXP '[😀-🙏]';
   ```
3. **Clean data** if necessary:
   - Remove emoji
   - Replace with text equivalents
   - Or store in separate field
4. **Export with utf8**:
   ```bash
   mysqldump --default-character-set=utf8 database > backup.sql
   ```
5. **Import to production**:
   ```bash
   mysql -h 172.16.2.97 -u user -p database < backup.sql
   ```

## Performance Considerations

### TEXT vs JSON Columns

- **Storage**: Similar size for both
- **Indexing**: Cannot index JSON content in TEXT columns
- **Queries**: Must retrieve full column, cannot use JSON functions
- **Application**: Laravel handles encoding/decoding transparently

### Optimization Tips

1. **Keep JSON data small**: Large TEXT columns slow down queries
2. **Use separate tables**: For frequently queried JSON fields
3. **Add indexes**: On columns used in WHERE clauses
4. **Cache results**: Use Laravel's cache for expensive queries

## Security Considerations

1. **SQL Injection**: Laravel's query builder protects against this
2. **Character Encoding**: Validate input to prevent encoding attacks
3. **Connection Security**: Use SSL if available (check with DBA)
4. **Credentials**: Never commit `.env` file to version control

## Support

For issues or questions:

1. Check this documentation first
2. Review Laravel logs: `storage/logs/laravel.log`
3. Check database server logs
4. Contact system administrator for server-specific issues

## References

- [MySQL Character Sets](https://dev.mysql.com/doc/refman/5.1/en/charset.html)
- [Laravel Database Configuration](https://laravel.com/docs/database)
- [Laravel Eloquent Casting](https://laravel.com/docs/eloquent-mutators#attribute-casting)