-- ============================================================================
-- NEIGHBOR SYSTEM ENHANCEMENT
-- Adds ID verification and document attachment support
-- Run this after database_cbc_system.sql
-- ============================================================================

-- Add new columns to land_neighbors for ID verification and documents
ALTER TABLE land_neighbors
ADD COLUMN IF NOT EXISTS neighbor_national_id VARCHAR(50) NULL AFTER neighbor_phone,
ADD COLUMN IF NOT EXISTS neighbor_id_type ENUM('national_id', 'voter_id', 'passport', 'driving_license', 'other') NULL AFTER neighbor_national_id,
ADD COLUMN IF NOT EXISTS neighbor_id_document VARCHAR(255) NULL COMMENT 'Path to uploaded ID document image' AFTER neighbor_id_type,
ADD COLUMN IF NOT EXISTS confirmation_document VARCHAR(255) NULL COMMENT 'Path to signed confirmation document' AFTER confirmation_method,
ADD COLUMN IF NOT EXISTS confirmation_signature TEXT NULL COMMENT 'Digital signature data or image path' AFTER confirmation_document,
ADD COLUMN IF NOT EXISTS witness_name VARCHAR(100) NULL AFTER confirmation_signature,
ADD COLUMN IF NOT EXISTS witness_phone VARCHAR(20) NULL AFTER witness_name,
ADD COLUMN IF NOT EXISTS witness_national_id VARCHAR(50) NULL AFTER witness_phone,
ADD COLUMN IF NOT EXISTS detection_method ENUM('auto', 'manual', 'village_added') DEFAULT 'manual' AFTER neighbor_direction,
ADD COLUMN IF NOT EXISTS overlap_percentage DECIMAL(5,2) NULL COMMENT 'If auto-detected, percentage overlap' AFTER detection_method,
ADD COLUMN IF NOT EXISTS distance_meters DECIMAL(10,2) NULL COMMENT 'Distance to nearest boundary' AFTER overlap_percentage,
ADD COLUMN IF NOT EXISTS confirmed_by_village_official INT NULL AFTER confirmed_at,
ADD COLUMN IF NOT EXISTS village_confirmation_notes TEXT NULL AFTER confirmed_by_village_official;

-- Add index for national ID lookups
ALTER TABLE land_neighbors ADD INDEX IF NOT EXISTS idx_neighbor_national_id (neighbor_national_id);

-- Add foreign key for village official confirmation
ALTER TABLE land_neighbors
ADD CONSTRAINT IF NOT EXISTS fk_confirmed_by_official
FOREIGN KEY (confirmed_by_village_official) REFERENCES users(id) ON DELETE SET NULL;

-- Create table for neighbor confirmation documents
CREATE TABLE IF NOT EXISTS neighbor_confirmation_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_neighbor_id INT NOT NULL,
    document_type ENUM('id_front', 'id_back', 'confirmation_letter', 'signed_form', 'photo_with_boundary', 'witness_statement', 'other') NOT NULL,
    document_path VARCHAR(255) NOT NULL,
    document_name VARCHAR(100) NULL,
    uploaded_by INT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    verified_by INT NULL,
    verified_at TIMESTAMP NULL,
    verification_notes TEXT NULL,

    FOREIGN KEY (land_neighbor_id) REFERENCES land_neighbors(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_land_neighbor (land_neighbor_id),
    INDEX idx_document_type (document_type)
);

-- Insert default settings for neighbor detection
INSERT INTO payment_settings (setting_key, setting_value, description) VALUES
('neighbor_detection_buffer_meters', '50', 'Buffer distance in meters for detecting nearby lands as neighbors'),
('neighbor_id_required', '1', 'Whether national ID is required for neighbor confirmation'),
('neighbor_document_required', '1', 'Whether signed document/photo is required for confirmation')
ON DUPLICATE KEY UPDATE setting_key = setting_key;
