api.py

"""
API endpoints for Multicaster application
"""

from flask import Blueprint, request, jsonify, make_response
from sqlalchemy import or_, and_, func
from datetime import datetime
from models import (
    db, Branch, Switch, SwitchPort, Device, DeviceIP, 
    DeviceConnection, IPAllocation, UserRole, PortSpeed, DeviceType, PortGroup, BranchMulticastConfig, DeviceTemplate, SwitchPair
)

from auth import require_role
from services.ip_service import IPAllocationService
from services.port_service import PortAllocationService
import ipaddress
import csv
import io
import os
import subprocess
import tempfile
from datetime import datetime

api_bp = Blueprint('api', __name__)

# Branch Management Endpoints
@api_bp.route('/branches', methods=['GET'])
@require_role(UserRole.VIEWER)
def list_branches():
    """List all branches"""
    branches = Branch.query.all()
    return jsonify({
        'branches': [branch.to_dict() for branch in branches]
    })

@api_bp.route('/branches', methods=['POST'])
@require_role(UserRole.MANAGER)
def create_branch():
    """Create new branch"""
    data = request.get_json()
    
    if not data or not all(k in data for k in ['name', 'code']):
        return jsonify({'error': 'Name and code are required'}), 400
    
    # Check if branch code already exists
    if Branch.query.filter_by(code=data['code']).first():
        return jsonify({'error': 'Branch code already exists'}), 409
    
    # Create branch with only basic fields - IP ranges will be configured later
    branch = Branch(
        name=data['name'],
        code=data['code'],
        address=data.get('address', ''),
        vlan_range=data.get('vlan_range', ''),
        # Initialize empty range arrays
        unicast_control_ranges=[],
        unicast_media_main_ranges=[],
        multicast_media_main_video_ranges=[],
        multicast_media_main_audio1_ranges=[],
        multicast_media_main_audio2_ranges=[],
        multicast_media_main_audio3_ranges=[],
        multicast_media_main_audio4_ranges=[],
        multicast_media_main_data_ranges=[],
        unicast_media_backup_ranges=[],
        multicast_media_backup_video_ranges=[],
        multicast_media_backup_audio1_ranges=[],
        multicast_media_backup_audio2_ranges=[],
        multicast_media_backup_audio3_ranges=[],
        multicast_media_backup_audio4_ranges=[],
        multicast_media_backup_data_ranges=[]
    )
    
    db.session.add(branch)
    db.session.commit()
    
    return jsonify({
        'message': 'Branch created successfully',
        'branch': branch.to_dict()
    }), 201

@api_bp.route('/branches/<int:branch_id>', methods=['GET'])
@require_role(UserRole.VIEWER)
def get_branch(branch_id):
    """Get branch details"""
    branch = Branch.query.get_or_404(branch_id)
    
    # Get additional statistics
    branch_data = branch.to_dict()
    branch_data['switches'] = [switch.to_dict() for switch in branch.switches]
    branch_data['ip_stats'] = IPAllocationService.get_branch_ip_stats(branch_id)
    
    return jsonify({'branch': branch_data})

@api_bp.route('/branches/<int:branch_id>', methods=['PUT'])
@require_role(UserRole.MANAGER)
def update_branch(branch_id):
    """Update branch"""
    branch = Branch.query.get_or_404(branch_id)
    data = request.get_json()
    

    if 'name' in data:
        branch.name = data['name']
    
    if 'code' in data and data['code'] != branch.code:
        if Branch.query.filter_by(code=data['code']).first():
            return jsonify({'error': 'Branch code already exists'}), 409
        branch.code = data['code']
    
    if 'address' in data:
        branch.address = data['address']
    
    # Handle IP range updates
    # Control Network
    if 'unicast_control_ranges' in data:
        branch.unicast_control_ranges = data['unicast_control_ranges']
    
    # Media Main Network
    if 'unicast_media_main_ranges' in data:
        branch.unicast_media_main_ranges = data['unicast_media_main_ranges']
    if 'multicast_media_main_video_ranges' in data:
        branch.multicast_media_main_video_ranges = data['multicast_media_main_video_ranges']
    if 'multicast_media_main_audio1_ranges' in data:
        branch.multicast_media_main_audio1_ranges = data['multicast_media_main_audio1_ranges']
    if 'multicast_media_main_audio2_ranges' in data:
        branch.multicast_media_main_audio2_ranges = data['multicast_media_main_audio2_ranges']
    if 'multicast_media_main_audio3_ranges' in data:
        branch.multicast_media_main_audio3_ranges = data['multicast_media_main_audio3_ranges']
    if 'multicast_media_main_audio4_ranges' in data:
        branch.multicast_media_main_audio4_ranges = data['multicast_media_main_audio4_ranges']
    if 'multicast_media_main_data_ranges' in data:
        branch.multicast_media_main_data_ranges = data['multicast_media_main_data_ranges']
    
    # Media Backup Network
    if 'unicast_media_backup_ranges' in data:
        branch.unicast_media_backup_ranges = data['unicast_media_backup_ranges']
    if 'multicast_media_backup_video_ranges' in data:
        branch.multicast_media_backup_video_ranges = data['multicast_media_backup_video_ranges']
    if 'multicast_media_backup_audio1_ranges' in data:
        branch.multicast_media_backup_audio1_ranges = data['multicast_media_backup_audio1_ranges']
    if 'multicast_media_backup_audio2_ranges' in data:
        branch.multicast_media_backup_audio2_ranges = data['multicast_media_backup_audio2_ranges']
    if 'multicast_media_backup_audio3_ranges' in data:
        branch.multicast_media_backup_audio3_ranges = data['multicast_media_backup_audio3_ranges']
    if 'multicast_media_backup_audio4_ranges' in data:
        branch.multicast_media_backup_audio4_ranges = data['multicast_media_backup_audio4_ranges']
    if 'multicast_media_backup_data_ranges' in data:
        branch.multicast_media_backup_data_ranges = data['multicast_media_backup_data_ranges']
    
    if 'vlan_range' in data:
        branch.vlan_range = data['vlan_range']
    
    if 'use_subnet30_allocation' in data:
        branch.use_subnet30_allocation = data['use_subnet30_allocation']
    
    db.session.commit()
    
    return jsonify({
        'message': 'Branch updated successfully',
        'branch': branch.to_dict()
    })

@api_bp.route('/branches/<int:branch_id>', methods=['DELETE'])
@require_role(UserRole.ADMIN)
def delete_branch(branch_id):
    """Delete branch (admin only)"""
    branch = Branch.query.get_or_404(branch_id)
    
    db.session.delete(branch)
    db.session.commit()
    
    return jsonify({'message': 'Branch deleted successfully'})

# Switch Management Endpoints
@api_bp.route('/branches/<int:branch_id>/switches', methods=['GET'])
@require_role(UserRole.VIEWER)
def list_switches(branch_id):
    """List switches in a branch"""
    branch = Branch.query.get_or_404(branch_id)
    switches = Switch.query.filter_by(branch_id=branch_id).all()
    
    return jsonify({
        'switches': [switch.to_dict() for switch in switches],
        'branch': branch.to_dict()
    })

@api_bp.route('/switches', methods=['POST'])
@require_role(UserRole.OPERATOR)
def create_switch():
    """Create new switch"""
    data = request.get_json()
    
    if not data or not all(k in data for k in ['name', 'branch_id']):
        return jsonify({'error': 'Name and branch_id are required'}), 400
    
    # Verify branch exists
    branch = Branch.query.get_or_404(data['branch_id'])
    
    # Parse switch type
    switch_type_value = data.get('switch_type', 'media')
    try:
        from models import SwitchType
        switch_type = SwitchType(switch_type_value)
    except ValueError:
        return jsonify({'error': f'Invalid switch type: {switch_type_value}'}), 400
    
    switch = Switch(
        name=data['name'],
        model=data.get('model'),
        manufacturer=data.get('manufacturer'),
        ip_address=data.get('ip_address'),
        management_ip=data.get('management_ip'),
        location=data.get('location'),
        switch_type=switch_type,
        api_endpoint=data.get('api_endpoint'),
        is_managed=data.get('is_managed', False),
        branch_id=data['branch_id']
    )
    
    db.session.add(switch)
    db.session.commit()
    
    # Create default ports if specified
    try:
        port_count = int(data.get('port_count', 48))
        if port_count <= 0 or port_count > 512:  # Reasonable limits
            return jsonify({'error': 'Port count must be between 1 and 512'}), 400
    except (ValueError, TypeError):
        return jsonify({'error': 'Port count must be a valid number'}), 400
    
    port_speed = data.get('default_port_speed', '10G')
    
    try:
        PortAllocationService.create_default_ports(switch.id, port_count, PortSpeed(port_speed))
    except Exception as e:
        # If port creation fails, clean up the switch
        db.session.delete(switch)
        db.session.commit()
        return jsonify({'error': f'Failed to create switch ports: {str(e)}'}), 500
    
    return jsonify({
        'message': 'Switch created successfully',
        'switch': switch.to_dict()
    }), 201

@api_bp.route('/switches/<int:switch_id>', methods=['GET'])
@require_role(UserRole.VIEWER)
def get_switch(switch_id):
    """Get switch details with ports and devices"""
    switch = Switch.query.get_or_404(switch_id)
    
    switch_data = switch.to_dict()
    switch_data['ports'] = [port.to_dict() for port in switch.ports]
    switch_data['devices'] = [device.to_dict() for device in switch.devices]
    
    return jsonify({'switch': switch_data})

@api_bp.route('/switches/<int:switch_id>', methods=['PUT'])
@require_role(UserRole.OPERATOR)
def update_switch(switch_id):
    """Update switch"""
    switch = Switch.query.get_or_404(switch_id)
    data = request.get_json()
    
    updatable_fields = [
        'name', 'model', 'manufacturer', 'ip_address', 
        'management_ip', 'location', 'api_endpoint', 'is_managed'
    ]
    
    for field in updatable_fields:
        if field in data:
            setattr(switch, field, data[field])
    
    db.session.commit()
    
    return jsonify({
        'message': 'Switch updated successfully',
        'switch': switch.to_dict()
    })

@api_bp.route('/switches/<int:switch_id>', methods=['DELETE'])
@require_role(UserRole.MANAGER)
def delete_switch(switch_id):
    """Delete switch"""
    switch = Switch.query.get_or_404(switch_id)
    
    db.session.delete(switch)
    db.session.commit()
    
    return jsonify({'message': 'Switch deleted successfully'})

# Port Management Endpoints
@api_bp.route('/switches/<int:switch_id>/ports', methods=['GET'])
@require_role(UserRole.VIEWER)
def list_ports(switch_id):
    """List ports for a switch, including breakout sub-ports"""
    switch = Switch.query.get_or_404(switch_id)
    
    # Get all ports for this switch, including breakout sub-ports
    all_ports = SwitchPort.query.filter_by(switch_id=switch_id).order_by(SwitchPort.port_number).all()
    
    # Organize ports: main ports first, then their sub-ports
    organized_ports = []
    
    # First pass: get all main ports (no parent)
    main_ports = [p for p in all_ports if p.parent_port_id is None]
    
    for main_port in main_ports:
        organized_ports.append(main_port)
        
        # Add sub-ports for this main port if they exist
        sub_ports = [p for p in all_ports if p.parent_port_id == main_port.id]
        sub_ports.sort(key=lambda x: x.port_number)  # Sort sub-ports by number
        organized_ports.extend(sub_ports)
    
    return jsonify({
        'ports': [port.to_dict() for port in organized_ports],
        'switch': switch.to_dict(),
        'total_main_ports': len(main_ports),
        'total_ports': len(all_ports)
    })

# Port group endpoints
@api_bp.route('/switches/<int:switch_id>/port-groups', methods=['GET'])
@require_role(UserRole.VIEWER)
def list_port_groups(switch_id):
    """List all port groups for a switch"""
    switch = Switch.query.get_or_404(switch_id)
    port_groups = PortGroup.query.filter_by(switch_id=switch_id).order_by(PortGroup.group_number).all()
    
    return jsonify({
        'port_groups': [pg.to_dict() for pg in port_groups],
        'switch': switch.to_dict()
    })

@api_bp.route('/port-groups/<int:group_id>/mode', methods=['PUT'])
@require_role(UserRole.OPERATOR)
def change_port_group_mode(group_id):
    """Change port group mode between 100G and 25G"""
    data = request.get_json()
    
    if not data or 'mode' not in data:
        return jsonify({'error': 'Mode is required'}), 400
    
    try:
        new_mode = PortSpeed(data['mode'])
        if new_mode not in [PortSpeed.HUNDRED_G, PortSpeed.TWENTY_FIVE_G]:
            return jsonify({'error': 'Only 100G and 25G modes are supported'}), 400
    except ValueError:
        return jsonify({'error': 'Invalid mode specified'}), 400
    
    success, message = PortAllocationService.change_port_group_mode(group_id, new_mode)
    
    if not success:
        return jsonify({'error': message}), 400
    
    # Get updated port group
    port_group = PortGroup.query.get(group_id)
    
    return jsonify({
        'message': message,
        'port_group': port_group.to_dict() if port_group else None
    })

