-- ============================================================================
-- LANDSECURE CBC (Community Boundary Confirmation) SYSTEM
-- Migration Script for Village Land Verification & Conflict Resolution
-- ============================================================================
-- This migration adds:
-- 1. New user roles (village_chairman, land_officer, surveyor, district_officer)
-- 2. Village leaders table
-- 3. Neighbor confirmation system
-- 4. Land verification workflow
-- 5. Dispute reporting & resolution
-- 6. Digital land certificates
-- 7. Land history timeline
-- 8. Physical beacon tracking
-- ============================================================================

-- ============================================================================
-- STEP 1: UPDATE USER ROLES
-- ============================================================================

-- Modify the role ENUM to include new CBC roles
ALTER TABLE users MODIFY COLUMN role ENUM(
    'admin',
    'broker',
    'user',
    'village_chairman',    -- Mwenyekiti wa Kijiji
    'land_officer',        -- Afisa Ardhi wa Kata/Kijiji
    'surveyor',            -- Mpima Ardhi (Registered)
    'district_officer',    -- Afisa Ardhi wa Wilaya
    'land_council',        -- Baraza la Ardhi kijiji/Mtaa
    'user_admin'
) DEFAULT 'user';

-- Add verification fields to users
ALTER TABLE users ADD COLUMN IF NOT EXISTS national_id VARCHAR(50) NULL AFTER phone;
ALTER TABLE users ADD COLUMN IF NOT EXISTS id_verified TINYINT(1) DEFAULT 0 AFTER national_id;
ALTER TABLE users ADD COLUMN IF NOT EXISTS id_verified_at TIMESTAMP NULL AFTER id_verified;
ALTER TABLE users ADD COLUMN IF NOT EXISTS id_verified_by INT NULL AFTER id_verified_at;
ALTER TABLE users ADD COLUMN IF NOT EXISTS selfie_photo VARCHAR(255) NULL AFTER id_verified_by;
ALTER TABLE users ADD COLUMN IF NOT EXISTS id_photo VARCHAR(255) NULL AFTER selfie_photo;

-- Add location fields for CBC users (region, district, village assignment)
ALTER TABLE users ADD COLUMN IF NOT EXISTS region_id INT NULL AFTER id_photo;
ALTER TABLE users ADD COLUMN IF NOT EXISTS district_id INT NULL AFTER region_id;
ALTER TABLE users ADD COLUMN IF NOT EXISTS village_id INT NULL AFTER district_id;

-- Add foreign keys for user location
ALTER TABLE users ADD CONSTRAINT fk_user_region FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL;
ALTER TABLE users ADD CONSTRAINT fk_user_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL;
ALTER TABLE users ADD CONSTRAINT fk_user_village FOREIGN KEY (village_id) REFERENCES villages(id) ON DELETE SET NULL;

-- ============================================================================
-- STEP 2: VILLAGE LEADERS TABLE
-- ============================================================================

CREATE TABLE IF NOT EXISTS village_leaders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    village_id INT NOT NULL,
    position ENUM('chairman', 'executive_officer', 'land_committee_member', 'land_council') NOT NULL,
    position_title VARCHAR(100), -- e.g., "Mwenyekiti", "Mtendaji", "Mjumbe wa Kamati ya Ardhi", "Baraza la Ardhi"
    appointment_date DATE NOT NULL,
    appointment_letter VARCHAR(255) NULL, -- Uploaded appointment document
    term_end_date DATE NULL,
    is_active TINYINT(1) DEFAULT 1,
    verified_by INT NULL, -- Admin who verified this leader
    verified_at TIMESTAMP NULL,
    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,
    FOREIGN KEY (village_id) REFERENCES villages(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,

    UNIQUE KEY unique_leader_position (village_id, position, is_active),
    INDEX idx_village_id (village_id),
    INDEX idx_user_id (user_id),
    INDEX idx_is_active (is_active)
);

