path: root/php_migration_notes.txt

# PHP Website Migration Notes: JSON to MariaDB

This document outlines the changes needed to migrate the PHP website from JSON
file storage to MariaDB for commission management.

## 1. Database Connection Setup (PDO)

Create a file `db.php` for database connection:

```php
<?php
$db_host = '10.0.0.xxx';  // Same as bot's DB_HOST
$db_name = 'mystra';
$db_user = 'mystra';
$db_pass = '...';         // Same as bot's DB_PASSWORD

try {
    $pdo = new PDO(
        "mysql:host=$db_host;dbname=$db_name;charset=utf8mb4",
        $db_user,
        $db_pass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}
```

## 2. Updated Commission Submission Form Handling

Replace JSON file writing with database insert:

```php
<?php
require_once 'db.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $email = filter_input(INPUT_POST, 'email', FILTER_SANITIZE_EMAIL);
    $name = filter_input(INPUT_POST, 'name', FILTER_SANITIZE_STRING);
    $type = filter_input(INPUT_POST, 'type', FILTER_SANITIZE_STRING);
    $description = filter_input(INPUT_POST, 'description', FILTER_SANITIZE_STRING);
    $budget = filter_input(INPUT_POST, 'budget', FILTER_SANITIZE_STRING);

    // Validate commission type
    $valid_types = ['art', 'design', 'other', 'unsure'];
    if (!in_array($type, $valid_types)) {
        $error = "Invalid commission type";
    } else {
        $stmt = $pdo->prepare("
            INSERT INTO commissions (discord_user_id, email, name, commission_type, description, budget)
            VALUES (NULL, :email, :name, :type, :description, :budget)
        ");

        $stmt->execute([
            ':email' => $email,
            ':name' => $name,
            ':type' => $type,
            ':description' => $description,
            ':budget' => $budget ?: null,
        ]);

        $commission_id = $pdo->lastInsertId();
        $success = "Commission submitted! Your ID is #$commission_id";
    }
}
```

## 3. Updated Commission Display Queries

### Show Queue Count (Public)

```php
<?php
require_once 'db.php';

$stmt = $pdo->query("
    SELECT
        COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending,
        COUNT(CASE WHEN status = 'in_progress' THEN 1 END) as in_progress
    FROM commissions
");
$counts = $stmt->fetch();
$total = $counts['pending'] + $counts['in_progress'];

echo "Queue: $total commissions ({$counts['pending']} pending, {$counts['in_progress']} in progress)";
```

### List All Commissions (Admin)

```php
<?php
require_once 'db.php';

$status_filter = $_GET['status'] ?? null;

if ($status_filter) {
    $stmt = $pdo->prepare("
        SELECT id, name, email, commission_type, status, created_at
        FROM commissions
        WHERE status = :status
        ORDER BY created_at DESC
    ");
    $stmt->execute([':status' => $status_filter]);
} else {
    $stmt = $pdo->query("
        SELECT id, name, email, commission_type, status, created_at
        FROM commissions
        ORDER BY created_at DESC
    ");
}

$commissions = $stmt->fetchAll();
```

### View Single Commission (Admin)

```php
<?php
require_once 'db.php';

$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);

$stmt = $pdo->prepare("SELECT * FROM commissions WHERE id = :id");
$stmt->execute([':id' => $id]);
$commission = $stmt->fetch();

if (!$commission) {
    die("Commission not found");
}
```

### Update Commission Status (Admin)

```php
<?php
require_once 'db.php';

$id = filter_input(INPUT_POST, 'id', FILTER_VALIDATE_INT);
$status = filter_input(INPUT_POST, 'status', FILTER_SANITIZE_STRING);
$reason = filter_input(INPUT_POST, 'rejection_reason', FILTER_SANITIZE_STRING);

$valid_statuses = ['pending', 'accepted', 'in_progress', 'completed', 'rejected'];
if (!in_array($status, $valid_statuses)) {
    die("Invalid status");
}

if ($status === 'rejected' && $reason) {
    $stmt = $pdo->prepare("
        UPDATE commissions
        SET status = :status, rejection_reason = :reason
        WHERE id = :id
    ");
    $stmt->execute([':status' => $status, ':reason' => $reason, ':id' => $id]);
} else {
    $stmt = $pdo->prepare("UPDATE commissions SET status = :status WHERE id = :id");
    $stmt->execute([':status' => $status, ':id' => $id]);
}
```

## 4. Migration Script: Import Existing JSON Data

If you have existing commissions in a JSON file, use this script to import them:

```php
<?php
require_once 'db.php';

// Load existing JSON data
$json_file = 'commissions.json';  // Adjust path as needed
$json_data = file_get_contents($json_file);
$commissions = json_decode($json_data, true);

if (!$commissions) {
    die("No commissions to migrate or invalid JSON");
}

$stmt = $pdo->prepare("
    INSERT INTO commissions (email, name, commission_type, description, budget, status, created_at)
    VALUES (:email, :name, :type, :description, :budget, :status, :created_at)
");

$count = 0;
foreach ($commissions as $c) {
    // Map your existing JSON fields to database columns
    // Adjust field names based on your actual JSON structure
    $stmt->execute([
        ':email' => $c['email'] ?? '',
        ':name' => $c['name'] ?? '',
        ':type' => $c['type'] ?? 'unsure',
        ':description' => $c['description'] ?? '',
        ':budget' => $c['budget'] ?? null,
        ':status' => $c['status'] ?? 'pending',
        ':created_at' => $c['created_at'] ?? date('Y-m-d H:i:s'),
    ]);
    $count++;
}

echo "Migrated $count commissions to MariaDB\n";
```

## 5. MariaDB Setup Commands

Run these on your database LXC:

```sql
-- Create database and user
CREATE DATABASE mystra CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'mystra'@'10.0.0.%' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON mystra.* TO 'mystra'@'10.0.0.%';
FLUSH PRIVILEGES;

-- Create the commissions table
USE mystra;

CREATE TABLE commissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    discord_user_id BIGINT,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    commission_type ENUM('art', 'design', 'other', 'unsure') NOT NULL,
    description TEXT NOT NULL,
    budget VARCHAR(100),
    status ENUM('pending', 'accepted', 'in_progress', 'completed', 'rejected') DEFAULT 'pending',
    rejection_reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Optional: Add index for faster status filtering
CREATE INDEX idx_status ON commissions(status);
```

## 6. MariaDB Configuration

In `/etc/mysql/mariadb.conf.d/50-server.cnf`, ensure MariaDB listens on the
network interface (not just localhost):

```ini
bind-address = 0.0.0.0
```

Then restart MariaDB:

```bash
systemctl restart mariadb
```

## Notes

- Both the Discord bot and PHP website use the same database and table
- discord_user_id will be NULL for commissions submitted via the website
- The bot will notify users via Discord DM only if they submitted via Discord
- Make sure to update DB credentials in both bot's config.py and PHP's db.php