-- Staff roles (office roles) and staff.staff_office_role_id
-- Allowance rules by parameters (all / by union / by category / by office role)
-- Run this migration (e.g. mysql -u root university_payroll < sql/migration_staff_roles_and_allowance_parameter_rules.sql)
-- If staff already has staff_office_role_id, comment out or skip the two ALTER TABLE staff lines below.

-- 1. Staff roles table
CREATE TABLE IF NOT EXISTS staff_roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Add office role to staff (skip if column already exists)
ALTER TABLE staff ADD COLUMN staff_office_role_id INT NULL AFTER union_id;
ALTER TABLE staff ADD CONSTRAINT fk_staff_office_role FOREIGN KEY (staff_office_role_id) REFERENCES staff_roles(id) ON DELETE SET NULL;

-- 3. Allowance parameter rules
CREATE TABLE IF NOT EXISTS allowance_parameter_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    allowance_type_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    apply_to ENUM('all','by_union','by_category','by_office_role','by_scale_type') NOT NULL,
    union_id INT NULL,
    staff_category_id INT NULL,
    staff_office_role_id INT NULL,
    scale_type VARCHAR(50) NULL COMMENT 'When apply_to=by_scale_type: e.g. CONTISS, CONUASS',
    effective_date DATE NULL,
    end_date DATE NULL,
    FOREIGN KEY (allowance_type_id) REFERENCES allowance_types(id) ON DELETE CASCADE,
    FOREIGN KEY (union_id) REFERENCES `unions`(id) ON DELETE CASCADE,
    FOREIGN KEY (staff_category_id) REFERENCES staff_categories(id) ON DELETE CASCADE,
    FOREIGN KEY (staff_office_role_id) REFERENCES staff_roles(id) ON DELETE CASCADE
);

CREATE INDEX idx_allowance_parameter_rules_type ON allowance_parameter_rules (allowance_type_id);
CREATE INDEX idx_allowance_parameter_rules_dates ON allowance_parameter_rules (effective_date, end_date);
