قاعدة البيانات — تحسين وأمان

<?php
// ─── ❌ كود خطير — عُرضة لـ SQL Injection ─────────────────
$username = $_POST['username']; // يمكن أن يكون: ' OR '1'='1
$password = $_POST['password'];

// إذا أدخل المهاجم:  username = ' OR '1'='1' --
// الاستعلام يصبح:
// SELECT * FROM users WHERE username = '' OR '1'='1' -- AND password = 'x'
// ويُعيد كل المستخدمين!
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $pdo->query($sql); // خطر!

// ─── ✅ الطريقة الصحيحة — Prepared Statements ─────────────
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->execute([$username, $password]);
$user = $stmt->fetch();
// مهما كان المدخل، سيُعامَل كنص عادي وليس كود SQL

// ─── ✅ Named Parameters (أوضح) ────────────────────────────
$stmt = $pdo->prepare("
    SELECT id, username, email
    FROM users
    WHERE email = :email
    AND is_active = :active
    LIMIT 1
");
$stmt->execute([
    ':email'  => filter_var($email, FILTER_SANITIZE_EMAIL),
    ':active' => 1,
]);
$user = $stmt->fetch();

// ─── ✅ Dynamic ORDER BY آمن (لا يمكن parameterize) ────────
$allowed_cols  = ['name', 'price', 'created_at', 'stock'];
$allowed_dirs  = ['ASC', 'DESC'];
$sort_col = in_array($_GET['sort'] ?? '', $allowed_cols) ? $_GET['sort'] : 'created_at';
$sort_dir = in_array(strtoupper($_GET['dir'] ?? ''), $allowed_dirs) ? strtoupper($_GET['dir']) : 'DESC';

// الآن آمن للاستخدام في الاستعلام
$sql = "SELECT * FROM products ORDER BY $sort_col $sort_dir";
?>

-- ─── أنواع الـ Indexes ───────────────────────────────────────

-- 1. PRIMARY KEY — معرّف فريد (ينشأ تلقائياً)
ALTER TABLE users ADD PRIMARY KEY (id);

-- 2. UNIQUE INDEX — لا تكرار (مثل البريد الإلكتروني)
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_username ON users(username);

-- 3. Regular INDEX — تسريع البحث والفلترة
CREATE INDEX idx_category ON products(category);
CREATE INDEX idx_status   ON orders(status);
CREATE INDEX idx_role     ON users(role);

-- 4. Composite INDEX — لعدة أعمدة معاً (ترتيب مهم!)
-- مثالي للاستعلامات: WHERE status='active' AND role='admin'
CREATE INDEX idx_status_role ON users(is_active, role);

-- 5. FULLTEXT INDEX — للبحث النصي الكامل
CREATE FULLTEXT INDEX ft_products ON products(name, description);

-- البحث بـ FULLTEXT
SELECT * FROM products
WHERE MATCH(name, description) AGAINST('PHP برمجة' IN BOOLEAN MODE);

-- ─── EXPLAIN — لفهم كيف MySQL تنفذ الاستعلام ────────────────
EXPLAIN SELECT * FROM users WHERE email = 'ahmed@example.com';
-- type=ref (يستخدم index) = سريع ✅
-- type=ALL (full scan)   = بطيء ❌

-- ─── متى تضيف Index؟ ────────────────────────────────────────
-- ✅ أعمدة WHERE المتكررة
-- ✅ أعمدة ORDER BY
-- ✅ أعمدة JOIN ... ON
-- ✅ أعمدة UNIQUE (email, username)
-- ❌ أعمدة نادراً ما تُفلتر (تُبطئ INSERT/UPDATE)
-- ❌ جداول صغيرة جداً (< 1000 صف) — لا فرق ملحوظ

-- ─── فحص الـ Indexes الموجودة ────────────────────────────────
SHOW INDEX FROM users;
SHOW INDEX FROM products;

-- حذف Index غير ضروري
DROP INDEX idx_old ON users;

-- ─── ❌ تصميم سيء — تكرار وبيانات مختلطة ─────────────────
CREATE TABLE orders_bad (
  id           INT PRIMARY KEY,
  user_name    VARCHAR(100),  -- تكرار! لو غيّر المستخدم اسمه؟
  user_email   VARCHAR(100),  -- تكرار!
  product_name VARCHAR(200),  -- تكرار!
  product_price DECIMAL(10,2), -- تكرار!
  quantity     INT,
  categories   VARCHAR(500)   -- '1,2,3' في خلية واحدة = ❌ 1NF
);

-- ─── ✅ تصميم صحيح — Normalized ────────────────────────────
-- جدول المستخدمين (بيانات واحدة لكل مستخدم)
CREATE TABLE users (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50)  NOT NULL UNIQUE,
  email    VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- جدول الفئات (مستقل)
CREATE TABLE categories (
  id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  slug VARCHAR(100) NOT NULL UNIQUE
);

-- جدول المنتجات (Foreign Key للفئة)
CREATE TABLE products (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NOT NULL,
  name        VARCHAR(200) NOT NULL,
  price       DECIMAL(10,2) NOT NULL,
  stock       INT UNSIGNED DEFAULT 0,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- جدول الطلبات (Foreign Key للمستخدم)
CREATE TABLE orders (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  status     ENUM('pending','paid','shipped','delivered') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- جدول تفاصيل الطلب (Many-to-Many بين orders وproducts)
CREATE TABLE order_items (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity   INT UNSIGNED NOT NULL DEFAULT 1,
  unit_price DECIMAL(10,2) NOT NULL,  -- السعر وقت الشراء
  FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- ─── الاستعلام الكامل مع JOIN ─────────────────────────────
SELECT
    o.id           AS order_id,
    u.username,
    u.email,
    p.name         AS product_name,
    c.name         AS category,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS subtotal
FROM orders o
JOIN users       u  ON o.user_id    = u.id
JOIN order_items oi ON o.id         = oi.order_id
JOIN products    p  ON oi.product_id = p.id
JOIN categories  c  ON p.category_id = c.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC;

-- ─── ❌ أخطاء شائعة تُبطئ الاستعلامات ─────────────────────

-- 1. SELECT * بدل الأعمدة المطلوبة
SELECT * FROM users;               -- ❌
SELECT id, username, email FROM users; -- ✅

-- 2. استخدام دالة على عمود مفهرس (يُعطّل الـ Index)
SELECT * FROM users WHERE YEAR(created_at) = 2024; -- ❌
SELECT * FROM users
  WHERE created_at >= '2024-01-01'
    AND created_at <  '2025-01-01'; -- ✅

-- 3. LIKE يبدأ بـ % (يُعطّل الـ Index)
SELECT * FROM products WHERE name LIKE '%كتاب%'; -- ❌ Full Scan
SELECT * FROM products WHERE name LIKE 'كتاب%';  -- ✅ يستخدم Index

-- 4. N+1 Problem — استعلام لكل صف!
-- ❌ المشكلة: استعلام للطلبات ثم استعلام لكل مستخدم
$orders = $pdo->query("SELECT * FROM orders")->fetchAll();
foreach ($orders as $order) {
    $user = $pdo->query("SELECT * FROM users WHERE id = " . $order['user_id'])->fetch(); // N استعلام!
    echo $user['username'];
}

-- ✅ الحل: JOIN واحد
$orders = $pdo->query("
    SELECT o.*, u.username, u.email
    FROM orders o
    JOIN users u ON o.user_id = u.id
")->fetchAll();
foreach ($orders as $order) {
    echo $order['username']; // لا استعلامات إضافية!
}

-- ─── Caching — تخزين نتائج الاستعلامات الثقيلة ──────────
<?php
function getCachedProducts(PDO $pdo): array {
    $cache_file = sys_get_temp_dir() . '/products_cache.json';
    $cache_ttl  = 300; // 5 دقائق

    // هل الـ Cache موجود وحديث؟
    if (file_exists($cache_file) && (time() - filemtime($cache_file)) < $cache_ttl) {
        return json_decode(file_get_contents($cache_file), true);
    }

    // تنفيذ الاستعلام الثقيل
    $products = $pdo->query("
        SELECT p.*, c.name as category_name, COUNT(oi.id) as orders_count
        FROM products p
        LEFT JOIN categories c ON p.category_id = c.id
        LEFT JOIN order_items oi ON p.id = oi.product_id
        GROUP BY p.id
        ORDER BY orders_count DESC
        LIMIT 50
    ")->fetchAll();

    // حفظ في الـ Cache
    file_put_contents($cache_file, json_encode($products));

    return $products;
}
?>

<?php
// ─── migrations/001_create_users_table.php ────────────────
return [
    'up' => "
        CREATE TABLE IF NOT EXISTS users (
          id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          username   VARCHAR(50)  NOT NULL UNIQUE,
          email      VARCHAR(100) NOT NULL UNIQUE,
          password   VARCHAR(255) NOT NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",
    'down' => "DROP TABLE IF EXISTS users",
];

// ─── migrations/002_add_role_to_users.php ─────────────────
return [
    'up'   => "ALTER TABLE users ADD COLUMN role ENUM('user','admin') DEFAULT 'user' AFTER password",
    'down' => "ALTER TABLE users DROP COLUMN role",
];

// ─── migrations/003_create_products_table.php ─────────────
return [
    'up' => "
        CREATE TABLE IF NOT EXISTS products (
          id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          name        VARCHAR(200) NOT NULL,
          price       DECIMAL(10,2) NOT NULL DEFAULT 0,
          stock       INT UNSIGNED DEFAULT 0,
          category_id INT UNSIGNED NULL,
          created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          INDEX idx_category (category_id)
        ) ENGINE=InnoDB
    ",
    'down' => "DROP TABLE IF EXISTS products",
];

// ─── migrate.php — تشغيل الـ Migrations ──────────────────
require_once 'db.php';

// جدول لتتبع الـ Migrations المُطبّقة
$pdo->exec("
    CREATE TABLE IF NOT EXISTS migrations (
        id         INT AUTO_INCREMENT PRIMARY KEY,
        filename   VARCHAR(255) NOT NULL UNIQUE,
        applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
");

$applied = $pdo->query("SELECT filename FROM migrations")->fetchAll(PDO::FETCH_COLUMN);
$files   = glob('migrations/*.php');
sort($files);

foreach ($files as $file) {
    $name = basename($file);

    if (in_array($name, $applied)) {
        echo "⏩ تجاوز: $name (مُطبَّق مسبقاً)\n";
        continue;
    }

    $migration = require $file;
    try {
        $pdo->beginTransaction();
        $pdo->exec($migration['up']);
        $pdo->prepare("INSERT INTO migrations (filename) VALUES (?)")->execute([$name]);
        $pdo->commit();
        echo "✅ تطبيق: $name\n";
    } catch (Exception $e) {
        $pdo->rollBack();
        echo "❌ فشل: $name — " . $e->getMessage() . "\n";
        break;
    }
}
echo "اكتملت الـ Migrations!\n";
?>

<?php
// ─── backup.php — نسخ احتياطي لـ MySQL ───────────────────
define('DB_HOST', 'localhost');
define('DB_NAME', 'php_course_db');
define('DB_USER', 'root');
define('DB_PASS', '');
define('MYSQLDUMP_PATH', 'C:\\xampp\\mysql\\bin\\mysqldump.exe'); // Windows
// define('MYSQLDUMP_PATH', '/usr/bin/mysqldump'); // Linux/Mac

$backup_dir  = __DIR__ . '/backups/';
$backup_file = $backup_dir . DB_NAME . '_' . date('Y-m-d_H-i-s') . '.sql';

// إنشاء مجلد الـ Backup
if (!is_dir($backup_dir)) mkdir($backup_dir, 0755, true);

// أمر الـ dump
$command = sprintf(
    '"%s" --host=%s --user=%s --password=%s %s > "%s" 2>&1',
    MYSQLDUMP_PATH,
    DB_HOST,
    DB_USER,
    DB_PASS,
    DB_NAME,
    $backup_file
);

exec($command, $output, $return_code);

if ($return_code === 0) {
    $size = round(filesize($backup_file) / 1024, 2);
    echo "✅ تم الـ Backup بنجاح!\n";
    echo "الملف: $backup_file\n";
    echo "الحجم: {$size} KB\n";

    // حذف النسخ القديمة (أكثر من 7 أيام)
    foreach (glob($backup_dir . '*.sql') as $old_file) {
        if (time() - filemtime($old_file) > 7 * 24 * 3600) {
            unlink($old_file);
            echo "🗑️ حُذف: " . basename($old_file) . "\n";
        }
    }
} else {
    echo "❌ فشل الـ Backup!\n";
    echo implode("\n", $output);
}

// ─── Restore من ملف SQL ───────────────────────────────────
function restoreBackup(string $sql_file): bool {
    if (!file_exists($sql_file)) return false;

    $command = sprintf(
        '"%s" --host=%s --user=%s --password=%s %s < "%s" 2>&1',
        'C:\\xampp\\mysql\\bin\\mysql.exe',
        DB_HOST, DB_USER, DB_PASS, DB_NAME,
        $sql_file
    );

    exec($command, $output, $code);
    return $code === 0;
}
?>