ALTER TABLE employees
  ADD COLUMN name VARCHAR(255) NOT NULL DEFAULT '',
  ADD COLUMN email VARCHAR(255) NOT NULL DEFAULT '',
  ADD COLUMN phone VARCHAR(50) NULL,
  ADD COLUMN join_date DATE NOT NULL DEFAULT (CURDATE()),
  ADD COLUMN location VARCHAR(255) NULL,
  ADD COLUMN avatar_storage_key VARCHAR(512) NULL,
  ADD COLUMN avatar_thumbnail_key VARCHAR(512) NULL,
  ADD COLUMN avatar_color VARCHAR(32) NOT NULL DEFAULT '#6366f1',
  ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE employees
  MODIFY COLUMN name VARCHAR(255) NOT NULL,
  MODIFY COLUMN email VARCHAR(255) NOT NULL;

ALTER TABLE employees ADD UNIQUE KEY uq_employees_email_active (email, deleted_at);

CREATE TABLE IF NOT EXISTS employee_skills (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_employee_skills_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  INDEX idx_employee_skills_emp (employee_id)
);