# OTP Integration - Live Server Deployment Guide

## 🚀 **Pre-Deployment Checklist**

### **1. Backup Database**
```bash
# Create a full database backup before making changes
mysqldump -u your_username -p your_database_name > backup_before_otp_$(date +%Y%m%d_%H%M%S).sql
```

### **2. Verify Current System**
- ✅ Ensure your Laravel API is working properly
- ✅ Verify email configuration is working
- ✅ Test user login/registration before OTP
- ✅ Check that all migrations are up to date

## 📊 **Database Migration Steps**

### **Step 1: Run the OTP SQL Script**

Execute the SQL commands in this order:

```sql
-- 1. Add OTP fields to users table
ALTER TABLE `users` ADD COLUMN `otp_enabled` BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE `users` ADD COLUMN `otp_code` VARCHAR(6) NULL;
ALTER TABLE `users` ADD COLUMN `otp_expires_at` TIMESTAMP NULL;
ALTER TABLE `users` ADD COLUMN `otp_attempts` INT NOT NULL DEFAULT 0;
ALTER TABLE `users` ADD COLUMN `otp_last_attempt_at` TIMESTAMP NULL;
ALTER TABLE `users` ADD COLUMN `otp_verified` BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE `users` ADD COLUMN `otp_method` VARCHAR(20) NOT NULL DEFAULT 'email';
```

### **Step 2: Create OTP Logs Table**

```sql
-- 2. Create the otp_logs table
CREATE TABLE `otp_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `otp_code` VARCHAR(6) NOT NULL,
  `method` VARCHAR(20) NOT NULL DEFAULT 'email',
  `action` VARCHAR(50) NOT NULL,
  `status` VARCHAR(20) NOT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `generated_at` TIMESTAMP NULL,
  `sent_at` TIMESTAMP NULL,
  `verified_at` TIMESTAMP NULL,
  `expires_at` TIMESTAMP NULL,
  `attempts` INT NOT NULL DEFAULT 0,
  `metadata` JSON NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_otp_code` (`otp_code`),
  KEY `idx_status` (`status`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_otp_logs_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### **Step 3: Update Existing Users**

```sql
-- 3. Enable OTP for all existing users
UPDATE `users` SET `otp_enabled` = TRUE WHERE `otp_enabled` IS NULL;

-- 4. Reset any existing OTP data
UPDATE `users` SET 
  `otp_code` = NULL,
  `otp_expires_at` = NULL,
  `otp_attempts` = 0,
  `otp_last_attempt_at` = NULL,
  `otp_verified` = FALSE,
  `otp_method` = 'email';
```

### **Step 4: Add Performance Indexes**

```sql
-- 5. Add indexes for better performance
ALTER TABLE `users` ADD INDEX `idx_otp_enabled` (`otp_enabled`);
ALTER TABLE `users` ADD INDEX `idx_otp_code` (`otp_code`);
ALTER TABLE `users` ADD INDEX `idx_otp_expires_at` (`otp_expires_at`);
```

## 🔧 **Laravel Configuration**

### **Environment Variables**

Add these to your `.env` file:

```env
# Email Configuration for OTP
MAIL_MAILER=smtp
MAIL_HOST=your-smtp-server.com
MAIL_PORT=587
MAIL_USERNAME=noreply@firstcapitalsint.com
MAIL_PASSWORD=your-email-password
MAIL_ENCRYPTION=tls
MAIL_FROM_ADDRESS=noreply@firstcapitalsint.com
MAIL_FROM_NAME="First Capital"

# Frontend URLs
FRONTEND_URL=https://firstcapitalsint.com
ADMIN_FRONTEND_URL=https://admin.firstcapitalsint.com
```

### **Laravel Commands**

Run these commands after database setup:

```bash
# Clear and rebuild caches
php artisan config:cache
php artisan route:cache
php artisan view:cache

# If using queues for emails
php artisan queue:restart

# Verify routes are working
php artisan route:list | grep otp
```

## 📧 **Email Templates**

The following email templates are already configured:
- ✅ `resources/views/emails/otp.blade.php`
- ✅ `resources/views/emails/otp-text.blade.php`
- ✅ `resources/views/emails/otp-status-change.blade.php`
- ✅ `resources/views/emails/otp-status-change-text.blade.php`

## 🔍 **Testing Checklist**

### **1. Database Verification**

```sql
-- Check users table has new columns
DESCRIBE users;

-- Check otp_logs table exists
DESCRIBE otp_logs;

