-- --------------------------------------------------------
-- File SQL: absensi_rfid.sql
-- Sistem Absensi Karyawan dengan RFID (PHP + MySQL)
-- --------------------------------------------------------
 

-- --------------------------------------------------------
-- Table: departments
-- --------------------------------------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Data awal departemen
INSERT INTO `departments` (`name`) VALUES
('HRD'),
('IT'),
('Finance'),
('Marketing');

-- --------------------------------------------------------
-- Table: employees
-- --------------------------------------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `rfid_uid` VARCHAR(100) NOT NULL UNIQUE,
  `department_id` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Data awal karyawan
INSERT INTO `employees` (`name`, `rfid_uid`, `department_id`) VALUES
('Andi Setiawan', 'RFID12345', 2),
('Budi Santoso', 'RFID54321', 3),
('Citra Dewi', 'RFID67890', 1);

-- --------------------------------------------------------
-- Table: attendances
-- --------------------------------------------------------
DROP TABLE IF EXISTS `attendances`;
CREATE TABLE `attendances` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` INT NOT NULL,
  `rfid_uid` VARCHAR(100) NOT NULL,
  `type` ENUM('masuk','pulang') NOT NULL,
  `status` ENUM('hadir','terlambat','izin','sakit','alpha') DEFAULT 'hadir',
  `attendance_date` DATE NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uniq_attendance` (`employee_id`, `type`, `attendance_date`)
) ENGINE=InnoDB;

-- --------------------------------------------------------
-- Contoh Data Absensi (opsional)
-- --------------------------------------------------------
INSERT INTO `attendances` (`employee_id`, `rfid_uid`, `type`, `status`, `attendance_date`, `created_at`) VALUES
(1, 'RFID12345', 'masuk', 'hadir', CURDATE(), NOW()),
(2, 'RFID54321', 'masuk', 'terlambat', CURDATE(), NOW());
