<?php
/**
 * Install Satellite Monitoring Tables
 * Run this file once to create the required database tables
 * Access: http://localhost/uhakikimadalali/install_satellite_tables.php
 */

require_once 'db_connect.php';

echo "<h2>Installing Satellite Monitoring Tables</h2>";
echo "<pre>";

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

// 1. Create satellite_monitoring table
$sql1 = "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)
)";

if (mysqli_query($conn, $sql1)) {
    $success[] = "Created satellite_monitoring table";
} else {
    $errors[] = "satellite_monitoring: " . mysqli_error($conn);
}

// 2. Create satellite_changes table
$sql2 = "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)
)";

if (mysqli_query($conn, $sql2)) {
    $success[] = "Created satellite_changes table";
} else {
    $errors[] = "satellite_changes: " . mysqli_error($conn);
}

// 3. Create satellite_images table
$sql3 = "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)
)";

if (mysqli_query($conn, $sql3)) {
    $success[] = "Created satellite_images table";
} else {
    $errors[] = "satellite_images: " . mysqli_error($conn);
}

// 4. Create satellite_alerts table
$sql4 = "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)
)";

if (mysqli_query($conn, $sql4)) {
    $success[] = "Created satellite_alerts table";
} else {
    $errors[] = "satellite_alerts: " . mysqli_error($conn);
}

// 5. Create satellite_api_usage table for tracking API calls
$sql5 = "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)
)";

if (mysqli_query($conn, $sql5)) {
    $success[] = "Created satellite_api_usage table";
} else {
    $errors[] = "satellite_api_usage: " . mysqli_error($conn);
}

// 7. Add columns to lands table (ignore errors if already exist)
mysqli_report(MYSQLI_REPORT_OFF); // Temporarily disable error reporting
mysqli_query($conn, "ALTER TABLE lands ADD COLUMN satellite_monitoring_enabled TINYINT(1) DEFAULT 0");
mysqli_query($conn, "ALTER TABLE lands ADD COLUMN last_satellite_check DATETIME NULL");
mysqli_query($conn, "ALTER TABLE lands ADD COLUMN satellite_alerts_count INT DEFAULT 0");
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Re-enable
$success[] = "Updated lands table (or columns already exist)";

// 8. Add metadata column to cbc_notifications
mysqli_report(MYSQLI_REPORT_OFF);
mysqli_query($conn, "ALTER TABLE cbc_notifications ADD COLUMN metadata JSON NULL");
mysqli_query($conn, "ALTER TABLE cbc_notifications ADD COLUMN read_at DATETIME NULL");
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$success[] = "Updated cbc_notifications table (or columns already exist)";

// 9. Add satellite settings
$settings = [
    ['satellite_monitoring_enabled', '1'],
    ['satellite_default_frequency', 'monthly'],
    ['satellite_ndvi_change_threshold', '0.15'],
    ['satellite_planet_api_key', 'PLAK64c3351261ee47e89cc7b67fdc9252dd'],
    ['satellite_sentinel_api_key', '']
];

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

    @mysqli_query($conn, "INSERT INTO payment_settings (setting_key, setting_value)
                          VALUES ('$key', '$value')
                          ON DUPLICATE KEY UPDATE setting_key = setting_key");
}
$success[] = "Added satellite settings";

// 10. Create land verification logs table
$sql10 = "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)
)";

if (mysqli_query($conn, $sql10)) {
    $success[] = "Created land_verification_logs table";
} else {
    $errors[] = "land_verification_logs: " . mysqli_error($conn);
}

// 11. Create location scan logs table
$sql11 = "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)
)";

if (mysqli_query($conn, $sql11)) {
    $success[] = "Created location_scan_logs table";
} else {
    $errors[] = "location_scan_logs: " . mysqli_error($conn);
}

// 12. Add verification code columns to lands table
mysqli_report(MYSQLI_REPORT_OFF);
mysqli_query($conn, "ALTER TABLE lands ADD COLUMN verification_code VARCHAR(50) NULL");
mysqli_query($conn, "ALTER TABLE lands ADD COLUMN qr_code VARCHAR(100) NULL");
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$success[] = "Added verification code columns to lands table";

// 13. Generate verification codes for existing lands without one
$landsResult = mysqli_query($conn, "SELECT id FROM lands WHERE verification_code IS NULL OR verification_code = ''");
$updated = 0;
while ($land = mysqli_fetch_assoc($landsResult)) {
    $code = 'LS' . strtoupper(substr(md5($land['id'] . time() . rand()), 0, 8));
    mysqli_query($conn, "UPDATE lands SET verification_code = '$code' WHERE id = {$land['id']}");
    $updated++;
}
if ($updated > 0) {
    $success[] = "Generated verification codes for $updated lands";
}

// Display results
echo "\n=== SUCCESS ===\n";
foreach ($success as $msg) {
    echo "✓ $msg\n";
}

if (!empty($errors)) {
    echo "\n=== ERRORS ===\n";
    foreach ($errors as $err) {
        echo "✗ $err\n";
    }
}

echo "\n</pre>";
echo "<h3>Installation " . (empty($errors) ? "Complete!" : "Complete with some errors") . "</h3>";
echo "<p><a href='satellite-monitoring'>Go to Satellite Monitoring</a></p>";

// Delete this file hint
echo "<p style='color: #666; font-size: 12px;'>For security, delete this file after installation.</p>";
?>
