-- =====================================================================
--  HRPay LK — Module 2b: Shifts & Timetable (additive schema)
--  Run AFTER module2_attendance.sql. Import into your existing DB.
-- =====================================================================

-- ----------------------------------------------------------------------
-- Shift definitions (Day / Night / etc). Drives lateness & OT calculation.
-- crosses_midnight = 1 for night shifts whose end time is on the next day.
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS shifts (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code             VARCHAR(20)  NOT NULL,
  name             VARCHAR(80)  NOT NULL,
  start_time       TIME         NOT NULL,
  end_time         TIME         NOT NULL,
  break_minutes    INT UNSIGNED NOT NULL DEFAULT 60,
  standard_hours   DECIMAL(4,2) NOT NULL DEFAULT 8.00,
  grace_minutes    INT UNSIGNED NOT NULL DEFAULT 15,
  crosses_midnight TINYINT(1)   NOT NULL DEFAULT 0,
  color            VARCHAR(7)   NOT NULL DEFAULT '#0c6b5e',
  is_active        TINYINT(1)   NOT NULL DEFAULT 1,
  created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_shift_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------
-- Employee shift assignments, effective-dated so rosters can change over
-- time. The shift in force on a given day is the assignment whose date
-- range covers that day (latest effective_from wins).
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS employee_shifts (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id    INT UNSIGNED NOT NULL,
  shift_id       INT UNSIGNED NOT NULL,
  effective_from DATE         NOT NULL,
  effective_to   DATE             NULL,   -- NULL = open-ended
  created_by     INT UNSIGNED     NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_es_emp (employee_id, effective_from),
  KEY idx_es_range (employee_id, effective_from, effective_to),
  CONSTRAINT fk_es_emp FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE CASCADE,
  CONSTRAINT fk_es_shift FOREIGN KEY (shift_id) REFERENCES shifts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed a standard 2-shift system (Day + Night).
INSERT INTO shifts (code, name, start_time, end_time, break_minutes, standard_hours, grace_minutes, crosses_midnight, color)
VALUES
  ('DAY',   'Day Shift',   '08:30:00', '17:30:00', 60, 8.00, 15, 0, '#0c6b5e'),
  ('NIGHT', 'Night Shift', '22:00:00', '06:00:00', 60, 8.00, 15, 1, '#3b3a7a')
ON DUPLICATE KEY UPDATE name = VALUES(name);