-- ============================================================================
-- STEP 3: SURVEYORS TABLE (Registered Professional Surveyors)
-- ============================================================================

CREATE TABLE IF NOT EXISTS registered_surveyors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    license_number VARCHAR(50) NOT NULL UNIQUE,
    license_type ENUM('registered', 'licensed', 'government') NOT NULL,
    license_document VARCHAR(255) NULL,
    license_expiry_date DATE NOT NULL,
    company_name VARCHAR(255) NULL,
    regions_covered TEXT NULL, -- JSON array of region IDs they cover
    is_active TINYINT(1) DEFAULT 1,
    verified_by INT NULL,
    verified_at TIMESTAMP NULL,
    total_verifications INT DEFAULT 0,
    rating DECIMAL(2,1) DEFAULT 5.0,
    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,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_license_number (license_number),
    INDEX idx_is_active (is_active)
);

-- ============================================================================
-- STEP 4: ENHANCED LANDS TABLE FOR CBC
-- ============================================================================

-- Add CBC verification fields to lands table
ALTER TABLE lands ADD COLUMN IF NOT EXISTS village_id INT NULL AFTER location_address;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS region_id INT NULL AFTER village_id;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS district_id INT NULL AFTER region_id;

-- Land verification status (multi-step verification)
ALTER TABLE lands MODIFY COLUMN status ENUM(
    'draft',                    -- Just created, not submitted
    'pending_neighbor',         -- Awaiting neighbor confirmation
    'neighbor_verified',        -- Neighbors confirmed
    'pending_village',          -- Awaiting village leader approval
    'village_verified',         -- Village leader approved
    'pending_survey',           -- Awaiting surveyor verification
    'surveyor_verified',        -- Professional surveyor verified
    'fully_verified',           -- All verifications complete
    'disputed',                 -- Land is under dispute
    'rejected',                 -- Verification rejected
    'active'                    -- Legacy status
) DEFAULT 'draft';

-- Verification tracking fields
ALTER TABLE lands ADD COLUMN IF NOT EXISTS neighbor_verification_count INT DEFAULT 0 AFTER status;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS neighbor_required_count INT DEFAULT 2 AFTER neighbor_verification_count;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS village_verified_by INT NULL AFTER neighbor_required_count;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS village_verified_at TIMESTAMP NULL AFTER village_verified_by;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS village_rejection_reason TEXT NULL AFTER village_verified_at;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS surveyor_verified_by INT NULL AFTER village_rejection_reason;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS surveyor_verified_at TIMESTAMP NULL AFTER surveyor_verified_by;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS survey_document VARCHAR(255) NULL AFTER surveyor_verified_at;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS survey_accuracy DECIMAL(5,2) NULL AFTER survey_document; -- GPS accuracy in meters

-- Certificate fields
ALTER TABLE lands ADD COLUMN IF NOT EXISTS certificate_number VARCHAR(50) UNIQUE NULL AFTER survey_accuracy;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS certificate_issued_at TIMESTAMP NULL AFTER certificate_number;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS certificate_qr_code VARCHAR(255) NULL AFTER certificate_issued_at;

-- Physical beacon tracking
ALTER TABLE lands ADD COLUMN IF NOT EXISTS has_physical_beacon TINYINT(1) DEFAULT 0 AFTER certificate_qr_code;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS beacon_id VARCHAR(50) NULL AFTER has_physical_beacon;
ALTER TABLE lands ADD COLUMN IF NOT EXISTS beacon_installed_at TIMESTAMP NULL AFTER beacon_id;

-- Add foreign keys for new columns
ALTER TABLE lands ADD CONSTRAINT fk_land_village FOREIGN KEY (village_id) REFERENCES villages(id) ON DELETE SET NULL;
ALTER TABLE lands ADD CONSTRAINT fk_land_region FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL;
ALTER TABLE lands ADD CONSTRAINT fk_land_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL;
ALTER TABLE lands ADD CONSTRAINT fk_land_village_verifier FOREIGN KEY (village_verified_by) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE lands ADD CONSTRAINT fk_land_surveyor_verifier FOREIGN KEY (surveyor_verified_by) REFERENCES users(id) ON DELETE SET NULL;

