-- ============================================
-- SQL TAMBAHAN: SCAN ABSENSI QR + RFID ANTI TITIP
-- Compatible dengan tabel existing infe8543_metasby
-- ============================================

-- 1. Tambah kolom QR & RFID di tabel employees/users yang sudah ada
-- Cek dulu nama tabel karyawan kamu, biasanya `employees` atau `users`
ALTER TABLE `employees` 
ADD COLUMN `qr_token` VARCHAR(64) DEFAULT NULL COMMENT 'QR dinamis, rotasi tiap hari' AFTER `id`,
ADD COLUMN `qr_token_expired_at` DATETIME DEFAULT NULL,
ADD COLUMN `rfid_uid` VARCHAR(32) DEFAULT NULL COMMENT 'UID kartu RFID 10 digit hex' AFTER `qr_token`,
ADD COLUMN `rfid_card_number` VARCHAR(20) DEFAULT NULL,
ADD COLUMN `face_embedding` TEXT DEFAULT NULL COMMENT 'Base64 face embedding untuk anti titip',
ADD INDEX `idx_qr_token` (`qr_token`),
ADD INDEX `idx_rfid_uid` (`rfid_uid`);

-- Kalau pakai tabel users sebagai karyawan:
ALTER TABLE `users`
ADD COLUMN `qr_token` VARCHAR(64) DEFAULT NULL AFTER `id`,
ADD COLUMN `rfid_uid` VARCHAR(32) DEFAULT NULL AFTER `qr_token`,
ADD INDEX `idx_qr_rfid` (`qr_token`, `rfid_uid`);

-- 2. Tabel master device scanner (tablet di depan pintu + RFID reader)
CREATE TABLE IF NOT EXISTS `attendance_devices` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `company_id` INT(11) NOT NULL,
  `device_name` VARCHAR(100) NOT NULL COMMENT 'Contoh: Pintu Utama Lt1',
  `device_code` VARCHAR(50) NOT NULL COMMENT 'DEV-001',
  `device_type` ENUM('QR','RFID','QR_RFID','TABLET') DEFAULT 'QR_RFID',
  `location_name` VARCHAR(100) DEFAULT NULL,
  `latitude` DECIMAL(10,8) DEFAULT NULL,
  `longitude` DECIMAL(11,8) DEFAULT NULL,
  `radius_meter` INT DEFAULT 100,
  `is_active` TINYINT(1) DEFAULT 1,
  `last_seen_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `device_code` (`device_code`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Tabel utama scan log QR/RFID - ini inti anti titip
CREATE TABLE IF NOT EXISTS `attendance_qr_rfid_logs` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `company_id` INT(11) NOT NULL,
  `employee_id` INT(11) NOT NULL,
  `device_id` INT(11) DEFAULT NULL,
  `scan_type` ENUM('QR','RFID','MANUAL') NOT NULL,
  `qr_token` VARCHAR(64) DEFAULT NULL,
  `rfid_uid` VARCHAR(32) DEFAULT NULL,
  `scan_time` DATETIME NOT NULL COMMENT 'Waktu scan di device',
  `server_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Waktu sampai server',
  `type` ENUM('IN','OUT') NOT NULL DEFAULT 'IN',
  `latitude` DECIMAL(10,8) DEFAULT NULL,
  `longitude` DECIMAL(11,8) DEFAULT NULL,
  `is_inside_radius` TINYINT(1) DEFAULT 1,
  `distance_meter` INT DEFAULT NULL,
  `selfie_required` TINYINT(1) DEFAULT 1 COMMENT '1=wajib selfie setelah scan',
  `selfie_path` VARCHAR(255) DEFAULT NULL,
  `face_match_score` DECIMAL(5,2) DEFAULT NULL COMMENT '0-100, min 80% untuk lolos',
  `face_match_status` ENUM('PENDING','MATCH','MISMATCH','NO_FACE') DEFAULT 'PENDING',
  `anti_fake_check` ENUM('PASS','FAIL','PENDING') DEFAULT 'PENDING' COMMENT 'Deteksi foto layar, fake GPS',
  `status` ENUM('SUCCESS','REJECTED_FACE_MISMATCH','REJECTED_OUTSIDE_RADIUS','REJECTED_FAKE_GPS','REJECTED_QR_EXPIRED','REJECTED_RFID_NOT_REGISTERED','REJECTED_DUPLICATE') DEFAULT 'SUCCESS',
  `notes` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `employee_id` (`employee_id`),
  KEY `scan_time` (`scan_time`),
  KEY `qr_token` (`qr_token`),
  KEY `rfid_uid` (`rfid_uid`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Tabel QR rotasi harian (QR dinamis biar tidak bisa di-screenshot & share)
CREATE TABLE IF NOT EXISTS `qr_daily_tokens` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `company_id` INT(11) NOT NULL,
  `employee_id` INT(11) NOT NULL,
  `qr_token` VARCHAR(64) NOT NULL,
  `valid_date` DATE NOT NULL COMMENT 'Berlaku hari ini saja',
  `is_used` TINYINT(1) DEFAULT 0,
  `used_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_token` (`qr_token`),
  KEY `employee_date` (`employee_id`,`valid_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. View untuk dashboard anti titip
CREATE OR REPLACE VIEW `view_attendance_anti_titip` AS
SELECT 
  l.id, l.company_id, c.name as company_name,
  e.name as employee_name, e.rfid_uid, l.scan_type, l.scan_time, l.type,
  l.is_inside_radius, l.distance_meter,
  l.face_match_score, l.face_match_status, l.status,
  l.selfie_path,
  TIMESTAMPDIFF(SECOND, l.scan_time, l.server_time) as delay_seconds
FROM attendance_qr_rfid_logs l
JOIN companies c ON c.id=l.company_id
JOIN employees e ON e.id=l.employee_id
WHERE l.created_at >= CURDATE() - INTERVAL 7 DAY
ORDER BY l.scan_time DESC;

-- 6. Insert device contoh
INSERT INTO attendance_devices (company_id, device_name, device_code, device_type, location_name, radius_meter) VALUES
(1, 'Pintu Utama', 'DEV-001', 'QR_RFID', 'Lobi Utama', 100);
