-- =============================================================================
-- ARTH ERP — Employee KRA & performance tracking migration
-- =============================================================================
-- Import in phpMyAdmin or: mysql -u user -p database_name < 002_hr_performance.sql
-- Safe to re-run (CREATE TABLE IF NOT EXISTS).
-- =============================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS hr_employee_kras (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  title VARCHAR(150) NOT NULL,
  description TEXT NULL,
  period_type ENUM('daily','weekly','monthly') NOT NULL DEFAULT 'monthly',
  target_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  unit VARCHAR(40) NOT NULL DEFAULT 'units',
  weight DECIMAL(5,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_hr_kra_emp (employee_id),
  CONSTRAINT fk_hr_kra_emp FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_kra_progress (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  kra_id BIGINT UNSIGNED NOT NULL,
  period_key VARCHAR(20) NOT NULL,
  actual_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  recorded_by INT UNSIGNED NULL,
  recorded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_kra_progress (kra_id, period_key),
  CONSTRAINT fk_hr_kra_prog_kra FOREIGN KEY (kra_id) REFERENCES hr_employee_kras(id) ON DELETE CASCADE,
  CONSTRAINT fk_hr_kra_prog_user FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SELECT '002_hr_performance migration completed successfully.' AS status;