<?php
// util/run_migration.php
require_once __DIR__ . '/../config.php';

header('Content-Type: text/plain');

if (!$conn) {
    die("Database connection failed. Check config.php.\n");
}

echo "Starting Database Migration...\n";

// Read SQL file
$sqlFile = __DIR__ . '/migrate_subscription_system.sql';
if (!file_exists($sqlFile)) {
    die("Error: SQL file not found at $sqlFile\n");
}

$sqlContent = file_get_contents($sqlFile);

// Remove comments to check for empty lines if needed, but mysqli_multi_query handles comments fine usually.
// However, the DELIMITER syntax is client-side (MySQL Workbench/CLI), not server-side.
// We need to parse this manually or simplify the SQL to not use Procedures if we can avoid it.
// Simpler approach for PHP execution: Direct ALTERs with try-catch logic instead of Stored Procedures.

echo "Executing SQL commands...\n";

// We'll execute creating tables first
$tablesSql = "
CREATE TABLE IF NOT EXISTS subscription_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    token_amount INT NOT NULL,
    description TEXT,
    duration_days INT DEFAULT 30,
    is_active TINYINT(1) DEFAULT 1,
    is_featured TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS token_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    package_id INT NULL,
    amount_paid DECIMAL(10,2) NOT NULL,
    tokens_requested INT NOT NULL,
    currency VARCHAR(10) DEFAULT 'KES',
    payment_method VARCHAR(50) DEFAULT 'manual',
    payment_proof VARCHAR(255) NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    admin_notes TEXT,
    reviewed_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
";

if ($conn->multi_query($tablesSql)) {
    do {
        if ($result = $conn->store_result())
            $result->free();
    } while ($conn->more_results() && $conn->next_result());
    echo "Tables created/verified.\n";
} else {
    echo "Error creating tables: " . $conn->error . "\n";
}

// Now handle ALTERs safely
function safe_alter($conn, $sql)
{
    try {
        $conn->query($sql);
        echo "Executed: " . substr($sql, 0, 50) . "...\n";
    } catch (Exception $e) {
        // 1060 = Duplicate column name
        if ($conn->errno == 1060) {
            echo "Skipped (Column exists): " . substr($sql, 0, 50) . "...\n";
        } else {
            echo "Error: " . $conn->error . "\n";
        }
    }
}

safe_alter($conn, "ALTER TABLE users ADD COLUMN is_enterprise TINYINT(1) DEFAULT 0");
safe_alter($conn, "ALTER TABLE users ADD COLUMN phone_number VARCHAR(20) NULL");
safe_alter($conn, "ALTER TABLE token_transactions ADD COLUMN reference_id VARCHAR(100) NULL COMMENT 'Internal Order ID or Ref'");
safe_alter($conn, "ALTER TABLE token_transactions ADD COLUMN transaction_ref VARCHAR(100) NULL COMMENT 'External Payment Ref'");

// Insert Defaults
$check = $conn->query("SELECT COUNT(*) as count FROM subscription_packages");
$row = $check->fetch_assoc();
if ($row['count'] == 0) {
    echo "Inserting default packages...\n";
    $conn->query("INSERT INTO subscription_packages (name, price, token_amount, description, is_active, is_featured) VALUES 
        ('Starter Bundle', 1000.00, 1500, 'Perfect for getting started.', 1, 0),
        ('Pro Bundle', 5000.00, 8000, 'Best value for frequent contributors.', 1, 1),
        ('Enterprise Pack', 20000.00, 35000, 'Maximum tokens for large organizations.', 1, 0)
    ");
}

echo "Migration Complete.\n";
?>