-- ============================================================================
-- STEP 5: NEIGHBOR CONFIRMATION SYSTEM
-- ============================================================================

CREATE TABLE IF NOT EXISTS land_neighbors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL,                          -- The land being registered
    neighbor_land_id INT NULL,                     -- If neighbor has registered land (auto-detected)
    neighbor_user_id INT NULL,                     -- If neighbor is a registered user
    neighbor_name VARCHAR(100) NULL,               -- Manual entry if not registered
    neighbor_phone VARCHAR(20) NULL,
    neighbor_direction ENUM('north', 'south', 'east', 'west', 'northeast', 'northwest', 'southeast', 'southwest') NOT NULL,

    -- Confirmation status
    confirmation_status ENUM('pending', 'confirmed', 'disputed', 'no_response') DEFAULT 'pending',
    confirmation_code VARCHAR(20) NULL,            -- SMS verification code
    confirmed_at TIMESTAMP NULL,
    confirmation_method ENUM('app', 'sms', 'in_person', 'village_meeting') NULL,

    -- Dispute info if neighbor disputes
    dispute_reason TEXT NULL,
    dispute_boundary_description TEXT NULL,

    -- Tracking
    notification_sent_at TIMESTAMP NULL,
    reminder_count INT DEFAULT 0,
    expires_at TIMESTAMP NULL,                     -- Auto-expire if no response

    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 (neighbor_land_id) REFERENCES lands(id) ON DELETE SET NULL,
    FOREIGN KEY (neighbor_user_id) REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_land_id (land_id),
    INDEX idx_neighbor_user (neighbor_user_id),
    INDEX idx_confirmation_status (confirmation_status),
    INDEX idx_confirmation_code (confirmation_code)
);

-- ============================================================================
-- STEP 6: DISPUTE REPORTING SYSTEM
-- ============================================================================

CREATE TABLE IF NOT EXISTS land_disputes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dispute_number VARCHAR(20) NOT NULL UNIQUE,    -- e.g., DSP-2026-00001

    -- The land in question
    land_id INT NOT NULL,
    land_owner_id INT NOT NULL,

    -- Who reported the dispute
    reported_by_user_id INT NULL,
    reported_by_name VARCHAR(100) NULL,
    reported_by_phone VARCHAR(20) NOT NULL,
    reported_by_national_id VARCHAR(50) NULL,

    -- Dispute details
    dispute_type ENUM(
        'boundary_overlap',       -- Migogoro wa mpaka
        'ownership_claim',        -- Madai ya umiliki
        'illegal_occupation',     -- Uvamizi wa ardhi
        'inheritance_dispute',    -- Mgogoro wa urithi
        'sale_fraud',             -- Ulaghai wa mauzo
        'double_registration',    -- Usajili mara mbili
        'encroachment',           -- Uvamizi wa sehemu
        'other'
    ) NOT NULL,

    dispute_description TEXT NOT NULL,
    evidence_documents TEXT NULL,                  -- JSON array of document paths

    -- Location info
    village_id INT NULL,
    district_id INT NULL,

    -- Status tracking
    status ENUM(
        'reported',               -- Just reported
        'under_review',           -- Village reviewing
        'investigating',          -- Investigation ongoing
        'mediation',              -- In mediation process
        'resolved',               -- Dispute resolved
        'escalated',              -- Escalated to district
        'court_referred',         -- Sent to court
        'dismissed'               -- Dismissed as invalid
    ) DEFAULT 'reported',

    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',

    -- Assignment
    assigned_to INT NULL,                          -- Village leader handling this
    assigned_at TIMESTAMP NULL,

    -- Resolution
    resolution_type ENUM(
        'boundary_adjusted',
        'ownership_confirmed',
        'ownership_transferred',
        'land_divided',
        'compensation_agreed',
        'dismissed',
        'court_decision',
        'other'
    ) NULL,
    resolution_notes TEXT NULL,
    resolved_by INT NULL,
    resolved_at TIMESTAMP NULL,

    -- Timeline
    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 (land_owner_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reported_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (village_id) REFERENCES villages(id) ON DELETE SET NULL,
    FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_land_id (land_id),
    INDEX idx_status (status),
    INDEX idx_dispute_type (dispute_type),
    INDEX idx_village_id (village_id),
    INDEX idx_assigned_to (assigned_to),
    INDEX idx_priority (priority)
);

