-- ============================================================================
-- SATELLITE MONITORING SYSTEM
-- Enables AI-powered land change detection using satellite imagery
-- Run this after database_cbc_system.sql
-- ============================================================================

-- Satellite monitoring configuration per land
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 COMMENT 'Baseline vegetation index',
    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,

    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE,
    UNIQUE KEY unique_land_monitoring (land_id),
    INDEX idx_next_check (next_check_date, monitoring_status),
    INDEX idx_status (monitoring_status)
);

-- Change detection results
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',      -- Kupotea kwa mimea
        'vegetation_gain',      -- Kuongezeka kwa mimea
        'new_structure',        -- Jengo jipya
        'land_clearing',        -- Kusafisha ardhi
        'water_change',         -- Mabadiliko ya maji
        'soil_disturbance',     -- Kuchimba/kusawazisha ardhi
        'boundary_encroachment', -- Uvamizi wa mpaka
        'fire_damage',          -- Uharibifu wa moto
        'flooding',             -- Mafuriko
        'erosion',              -- Mmomonyoko
        'unknown'
    ) NOT NULL,

    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    affected_area_sqm DECIMAL(12,2) NULL COMMENT 'Affected area in square meters',
    affected_percentage DECIMAL(5,2) NULL COMMENT 'Percentage of total land affected',

    -- Location of change within land
    change_center_lat DECIMAL(10,8) NULL,
    change_center_lng DECIMAL(11,8) NULL,
    change_polygon JSON NULL COMMENT 'GeoJSON of affected area',

    -- AI Analysis metrics
    confidence_score DECIMAL(5,2) NULL COMMENT 'AI confidence 0-100',
    ndvi_before DECIMAL(5,4) NULL,
    ndvi_after DECIMAL(5,4) NULL,
    ndvi_change DECIMAL(5,4) NULL,

    -- Image references
    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,

    -- Review workflow
    status ENUM('detected', 'pending_review', 'reviewed', 'confirmed', 'false_positive', 'resolved') DEFAULT 'detected',
    auto_classified TINYINT(1) DEFAULT 1 COMMENT 'Was this classified by AI?',
    reviewed_by INT NULL,
    reviewed_at DATETIME NULL,
    review_notes TEXT NULL,

    -- Resolution
    resolved_by INT NULL,
    resolved_at DATETIME NULL,
    resolution_notes TEXT NULL,

    -- Alerts
    alert_sent TINYINT(1) DEFAULT 0,
    alert_sent_at DATETIME NULL,
    alert_method VARCHAR(50) NULL COMMENT 'sms, email, push, in_app',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_land_date (land_id, detection_date),
    INDEX idx_change_type (change_type),
    INDEX idx_severity (severity),
    INDEX idx_status (status),
    INDEX idx_alert_pending (alert_sent, severity)
);

-- Satellite imagery cache/history
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 data
    image_url VARCHAR(500) NULL COMMENT 'URL to stored image',
    thumbnail_url VARCHAR(500) NULL,
    image_width INT NULL,
    image_height INT NULL,

    -- Quality metrics
    cloud_coverage DECIMAL(5,2) NULL COMMENT 'Percentage 0-100',
    resolution_meters DECIMAL(6,2) NULL,
    quality_score DECIMAL(5,2) NULL COMMENT 'Overall quality 0-100',

    -- Analysis data
    mean_ndvi DECIMAL(5,4) NULL,
    min_ndvi DECIMAL(5,4) NULL,
    max_ndvi DECIMAL(5,4) NULL,
    vegetation_coverage DECIMAL(5,2) NULL COMMENT 'Percentage vegetated',
    built_up_area DECIMAL(5,2) NULL COMMENT 'Percentage with structures',
    water_area DECIMAL(5,2) NULL COMMENT 'Percentage water',
    bare_soil_area DECIMAL(5,2) NULL COMMENT 'Percentage bare soil',

    metadata JSON NULL COMMENT 'Additional satellite metadata',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE,
    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)
);

-- Monitoring alerts log
CREATE TABLE IF NOT EXISTS satellite_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    change_id INT NOT 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,

    FOREIGN KEY (change_id) REFERENCES satellite_changes(id) ON DELETE CASCADE,
    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_status (user_id, alert_status),
    INDEX idx_pending (alert_status, created_at)
);

