USE hrms_payroll;

CREATE TABLE IF NOT EXISTS employee_device_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    device_id BIGINT UNSIGNED NOT NULL,
    device_user_id VARCHAR(50) NOT NULL,
    card_number VARCHAR(50) NULL,
    sync_status ENUM('Pending', 'Synced', 'Failed', 'Not Assigned') DEFAULT 'Pending',
    last_synced_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    UNIQUE KEY uq_employee_device (employee_id, device_id),
    UNIQUE KEY uq_device_user (device_id, device_user_id)
);

CREATE TABLE IF NOT EXISTS device_sync_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    device_id BIGINT UNSIGNED NOT NULL,
    action ENUM('Push Employee', 'Update Employee', 'Delete Employee', 'Sync Again') NOT NULL,
    status ENUM('Success', 'Failed') NOT NULL,
    response_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);