from sqlalchemy import Column, String, DateTime, Boolean, Integer, Text, func
from app.database import Base


class NL43Config(Base):
    """
    NL43 connection/config metadata for the standalone SLMM addon.
    """

    __tablename__ = "nl43_config"

    unit_id = Column(String, primary_key=True, index=True)
    host = Column(String, default="127.0.0.1")
    tcp_port = Column(Integer, default=2255)  # NL43 TCP control port (standard: 2255)
    tcp_enabled = Column(Boolean, default=True)
    ftp_enabled = Column(Boolean, default=False)
    ftp_port = Column(Integer, default=21)  # FTP port (standard: 21)
    ftp_username = Column(String, nullable=True)  # FTP login username
    ftp_password = Column(String, nullable=True)  # FTP login password
    web_enabled = Column(Boolean, default=False)

    # Background polling configuration
    poll_interval_seconds = Column(Integer, nullable=True, default=60)  # Polling interval (10-3600 seconds)
    poll_enabled = Column(Boolean, default=True)  # Enable/disable background polling for this device

    # Wedge recovery configuration
    auto_recovery_enabled = Column(Boolean, default=False)  # Master switch for automatic wedge recovery
    reset_backend = Column(String, default="manual")  # Reset actuation: "manual" (notify only) or "webhook"
    reset_webhook_url = Column(String, nullable=True)  # URL that triggers a power cycle (relay controller)
    reset_webhook_method = Column(String, default="POST")  # HTTP method for webhook (GET or POST)
    recovery_boot_wait_seconds = Column(Integer, default=90)  # Wait after reset before reconnect attempts
    recovery_reconnect_timeout = Column(Integer, default=180)  # Max seconds to wait for device after boot wait
    recovery_auto_resume = Column(Boolean, default=True)  # Restart measurement if it was running pre-wedge
    recovery_max_attempts = Column(Integer, default=2)  # Max recovery attempts per window
    recovery_window_minutes = Column(Integer, default=360)  # Attempt-limit window (default 6 hours)


class NL43Status(Base):
    """
    Latest NL43 status snapshot for quick dashboard/API access.
    """

    __tablename__ = "nl43_status"

    unit_id = Column(String, primary_key=True, index=True)
    last_seen = Column(DateTime, default=func.now())
    measurement_state = Column(String, default="unknown")  # Measure/Stop
    measurement_start_time = Column(DateTime, nullable=True)  # When measurement started (UTC)
    counter = Column(String, nullable=True)  # d0: Measurement interval counter (1-600)
    lp = Column(String, nullable=True)    # Instantaneous sound pressure level
    leq = Column(String, nullable=True)   # Equivalent continuous sound level
    lmax = Column(String, nullable=True)  # Maximum level
    lmin = Column(String, nullable=True)  # Minimum level
    lpeak = Column(String, nullable=True)  # Peak level
    battery_level = Column(String, nullable=True)
    power_source = Column(String, nullable=True)
    sd_remaining_mb = Column(String, nullable=True)
    sd_free_ratio = Column(String, nullable=True)
    raw_payload = Column(Text, nullable=True)

    # Background polling status
    is_reachable = Column(Boolean, default=True)  # Device reachability status
    consecutive_failures = Column(Integer, default=0)  # Count of consecutive poll failures
    last_poll_attempt = Column(DateTime, nullable=True)  # Last time background poller attempted to poll
    last_success = Column(DateTime, nullable=True)  # Last successful poll timestamp
    last_error = Column(Text, nullable=True)  # Last error message (truncated to 500 chars)

    # FTP start time sync tracking
    start_time_sync_attempted = Column(Boolean, default=False)  # True if FTP sync was attempted for current measurement

    # Wedge detection and recovery tracking
    connection_state = Column(String, default="ok")  # ok | wedged | degraded | offline
    last_wedge_at = Column(DateTime, nullable=True)  # When the current/most recent wedge was detected
    wedge_count = Column(Integer, default=0)  # Lifetime count of detected wedges
    recovery_state = Column(String, default="idle")  # idle | resetting | waiting_boot | reconnecting | resuming | awaiting_manual_reset | failed
    last_recovery_at = Column(DateTime, nullable=True)  # When the last recovery attempt started
    last_recovery_result = Column(Text, nullable=True)  # Outcome summary of the last recovery attempt


class DeviceLog(Base):
    """
    Per-device log entries for debugging and audit trail.
    Stores events like commands, state changes, errors, and FTP operations.
    """

    __tablename__ = "device_logs"

    id = Column(Integer, primary_key=True, autoincrement=True)
    unit_id = Column(String, index=True, nullable=False)
    timestamp = Column(DateTime, default=func.now(), index=True)
    level = Column(String, default="INFO")  # DEBUG, INFO, WARNING, ERROR
    category = Column(String, default="GENERAL")  # TCP, FTP, POLL, COMMAND, STATE, SYNC
    message = Column(Text, nullable=False)