CREATE OR REPLACE FUNCTION give_raise(
  p_emp_id    INT,
  p_pct       NUMERIC,
  p_reason    TEXT DEFAULT 'Annual review'
)
RETURNS TABLE(emp_id INT, old_salary NUMERIC, new_salary NUMERIC) AS $$
DECLARE
  v_old_sal NUMERIC;
  v_new_sal NUMERIC;
BEGIN
  IF p_pct <= 0 OR p_pct > 50 THEN
    RAISE EXCEPTION 'Raise percentage must be between 0 and 50. Got: %', p_pct;
  END IF;

  SELECT salary INTO v_old_sal FROM employees WHERE employees.emp_id = p_emp_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Employee % not found', p_emp_id;
  END IF;

  v_new_sal := ROUND(v_old_sal * (1 + p_pct/100), 2);

  UPDATE employees SET salary = v_new_sal WHERE employees.emp_id = p_emp_id;

  INSERT INTO salary_history(emp_id, old_salary, new_salary, reason)
  VALUES (p_emp_id, v_old_sal, v_new_sal, p_reason);

  RETURN QUERY SELECT p_emp_id, v_old_sal, v_new_sal;
END;
$$ LANGUAGE plpgsql;

-- Usage:
SELECT * FROM give_raise(101, 10.5, 'Promotion');