# Database Setup Instructions for cPanel

## Overview

This application uses MySQL database to store:
- User accounts and authentication data
- Comments and community discussions
- Voting and reputation scores
- Subscription and payment information
- Moderation settings and activity logs

---

## Step 1: Create Database in cPanel

1. Log into your cPanel account
2. Navigate to **Databases → MySQL Databases**
3. Under "Create New Database":
   - Enter database name: `winslow_membership` (or your preferred name)
   - Click **Create Database**
   - Note: cPanel may prefix your database name with your username (e.g., `username_winslow_membership`)

---

## Step 2: Create Database User

1. Scroll to "MySQL Users" section
2. Under "Add New User":
   - Username: `winslow_user` (or your preferred username)
   - Password: Click **Generate Password** for a strong password
   - **IMPORTANT**: Copy and save this password securely
   - Click **Create User**
   - Note: cPanel may prefix your username (e.g., `username_winslow_user`)

---

## Step 3: Grant User Privileges

1. Scroll to "Add User To Database" section
2. Select:
   - User: The user you just created
   - Database: The database you just created
3. Click **Add**
4. On the privileges page, select **ALL PRIVILEGES**
5. Click **Make Changes**

---

## Step 4: Note Your Database Credentials

Write down the following information (you'll need it for environment variables):

```
Database Host: localhost (or 127.0.0.1)
Database Port: 3306
Database Name: [your full database name with prefix]
Database User: [your full username with prefix]
Database Password: [the password you generated]
```

---

## Step 5: Construct DATABASE_URL

Your DATABASE_URL should be in this format:

```
mysql://USERNAME:PASSWORD@HOST:PORT/DATABASE_NAME
```

Example:
```
mysql://username_winslow_user:MySecurePass123!@localhost:3306/username_winslow_membership
```

**Important Notes:**
- Replace `USERNAME` with your full database username (including cPanel prefix)
- Replace `PASSWORD` with your database password
- If password contains special characters (@, :, /, etc.), you may need to URL-encode them
- Replace `DATABASE_NAME` with your full database name (including cPanel prefix)

---

## Step 6: Initialize Database Schema

After uploading the application and setting environment variables:

1. Access cPanel Terminal (Advanced → Terminal)
2. Navigate to application directory:
   ```bash
   cd winslow-membership
   ```
3. Enter Node.js virtual environment:
   ```bash
   source /home/YOUR_USERNAME/nodevenv/winslow-membership/18/bin/activate
   ```
4. Run database migration:
   ```bash
   pnpm db:push
   ```

This command will create all necessary tables:
- `users` - User accounts and profiles
- `subscriptionPlans` - Subscription tier definitions
- `resources` - Downloadable content
- `contentSections` - Principle and rule content
- `comments` - Community comments
- `commentVotes` - Upvotes and downvotes
- `commentReports` - Flagged content
- `notifications` - User notifications
- `moderationSettings` - Auto-moderation rules
- `moderationActivityLog` - Moderation history

---

## Step 7: Verify Database Setup

1. In cPanel, go to **Databases → phpMyAdmin**
2. Select your database from the left sidebar
3. You should see all the tables listed above
4. Click on `users` table - it should be empty initially
5. Click on `subscriptionPlans` table - it should have 3 rows (free, paid_monthly, paid_annual)

---

## Troubleshooting

### "Access denied" errors:
- Verify database user has ALL PRIVILEGES
- Check that username and password are correct in DATABASE_URL
- Ensure database user is added to the database

### "Unknown database" errors:
- Verify database name is correct (including cPanel prefix)
- Check that database was created successfully in MySQL Databases

### "Connection refused" errors:
- Verify database host is `localhost` or `127.0.0.1`
- Check that MySQL service is running (contact hosting support)

### "Too many connections" errors:
- Contact your hosting provider to increase MySQL connection limit
- Check for connection leaks in application logs

### Special characters in password:
If your password contains special characters, URL-encode them:
- `@` → `%40`
- `:` → `%3A`
- `/` → `%2F`
- `?` → `%3F`
- `#` → `%23`
- `&` → `%26`
- `=` → `%3D`

Example:
```
Password: Pass@123!
Encoded: Pass%40123!
```

---

## Database Maintenance

### Backup Database:
1. Go to **Databases → phpMyAdmin**
2. Select your database
3. Click **Export** tab
4. Choose **Quick** export method
5. Click **Go** to download SQL file

### Restore Database:
1. Go to **Databases → phpMyAdmin**
2. Select your database
3. Click **Import** tab
4. Choose your SQL backup file
5. Click **Go**

### View Database Size:
1. Go to **Databases → MySQL Databases**
2. Scroll to "Current Databases"
3. Your database size is shown in the list

---

## Security Best Practices

1. **Use strong passwords** - Always generate random passwords for database users
2. **Limit privileges** - Only grant necessary privileges (ALL PRIVILEGES is fine for this app)
3. **Regular backups** - Schedule weekly database backups
4. **Monitor size** - Check database size regularly to avoid quota issues
5. **Secure credentials** - Never commit DATABASE_URL to version control
6. **Use SSL** - If available, enable SSL for database connections

---

## Need Help?

- **Database not created**: Contact your hosting provider
- **Privileges not working**: Verify user is added to database
- **Connection issues**: Check DATABASE_URL format
- **Migration errors**: Check application logs in cPanel Terminal