<?php
/**
 * Full Database Migration Script
 * Safely migrates from old database to new database structure
 * - Adds missing tables
 * - Adds missing columns to existing tables
 * - Preserves all existing data
 * - Can be run multiple times (idempotent)
 *
 * Access: http://yourdomain.com/uhakikimadalali/migrate_full.php
 * DELETE THIS FILE AFTER RUNNING!
 */

set_time_limit(600);
ini_set('memory_limit', '256M');

require_once 'db_connect.php';

mysqli_report(MYSQLI_REPORT_OFF);

echo "<!DOCTYPE html><html><head><title>Full Database Migration</title>";
echo "<style>
body { font-family: 'Segoe UI', sans-serif; max-width: 900px; 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; }
h2 { color: #334155; border-bottom: 2px solid #e2e8f0; padding-bottom: 10px; margin-top: 30px; }
.success { color: #22c55e; margin: 5px 0; font-size: 14px; }
.skip { color: #64748b; margin: 5px 0; font-size: 14px; }
.error { color: #ef4444; margin: 5px 0; font-size: 14px; }
.info { color: #3b82f6; margin: 5px 0; }
pre { background: #f8fafc; padding: 10px; border-radius: 6px; font-size: 12px; overflow-x: auto; }
.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: 10px 5px 10px 0; }
.section { background: #f8fafc; padding: 15px; border-radius: 8px; margin: 15px 0; }
</style></head><body><div class='container'>";

echo "<h1>🔄 Full Database Migration</h1>";
echo "<p>Migrating database to new structure without affecting existing data...</p><hr>";

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

// Helper functions
function tableExists($conn, $tableName) {
    $result = mysqli_query($conn, "SHOW TABLES LIKE '$tableName'");
    return $result && mysqli_num_rows($result) > 0;
}

function columnExists($conn, $tableName, $columnName) {
    $result = mysqli_query($conn, "SHOW COLUMNS FROM `$tableName` LIKE '$columnName'");
    return $result && mysqli_num_rows($result) > 0;
}

function indexExists($conn, $tableName, $indexName) {
    $result = mysqli_query($conn, "SHOW INDEX FROM `$tableName` WHERE Key_name = '$indexName'");
    return $result && mysqli_num_rows($result) > 0;
}

function safeAddColumn($conn, $table, $column, $definition, &$success, &$skipped, &$errors) {
    if (!tableExists($conn, $table)) {
        $skipped[] = "Table `$table` doesn't exist, skipping column `$column`";
        return false;
    }
    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 {
            $errors[] = "Failed to add `$column` to `$table`: " . mysqli_error($conn);
        }
    } else {
        $skipped[] = "Column `$column` already exists in `$table`";
    }
    return false;
}

function safeCreateTable($conn, $tableName, $sql, &$success, &$skipped, &$errors) {
    if (!tableExists($conn, $tableName)) {
        if (mysqli_query($conn, $sql)) {
            $success[] = "Created table `$tableName`";
            return true;
        } else {
            $errors[] = "Failed to create `$tableName`: " . mysqli_error($conn);
        }
    } else {
        $skipped[] = "Table `$tableName` already exists";
    }
    return false;
}

// ============================================================
// SECTION 1: NEW TABLES
// ============================================================
echo "<h2>📊 Creating New Tables</h2><div class='section'>";

// 1. villages table (if not exists - may need different structure)
safeCreateTable($conn, 'villages', "
CREATE TABLE IF NOT EXISTS `villages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `district_id` int(11) NOT NULL,
  `village_name` varchar(100) NOT NULL,
  `mtaa_name` varchar(100) DEFAULT NULL,
  `ward_name` varchar(100) DEFAULT NULL,
  `chairman_user_id` int(11) DEFAULT NULL,
  `secretary_user_id` int(11) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `total_lands` int(11) DEFAULT 0,
  `verified_lands` int(11) DEFAULT 0,
  `added_by` int(11) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `district_id` (`district_id`),
  KEY `chairman_user_id` (`chairman_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 2. cbc_notifications table
safeCreateTable($conn, 'cbc_notifications', "
CREATE TABLE IF NOT EXISTS `cbc_notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `notification_type` enum('neighbor_confirmation_request','neighbor_confirmed','neighbor_disputed','village_review_pending','village_approved','village_rejected','surveyor_assigned','survey_complete','certificate_ready','dispute_raised','dispute_update','dispute_resolved','beacon_movement','land_nearby_registered','satellite_alert','conflict_detected') NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `land_id` int(11) DEFAULT NULL,
  `dispute_id` int(11) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `read_at` timestamp NULL DEFAULT NULL,
  `push_sent` tinyint(1) DEFAULT 0,
  `sms_sent` tinyint(1) DEFAULT 0,
  `email_sent` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `metadata` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `land_id` (`land_id`),
  KEY `is_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 3. cbc_verifications table
safeCreateTable($conn, 'cbc_verifications', "
CREATE TABLE IF NOT EXISTS `cbc_verifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `land_id` int(11) NOT NULL,
  `request_number` varchar(50) DEFAULT NULL,
  `requested_by` int(11) NOT NULL,
  `village_id` int(11) DEFAULT NULL,
  `status` enum('pending','accepted','in_progress','approved','rejected','cancelled') DEFAULT 'pending',
  `verification_date` datetime DEFAULT NULL,
  `verified_by` int(11) DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `land_id` (`land_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 4. verification_requests table
safeCreateTable($conn, 'verification_requests', "
CREATE TABLE IF NOT EXISTS `verification_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_number` varchar(50) NOT NULL,
  `land_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `village_id` int(11) DEFAULT NULL,
  `assigned_chairman` int(11) DEFAULT NULL,
  `status` enum('pending','accepted','in_progress','site_visit_scheduled','site_visit_completed','neighbors_notified','neighbors_confirmed','approved','rejected','cancelled') DEFAULT 'pending',
  `priority` enum('normal','high','urgent') DEFAULT 'normal',
  `request_notes` text DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `site_visit_date` datetime DEFAULT NULL,
  `site_visit_notes` text DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_number` (`request_number`),
  KEY `land_id` (`land_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 5. land_neighbors table
safeCreateTable($conn, 'land_neighbors', "
CREATE TABLE IF NOT EXISTS `land_neighbors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `land_id` int(11) NOT NULL,
  `neighbor_name` varchar(255) NOT NULL,
  `neighbor_phone` varchar(20) DEFAULT NULL,
  `neighbor_email` varchar(255) DEFAULT NULL,
  `direction` enum('north','south','east','west','northeast','northwest','southeast','southwest') DEFAULT NULL,
  `neighbor_land_id` int(11) DEFAULT NULL,
  `relationship` varchar(100) DEFAULT NULL,
  `confirmation_code` varchar(50) DEFAULT NULL,
  `status` enum('pending','confirmed','disputed','expired') DEFAULT 'pending',
  `confirmed_at` datetime DEFAULT NULL,
  `confirmation_notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `land_id` (`land_id`),
  KEY `status` (`status`),
  KEY `confirmation_code` (`confirmation_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 6. land_disputes table
safeCreateTable($conn, 'land_disputes', "
CREATE TABLE IF NOT EXISTS `land_disputes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dispute_number` varchar(50) DEFAULT NULL,
  `land_id` int(11) NOT NULL,
  `reported_by` int(11) NOT NULL,
  `against_user_id` int(11) DEFAULT NULL,
  `dispute_type` enum('boundary','ownership','encroachment','inheritance','fraud','other') NOT NULL,
  `description` text NOT NULL,
  `evidence_files` text DEFAULT NULL,
  `status` enum('pending','investigating','mediation','resolved','escalated','closed') DEFAULT 'pending',
  `priority` enum('low','medium','high','critical') DEFAULT 'medium',
  `assigned_to` int(11) DEFAULT NULL,
  `resolution` text DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `resolved_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `land_id` (`land_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 7. dispute_activities table
safeCreateTable($conn, 'dispute_activities', "
CREATE TABLE IF NOT EXISTS `dispute_activities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dispute_id` int(11) NOT NULL,
  `activity_type` enum('created','status_changed','assigned','comment_added','document_uploaded','meeting_scheduled','meeting_held','escalated','resolved') NOT NULL,
  `description` text NOT NULL,
  `performed_by` int(11) DEFAULT NULL,
  `metadata` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `dispute_id` (`dispute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 8. land_points table (for polygon coordinates)
safeCreateTable($conn, 'land_points', "
CREATE TABLE IF NOT EXISTS `land_points` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `land_id` int(11) NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `point_order` int(11) NOT NULL DEFAULT 0,
  `point_label` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `land_id` (`land_id`),
  KEY `idx_coords` (`latitude`, `longitude`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 9. satellite_monitoring table
safeCreateTable($conn, 'satellite_monitoring', "
CREATE TABLE IF NOT EXISTS `satellite_monitoring` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `land_id` int(11) NOT NULL,
  `monitoring_status` enum('active','paused','disabled') DEFAULT 'active',
  `monitoring_frequency` enum('daily','weekly','monthly') DEFAULT 'monthly',
  `last_check_date` datetime DEFAULT NULL,
  `next_check_date` datetime DEFAULT NULL,
  `baseline_image_date` date DEFAULT NULL,
  `baseline_ndvi` decimal(5,4) DEFAULT 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 NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_land_monitoring` (`land_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 10. satellite_changes table
safeCreateTable($conn, 'satellite_changes', "
CREATE TABLE IF NOT EXISTS `satellite_changes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `land_id` int(11) 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) DEFAULT NULL,
  `affected_percentage` decimal(5,2) DEFAULT NULL,
  `change_center_lat` decimal(10,8) DEFAULT NULL,
  `change_center_lng` decimal(11,8) DEFAULT NULL,
  `confidence_score` decimal(5,2) DEFAULT NULL,
  `ndvi_before` decimal(5,4) DEFAULT NULL,
  `ndvi_after` decimal(5,4) DEFAULT NULL,
  `ndvi_change` decimal(5,4) DEFAULT NULL,
  `status` enum('detected','pending_review','reviewed','confirmed','false_positive','resolved') DEFAULT 'detected',
  `alert_sent` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_land_date` (`land_id`, `detection_date`),
  KEY `idx_severity` (`severity`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 11. satellite_images table
safeCreateTable($conn, 'satellite_images', "
CREATE TABLE IF NOT EXISTS `satellite_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `land_id` int(11) 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) DEFAULT NULL,
  `thumbnail_url` varchar(500) DEFAULT NULL,
  `cloud_coverage` decimal(5,2) DEFAULT NULL,
  `mean_ndvi` decimal(5,4) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_land_date` (`land_id`, `image_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 12. satellite_alerts table
safeCreateTable($conn, 'satellite_alerts', "
CREATE TABLE IF NOT EXISTS `satellite_alerts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `change_id` int(11) DEFAULT NULL,
  `land_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `alert_type` enum('sms','email','push','in_app','whatsapp') NOT NULL,
  `alert_status` enum('pending','sent','delivered','failed','read') DEFAULT 'pending',
  `message_title` varchar(200) NOT NULL,
  `message_body` text NOT NULL,
  `sent_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_status` (`user_id`, `alert_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 13. satellite_api_usage table
safeCreateTable($conn, 'satellite_api_usage', "
CREATE TABLE IF NOT EXISTS `satellite_api_usage` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `operation` varchar(50) NOT NULL,
  `units_used` int(11) DEFAULT 1,
  `land_id` int(11) DEFAULT NULL,
  `response_status` varchar(20) DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_operation` (`operation`),
  KEY `idx_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 14. land_verification_logs table
safeCreateTable($conn, 'land_verification_logs', "
CREATE TABLE IF NOT EXISTS `land_verification_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `land_id` int(11) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `verified_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_land_id` (`land_id`),
  KEY `idx_verified_at` (`verified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 15. location_scan_logs table
safeCreateTable($conn, 'location_scan_logs', "
CREATE TABLE IF NOT EXISTS `location_scan_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `lands_found` int(11) DEFAULT 0,
  `scanned_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_coords` (`latitude`, `longitude`),
  KEY `idx_scanned_at` (`scanned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 16. beacon_pings table
safeCreateTable($conn, 'beacon_pings', "
CREATE TABLE IF NOT EXISTS `beacon_pings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `beacon_id` int(11) NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `battery_level` int(11) DEFAULT NULL,
  `signal_strength` int(11) DEFAULT NULL,
  `ping_time` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `beacon_id` (`beacon_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

// 17. survey_requests table
safeCreateTable($conn, 'survey_requests', "
CREATE TABLE IF NOT EXISTS `survey_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_number` varchar(50) NOT NULL,
  `land_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `village_id` int(11) DEFAULT NULL,
  `assigned_surveyor` int(11) DEFAULT NULL,
  `status` enum('pending','accepted','scheduled','in_progress','completed','cancelled') DEFAULT 'pending',
  `survey_type` enum('initial','boundary_correction','subdivision','verification') DEFAULT 'initial',
  `scheduled_date` datetime DEFAULT NULL,
  `completed_date` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `land_id` (`land_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
", $success, $skipped, $errors);

echo "</div>";

// ============================================================
// SECTION 2: ADD COLUMNS TO EXISTING TABLES
// ============================================================
echo "<h2>📝 Adding Columns to Existing Tables</h2><div class='section'>";

// Add columns to users table
if (tableExists($conn, 'users')) {
    safeAddColumn($conn, 'users', 'village_id', "int(11) DEFAULT NULL AFTER `broker_code`", $success, $skipped, $errors);
    safeAddColumn($conn, 'users', 'national_id', "varchar(50) DEFAULT NULL AFTER `phone`", $success, $skipped, $errors);
    safeAddColumn($conn, 'users', 'profile_image', "varchar(255) DEFAULT NULL", $success, $skipped, $errors);
    safeAddColumn($conn, 'users', 'address', "text DEFAULT NULL", $success, $skipped, $errors);
}

// Add columns to lands table
if (tableExists($conn, 'lands')) {
    safeAddColumn($conn, 'lands', 'village_id', "int(11) DEFAULT NULL AFTER `district_id`", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'area_sqm', "decimal(15,2) DEFAULT NULL", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'land_use', "enum('residential','agricultural','commercial','industrial','mixed','vacant') DEFAULT 'residential'", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'certificate_number', "varchar(100) DEFAULT NULL", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'registration_number', "varchar(100) DEFAULT NULL", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'neighbors_confirmed', "int(11) DEFAULT 0", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'neighbors_total', "int(11) DEFAULT 0", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'cbc_status', "enum('not_started','neighbors_pending','village_review','approved','rejected') DEFAULT 'not_started'", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'cbc_approved_at', "datetime DEFAULT NULL", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'cbc_approved_by', "int(11) DEFAULT NULL", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'satellite_monitoring_enabled', "tinyint(1) DEFAULT 0", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'last_satellite_check', "datetime DEFAULT NULL", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'satellite_alerts_count', "int(11) DEFAULT 0", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'verification_code', "varchar(50) DEFAULT NULL", $success, $skipped, $errors);
    safeAddColumn($conn, 'lands', 'qr_code', "varchar(100) DEFAULT NULL", $success, $skipped, $errors);
}

// Add columns to cbc_notifications
if (tableExists($conn, 'cbc_notifications')) {
    safeAddColumn($conn, 'cbc_notifications', 'metadata', "longtext DEFAULT NULL", $success, $skipped, $errors);
}

echo "</div>";

// ============================================================
// SECTION 3: ADD SETTINGS
// ============================================================
echo "<h2>⚙️ Adding Settings</h2><div class='section'>";

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', ''],
        ['cbc_auto_approve_neighbors', '0'],
        ['cbc_neighbor_confirmation_days', '14'],
        ['cbc_village_review_days', '30']
    ];

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

        $check = mysqli_query($conn, "SELECT id FROM payment_settings WHERE setting_key = '$key'");
        if ($check && mysqli_num_rows($check) == 0) {
            if (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 "</div>";

// ============================================================
// SECTION 4: GENERATE VERIFICATION CODES
// ============================================================
echo "<h2>🔑 Generating Verification Codes</h2><div class='section'>";

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";
    }
}

echo "</div>";

// ============================================================
// SECTION 5: COPY DATA FROM beacon_points TO land_points
// ============================================================
echo "<h2>📍 Syncing Land Points</h2><div class='section'>";

if (tableExists($conn, 'beacon_points') && tableExists($conn, 'land_points')) {
    // Check if land_points is empty
    $countResult = mysqli_query($conn, "SELECT COUNT(*) as cnt FROM land_points");
    $countRow = mysqli_fetch_assoc($countResult);

    if ($countRow['cnt'] == 0) {
        // Copy data from beacon_points to land_points
        $copyResult = mysqli_query($conn, "
            INSERT INTO land_points (land_id, latitude, longitude, point_order, point_label, created_at)
            SELECT land_id, latitude, longitude, point_order, point_label, created_at
            FROM beacon_points
        ");

        if ($copyResult) {
            $copied = mysqli_affected_rows($conn);
            if ($copied > 0) {
                $success[] = "Copied $copied points from beacon_points to land_points";
            }
        }
    } else {
        $skipped[] = "land_points already has data, skipping sync";
    }
}

echo "</div>";

// ============================================================
// SECTION 6: UPDATE USER ROLES
// ============================================================
echo "<h2>👥 Updating User Roles</h2><div class='section'>";

// Check if role column needs to be modified to include village_chairman
if (tableExists($conn, 'users')) {
    $roleCheck = mysqli_query($conn, "SHOW COLUMNS FROM users LIKE 'role'");
    if ($roleCheck && $row = mysqli_fetch_assoc($roleCheck)) {
        $currentType = $row['Type'];
        if (strpos($currentType, 'village_chairman') === false) {
            $alterResult = mysqli_query($conn, "
                ALTER TABLE users MODIFY COLUMN role
                ENUM('admin','broker','user','village_chairman','village_secretary','surveyor') DEFAULT 'user'
            ");
            if ($alterResult) {
                $success[] = "Updated users.role to include village roles";
            } else {
                $skipped[] = "Could not update users.role enum (may need manual update)";
            }
        } else {
            $skipped[] = "users.role already includes village roles";
        }
    }
}

echo "</div>";

// ============================================================
// DISPLAY RESULTS
// ============================================================
echo "<h2>📋 Migration Results</h2>";

$totalSuccess = count($success);
$totalSkipped = count($skipped);
$totalErrors = count($errors);

echo "<div style='display: flex; gap: 20px; margin: 20px 0;'>";
echo "<div style='background: #dcfce7; padding: 20px; border-radius: 10px; flex: 1; text-align: center;'>";
echo "<div style='font-size: 36px; font-weight: bold; color: #22c55e;'>$totalSuccess</div>";
echo "<div style='color: #166534;'>Changes Applied</div></div>";
echo "<div style='background: #f1f5f9; padding: 20px; border-radius: 10px; flex: 1; text-align: center;'>";
echo "<div style='font-size: 36px; font-weight: bold; color: #64748b;'>$totalSkipped</div>";
echo "<div style='color: #475569;'>Already Up-to-date</div></div>";
echo "<div style='background: #fee2e2; padding: 20px; border-radius: 10px; flex: 1; text-align: center;'>";
echo "<div style='font-size: 36px; font-weight: bold; color: #ef4444;'>$totalErrors</div>";
echo "<div style='color: #991b1b;'>Errors</div></div>";
echo "</div>";

if (!empty($success)) {
    echo "<details open><summary style='cursor: pointer; font-weight: bold; color: #22c55e;'>✅ Successful Changes ($totalSuccess)</summary><div class='section'>";
    foreach ($success as $msg) {
        echo "<p class='success'>✓ $msg</p>";
    }
    echo "</div></details>";
}

if (!empty($skipped)) {
    echo "<details><summary style='cursor: pointer; font-weight: bold; color: #64748b;'>⏭️ Skipped ($totalSkipped)</summary><div class='section'>";
    foreach ($skipped as $msg) {
        echo "<p class='skip'>○ $msg</p>";
    }
    echo "</div></details>";
}

if (!empty($errors)) {
    echo "<details open><summary style='cursor: pointer; font-weight: bold; color: #ef4444;'>❌ Errors ($totalErrors)</summary><div class='section'>";
    foreach ($errors as $msg) {
        echo "<p class='error'>✗ $msg</p>";
    }
    echo "</div></details>";
}

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>⚠️ IMPORTANT - Security Notice:</strong><br>
        Please <strong>DELETE</strong> this file (<code>migrate_full.php</code>) from your server immediately after confirming the migration was successful.
    </div>";
    echo "<a href='dashboard' class='btn'>Go to Dashboard</a>";
    echo "<a href='village/dashboard' class='btn' style='background: #667eea;'>Go to Village Dashboard</a>";
} else {
    echo "<div class='warning' style='background: #fee2e2; border-color: #ef4444;'>
        <strong>⚠️ Some errors occurred.</strong><br>
        Review the errors above. Some may be safe to ignore (e.g., duplicate column errors).
    </div>";
}

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

mysqli_close($conn);
?>
