USE hrms_payroll;

CREATE TABLE IF NOT EXISTS devices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    port INT UNSIGNED NOT NULL DEFAULT 4370,
    serial_number VARCHAR(100) NULL UNIQUE,
    vendor ENUM('ZKTeco', 'ESSL', 'Matrix', 'Suprema', 'Hikvision') DEFAULT 'ZKTeco',
    status ENUM('Online', 'Offline', 'Testing') DEFAULT 'Offline',
    last_ping TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_users (
    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, -- User ID registered on the biometric device
    enrollment_status ENUM('Enrolled', 'Pending', 'Failed') DEFAULT 'Pending',
    rfid_card_number VARCHAR(50) NULL,
    face_enrolled BOOLEAN DEFAULT FALSE,
    fingerprint_enrolled BOOLEAN DEFAULT FALSE,
    last_sync 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 (device_id, device_user_id)
);

CREATE TABLE IF NOT EXISTS device_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL,
    log_type ENUM('Info', 'Warning', 'Error', 'Connection') DEFAULT 'Info',
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_health_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL,
    status ENUM('Online', 'Offline') NOT NULL,
    latency_ms INT NULL,
    checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_commands (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL,
    command ENUM('Restart', 'Shutdown', 'SyncTime', 'ClearLogs', 'ClearUsers', 'DownloadUsers', 'DownloadAttendance', 'UploadUser', 'Lock', 'Unlock', 'EnableUser', 'DisableUser') NOT NULL,
    payload JSON NULL,
    status ENUM('Pending', 'In Progress', 'Completed', 'Failed') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_command_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    command_id BIGINT UNSIGNED NOT NULL,
    status ENUM('In Progress', 'Completed', 'Failed') NOT NULL,
    response TEXT NULL,
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (command_id) REFERENCES device_commands(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS sync_queues (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue_type ENUM('EmployeeSync', 'AttendanceSync', 'DeviceCommand') NOT NULL,
    reference_id BIGINT UNSIGNED NOT NULL, -- e.g., employee_id or command_id
    payload JSON NULL,
    status ENUM('Pending', 'Processing', 'Success', 'Failed') DEFAULT 'Pending',
    attempts INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sync_failures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue_id BIGINT UNSIGNED NOT NULL,
    error_message TEXT NOT NULL,
    failed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (queue_id) REFERENCES sync_queues(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS sync_retry_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue_id BIGINT UNSIGNED NOT NULL,
    attempt_number INT UNSIGNED NOT NULL,
    status ENUM('Success', 'Failed') NOT NULL,
    error_message TEXT NULL,
    retried_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (queue_id) REFERENCES sync_queues(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_sync_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL,
    sync_type ENUM('UserPush', 'LogPull') NOT NULL,
    records_synced INT UNSIGNED DEFAULT 0,
    status ENUM('Success', 'Failed') NOT NULL,
    message TEXT NULL,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS attendance_import_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL,
    import_source ENUM('Device', 'File', 'API') DEFAULT 'Device',
    records_imported INT UNSIGNED DEFAULT 0,
    status ENUM('Success', 'Failed') NOT NULL,
    message TEXT NULL,
    imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);