# Database Operations Documentation

This document provides detailed information about the key database operation functions in `model/operations.php` for the BlackMural Bulk SMS platform.

## Core Database Functions

### 1. `insert_edit_form($table_name)`

**Purpose**: A smart function that automatically handles both INSERT and UPDATE operations based on form data.

**Use Case**: This function is particularly beneficial when your HTML form field names match exactly with your database column names. It automatically validates form fields against actual database columns and performs the appropriate operation.

**Parameters**:
- `$table_name` (string): The name of the database table to operate on

**Expected Input**:
- Requires `$_POST` data to be available
- Form field names should match database column names
- If `$_POST['id']` exists, it performs an UPDATE operation
- If `$_POST['id']` doesn't exist, it performs an INSERT operation

**Output**:
- Returns `true` if the operation was successful
- Returns `false` if the operation failed or no POST data available

**Example Usage**:
```php
// For a form with fields: name, email, username (matching DB columns)
if (insert_edit_form('clients')) {
    echo "Operation successful!";
} else {
    echo "Operation failed!";
}
```

**Key Benefits**:
- Automatically validates form fields against database schema
- Handles both INSERT and UPDATE in one function
- Automatically sanitizes all input data
- Removes any POST fields that don't exist in the database table

---

### 2. `build_sql_insert($table, $elements)`

**Purpose**: Constructs and executes an INSERT SQL statement from an associative array.

**Parameters**:
- `$table` (string): The database table name
- `$elements` (array): Associative array where keys are column names and values are the data to insert

**Expected Input**:
```php
$data = [
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'username' => 'johndoe'
];
```

**Output**:
- Returns `true` if the INSERT was successful
- Returns `false` if the INSERT failed

**Example Usage**:
```php
$client_data = [
    'name' => 'Test Client',
    'username' => 'testclient',
    'email' => 'test@client.com',
    'admin_id' => 1
];

if (build_sql_insert('clients', $client_data)) {
    echo "Client created successfully!";
}
```

---

### 3. `build_sql_edit($table, $elements, $id)`

**Purpose**: Constructs and executes an UPDATE SQL statement for a specific record.

**Parameters**:
- `$table` (string): The database table name
- `$elements` (array): Associative array of column names and new values
- `$id` (string/int): The ID of the record to update

**Expected Input**:
```php
$update_data = [
    'name' => 'Updated Name',
    'email' => 'updated@example.com'
];
$record_id = 5;
```

**Output**:
- Returns `true` if the UPDATE was successful
- Returns `false` if the UPDATE failed

**Example Usage**:
```php
$updated_client = [
    'name' => 'Updated Client Name',
    'email' => 'newemail@client.com'
];

if (build_sql_edit('clients', $updated_client, 1)) {
    echo "Client updated successfully!";
}
```

---

### 4. `security($name)`

**Purpose**: Sanitizes and validates POST data to prevent SQL injection and XSS attacks.

**Parameters**:
- `$name` (string): The name of the POST field to sanitize

**Expected Input**:
- Requires `$_POST[$name]` to exist
- Special handling for email fields (validation and sanitization)

**Output**:
- Returns sanitized and escaped string
- Returns empty string if the POST field doesn't exist

**Security Features**:
- Trims whitespace
- Special email validation for email fields
- HTML entity encoding (XSS protection)
- MySQL real escape string (SQL injection protection)

**Example Usage**:
```php
$safe_username = security('username');
$safe_email = security('email'); // Gets additional email validation
$safe_message = security('message');
```

---

### 5. `security_get($name)`

**Purpose**: Sanitizes and validates GET data with the same security measures as `security()`.

**Parameters**:
- `$name` (string): The name of the GET parameter to sanitize

**Expected Input**:
- Requires `$_GET[$name]` to exist
- Same validation rules as `security()` function

**Output**:
- Returns sanitized and escaped string
- Returns empty string if the GET parameter doesn't exist

**Example Usage**:
```php
$safe_id = security_get('id');
$safe_search = security_get('search');
$safe_filter = security_get('filter');
```

---

### 6. `select_rows($sql)`

**Purpose**: Executes a SELECT query and returns all results as an associative array.

**Parameters**:
- `$sql` (string): The complete SQL SELECT statement

**Expected Input**:
- Valid SQL SELECT statement
- Example: `"SELECT * FROM clients WHERE admin_id = 1"`

**Output**:
- Returns array of associative arrays (each row as an associative array)
- Returns empty array if no results found

**Example Usage**:
```php
// Get all clients for a specific admin
$clients = select_rows("SELECT * FROM clients WHERE admin_id = 1 AND is_active = 1");

// Get client dashboard stats
$stats = select_rows("SELECT * FROM client_dashboard_stats WHERE client_id = 1");

// Process results
foreach ($clients as $client) {
    echo $client['name'] . " - " . $client['email'];
}
```

---

### 7. `delete($table, $id)`

**Purpose**: Deletes a record from the specified table by ID.

**Parameters**:
- `$table` (string): The database table name
- `$id` (string/int): The ID of the record to delete

**Expected Input**:
- Valid table name
- Valid record ID that exists in the table

**Output**:
- Returns `true` if the DELETE was successful
- Returns `false` if the DELETE failed

**Example Usage**:
```php
// Delete a client
if (delete('clients', 5)) {
    echo "Client deleted successfully!";
} else {
    echo "Failed to delete client!";
}

// Delete a contact group
if (delete('contact_groups', 10)) {
    echo "Contact group deleted!";
}
```

---

## Usage Best Practices

### 1. Form Field Naming Convention
When using `insert_edit_form()`, ensure your HTML form field names exactly match your database column names:

```html
<!-- Good: Field names match DB columns -->
<input name="name" type="text">
<input name="username" type="text">
<input name="email" type="email">
<input name="admin_id" type="hidden" value="1">
```

### 2. Data Validation Flow
```php
// 1. Use security functions for individual field validation
$username = security('username');
$email = security('email');

// 2. Or use insert_edit_form for automatic handling
if (insert_edit_form('clients')) {
    // Success handling
    header('Location: clients.php?success=1');
} else {
    // Error handling
    $error = "Failed to save client data";
}
```

### 3. Query Building
```php
// For complex queries, use select_rows
$admin_id = security_get('admin_id');
$sql = "SELECT c.*, a.name as admin_name 
        FROM clients c 
        JOIN admins a ON c.admin_id = a.id 
        WHERE c.admin_id = '$admin_id' 
        AND c.is_active = 1";
$clients = select_rows($sql);
```

## Security Features

All functions implement multiple layers of security:

1. **Input Validation**: Checks if data exists before processing
2. **Data Sanitization**: Removes/escapes harmful characters
3. **SQL Injection Protection**: Uses `mysqli_real_escape_string()`
4. **XSS Protection**: Uses `htmlspecialchars()` with proper flags
5. **Email Validation**: Special validation for email fields
6. **Schema Validation**: `insert_edit_form()` validates against actual database columns

## Error Handling

- Functions return boolean values for success/failure indication
- Use return values to implement proper error handling in your application
- Consider logging errors for debugging purposes

## Integration with BlackMural SMS Platform

These functions are designed to work seamlessly with the BlackMural Bulk SMS database structure, including:

- **User Management**: super_admins, admins, clients, client_users
- **SMS Operations**: campaigns, messages, templates
- **Contact Management**: contacts, contact_groups
- **API Integration**: api_requests, packages, webhooks

Use these functions consistently throughout the application for secure and reliable database operations.