@api_bp.route('/ports/<int:port_id>/devices', methods=['GET'])
@require_role(UserRole.VIEWER)
def get_port_devices(port_id):
    """Get devices connected to a specific port"""
    port = SwitchPort.query.get_or_404(port_id)
    
    # Get device connections for this port
    connections = DeviceConnection.query.filter_by(port_id=port_id).all()
    devices = [conn.device.to_dict() for conn in connections]
    
    return jsonify({
        'port': port.to_dict(),
        'devices': devices
    })

@api_bp.route('/ports/<int:port_id>/breakout', methods=['POST'])
@require_role(UserRole.OPERATOR)
def breakout_port(port_id):
    """Toggle port breakout (100G <-> 25G) for the port's group"""
    port = SwitchPort.query.get_or_404(port_id)
    
    if not port.port_group:
        return jsonify({'error': 'Port is not part of a port group'}), 400
    
    # Only allow breakout on odd ports (1, 3, 5, 7, etc.)
    if port.port_number % 2 == 0:
        return jsonify({'error': 'Port breakout can only be performed on odd ports (1, 3, 5, 7, etc.)'}), 400
    
    # Determine the new mode (toggle between 100G and 25G)
    current_mode = port.port_group.current_mode
    new_mode = PortSpeed.TWENTY_FIVE_G if current_mode == PortSpeed.HUNDRED_G else PortSpeed.HUNDRED_G
    
    # Use the port allocation service to change the mode
    success, message = PortAllocationService.change_port_group_mode(port.port_group.id, new_mode)
    
    if not success:
        return jsonify({'error': message}), 400
    
    # Refresh the port and return updated information
    db.session.refresh(port)
    
    return jsonify({
        'message': message,
        'port': port.to_dict(),
        'port_group': port.port_group.to_dict()
    })

# Device Management Endpoints
@api_bp.route('/devices', methods=['GET'])
@require_role(UserRole.VIEWER)
def list_devices():
    """List all devices with optional filtering"""
    query = Device.query
    
    # Filter by branch
    branch_id = request.args.get('branch_id', type=int)
    if branch_id:
        query = query.join(Switch).filter(Switch.branch_id == branch_id)
    
    # Filter by switch
    switch_id = request.args.get('switch_id', type=int)
    if switch_id:
        query = query.filter(Device.switch_id == switch_id)
    
    # Filter by device type
    device_type = request.args.get('device_type')
    if device_type:
        query = query.filter(Device.device_type == DeviceType(device_type))
    
    # Search by name
    search = request.args.get('search')
    if search:
        query = query.filter(or_(
            Device.name.ilike(f'%{search}%'),
            Device.model.ilike(f'%{search}%'),
            Device.manufacturer.ilike(f'%{search}%')
        ))
    
    devices = query.all()
    
    return jsonify({
        'devices': [device.to_dict() for device in devices]
    })

@api_bp.route('/devices/batch', methods=['POST'])
@require_role(UserRole.OPERATOR)
def create_devices_batch():
    """Create multiple devices with batch naming and smart switch assignment"""
    data = request.get_json()
    
    # Extract batch parameters
    template_id = data.get('template_id')
    prefix = data.get('prefix', '')
    start_number = data.get('start_number', 1)
    count = data.get('count', 1)
    branch_id = data.get('branch_id')
    
    # Media switches (same for all devices)
    media_main_switch_id = data.get('media_main_switch_id')
    media_backup_switch_id = data.get('media_backup_switch_id')
    
    # Control switches (will shuffle between)
    control_switch_ids = data.get('control_switch_ids', [])
    
    if not template_id or not branch_id:
        return jsonify({'error': 'Template ID and Branch ID are required'}), 400
    
    if count > 50:  # Reasonable limit
        return jsonify({'error': 'Maximum 50 devices can be created at once'}), 400
    
    # Get template
    template = DeviceTemplate.query.get_or_404(template_id)
    
    # Get branch
    branch = Branch.query.get_or_404(branch_id)
    
    created_devices = []
    errors = []
    
    try:
        for i in range(count):
            device_number = start_number + i
            device_name = f"{prefix} {device_number}" if prefix else str(device_number)
            
            # For control switches, shuffle between available switches
            control_switch_id = None
            if control_switch_ids:
                control_switch_id = control_switch_ids[i % len(control_switch_ids)]
            
            # Determine device type based on template capabilities
            # If template has video/audio/data ports, it's likely a multicast device
            device_type = DeviceType.MULTICAST if (template.video_ports > 0 or template.audio_ports > 0 or template.data_ports > 0) else DeviceType.UNICAST
            
            # Determine device capabilities based on template
            supports_multicast = bool(template.video_ports > 0 or template.audio_ports > 0 or template.data_ports > 0)
            supports_unicast = True  # Most devices support unicast
            
            # Prepare device data to reuse single device creation logic
            device_data = {
                'name': device_name,
                'manufacturer': template.manufacturer,
                'model': template.model,
                'serial_number': '',
                'device_type': device_type,  # Use enum directly
                'template_id': template_id,  # Include template_id for multi-port allocation
                'control_switch_id': control_switch_id,
                'media_main_switch_id': media_main_switch_id,
                'media_backup_switch_id': media_backup_switch_id,
                'video_ports': template.video_ports,
                'audio_ports': template.audio_ports,
                'data_ports': template.data_ports,
                'supports_multicast': supports_multicast,
                'supports_unicast': supports_unicast,
                'required_port_speed': template.required_port_speed,
                'multicast_video_port': 50100,
                'multicast_audio1_port': 50104,
                'multicast_audio2_port': 50104,
                'multicast_audio3_port': 50104,
                'multicast_audio4_port': 50104,
                'multicast_data_port': 50102,
                'notes': template.description,
                'port_assignment_mode': 'auto',
                # Enable all required checkboxes for full allocation
                'needs_control': True,
                'needs_video': True,
                'needs_audio': True,
                'needs_data': True,
                'needs_control_unicast': True,
                'needs_media_main_unicast': True,
                'needs_media_backup_unicast': True,
                'needs_media_main_video': supports_multicast,
                'needs_media_main_audio1': supports_multicast,
                'needs_media_main_audio2': supports_multicast,
                'needs_media_main_audio3': supports_multicast,
                'needs_media_main_audio4': supports_multicast,
                'needs_media_main_data': supports_multicast,
                'needs_media_backup_video': supports_multicast,
                'needs_media_backup_audio1': supports_multicast,
                'needs_media_backup_audio2': supports_multicast,
                'needs_media_backup_audio3': supports_multicast,
                'needs_media_backup_audio4': supports_multicast,
                'needs_media_backup_data': supports_multicast,
                'unicast_count': 1,
                'required_connections': ['data'],
                'control_port_id': '',
                'media_main_port_id': '',
                'media_backup_port_id': ''
            }
            
            try:
                # Create device (exclude fields that aren't direct Device model fields)
                device = Device(**{k: v for k, v in device_data.items() if k not in [
                    'control_switch_id', 'media_main_switch_id', 'media_backup_switch_id', 'port_assignment_mode',
                    'needs_control', 'needs_video', 'needs_audio', 'needs_data', 'needs_control_unicast',
                    'needs_media_main_unicast', 'needs_media_backup_unicast', 'needs_media_main_video',
                    'needs_media_main_audio1', 'needs_media_main_audio2', 'needs_media_main_audio3',
                    'needs_media_main_audio4', 'needs_media_main_data', 'needs_media_backup_video',
                    'needs_media_backup_audio1', 'needs_media_backup_audio2', 'needs_media_backup_audio3',
                    'needs_media_backup_audio4', 'needs_media_backup_data', 'unicast_count',
                    'required_connections', 'control_port_id', 'media_main_port_id', 'media_backup_port_id'
                ]})
                
                # Set switch IDs for 3-port device structure
                device.control_switch_id = control_switch_id
                device.media_main_switch_id = media_main_switch_id
                device.media_backup_switch_id = media_backup_switch_id
                device.template_id = template_id  # Ensure template_id is set
                
                db.session.add(device)
                db.session.flush()  # Get device ID
                
                # Allocate ports using the new multi-port service
                port_result = PortAllocationService.allocate_multi_port_device_ports(
                    control_switch_id=control_switch_id,
                    media_main_switch_id=media_main_switch_id,
                    media_backup_switch_id=media_backup_switch_id,
                    control_ports_count=template.control_ports_count,
                    media_main_ports_count=template.media_main_ports_count,
                    media_backup_ports_count=template.media_backup_ports_count,
                    control_port_speed=template.control_port_speed,
                    media_main_port_speed=template.media_main_port_speed,
                    media_backup_port_speed=template.media_backup_port_speed,
                    port_assignment_mode='auto'
                )
                
                if port_result['success']:
                    # Create port connections for multiple ports per interface
                    # Control ports
                    for i, control_port in enumerate(port_result.get('control_ports', [])):
                        connection = DeviceConnection(
                            device_id=device.id,
                            port_id=control_port.id,
                            connection_type=f'control{f"_{i+1}" if i > 0 else ""}'  # control, control_2, control_3, etc.
                        )
                        db.session.add(connection)
                    
                    # Media main ports
                    for i, media_main_port in enumerate(port_result.get('media_main_ports', [])):
                        connection = DeviceConnection(
                            device_id=device.id,
                            port_id=media_main_port.id,
                            connection_type=f'media_main{f"_{i+1}" if i > 0 else ""}'  # media_main, media_main_2, etc.
                        )
                        db.session.add(connection)
                    
                    # Media backup ports
                    for i, media_backup_port in enumerate(port_result.get('media_backup_ports', [])):
                        connection = DeviceConnection(
                            device_id=device.id,
                            port_id=media_backup_port.id,
                            connection_type=f'media_backup{f"_{i+1}" if i > 0 else ""}'  # media_backup, media_backup_2, etc.
                        )
                        db.session.add(connection)
                
                # Allocate IPs
                has_control = bool(control_switch_id)
                has_media_main = bool(media_main_switch_id)
                has_media_backup = bool(media_backup_switch_id)
                
                # Use multi-port IP allocation to match the single device creation behavior
                allocated_ip_groups = IPAllocationService.allocate_multi_port_device_ips(
                    branch_id=branch_id,
                    device_name=device.name,
                    control_ports_count=template.control_ports_count,
                    media_main_ports_count=template.media_main_ports_count,
                    media_backup_ports_count=template.media_backup_ports_count,
                    video_ports=device.video_ports if device.supports_multicast else 0,
                    audio_ports=device.audio_ports if device.supports_multicast else 0,
                    data_ports=device.data_ports if device.supports_multicast else 0
                )
                
                # Create DeviceIP records
                for ip_type, ips in allocated_ip_groups.items():
                    for ip_addr in ips:
                        if 'control' in ip_type:
                            description = 'Control Network IP'
                        elif 'media_main' in ip_type:
                            if 'unicast' in ip_type:
                                description = 'Media Main Network IP'
                            else:
                                ip_service = ip_type.replace('media_main_', '').replace('_', ' ').title()
                                description = f'Media Main {ip_service} Multicast'
                        elif 'media_backup' in ip_type:
                            if 'unicast' in ip_type:
                                description = 'Media Backup Network IP'
                            else:
                                ip_service = ip_type.replace('media_backup_', '').replace('_', ' ').title()
                                description = f'Media Backup {ip_service} Multicast'
                        else:
                            description = f'{ip_type.replace("_", " ").title()} IP'
                        
                        device_ip = DeviceIP(
                            ip_address=ip_addr,
                            ip_type='unicast' if 'unicast' in ip_type else 'multicast',
                            device_id=device.id,
                            is_primary=('control' in ip_type),
                            description=description
                        )
                        db.session.add(device_ip)
                
                created_devices.append({
                    'name': device.name,
                    'id': device.id,
                    'status': 'created'
                })
                
            except Exception as e:
                errors.append(f"Device '{device_name}': {str(e)}")
                db.session.rollback()
                # Continue with next device
        
        if created_devices:
            db.session.commit()
        
        return jsonify({
            'message': f'Batch creation completed. {len(created_devices)} devices created, {len(errors)} errors.',
            'created_devices': created_devices,
            'errors': errors,
            'total_requested': count,
            'total_created': len(created_devices)
        })
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'Batch creation failed: {str(e)}'}), 500

