models.py

"""
Database models for Multicaster application
"""

from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from enum import Enum
import json

db = SQLAlchemy()

class UserRole(Enum):
    """User roles for RBAC"""
    ADMIN = 'admin'
    MANAGER = 'manager'
    OPERATOR = 'operator'
    VIEWER = 'viewer'

class AuthType(Enum):
    """Authentication types"""
    LOCAL = 'local'
    LDAP = 'ldap'

class PortSpeed(Enum):
    """Switch port speeds"""
    ONE_G = '1G'
    TEN_G = '10G'
    TWENTY_FIVE_G = '25G'
    FIFTY_G = '50G'
    HUNDRED_G = '100G'

class SwitchType(Enum):
    """Switch types for network segregation"""
    CONTROL = 'control'
    MEDIA = 'media'

class DeviceType(Enum):
    """Device types"""
    MULTICAST = 'multicast'
    UNICAST = 'unicast'
    SWITCH = 'switch'
    ROUTER = 'router'
    OTHER = 'other'

class User(db.Model):
    """User model supporting both local and LDAP authentication"""
    __tablename__ = 'users'
    
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password_hash = db.Column(db.String(128))  # Only for local users
    role = db.Column(db.Enum(UserRole), nullable=False, default=UserRole.VIEWER)
    auth_type = db.Column(db.Enum(AuthType), nullable=False, default=AuthType.LOCAL)
    ldap_dn = db.Column(db.String(255))  # LDAP Distinguished Name
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    last_login = db.Column(db.DateTime)
    
    def __repr__(self):
        return f'<User {self.username}>'
    
    def to_dict(self):
        return {
            'id': self.id,
            'username': self.username,
            'email': self.email,
            'role': self.role.value,
            'auth_type': self.auth_type.value,
            'is_active': self.is_active,
            'created_at': self.created_at.isoformat(),
            'last_login': self.last_login.isoformat() if self.last_login else None
        }

