-- Indexes for 5000+ staff: faster filters, joins, and search.
-- Run on existing database. New installs: see schema.sql for same indexes.
USE university_payroll;

-- Staff: list by department/status, search by name/number/payroll_id
CREATE INDEX idx_staff_department_id ON staff (department_id);
CREATE INDEX idx_staff_status ON staff (status);
CREATE INDEX idx_staff_name ON staff (last_name, first_name);
-- staff_number and payroll_id already indexed via UNIQUE

-- staff_allowances: by staff and type
CREATE INDEX idx_staff_allowances_staff_id ON staff_allowances (staff_id);
CREATE INDEX idx_staff_allowances_effective ON staff_allowances (staff_id, effective_date);

-- staff_deductions: by staff and type
CREATE INDEX idx_staff_deductions_staff_id ON staff_deductions (staff_id);
CREATE INDEX idx_staff_deductions_effective ON staff_deductions (staff_id, effective_date);

-- salary_arrears: by staff and period
CREATE INDEX idx_salary_arrears_staff_id ON salary_arrears (staff_id);
CREATE INDEX idx_salary_arrears_period ON salary_arrears (arrear_year, arrear_month);

-- payroll: period and staff lookups
CREATE INDEX idx_payroll_records_period ON payroll_records (payroll_period_id);
CREATE INDEX idx_payroll_records_staff ON payroll_records (staff_id);
CREATE INDEX idx_payroll_items_record ON payroll_items (payroll_record_id);
CREATE INDEX idx_payroll_items_type_name ON payroll_items (payroll_record_id, item_type, item_name);
