-- Salary Adjustment module: reduced pay (half month or by days) for a staff in a given period.
-- Run: mysql -u root -p university_payroll < sql/migration_salary_adjustments.sql

CREATE TABLE IF NOT EXISTS salary_adjustments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    period_month TINYINT NOT NULL,
    period_year SMALLINT NOT NULL,
    adjustment_type ENUM('half_month','days_worked') NOT NULL,
    days_worked INT NULL COMMENT 'Required when adjustment_type = days_worked; 1 to days in month',
    note VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_staff_period (staff_id, period_month, period_year),
    KEY idx_staff_period (staff_id, period_year, period_month),
    FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE
);