class Branch(db.Model):
    """Company branch/facility model"""
    __tablename__ = 'branches'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    code = db.Column(db.String(10), unique=True, nullable=False)  # Branch code (e.g., NYC, LA)
    address = db.Column(db.Text)
    vlan_range = db.Column(db.String(50))       # VLAN range (e.g., 100-199)
    
    # Control Network (Unicast only - no multicast allowed)
    unicast_control_ranges = db.Column(db.JSON)     # Array of control unicast ranges (e.g., ["10.1.0.0/24", "10.2.0.0/24"])
    
    # Media Main Network (Unicast + Video/Audio/Data Multicast)
    unicast_media_main_ranges = db.Column(db.JSON)  # Array of media main unicast ranges (e.g., ["10.1.1.0/24", "10.2.1.0/24"])
    multicast_media_main_video_ranges = db.Column(db.JSON)  # Array of media main video multicast ranges
    multicast_media_main_audio1_ranges = db.Column(db.JSON)  # Array of media main audio channel 1 multicast ranges  
    multicast_media_main_audio2_ranges = db.Column(db.JSON)  # Array of media main audio channel 2 multicast ranges
    multicast_media_main_audio3_ranges = db.Column(db.JSON)  # Array of media main audio channel 3 multicast ranges
    multicast_media_main_audio4_ranges = db.Column(db.JSON)  # Array of media main audio channel 4 multicast ranges
    multicast_media_main_data_ranges = db.Column(db.JSON)   # Array of media main data multicast ranges
    
    # Media Backup Network (Unicast + Video/Audio/Data Multicast)
    unicast_media_backup_ranges = db.Column(db.JSON) # Array of media backup unicast ranges (e.g., ["10.1.2.0/24", "10.2.2.0/24"])
    multicast_media_backup_video_ranges = db.Column(db.JSON)  # Array of media backup video multicast ranges
    multicast_media_backup_audio1_ranges = db.Column(db.JSON)  # Array of media backup audio channel 1 multicast ranges
    multicast_media_backup_audio2_ranges = db.Column(db.JSON)  # Array of media backup audio channel 2 multicast ranges
    multicast_media_backup_audio3_ranges = db.Column(db.JSON)  # Array of media backup audio channel 3 multicast ranges
    multicast_media_backup_audio4_ranges = db.Column(db.JSON)  # Array of media backup audio channel 4 multicast ranges
    multicast_media_backup_data_ranges = db.Column(db.JSON)   # Array of media backup data multicast ranges
    
    # /30 Subnet allocation mode for multicast (when enabled, each device port gets a /30 subnet)
    # Switch gets odd IP (.1, .5, .9, etc.), device gets even IP (.2, .6, .10, etc.)
    use_subnet30_allocation = db.Column(db.Boolean, default=False)  # Enable /30 subnet mode
    
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    switches = db.relationship('Switch', backref='branch', lazy=True, cascade='all, delete-orphan')
    ip_allocations = db.relationship('IPAllocation', backref='branch', lazy=True, cascade='all, delete-orphan')
    
    def __repr__(self):
        return f'<Branch {self.name} ({self.code})>'
    
    def to_dict(self):
        result = {
            'id': self.id,
            'name': self.name,
            'code': self.code,
            'address': self.address,
            'vlan_range': self.vlan_range,
            # Control Network
            'unicast_control_ranges': self.unicast_control_ranges or [],
            # Media Main Network  
            'unicast_media_main_ranges': self.unicast_media_main_ranges or [],
            'multicast_media_main_video_ranges': self.multicast_media_main_video_ranges or [],
            'multicast_media_main_audio1_ranges': self.multicast_media_main_audio1_ranges or [],
            'multicast_media_main_audio2_ranges': self.multicast_media_main_audio2_ranges or [],
            'multicast_media_main_audio3_ranges': self.multicast_media_main_audio3_ranges or [],
            'multicast_media_main_audio4_ranges': self.multicast_media_main_audio4_ranges or [],
            'multicast_media_main_data_ranges': self.multicast_media_main_data_ranges or [],
            # Media Backup Network
            'unicast_media_backup_ranges': self.unicast_media_backup_ranges or [],
            'multicast_media_backup_video_ranges': self.multicast_media_backup_video_ranges or [],
            'multicast_media_backup_audio1_ranges': self.multicast_media_backup_audio1_ranges or [],
            'multicast_media_backup_audio2_ranges': self.multicast_media_backup_audio2_ranges or [],
            'multicast_media_backup_audio3_ranges': self.multicast_media_backup_audio3_ranges or [],
            'multicast_media_backup_audio4_ranges': self.multicast_media_backup_audio4_ranges or [],
            'multicast_media_backup_data_ranges': self.multicast_media_backup_data_ranges or [],
            'use_subnet30_allocation': self.use_subnet30_allocation,
            'created_at': self.created_at.isoformat(),
            'switch_count': len(self.switches),
            'device_count': sum(len(switch.devices) for switch in self.switches)
        }
        
        # Include multicast configuration if it exists
        if self.multicast_config:
            result['multicast_config'] = self.multicast_config.to_dict()
        
        return result
    
    # Control Network Helper Methods
    def get_all_control_ranges(self):
        """Get all control unicast ranges"""
        return self.unicast_control_ranges or []
    
    # Media Main Network Helper Methods
    def get_all_media_main_ranges(self):
        """Get all media main unicast ranges"""
        return self.unicast_media_main_ranges or []
    
    def get_all_media_main_video_ranges(self):
        """Get all media main video multicast ranges"""
        return self.multicast_media_main_video_ranges or []
    
    def get_all_media_main_audio1_ranges(self):
        """Get all media main audio channel 1 multicast ranges"""
        return self.multicast_media_main_audio1_ranges or []
    
    def get_all_media_main_audio2_ranges(self):
        """Get all media main audio channel 2 multicast ranges"""
        return self.multicast_media_main_audio2_ranges or []
    
    def get_all_media_main_audio3_ranges(self):
        """Get all media main audio channel 3 multicast ranges"""
        return self.multicast_media_main_audio3_ranges or []
    
    def get_all_media_main_audio4_ranges(self):
        """Get all media main audio channel 4 multicast ranges"""
        return self.multicast_media_main_audio4_ranges or []
    
    def get_all_media_main_data_ranges(self):
        """Get all media main data multicast ranges"""
        return self.multicast_media_main_data_ranges or []
    
    # Media Backup Network Helper Methods
    def get_all_media_backup_ranges(self):
        """Get all media backup unicast ranges"""
        return self.unicast_media_backup_ranges or []
    
    def get_all_media_backup_video_ranges(self):
        """Get all media backup video multicast ranges"""
        return self.multicast_media_backup_video_ranges or []
    
    def get_all_media_backup_audio1_ranges(self):
        """Get all media backup audio channel 1 multicast ranges"""
        return self.multicast_media_backup_audio1_ranges or []
    
    def get_all_media_backup_audio2_ranges(self):
        """Get all media backup audio channel 2 multicast ranges"""
        return self.multicast_media_backup_audio2_ranges or []
    
    def get_all_media_backup_audio3_ranges(self):
        """Get all media backup audio channel 3 multicast ranges"""
        return self.multicast_media_backup_audio3_ranges or []
    
    def get_all_media_backup_audio4_ranges(self):
        """Get all media backup audio channel 4 multicast ranges"""
        return self.multicast_media_backup_audio4_ranges or []
    
    def get_all_media_backup_data_ranges(self):
        """Get all media backup data multicast ranges"""
        return self.multicast_media_backup_data_ranges or []

