-- ============================================================================
-- ALERT SYSTEM ENHANCEMENT
-- Adds metadata column and improves notification system
-- Run this after database_cbc_system.sql
-- ============================================================================

-- Add metadata column to cbc_notifications if not exists
ALTER TABLE cbc_notifications
ADD COLUMN IF NOT EXISTS metadata JSON NULL COMMENT 'Additional notification data' AFTER land_id,
ADD COLUMN IF NOT EXISTS read_at DATETIME NULL COMMENT 'When notification was read' AFTER is_read;

-- Add index for faster queries
ALTER TABLE cbc_notifications
ADD INDEX IF NOT EXISTS idx_user_unread (user_id, is_read, created_at);

-- Create notification preferences table
CREATE TABLE IF NOT EXISTS notification_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,

    -- In-app notifications
    notify_satellite_alerts TINYINT(1) DEFAULT 1,
    notify_neighbor_changes TINYINT(1) DEFAULT 1,
    notify_status_changes TINYINT(1) DEFAULT 1,
    notify_overlap_alerts TINYINT(1) DEFAULT 1,
    notify_payment_updates TINYINT(1) DEFAULT 1,
    notify_certificate_issued TINYINT(1) DEFAULT 1,

    -- SMS notifications
    sms_critical_alerts TINYINT(1) DEFAULT 1,
    sms_disputes TINYINT(1) DEFAULT 1,
    sms_verification_complete TINYINT(1) DEFAULT 0,

    -- Email notifications
    email_daily_digest TINYINT(1) DEFAULT 0,
    email_weekly_summary TINYINT(1) DEFAULT 1,
    email_critical_alerts TINYINT(1) DEFAULT 1,

    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,
    UNIQUE KEY unique_user_prefs (user_id)
);

-- Insert default preferences for existing users
INSERT INTO notification_preferences (user_id)
SELECT id FROM users WHERE id NOT IN (SELECT user_id FROM notification_preferences)
ON DUPLICATE KEY UPDATE user_id = user_id;

-- ============================================================================
-- NOTIFICATION TYPES REFERENCE
-- ============================================================================
-- satellite_alert        - Satellite change detected on user's land
-- satellite_alert_official - Critical satellite change for village officials
-- neighbor_change        - Change detected on neighboring land
-- neighbor_confirmation  - Request to confirm boundary
-- neighbor_confirmed     - Neighbor confirmed the boundary
-- neighbor_disputed      - Neighbor disputed the boundary
-- overlap_detected       - Overlap with another land detected
-- overlap_official       - Overlap alert for village officials
-- status_change          - Land verification status changed
-- new_land               - New land registered in village
-- payment_received       - Payment confirmation
-- certificate_issued     - Digital certificate issued
-- dispute_raised         - New dispute reported
-- verification_request   - Verification request submitted
-- ============================================================================

-- Log migration
INSERT INTO activity_logs (action, description) VALUES
('ALERT_SYSTEM_UPDATE', 'Alert system enhancement completed');
