-- Add missing columns to users table for CBC system
-- Run this in phpMyAdmin

-- Add national_id column
ALTER TABLE users ADD COLUMN IF NOT EXISTS national_id VARCHAR(50) NULL AFTER phone;

-- Add location columns
ALTER TABLE users ADD COLUMN IF NOT EXISTS region_id INT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS district_id INT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS village_id INT NULL;

-- Update role ENUM to include new CBC roles
ALTER TABLE users MODIFY COLUMN role ENUM(
    'admin',
    'broker',
    'user',
    'village_chairman',
    'land_officer',
    'surveyor',
    'district_officer',
    'land_council',
    'user_admin'
) DEFAULT 'user';

-- Done!
SELECT 'Columns added successfully!' AS result;