class Switch(db.Model):
    """Network switch model"""
    __tablename__ = 'switches'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    model = db.Column(db.String(100))
    manufacturer = db.Column(db.String(100))
    ip_address = db.Column(db.String(45))  # Support IPv6
    management_ip = db.Column(db.String(45))
    location = db.Column(db.String(200))
    switch_type = db.Column(db.Enum(SwitchType), nullable=False, default=SwitchType.MEDIA)  # Control or Media switch
    api_endpoint = db.Column(db.String(255))  # For future API integration
    api_credentials = db.Column(db.Text)      # Encrypted credentials for API
    is_managed = db.Column(db.Boolean, default=False)  # Whether we can manage via API
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Foreign keys
    branch_id = db.Column(db.Integer, db.ForeignKey('branches.id'), nullable=False)
    
    # Relationships
    ports = db.relationship('SwitchPort', backref='switch', lazy=True, cascade='all, delete-orphan')
    devices = db.relationship('Device', foreign_keys='Device.switch_id', backref='switch', lazy=True)
    
    def __repr__(self):
        return f'<Switch {self.name} in {self.branch.name}>'
    
    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'model': self.model,
            'manufacturer': self.manufacturer,
            'ip_address': self.ip_address,
            'management_ip': self.management_ip,
            'location': self.location,
            'switch_type': self.switch_type.value,
            'is_managed': self.is_managed,
            'branch_id': self.branch_id,
            'branch_name': self.branch.name,
            'port_count': len(self.ports),
            'device_count': len(self.devices),
            'created_at': self.created_at.isoformat()
        }

class PortGroup(db.Model):
    """Port group model for managing breakout configurations"""
    __tablename__ = 'port_groups'
    
    id = db.Column(db.Integer, primary_key=True)
    group_number = db.Column(db.Integer, nullable=False)  # 1, 2, 3, 4... (for ports 1-4, 5-8, 9-12, 13-16...)
    current_mode = db.Column(db.Enum(PortSpeed), nullable=False, default=PortSpeed.HUNDRED_G)  # 100G or 25G
    is_broken_out = db.Column(db.Boolean, default=False)  # True if in 25G mode
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Foreign keys
    switch_id = db.Column(db.Integer, db.ForeignKey('switches.id'), nullable=False)
    
    # Relationships
    ports = db.relationship('SwitchPort', backref='port_group', lazy=True)
    
    def __repr__(self):
        return f'<PortGroup {self.group_number} on Switch {self.switch_id}>'
    
    @property
    def start_port(self):
        """Get the first port number in this group"""
        return (self.group_number - 1) * 4 + 1
    
    @property
    def end_port(self):
        """Get the last port number in this group"""
        return self.group_number * 4
    
    @property
    def available_ports(self):
        """Get available ports based on current mode"""
        if self.is_broken_out:
            # In 25G mode, only odd ports (1, 3) are available
            return [p for p in self.ports if p.port_number % 2 == 1 and p.is_available]
        else:
            # In 100G mode, all 4 ports are available
            return [p for p in self.ports if p.is_available]
    
    def to_dict(self):
        return {
            'id': self.id,
            'group_number': self.group_number,
            'start_port': self.start_port,
            'end_port': self.end_port,
            'current_mode': self.current_mode.value,
            'is_broken_out': self.is_broken_out,
            'switch_id': self.switch_id,
            'available_ports': len(self.available_ports),
            'total_ports': len(self.ports),
            'created_at': self.created_at.isoformat()
        }

