-- Migration: Add personal and bank details to users table
-- Run this migration on existing databases to add the new fields

-- Common fields for Employee and Contractor
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20) AFTER is_active;
ALTER TABLE users ADD COLUMN aadhar_number VARCHAR(12) AFTER phone_number;
ALTER TABLE users ADD COLUMN bank_account_number VARCHAR(30) AFTER aadhar_number;
ALTER TABLE users ADD COLUMN bank_name VARCHAR(100) AFTER bank_account_number;
ALTER TABLE users ADD COLUMN bank_ifsc VARCHAR(20) AFTER bank_name;

-- Contractor-specific fields
ALTER TABLE users ADD COLUMN contractor_agreement_number VARCHAR(50) AFTER bank_ifsc;
ALTER TABLE users ADD COLUMN pf_number VARCHAR(30) AFTER contractor_agreement_number;
ALTER TABLE users ADD COLUMN esic_number VARCHAR(30) AFTER pf_number;

-- Add indexes for commonly queried fields
CREATE INDEX idx_users_phone ON users(phone_number);
CREATE INDEX idx_users_aadhar ON users(aadhar_number);