-- Dispute activity log (timeline)
CREATE TABLE IF NOT EXISTS dispute_activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dispute_id INT NOT NULL,
    activity_type ENUM(
        'created',
        'status_changed',
        'assigned',
        'comment_added',
        'document_uploaded',
        'meeting_scheduled',
        'meeting_held',
        'escalated',
        'resolved'
    ) NOT NULL,
    description TEXT NOT NULL,
    performed_by INT NULL,
    metadata TEXT NULL,                            -- JSON for extra data
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (dispute_id) REFERENCES land_disputes(id) ON DELETE CASCADE,
    FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_dispute_id (dispute_id),
    INDEX idx_activity_type (activity_type)
);

-- ============================================================================
-- STEP 7: LAND HISTORY TIMELINE
-- ============================================================================

CREATE TABLE IF NOT EXISTS land_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL,

    event_type ENUM(
        'registered',             -- Land first registered
        'boundary_updated',       -- Boundary points changed
        'neighbor_confirmed',     -- A neighbor confirmed
        'neighbor_disputed',      -- A neighbor disputed
        'village_approved',       -- Village leader approved
        'village_rejected',       -- Village leader rejected
        'surveyor_verified',      -- Professional survey done
        'certificate_issued',     -- Digital certificate issued
        'beacon_installed',       -- Physical beacon placed
        'beacon_moved',           -- Beacon location changed (alert!)
        'ownership_transferred',  -- Land sold/inherited
        'dispute_raised',         -- Dispute reported
        'dispute_resolved',       -- Dispute closed
        'document_added',         -- New document uploaded
        'status_changed'          -- General status change
    ) NOT NULL,

    event_description TEXT NOT NULL,
    event_data TEXT NULL,                          -- JSON for detailed data

    -- Who/what caused this event
    performed_by INT NULL,
    performed_by_role VARCHAR(50) NULL,

    -- For ownership transfers
    previous_owner_id INT NULL,
    new_owner_id INT NULL,

    -- GPS snapshot at time of event (for boundary changes)
    boundary_snapshot TEXT NULL,                   -- JSON of beacon points

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE,
    FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (previous_owner_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (new_owner_id) REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_land_id (land_id),
    INDEX idx_event_type (event_type),
    INDEX idx_created_at (created_at)
);

-- ============================================================================
-- STEP 8: DIGITAL LAND CERTIFICATES
-- ============================================================================