@api_bp.route('/devices', methods=['POST'])
@require_role(UserRole.OPERATOR)
def create_device():
    """Create new device with automatic IP and port allocation"""
    data = request.get_json()
    print(f"DEBUG API: Full request data: {data}")
    
    # For 3-port devices, we need either legacy switch_id OR the new 3-port switches
    required_fields = ['name', 'device_type']
    if not data or not all(k in data for k in required_fields):
        return jsonify({'error': 'Name and device_type are required'}), 400
    
    # Check 3-port device configuration - at least one switch must be specified
    has_3port_switches = data.get('control_switch_id') or data.get('media_main_switch_id') or data.get('media_backup_switch_id')
    
    if not has_3port_switches:
        return jsonify({'error': 'At least one switch must be specified (control, media main, or media backup)'}), 400
    
    # Get switch to determine branch (all switches should be in same branch)
    switch_id = data.get('control_switch_id') or data.get('media_main_switch_id') or data.get('media_backup_switch_id')
    switch = Switch.query.get_or_404(switch_id)
    branch_id = switch.branch_id
    
    # Create device
    device = Device(
        name=data['name'],
        model=data.get('model'),
        manufacturer=data.get('manufacturer'),
        device_type=DeviceType(data['device_type']),
        serial_number=data.get('serial_number'),
        video_ports=data.get('video_ports', 0),
        audio_ports=data.get('audio_ports', 0),
        data_ports=data.get('data_ports', 1),
        supports_multicast=data.get('supports_multicast', False),
        supports_unicast=data.get('supports_unicast', True),
        required_port_speed=data.get('required_port_speed', '10G'),
        template_id=data.get('template_id'),  # Store template reference
        notes=data.get('notes'),
        # Legacy switch (set to one of the 3-port switches for backward compatibility)
        switch_id=switch.id,
        # New 3-port device switches
        control_switch_id=data.get('control_switch_id'),
        media_main_switch_id=data.get('media_main_switch_id'),
        media_backup_switch_id=data.get('media_backup_switch_id')
    )
    
    db.session.add(device)
    db.session.flush()  # Get device ID
    
    # Check port compatibility and suggest actions if needed
    required_port_speed = device.required_port_speed
    port_warnings = []
    port_actions_needed = []
    
    # Check each switch that the device needs to connect to
    switches_to_check = []
    if device.control_switch_id:
        switches_to_check.append(('control', device.control_switch_id))
    if device.media_main_switch_id:
        switches_to_check.append(('media_main', device.media_main_switch_id))
    if device.media_backup_switch_id:
        switches_to_check.append(('media_backup', device.media_backup_switch_id))
    
    for connection_type, switch_id in switches_to_check:
        from services.port_service import PortAllocationService
        port_result = PortAllocationService.find_compatible_port(switch_id, required_port_speed, connection_type)
        
        if port_result['status'] == 'wrong_switch_type':
            # Wrong switch type - control/media segregation violation
            return jsonify({
                'error': 'Invalid switch type',
                'message': f'Switch type mismatch for {connection_type} connection.',
                'warnings': port_result['warnings'],
                'alternatives': [],
                'requires_confirmation': False,
                'action_type': 'switch_type_error'
            }), 400
            
        elif port_result['status'] == 'needs_breakout':
            # Serialize alternatives to make them JSON-safe
            serialized_alternatives = []
            for alt in port_result['alternatives']:
                serialized_alt = {
                    'action': alt['action'],
                    'description': alt['description']
                }
                # Convert PortGroup to dict if present
                if 'group' in alt and alt['group']:
                    serialized_alt['group'] = {
                        'id': alt['group'].id,
                        'group_number': alt['group'].group_number,
                        'start_port': alt['group'].start_port,
                        'end_port': alt['group'].end_port,
                        'current_mode': alt['group'].current_mode.value,
                        'is_broken_out': alt['group'].is_broken_out
                    }
                # Convert SwitchPort to dict if present
                if 'port' in alt and alt['port']:
                    serialized_alt['port'] = alt['port'].to_dict()
                
                serialized_alternatives.append(serialized_alt)
            
            # Require user confirmation for breakouts
            return jsonify({
                'error': 'Port breakout required',
                'message': f'No {required_port_speed} ports available on {connection_type} switch. Breakout required.',
                'warnings': port_result['warnings'],
                'alternatives': serialized_alternatives,
                'requires_confirmation': True,
                'action_type': 'breakout'
            }), 400
            
        elif port_result['status'] == 'needs_change':
            # Serialize alternatives to make them JSON-safe
            serialized_alternatives = []
            for alt in port_result['alternatives']:
                serialized_alt = {
                    'action': alt['action'],
                    'description': alt['description']
                }
                # Convert SwitchPort to dict if present
                if 'port' in alt and alt['port']:
                    serialized_alt['port'] = alt['port'].to_dict()
                
                serialized_alternatives.append(serialized_alt)
            
            # Require user confirmation for port reassignments
            return jsonify({
                'error': 'Port reassignment required', 
                'message': f'No available {required_port_speed} ports on {connection_type} switch. Port reassignment required.',
                'warnings': port_result['warnings'],
                'alternatives': serialized_alternatives,
                'requires_confirmation': True,
                'action_type': 'reassignment'
            }), 400
            
        elif port_result['status'] == 'not_available':
            # No compatible ports available at all
            return jsonify({
                'error': 'No compatible ports available',
                'message': f'No {required_port_speed} ports available on {connection_type} switch and no alternatives found.',
                'switch_id': switch_id,
                'connection_type': connection_type
            }), 400
            
        elif port_result['status'] == 'found':
            # Port found - add any warnings to response
            if port_result['warnings']:
                port_warnings.extend([f"{connection_type}: {w}" for w in port_result['warnings']])
    
    # Automatic IP allocation based on device ports and network configuration
    # Determine which networks this device will use
    has_control = bool(device.control_switch_id)
    has_media_main = bool(device.media_main_switch_id)
    has_media_backup = bool(device.media_backup_switch_id)
    
    # Simplified IP allocation - automatically expand to detailed requirements
    needs_control = data.get('needs_control', False)
    needs_video = data.get('needs_video', False)
    needs_audio = data.get('needs_audio', False)
    needs_data = data.get('needs_data', False)
    
    # Expand simplified requirements to detailed network-specific requirements
    needs_control_unicast = needs_control
    
    # Media Main network requirements (always include unicast if any media service is needed)
    has_media_requirements = needs_video or needs_audio or needs_data
    needs_media_main_unicast = has_media_requirements
    needs_media_main_video = needs_video
    needs_media_main_audio1 = needs_audio  # All 4 audio channels if audio is selected
    needs_media_main_audio2 = needs_audio
    needs_media_main_audio3 = needs_audio
    needs_media_main_audio4 = needs_audio
    needs_media_main_data = needs_data
    
    # Media Backup network requirements (mirror of main)
    needs_media_backup_unicast = has_media_requirements
    needs_media_backup_video = needs_video
    needs_media_backup_audio1 = needs_audio
    needs_media_backup_audio2 = needs_audio
    needs_media_backup_audio3 = needs_audio
    needs_media_backup_audio4 = needs_audio
    needs_media_backup_data = needs_data
    
    # Override has_control, has_media_main, has_media_backup based on actual IP needs
    has_control = bool(device.control_switch_id) and needs_control_unicast
    has_media_main = bool(device.media_main_switch_id) and (
        needs_media_main_unicast or needs_media_main_video or 
        needs_media_main_audio1 or needs_media_main_audio2 or needs_media_main_audio3 or needs_media_main_audio4 or 
        needs_media_main_data
    )
    has_media_backup = bool(device.media_backup_switch_id) and (
        needs_media_backup_unicast or needs_media_backup_video or 
        needs_media_backup_audio1 or needs_media_backup_audio2 or needs_media_backup_audio3 or needs_media_backup_audio4 or 
        needs_media_backup_data
    )
    
    # Use actual device port counts (only allocate if the service is enabled via checkboxes)
    video_ports = device.video_ports if (needs_media_main_video or needs_media_backup_video) else 0
    data_ports = device.data_ports if (needs_media_main_data or needs_media_backup_data) else 0
    
    # Use actual device audio port count (only allocate if audio service is enabled via checkboxes)
    audio_ports = device.audio_ports if (needs_media_main_audio1 or needs_media_main_audio2 or needs_media_main_audio3 or needs_media_main_audio4 or 
                                        needs_media_backup_audio1 or needs_media_backup_audio2 or needs_media_backup_audio3 or needs_media_backup_audio4) else 0
    
    # Check if this is template-based multi-port allocation
    template_id = data.get('template_id')
    if template_id:
        template = DeviceTemplate.query.get(template_id)
        if template:
            # Use multi-port IP allocation based on template
            allocated_ip_groups = IPAllocationService.allocate_multi_port_device_ips(
                branch_id=branch_id,
                device_name=device.name,
                control_ports_count=template.control_ports_count,
                media_main_ports_count=template.media_main_ports_count,
                media_backup_ports_count=template.media_backup_ports_count,
                video_ports=video_ports,
                audio_ports=audio_ports,
                data_ports=data_ports
            )
        else:
            # Fallback to single-port allocation
            allocated_ip_groups = IPAllocationService.allocate_3port_device_ips(
                branch_id=branch_id,
                device_name=device.name,
                has_control=has_control,
                has_media_main=has_media_main,
                has_media_backup=has_media_backup,
                video_ports=video_ports,
                audio_ports=audio_ports,
                data_ports=data_ports
            )
    else:
        # Standard single-port allocation for non-template devices
        allocated_ip_groups = IPAllocationService.allocate_3port_device_ips(
            branch_id=branch_id,
            device_name=device.name,
            has_control=has_control,
            has_media_main=has_media_main,
            has_media_backup=has_media_backup,
            video_ports=video_ports,
            audio_ports=audio_ports,
            data_ports=data_ports
        )
    
    # Create DeviceIP records for all allocated IPs
    all_allocated_ips = []
    
    # Control Network Unicast IPs (no multicast on control network)
    for ip_addr in allocated_ip_groups['control_unicast']:
        device_ip = DeviceIP(
            ip_address=ip_addr,
            ip_type='unicast',
            device_id=device.id,
            is_primary=True,  # Control IP is primary
            description='Control Network IP'
        )
        db.session.add(device_ip)
        all_allocated_ips.append(ip_addr)
    
    # Media Main Network IPs
    for ip_addr in allocated_ip_groups['media_main_unicast']:
        device_ip = DeviceIP(
            ip_address=ip_addr,
            ip_type='unicast',
            device_id=device.id,
            is_primary=False,
            description='Media Main Network IP'
        )
        db.session.add(device_ip)
        all_allocated_ips.append(ip_addr)
    
    # Get or create branch multicast configuration
    branch_config = BranchMulticastConfig.query.filter_by(branch_id=branch_id).first()
    if not branch_config:
        branch_config = BranchMulticastConfig(branch_id=branch_id)
        db.session.add(branch_config)
        db.session.flush()  # Get the ID
    
    # Media Main Multicast IPs
    for i, ip_addr in enumerate(allocated_ip_groups['media_main_multicast_video']):
        device_ip = DeviceIP(
            ip_address=ip_addr,
            ip_type='multicast',
            device_id=device.id,
            is_primary=False,
            description=f'Media Main Video Port {i+1}',
            port=branch_config.multicast_video_port  # Use branch's configured video port
        )
        db.session.add(device_ip)
        all_allocated_ips.append(ip_addr)
    
    # Process audio multicast IPs - they are now organized by channel
    audio_ips = allocated_ip_groups['media_main_multicast_audio']
    audio_ports_count = device.audio_ports
    
    if audio_ports_count > 0 and audio_ips:
        for i, ip_addr in enumerate(audio_ips):
            # Calculate which channel and port within that channel
            # IPs are allocated as: Ch1_Port1, Ch1_Port2, ..., Ch1_PortN, Ch2_Port1, Ch2_Port2, ...
            audio_channel = (i // audio_ports_count) + 1
            port_within_channel = (i % audio_ports_count) + 1
            
            audio_port_attr = f'multicast_audio{audio_channel}_port'
            audio_port = getattr(branch_config, audio_port_attr)
            
            device_ip = DeviceIP(
                ip_address=ip_addr,
                ip_type='multicast',
                device_id=device.id,
                is_primary=False,
                description=f'Media Main Audio Ch{audio_channel} Port {port_within_channel}',
                port=audio_port  # Use branch's configured audio channel port
            )
            db.session.add(device_ip)
            all_allocated_ips.append(ip_addr)
    
    for i, ip_addr in enumerate(allocated_ip_groups['media_main_multicast_data']):
        device_ip = DeviceIP(
            ip_address=ip_addr,
            ip_type='multicast',
            device_id=device.id,
            is_primary=False,
            description=f'Media Main Data Port {i+1}',
            port=branch_config.multicast_data_port  # Use branch's configured data port
        )
        db.session.add(device_ip)
        all_allocated_ips.append(ip_addr)
    
    # Media Backup Network IPs
    for ip_addr in allocated_ip_groups['media_backup_unicast']:
        device_ip = DeviceIP(
            ip_address=ip_addr,
            ip_type='unicast',
            device_id=device.id,
            is_primary=False,
            description='Media Backup Network IP'
        )
        db.session.add(device_ip)
        all_allocated_ips.append(ip_addr)
    
    # Media Backup Multicast IPs
    for i, ip_addr in enumerate(allocated_ip_groups['media_backup_multicast_video']):
        device_ip = DeviceIP(
            ip_address=ip_addr,
            ip_type='multicast',
            device_id=device.id,
            is_primary=False,
            description=f'Media Backup Video Port {i+1}',
            port=branch_config.multicast_video_port  # Use branch's configured video port (same as main)
        )
        db.session.add(device_ip)
        all_allocated_ips.append(ip_addr)
    
    # Process backup audio multicast IPs - they are now organized by channel
    backup_audio_ips = allocated_ip_groups['media_backup_multicast_audio']
    
    if audio_ports_count > 0 and backup_audio_ips:
        for i, ip_addr in enumerate(backup_audio_ips):
            # Calculate which channel and port within that channel
            # IPs are allocated as: Ch1_Port1, Ch1_Port2, ..., Ch1_PortN, Ch2_Port1, Ch2_Port2, ...
            audio_channel = (i // audio_ports_count) + 1
            port_within_channel = (i % audio_ports_count) + 1
            
            audio_port_attr = f'multicast_audio{audio_channel}_port'
            audio_port = getattr(branch_config, audio_port_attr)
            
            device_ip = DeviceIP(
                ip_address=ip_addr,
                ip_type='multicast',
                device_id=device.id,
                is_primary=False,
                description=f'Media Backup Audio Ch{audio_channel} Port {port_within_channel}',
                port=audio_port  # Use branch's configured audio channel port (same as main)
            )
            db.session.add(device_ip)
            all_allocated_ips.append(ip_addr)
    
    for i, ip_addr in enumerate(allocated_ip_groups['media_backup_multicast_data']):
        device_ip = DeviceIP(
            ip_address=ip_addr,
            ip_type='multicast',
            device_id=device.id,
            is_primary=False,
            description=f'Media Backup Data Port {i+1}',
            port=branch_config.multicast_data_port  # Use branch's configured data port (same as main)
        )
        db.session.add(device_ip)
        all_allocated_ips.append(ip_addr)
    
    # 3-Port device port allocation
    port_assignment_mode = data.get('port_assignment_mode', 'auto')
    
    # Check if template was already processed for multi-port allocation
    template_id = data.get('template_id')
    print(f"DEBUG: Template ID in request: {template_id}")
    if template_id:
        # Use template configuration for multi-port allocation  
        template = DeviceTemplate.query.get(template_id)
        print(f"DEBUG: Template found: {template.name if template else None}")
        if template:
            print(f"DEBUG: Using multi-port allocation with template: {template.name}")
            port_allocation_result = PortAllocationService.allocate_multi_port_device_ports(
                control_switch_id=device.control_switch_id,
                media_main_switch_id=device.media_main_switch_id,
                media_backup_switch_id=device.media_backup_switch_id,
                control_ports_count=template.control_ports_count,
                media_main_ports_count=template.media_main_ports_count,
                media_backup_ports_count=template.media_backup_ports_count,
                control_port_speed=template.control_port_speed,
                media_main_port_speed=template.media_main_port_speed,
                media_backup_port_speed=template.media_backup_port_speed,
                port_assignment_mode=port_assignment_mode
            )
        else:
            print("DEBUG: Template not found, using single-port allocation")
            # Template not found, fall back to single port allocation
            port_allocation_result = PortAllocationService.allocate_3port_device_ports(
                control_switch_id=device.control_switch_id,
                media_main_switch_id=device.media_main_switch_id,
                media_backup_switch_id=device.media_backup_switch_id,
                port_assignment_mode=port_assignment_mode,
                required_port_speed=required_port_speed
            )
    else:
        print("DEBUG: No template provided, using single-port allocation")
        # Manual device creation without template - fall back to single port allocation
        port_allocation_result = PortAllocationService.allocate_3port_device_ports(
            control_switch_id=device.control_switch_id,
            media_main_switch_id=device.media_main_switch_id,
            media_backup_switch_id=device.media_backup_switch_id,
            port_assignment_mode=port_assignment_mode,
            required_port_speed=required_port_speed
        )
    
    # Create DeviceConnection records for allocated ports
    # Handle both single-port (legacy) and multi-port allocation results
    allocated_ports = []
    
    # Check if we have multi-port results (lists) or single-port results (individual ports)
    print(f"DEBUG: Port allocation result keys: {list(port_allocation_result.keys())}")
    print(f"DEBUG: Has control_ports: {'control_ports' in port_allocation_result}")
    if 'control_ports' in port_allocation_result:
        # Multi-port allocation result
        print(f"DEBUG: Multi-port result detected")
        print(f"DEBUG: control_ports count: {len(port_allocation_result.get('control_ports', []))}")
        print(f"DEBUG: media_main_ports count: {len(port_allocation_result.get('media_main_ports', []))}")
        print(f"DEBUG: media_backup_ports count: {len(port_allocation_result.get('media_backup_ports', []))}")
        
        # Control ports
        for i, control_port in enumerate(port_allocation_result.get('control_ports', [])):
            print(f"DEBUG: Creating control connection {i+1} for port {control_port.id}")
            connection = DeviceConnection(
                device_id=device.id,
                port_id=control_port.id,
                connection_type=f'control{f"_{i+1}" if i > 0 else ""}'  # control, control_2, control_3, etc.
            )
            db.session.add(connection)
            allocated_ports.append(control_port)
        
        # Media main ports
        for i, media_main_port in enumerate(port_allocation_result.get('media_main_ports', [])):
            print(f"DEBUG: Creating media_main connection {i+1} for port {media_main_port.id}")
            connection = DeviceConnection(
                device_id=device.id,
                port_id=media_main_port.id,
                connection_type=f'media_main{f"_{i+1}" if i > 0 else ""}'  # media_main, media_main_2, etc.
            )
            db.session.add(connection)
            allocated_ports.append(media_main_port)
        
        # Media backup ports
        for i, media_backup_port in enumerate(port_allocation_result.get('media_backup_ports', [])):
            print(f"DEBUG: Creating media_backup connection {i+1} for port {media_backup_port.id}")
            connection = DeviceConnection(
                device_id=device.id,
                port_id=media_backup_port.id,
                connection_type=f'media_backup{f"_{i+1}" if i > 0 else ""}'  # media_backup, media_backup_2, etc.
            )
            db.session.add(connection)
            allocated_ports.append(media_backup_port)
    else:
        # Single-port allocation result (legacy format)
        if port_allocation_result.get('control_port'):
            connection = DeviceConnection(
                device_id=device.id,
                port_id=port_allocation_result['control_port'].id,
                connection_type='control'
            )
            db.session.add(connection)
            allocated_ports.append(port_allocation_result['control_port'])
        
        if port_allocation_result.get('media_main_port'):
            connection = DeviceConnection(
                device_id=device.id,
                port_id=port_allocation_result['media_main_port'].id,
                connection_type='media_main'
            )
            db.session.add(connection)
            allocated_ports.append(port_allocation_result['media_main_port'])
        
        if port_allocation_result.get('media_backup_port'):
            connection = DeviceConnection(
                device_id=device.id,
                port_id=port_allocation_result['media_backup_port'].id,
                connection_type='media_backup'
            )
            db.session.add(connection)
            allocated_ports.append(port_allocation_result['media_backup_port'])
    
    db.session.commit()
    
    device_data = device.to_dict()
    device_data['allocated_ips'] = all_allocated_ips
    device_data['allocated_ip_groups'] = allocated_ip_groups
    device_data['allocated_ports'] = [port.to_dict() for port in allocated_ports]
    
    # Handle both single-port and multi-port allocation results
    port_result = {
        'success': port_allocation_result['success'],
        'message': port_allocation_result['message']
    }
    
    # Multi-port result format (lists)
    if 'control_ports' in port_allocation_result:
        port_result['control_ports'] = [p.to_dict() for p in port_allocation_result['control_ports']]
        port_result['media_main_ports'] = [p.to_dict() for p in port_allocation_result['media_main_ports']]
        port_result['media_backup_ports'] = [p.to_dict() for p in port_allocation_result['media_backup_ports']]
        # Also provide single-port format for backward compatibility (first port in each list)
        port_result['control_port'] = port_allocation_result['control_ports'][0].to_dict() if port_allocation_result['control_ports'] else None
        port_result['media_main_port'] = port_allocation_result['media_main_ports'][0].to_dict() if port_allocation_result['media_main_ports'] else None
        port_result['media_backup_port'] = port_allocation_result['media_backup_ports'][0].to_dict() if port_allocation_result['media_backup_ports'] else None
    # Single-port result format
    else:
        port_result['control_port'] = port_allocation_result['control_port'].to_dict() if port_allocation_result.get('control_port') else None
        port_result['media_main_port'] = port_allocation_result['media_main_port'].to_dict() if port_allocation_result.get('media_main_port') else None
        port_result['media_backup_port'] = port_allocation_result['media_backup_port'].to_dict() if port_allocation_result.get('media_backup_port') else None
    
    device_data['port_allocation_result'] = port_result
    
    response_data = {
        'message': 'Device created successfully',
        'device': device_data
    }
    
    # Add port warnings if any
    if port_warnings:
        response_data['port_warnings'] = port_warnings
        response_data['message'] += ' (with port compatibility notes)'
    
    return jsonify(response_data), 201

@api_bp.route('/devices/<int:device_id>', methods=['GET'])
@require_role(UserRole.VIEWER)
def get_device(device_id):
    """Get device details"""
    device = Device.query.get_or_404(device_id)
    return jsonify({'device': device.to_dict()})

@api_bp.route('/devices/<int:device_id>', methods=['PUT'])
@require_role(UserRole.OPERATOR)
def update_device(device_id):
    """Update device"""
    device = Device.query.get_or_404(device_id)
    data = request.get_json()
    
    updatable_fields = [
        'name', 'model', 'manufacturer', 'serial_number',
        'video_ports', 'audio_ports', 'data_ports',
        'supports_multicast', 'supports_unicast', 'required_port_speed', 'notes',
        # Legacy switch
        'switch_id',
        # New 3-port device switches
        'control_switch_id', 'media_main_switch_id', 'media_backup_switch_id'
    ]
    
    for field in updatable_fields:
        if field in data:
            setattr(device, field, data[field])
    
    db.session.commit()
    
    return jsonify({
        'message': 'Device updated successfully',
        'device': device.to_dict()
    })

@api_bp.route('/devices/<int:device_id>/unicast-ips', methods=['PUT'])
@require_role(UserRole.OPERATOR)
def update_device_unicast_ips(device_id):
    """Update device unicast IP addresses"""
    device = Device.query.get_or_404(device_id)
    data = request.get_json()
    
    # Get current unicast IPs for this device
    current_unicast_ips = DeviceIP.query.filter_by(
        device_id=device_id,
        ip_type='unicast'
    ).all()
    
    # Create a mapping of current IPs by description type
    current_ips_map = {}
    for ip in current_unicast_ips:
        if ip.description:
            desc = ip.description.lower()
            if 'control' in desc:
                current_ips_map['control'] = ip
            elif 'media main' in desc:
                current_ips_map['media_main'] = ip
            elif 'media backup' in desc:
                current_ips_map['media_backup'] = ip
    
    # Update or create unicast IPs
    ip_mappings = {
        'control': 'Control Network IP',
        'media_main': 'Media Main Network IP',
        'media_backup': 'Media Backup Network IP'
    }
    
    for ip_type, description in ip_mappings.items():
        new_ip = data.get(ip_type, '').strip()
        
        if new_ip:
            # IP address provided
            if ip_type in current_ips_map:
                # Update existing IP
                current_ips_map[ip_type].ip_address = new_ip
            else:
                # Create new IP
                new_device_ip = DeviceIP(
                    device_id=device_id,
                    ip_address=new_ip,
                    ip_type='unicast',
                    description=description
                )
                db.session.add(new_device_ip)
        else:
            # No IP address provided, remove existing if any
            if ip_type in current_ips_map:
                db.session.delete(current_ips_map[ip_type])
    
    db.session.commit()
    
    return jsonify({
        'message': 'Unicast IPs updated successfully',
        'device': device.to_dict()
    })

@api_bp.route('/devices/<int:device_id>', methods=['DELETE'])
@require_role(UserRole.OPERATOR)
def delete_device(device_id):
    """Delete device and deallocate all associated IPs"""
    device = Device.query.get_or_404(device_id)
    
    # Get all device IPs before deleting them
    device_ips = DeviceIP.query.filter_by(device_id=device_id).all()
    
    print(f"DEBUG: Deleting device {device.name} (ID: {device_id})")
    print(f"DEBUG: Found {len(device_ips)} device IPs")
    
    # Deallocate all IP addresses associated with this device
    # Method 1: Traditional DeviceIP-linked allocations
    for device_ip in device_ips:
        # Find corresponding IP allocation records
        ip_allocations = IPAllocation.query.filter_by(device_ip_id=device_ip.id).all()
        print(f"DEBUG: Found {len(ip_allocations)} IP allocations linked to DeviceIP {device_ip.id}")
        
        for ip_allocation in ip_allocations:
            print(f"DEBUG: Deallocating linked IP {ip_allocation.ip_address} from {ip_allocation.allocated_to}")
            # Reset allocation status
            ip_allocation.is_allocated = False
            ip_allocation.allocated_to = None
            ip_allocation.allocated_at = None
            ip_allocation.device_ip_id = None
            ip_allocation.notes = None
    
    # Method 2: Multi-port device IP allocations by name pattern
    # These might not be linked to DeviceIP records but allocated by device name
    name_pattern_allocations = IPAllocation.query.filter(
        IPAllocation.allocated_to.like(f"{device.name}%"),
        IPAllocation.is_allocated == True
    ).all()
    
    print(f"DEBUG: Found {len(name_pattern_allocations)} IP allocations by device name pattern '{device.name}%'")
    
    for ip_allocation in name_pattern_allocations:
        print(f"DEBUG: Deallocating pattern IP {ip_allocation.ip_address} from {ip_allocation.allocated_to}")
        # Reset allocation status
        ip_allocation.is_allocated = False
        ip_allocation.allocated_to = None
        ip_allocation.allocated_at = None
        ip_allocation.device_ip_id = None
        ip_allocation.notes = None
    
    # Free up ports by setting them as available
    connections = DeviceConnection.query.filter_by(device_id=device_id).all()
    print(f"DEBUG: Found {len(connections)} device connections to free")
    
    for connection in connections:
        if connection.port:
            print(f"DEBUG: Freeing port {connection.port.port_number} on switch {connection.port.switch_id}")
            connection.port.is_available = True
    
    # Delete device connections
    deleted_connections = DeviceConnection.query.filter_by(device_id=device_id).delete()
    print(f"DEBUG: Attempted to delete {len(connections)} connections, actually deleted {deleted_connections}")
    
    # Delete device IPs
    deleted_device_ips = DeviceIP.query.filter_by(device_id=device_id).delete()
    print(f"DEBUG: Deleted {deleted_device_ips} DeviceIP records")
    
    # Delete the device itself
    db.session.delete(device)
    db.session.commit()
    
    print(f"DEBUG: Device {device.name} deleted successfully")
    return jsonify({'message': 'Device deleted successfully and IPs deallocated'})

@api_bp.route('/devices/<int:device_id>/ports', methods=['PUT'])
@require_role(UserRole.OPERATOR)
def assign_device_ports(device_id):
    """Assign or update device port connections"""
    try:
        data = request.get_json()
        
        # Get port IDs from request
        control_port_id = data.get('control_port_id')
        media_main_port_id = data.get('media_main_port_id')
        media_backup_port_id = data.get('media_backup_port_id')
        
        # Assign ports
        result = PortAllocationService.assign_device_to_ports(
            device_id=device_id,
            control_port_id=control_port_id,
            media_main_port_id=media_main_port_id,
            media_backup_port_id=media_backup_port_id
        )
        
        if result['success']:
            return jsonify({
                'message': result['message'],
                'connections': [conn.to_dict() if hasattr(conn, 'to_dict') else {
                    'id': conn.id,
                    'device_id': conn.device_id,
                    'port_id': conn.port_id,
                    'connection_type': conn.connection_type
                } for conn in result['connections']]
            }), 200
        else:
            return jsonify({'error': result['message']}), 400
            
    except Exception as e:
        return jsonify({'error': f'Failed to assign ports: {str(e)}'}), 500

@api_bp.route('/devices/<int:device_id>/ports', methods=['DELETE'])
@require_role(UserRole.OPERATOR)
def detach_device_ports(device_id):
    """Remove all port assignments for a device"""
    try:
        result = PortAllocationService.detach_device_from_ports(device_id)
        
        if result['success']:
            return jsonify({'message': result['message']}), 200
        else:
            return jsonify({'error': result['message']}), 400
            
    except Exception as e:
        return jsonify({'error': f'Failed to detach ports: {str(e)}'}), 500

# Port Update Endpoint
@api_bp.route('/ports/<int:port_id>', methods=['PUT'])
@require_role(UserRole.OPERATOR)
def update_port(port_id):
    """Update port information"""
    port = SwitchPort.query.get_or_404(port_id)
    data = request.get_json()
    
    updatable_fields = ['description', 'vlan']
    
    for field in updatable_fields:
        if field in data:
            setattr(port, field, data[field])
    
    db.session.commit()
    
    return jsonify({
        'message': 'Port updated successfully',
        'port': port.to_dict()
    })

# Device Connection Endpoints
@api_bp.route('/device-connections', methods=['POST'])
@require_role(UserRole.OPERATOR)
def create_device_connection():
    """Create a connection between a device and a port"""
    data = request.get_json()
    
    if not data or not all(k in data for k in ['device_id', 'port_id', 'connection_type']):
        return jsonify({'error': 'device_id, port_id, and connection_type are required'}), 400
    
    # Validate device and port exist
    device = Device.query.get_or_404(data['device_id'])
    port = SwitchPort.query.get_or_404(data['port_id'])
    
    # Check if port is usable and available
    if not port.is_usable:
        return jsonify({'error': 'Port is not usable'}), 400
    
    if not port.is_available:
        return jsonify({'error': 'Port is already in use'}), 400
    
    # Create the connection
    connection = DeviceConnection(
        device_id=data['device_id'],
        port_id=data['port_id'],
        connection_type=data['connection_type'],
        cable_type=data.get('cable_type')
    )
    
    db.session.add(connection)
    
    # Mark port as not available
    port.is_available = False
    
    db.session.commit()
    
    return jsonify({
        'message': 'Device connection created successfully',
        'connection': connection.to_dict()
    })



@api_bp.route('/device-connections/<int:connection_id>', methods=['DELETE'])
@require_role(UserRole.OPERATOR)
def delete_device_connection(connection_id):
    """Delete a device connection and free up the port"""
    connection = DeviceConnection.query.get_or_404(connection_id)
    
    # Free up the port
    port = connection.port
    port.is_available = True
    
    db.session.delete(connection)
    db.session.commit()
    
    return jsonify({'message': 'Device connection deleted successfully'})

@api_bp.route('/device-connections/<int:connection_id>', methods=['PUT'])
@require_role(UserRole.OPERATOR)
def update_device_connection(connection_id):
    """Update a device connection to use a different port"""
    data = request.get_json()
    new_port_id = data.get('port_id')
    
    if not new_port_id:
        return jsonify({'error': 'port_id is required'}), 400
    
    # Get the connection and validate it exists
    connection = DeviceConnection.query.get_or_404(connection_id)
    
    # Get the new port and validate it's available
    new_port = SwitchPort.query.get_or_404(new_port_id)
    if not new_port.is_available:
        return jsonify({'error': 'Selected port is not available'}), 400
    
    # Free up the old port
    if connection.port:
        connection.port.is_available = True
    
    # Assign the new port
    connection.port_id = new_port_id
    new_port.is_available = False
    
    db.session.commit()
    
    return jsonify({
        'message': 'Device connection updated successfully',
        'connection': {
            'id': connection.id,
            'device_id': connection.device_id,
            'port_id': connection.port_id,
            'connection_type': connection.connection_type,
            'port': {
                'id': new_port.id,
                'port_number': new_port.port_number,
                'port_name': new_port.port_name,
                'speed': new_port.speed.value if hasattr(new_port.speed, 'value') else new_port.speed,
                'switch': {
                    'id': new_port.switch.id,
                    'name': new_port.switch.name
                } if new_port.switch else None
            }
        }
    })

@api_bp.route('/device-connections/auto-assign', methods=['POST'])
@require_role(UserRole.OPERATOR)
def auto_assign_device_port():
    """Automatically assign a device to the next available port on a switch"""
    data = request.get_json()
    
    if not data or not all(k in data for k in ['device_id', 'switch_id', 'connection_type']):
        return jsonify({'error': 'device_id, switch_id, and connection_type are required'}), 400
    
    # Validate device and switch exist
    device = Device.query.get_or_404(data['device_id'])
    switch = Switch.query.get_or_404(data['switch_id'])
    
    # Find the next available usable port
    port = PortAllocationService.allocate_next_port(
        switch_id=data['switch_id'],
        connection_type=data['connection_type']
    )
    
    if not port:
        return jsonify({'error': 'No available ports on this switch'}), 400
    
    # Create the connection
    connection = DeviceConnection(
        device_id=data['device_id'],
        port_id=port.id,
        connection_type=data['connection_type'],
        cable_type=data.get('cable_type')
    )
    
    db.session.add(connection)
    
    # Mark port as not available
    port.is_available = False
    
    db.session.commit()
    
    return jsonify({
        'message': f'Device automatically assigned to port {port.port_name or port.port_number}',
        'connection': connection.to_dict(),
        'port': port.to_dict()
    })

# IP Management Endpoints
@api_bp.route('/branches/<int:branch_id>/ips', methods=['GET'])
@require_role(UserRole.VIEWER)
def list_branch_ips(branch_id):
    """List IP allocations for a branch"""
    branch = Branch.query.get_or_404(branch_id)
    
    # Get filter parameters
    ip_type = request.args.get('ip_type')
    allocated_only = request.args.get('allocated_only', 'false').lower() == 'true'
    
    query = IPAllocation.query.filter_by(branch_id=branch_id)
    
    if ip_type:
        query = query.filter_by(ip_type=ip_type)
    
    if allocated_only:
        query = query.filter_by(is_allocated=True)
    
    allocations = query.order_by(IPAllocation.ip_address).all()
    
    return jsonify({
        'ip_allocations': [allocation.to_dict() for allocation in allocations],
        'branch': branch.to_dict(),
        'stats': IPAllocationService.get_branch_ip_stats(branch_id)
    })

@api_bp.route('/branches/<int:branch_id>/ips/allocate', methods=['POST'])
@require_role(UserRole.OPERATOR)
def allocate_ips(branch_id):
    """Allocate selected IP addresses"""
    data = request.get_json()
    ip_ids = data.get('ip_ids', [])
    allocated_to = data.get('allocated_to', 'Manual Allocation')
    notes = data.get('notes', '')
    
    if not ip_ids:
        return jsonify({'error': 'No IP addresses selected'}), 400
    
    # Get branch
    branch = Branch.query.get_or_404(branch_id)
    
    # Get the IP allocations
    allocations = IPAllocation.query.filter(
        and_(
            IPAllocation.id.in_(ip_ids),
            IPAllocation.branch_id == branch_id,
            IPAllocation.is_allocated == False
        )
    ).all()
    
    if not allocations:
        return jsonify({'error': 'No available IPs found for allocation'}), 400
    
    # Allocate the IPs
    allocated_count = 0
    for allocation in allocations:
        allocation.is_allocated = True
        allocation.allocated_to = allocated_to
        allocation.allocated_at = datetime.utcnow()
        if notes:
            allocation.notes = notes
        allocated_count += 1
    
    db.session.commit()
    
    return jsonify({
        'message': f'Successfully allocated {allocated_count} IP addresses',
        'allocated_count': allocated_count,
        'allocated_to': allocated_to
    })

@api_bp.route('/branches/<int:branch_id>/ips/deallocate', methods=['POST'])
@require_role(UserRole.OPERATOR)
def deallocate_ips(branch_id):
    """Deallocate selected IP addresses"""
    data = request.get_json()
    ip_ids = data.get('ip_ids', [])
    
    if not ip_ids:
        return jsonify({'error': 'No IP addresses selected'}), 400
    
    # Get branch
    branch = Branch.query.get_or_404(branch_id)
    
    # Get the IP allocations
    allocations = IPAllocation.query.filter(
        and_(
            IPAllocation.id.in_(ip_ids),
            IPAllocation.branch_id == branch_id,
            IPAllocation.is_allocated == True
        )
    ).all()
    
    if not allocations:
        return jsonify({'error': 'No allocated IPs found for deallocation'}), 400
    
    # Deallocate the IPs
    deallocated_count = 0
    for allocation in allocations:
        allocation.is_allocated = False
        allocation.allocated_to = None
        allocation.allocated_at = None
        allocation.notes = None
        allocation.device_ip_id = None
        deallocated_count += 1
    
    db.session.commit()
    
    return jsonify({
        'message': f'Successfully deallocated {deallocated_count} IP addresses',
        'deallocated_count': deallocated_count
    })

@api_bp.route('/branches/<int:branch_id>/switches', methods=['GET'])
@require_role(UserRole.VIEWER)
def list_branch_switches(branch_id):
    """List switches in a branch by type"""
    branch = Branch.query.get_or_404(branch_id)
    
    switch_type = request.args.get('type')  # 'control' or 'media'
    
    query = Switch.query.filter_by(branch_id=branch_id)
    
    if switch_type:
        from models import SwitchType
        if switch_type == 'control':
            query = query.filter_by(switch_type=SwitchType.CONTROL)
        elif switch_type == 'media':
            query = query.filter_by(switch_type=SwitchType.MEDIA)
    
    switches = query.order_by(Switch.name).all()
    
    return jsonify({
        'switches': [switch.to_dict() for switch in switches],
        'branch': branch.to_dict()
    })

@api_bp.route('/branches/<int:branch_id>/ports', methods=['GET'])
@require_role(UserRole.VIEWER)
def list_branch_ports(branch_id):
    """List all ports in a branch with their connections"""
    branch = Branch.query.get_or_404(branch_id)
    
    ports_data = []
    for switch in branch.switches:
        for port in switch.ports:
            port_data = port.to_dict()
            port_data['switch_name'] = switch.name
            port_data['connections'] = [conn.to_dict() for conn in port.device_connections]
            ports_data.append(port_data)
    
    return jsonify({
        'ports': ports_data,
        'branch': branch.to_dict()
    })

# Statistics and Dashboard Endpoints
@api_bp.route('/dashboard/stats', methods=['GET'])
@require_role(UserRole.VIEWER)
def get_dashboard_stats():
    """Get dashboard statistics"""
    stats = {
        'total_branches': Branch.query.count(),
        'total_switches': Switch.query.count(),
        'total_devices': Device.query.count(),
        'total_ports': SwitchPort.query.count(),
        'available_ports': SwitchPort.query.filter_by(is_available=True).count(),
        'multicast_devices': Device.query.filter_by(supports_multicast=True).count(),
        'recent_devices': [
            device.to_dict() for device in 
            Device.query.order_by(Device.created_at.desc()).limit(5).all()
        ]
    }
    
    # Branch-wise statistics
    branch_stats = []
    for branch in Branch.query.all():
        branch_data = branch.to_dict()
        branch_data['ip_stats'] = IPAllocationService.get_branch_ip_stats(branch.id)
        branch_stats.append(branch_data)
    
    stats['branch_stats'] = branch_stats
    
    return jsonify(stats)

# Error handlers
@api_bp.errorhandler(404)
def not_found(error):
    return jsonify({'error': 'Resource not found'}), 404

@api_bp.errorhandler(400)
def bad_request(error):
    return jsonify({'error': 'Bad request'}), 400

@api_bp.errorhandler(500)
def internal_error(error):
    db.session.rollback()
    return jsonify({'error': 'Internal server error'}), 500

@api_bp.route('/branches/<int:branch_id>/multicast-config', methods=['GET'])
@require_role(UserRole.VIEWER)
def get_branch_multicast_config(branch_id):
    """Get branch multicast configuration"""
    branch = Branch.query.get_or_404(branch_id)
    config = BranchMulticastConfig.query.filter_by(branch_id=branch_id).first()
    
    if not config:
        # Create default config if none exists
        config = BranchMulticastConfig(branch_id=branch_id)
        db.session.add(config)
        db.session.commit()
    
    return jsonify(config.to_dict())

@api_bp.route('/branches/<int:branch_id>/multicast-config', methods=['PUT'])
@require_role(UserRole.OPERATOR)  
def update_branch_multicast_config(branch_id):
    """Update branch multicast configuration"""
    branch = Branch.query.get_or_404(branch_id)
    data = request.get_json()
    
    config = BranchMulticastConfig.query.filter_by(branch_id=branch_id).first()
    
    if not config:
        # Create new config
        config = BranchMulticastConfig(branch_id=branch_id)
        db.session.add(config)
    
    # Update port configuration
    updatable_fields = [
        'multicast_video_port', 'multicast_audio1_port', 'multicast_audio2_port',
        'multicast_audio3_port', 'multicast_audio4_port', 'multicast_data_port'
    ]
    
    for field in updatable_fields:
        if field in data:
            setattr(config, field, data[field])
    
    db.session.commit()
    return jsonify(config.to_dict())

# Device Templates API Endpoints (Admin Only)
@api_bp.route('/device-templates', methods=['GET'])
@require_role(UserRole.VIEWER)
def get_device_templates():
    """Get all device templates"""
    templates = DeviceTemplate.query.filter_by(is_active=True).order_by(DeviceTemplate.name).all()
    return jsonify({
        'templates': [template.to_dict() for template in templates]
    })

@api_bp.route('/device-templates', methods=['POST'])
@require_role(UserRole.ADMIN)
def create_device_template():
    """Create a new device template"""
    data = request.get_json()
    
    # Validate required fields
    if not data.get('name'):
        return jsonify({'error': 'Template name is required'}), 400
    
    # Check for duplicate template names
    existing = DeviceTemplate.query.filter_by(name=data['name'], is_active=True).first()
    if existing:
        return jsonify({'error': 'A template with this name already exists'}), 400
    
    template = DeviceTemplate(
        name=data['name'],
        model=data.get('model', ''),
        manufacturer=data.get('manufacturer', ''),
        # New multi-port interface configuration
        control_ports_count=data.get('control_ports_count', 1),
        media_main_ports_count=data.get('media_main_ports_count', 1),
        media_backup_ports_count=data.get('media_backup_ports_count', 0),
        control_port_speed=data.get('control_port_speed', '1G'),
        media_main_port_speed=data.get('media_main_port_speed', '25G'),
        media_backup_port_speed=data.get('media_backup_port_speed', '25G'),
        # Legacy fields for backward compatibility
        video_ports=data.get('video_ports', 0),
        audio_ports=data.get('audio_ports', 0),
        data_ports=data.get('data_ports', 1),
        required_port_speed=data.get('required_port_speed', '10G'),
        description=data.get('description', '')
    )
    
    db.session.add(template)
    db.session.commit()
    
    return jsonify(template.to_dict()), 201

@api_bp.route('/device-templates/<int:template_id>', methods=['GET'])
@require_role(UserRole.VIEWER)
def get_device_template(template_id):
    """Get a specific device template"""
    template = DeviceTemplate.query.get_or_404(template_id)
    if not template.is_active:
        return jsonify({'error': 'Template not found'}), 404
    return jsonify(template.to_dict())

@api_bp.route('/device-templates/<int:template_id>', methods=['PUT'])
@require_role(UserRole.ADMIN)
def update_device_template(template_id):
    """Update a device template"""
    template = DeviceTemplate.query.get_or_404(template_id)
    if not template.is_active:
        return jsonify({'error': 'Template not found'}), 404
    
    data = request.get_json()
    
    # Check for duplicate names (excluding current template)
    if 'name' in data and data['name'] != template.name:
        existing = DeviceTemplate.query.filter_by(name=data['name'], is_active=True).first()
        if existing:
            return jsonify({'error': 'A template with this name already exists'}), 400
    
    # Update fields
    updatable_fields = [
        'name', 'model', 'manufacturer', 
        # New multi-port interface fields
        'control_ports_count', 'media_main_ports_count', 'media_backup_ports_count',
        'control_port_speed', 'media_main_port_speed', 'media_backup_port_speed',
        # Legacy fields for backward compatibility
        'video_ports', 'audio_ports', 'data_ports', 'required_port_speed', 'description'
    ]
    
    for field in updatable_fields:
        if field in data:
            setattr(template, field, data[field])
    
    db.session.commit()
    return jsonify(template.to_dict())

@api_bp.route('/device-templates/<int:template_id>', methods=['DELETE'])
@require_role(UserRole.ADMIN)
def delete_device_template(template_id):
    """Delete (deactivate) a device template"""
    template = DeviceTemplate.query.get_or_404(template_id)
    if not template.is_active:
        return jsonify({'error': 'Template not found'}), 404
    
    # Soft delete by setting is_active to False
    template.is_active = False
    db.session.commit()
    
    return jsonify({'message': 'Template deleted successfully'})

# CSV Export/Import Endpoints
def _extract_port_number(description):
    """Extract port number from IP description (e.g., 'Port 1' -> '1')"""
    import re
    match = re.search(r'port\s+(\d+)', description, re.IGNORECASE)
    return match.group(1) if match else ''

@api_bp.route('/devices/export', methods=['GET'])
@require_role(UserRole.VIEWER)
def export_devices_csv():
    """Export devices and their network associations to CSV"""
    try:
        # Get optional branch filter
        branch_id = request.args.get('branch_id', type=int)
        
        # Query devices with all related data
        # Join through Switch to get to Branch (Device -> Switch -> Branch)
        query = db.session.query(Device).join(Switch, Device.switch_id == Switch.id).join(Branch, Switch.branch_id == Branch.id)
        
        if branch_id:
            query = query.filter(Switch.branch_id == branch_id)
        
        devices = query.all()
        
        # Create CSV output
        output = io.StringIO()
        writer = csv.writer(output)
        
        # Write CSV headers - port-centric with all networks on same row
        headers = [
            'Device Name', 'Device Type', 'Manufacturer', 'Model', 'Branch', 'Port Number',
            'Control Switch', 'Main Switch', 'Backup Switch',
            'Control Port', 'Main Port', 'Backup Port', 
            'Control IP', 'Main Unicast IP', 'Backup Unicast IP',
            'Video Main IP', 'Video Main Port', 'Video Backup IP', 'Video Backup Port',
            'Audio1 Main IP', 'Audio1 Main Port', 'Audio1 Backup IP', 'Audio1 Backup Port',
            'Audio2 Main IP', 'Audio2 Main Port', 'Audio2 Backup IP', 'Audio2 Backup Port',
            'Audio3 Main IP', 'Audio3 Main Port', 'Audio3 Backup IP', 'Audio3 Backup Port',
            'Audio4 Main IP', 'Audio4 Main Port', 'Audio4 Backup IP', 'Audio4 Backup Port',
            'Data Main IP', 'Data Main Port', 'Data Backup IP', 'Data Backup Port',
            'Required Port Speed', 'Video Ports Total', 'Audio Ports Total', 'Data Ports Total'
        ]
        writer.writerow(headers)
        
        # Process each device - create one row per port with all networks
        for device in devices:
            # Get device connections
            connections = DeviceConnection.query.filter_by(device_id=device.id).all()
            connection_map = {conn.connection_type: conn for conn in connections}
            
            # Get all device IPs
            device_ips = DeviceIP.query.filter_by(device_id=device.id).all()
            
            # Get branch multicast configuration for port numbers
            branch_config = BranchMulticastConfig.query.filter_by(
                branch_id=device.switch.branch_id
            ).first()
            
            # Organize IPs by port number and service type
            port_data = {}  # {port_num: {service_type: {network: ip}}}
            control_ip = ''
            main_unicast_ip = ''
            backup_unicast_ip = ''
            
            for ip in device_ips:
                desc = ip.description.lower()
                
                if 'control' in desc and ip.ip_type == 'unicast':
                    control_ip = ip.ip_address
                elif 'media main' in desc and ip.ip_type == 'unicast':
                    main_unicast_ip = ip.ip_address
                elif 'media backup' in desc and ip.ip_type == 'unicast':
                    backup_unicast_ip = ip.ip_address
                elif ip.ip_type == 'multicast':
                    # Extract port number and service type
                    port_num = _extract_port_number(desc)
                    if not port_num:
                        continue
                        
                    port_num = int(port_num)
                    
                    if port_num not in port_data:
                        port_data[port_num] = {}
                    
                    # Determine service type and network
                    if 'video' in desc:
                        service = 'video'
                    elif 'audio ch1' in desc:
                        service = 'audio1'
                    elif 'audio ch2' in desc:
                        service = 'audio2'
                    elif 'audio ch3' in desc:
                        service = 'audio3'
                    elif 'audio ch4' in desc:
                        service = 'audio4'
                    elif 'data' in desc:
                        service = 'data'
                    else:
                        continue
                    
                    if service not in port_data[port_num]:
                        port_data[port_num][service] = {}
                    
                    if 'media main' in desc:
                        port_data[port_num][service]['main'] = ip.ip_address
                    elif 'media backup' in desc:
                        port_data[port_num][service]['backup'] = ip.ip_address
            
            # If no ports found, create one row with device info
            if not port_data:
                max_ports = max(device.video_ports or 0, device.audio_ports or 0, device.data_ports or 0)
                if max_ports == 0:
                    max_ports = 1
                    
                for port_num in range(1, max_ports + 1):
                    writer.writerow([
                        device.name, device.device_type.value if device.device_type else '',
                        device.manufacturer or '', device.model or '', device.switch.branch.name, port_num,
                        # Switches
                        connection_map.get('control').port.switch.name if connection_map.get('control') and connection_map.get('control').port else '',
                        connection_map.get('media_main').port.switch.name if connection_map.get('media_main') and connection_map.get('media_main').port else '',
                        connection_map.get('media_backup').port.switch.name if connection_map.get('media_backup') and connection_map.get('media_backup').port else '',
                        # Ports
                        connection_map.get('control').port.port_number if connection_map.get('control') and connection_map.get('control').port else '',
                        connection_map.get('media_main').port.port_number if connection_map.get('media_main') and connection_map.get('media_main').port else '',
                        connection_map.get('media_backup').port.port_number if connection_map.get('media_backup') and connection_map.get('media_backup').port else '',
                        # Unicast IPs
                        control_ip, main_unicast_ip, backup_unicast_ip,
                        # Video, Audio1-4, Data (all empty)
                        '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
                        # Device specs
                        device.required_port_speed or '', device.video_ports or 0, device.audio_ports or 0, device.data_ports or 0
                    ])
                continue
            
            # Create rows for each port
            for port_num in sorted(port_data.keys()):
                port_services = port_data[port_num]
                
                # Build row data
                row = [
                    # Device info
                    device.name,
                    device.device_type.value if device.device_type else '',
                    device.manufacturer or '',
                    device.model or '',
                    device.switch.branch.name,
                    port_num,
                    
                    # Switch names
                    connection_map.get('control').port.switch.name if connection_map.get('control') and connection_map.get('control').port else '',
                    connection_map.get('media_main').port.switch.name if connection_map.get('media_main') and connection_map.get('media_main').port else '',
                    connection_map.get('media_backup').port.switch.name if connection_map.get('media_backup') and connection_map.get('media_backup').port else '',
                    
                    # Switch port numbers
                    connection_map.get('control').port.port_number if connection_map.get('control') and connection_map.get('control').port else '',
                    connection_map.get('media_main').port.port_number if connection_map.get('media_main') and connection_map.get('media_main').port else '',
                    connection_map.get('media_backup').port.port_number if connection_map.get('media_backup') and connection_map.get('media_backup').port else '',
                    
                    # Unicast IPs
                    control_ip,
                    main_unicast_ip,
                    backup_unicast_ip,
                    
                    # Video multicast
                    port_services.get('video', {}).get('main', ''),
                    branch_config.multicast_video_port if branch_config else '',
                    port_services.get('video', {}).get('backup', ''),
                    branch_config.multicast_video_port if branch_config else '',
                    
                    # Audio1 multicast
                    port_services.get('audio1', {}).get('main', ''),
                    branch_config.multicast_audio1_port if branch_config else '',
                    port_services.get('audio1', {}).get('backup', ''),
                    branch_config.multicast_audio1_port if branch_config else '',
                    
                    # Audio2 multicast
                    port_services.get('audio2', {}).get('main', ''),
                    branch_config.multicast_audio2_port if branch_config else '',
                    port_services.get('audio2', {}).get('backup', ''),
                    branch_config.multicast_audio2_port if branch_config else '',
                    
                    # Audio3 multicast
                    port_services.get('audio3', {}).get('main', ''),
                    branch_config.multicast_audio3_port if branch_config else '',
                    port_services.get('audio3', {}).get('backup', ''),
                    branch_config.multicast_audio3_port if branch_config else '',
                    
                    # Audio4 multicast
                    port_services.get('audio4', {}).get('main', ''),
                    branch_config.multicast_audio4_port if branch_config else '',
                    port_services.get('audio4', {}).get('backup', ''),
                    branch_config.multicast_audio4_port if branch_config else '',
                    
                    # Data multicast
                    port_services.get('data', {}).get('main', ''),
                    branch_config.multicast_data_port if branch_config else '',
                    port_services.get('data', {}).get('backup', ''),
                    branch_config.multicast_data_port if branch_config else '',
                    
                    # Device specifications
                    device.required_port_speed or '',
                    device.video_ports or 0,
                    device.audio_ports or 0,
                    device.data_ports or 0
                ]
                
                writer.writerow(row)
        
        output.seek(0)
        
        # Create response
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        if branch_id:
            branch = Branch.query.get(branch_id)
            branch_name = branch.name.replace(' ', '_') if branch else f'branch_{branch_id}'
        else:
            branch_name = 'all_branches'
        filename = f'devices_export_{branch_name}_{timestamp}.csv'
        
        response = make_response(output.getvalue())
        response.headers['Content-Type'] = 'text/csv'
        response.headers['Content-Disposition'] = f'attachment; filename="{filename}"'
        
        return response
        
    except Exception as e:
        print(f"Error exporting devices: {str(e)}")
        return jsonify({'error': f'Export failed: {str(e)}'}), 500

def _create_device_ips_from_csv(device, connections, ports):
    """Create DeviceIP records from CSV data for a new device"""
    
    # Create control network IP
    if connections['control']['ip']:
        control_ip = DeviceIP(
            device_id=device.id,
            ip_address=connections['control']['ip'],
            ip_type='unicast',
            description='Control Network IP'
        )
        db.session.add(control_ip)
    
    # Create main network unicast IP
    if connections['main']['ip']:
        main_ip = DeviceIP(
            device_id=device.id,
            ip_address=connections['main']['ip'],
            ip_type='unicast',
            description='Media Main Network IP'
        )
        db.session.add(main_ip)
    
    # Create backup network unicast IP
    if connections['backup']['ip']:
        backup_ip = DeviceIP(
            device_id=device.id,
            ip_address=connections['backup']['ip'],
            ip_type='unicast',
            description='Media Backup Network IP'
        )
        db.session.add(backup_ip)
    
    # Create multicast IPs for each port
    for port_data in ports:
        port_num = port_data['port_number']
        
        # Video multicast IPs
        if port_data['video']['main_ip']:
            video_main_ip = DeviceIP(
                device_id=device.id,
                ip_address=port_data['video']['main_ip'],
                ip_type='multicast',
                port=int(port_data['video']['main_port']) if port_data['video']['main_port'] else None,
                description=f'Media Main Video Port {port_num}'
            )
            db.session.add(video_main_ip)
        
        if port_data['video']['backup_ip']:
            video_backup_ip = DeviceIP(
                device_id=device.id,
                ip_address=port_data['video']['backup_ip'],
                ip_type='multicast',
                port=int(port_data['video']['backup_port']) if port_data['video']['backup_port'] else None,
                description=f'Media Backup Video Port {port_num}'
            )
            db.session.add(video_backup_ip)
        
        # Audio multicast IPs
        for audio_ch in ['audio1', 'audio2', 'audio3', 'audio4']:
            ch_num = audio_ch[-1]  # Extract channel number
            
            if port_data[audio_ch]['main_ip']:
                audio_main_ip = DeviceIP(
                    device_id=device.id,
                    ip_address=port_data[audio_ch]['main_ip'],
                    ip_type='multicast',
                    port=int(port_data[audio_ch]['main_port']) if port_data[audio_ch]['main_port'] else None,
                    description=f'Media Main Audio Ch{ch_num} Port {port_num}'
                )
                db.session.add(audio_main_ip)
            
            if port_data[audio_ch]['backup_ip']:
                audio_backup_ip = DeviceIP(
                    device_id=device.id,
                    ip_address=port_data[audio_ch]['backup_ip'],
                    ip_type='multicast',
                    port=int(port_data[audio_ch]['backup_port']) if port_data[audio_ch]['backup_port'] else None,
                    description=f'Media Backup Audio Ch{ch_num} Port {port_num}'
                )
                db.session.add(audio_backup_ip)
        
        # Data multicast IPs
        if port_data['data']['main_ip']:
            data_main_ip = DeviceIP(
                device_id=device.id,
                ip_address=port_data['data']['main_ip'],
                ip_type='multicast',
                port=int(port_data['data']['main_port']) if port_data['data']['main_port'] else None,
                description=f'Media Main Data Port {port_num}'
            )
            db.session.add(data_main_ip)
        
        if port_data['data']['backup_ip']:
            data_backup_ip = DeviceIP(
                device_id=device.id,
                ip_address=port_data['data']['backup_ip'],
                ip_type='multicast',
                port=int(port_data['data']['backup_port']) if port_data['data']['backup_port'] else None,
                description=f'Media Backup Data Port {port_num}'
            )
            db.session.add(data_backup_ip)


def _update_device_ips_from_csv(device, connections, ports):
    """Update DeviceIP records from CSV data for an existing device"""
    
    # Remove existing IPs for this device
    DeviceIP.query.filter_by(device_id=device.id).delete()
    
    # Create new IPs from CSV data
    _create_device_ips_from_csv(device, connections, ports)


@api_bp.route('/devices/import', methods=['POST'])
@require_role(UserRole.OPERATOR)
def import_devices_csv():
    """Import devices from CSV file"""
    try:
        if 'file' not in request.files:
            return jsonify({'error': 'No file uploaded'}), 400
        
        file = request.files['file']
        if file.filename == '':
            return jsonify({'error': 'No file selected'}), 400
        
        if not file.filename.lower().endswith('.csv'):
            return jsonify({'error': 'File must be a CSV file'}), 400
        
        # Read and parse CSV
        stream = io.StringIO(file.stream.read().decode("UTF8"), newline=None)
        csv_reader = csv.DictReader(stream)
        
        imported_count = 0
        errors = []
        
        # Group rows by device to handle multiple ports per device
        devices_data = {}  # {device_name: {device_info, ports: [port_data]}}
        
        for row_num, row in enumerate(csv_reader, start=2):  # Start at 2 because row 1 is headers
            try:
                # Validate required fields
                device_name = row.get('Device Name', '').strip()
                branch_name = row.get('Branch', '').strip()
                port_number = row.get('Port Number', '').strip()
                
                if not device_name or not branch_name:
                    errors.append(f"Row {row_num}: Missing required fields (Device Name, Branch)")
                    continue
                
                # Group by device
                if device_name not in devices_data:
                    devices_data[device_name] = {
                        'device_info': {
                            'name': device_name,
                            'device_type': row.get('Device Type', 'multicast'),
                            'manufacturer': row.get('Manufacturer', ''),
                            'model': row.get('Model', ''),
                            'branch': branch_name,
                            'required_port_speed': row.get('Required Port Speed', '10G'),
                            'video_ports_total': int(row.get('Video Ports Total', 0)) if row.get('Video Ports Total') else 0,
                            'audio_ports_total': int(row.get('Audio Ports Total', 0)) if row.get('Audio Ports Total') else 0,
                            'data_ports_total': int(row.get('Data Ports Total', 0)) if row.get('Data Ports Total') else 0
                        },
                        'ports': [],
                        'connections': {
                            'control': {
                                'switch': row.get('Control Switch', '').strip(),
                                'port': row.get('Control Port', '').strip(),
                                'ip': row.get('Control IP', '').strip()
                            },
                            'main': {
                                'switch': row.get('Main Switch', '').strip(),
                                'port': row.get('Main Port', '').strip(),
                                'ip': row.get('Main Unicast IP', '').strip()
                            },
                            'backup': {
                                'switch': row.get('Backup Switch', '').strip(),
                                'port': row.get('Backup Port', '').strip(),
                                'ip': row.get('Backup Unicast IP', '').strip()
                            }
                        }
                    }
                
                # Add port data if port number is specified
                if port_number:
                    port_data = {
                        'port_number': int(port_number),
                        'video': {
                            'main_ip': row.get('Video Main IP', '').strip(),
                            'main_port': row.get('Video Main Port', '').strip(),
                            'backup_ip': row.get('Video Backup IP', '').strip(),
                            'backup_port': row.get('Video Backup Port', '').strip()
                        },
                        'audio1': {
                            'main_ip': row.get('Audio1 Main IP', '').strip(),
                            'main_port': row.get('Audio1 Main Port', '').strip(),
                            'backup_ip': row.get('Audio1 Backup IP', '').strip(),
                            'backup_port': row.get('Audio1 Backup Port', '').strip()
                        },
                        'audio2': {
                            'main_ip': row.get('Audio2 Main IP', '').strip(),
                            'main_port': row.get('Audio2 Main Port', '').strip(),
                            'backup_ip': row.get('Audio2 Backup IP', '').strip(),
                            'backup_port': row.get('Audio2 Backup Port', '').strip()
                        },
                        'audio3': {
                            'main_ip': row.get('Audio3 Main IP', '').strip(),
                            'main_port': row.get('Audio3 Main Port', '').strip(),
                            'backup_ip': row.get('Audio3 Backup IP', '').strip(),
                            'backup_port': row.get('Audio3 Backup Port', '').strip()
                        },
                        'audio4': {
                            'main_ip': row.get('Audio4 Main IP', '').strip(),
                            'main_port': row.get('Audio4 Main Port', '').strip(),
                            'backup_ip': row.get('Audio4 Backup IP', '').strip(),
                            'backup_port': row.get('Audio4 Backup Port', '').strip()
                        },
                        'data': {
                            'main_ip': row.get('Data Main IP', '').strip(),
                            'main_port': row.get('Data Main Port', '').strip(),
                            'backup_ip': row.get('Data Backup IP', '').strip(),
                            'backup_port': row.get('Data Backup Port', '').strip()
                        }
                    }
                    devices_data[device_name]['ports'].append(port_data)
                    
            except Exception as e:
                errors.append(f"Row {row_num}: Error parsing row - {str(e)}")
                continue
        
        # Now process each device
        for device_name, device_data in devices_data.items():
            try:
                device_info = device_data['device_info']
                connections = device_data['connections']
                ports = device_data['ports']
                
                # Find branch
                branch = Branch.query.filter_by(name=device_info['branch']).first()
                if not branch:
                    errors.append(f"Device '{device_name}': Branch '{device_info['branch']}' not found")
                    continue
                
                # Validate device type first (needed for both new and existing devices)
                device_type = DeviceType.MULTICAST  # Default
                if device_info['device_type']:
                    try:
                        device_type = DeviceType(device_info['device_type'].lower())
                    except ValueError:
                        device_type = DeviceType.MULTICAST
                
                # Check if device already exists (by name only since we support updates)
                existing_device = Device.query.filter_by(name=device_name).first()
                
                if existing_device:
                    # Update existing device instead of creating new one
                    existing_device.device_type = device_type
                    existing_device.manufacturer = device_info['manufacturer']
                    existing_device.model = device_info['model']
                    existing_device.video_ports = device_info['video_ports_total']
                    existing_device.audio_ports = device_info['audio_ports_total']
                    existing_device.data_ports = device_info['data_ports_total']
                    existing_device.required_port_speed = device_info['required_port_speed']
                    
                    # Update IP allocations from CSV
                    _update_device_ips_from_csv(existing_device, connections, ports)
                    
                    imported_count += 1
                    continue
                
                # For now, create a basic device entry
                # TODO: Implement full switch connection and IP allocation logic
                device = Device(
                    name=device_name,
                    device_type=device_type,
                    manufacturer=device_info['manufacturer'],
                    model=device_info['model'],
                    switch_id=None,  # Will need to handle 3-port connections
                    video_ports=device_info['video_ports_total'],
                    audio_ports=device_info['audio_ports_total'],
                    data_ports=device_info['data_ports_total'],
                    required_port_speed=device_info['required_port_speed']
                )
                
                db.session.add(device)
                db.session.flush()  # Get device ID
                
                # Create IP allocations based on provided IPs from CSV
                _create_device_ips_from_csv(device, connections, ports)
                
                # TODO: Create switch connections based on switch names
                # TODO: Handle port assignments and multicast configurations
                
                imported_count += 1
                
            except Exception as e:
                errors.append(f"Device '{device_name}': Error creating device - {str(e)}")
                continue
        
        db.session.commit()
        
        result = {
            'imported_count': imported_count,
            'total_rows': row_num - 1 if 'row_num' in locals() else 0,
            'errors': errors
        }
        
        if errors:
            result['status'] = 'partial_success'
            result['message'] = f'Imported {imported_count} devices with {len(errors)} errors'
        else:
            result['status'] = 'success'
            result['message'] = f'Successfully imported {imported_count} devices'
        
        return jsonify(result)
        
    except Exception as e:
        print(f"Error importing devices: {str(e)}")
        return jsonify({'error': f'Import failed: {str(e)}'}), 500


# Switch Pairs API Endpoints
@api_bp.route('/switch-pairs', methods=['GET'])
@require_role(UserRole.VIEWER)
def get_switch_pairs():
    """Get all switch pairs, optionally filtered by branch"""
    try:
        branch_id = request.args.get('branch_id', type=int)
        query = SwitchPair.query
        
        if branch_id:
            query = query.filter_by(branch_id=branch_id)
        
        switch_pairs = query.filter_by(is_active=True).all()
        return jsonify([pair.to_dict() for pair in switch_pairs])
        
    except Exception as e:
        print(f"Error fetching switch pairs: {str(e)}")
        return jsonify({'error': f'Failed to fetch switch pairs: {str(e)}'}), 500


@api_bp.route('/switch-pairs', methods=['POST'])
@require_role(UserRole.ADMIN)
def create_switch_pair():
    """Create a new switch pair (Admin only)"""
    try:
        data = request.get_json()
        
        # Validate required fields
        required_fields = ['branch_id', 'main_switch_id', 'backup_switch_id']
        for field in required_fields:
            if field not in data:
                return jsonify({'error': f'Missing required field: {field}'}), 400
        
        # Validate that switches exist and are in the same branch
        main_switch = Switch.query.get(data['main_switch_id'])
        backup_switch = Switch.query.get(data['backup_switch_id'])
        
        if not main_switch or not backup_switch:
            return jsonify({'error': 'One or both switches not found'}), 404
        
        if main_switch.branch_id != data['branch_id'] or backup_switch.branch_id != data['branch_id']:
            return jsonify({'error': 'Switches must be in the specified branch'}), 400
        
        if main_switch.id == backup_switch.id:
            return jsonify({'error': 'Main and backup switches must be different'}), 400
        
        # Check for existing pairs
        existing_main = SwitchPair.query.filter_by(
            branch_id=data['branch_id'],
            main_switch_id=data['main_switch_id'],
            is_active=True
        ).first()
        
        if existing_main:
            return jsonify({'error': f'Switch {main_switch.name} is already a main switch in a pair'}), 400
        
        existing_backup = SwitchPair.query.filter_by(
            branch_id=data['branch_id'],
            backup_switch_id=data['backup_switch_id'],
            is_active=True
        ).first()
        
        if existing_backup:
            return jsonify({'error': f'Switch {backup_switch.name} is already a backup switch in a pair'}), 400
        
        # Create switch pair
        switch_pair = SwitchPair(
            branch_id=data['branch_id'],
            main_switch_id=data['main_switch_id'],
            backup_switch_id=data['backup_switch_id'],
            description=data.get('description', ''),
            created_by=request.current_user.id if hasattr(request, 'current_user') else None
        )
        
        db.session.add(switch_pair)
        db.session.commit()
        
        return jsonify(switch_pair.to_dict()), 201
        
    except Exception as e:
        db.session.rollback()
        print(f"Error creating switch pair: {str(e)}")
        return jsonify({'error': f'Failed to create switch pair: {str(e)}'}), 500


@api_bp.route('/switch-pairs/<int:pair_id>', methods=['PUT'])
@require_role(UserRole.ADMIN)
def update_switch_pair(pair_id):
    """Update a switch pair (Admin only)"""
    try:
        switch_pair = SwitchPair.query.get_or_404(pair_id)
        data = request.get_json()
        
        # Update allowed fields
        if 'description' in data:
            switch_pair.description = data['description']
        
        if 'is_active' in data:
            switch_pair.is_active = data['is_active']
        
        db.session.commit()
        return jsonify(switch_pair.to_dict())
        
    except Exception as e:
        db.session.rollback()
        print(f"Error updating switch pair: {str(e)}")
        return jsonify({'error': f'Failed to update switch pair: {str(e)}'}), 500


@api_bp.route('/switch-pairs/<int:pair_id>', methods=['DELETE'])
@require_role(UserRole.ADMIN)
def delete_switch_pair(pair_id):
    """Delete a switch pair (Admin only)"""
    try:
        switch_pair = SwitchPair.query.get_or_404(pair_id)
        
        # Soft delete by setting is_active to False
        switch_pair.is_active = False
        db.session.commit()
        
        return jsonify({'message': 'Switch pair deleted successfully'})
        
    except Exception as e:
        db.session.rollback()
        print(f"Error deleting switch pair: {str(e)}")
        return jsonify({'error': f'Failed to delete switch pair: {str(e)}'}), 500


@api_bp.route('/switches/<int:switch_id>/paired-backup', methods=['GET'])
@require_role(UserRole.VIEWER)
def get_paired_backup_switch(switch_id):
    """Get the backup switch paired with the given main switch"""
    try:
        switch_pair = SwitchPair.query.filter_by(
            main_switch_id=switch_id,
            is_active=True
        ).first()
        
        if not switch_pair:
            return jsonify({'backup_switch': None})
        
        return jsonify({
            'backup_switch': {
                'id': switch_pair.backup_switch.id,
                'name': switch_pair.backup_switch.name,
                'switch_type': switch_pair.backup_switch.switch_type.value if switch_pair.backup_switch.switch_type else None
            },
            'pair_id': switch_pair.id,
            'description': switch_pair.description
        })
        
    except Exception as e:
        print(f"Error getting paired backup switch: {str(e)}")
        return jsonify({'error': f'Failed to get paired backup switch: {str(e)}'}), 500


# System Administration Endpoints (Admin Only)
@api_bp.route('/admin/export-db', methods=['GET'])
@require_role(UserRole.ADMIN)
def export_database():
    """Export the complete database to SQL file (Admin only)"""
    try:
        from config import Config
        DATABASE_URL = Config.DATABASE_URL
        
        # Create a temporary file for the export
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f'multicaster_db_backup_{timestamp}.sql'
        
        with tempfile.NamedTemporaryFile(mode='w+b', suffix='.sql', delete=False) as temp_file:
            temp_path = temp_file.name
        
        # Determine database type and export accordingly
        if 'postgresql' in DATABASE_URL or 'postgres' in DATABASE_URL:
            # PostgreSQL export
            cmd = ['pg_dump', DATABASE_URL, '--file', temp_path, '--verbose', '--clean', '--no-owner', '--no-privileges']
            result = subprocess.run(cmd, capture_output=True, text=True)
            
            if result.returncode != 0:
                raise Exception(f"pg_dump failed: {result.stderr}")
                
        elif 'sqlite' in DATABASE_URL:
            # SQLite export using .dump command
            db_path = DATABASE_URL.replace('sqlite:///', '')
            
            # Handle relative paths and check instance directory
            if not os.path.isabs(db_path):
                # Try instance directory first (Flask's default)
                instance_path = os.path.join('instance', os.path.basename(db_path))
                if os.path.exists(instance_path):
                    db_path = instance_path
                elif not os.path.exists(db_path):
                    raise Exception(f"Database file not found at {db_path} or {instance_path}")
            
            if not os.path.exists(db_path):
                raise Exception(f"Database file not found: {db_path}")
                
            cmd = ['sqlite3', db_path, '.dump']
            
            with open(temp_path, 'w') as f:
                result = subprocess.run(cmd, stdout=f, stderr=subprocess.PIPE, text=True)
            
            if result.returncode != 0:
                raise Exception(f"sqlite3 dump failed: {result.stderr}")
        else:
            raise Exception("Unsupported database type for export")
        
        # Read the exported file and return as response
        def generate():
            with open(temp_path, 'rb') as f:
                while True:
                    data = f.read(4096)
                    if not data:
                        break
                    yield data
            # Clean up temp file after streaming
            try:
                os.unlink(temp_path)
            except:
                pass
        
        response = make_response(generate())
        response.headers['Content-Type'] = 'application/octet-stream'
        response.headers['Content-Disposition'] = f'attachment; filename="{filename}"'
        
        return response
        
    except Exception as e:
        error_msg = str(e)
        print(f"Error exporting database: {error_msg}")
        print(f"DATABASE_URL: {DATABASE_URL}")
        
        # Clean up temp file on error
        try:
            if 'temp_path' in locals():
                os.unlink(temp_path)
        except:
            pass
        return jsonify({'error': f'Failed to export database: {error_msg}'}), 500


@api_bp.route('/admin/import-db', methods=['POST'])
@require_role(UserRole.ADMIN)
def import_database():
    """Import database from SQL file, replacing all existing data (Admin only)"""
    try:
        if 'file' not in request.files:
            return jsonify({'error': 'No file uploaded'}), 400
        
        file = request.files['file']
        if file.filename == '':
            return jsonify({'error': 'No file selected'}), 400
        
        if not file.filename.lower().endswith('.sql'):
            return jsonify({'error': 'File must be a SQL file (.sql)'}), 400
        
        from config import Config
        DATABASE_URL = Config.DATABASE_URL
        
        # Save uploaded file to temporary location
        with tempfile.NamedTemporaryFile(mode='w+b', suffix='.sql', delete=False) as temp_file:
            file.save(temp_file.name)
            temp_path = temp_file.name
        
        try:
            # Determine database type and import accordingly
            if 'postgresql' in DATABASE_URL or 'postgres' in DATABASE_URL:
                # PostgreSQL import
                cmd = ['psql', DATABASE_URL, '--file', temp_path, '--quiet']
                result = subprocess.run(cmd, capture_output=True, text=True)
                
                if result.returncode != 0:
                    raise Exception(f"psql import failed: {result.stderr}")
                    
            elif 'sqlite' in DATABASE_URL:
                # SQLite import
                db_path = DATABASE_URL.replace('sqlite:///', '')
                
                # Handle relative paths and check instance directory
                if not os.path.isabs(db_path):
                    # Try instance directory first (Flask's default)
                    instance_path = os.path.join('instance', os.path.basename(db_path))
                    if os.path.exists(instance_path):
                        db_path = instance_path
                    elif not os.path.exists(db_path):
                        # Create the instance directory if it doesn't exist
                        os.makedirs('instance', exist_ok=True)
                        db_path = instance_path
                
                # First, backup current database if it exists
                if os.path.exists(db_path):
                    backup_path = f"{db_path}.backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
                    try:
                        import shutil
                        shutil.copy2(db_path, backup_path)
                    except:
                        pass  # Continue even if backup fails
                
                # Import new database by replacing the file
                # Remove existing database file
                if os.path.exists(db_path):
                    os.remove(db_path)
                
                # Import using sqlite3 command line (more reliable than SQLAlchemy for full imports)
                cmd = ['sqlite3', db_path]
                with open(temp_path, 'r') as f:
                    result = subprocess.run(cmd, stdin=f, capture_output=True, text=True)
                
                if result.returncode != 0:
                    raise Exception(f"sqlite3 import failed: {result.stderr}")
                
                print(f"Successfully imported database to: {db_path}")
            else:
                raise Exception("Unsupported database type for import")
            
            return jsonify({'message': 'Database imported successfully'})
            
        finally:
            # Clean up temp file
            try:
                os.unlink(temp_path)
            except:
                pass
        
    except Exception as e:
        print(f"Error importing database: {str(e)}")
        db.session.rollback()
        return jsonify({'error': f'Failed to import database: {str(e)}'}), 500


@api_bp.route('/admin/erase-db', methods=['POST'])
@require_role(UserRole.ADMIN)
def erase_database():
    """Erase all data from the database (Admin only)"""
    try:
        # List of tables to clear in dependency order (child tables first)
        tables_to_clear = [
            'device_connections',
            'device_ips', 
            'ip_allocations',
            'switch_ports',
            'port_groups',
            'devices',
            'switches',
            'branch_multicast_configs',
            'device_templates',
            'switch_pairs',
            'branches'
            # Note: Not clearing users table to keep admin access
        ]
        
        # Disable foreign key constraints temporarily
        db.session.execute(db.text('PRAGMA foreign_keys = OFF;'))
        
        # Clear each table
        for table_name in tables_to_clear:
            try:
                db.session.execute(db.text(f'DELETE FROM {table_name};'))
                # Reset auto-increment counters for SQLite
                db.session.execute(db.text(f'DELETE FROM sqlite_sequence WHERE name="{table_name}";'))
            except Exception as e:
                print(f"Warning: Failed to clear table {table_name}: {e}")
        
        # Re-enable foreign key constraints
        db.session.execute(db.text('PRAGMA foreign_keys = ON;'))
        
        db.session.commit()
        
        return jsonify({
            'message': 'Database erased successfully. All data has been permanently deleted.',
            'cleared_tables': tables_to_clear
        })
        
    except Exception as e:
        print(f"Error erasing database: {str(e)}")
        db.session.rollback()
        return jsonify({'error': f'Failed to erase database: {str(e)}'}), 500