-- Opel Lifts Attendance Management System
-- MySQL 8.x / MariaDB 10.5+ Schema
-- InnoDB Engine, UTF8MB4 Charset

CREATE DATABASE IF NOT EXISTS opel_attendance
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE opel_attendance;

-- Attendance Table
CREATE TABLE attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  date DATE NOT NULL,
  action ENUM('Login', 'Logout') NOT NULL,
  time VARCHAR(20) NOT NULL,
  timestamp DATETIME NOT NULL,
  latitude DECIMAL(10, 8) DEFAULT NULL,
  longitude DECIMAL(11, 8) DEFAULT NULL,
  location_url VARCHAR(255) DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  is_auto_logout TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_employee_date (employee_id, date),
  INDEX idx_date (date),
  INDEX idx_action (action),
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