CREATE TABLE IF NOT EXISTS land_certificates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL UNIQUE,
    certificate_number VARCHAR(50) NOT NULL UNIQUE,

    -- Certificate data (snapshot at time of issue)
    owner_name VARCHAR(100) NOT NULL,
    owner_phone VARCHAR(20) NOT NULL,
    owner_national_id VARCHAR(50) NULL,

    land_name VARCHAR(100) NOT NULL,
    land_size DECIMAL(15,4) NOT NULL,
    land_size_unit ENUM('sqm', 'acres', 'hectares') NOT NULL,

    -- Location
    village_name VARCHAR(100) NULL,
    district_name VARCHAR(100) NULL,
    region_name VARCHAR(100) NULL,

    -- GPS boundary (frozen at certificate time)
    boundary_points TEXT NOT NULL,                 -- JSON array of coordinates
    center_latitude DECIMAL(10, 8) NOT NULL,
    center_longitude DECIMAL(11, 8) NOT NULL,

    -- Verification details
    neighbor_verifications INT DEFAULT 0,
    village_verified TINYINT(1) DEFAULT 0,
    village_verified_by_name VARCHAR(100) NULL,
    surveyor_verified TINYINT(1) DEFAULT 0,
    surveyor_name VARCHAR(100) NULL,
    surveyor_license VARCHAR(50) NULL,

    -- QR Code
    qr_code_data VARCHAR(255) NOT NULL,            -- Unique verification URL/code
    qr_code_image VARCHAR(255) NULL,               -- Path to QR image

    -- Certificate status
    status ENUM('active', 'revoked', 'superseded') DEFAULT 'active',
    revoked_reason TEXT NULL,
    revoked_at TIMESTAMP NULL,
    revoked_by INT NULL,

    -- PDF certificate
    pdf_path VARCHAR(255) NULL,

    issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,                     -- If certificates expire

    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE,
    FOREIGN KEY (revoked_by) REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_certificate_number (certificate_number),
    INDEX idx_qr_code_data (qr_code_data),
    INDEX idx_status (status)
);

-- ============================================================================
-- STEP 9: PHYSICAL BEACON TRACKING
-- ============================================================================

CREATE TABLE IF NOT EXISTS physical_beacons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    beacon_id VARCHAR(50) NOT NULL UNIQUE,         -- Hardware beacon ID (e.g., LS-BEACON-48392)
    land_id INT NULL,                              -- Linked land (NULL if unassigned)

    -- Installation details
    installed_latitude DECIMAL(10, 8) NULL,
    installed_longitude DECIMAL(11, 8) NULL,
    installed_at TIMESTAMP NULL,
    installed_by INT NULL,

    -- Current/last known position
    current_latitude DECIMAL(10, 8) NULL,
    current_longitude DECIMAL(11, 8) NULL,
    last_ping_at TIMESTAMP NULL,

    -- Status
    status ENUM('available', 'installed', 'moved', 'damaged', 'lost') DEFAULT 'available',
    battery_level INT NULL,                        -- 0-100%

    -- Movement detection
    movement_detected TINYINT(1) DEFAULT 0,
    movement_distance DECIMAL(10,2) NULL,          -- Distance moved in meters
    movement_alert_sent TINYINT(1) DEFAULT 0,

    -- Hardware info
    hardware_version VARCHAR(20) NULL,
    firmware_version VARCHAR(20) NULL,

    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 SET NULL,
    FOREIGN KEY (installed_by) REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_beacon_id (beacon_id),
    INDEX idx_land_id (land_id),
    INDEX idx_status (status),
    INDEX idx_movement_detected (movement_detected)
);

-- Beacon ping history (for IoT beacons)
CREATE TABLE IF NOT EXISTS beacon_pings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    beacon_id INT NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    battery_level INT NULL,
    signal_strength INT NULL,
    ping_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (beacon_id) REFERENCES physical_beacons(id) ON DELETE CASCADE,

    INDEX idx_beacon_id (beacon_id),
    INDEX idx_ping_time (ping_time)
);

-- ============================================================================
-- STEP 10: VILLAGE LAND DASHBOARD STATISTICS
-- ============================================================================

CREATE TABLE IF NOT EXISTS village_land_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    village_id INT NOT NULL,
    stat_date DATE NOT NULL,

    -- Land counts
    total_lands INT DEFAULT 0,
    verified_lands INT DEFAULT 0,
    pending_lands INT DEFAULT 0,
    disputed_lands INT DEFAULT 0,

    -- Area totals
    total_area_sqm DECIMAL(20,4) DEFAULT 0,
    verified_area_sqm DECIMAL(20,4) DEFAULT 0,

    -- Disputes
    active_disputes INT DEFAULT 0,
    resolved_disputes_month INT DEFAULT 0,

    -- Users
    registered_landowners INT DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (village_id) REFERENCES villages(id) ON DELETE CASCADE,

    UNIQUE KEY unique_village_date (village_id, stat_date),
    INDEX idx_village_id (village_id),
    INDEX idx_stat_date (stat_date)
);