-- API usage tracking for billing
CREATE TABLE IF NOT EXISTS satellite_api_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_provider ENUM('sentinel_hub', 'google_earth', 'planet', 'other') NOT NULL,
    request_type VARCHAR(50) NOT NULL COMMENT 'ndvi, rgb, change_detection, etc.',
    land_id INT NULL,
    user_id INT NULL,

    request_date DATE NOT NULL,
    requests_count INT DEFAULT 1,
    data_volume_mb DECIMAL(10,4) NULL,
    estimated_cost_usd DECIMAL(10,4) NULL,

    response_status ENUM('success', 'error', 'rate_limited') DEFAULT 'success',
    response_time_ms INT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_provider_date (api_provider, request_date),
    INDEX idx_user_date (user_id, request_date)
);

-- Settings for satellite monitoring
INSERT INTO payment_settings (setting_key, setting_value, description) VALUES
('satellite_monitoring_enabled', '1', 'Enable satellite monitoring feature'),
('satellite_default_frequency', 'monthly', 'Default monitoring frequency for new lands'),
('satellite_sentinel_api_key', '', 'Sentinel Hub API key'),
('satellite_earth_engine_project', '', 'Google Earth Engine project ID'),
('satellite_planet_api_key', '', 'Planet Labs API key'),
('satellite_alert_sms_enabled', '1', 'Send SMS alerts for critical changes'),
('satellite_alert_email_enabled', '1', 'Send email alerts for changes'),
('satellite_cloud_threshold', '30', 'Maximum cloud coverage percentage to use image'),
('satellite_ndvi_change_threshold', '0.15', 'NDVI change threshold to trigger alert'),
('satellite_free_checks_per_month', '2', 'Free satellite checks per land per month'),
('satellite_premium_checks_per_month', '10', 'Premium user satellite checks per month')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- Add satellite monitoring fields to lands table
ALTER TABLE lands
ADD COLUMN IF NOT EXISTS satellite_monitoring_enabled TINYINT(1) DEFAULT 0 AFTER neighbor_required_count,
ADD COLUMN IF NOT EXISTS last_satellite_check DATETIME NULL AFTER satellite_monitoring_enabled,
ADD COLUMN IF NOT EXISTS satellite_alerts_count INT DEFAULT 0 AFTER last_satellite_check;

-- Create index for monitoring queries
CREATE INDEX IF NOT EXISTS idx_lands_satellite ON lands(satellite_monitoring_enabled, last_satellite_check);

-- ============================================================================
-- SAMPLE DATA / DEMO
-- ============================================================================

-- Insert sample monitoring for existing lands (first 5)
INSERT INTO satellite_monitoring (land_id, monitoring_status, monitoring_frequency, next_check_date)
SELECT id, 'active', 'monthly', DATE_ADD(NOW(), INTERVAL 7 DAY)
FROM lands
WHERE status NOT IN ('rejected', 'deleted')
LIMIT 5
ON DUPLICATE KEY UPDATE monitoring_status = monitoring_status;

-- ============================================================================
-- VIEWS FOR REPORTING
-- ============================================================================

CREATE OR REPLACE VIEW v_satellite_monitoring_summary AS
SELECT
    l.id as land_id,
    l.land_name,
    l.user_id,
    u.full_name as owner_name,
    sm.monitoring_status,
    sm.monitoring_frequency,
    sm.last_check_date,
    sm.next_check_date,
    sm.baseline_ndvi,
    (SELECT COUNT(*) FROM satellite_changes sc WHERE sc.land_id = l.id) as total_changes,
    (SELECT COUNT(*) FROM satellite_changes sc WHERE sc.land_id = l.id AND sc.status = 'detected') as pending_review,
    (SELECT MAX(detection_date) FROM satellite_changes sc WHERE sc.land_id = l.id) as last_change_date,
    v.name as village_name
FROM lands l
LEFT JOIN satellite_monitoring sm ON l.id = sm.land_id
LEFT JOIN users u ON l.user_id = u.id
LEFT JOIN villages v ON l.village_id = v.id
WHERE l.status NOT IN ('rejected', 'deleted');

-- ============================================================================
-- MIGRATION COMPLETE
-- ============================================================================

-- Log migration
INSERT INTO activity_logs (action, description) VALUES
('SATELLITE_MIGRATION', 'Satellite monitoring system tables created successfully');
