-- =====================================================================
--  HRPay LK — Module 2: Attendance Tracking (additive schema)
--  Run AFTER the Module 1 schema. On cPanel: select your DB in phpMyAdmin,
--  then Import this file. (No CREATE DATABASE / USE lines.)
-- =====================================================================

-- ----------------------------------------------------------------------
-- Public / company holidays (Poya days, mercantile & public holidays).
-- Used to classify a work_date as a holiday so OT is rated correctly.
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS holidays (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  holiday_date  DATE         NOT NULL,
  name          VARCHAR(150) NOT NULL,
  type          ENUM('public','poya','mercantile','bank','company') NOT NULL DEFAULT 'public',
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_holiday_date (holiday_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------
-- Daily attendance — one row per employee per day.
-- worked_hours / late_minutes / ot_hours are computed by the app and
-- consumed by Payroll (Module 4) and PAYE/OT (Modules 4-6).
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS attendance (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id   INT UNSIGNED NOT NULL,
  work_date     DATE         NOT NULL,
  check_in      TIME             NULL,
  check_out     TIME             NULL,
  status        ENUM('present','absent','half_day','leave','holiday','off') NOT NULL DEFAULT 'present',
  day_type      ENUM('workday','weekend','holiday') NOT NULL DEFAULT 'workday',
  worked_hours  DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  late_minutes  INT UNSIGNED NOT NULL DEFAULT 0,
  ot_hours      DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  source        ENUM('manual','clock','import') NOT NULL DEFAULT 'manual',
  remarks       VARCHAR(255)     NULL,
  created_by    INT UNSIGNED     NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_att_emp_date (employee_id, work_date),
  KEY idx_att_date (work_date),
  KEY idx_att_status (status),
  CONSTRAINT fk_att_emp FOREIGN KEY (employee_id)
      REFERENCES employees (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A few sample 2026 holidays (edit/extend in the Holidays page).
INSERT INTO holidays (holiday_date, name, type) VALUES
  ('2026-01-14', 'Tamil Thai Pongal Day', 'public'),
  ('2026-02-04', 'Independence Day', 'public'),
  ('2026-05-01', 'May Day', 'public')
ON DUPLICATE KEY UPDATE name = VALUES(name);