-- Migration: Add category column for SRM and Delivery roles
-- Run this on your database to add the category field

-- Add category column to users table
ALTER TABLE users 
ADD COLUMN IF NOT EXISTS category VARCHAR(50) NULL 
COMMENT 'Category for SRM/Delivery: agent_manager or collecting_point_incharge';

-- Add index for category lookups
CREATE INDEX IF NOT EXISTS idx_users_category ON users(category);

-- Add combined index for role + category lookups
CREATE INDEX IF NOT EXISTS idx_users_role_category ON users(role, category);

-- Verify the change
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'users' AND COLUMN_NAME = 'category';
