<?php
/**
 * Migration Script for Static IP Support
 * This script adds Static IP type to the database schema
 */

// Include the initialization file
require_once 'init.php';

echo "<h2>Static IP Migration Script</h2>";
echo "<p>Starting migration...</p>";

try {
    // Get database connection
    $db = ORM::get_db();
    
    echo "<h3>Step 1: Adding 'Static' to type ENUM in tbl_plans</h3>";
    
    // Check current ENUM values
    $result = $db->query("SHOW COLUMNS FROM tbl_plans LIKE 'type'");
    $row = $result->fetch(PDO::FETCH_ASSOC);
    echo "<p>Current type column: " . $row['Type'] . "</p>";
    
    // Modify the ENUM to include Static
    if (strpos($row['Type'], 'Static') === false) {
        $sql = "ALTER TABLE tbl_plans MODIFY COLUMN type ENUM('Hotspot', 'PPPOE', 'Static') NOT NULL";
        $db->exec($sql);
        echo "<p style='color:green;'>✓ Successfully added 'Static' to type ENUM</p>";
    } else {
        echo "<p style='color:blue;'>ℹ 'Static' already exists in type ENUM</p>";
    }
    
    echo "<h3>Step 2: Adding static_ip column to tbl_customers</h3>";
    
    // Check if static_ip column exists
    $result = $db->query("SHOW COLUMNS FROM tbl_customers LIKE 'static_ip'");
    if ($result->rowCount() == 0) {
        $sql = "ALTER TABLE tbl_customers ADD COLUMN static_ip VARCHAR(50) DEFAULT NULL AFTER pppoe_ip";
        $db->exec($sql);
        echo "<p style='color:green;'>✓ Successfully added static_ip column</p>";
    } else {
        echo "<p style='color:blue;'>ℹ static_ip column already exists</p>";
    }
    
    echo "<h3>Step 3: Adding mac_address column to tbl_customers</h3>";
    
    // Check if mac_address column exists
    $result = $db->query("SHOW COLUMNS FROM tbl_customers LIKE 'mac_address'");
    if ($result->rowCount() == 0) {
        $sql = "ALTER TABLE tbl_customers ADD COLUMN mac_address VARCHAR(20) DEFAULT NULL AFTER static_ip";
        $db->exec($sql);
        echo "<p style='color:green;'>✓ Successfully added mac_address column</p>";
    } else {
        echo "<p style='color:blue;'>ℹ mac_address column already exists</p>";
    }
    
    echo "<h3>Step 4: Verifying pool column in tbl_plans</h3>";
    
    // Check if pool column exists
    $result = $db->query("SHOW COLUMNS FROM tbl_plans LIKE 'pool'");
    if ($result->rowCount() == 0) {
        $sql = "ALTER TABLE tbl_plans ADD COLUMN pool VARCHAR(50) DEFAULT NULL";
        $db->exec($sql);
        echo "<p style='color:green;'>✓ Successfully added pool column</p>";
    } else {
        echo "<p style='color:blue;'>ℹ pool column already exists</p>";
    }
    
    echo "<h3>Migration Summary</h3>";
    echo "<ul style='color:green;'>";
    echo "<li>✓ Database schema updated successfully</li>";
    echo "<li>✓ Static IP type added to tbl_plans</li>";
    echo "<li>✓ Customer table updated with static_ip and mac_address fields</li>";
    echo "<li>✓ System is ready for Static IP functionality</li>";
    echo "</ul>";
    
    echo "<h3>Next Steps:</h3>";
    echo "<ol>";
    echo "<li>Go to <a href='" . U . "services/static'>Internet Plan → Static IP</a></li>";
    echo "<li>Create your first Static IP plan</li>";
    echo "<li>Assign Static IPs to customers in the customer edit page</li>";
    echo "<li>Recharge customers with Static IP plans</li>";
    echo "</ol>";
    
    echo "<p><strong>You can now delete this file (migrate_static_ip.php) for security.</strong></p>";
    
} catch (Exception $e) {
    echo "<p style='color:red;'>✗ Error: " . $e->getMessage() . "</p>";
    echo "<p>Please check your database connection and try again.</p>";
}

echo "<hr>";
echo "<p><a href='" . U . "dashboard'>← Back to Dashboard</a></p>";
?>
