# Database Setup Guide - Rebrandly AI

Quick reference guide for setting up the MySQL database on Namecheap hosting.

---

## Quick Setup (5 Minutes)

### 1. Create Database in cPanel

```
1. Login to Namecheap cPanel
2. Go to "MySQL Databases"
3. Create database: rebrandlyai_db
4. Create user: rebrandly_user
5. Set strong password (save it!)
6. Add user to database with ALL PRIVILEGES
```

### 2. Import Schema

```
1. Open phpMyAdmin in cPanel
2. Select your database
3. Click "Import" tab
4. Upload: database/schema.sql
5. Click "Go"
```

### 3. Verify Tables Created

Check these tables exist:
- ✅ users
- ✅ sessions
- ✅ analysis_runs
- ✅ exports
- ✅ billing_events
- ✅ admin_logs

### 4. Update .env File

```env
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=rebrandly_user
MYSQL_PASSWORD=your_password_here
MYSQL_DATABASE=rebrandlyai_db
```

---

## Database Schema Overview

### users
Stores all user accounts, authentication, and subscription data.

**Key Fields**:
- `id` - Primary key
- `email` - Unique email address
- `username` - Unique username
- `password_hash` - Bcrypt hashed password
- `email_verified` - Email verification status
- `plan` - Current subscription plan (free, premium, extra_premium, platinum)
- `is_admin` - Admin access flag
- `stripe_customer_id` - Stripe customer reference
- `analyses_today` - Daily usage counter
- `total_analyses` - Lifetime usage counter

### sessions
Stores active user sessions for authentication.

**Key Fields**:
- `id` - Primary key
- `user_id` - Foreign key to users
- `session_token` - Unique session identifier
- `expires_at` - Session expiration timestamp

### analysis_runs
Stores each scraping and rebranding operation.

**Key Fields**:
- `id` - Primary key
- `user_id` - Foreign key to users
- `store_url` - Shopify store URL analyzed
- `products_found` - Number of products scraped
- `products_processed` - Number of products rebranded
- `plan_used` - Plan at time of analysis
- `status` - pending, processing, completed, failed
- `result_data` - JSON data of results

### exports
Stores CSV export records.

**Key Fields**:
- `id` - Primary key
- `user_id` - Foreign key to users
- `analysis_run_id` - Foreign key to analysis_runs
- `file_name` - Export filename
- `product_count` - Number of products in export

### billing_events
Stores Stripe webhook events for audit trail.

**Key Fields**:
- `id` - Primary key
- `user_id` - Foreign key to users
- `stripe_event_id` - Unique Stripe event ID
- `event_type` - Type of Stripe event
- `payload_json` - Full event payload
- `processed` - Processing status

### admin_logs
Stores admin actions for audit trail.

**Key Fields**:
- `id` - Primary key
- `admin_user_id` - Admin who performed action
- `target_user_id` - User affected by action
- `action` - Type of action performed
- `details_json` - Additional details

---

## Common Database Operations

### Create Admin User

After signing up normally, run this SQL:

```sql
UPDATE users 
SET is_admin = TRUE, 
    email_verified = TRUE, 
    plan = 'platinum' 
WHERE email = 'your-email@example.com';
```

### Reset User Password

```sql
-- User must use "Forgot Password" flow
-- Or manually set verification token:
UPDATE users 
SET password_reset_token = 'generated_token_here',
    password_reset_expires_at = DATE_ADD(NOW(), INTERVAL 1 HOUR)
WHERE email = 'user@example.com';
```

### View User Stats

```sql
SELECT 
    email,
    plan,
    total_analyses,
    total_products_processed,
    total_exports,
    created_at
FROM users
WHERE email = 'user@example.com';
```

### Clean Up Expired Sessions

```sql
DELETE FROM sessions WHERE expires_at < NOW();
```

### View Recent Analysis Runs

```sql
SELECT 
    u.email,
    a.store_domain,
    a.products_processed,
    a.status,
    a.created_at
FROM analysis_runs a
JOIN users u ON a.user_id = u.id
ORDER BY a.created_at DESC
LIMIT 20;
```

### Get User Activity Summary

```sql
SELECT 
    u.email,
    u.plan,
    COUNT(DISTINCT a.id) as total_runs,
    SUM(a.products_processed) as total_products,
    COUNT(DISTINCT e.id) as total_exports
FROM users u
LEFT JOIN analysis_runs a ON u.id = a.user_id
LEFT JOIN exports e ON u.id = e.user_id
WHERE u.id = ?
GROUP BY u.id;
```

### Admin Dashboard Stats

