<?php
/**
 * Migration Script: Split pricing range 0.10GB - 9.90GB into two ranges:
 * 1. 0.1GB - 4.99GB
 * 2. 5GB - 9.99GB
 * 
 * This script will:
 * 1. Find the existing range 0.10GB - 9.90GB (or 0.1GB - 9.9GB) for each network
 * 2. Preserve the price_per_gb value
 * 3. Delete the old range
 * 4. Create two new ranges with the same price_per_gb (admin can adjust later)
 */

require_once 'config.php';

echo "Starting pricing range split migration...\n\n";

// Start transaction
$conn->begin_transaction();

try {
    // Find all ranges that match 0.1-9.9 or 0.10-9.90 (with some tolerance for floating point)
    $findStmt = $conn->prepare("
        SELECT id, network_type, min_size_gb, max_size_gb, price_per_gb, is_active
        FROM agent_pricing_rules 
        WHERE role = 'agent'
        AND (
            (ABS(min_size_gb - 0.1) < 0.01 AND ABS(max_size_gb - 9.9) < 0.01)
            OR (ABS(min_size_gb - 0.10) < 0.01 AND ABS(max_size_gb - 9.90) < 0.01)
        )
    ");
    $findStmt->execute();
    $result = $findStmt->get_result();
    $rangesToSplit = [];
    
    while ($row = $result->fetch_assoc()) {
        $rangesToSplit[] = $row;
    }
    $findStmt->close();
    
    if (empty($rangesToSplit)) {
        echo "No ranges found matching 0.1GB - 9.9GB. Nothing to split.\n";
        $conn->rollback();
        exit(0);
    }
    
    echo "Found " . count($rangesToSplit) . " range(s) to split:\n";
    foreach ($rangesToSplit as $range) {
        echo "  - Network: {$range['network_type']}, Range: {$range['min_size_gb']}GB - {$range['max_size_gb']}GB, Price: GH₵{$range['price_per_gb']}/GB\n";
    }
    echo "\n";
    
    // Process each range
    foreach ($rangesToSplit as $range) {
        $networkType = $range['network_type'];
        $oldPricePerGb = $range['price_per_gb'];
        $isActive = $range['is_active'];
        $oldId = $range['id'];
        
        echo "Processing {$networkType} network...\n";
        
        // Check if the new ranges already exist
        $checkStmt = $conn->prepare("
            SELECT id FROM agent_pricing_rules 
            WHERE network_type = ? 
            AND role = 'agent'
            AND (
                (ABS(min_size_gb - 0.1) < 0.01 AND ABS(max_size_gb - 4.99) < 0.01)
                OR (ABS(min_size_gb - 5.0) < 0.01 AND ABS(max_size_gb - 9.99) < 0.01)
            )
        ");
        $checkStmt->bind_param("s", $networkType);
        $checkStmt->execute();
        $checkResult = $checkStmt->get_result();
        
        if ($checkResult->num_rows > 0) {
            echo "  ⚠ Warning: New ranges already exist for {$networkType}. Skipping...\n";
            $checkStmt->close();
            continue;
        }
        $checkStmt->close();
        
        // Delete the old range
        $deleteStmt = $conn->prepare("DELETE FROM agent_pricing_rules WHERE id = ?");
        $deleteStmt->bind_param("i", $oldId);
        if ($deleteStmt->execute()) {
            echo "  ✓ Deleted old range (ID: {$oldId})\n";
        } else {
            throw new Exception("Failed to delete old range: " . $deleteStmt->error);
        }
        $deleteStmt->close();
        
        // Create first range: 0.1GB - 4.99GB
        $insert1Stmt = $conn->prepare("
            INSERT INTO agent_pricing_rules 
            (network_type, role, min_size_gb, max_size_gb, price_per_gb, is_active, created_at) 
            VALUES (?, 'agent', 0.1, 4.99, ?, ?, NOW())
        ");
        $insert1Stmt->bind_param("sdi", $networkType, $oldPricePerGb, $isActive);
        if ($insert1Stmt->execute()) {
            $newId1 = $conn->insert_id;
            echo "  ✓ Created range 1: 0.1GB - 4.99GB (ID: {$newId1}, Price: GH₵{$oldPricePerGb}/GB)\n";
        } else {
            throw new Exception("Failed to create first range: " . $insert1Stmt->error);
        }
        $insert1Stmt->close();
        
        // Create second range: 5GB - 9.99GB
        $insert2Stmt = $conn->prepare("
            INSERT INTO agent_pricing_rules 
            (network_type, role, min_size_gb, max_size_gb, price_per_gb, is_active, created_at) 
            VALUES (?, 'agent', 5.0, 9.99, ?, ?, NOW())
        ");
        $insert2Stmt->bind_param("sdi", $networkType, $oldPricePerGb, $isActive);
        if ($insert2Stmt->execute()) {
            $newId2 = $conn->insert_id;
            echo "  ✓ Created range 2: 5GB - 9.99GB (ID: {$newId2}, Price: GH₵{$oldPricePerGb}/GB)\n";
        } else {
            throw new Exception("Failed to create second range: " . $insert2Stmt->error);
        }
        $insert2Stmt->close();
        
        echo "\n";
    }
    
    // Commit transaction
    $conn->commit();
    echo "✓ Migration completed successfully!\n";
    echo "\nNote: Both new ranges have been created with the same price per GB as the original range.\n";
    echo "You can adjust the prices individually in the admin panel if needed.\n";
    
} catch (Exception $e) {
    $conn->rollback();
    echo "✗ Error: " . $e->getMessage() . "\n";
    echo "Transaction rolled back. No changes were made.\n";
    exit(1);
}

$conn->close();
?>