class SwitchPort(db.Model):
    """Switch port model"""
    __tablename__ = 'switch_ports'
    
    id = db.Column(db.Integer, primary_key=True)
    port_number = db.Column(db.Integer, nullable=False)
    port_name = db.Column(db.String(50))  # e.g., "GigE1/0/1", "TenGigE1/1/1"
    speed = db.Column(db.Enum(PortSpeed), nullable=False)
    is_available = db.Column(db.Boolean, default=True)
    is_breakout = db.Column(db.Boolean, default=False)  # Is this a breakout port?
    parent_port_id = db.Column(db.Integer, db.ForeignKey('switch_ports.id'))  # For breakout ports
    vlan = db.Column(db.Integer)
    description = db.Column(db.String(255))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Foreign keys
    switch_id = db.Column(db.Integer, db.ForeignKey('switches.id'), nullable=False)
    port_group_id = db.Column(db.Integer, db.ForeignKey('port_groups.id'))
    
    # Relationships
    breakout_ports = db.relationship('SwitchPort', backref=db.backref('parent_port', remote_side=[id]))
    device_connections = db.relationship('DeviceConnection', backref='port', lazy=True)
    
    def __repr__(self):
        return f'<SwitchPort {self.port_name or self.port_number} on {self.switch.name}>'
    
    @property
    def is_usable(self):
        """Check if port is usable based on port group configuration"""
        # Breakout sub-ports are always usable if available
        if self.parent_port_id is not None:
            return self.is_available
        
        if not self.port_group:
            return self.is_available
        
        # In 100G mode, all main ports are usable
        if not self.port_group.is_broken_out:
            return self.is_available
        
        # In 25G breakout mode:
        # - Main ports are not directly usable (use their sub-ports instead)
        # - Only sub-ports of odd main ports are usable
        if self.port_group.is_broken_out:
            return False  # Main ports are not usable in breakout mode, use sub-ports
        
        return self.is_available
    
    def to_dict(self):
        port_group_id = self.port_group_id
        port_group = self.port_group
            
        return {
            'id': self.id,
            'port_number': self.port_number,
            'port_name': self.port_name,
            'speed': self.speed.value,
            'is_available': self.is_available,
            'is_usable': self.is_usable,  # New: indicates if port can actually be used
            'is_breakout': self.is_breakout,
            'parent_port_id': self.parent_port_id,
            'port_group_id': port_group_id,
            'port_group': port_group.to_dict() if port_group else None,
            'vlan': self.vlan,
            'description': self.description,
            'switch_id': self.switch_id,
            'switch_name': self.switch.name,
            'connected_devices': len(self.device_connections),
            'created_at': self.created_at.isoformat()
        }

