<?php
/**
 * migrate.php — একবার রান করুন, তারপর ডিলিট করুন
 * URL: https://demo6.dotnests.site/migrate.php
 */
require_once __DIR__ . '/config/database.php';

$results = [];

$migrations = [
    // Orders table additions
    "ALTER TABLE orders ADD COLUMN IF NOT EXISTS order_type ENUM('online','offline') NOT NULL DEFAULT 'online' AFTER status",
    "ALTER TABLE orders ADD COLUMN IF NOT EXISTS delivery_charge DECIMAL(10,2) NOT NULL DEFAULT '0.00' AFTER total_amount",

    // Courier logs
    "CREATE TABLE IF NOT EXISTS courier_logs (
        id int(11) NOT NULL AUTO_INCREMENT,
        order_id int(11) NOT NULL,
        consignment_id varchar(100) DEFAULT NULL,
        tracking_code varchar(100) DEFAULT NULL,
        courier_status varchar(50) DEFAULT NULL,
        response text DEFAULT NULL,
        created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id), KEY order_id (order_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // Coupons table
    "CREATE TABLE IF NOT EXISTS coupons (
        id int(11) NOT NULL AUTO_INCREMENT,
        code varchar(50) NOT NULL,
        discount_type ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
        discount_value decimal(10,2) NOT NULL DEFAULT '0.00',
        min_order_amount decimal(10,2) NOT NULL DEFAULT '0.00',
        max_discount decimal(10,2) NOT NULL DEFAULT '0.00',
        usage_limit int(11) NOT NULL DEFAULT '0',
        used_count int(11) NOT NULL DEFAULT '0',
        expiry_date date DEFAULT NULL,
        is_active tinyint(1) NOT NULL DEFAULT '1',
        require_login tinyint(1) NOT NULL DEFAULT '0',
        assigned_user_id int(11) DEFAULT NULL,
        created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id), UNIQUE KEY code (code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // Coupon usage tracking
    "CREATE TABLE IF NOT EXISTS coupon_usage (
        id int(11) NOT NULL AUTO_INCREMENT,
        coupon_id int(11) NOT NULL,
        user_id int(11) DEFAULT NULL,
        order_id int(11) NOT NULL,
        used_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id), KEY coupon_id (coupon_id), KEY user_id (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // Product images table
    "CREATE TABLE IF NOT EXISTS product_images (
        id int(11) NOT NULL AUTO_INCREMENT,
        product_id int(11) NOT NULL,
        image varchar(255) NOT NULL,
        sort_order int(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (id), KEY product_id (product_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // Payment methods
    "CREATE TABLE IF NOT EXISTS payment_methods (
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(100) NOT NULL,
        number varchar(50) NOT NULL,
        instructions text DEFAULT NULL,
        is_active tinyint(1) NOT NULL DEFAULT '1',
        sort_order int(11) NOT NULL DEFAULT '0',
        icon varchar(50) DEFAULT 'fa-mobile-alt',
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // Settings for new features
    "INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
        ('steadfast_api_key',''),
        ('steadfast_api_secret',''),
        ('steadfast_enabled','0'),
        ('fb_pixel_id',''),
        ('fb_pixel_enabled','0')",

    // Default payment methods
    "INSERT IGNORE INTO payment_methods (name, number, instructions, is_active, sort_order, icon) VALUES
        ('বিকাশ', '01XXXXXXXXX', 'বিকাশ অ্যাপ থেকে Send Money করুন। Reference এ অর্ডার নম্বর লিখুন।', 1, 1, 'fa-mobile-alt'),
        ('নগদ',   '01XXXXXXXXX', 'নগদ অ্যাপ থেকে Send Money করুন। Reference এ অর্ডার নম্বর লিখুন।', 1, 2, 'fa-wallet'),
        ('রকেট',  '01XXXXXXXXX', 'রকেট অ্যাপ থেকে Send Money করুন। Reference এ অর্ডার নম্বর লিখুন।', 1, 3, 'fa-rocket')",

    // users share_percent column
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS share_percent DECIMAL(5,2) NOT NULL DEFAULT '0.00' AFTER role",
];

foreach ($migrations as $sql) {
    try {
        $pdo->exec($sql);
        $results[] = ['ok', substr(trim($sql),0,80).'...'];
    } catch(PDOException $e) {
        $results[] = ['err', substr(trim($sql),0,80).'... → '.$e->getMessage()];
    }
}
?>
<!DOCTYPE html>
<html lang="bn"><head><meta charset="UTF-8"><title>Migration</title>
<style>body{font-family:sans-serif;padding:24px;max-width:800px;margin:0 auto}.ok{color:#276749;background:#f0fff4;border-left:3px solid #38a169;padding:8px 12px;margin:4px 0;font-size:.85rem}.err{color:#c53030;background:#fff5f5;border-left:3px solid #e53e3e;padding:8px 12px;margin:4px 0;font-size:.85rem}h2{color:#1A2A4F}.done{background:#D4AF37;color:#1A2A4F;padding:16px 24px;border-radius:8px;font-weight:700;font-size:1.1rem;margin-top:20px;display:inline-block}</style>
</head><body>
<h2>🔧 Database Migration</h2>
<?php foreach($results as [$status,$msg]): ?>
<div class="<?=$status?>"><strong><?=$status==='ok'?'✓':'✗'?></strong> <?=htmlspecialchars($msg)?></div>
<?php endforeach; ?>
<div class="done">✅ Migration সম্পন্ন! এই ফাইলটি এখনই সার্ভার থেকে মুছে দিন।</div>
<p style="margin-top:16px;color:#6b6860;font-size:.9rem;">⚠️ নিরাপত্তার জন্য এই ফাইলটি ব্যবহারের পর অবশ্যই সার্ভার থেকে ডিলিট করুন।</p>
</body></html>
