<?php
require_once '../includes/config.php';
requireLogin();
$user = getCurrentUser();
$db   = getDB();

// Auto-create package_id column if missing
try { $db->exec("ALTER TABLE users ADD COLUMN package_id INT DEFAULT 1"); } catch(Exception $e) {}

$input = json_decode(file_get_contents('php://input'), true);
$pkgId = intval($input['package_id'] ?? 0);

if (!$pkgId) jsonResponse(false, 'Invalid package ID');

// Get target package
$stmt = $db->prepare("SELECT * FROM user_packages WHERE id=? AND is_active=1");
$stmt->execute([$pkgId]);
$pkg = $stmt->fetch();
if (!$pkg) jsonResponse(false, 'Package not found');

// Get current package_id
$curStmt = $db->prepare("SELECT COALESCE(package_id,1) as package_id FROM users WHERE id=?");
$curStmt->execute([$user['id']]);
$curRow = $curStmt->fetch();
$curPkgId = intval($curRow['package_id'] ?? 1);

if ($curPkgId === $pkgId) {
    jsonResponse(false, 'You already have this package!');
}

$fee = floatval($pkg['upgrade_fee'] ?? 0);

// Check balance
$freshBalance = $db->prepare("SELECT balance FROM users WHERE id=?");
$freshBalance->execute([$user['id']]);
$balance = floatval($freshBalance->fetchColumn());

if ($fee > 0 && $balance < $fee) {
    jsonResponse(false, 'Insufficient balance! Required: ৳'.number_format($fee,2).' | Your balance: ৳'.number_format($balance,2));
}

// Get current package name for log
$curNameStmt = $db->prepare("SELECT name FROM user_packages WHERE id=?");
$curNameStmt->execute([$curPkgId]);
$curName = $curNameStmt->fetchColumn() ?: 'Basic';

try {
    $db->beginTransaction();

    if ($fee > 0) {
        // Deduct fee
        $db->prepare("UPDATE users SET balance=balance-? WHERE id=?")->execute([$fee, $user['id']]);
        $newBal = $balance - $fee;
        $ref = 'PKG'.strtoupper(substr(uniqid(),7));
        $db->prepare("INSERT INTO transactions (user_id,type,amount,charge,net_amount,balance_before,balance_after,reference,status,note) VALUES (?,?,?,0,?,?,?,?,?,?)")
           ->execute([$user['id'],'package_fee',$fee,$fee,$balance,$newBal,$ref,'success',
                      'Package upgrade: '.$curName.' → '.$pkg['name']]);
    }

    // Update package
    $db->prepare("UPDATE users SET package_id=? WHERE id=?")->execute([$pkgId, $user['id']]);

    // Notification
    $db->prepare("INSERT INTO notifications (user_id,title,message,type) VALUES (?,?,?,'success')")
       ->execute([$user['id'], '🎯 Package Updated!',
                  'Package changed to '.$pkg['name'].($fee>0 ? ' | Fee: ৳'.number_format($fee,2) : ' (Free)')]);

    $db->commit();

    jsonResponse(true, 'Package updated to '.$pkg['name'].'!', [
        'package_name'  => $pkg['name'],
        'package_color' => $pkg['color'],
        'fee_charged'   => $fee,
        'new_balance'   => $fee > 0 ? ($balance - $fee) : $balance,
    ]);

} catch(Exception $e) {
    $db->rollBack();
    jsonResponse(false, 'Error: '.$e->getMessage());
}