class Device(db.Model):
    """Network device model"""
    __tablename__ = 'devices'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    model = db.Column(db.String(100))
    manufacturer = db.Column(db.String(100))
    device_type = db.Column(db.Enum(DeviceType), nullable=False)
    serial_number = db.Column(db.String(100))
    
    # Port information (legacy fields for backward compatibility)
    video_ports = db.Column(db.Integer, default=0)
    audio_ports = db.Column(db.Integer, default=0)
    data_ports = db.Column(db.Integer, default=0)
    
    # Device capabilities
    supports_multicast = db.Column(db.Boolean, default=False)
    supports_unicast = db.Column(db.Boolean, default=True)
    
    # New 3-port device structure
    control_switch_id = db.Column(db.Integer, db.ForeignKey('switches.id'))    # Control network switch
    media_main_switch_id = db.Column(db.Integer, db.ForeignKey('switches.id')) # Media main network switch
    media_backup_switch_id = db.Column(db.Integer, db.ForeignKey('switches.id')) # Media backup network switch
    
    # Port requirements
    required_port_speed = db.Column(db.String(10), default='10G')  # '10G', '25G', '50G', '100G'
    
    # Template reference for device creation
    template_id = db.Column(db.Integer, db.ForeignKey('device_templates.id'))  # Optional template used for creation
    
    # Default multicast port numbers for each service type
    multicast_video_port = db.Column(db.Integer, default=50100)     # Default port for video multicast
    multicast_audio1_port = db.Column(db.Integer, default=50104)    # Default port for audio channel 1
    multicast_audio2_port = db.Column(db.Integer, default=50104)    # Default port for audio channel 2
    multicast_audio3_port = db.Column(db.Integer, default=50104)    # Default port for audio channel 3
    multicast_audio4_port = db.Column(db.Integer, default=50104)    # Default port for audio channel 4
    multicast_data_port = db.Column(db.Integer, default=50102)      # Default port for data multicast
    
    # Metadata
    notes = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Foreign keys (legacy - kept for backward compatibility)
    switch_id = db.Column(db.Integer, db.ForeignKey('switches.id'), nullable=True)
    
    # Relationships
    ip_addresses = db.relationship('DeviceIP', backref='device', lazy=True, cascade='all, delete-orphan')
    port_connections = db.relationship('DeviceConnection', backref='device', lazy=True, cascade='all, delete-orphan')
    
    # Switch relationships for 3-port devices
    control_switch = db.relationship('Switch', foreign_keys=[control_switch_id], backref='control_devices', post_update=True)
    media_main_switch = db.relationship('Switch', foreign_keys=[media_main_switch_id], backref='media_main_devices', post_update=True)
    media_backup_switch = db.relationship('Switch', foreign_keys=[media_backup_switch_id], backref='media_backup_devices', post_update=True)
    
    def __repr__(self):
        return f'<Device {self.name}>'
    
    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'model': self.model,
            'manufacturer': self.manufacturer,
            'device_type': self.device_type.value,
            'serial_number': self.serial_number,
            'video_ports': self.video_ports,
            'audio_ports': self.audio_ports,
            'data_ports': self.data_ports,
            'supports_multicast': self.supports_multicast,
            'supports_unicast': self.supports_unicast,
            'required_port_speed': self.required_port_speed,
            'template_id': self.template_id,
            'multicast_video_port': self.multicast_video_port,
            'multicast_audio1_port': self.multicast_audio1_port,
            'multicast_audio2_port': self.multicast_audio2_port,
            'multicast_audio3_port': self.multicast_audio3_port,
            'multicast_audio4_port': self.multicast_audio4_port,
            'multicast_data_port': self.multicast_data_port,
            'notes': self.notes,
            # Legacy switch information
            'switch_id': self.switch_id,
            'switch_name': self.switch.name if self.switch else None,
            'branch_id': self.switch.branch_id if self.switch else None,
            'branch_name': self.switch.branch.name if self.switch else None,
            # New 3-port switch information
            'control_switch_id': self.control_switch_id,
            'control_switch_name': self.control_switch.name if self.control_switch else None,
            'media_main_switch_id': self.media_main_switch_id,
            'media_main_switch_name': self.media_main_switch.name if self.media_main_switch else None,
            'media_backup_switch_id': self.media_backup_switch_id,
            'media_backup_switch_name': self.media_backup_switch.name if self.media_backup_switch else None,
            'ip_addresses': [ip.to_dict() for ip in self.ip_addresses],
            'port_connections': [conn.to_dict() for conn in self.port_connections],
            'created_at': self.created_at.isoformat()
        }

class DeviceIP(db.Model):
    """Device IP address model"""
    __tablename__ = 'device_ips'
    
    id = db.Column(db.Integer, primary_key=True)
    ip_address = db.Column(db.String(45), nullable=False)  # Support IPv6
    ip_type = db.Column(db.String(20), nullable=False)  # 'multicast', 'unicast', 'control'
    port = db.Column(db.Integer)  # Port number if applicable
    is_primary = db.Column(db.Boolean, default=False)
    description = db.Column(db.String(255))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Foreign keys
    device_id = db.Column(db.Integer, db.ForeignKey('devices.id'), nullable=False)
    
    def __repr__(self):
        return f'<DeviceIP {self.ip_address} ({self.ip_type})>'
    
    def to_dict(self):
        return {
            'id': self.id,
            'ip_address': self.ip_address,
            'ip_type': self.ip_type,
            'port': self.port,
            'is_primary': self.is_primary,
            'description': self.description,
            'device_id': self.device_id,
            'device_name': self.device.name,
            'created_at': self.created_at.isoformat()
        }

