create_timeclock_table.sql

-- Time Clock System for Employee Clock In/Out
-- Tracks employee clock in and clock out times
-- Automatically calculates hours and minutes worked

-- Create the time_clock table
CREATE TABLE IF NOT EXISTS `time_clock` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `employee_no` INT(11) NOT NULL,
  `clock_in` DATETIME NOT NULL,
  `clock_out` DATETIME DEFAULT NULL,
  `total_hours` DECIMAL(6,2) DEFAULT 0.00 COMMENT 'Total hours worked (calculated)',
  `break_minutes` INT DEFAULT 0 COMMENT 'Break time in minutes (manual entry)',
  `notes` TEXT DEFAULT NULL COMMENT 'Optional notes about the shift',
  `status` ENUM('clocked_in', 'clocked_out') DEFAULT 'clocked_in',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employee_no` (`employee_no`),
  KEY `clock_in_idx` (`clock_in`),
  KEY `status_idx` (`status`),
  KEY `employee_date_idx` (`employee_no`, `clock_in`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add time clock enable/disable setting to company_settings
ALTER TABLE `company_settings`
  ADD COLUMN `timeclock_enabled` TINYINT(1) DEFAULT 0 COMMENT 'Enable/disable time clock feature (0=disabled, 1=enabled)';