-- Employee HR enrichment: manager, employment lifecycle, personal, shift mode, salary.
-- Portal users remain provisioned via employees.user_id (010_employee_portal_users).

ALTER TABLE employees
  ADD COLUMN manager_employee_id INT NULL AFTER desg_id,
  ADD COLUMN employment_type ENUM('full_time','part_time','contractor','intern') NOT NULL DEFAULT 'full_time' AFTER status,
  ADD COLUMN employment_status ENUM('probation','active','on_leave','suspended','notice','exited','alumni') NOT NULL DEFAULT 'active' AFTER employment_type,
  ADD COLUMN probation_end_date DATE NULL AFTER employment_status,
  ADD COLUMN confirmation_date DATE NULL AFTER probation_end_date,
  ADD COLUMN date_of_birth DATE NULL AFTER confirmation_date,
  ADD COLUMN gender VARCHAR(32) NULL AFTER date_of_birth,
  ADD COLUMN emergency_contact_name VARCHAR(255) NULL AFTER gender,
  ADD COLUMN emergency_contact_phone VARCHAR(50) NULL AFTER emergency_contact_name,
  ADD COLUMN address_line TEXT NULL AFTER emergency_contact_phone,
  ADD COLUMN shift_mode ENUM('global','custom') NOT NULL DEFAULT 'global' AFTER address_line,
  ADD COLUMN basic_salary DECIMAL(14,2) NULL AFTER shift_mode,
  ADD COLUMN salary_currency VARCHAR(8) NOT NULL DEFAULT 'PKR' AFTER basic_salary,
  ADD COLUMN salary_effective_from DATE NULL AFTER salary_currency;

ALTER TABLE employees
  ADD CONSTRAINT fk_employees_manager FOREIGN KEY (manager_employee_id) REFERENCES employees(id) ON DELETE SET NULL;

-- Company default shift + per-employee custom windows (used when shift_mode = custom).
CREATE TABLE IF NOT EXISTS hr_shift_policies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL DEFAULT 'Company Default',
  is_global TINYINT(1) NOT NULL DEFAULT 1,
  work_days JSON NOT NULL,
  shift_start TIME NOT NULL DEFAULT '09:00:00',
  shift_end TIME NOT NULL DEFAULT '18:00:00',
  grace_clock_in_minutes INT NOT NULL DEFAULT 15,
  grace_clock_out_minutes INT NOT NULL DEFAULT 15,
  break_allowed_minutes INT NOT NULL DEFAULT 60,
  break_grace_minutes INT NOT NULL DEFAULT 5,
  max_breaks INT NOT NULL DEFAULT 2,
  timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Karachi',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  effective_from DATE NOT NULL DEFAULT (CURDATE()),
  effective_to DATE NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL
);

INSERT INTO hr_shift_policies (name, is_global, work_days, shift_start, shift_end, grace_clock_in_minutes, grace_clock_out_minutes, break_allowed_minutes, break_grace_minutes)
SELECT 'Company Default', 1, '[1,2,3,4,5]', '09:00:00', '18:00:00', 15, 15, 60, 5
WHERE NOT EXISTS (SELECT 1 FROM hr_shift_policies WHERE is_global = 1 AND deleted_at IS NULL);

CREATE TABLE IF NOT EXISTS employee_shift_overrides (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  work_days JSON NOT NULL,
  shift_start TIME NOT NULL,
  shift_end TIME NOT NULL,
  grace_clock_in_minutes INT NOT NULL DEFAULT 15,
  grace_clock_out_minutes INT NOT NULL DEFAULT 15,
  break_allowed_minutes INT NOT NULL DEFAULT 60,
  break_grace_minutes INT NOT NULL DEFAULT 5,
  max_breaks INT NOT NULL DEFAULT 2,
  timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Karachi',
  effective_from DATE NOT NULL DEFAULT (CURDATE()),
  effective_to DATE NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_emp_shift_override (employee_id),
  CONSTRAINT fk_emp_shift_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);