class BranchMulticastConfig(db.Model):
    """Branch-level multicast port configuration"""
    __tablename__ = 'branch_multicast_configs'
    
    id = db.Column(db.Integer, primary_key=True)
    branch_id = db.Column(db.Integer, db.ForeignKey('branches.id'), nullable=False, unique=True)
    
    # Default multicast port numbers for each service type (branch-wide)
    multicast_video_port = db.Column(db.Integer, default=50100)     # Default port for video multicast
    multicast_audio1_port = db.Column(db.Integer, default=50104)    # Default port for audio channel 1
    multicast_audio2_port = db.Column(db.Integer, default=50104)    # Default port for audio channel 2
    multicast_audio3_port = db.Column(db.Integer, default=50104)    # Default port for audio channel 3
    multicast_audio4_port = db.Column(db.Integer, default=50104)    # Default port for audio channel 4
    multicast_data_port = db.Column(db.Integer, default=50102)      # Default port for data multicast
    
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Relationships
    branch = db.relationship('Branch', backref=db.backref('multicast_config', uselist=False))
    
    def to_dict(self):
        return {
            'id': self.id,
            'branch_id': self.branch_id,
            'multicast_video_port': self.multicast_video_port,
            'multicast_audio1_port': self.multicast_audio1_port,
            'multicast_audio2_port': self.multicast_audio2_port,
            'multicast_audio3_port': self.multicast_audio3_port,
            'multicast_audio4_port': self.multicast_audio4_port,
            'multicast_data_port': self.multicast_data_port,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }

class DeviceTemplate(db.Model):
    """Device template model for standardizing device creation"""
    __tablename__ = 'device_templates'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)  # Template name (e.g., "Broadcast Camera", "Audio Mixer")
    model = db.Column(db.String(100))
    manufacturer = db.Column(db.String(100))
    
    # Network connection requirements - how many ports per interface type
    control_ports_count = db.Column(db.Integer, default=1)        # Number of control ports needed
    media_main_ports_count = db.Column(db.Integer, default=1)     # Number of media main ports needed  
    media_backup_ports_count = db.Column(db.Integer, default=0)   # Number of media backup ports needed
    
    # Legacy port counts (for multicast calculations)
    video_ports = db.Column(db.Integer, default=0)
    audio_ports = db.Column(db.Integer, default=0)
    data_ports = db.Column(db.Integer, default=1)
    
    # Port speed requirements per interface type
    control_port_speed = db.Column(db.String(10), default='1G')      # Speed for control ports
    media_main_port_speed = db.Column(db.String(10), default='25G')  # Speed for media main ports
    media_backup_port_speed = db.Column(db.String(10), default='25G') # Speed for media backup ports
    required_port_speed = db.Column(db.String(10), default='10G')   # Legacy field for backward compatibility
    
    # Template metadata
    description = db.Column(db.Text)
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def __repr__(self):
        return f'<DeviceTemplate {self.name}>'
    
    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'model': self.model,
            'manufacturer': self.manufacturer,
            # New multi-port interface configuration
            'control_ports_count': self.control_ports_count,
            'media_main_ports_count': self.media_main_ports_count,
            'media_backup_ports_count': self.media_backup_ports_count,
            'control_port_speed': self.control_port_speed,
            'media_main_port_speed': self.media_main_port_speed,
            'media_backup_port_speed': self.media_backup_port_speed,
            # Legacy fields for backward compatibility
            'has_control_port': self.control_ports_count > 0,
            'has_media_main_port': self.media_main_ports_count > 0,
            'has_media_backup_port': self.media_backup_ports_count > 0,
            'video_ports': self.video_ports,
            'audio_ports': self.audio_ports,
            'data_ports': self.data_ports,
            'required_port_speed': self.required_port_speed,
            'description': self.description,
            'is_active': self.is_active,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }

class DeviceConnection(db.Model):
    """Device to switch port connection model"""
    __tablename__ = 'device_connections'
    
    id = db.Column(db.Integer, primary_key=True)
    connection_type = db.Column(db.String(20), nullable=False)  # 'control', 'media_main', 'media_backup', 'video', 'audio', 'data' (legacy)
    cable_type = db.Column(db.String(50))  # e.g., 'Cat6', 'Fiber', 'Coax'
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Foreign keys
    device_id = db.Column(db.Integer, db.ForeignKey('devices.id'), nullable=False)
    port_id = db.Column(db.Integer, db.ForeignKey('switch_ports.id'), nullable=False)
    
    def __repr__(self):
        return f'<DeviceConnection {self.device.name} -> {self.port.port_name}>'
    
    def to_dict(self):
        return {
            'id': self.id,
            'connection_type': self.connection_type,
            'cable_type': self.cable_type,
            'device_id': self.device_id,
            'device_name': self.device.name,
            'port_id': self.port_id,
            'port_name': self.port.port_name or f'Port {self.port.port_number}',
            'switch_name': self.port.switch.name,
            'created_at': self.created_at.isoformat()
        }

class IPAllocation(db.Model):
    """IP address allocation tracking"""
    __tablename__ = 'ip_allocations'
    
    id = db.Column(db.Integer, primary_key=True)
    ip_address = db.Column(db.String(45), nullable=False)
    ip_type = db.Column(db.String(20), nullable=False)  # 'multicast', 'unicast'
    multicast_subtype = db.Column(db.String(20))  # 'video', 'audio', 'data' for multicast IPs
    is_allocated = db.Column(db.Boolean, default=False)
    allocated_to = db.Column(db.String(100))  # Device name or description
    allocated_at = db.Column(db.DateTime)
    notes = db.Column(db.Text)
    
    # Foreign keys
    branch_id = db.Column(db.Integer, db.ForeignKey('branches.id'), nullable=False)
    device_ip_id = db.Column(db.Integer, db.ForeignKey('device_ips.id'))  # If allocated to a device
    
    def __repr__(self):
        return f'<IPAllocation {self.ip_address} in {self.branch.name}>'
    
    def to_dict(self):
        return {
            'id': self.id,
            'ip_address': self.ip_address,
            'ip_type': self.ip_type,
            'multicast_subtype': self.multicast_subtype,
            'is_allocated': self.is_allocated,
            'allocated_to': self.allocated_to,
            'allocated_at': self.allocated_at.isoformat() if self.allocated_at else None,
            'notes': self.notes,
            'branch_id': self.branch_id,
            'branch_name': self.branch.name,
            'device_ip_id': self.device_ip_id
        }


class SwitchPair(db.Model):
    """Model for managing Main/Backup switch pairs for automatic redundancy"""
    __tablename__ = 'switch_pairs'
    
    id = db.Column(db.Integer, primary_key=True)
    branch_id = db.Column(db.Integer, db.ForeignKey('branches.id'), nullable=False)
    main_switch_id = db.Column(db.Integer, db.ForeignKey('switches.id'), nullable=False)
    backup_switch_id = db.Column(db.Integer, db.ForeignKey('switches.id'), nullable=False)
    description = db.Column(db.String(500))
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    
    # Relationships
    branch = db.relationship('Branch', backref='switch_pairs')
    main_switch = db.relationship('Switch', foreign_keys=[main_switch_id], backref='main_pairs')
    backup_switch = db.relationship('Switch', foreign_keys=[backup_switch_id], backref='backup_pairs')
    creator = db.relationship('User', backref='created_switch_pairs')
    
    # Constraints
    __table_args__ = (
        # Ensure main and backup switches are different
        db.CheckConstraint('main_switch_id != backup_switch_id', name='different_switches'),
        # Ensure main switch is only paired once per branch
        db.UniqueConstraint('branch_id', 'main_switch_id', name='unique_main_per_branch'),
        # Ensure backup switch is only used once per branch
        db.UniqueConstraint('branch_id', 'backup_switch_id', name='unique_backup_per_branch'),
    )
    
    def to_dict(self):
        return {
            'id': self.id,
            'branch_id': self.branch_id,
            'branch_name': self.branch.name,
            'main_switch_id': self.main_switch_id,
            'main_switch_name': self.main_switch.name,
            'backup_switch_id': self.backup_switch_id,
            'backup_switch_name': self.backup_switch.name,
            'description': self.description,
            'is_active': self.is_active,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'created_by': self.created_by,
            'creator_name': self.creator.username if self.creator else None
        }