# Migration Guide: Multi-Provider Email Support

## Step 1: Check Current Schema

First, check what indexes exist on your table:

```sql
SHOW INDEX FROM notification_delivery_config;
```

Look for a unique index on `channel_type`. It might be named:
- `channel_type`
- `notification_delivery_config_channel_type_key`
- Or something similar

**If you see a unique index on `channel_type`, note the exact name.**

## Step 2: Drop Old Unique Constraint (if exists)

Replace `INDEX_NAME_HERE` with the actual name from Step 1:

```sql
-- If the index name is 'channel_type':
ALTER TABLE notification_delivery_config DROP INDEX channel_type;

-- OR if it has a different name:
ALTER TABLE notification_delivery_config DROP INDEX notification_delivery_config_channel_type_key;
```

**If you got error "Can't DROP"**, it means there's no unique constraint. **This is fine! Skip to Step 3.**

## Step 3: Add New Unique Constraint

```sql
ALTER TABLE notification_delivery_config
ADD UNIQUE KEY unique_channel_provider (channel_type, provider);
```

This allows multiple providers per channel.

## Step 4: Add is_active Column

```sql
ALTER TABLE notification_delivery_config
ADD COLUMN is_active BOOLEAN DEFAULT false AFTER is_enabled;
```

## Step 5: Mark Current Config as Active

```sql
UPDATE notification_delivery_config
SET is_active = true
WHERE channel_type = 'email'
  AND is_enabled = true
LIMIT 1;
```

## Step 6: Insert Mailtrap Config

```sql
INSERT IGNORE INTO notification_delivery_config
(channel_type, is_enabled, is_active, provider, provider_config, status, success_rate, created_by, updated_by, created_at, updated_at)
VALUES (
  'email',
  false,
  false,
  'Mailtrap',
  JSON_OBJECT(
    'host', 'live.smtp.mailtrap.io',
    'port', 587,
    'secure', false,
    'user', 'apismtp@mailtrap.io',
    'pass', '',
    'senderEmail', '',
    'senderName', ''
  ),
  'Not Configured',
  0.0,
  1,
  1,
  NOW(),
  NOW()
);
```

## Step 7: Insert AWS SES Config

```sql
INSERT IGNORE INTO notification_delivery_config
(channel_type, is_enabled, is_active, provider, provider_config, status, success_rate, created_by, updated_by, created_at, updated_at)
VALUES (
  'email',
  false,
  false,
  'AWS SES',
  JSON_OBJECT(
    'host', '',
    'port', 587,
    'secure', false,
    'user', '',
    'pass', '',
    'senderEmail', '',
    'senderName', '',
    'region', 'us-east-1',
    'configurationSet', ''
  ),
  'Not Configured',
  0.0,
  1,
  1,
  NOW(),
  NOW()
);
```

## Step 8: Verify Migration

```sql
SELECT
  id,
  channel_type,
  provider,
  is_enabled,
  is_active,
  status,
  JSON_EXTRACT(provider_config, '$.host') as smtp_host
FROM notification_delivery_config
WHERE channel_type = 'email'
ORDER BY provider;
```

You should see:
- At least 2 rows for email (Mailtrap and AWS SES)
- One might be `is_enabled = true` (your current config)
- Only one should have `is_active = true`

## Step 9: Regenerate Prisma Client

After database migration, update your Prisma client:

```bash
npx prisma generate
```

## Troubleshooting

### Error: "Can't DROP 'channel_type'"
**Solution:** Your table doesn't have a unique constraint on `channel_type`. This is fine! Skip Step 2 and continue with Step 3.

### Error: "Duplicate entry for key 'unique_channel_provider'"
**Solution:** The unique constraint already exists. Skip Step 3.

### Error: "Duplicate column name 'is_active'"
**Solution:** The column already exists. Skip Step 4.

### Error: "Duplicate entry" on INSERT
**Solution:** The config already exists. This is normal with `INSERT IGNORE`.

## Alternative: Use Safe Migration Script

If you prefer, run the safe migration script that handles all checks automatically:

```bash
mysql -h 18.138.137.105 -u admin -p corrad-notification < database/migrations/005_support_multiple_email_providers_safe.sql
```

This script checks for existing indexes/columns before making changes.