-- ============================================================================
-- VERIFICATION REQUESTS TABLE
-- For tracking user requests for land verification
-- ============================================================================

CREATE TABLE IF NOT EXISTS verification_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_number VARCHAR(20) NOT NULL UNIQUE,
    land_id INT NOT NULL,
    user_id INT NOT NULL,
    village_id INT NULL,

    request_type ENUM(
        'village_verification',
        'boundary_update',
        'ownership_transfer',
        'dispute_resolution',
        'certificate_reissue'
    ) DEFAULT 'village_verification',

    notes TEXT NULL,

    -- Status tracking
    status ENUM('pending', 'in_progress', 'completed', 'rejected', 'cancelled') DEFAULT 'pending',

    -- Assignment
    assigned_to INT NULL,
    assigned_at TIMESTAMP NULL,

    -- Response
    response_notes TEXT NULL,
    responded_by INT NULL,
    responded_at TIMESTAMP NULL,

    -- Timestamps
    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,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (village_id) REFERENCES villages(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (responded_by) REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_land_id (land_id),
    INDEX idx_user_id (user_id),
    INDEX idx_village_id (village_id),
    INDEX idx_status (status),
    INDEX idx_request_number (request_number)
);

-- Log migration
INSERT INTO activity_logs (action, description) VALUES
('VERIFICATION_REQUESTS_MIGRATION', 'Verification requests table created successfully');
