CREATE TABLE IF NOT EXISTS designations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  dept_id INT NOT NULL,
  level VARCHAR(4) NOT NULL,
  description TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_designations_dept_name_active (dept_id, name, deleted_at),
  CONSTRAINT fk_designations_dept FOREIGN KEY (dept_id) REFERENCES departments(id),
  INDEX idx_designations_deleted (deleted_at),
  INDEX idx_designations_status (status),
  INDEX idx_designations_dept (dept_id)
);

ALTER TABLE employees ADD COLUMN desg_id INT NULL;
ALTER TABLE employees ADD CONSTRAINT fk_employees_desg FOREIGN KEY (desg_id) REFERENCES designations(id);