```sql
SELECT 
    COUNT(*) as total_users,
    SUM(CASE WHEN email_verified = TRUE THEN 1 ELSE 0 END) as verified_users,
    SUM(CASE WHEN plan = 'free' THEN 1 ELSE 0 END) as free_users,
    SUM(CASE WHEN plan = 'premium' THEN 1 ELSE 0 END) as premium_users,
    SUM(CASE WHEN plan = 'extra_premium' THEN 1 ELSE 0 END) as extra_premium_users,
    SUM(CASE WHEN plan = 'platinum' THEN 1 ELSE 0 END) as platinum_users,
    SUM(total_analyses) as total_analyses,
    SUM(total_products_processed) as total_products_processed
FROM users;
```

---

## Maintenance Queries

### Daily Cleanup (Automated)

```sql
-- Clean expired sessions
DELETE FROM sessions WHERE expires_at < NOW();

-- Clean expired verification tokens
UPDATE users 
SET email_verification_token = NULL, 
    email_verification_expires_at = NULL 
WHERE email_verification_expires_at < NOW();

-- Clean expired password reset tokens
UPDATE users 
SET password_reset_token = NULL, 
    password_reset_expires_at = NULL 
WHERE password_reset_expires_at < NOW();
```

### Reset Daily Usage (Runs at Midnight)

```sql
-- Reset daily counters for users whose date has changed
UPDATE users 
SET analyses_today = 0, 
    products_today = 0 
WHERE DATE(analyses_today_date) < CURDATE();
```

### Database Backup

```bash
# Via command line
mysqldump -u rebrandly_user -p rebrandlyai_db > backup_$(date +%Y%m%d).sql

# Compress backup
gzip backup_$(date +%Y%m%d).sql
```

### Restore from Backup

```bash
# Restore database
mysql -u rebrandly_user -p rebrandlyai_db < backup_20240323.sql

# Or from compressed
gunzip < backup_20240323.sql.gz | mysql -u rebrandly_user -p rebrandlyai_db
```

---

## Troubleshooting

### Can't Connect to Database

**Check credentials**:
```bash
mysql -u rebrandly_user -p rebrandlyai_db
```

**Check MySQL is running**:
```bash
sudo systemctl status mysql
```

**Check user privileges**:
```sql
SHOW GRANTS FOR 'rebrandly_user'@'localhost';
```

### Tables Not Created

**Verify schema file**:
- Check `database/schema.sql` exists
- Ensure no syntax errors
- Import manually via phpMyAdmin

**Check for errors**:
- Look at phpMyAdmin import results
- Check MySQL error log

### Performance Issues

**Add indexes** (if not already present):
```sql
-- Already included in schema.sql, but if needed:
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_session_token ON sessions(session_token);
CREATE INDEX idx_analysis_user ON analysis_runs(user_id);
```

**Optimize tables**:
```sql
OPTIMIZE TABLE users;
OPTIMIZE TABLE sessions;
OPTIMIZE TABLE analysis_runs;
OPTIMIZE TABLE exports;
```

**Check table sizes**:
```sql
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'rebrandlyai_db'
ORDER BY (data_length + index_length) DESC;
```

---

## Security Best Practices

1. **Use strong database password** (minimum 16 characters, mixed case, numbers, symbols)
2. **Limit database user privileges** to only what's needed
3. **Never expose database credentials** in client-side code
4. **Regular backups** (daily recommended)
5. **Monitor for suspicious queries** in MySQL logs
6. **Keep MySQL updated** to latest stable version
7. **Use prepared statements** (already implemented in code)
8. **Encrypt backups** before storing remotely

---

## Database Monitoring

### Check Database Size

```sql
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'rebrandlyai_db'
GROUP BY table_schema;
```

### Monitor Active Connections

```sql
SHOW PROCESSLIST;
```

### Check Slow Queries

```sql
-- Enable slow query log in MySQL config
-- Then check: /var/log/mysql/mysql-slow.log
```

---

## Migration Notes

### From Supabase to MySQL

This application has been migrated from Supabase to MySQL. Key changes:

1. **Authentication**: Custom session-based auth instead of Supabase Auth
2. **Database**: MySQL instead of PostgreSQL
3. **Email**: SMTP instead of Supabase email templates
4. **Storage**: Local or cloud storage instead of Supabase Storage

### Data Migration (if needed)

If migrating existing Supabase data:

1. Export data from Supabase
2. Transform to MySQL-compatible format
3. Import using provided schema
4. Update foreign key relationships
5. Verify data integrity

---

## Quick Reference

### Connection String Format

```
mysql://username:password@host:port/database
```

### Environment Variables

```env
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=rebrandly_user
MYSQL_PASSWORD=your_secure_password
MYSQL_DATABASE=rebrandlyai_db
```

### Default Ports

- MySQL: 3306
- phpMyAdmin: Usually 80/443 (via cPanel)

---

**Database setup complete!** ✅

For deployment instructions, see `NAMECHEAP_DEPLOYMENT.md`.