# Database Setup Guide

## Prerequisites

- MySQL 5.7+ or PostgreSQL 10+
- PHP 7.4+ with PDO MySQL extension
- Command-line access or phpMyAdmin

## MySQL Setup

### 1. Create Database

```sql
CREATE DATABASE chatbud DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'chatbud_user'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT ALL PRIVILEGES ON chatbud.* TO 'chatbud_user'@'localhost';
FLUSH PRIVILEGES;
```

### 2. Import Schema

Using command line:
```bash
mysql -u chatbud_user -p chatbud < database.sql
```

Or using phpMyAdmin:
1. Go to phpMyAdmin
2. Create database: `chatbud`
3. Select database
4. Go to Import tab
5. Select `database.sql` file
6. Click Import

### 3. Configure Environment

Create `.env` file in project root:
```env
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=chatbud
DB_USERNAME=chatbud_user
DB_PASSWORD=strong_password_here
```

## PostgreSQL Setup

### 1. Create Database and User

```sql
CREATE DATABASE chatbud;
CREATE USER chatbud_user WITH PASSWORD 'strong_password_here';
GRANT ALL PRIVILEGES ON DATABASE chatbud TO chatbud_user;
```

### 2. Import Schema

Convert SQL schema to PostgreSQL format and import:
```bash
psql -U chatbud_user -d chatbud -f database.sql
```

### 3. Configure Environment

```env
DB_CONNECTION=pgsql
DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=chatbud
DB_USERNAME=chatbud_user
DB_PASSWORD=strong_password_here
```

## Verification

### Check Database Connection

Create a test file `test_db.php`:
```php
<?php
require_once 'src/lib/Database.php';

try {
    $db = Database::getInstance();
    
    $result = $db->fetch("SELECT 1 as test");
    
    if ($result) {
        echo "✓ Database connection successful!";
        echo "\nTables created:";
        
        $tables = $db->fetchAll("SHOW TABLES");
        foreach ($tables as $table) {
            echo "\n- " . reset($table);
        }
    }
} catch (Exception $e) {
    echo "✗ Database connection failed: " . $e->getMessage();
}
?>
```

Run it:
```bash
php test_db.php
```

## Database Schema Overview

### Core Tables

- **users** - User accounts and profiles
- **user_sessions** - Active user sessions
- **posts** - User posts/status updates
- **post_media** - Images, videos, audio for posts
- **comments** - Comments/replies on posts
- **likes** - Like records for posts and comments
- **follows** - User following relationships

### Notification Tables

- **notifications** - User notifications (follows, likes, comments)
- **direct_messages** - Direct messaging between users

### Authentication Tables

- **password_reset_tokens** - Password reset tokens
- **email_verification_tokens** - Email verification tokens

### Security Tables

- **blocks** - User blocks (preventing interaction)
- **user_presence** - Online status tracking

## Backup & Restore

### Backup Database

MySQL:
```bash
mysqldump -u chatbud_user -p chatbud > backup_$(date +%Y%m%d_%H%M%S).sql
```

PostgreSQL:
```bash
pg_dump -U chatbud_user chatbud > backup_$(date +%Y%m%d_%H%M%S).sql
```

### Restore Database

MySQL:
```bash
mysql -u chatbud_user -p chatbud < backup_20260818_120000.sql
```

PostgreSQL:
```bash
psql -U chatbud_user chatbud < backup_20260818_120000.sql
```

## Maintenance

### Optimize Tables (MySQL)

```sql
OPTIMIZE TABLE users, posts, comments, follows, notifications;
```

### Analyze Tables

```sql
ANALYZE TABLE users, posts, comments, follows, notifications;
```

### Rebuild Indexes

```sql
REBUILD INDEX idx_created_at ON posts;
REBUILD INDEX idx_author_id ON posts;
```

## Troubleshooting

### Connection Refused
- Check MySQL/PostgreSQL service is running
- Verify credentials in .env
- Check firewall settings

### Table Doesn't Exist
- Verify database.sql was imported correctly
- Check for errors during import
- Try re-importing schema

### Permission Denied
- Verify user has correct permissions
- Grant privileges again:
  ```sql
  GRANT ALL PRIVILEGES ON chatbud.* TO 'chatbud_user'@'localhost';
  ```

### Slow Queries
- Run OPTIMIZE TABLE on large tables
- Check indexes are present
- Monitor query performance

## Security Best Practices

1. **Use Strong Passwords** - Generate random passwords for database users
2. **Restrict Network Access** - Database should only be accessible locally
3. **Backup Regularly** - Automated daily backups
4. **Update Software** - Keep MySQL/PostgreSQL updated
5. **Audit Logs** - Enable query logging for security audits
6. **Encryption** - Use SSL connections in production

## Advanced Configuration

### Connection Pooling

For production with many users, use PgBouncer (PostgreSQL) or MySQL Proxy.

### Replication

Set up master-slave replication for high availability.

### Monitoring

Use tools like:
- MySQL: Percona Monitoring and Management
- PostgreSQL: pgAdmin, pgBadger

## Support

For database issues, contact: support@chatbud.com