-- ===========================================================================
-- Migration: Deeper HR (attendance, leave, performance)
-- Date: 2026-07-30
--
-- Extends the existing employees directory with:
--   hr_attendance — daily attendance per employee (present/absent/late/…)
--   hr_leave      — leave requests, tracked pending -> approved/rejected (the
--                   "approvals" workflow)
--   hr_reviews    — periodic performance reviews with a 1–5 rating
-- All under the existing 'hr' permission. Idempotent.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS hr_attendance (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  work_date   DATE         NOT NULL,
  status      ENUM('present','absent','late','half_day','leave') NOT NULL DEFAULT 'present',
  hours       DECIMAL(5,2) NULL,
  note        VARCHAR(255) NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_att (farm_id, employee_id, work_date),
  KEY idx_att_farm (farm_id, work_date),
  CONSTRAINT fk_att_farm FOREIGN KEY (farm_id)     REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_att_emp  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS hr_leave (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  leave_type  VARCHAR(40)  NOT NULL DEFAULT 'annual',   -- annual|sick|unpaid|compassionate|other
  from_date   DATE         NOT NULL,
  to_date     DATE         NOT NULL,
  days        DECIMAL(4,1) NOT NULL DEFAULT 1,
  reason      VARCHAR(255) NULL,
  status      ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  decided_by  VARCHAR(120) NULL,
  decided_on  DATE         NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_leave_farm (farm_id, status),
  KEY idx_leave_emp (employee_id),
  CONSTRAINT fk_leave_farm FOREIGN KEY (farm_id)     REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_leave_emp  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS hr_reviews (
  id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id      INT UNSIGNED NOT NULL,
  employee_id  INT UNSIGNED NOT NULL,
  period       VARCHAR(40)  NOT NULL,               -- e.g. "2026 H1", "Q1 2026"
  review_date  DATE         NOT NULL,
  reviewer     VARCHAR(120) NULL,
  rating       TINYINT UNSIGNED NULL,               -- 1..5
  strengths    VARCHAR(500) NULL,
  improvements VARCHAR(500) NULL,
  notes        VARCHAR(500) NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rev_farm (farm_id),
  KEY idx_rev_emp (employee_id),
  CONSTRAINT fk_rev_farm FOREIGN KEY (farm_id)     REFERENCES farms(id)     ON DELETE CASCADE,
  CONSTRAINT fk_rev_emp  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;