-- Migration: Add overlap attempts tracking table
-- This table records when users attempt to add land that overlaps with existing parcels
-- Helps admin identify potential land fraud or boundary disputes

-- Create overlap_attempts table
CREATE TABLE IF NOT EXISTS overlap_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    -- Who attempted to add the overlapping land
    attempted_by_user_id INT NULL,
    attempted_by_user_name VARCHAR(100),
    attempted_by_phone VARCHAR(20),
    attempted_by_email VARCHAR(100),

    -- The new land that was attempted (may or may not be saved)
    new_land_id INT NULL,
    new_land_name VARCHAR(100),

    -- The existing land that was overlapped
    existing_land_id INT NOT NULL,
    existing_land_name VARCHAR(100),
    existing_land_owner_id INT,
    existing_land_owner_name VARCHAR(100),
    existing_land_owner_phone VARCHAR(20),

    -- Overlap details
    overlap_type ENUM('NEW_INSIDE_EXISTING', 'EXISTING_INSIDE_NEW', 'PARTIAL_OVERLAP', 'POINTS_INSIDE', 'EXISTING_POINTS_INSIDE') NOT NULL,
    overlap_percentage DECIMAL(5,2) DEFAULT 0,
    overlap_description TEXT,
    severity ENUM('info', 'warning', 'critical') DEFAULT 'warning',

    -- Location info for reference
    region VARCHAR(100),
    district VARCHAR(100),

    -- Tracking
    ip_address VARCHAR(45),
    user_agent TEXT,

    -- Admin review
    reviewed_by INT NULL,
    review_status ENUM('pending', 'reviewed', 'flagged', 'cleared') DEFAULT 'pending',
    review_notes TEXT,
    reviewed_at TIMESTAMP NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (attempted_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (existing_land_id) REFERENCES lands(id) ON DELETE CASCADE,
    FOREIGN KEY (existing_land_owner_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_attempted_by (attempted_by_user_id),
    INDEX idx_existing_land (existing_land_id),
    INDEX idx_review_status (review_status),
    INDEX idx_severity (severity),
    INDEX idx_created_at (created_at)
);

-- Add notification flag for admins
ALTER TABLE overlap_attempts ADD COLUMN admin_notified TINYINT(1) DEFAULT 0 AFTER review_status;
