import grpc
import openpyxl  # Requires: pip install openpyxl
from chirpstack_api import api

# ===== Configuration =====
GRPC_SERVER = "192.168.x.x:8080"
API_TOKEN = " "
APPLICATION_ID = " "
DEVICE_PROFILE_ID = " "

# Excel file path (in the same directory as the script)
EXCEL_FILE = "device_list.xlsx"
# =================

def read_devices_from_excel(filename):
    """
    Read device information from Excel file.
    Expected columns: dev_eui, name, description, dev_addr, nwk_s_key, app_s_key
    Note: The first row of Excel should be the column names (as shown in the example)
    """
    devices = []
    try:
        wb = openpyxl.load_workbook(filename)
        sheet = wb.active  # Use the first sheet

        # Find column indices (assuming the first row is the header)
        headers = {}
        for col_idx, cell in enumerate(sheet[1], 1):
            if cell.value:
                headers[cell.value.strip()] = col_idx

        # Iterate over data rows (starting from the second row)
        for row_idx in range(2, sheet.max_row + 1):
            row = sheet[row_idx]
            # If dev_eui is empty, skip this row
            dev_eui = row[headers.get("dev_eui", 1) - 1].value
            if not dev_eui:
                continue

            device = {
                "dev_eui": str(dev_eui),
                "name": str(row[headers.get("name", 2) - 1].value or ""),
                "description": str(row[headers.get("description", 3) - 1].value or ""),
                "dev_addr": str(row[headers.get("dev_addr", 4) - 1].value or ""),
                "nwk_s_key": str(row[headers.get("nwk_s_key", 5) - 1].value or ""),
                "app_s_key": str(row[headers.get("app_s_key", 6) - 1].value or "")
            }
            devices.append(device)

        wb.close()
        print(f"✓ Successfully read {len(devices)} devices from {filename}")
    except FileNotFoundError:
        print(f"✗ Error: File {filename} not found")
        return []
    except Exception as e:
        print(f"✗ Error reading Excel file: {e}")
        return []
    return devices

def create_abp_device(dev_info):
    """Create an ABP device using gRPC and activate it"""
    try:
        # 1. Create gRPC channel and client
        channel = grpc.insecure_channel(GRPC_SERVER)
        client = api.DeviceServiceStub(channel)
        auth_token = [("authorization", f"Bearer {API_TOKEN}")]

        # 2. Create ABP device
        create_req = api.CreateDeviceRequest()
        create_req.device.dev_eui = dev_info["dev_eui"]
        create_req.device.name = dev_info["name"]
        create_req.device.description = dev_info["description"]
        create_req.device.application_id = APPLICATION_ID
        create_req.device.device_profile_id = DEVICE_PROFILE_ID

        # Important: Mark as ABP device (uncomment if needed)
        # create_req.device.is_abp = True
        
        client.Create(create_req, metadata=auth_token)
        print(f"✓ Device {dev_info['dev_eui']} created")

        # 3. Configure device keys
        keys_req = api.CreateDeviceKeysRequest()
        keys_req.device_keys.dev_eui = dev_info["dev_eui"]
        keys_req.device_keys.nwk_key = "00000000000000000000000000000000"
        keys_req.device_keys.app_key = "00000000000000000000000000000000"
        client.CreateKeys(keys_req, metadata=auth_token)
        print(f"✓ Device {dev_info['dev_eui']} keys configured")

        # 4. Activate ABP device
        activate_req = api.ActivateDeviceRequest()
        activate_req.device_activation.dev_eui = dev_info["dev_eui"]
        activate_req.device_activation.dev_addr = dev_info["dev_addr"]
        activate_req.device_activation.nwk_s_enc_key = dev_info["nwk_s_key"]
        activate_req.device_activation.s_nwk_s_int_key = dev_info["nwk_s_key"]
        activate_req.device_activation.f_nwk_s_int_key = dev_info["nwk_s_key"]
        activate_req.device_activation.app_s_key = dev_info["app_s_key"]
        activate_req.device_activation.f_cnt_up = 0
        
        client.Activate(activate_req, metadata=auth_token)
        print(f"✓ Device {dev_info['dev_eui']} ABP activated")
        print("-" * 40)

    except grpc.RpcError as e:
        print(f"✗ Error processing device {dev_info['dev_eui']}: {e.code()} - {e.details()}")
    except Exception as e:
        print(f"✗ Unexpected error: {e}")

if __name__ == "__main__":
    print("Starting batch configuration of ABP devices...")
    devices_to_add = read_devices_from_excel(EXCEL_FILE)
    if not devices_to_add:
        print("No devices to process, exiting.")
        exit(1)
    
    for device in devices_to_add:
        create_abp_device(device)
    print("Batch configuration completed.")