1 files changed, 257 insertions, 0 deletions
diff --git a/php_migration_notes.txt b/php_migration_notes.txt
new file mode 100644
index 0000000..f52bd55
--- /dev/null
+++ b/php_migration_notes.txt
@@ -0,0 +1,257 @@
+# 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