-- =====================================================================
-- KIOSK DTR / BIOMETRIC PAYROLL  -  Working Version 2
-- Database: ogzguwwq_kiosk
-- Phase 1: PIN + snapshot attendance (face_descriptors table prepared
--          for Phase 2 but unused now)
-- Conventions: MyISAM-safe / InnoDB, no DEFINER on views, cPanel-friendly.
-- MySQL 5.x compatible (no JSON type, no CTEs, no window functions).
-- =====================================================================

SET sql_mode = '';

-- ----------------------------------------------------------------------
-- DEPARTMENTS
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS departments (
  dept_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  dept_code      VARCHAR(20)  NOT NULL,
  dept_name      VARCHAR(100) NOT NULL,
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  created_at     DATETIME     NOT NULL,
  PRIMARY KEY (dept_id),
  UNIQUE KEY uq_dept_code (dept_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- EMPLOYEES  (the program is the source of truth for all of this)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS employees (
  emp_id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  emp_no         VARCHAR(32)  NOT NULL,          -- stable external id used at kiosk
  first_name     VARCHAR(60)  NOT NULL,
  last_name      VARCHAR(60)  NOT NULL,
  middle_name    VARCHAR(60)  NULL,
  dept_id        INT UNSIGNED NULL,
  position       VARCHAR(100) NULL,
  email          VARCHAR(120) NULL,
  daily_rate     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  hourly_rate    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  schedule_id    INT UNSIGNED NULL,
  photo_path     VARCHAR(255) NULL,              -- profile photo (HR)
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  created_at     DATETIME     NOT NULL,
  updated_at     DATETIME     NULL,
  PRIMARY KEY (emp_id),
  UNIQUE KEY uq_emp_no (emp_no),
  KEY idx_emp_dept (dept_id),
  KEY idx_emp_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- PINS  (hashed; one active PIN per employee)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS emp_pins (
  pin_id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  emp_id         INT UNSIGNED NOT NULL,
  pin_hash       VARCHAR(255) NOT NULL,          -- sha256 hash, never plain
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  created_at     DATETIME     NOT NULL,
  PRIMARY KEY (pin_id),
  KEY idx_pin_emp (emp_id),
  KEY idx_pin_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- FACE DESCRIPTORS  (Phase 2 - prepared now, unused in Phase 1)
-- 128 floats stored as comma-separated text (MySQL 5.x has no JSON/array)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS face_descriptors (
  face_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  emp_id         INT UNSIGNED NOT NULL,
  descriptor     TEXT         NOT NULL,          -- "0.123,-0.045,..." 128 vals
  quality        DECIMAL(5,2) NULL,
  sample_no      TINYINT      NOT NULL DEFAULT 1,
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  created_at     DATETIME     NOT NULL,
  PRIMARY KEY (face_id),
  KEY idx_face_emp (emp_id),
  KEY idx_face_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- KIOSK DEVICES  (each tablet/phone registers a label; multi-device ready)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS kiosks (
  kiosk_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  kiosk_key      VARCHAR(64)  NOT NULL,          -- shared secret in kiosk URL/config
  label          VARCHAR(80)  NOT NULL,          -- "Main Gate", "HR Office"
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  last_seen      DATETIME     NULL,
  created_at     DATETIME     NOT NULL,
  PRIMARY KEY (kiosk_id),
  UNIQUE KEY uq_kiosk_key (kiosk_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- PUNCHES  (raw in/out events; device-agnostic so a hardware terminal
--           can write here later without schema change)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS punches (
  punch_id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  emp_id         INT UNSIGNED NOT NULL,
  emp_no         VARCHAR(32)  NOT NULL,          -- denormalized for fast reads
  punch_time     DATETIME     NOT NULL,
  direction      ENUM('IN','OUT') NOT NULL,
  matched_by     ENUM('PIN','FACE','MANUAL') NOT NULL DEFAULT 'PIN',
  kiosk_id       INT UNSIGNED NULL,
  kiosk_label    VARCHAR(80)  NULL,
  snapshot_path  VARCHAR(255) NULL,              -- audit photo for this punch
  lat            DECIMAL(10,7) NULL,             -- reserved for geofence (later)
  lng            DECIMAL(10,7) NULL,
  ip_addr        VARCHAR(45)  NULL,
  source         VARCHAR(20)  NOT NULL DEFAULT 'kiosk',
  created_at     DATETIME     NOT NULL,
  PRIMARY KEY (punch_id),
  KEY idx_punch_emp (emp_id),
  KEY idx_punch_time (punch_time),
  KEY idx_punch_emp_time (emp_id, punch_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- SCHEDULES  (basic shift definition; DTR engine is Phase 2/3)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS schedules (
  schedule_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sched_name     VARCHAR(80)  NOT NULL,
  time_in        TIME         NOT NULL DEFAULT '08:00:00',
  time_out       TIME         NOT NULL DEFAULT '17:00:00',
  grace_minutes  INT          NOT NULL DEFAULT 0,
  break_minutes  INT          NOT NULL DEFAULT 60,
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  created_at     DATETIME     NOT NULL,
  PRIMARY KEY (schedule_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- HOLIDAYS  (PH regular / special; used by DTR engine later)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS holidays (
  holiday_id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  holiday_date   DATE         NOT NULL,
  holiday_name   VARCHAR(120) NOT NULL,
  holiday_type   ENUM('REGULAR','SPECIAL') NOT NULL DEFAULT 'REGULAR',
  created_at     DATETIME     NOT NULL,
  PRIMARY KEY (holiday_id),
  UNIQUE KEY uq_holiday_date (holiday_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- ADMIN USERS  (ScriptCase admin auth lives here; simple for v2)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_users (
  admin_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username       VARCHAR(40)  NOT NULL,
  pass_hash      VARCHAR(255) NOT NULL,
  full_name      VARCHAR(100) NULL,
  role           ENUM('ADMIN','HR','VIEWER') NOT NULL DEFAULT 'HR',
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  created_at     DATETIME     NOT NULL,
  PRIMARY KEY (admin_id),
  UNIQUE KEY uq_admin_user (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- AUDIT LOG
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
  log_id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor          VARCHAR(60)  NULL,
  action         VARCHAR(60)  NOT NULL,
  detail         VARCHAR(255) NULL,
  ip_addr        VARCHAR(45)  NULL,
  created_at     DATETIME     NOT NULL,
  PRIMARY KEY (log_id),
  KEY idx_audit_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- =====================================================================
-- VIEWS  (DEFINER-free for hosting compatibility)
-- =====================================================================

-- Today's punches, newest first, for the live dashboard
CREATE OR REPLACE VIEW v_punches_today AS
SELECT
  p.punch_id,
  p.emp_id,
  p.emp_no,
  CONCAT(e.last_name, ', ', e.first_name) AS emp_name,
  d.dept_name,
  p.punch_time,
  p.direction,
  p.matched_by,
  p.kiosk_label,
  p.snapshot_path
FROM punches p
JOIN employees e ON e.emp_id = p.emp_id
LEFT JOIN departments d ON d.dept_id = e.dept_id
WHERE p.punch_time >= CONCAT(CURDATE(), ' 00:00:00')
  AND p.punch_time <= CONCAT(CURDATE(), ' 23:59:59')
ORDER BY p.punch_time DESC;

-- Current in/out status per active employee (last punch today)
CREATE OR REPLACE VIEW v_employee_status AS
SELECT
  e.emp_id,
  e.emp_no,
  CONCAT(e.last_name, ', ', e.first_name) AS emp_name,
  d.dept_name,
  (SELECT pp.direction FROM punches pp
     WHERE pp.emp_id = e.emp_id
       AND pp.punch_time >= CONCAT(CURDATE(), ' 00:00:00')
     ORDER BY pp.punch_time DESC LIMIT 1) AS last_direction,
  (SELECT pp.punch_time FROM punches pp
     WHERE pp.emp_id = e.emp_id
       AND pp.punch_time >= CONCAT(CURDATE(), ' 00:00:00')
     ORDER BY pp.punch_time DESC LIMIT 1) AS last_punch_time
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id
WHERE e.is_active = 1;

-- Daily first-IN / last-OUT summary (basis for DTR engine in later phase)
CREATE OR REPLACE VIEW v_daily_dtr AS
SELECT
  p.emp_id,
  p.emp_no,
  DATE(p.punch_time) AS work_date,
  MIN(CASE WHEN p.direction = 'IN'  THEN p.punch_time END) AS first_in,
  MAX(CASE WHEN p.direction = 'OUT' THEN p.punch_time END) AS last_out,
  COUNT(*) AS punch_count
FROM punches p
GROUP BY p.emp_id, p.emp_no, DATE(p.punch_time);