<?php
/**
 * Safe Database Migration Script
 * - Can be run multiple times without causing errors
 * - Does NOT delete or modify existing data
 * - Uses IF NOT EXISTS for all operations
 * - Safe to run on production with active users
 *
 * Access: http://yourdomain.com/uhakikimadalali/migrate_safe.php
 * Delete this file after running!
 */

// Prevent timeout for large databases
set_time_limit(300);

require_once 'db_connect.php';

// Disable strict error reporting for ALTER statements
mysqli_report(MYSQLI_REPORT_OFF);

echo "<!DOCTYPE html><html><head><title>Safe Database Migration</title>";
echo "<style>
body { font-family: 'Segoe UI', sans-serif; max-width: 800px; margin: 40px auto; padding: 20px; background: #f5f5f5; }
.container { background: white; padding: 30px; border-radius: 12px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
h1 { color: #1e3a5f; margin-bottom: 20px; }
.success { color: #22c55e; margin: 8px 0; }
.skip { color: #64748b; margin: 8px 0; }
.error { color: #ef4444; margin: 8px 0; }
.info { color: #3b82f6; margin: 8px 0; }
pre { background: #f8fafc; padding: 15px; border-radius: 8px; overflow-x: auto; font-size: 13px; }
.warning { background: #fef3c7; border: 1px solid #f59e0b; padding: 15px; border-radius: 8px; margin: 20px 0; }
.btn { display: inline-block; padding: 12px 24px; background: #22c55e; color: white; text-decoration: none; border-radius: 8px; margin-top: 20px; }
</style></head><body><div class='container'>";

echo "<h1>🔄 Safe Database Migration</h1>";
echo "<p>Running migration without affecting existing users...</p><hr>";

$success = [];
$skipped = [];
$errors = [];

// Helper function to check if table exists
function tableExists($conn, $tableName) {
    $result = mysqli_query($conn, "SHOW TABLES LIKE '$tableName'");
    return mysqli_num_rows($result) > 0;
}

// Helper function to check if column exists
function columnExists($conn, $tableName, $columnName) {
    $result = mysqli_query($conn, "SHOW COLUMNS FROM `$tableName` LIKE '$columnName'");
    return $result && mysqli_num_rows($result) > 0;
}

// Helper function to safely add column
function safeAddColumn($conn, $table, $column, $definition, &$success, &$skipped) {
    if (!columnExists($conn, $table, $column)) {
        $sql = "ALTER TABLE `$table` ADD COLUMN `$column` $definition";
        if (mysqli_query($conn, $sql)) {
            $success[] = "Added column `$column` to `$table`";
            return true;
        }
    } else {
        $skipped[] = "Column `$column` already exists in `$table`";
    }
    return false;
}

echo "<h3>📊 Creating Tables...</h3>";

// 1. satellite_monitoring table
$sql = "CREATE TABLE IF NOT EXISTS satellite_monitoring (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL,
    monitoring_status ENUM('active', 'paused', 'disabled') DEFAULT 'active',
    monitoring_frequency ENUM('daily', 'weekly', 'monthly') DEFAULT 'monthly',
    last_check_date DATETIME NULL,
    next_check_date DATETIME NULL,
    baseline_image_date DATE NULL,
    baseline_ndvi DECIMAL(5,4) NULL,
    satellite_source ENUM('sentinel2', 'landsat8', 'planet', 'combined') DEFAULT 'sentinel2',
    alert_on_vegetation_loss TINYINT(1) DEFAULT 1,
    alert_on_new_structure TINYINT(1) DEFAULT 1,
    alert_on_boundary_change TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_land_monitoring (land_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    if (!tableExists($conn, 'satellite_monitoring')) {
        $success[] = "Created table `satellite_monitoring`";
    } else {
        $skipped[] = "Table `satellite_monitoring` already exists";
    }
}

// 2. satellite_changes table
$sql = "CREATE TABLE IF NOT EXISTS satellite_changes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL,
    detection_date DATETIME NOT NULL,
    change_type ENUM('vegetation_loss', 'vegetation_gain', 'new_structure', 'land_clearing', 'water_change', 'soil_disturbance', 'boundary_encroachment', 'fire_damage', 'flooding', 'erosion', 'unknown') NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    affected_area_sqm DECIMAL(12,2) NULL,
    affected_percentage DECIMAL(5,2) NULL,
    change_center_lat DECIMAL(10,8) NULL,
    change_center_lng DECIMAL(11,8) NULL,
    change_polygon JSON NULL,
    confidence_score DECIMAL(5,2) NULL,
    ndvi_before DECIMAL(5,4) NULL,
    ndvi_after DECIMAL(5,4) NULL,
    ndvi_change DECIMAL(5,4) NULL,
    before_image_date DATE NULL,
    after_image_date DATE NULL,
    before_image_url VARCHAR(500) NULL,
    after_image_url VARCHAR(500) NULL,
    difference_image_url VARCHAR(500) NULL,
    analysis_report_url VARCHAR(500) NULL,
    status ENUM('detected', 'pending_review', 'reviewed', 'confirmed', 'false_positive', 'resolved') DEFAULT 'detected',
    auto_classified TINYINT(1) DEFAULT 1,
    reviewed_by INT NULL,
    reviewed_at DATETIME NULL,
    review_notes TEXT NULL,
    resolved_by INT NULL,
    resolved_at DATETIME NULL,
    resolution_notes TEXT NULL,
    alert_sent TINYINT(1) DEFAULT 0,
    alert_sent_at DATETIME NULL,
    alert_method VARCHAR(50) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_land_date (land_id, detection_date),
    INDEX idx_change_type (change_type),
    INDEX idx_severity (severity),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    $skipped[] = "Table `satellite_changes` ready";
}

// 3. satellite_images table
$sql = "CREATE TABLE IF NOT EXISTS satellite_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL,
    image_date DATE NOT NULL,
    satellite_source ENUM('sentinel2', 'landsat8', 'planet', 'google_earth', 'maxar') NOT NULL,
    image_type ENUM('rgb', 'ndvi', 'infrared', 'radar', 'composite') NOT NULL,
    image_url VARCHAR(500) NULL,
    thumbnail_url VARCHAR(500) NULL,
    image_width INT NULL,
    image_height INT NULL,
    cloud_coverage DECIMAL(5,2) NULL,
    resolution_meters DECIMAL(6,2) NULL,
    quality_score DECIMAL(5,2) NULL,
    mean_ndvi DECIMAL(5,4) NULL,
    min_ndvi DECIMAL(5,4) NULL,
    max_ndvi DECIMAL(5,4) NULL,
    vegetation_coverage DECIMAL(5,2) NULL,
    built_up_area DECIMAL(5,2) NULL,
    water_area DECIMAL(5,2) NULL,
    bare_soil_area DECIMAL(5,2) NULL,
    metadata JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_land_date (land_id, image_date),
    INDEX idx_source_type (satellite_source, image_type),
    UNIQUE KEY unique_image (land_id, image_date, satellite_source, image_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    $skipped[] = "Table `satellite_images` ready";
}

// 4. satellite_alerts table
$sql = "CREATE TABLE IF NOT EXISTS satellite_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    change_id INT NULL,
    land_id INT NOT NULL,
    user_id INT NOT NULL,
    alert_type ENUM('sms', 'email', 'push', 'in_app', 'whatsapp') NOT NULL,
    alert_status ENUM('pending', 'sent', 'delivered', 'failed', 'read') DEFAULT 'pending',
    recipient_phone VARCHAR(20) NULL,
    recipient_email VARCHAR(100) NULL,
    message_title VARCHAR(200) NOT NULL,
    message_body TEXT NOT NULL,
    sent_at DATETIME NULL,
    delivered_at DATETIME NULL,
    read_at DATETIME NULL,
    error_message TEXT NULL,
    retry_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_status (user_id, alert_status),
    INDEX idx_pending (alert_status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    $skipped[] = "Table `satellite_alerts` ready";
}

// 5. satellite_api_usage table
$sql = "CREATE TABLE IF NOT EXISTS satellite_api_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation VARCHAR(50) NOT NULL,
    units_used INT DEFAULT 1,
    land_id INT NULL,
    response_status VARCHAR(20) NULL,
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_operation (operation),
    INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    $skipped[] = "Table `satellite_api_usage` ready";
}

// 6. land_verification_logs table
$sql = "CREATE TABLE IF NOT EXISTS land_verification_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    verified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_land_id (land_id),
    INDEX idx_verified_at (verified_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    $skipped[] = "Table `land_verification_logs` ready";
}

// 7. location_scan_logs table
$sql = "CREATE TABLE IF NOT EXISTS location_scan_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    latitude DECIMAL(10,8) NOT NULL,
    longitude DECIMAL(11,8) NOT NULL,
    ip_address VARCHAR(45) NULL,
    lands_found INT DEFAULT 0,
    scanned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_coords (latitude, longitude),
    INDEX idx_scanned_at (scanned_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    $skipped[] = "Table `location_scan_logs` ready";
}

echo "<h3>📝 Adding Columns to Existing Tables...</h3>";

// Add columns to lands table
if (tableExists($conn, 'lands')) {
    safeAddColumn($conn, 'lands', 'satellite_monitoring_enabled', "TINYINT(1) DEFAULT 0", $success, $skipped);
    safeAddColumn($conn, 'lands', 'last_satellite_check', "DATETIME NULL", $success, $skipped);
    safeAddColumn($conn, 'lands', 'satellite_alerts_count', "INT DEFAULT 0", $success, $skipped);
    safeAddColumn($conn, 'lands', 'verification_code', "VARCHAR(50) NULL", $success, $skipped);
    safeAddColumn($conn, 'lands', 'qr_code', "VARCHAR(100) NULL", $success, $skipped);
}

// Add columns to cbc_notifications table
if (tableExists($conn, 'cbc_notifications')) {
    safeAddColumn($conn, 'cbc_notifications', 'metadata', "JSON NULL", $success, $skipped);
    safeAddColumn($conn, 'cbc_notifications', 'read_at', "DATETIME NULL", $success, $skipped);
}

echo "<h3>⚙️ Adding Settings...</h3>";

// Check if payment_settings table exists
if (tableExists($conn, 'payment_settings')) {
    $settings = [
        ['satellite_monitoring_enabled', '1'],
        ['satellite_default_frequency', 'monthly'],
        ['satellite_ndvi_change_threshold', '0.15'],
        ['satellite_planet_api_key', ''],
        ['satellite_sentinel_api_key', '']
    ];

    foreach ($settings as $setting) {
        $key = mysqli_real_escape_string($conn, $setting[0]);
        $value = mysqli_real_escape_string($conn, $setting[1]);

        // Check if setting exists
        $check = mysqli_query($conn, "SELECT id FROM payment_settings WHERE setting_key = '$key'");
        if ($check && mysqli_num_rows($check) == 0) {
            mysqli_query($conn, "INSERT INTO payment_settings (setting_key, setting_value) VALUES ('$key', '$value')");
            $success[] = "Added setting `$key`";
        } else {
            $skipped[] = "Setting `$key` already exists";
        }
    }
}

echo "<h3>🔑 Generating Verification Codes...</h3>";

// Generate verification codes for lands without one
if (tableExists($conn, 'lands') && columnExists($conn, 'lands', 'verification_code')) {
    $result = mysqli_query($conn, "SELECT id FROM lands WHERE verification_code IS NULL OR verification_code = ''");
    $count = 0;

    if ($result) {
        while ($land = mysqli_fetch_assoc($result)) {
            $code = 'LS' . strtoupper(substr(md5($land['id'] . microtime() . rand()), 0, 8));
            mysqli_query($conn, "UPDATE lands SET verification_code = '$code' WHERE id = {$land['id']} AND (verification_code IS NULL OR verification_code = '')");
            $count++;
        }
    }

    if ($count > 0) {
        $success[] = "Generated verification codes for $count lands";
    } else {
        $skipped[] = "All lands already have verification codes";
    }
}

// Display Results
echo "<h3>✅ Migration Results</h3>";

if (!empty($success)) {
    echo "<h4 style='color: #22c55e;'>Successful Changes (" . count($success) . "):</h4>";
    foreach ($success as $msg) {
        echo "<p class='success'>✓ $msg</p>";
    }
}

if (!empty($skipped)) {
    echo "<h4 style='color: #64748b;'>Skipped (Already Exists) (" . count($skipped) . "):</h4>";
    foreach ($skipped as $msg) {
        echo "<p class='skip'>○ $msg</p>";
    }
}

if (!empty($errors)) {
    echo "<h4 style='color: #ef4444;'>Errors (" . count($errors) . "):</h4>";
    foreach ($errors as $msg) {
        echo "<p class='error'>✗ $msg</p>";
    }
}

echo "<hr>";
echo "<p class='info'><strong>Migration completed at:</strong> " . date('Y-m-d H:i:s') . "</p>";

if (empty($errors)) {
    echo "<div class='warning'>
        <strong>⚠️ Security Notice:</strong><br>
        Please delete this file (<code>migrate_safe.php</code>) from your server after migration is complete.
    </div>";
    echo "<a href='dashboard' class='btn'>Go to Dashboard</a>";
} else {
    echo "<div class='warning' style='background: #fee2e2; border-color: #ef4444;'>
        <strong>⚠️ Some errors occurred.</strong><br>
        Please review the errors above and contact support if needed.
    </div>";
}

echo "</div></body></html>";

mysqli_close($conn);
?>