-- ============================================================================
-- STEP 11: NOTIFICATION SYSTEM FOR CBC
-- ============================================================================

CREATE TABLE IF NOT EXISTS cbc_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,

    notification_type ENUM(
        'neighbor_confirmation_request',
        'neighbor_confirmed',
        'neighbor_disputed',
        'village_review_pending',
        'village_approved',
        'village_rejected',
        'surveyor_assigned',
        'survey_complete',
        'certificate_ready',
        'dispute_raised',
        'dispute_update',
        'dispute_resolved',
        'beacon_movement',
        'land_nearby_registered'
    ) NOT NULL,

    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,

    -- Related entities
    land_id INT NULL,
    dispute_id INT NULL,

    -- Status
    is_read TINYINT(1) DEFAULT 0,
    read_at TIMESTAMP NULL,

    -- Push notification tracking
    push_sent TINYINT(1) DEFAULT 0,
    sms_sent TINYINT(1) DEFAULT 0,
    email_sent TINYINT(1) DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE,
    FOREIGN KEY (dispute_id) REFERENCES land_disputes(id) ON DELETE CASCADE,

    INDEX idx_user_id (user_id),
    INDEX idx_is_read (is_read),
    INDEX idx_notification_type (notification_type),
    INDEX idx_created_at (created_at)
);

-- ============================================================================
-- STEP 12: INSTANT LAND CHECK (Public Land Verification)
-- ============================================================================

CREATE TABLE IF NOT EXISTS land_check_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,

    -- Who checked
    checked_by_user_id INT NULL,
    checked_by_ip VARCHAR(45) NULL,

    -- What was checked
    search_latitude DECIMAL(10, 8) NULL,
    search_longitude DECIMAL(11, 8) NULL,
    search_land_id INT NULL,
    search_certificate_number VARCHAR(50) NULL,

    -- Result
    land_found TINYINT(1) DEFAULT 0,
    land_id_found INT NULL,
    verification_status VARCHAR(50) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (checked_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (land_id_found) REFERENCES lands(id) ON DELETE SET NULL,

    INDEX idx_search_coords (search_latitude, search_longitude),
    INDEX idx_checked_by (checked_by_user_id),
    INDEX idx_created_at (created_at)
);

-- ============================================================================
-- STEP 13: SYSTEM SETTINGS FOR CBC
-- ============================================================================

INSERT INTO payment_settings (setting_key, setting_value, description) VALUES
('cbc_neighbor_required', '2', 'Minimum number of neighbor confirmations required'),
('cbc_neighbor_timeout_days', '14', 'Days to wait for neighbor confirmation before auto-proceed'),
('cbc_village_review_timeout_days', '7', 'Days for village leader to review'),
('certificate_validity_years', '0', 'Certificate validity in years (0 = no expiry)'),
('surveyor_verification_fee', '50000', 'Fee for professional surveyor verification in TZS'),
('certificate_fee', '10000', 'Fee for digital certificate in TZS'),
('beacon_installation_fee', '30000', 'Fee for physical beacon installation in TZS')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- ============================================================================
-- STEP 14: INDEXES FOR PERFORMANCE
-- ============================================================================

-- Composite indexes for common queries
CREATE INDEX IF NOT EXISTS idx_lands_village_status ON lands(village_id, status);
CREATE INDEX IF NOT EXISTS idx_lands_user_status ON lands(user_id, status);
CREATE INDEX IF NOT EXISTS idx_disputes_village_status ON land_disputes(village_id, status);

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

-- Log migration
INSERT INTO activity_logs (action, description) VALUES
('CBC_MIGRATION', 'Community Boundary Confirmation system tables created successfully');