-- Verify existing users have OTP enabled
SELECT COUNT(*) as total, 
       SUM(otp_enabled) as otp_enabled_count 
FROM users;
```

### **2. API Endpoint Testing**

Test these endpoints:

```bash
# Test OTP verification endpoint
POST /api/verify-otp
{
  "otp_code": "123456",
  "action": "login"
}

# Test OTP resend endpoint  
POST /api/resend-otp
{
  "action": "login"
}

# Test admin OTP management
POST /api/admin/users/{id}/toggle-otp
GET /api/admin/users/{id}/otp-stats
POST /api/admin/users/{id}/reset-otp
```

### **3. User Flow Testing**

1. **New User Registration**:
   - Register new user
   - Verify `otp_enabled` is `TRUE` by default
   - Check welcome email is sent

2. **Login with OTP**:
   - Attempt login with correct credentials
   - Should receive `otp_required: true` response
   - Check OTP email is sent
   - Verify OTP code works
   - Confirm successful login after OTP

3. **Admin OTP Management**:
   - Login as admin
   - Toggle user OTP status
   - Verify user receives notification email
   - Test login with OTP disabled/enabled

## 🚨 **Troubleshooting**

### **Common Issues**

1. **Email Not Sending**:
   ```bash
   # Check Laravel logs
   tail -f storage/logs/laravel.log
   
   # Test email configuration
   php artisan tinker
   Mail::raw('Test email', function($msg) {
       $msg->to('test@example.com')->subject('Test');
   });
   ```

2. **Database Errors**:
   - Check foreign key constraints
   - Verify table charset is utf8mb4
   - Ensure proper indexes exist

3. **OTP Code Issues**:
   ```sql
   -- Check recent OTP logs
   SELECT * FROM otp_logs ORDER BY created_at DESC LIMIT 10;
   
   -- Check user OTP status
   SELECT id, email, otp_enabled, otp_code, otp_expires_at 
   FROM users WHERE email = 'user@example.com';
   ```

## 📊 **Monitoring Queries**

### **OTP Usage Statistics**

```sql
-- Daily OTP generation
SELECT DATE(created_at) as date, COUNT(*) as otp_generated
FROM otp_logs 
WHERE status = 'generated' 
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- OTP success rate
SELECT 
  action,
  COUNT(*) as total_attempts,
  SUM(CASE WHEN status = 'verified' THEN 1 ELSE 0 END) as successful,
  ROUND(SUM(CASE WHEN status = 'verified' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as success_rate
FROM otp_logs 
GROUP BY action;

-- Users with OTP disabled
SELECT COUNT(*) as users_otp_disabled 
FROM users WHERE otp_enabled = FALSE;
```

## 🔄 **Rollback Plan**

If issues occur, use this rollback script:

```sql
-- EMERGENCY ROLLBACK (Use with caution)
-- DROP TABLE IF EXISTS `otp_logs`;
-- ALTER TABLE `users` DROP COLUMN IF EXISTS `otp_enabled`;
-- ALTER TABLE `users` DROP COLUMN IF EXISTS `otp_code`;
-- ALTER TABLE `users` DROP COLUMN IF EXISTS `otp_expires_at`;
-- ALTER TABLE `users` DROP COLUMN IF EXISTS `otp_attempts`;
-- ALTER TABLE `users` DROP COLUMN IF EXISTS `otp_last_attempt_at`;
-- ALTER TABLE `users` DROP COLUMN IF EXISTS `otp_verified`;
-- ALTER TABLE `users` DROP COLUMN IF EXISTS `otp_method`;
```

## ✅ **Production Deployment Checklist**

- [ ] Database backup completed
- [ ] SQL scripts executed successfully  
- [ ] Environment variables configured
- [ ] Laravel caches cleared
- [ ] Email configuration tested
- [ ] API endpoints tested
- [ ] User registration tested
- [ ] Login with OTP tested
- [ ] Admin OTP management tested
- [ ] Monitoring queries working
- [ ] Error logging configured
- [ ] Rollback plan ready

## 📞 **Support**

If you encounter issues:
1. Check Laravel logs: `storage/logs/laravel.log`
2. Check database logs for constraint errors
3. Verify email configuration with test emails
4. Test API endpoints with Postman or similar tool

---

**Deployment Status**: Ready for Live Server  
**Estimated Downtime**: 5-10 minutes  
**Risk Level**: Low (with proper backup)  

*Follow this guide step by step for a successful OTP integration deployment.*
