from rest_framework import viewsets, status
from rest_framework.decorators import action
from rest_framework.response import Response
from rest_framework.views import APIView
from rest_framework.permissions import IsAuthenticated
from django.shortcuts import get_object_or_404
from django.db import models
from core.scoping import scope_to_client, is_ticket_readonly, is_internal
from .models import Client, ClientContact, ClientDepartment, TicketCategory, TicketSubCategory, ClientEscalationMatrix, ClientConfiguration, ClientDatabase, ClientEmailAccount, ClientNetwork, OutboundEmail
from .serializers import (ClientSerializer, ClientListSerializer, ClientContactSerializer,
                          ClientDepartmentSerializer, TicketCategorySerializer,
                          TicketSubCategorySerializer, EscalationMatrixSerializer,
                          ClientConfigurationSerializer, ClientDatabaseSerializer,
                          ClientEmailAccountSerializer, ClientNetworkSerializer,
                          OutboundEmailSerializer)


class ClientViewSet(viewsets.ModelViewSet):
    queryset = Client.objects.all()
    search_fields = ['name', 'code', 'industry']
    filterset_fields = ['is_active']

    def get_queryset(self):
        # A client-portal user may only ever see their OWN client record —
        # otherwise the portal exposes your full customer list.
        return scope_to_client(super().get_queryset(), self.request.user, lookup='id')

    def get_serializer_class(self):
        if self.action == 'list':
            return ClientListSerializer
        return ClientSerializer

    def perform_create(self, serializer):
        client = serializer.save()
        # Auto-generate a standard SLA policy set for every new client so they
        # are never left with zero SLAs. Uses AI when configured, else tier
        # defaults. Never let SLA generation break client creation.
        try:
            from sla.autogen import create_sla_policies_for_client
            create_sla_policies_for_client(client)
        except Exception:
            pass

    def perform_destroy(self, instance):
        # Deactivate every user linked to this client before deleting it, so we
        # don't leave orphaned active accounts behind. Soft-delete (is_active=
        # False) keeps their records/audit history and is reversible; the
        # User.client FK is SET_NULL by the delete that follows.
        from django.contrib.auth import get_user_model
        User = get_user_model()
        User.objects.filter(client=instance).update(is_active=False)
        instance.delete()


class ClientContactViewSet(viewsets.ModelViewSet):
    queryset = ClientContact.objects.select_related('client').all()

    def get_queryset(self):
        return scope_to_client(super().get_queryset(), self.request.user)

    serializer_class = ClientContactSerializer
    filterset_fields = ['client', 'is_primary']


class ClientDepartmentViewSet(viewsets.ModelViewSet):
    queryset = ClientDepartment.objects.select_related('client').all()

    def get_queryset(self):
        return scope_to_client(super().get_queryset(), self.request.user)

    serializer_class = ClientDepartmentSerializer
    filterset_fields = ['client', 'is_active']


class TicketCategoryViewSet(viewsets.ModelViewSet):
    queryset = TicketCategory.objects.select_related('client').prefetch_related('sub_categories').all()

    def get_queryset(self):
        return scope_to_client(super().get_queryset(), self.request.user)

    serializer_class = TicketCategorySerializer
    filterset_fields = ['client', 'is_active']


class TicketSubCategoryViewSet(viewsets.ModelViewSet):
    queryset = TicketSubCategory.objects.select_related('category').all()

    def get_queryset(self):
        return scope_to_client(super().get_queryset(), self.request.user,
                               lookup='category__client_id')

    serializer_class = TicketSubCategorySerializer
    filterset_fields = ['category', 'is_active']


class EscalationMatrixViewSet(viewsets.ModelViewSet):
    queryset = ClientEscalationMatrix.objects.all()

    def get_queryset(self):
        return scope_to_client(super().get_queryset(), self.request.user)

    serializer_class = EscalationMatrixSerializer
    filterset_fields = ['client']


class ClientConfigView(APIView):
    permission_classes = [IsAuthenticated]

    def _get_config(self, client_id):
        client = get_object_or_404(Client, pk=client_id)
        config, _ = ClientConfiguration.objects.get_or_create(client=client)
        return config

    def get(self, request, client_id):
        config = self._get_config(client_id)
        return Response(ClientConfigurationSerializer(config).data)

    def put(self, request, client_id):
        config = self._get_config(client_id)
        writable = [
            'facebook_page', 'facebook_page_id', 'twitter_handle', 'instagram_handle',
            'linkedin_page', 'whatsapp_number', 'youtube_channel', 'tiktok_handle',
            'timezone', 'support_email', 'escalation_email', 'billing_email',
            'webhook_url', 'custom_domain', 'crm_notes',
            'vicidial_server', 'vicidial_port', 'vicidial_user', 'vicidial_pass',
            'vicidial_api_user', 'vicidial_api_pass', 'vicidial_campaigns', 'vicidial_ingroup',
            'pfsense_wan_ip', 'pfsense_lan_subnet', 'pfsense_admin_url',
            'pfsense_vpn_type', 'pfsense_vpn_notes', 'pfsense_notes',
            'pinktool_db_host', 'pinktool_db_port', 'pinktool_db_name',
            'pinktool_db_user', 'pinktool_db_pass',
        ]
        for field in writable:
            if field in request.data:
                setattr(config, field, request.data[field])
        if 'report_emails' in request.data:
            raw = (request.data['report_emails'] or '').replace(';', ',')
            config.report_emails = ', '.join([e.strip() for e in raw.split(',') if e.strip()])
        if request.data.get('request_speedtest'):
            config.speedtest_requested = True
        config.save()
        return Response(ClientConfigurationSerializer(config).data)

    def patch(self, request, client_id):
        return self.put(request, client_id)


class ClientDatabaseViewSet(viewsets.ModelViewSet):
    permission_classes = [IsAuthenticated]
    serializer_class = ClientDatabaseSerializer

    def get_queryset(self):
        # Tenant isolation first — the ?client= param is a convenience filter,
        # NOT a security boundary (a caller can simply omit it).
        qs = scope_to_client(ClientDatabase.objects.all(), self.request.user)
        client_id = self.request.query_params.get('client')
        if client_id:
            qs = qs.filter(client_id=client_id)
        return qs

    @action(detail=True, methods=['get'])
    def reports(self, request, pk=None):
        import pymysql
        from datetime import date, timedelta
        db = get_object_or_404(ClientDatabase, pk=pk)
        date_from = request.query_params.get('from') or str(date.today() - timedelta(days=13))
        date_to   = request.query_params.get('to')   or str(date.today())
        try:
            conn = pymysql.connect(host=db.host or '127.0.0.1', port=int(db.port or 3306),
                user=db.db_user, password=db.db_pass, database=db.db_name, connect_timeout=5)
            cur = conn.cursor()

            cur.execute("SHOW COLUMNS FROM tickets")
            cols = {r[0] for r in cur.fetchall()}
            has_refund = 'refund_amount' in cols

            refund_sel = 'COALESCE(SUM(t.refund_amount),0)' if has_refund else '0'

            # By day
            cur.execute(f"""
                SELECT DATE(t.ticket_date) as d, COUNT(*) as cnt, {refund_sel} as refund
                FROM tickets t
                WHERE t.ticket_date BETWEEN %s AND %s
                GROUP BY DATE(t.ticket_date) ORDER BY d
            """, [date_from, date_to])
            by_day = [{'date': str(r[0]), 'count': r[1], 'refund': float(r[2])} for r in cur.fetchall()]

            # By email type
            if 'email_type_id' in cols:
                cur.execute("SHOW TABLES LIKE 'email_types'")
                has_et = bool(cur.fetchone())
                if has_et:
                    cur.execute(f"""
                        SELECT e.name, COUNT(t.id) as cnt, {refund_sel}
                        FROM tickets t LEFT JOIN email_types e ON e.id=t.email_type_id
                        WHERE t.ticket_date BETWEEN %s AND %s
                        GROUP BY e.id, e.name ORDER BY cnt DESC
                    """, [date_from, date_to])
                    by_type = [{'name': r[0] or 'Unknown', 'count': r[1], 'refund': float(r[2])} for r in cur.fetchall()]
                else:
                    by_type = []
            else:
                by_type = []

            # By campaign
            if 'campaign_id' in cols:
                cur.execute("SHOW TABLES LIKE 'campaigns'")
                has_c = bool(cur.fetchone())
                if has_c:
                    cur.execute(f"""
                        SELECT c.name, COUNT(t.id) as cnt, {refund_sel}
                        FROM tickets t LEFT JOIN campaigns c ON c.id=t.campaign_id
                        WHERE t.ticket_date BETWEEN %s AND %s
                        GROUP BY c.id, c.name ORDER BY cnt DESC
                    """, [date_from, date_to])
                    by_campaign = [{'name': r[0] or 'Unknown', 'count': r[1], 'refund': float(r[2])} for r in cur.fetchall()]
                else:
                    by_campaign = []
            else:
                by_campaign = []

            # By agent
            if 'user_id' in cols:
                cur.execute("SHOW TABLES LIKE 'users'")
                has_u = bool(cur.fetchone())
                if has_u:
                    cur.execute(f"""
                        SELECT u.name, COUNT(t.id) as cnt, {refund_sel}
                        FROM tickets t LEFT JOIN users u ON u.id=t.user_id
                        WHERE t.ticket_date BETWEEN %s AND %s
                        GROUP BY u.id, u.name ORDER BY cnt DESC
                    """, [date_from, date_to])
                    by_agent = [{'name': r[0] or 'Unknown', 'count': r[1], 'refund': float(r[2])} for r in cur.fetchall()]
                else:
                    by_agent = []
            else:
                by_agent = []

            # Totals
            cur.execute(f"SELECT COUNT(*), {refund_sel} FROM tickets t WHERE t.ticket_date BETWEEN %s AND %s", [date_from, date_to])
            total_row = cur.fetchone()
            cur.close(); conn.close()

            return Response({
                'date_from': date_from, 'date_to': date_to,
                'total_count': total_row[0], 'total_refund': float(total_row[1]),
                'by_day': by_day, 'by_type': by_type,
                'by_campaign': by_campaign, 'by_agent': by_agent,
            })
        except Exception as e:
            return Response({'error': str(e)}, status=500)

    @action(detail=True, methods=['get'])
    def test(self, request, pk=None):
        import pymysql
        db = get_object_or_404(ClientDatabase, pk=pk)
        try:
            conn = pymysql.connect(
                host=db.host or '127.0.0.1',
                port=int(db.port or 3306),
                user=db.db_user,
                password=db.db_pass,
                database=db.db_name,
                connect_timeout=5,
            )
            with conn.cursor() as cur:
                cur.execute('SELECT VERSION()')
                version = cur.fetchone()[0]
            conn.close()
            return Response({'ok': True, 'version': version})
        except Exception as e:
            return Response({'ok': False, 'error': str(e)}, status=400)

    @action(detail=True, methods=['get'])
    def tickets(self, request, pk=None):
        import pymysql
        db = get_object_or_404(ClientDatabase, pk=pk)
        page = int(request.query_params.get('page', 1))
        page_size = int(request.query_params.get('page_size', 15))
        search = request.query_params.get('search', '').strip()
        status_filter = request.query_params.get('status', '').strip()
        offset = (page - 1) * page_size
        try:
            conn = pymysql.connect(
                host=db.host or '127.0.0.1',
                port=int(db.port or 3306),
                user=db.db_user,
                password=db.db_pass,
                database=db.db_name,
                connect_timeout=5,
            )
            cur = conn.cursor()

            # Detect available columns
            cur.execute("SHOW COLUMNS FROM tickets")
            cols = {row[0] for row in cur.fetchall()}

            select_parts = ['t.id', 't.ticket_date']
            joins = []

            if 'campaign_id' in cols:
                cur.execute("SHOW TABLES LIKE 'campaigns'")
                if cur.fetchone():
                    select_parts.append('c.name AS campaign')
                    joins.append('LEFT JOIN campaigns c ON c.id = t.campaign_id')
                else:
                    select_parts.append('NULL AS campaign')
            else:
                select_parts.append('NULL AS campaign')

            if 'user_id' in cols:
                cur.execute("SHOW TABLES LIKE 'users'")
                if cur.fetchone():
                    select_parts.append('u.name AS agent')
                    joins.append('LEFT JOIN users u ON u.id = t.user_id')
                else:
                    select_parts.append('NULL AS agent')
            else:
                select_parts.append('NULL AS agent')

            if 'email_type_id' in cols:
                cur.execute("SHOW TABLES LIKE 'email_types'")
                if cur.fetchone():
                    select_parts.append('e.name AS email_type')
                    joins.append('LEFT JOIN email_types e ON e.id = t.email_type_id')
                else:
                    select_parts.append('NULL AS email_type')
            else:
                select_parts.append('NULL AS email_type')

            select_parts.append('t.refund_amount' if 'refund_amount' in cols else 'NULL AS refund_amount')
            select_parts.append('t.status' if 'status' in cols else "NULL AS status")
            select_parts.append('t.remarks' if 'remarks' in cols else "NULL AS remarks")
            select_parts.append('t.customer_name' if 'customer_name' in cols else "NULL AS customer_name")
            select_parts.append('t.customer_email' if 'customer_email' in cols else "NULL AS customer_email")
            select_parts.append('t.order_id' if 'order_id' in cols else "NULL AS order_id")

            where_clauses = []
            params = []
            if search:
                search_cols = []
                if 'customer_name' in cols: search_cols.append('t.customer_name LIKE %s')
                if 'customer_email' in cols: search_cols.append('t.customer_email LIKE %s')
                if 'order_id' in cols: search_cols.append('t.order_id LIKE %s')
                if 'remarks' in cols: search_cols.append('t.remarks LIKE %s')
                if search_cols:
                    where_clauses.append('(' + ' OR '.join(search_cols) + ')')
                    params.extend([f'%{search}%'] * len(search_cols))
            if status_filter and 'status' in cols:
                where_clauses.append('t.status = %s')
                params.append(status_filter)

            where_sql = ('WHERE ' + ' AND '.join(where_clauses)) if where_clauses else ''
            joins_sql = ' '.join(joins)

            count_sql = f"SELECT COUNT(*) FROM tickets t {joins_sql} {where_sql}"
            cur.execute(count_sql, params)
            total = cur.fetchone()[0]

            data_sql = f"SELECT {', '.join(select_parts)} FROM tickets t {joins_sql} {where_sql} ORDER BY t.id DESC LIMIT %s OFFSET %s"
            cur.execute(data_sql, params + [page_size, offset])
            rows = cur.fetchall()

            field_names = ['id', 'ticket_date', 'campaign', 'agent', 'email_type',
                           'refund_amount', 'status', 'remarks', 'customer_name', 'customer_email', 'order_id']
            tickets = []
            for row in rows:
                t = {}
                for i, f in enumerate(field_names):
                    v = row[i]
                    t[f] = str(v) if v is not None else None
                tickets.append(t)

            # Summary stats
            cur.execute(f"SELECT COUNT(*) FROM tickets t {joins_sql} WHERE t.ticket_date = CURDATE()")
            today_count = cur.fetchone()[0]
            refund_col = 'COALESCE(SUM(t.refund_amount),0)' if 'refund_amount' in cols else '0'
            cur.execute(f"SELECT {refund_col} FROM tickets t {joins_sql}")
            total_refund = float(cur.fetchone()[0])

            cur.close()
            conn.close()

            return Response({
                'count': total,
                'today': today_count,
                'total_refund': total_refund,
                'db_name': db.name,
                'results': tickets,
            })
        except Exception as e:
            return Response({'error': str(e)}, status=500)


class ClientEmailAccountViewSet(viewsets.ModelViewSet):
    serializer_class = ClientEmailAccountSerializer
    permission_classes = [IsAuthenticated]

    def get_queryset(self):
        # Tenant isolation first — this endpoint returns IMAP/SMTP credentials,
        # so it must never fall back to "all clients" when ?client= is omitted.
        qs = scope_to_client(ClientEmailAccount.objects.all(), self.request.user)
        client_id = self.request.query_params.get('client')
        if client_id:
            qs = qs.filter(client_id=client_id)
        return qs

    def perform_destroy(self, instance):
        from tickets.models import Ticket
        Ticket.objects.filter(client=instance.client, channel='email').delete()
        instance.delete()

    @action(detail=True, methods=['post'])
    def test(self, request, pk=None):
        acct = self.get_object()
        try:
            import imaplib, ssl as _ssl
            if acct.imap_ssl:
                M = imaplib.IMAP4_SSL(acct.imap_host, int(acct.imap_port))
            else:
                M = imaplib.IMAP4(acct.imap_host, int(acct.imap_port))
            M.login(acct.imap_user or acct.email, acct.imap_pass)
            candidates = [acct.imap_folder, f'INBOX.{acct.imap_folder}', 'INBOX']
            folder, typ, data = acct.imap_folder, 'NO', [b'0']
            for f in candidates:
                typ, data = M.select(f, readonly=True)
                if typ == 'OK':
                    folder = f
                    break
            if typ != 'OK':
                # List available folders to help user configure correctly
                _, lst = M.list()
                available = [l.decode().split('"."')[-1].strip().strip('"') for l in (lst or []) if l]
                M.logout()
                return Response({'ok': False, 'message': f'Cannot open any folder. Available: {available}'})
            count = int(data[0]) if data[0] else 0
            M.logout()
            return Response({'ok': True, 'message': f'Connected. {count} emails in {folder}.'})
        except Exception as e:
            return Response({'ok': False, 'message': str(e)}, status=400)

    @action(detail=True, methods=['post'])
    def fetch(self, request, pk=None):
        """Fetch emails from IMAP and create CRM tickets (reuses cron logic)."""
        acct = self.get_object()
        try:
            from clients.management.commands.fetch_emails import fetch_account
            result = fetch_account(acct)
            summary = (f"{result['fetched']} new message(s): "
                       f"{result['created']} ticket(s), "
                       f"{result.get('threaded', 0)} reply(ies) threaded, "
                       f"{result.get('duplicates', 0)} duplicate(s).")
            if result['errors']:
                return Response({
                    'created': result['created'],
                    'fetched': result['fetched'],
                    'threaded': result.get('threaded', 0),
                    'duplicates': result.get('duplicates', 0),
                    'errors': result['errors'],
                    'message': f"{summary} Errors: {result['errors']}",
                }, status=207)
            return Response({
                'created': result['created'],
                'fetched': result['fetched'],
                'threaded': result.get('threaded', 0),
                'duplicates': result.get('duplicates', 0),
                'message': summary,
            })
        except Exception as e:
            return Response({'error': str(e)}, status=400)


class PinkToolStatsView(APIView):
    permission_classes = [IsAuthenticated]

    def get(self, request, client_id):
        import pymysql
        config = get_object_or_404(ClientConfiguration, client_id=client_id)

        if not config.pinktool_db_name:
            return Response({'error': 'PinkTool database not configured'}, status=400)

        try:
            conn = pymysql.connect(
                host=config.pinktool_db_host or '127.0.0.1',
                port=int(config.pinktool_db_port or 3306),
                user=config.pinktool_db_user,
                password=config.pinktool_db_pass,
                database=config.pinktool_db_name,
                connect_timeout=5,
            )
            cur = conn.cursor()

            # Summary counts
            cur.execute("SELECT COUNT(*) FROM tickets")
            total = cur.fetchone()[0]

            cur.execute("SELECT COUNT(*) FROM tickets WHERE DATE(ticket_date) = CURDATE()")
            today = cur.fetchone()[0]

            cur.execute("SELECT COUNT(*) FROM tickets WHERE YEAR(ticket_date)=YEAR(NOW()) AND MONTH(ticket_date)=MONTH(NOW())")
            this_month = cur.fetchone()[0]

            cur.execute("SELECT COALESCE(SUM(refund_amount),0) FROM tickets WHERE DATE(ticket_date) = CURDATE()")
            refund_today = float(cur.fetchone()[0])

            cur.execute("SELECT COALESCE(SUM(refund_amount),0) FROM tickets WHERE YEAR(ticket_date)=YEAR(NOW()) AND MONTH(ticket_date)=MONTH(NOW())")
            refund_month = float(cur.fetchone()[0])

            cur.execute("SELECT COALESCE(SUM(refund_amount),0) FROM tickets")
            refund_total = float(cur.fetchone()[0])

            # By campaign (last 30 days)
            cur.execute("""
                SELECT c.name, COUNT(t.id) as cnt, COALESCE(SUM(t.refund_amount),0) as refund
                FROM tickets t
                JOIN campaigns c ON c.id = t.campaign_id
                WHERE t.ticket_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                GROUP BY c.id, c.name ORDER BY cnt DESC LIMIT 10
            """)
            by_campaign = [{'campaign': r[0], 'tickets': r[1], 'refund': float(r[2])} for r in cur.fetchall()]

            # By agent (last 30 days)
            cur.execute("""
                SELECT u.name, COUNT(t.id) as cnt, COALESCE(SUM(t.refund_amount),0) as refund
                FROM tickets t
                JOIN users u ON u.id = t.user_id
                WHERE t.ticket_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                GROUP BY u.id, u.name ORDER BY cnt DESC LIMIT 10
            """)
            by_agent = [{'agent': r[0], 'tickets': r[1], 'refund': float(r[2])} for r in cur.fetchall()]

            # By email type (last 30 days)
            cur.execute("""
                SELECT e.name, COUNT(t.id) as cnt
                FROM tickets t
                JOIN email_types e ON e.id = t.email_type_id
                WHERE t.ticket_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                GROUP BY e.id, e.name ORDER BY cnt DESC LIMIT 10
            """)
            by_email_type = [{'type': r[0], 'count': r[1]} for r in cur.fetchall()]

            # Daily trend (last 14 days)
            cur.execute("""
                SELECT ticket_date, COUNT(*) as cnt, COALESCE(SUM(refund_amount),0) as refund
                FROM tickets
                WHERE ticket_date >= DATE_SUB(CURDATE(), INTERVAL 14 DAY)
                GROUP BY ticket_date ORDER BY ticket_date
            """)
            trend = [{'date': str(r[0]), 'tickets': r[1], 'refund': float(r[2])} for r in cur.fetchall()]

            cur.close()
            conn.close()

            return Response({
                'summary': {
                    'total': total, 'today': today, 'this_month': this_month,
                    'refund_today': refund_today, 'refund_month': refund_month, 'refund_total': refund_total,
                },
                'by_campaign': by_campaign,
                'by_agent': by_agent,
                'by_email_type': by_email_type,
                'trend': trend,
            })

        except Exception as e:
            return Response({'error': str(e)}, status=500)


class ClientNetworkViewSet(viewsets.ModelViewSet):
    serializer_class = ClientNetworkSerializer
    permission_classes = [IsAuthenticated]

    def get_queryset(self):
        qs = scope_to_client(ClientNetwork.objects.all(), self.request.user)
        client_id = self.request.query_params.get('client')
        if client_id:
            qs = qs.filter(client_id=client_id)
        return qs

    @action(detail=True, methods=['get'])
    def ping(self, request, pk=None):
        network = self.get_object()
        targets = _ping_network(network)
        return Response({
            'targets': targets,
            'all_up': all(t['up'] for t in targets if t['up'] is not None),
        })

    @action(detail=False, methods=['get'])
    def gateways(self, request):
        """Gateway names this client's pfSense has actually reported.

        Saves the operator guessing what to type in Gateway Name — these are
        the exact strings arriving from the probe.
        """
        from .models import NetworkSpeedTest, NetworkAlert
        qs = scope_to_client(NetworkSpeedTest.objects.all(), request.user)
        alerts = scope_to_client(NetworkAlert.objects.all(), request.user)
        client_id = request.query_params.get('client')
        if client_id:
            qs = qs.filter(client_id=client_id)
            alerts = alerts.filter(client_id=client_id)
        seen = set(qs.values_list('gateway', flat=True).distinct())
        seen |= set(alerts.values_list('gateway', flat=True).distinct())
        seen = sorted(g for g in seen if g)

        # Gateways the probe reports that no connection claims. Their telemetry
        # is silently landing on the primary, so it is worth saying out loud.
        nets = scope_to_client(ClientNetwork.objects.all(), request.user)
        if client_id:
            nets = nets.filter(client_id=client_id)
        claimed = {g.strip().lower()
                   for g in nets.values_list('gateway_name', flat=True) if (g or '').strip()}
        return Response({
            'gateways': seen,
            'unclaimed': [g for g in seen if g.strip().lower() not in claimed],
        })

    @action(detail=True, methods=['get'])
    def activity(self, request, pk=None):
        """Everything this pfSense has synced: outages and speed history.

        Replaces reading the same information out of the ticket queue.
        """
        from .models import NetworkAlert, NetworkSpeedTest
        network = self.get_object()

        alerts = (NetworkAlert.objects.filter(client_id=network.client_id)
                  .filter(models.Q(network=network) | models.Q(network__isnull=True))[:25])
        speeds = NetworkSpeedTest.objects.filter(
            client_id=network.client_id).filter(
            models.Q(network=network) | models.Q(network__isnull=True))[:60]

        def duration(a):
            if a.kind != NetworkAlert.DOWN or not a.resolved_at:
                return None
            return round((a.resolved_at - a.created_at).total_seconds() / 60)

        latest = {}
        for s in speeds:
            latest.setdefault(s.gateway, s)

        return Response({
            'alerts': [{
                'id': a.id, 'kind': a.kind, 'gateway': a.gateway,
                'message': a.message, 'created_at': a.created_at,
                'resolved_at': a.resolved_at, 'down_minutes': duration(a),
            } for a in alerts],
            'currently_down': list(
                NetworkAlert.objects.filter(client_id=network.client_id,
                                            kind=NetworkAlert.DOWN, resolved_at__isnull=True)
                .values_list('gateway', flat=True).distinct()),
            'speed_latest': [{
                'gateway': g, 'mbps': s.mbps, 'raw': s.raw, 'tested_at': s.tested_at,
            } for g, s in latest.items()],
            'speed_history': [{
                'gateway': s.gateway, 'mbps': s.mbps, 'tested_at': s.tested_at,
            } for s in speeds],
        })


def _do_ping(host):
    import subprocess, platform
    if not host:
        return None
    # Platform-correct flags: Windows -w is milliseconds; Linux uses -W seconds
    # for the per-reply wait (the old '-w 1000' meant 1000s on Linux, so every
    # down target hung until the subprocess timeout).
    if platform.system().lower() == 'windows':
        args = ['ping', '-n', '1', '-w', '1000', host]
    else:
        args = ['ping', '-c', '1', '-W', '2', host]
    try:
        result = subprocess.run(
            args, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL, timeout=4
        )
        return result.returncode == 0
    except Exception:
        return False


def _ping_network(network):
    targets = []
    internet_up = _do_ping('8.8.8.8')
    targets.append({'label': 'Internet', 'host': '8.8.8.8', 'up': internet_up, 'type': 'internet'})

    # The connection's own address. static_ip is the current field; pfsense_wan_ip
    # is the legacy one, still honoured for links added before the form changed.
    host = network.static_ip or network.pfsense_wan_ip
    if host:
        targets.append({'label': 'Static IP', 'host': host, 'up': _do_ping(host), 'type': 'pfsense'})

    if network.pfsense_admin_url:
        import requests as req
        try:
            r = req.get(network.pfsense_admin_url, timeout=3, verify=False)
            up = r.status_code < 500
        except Exception:
            up = False
        targets.append({'label': 'pfSense Admin', 'host': network.pfsense_admin_url, 'up': up, 'type': 'pfsense'})

    # starlink_ip is legacy and no longer pinged — it auto-filled with a private
    # default nobody entered, and reported DOWN forever. Real extra hosts go in
    # extra_ping_targets, which is what the form now collects.

    if network.extra_ping_targets:
        for t in network.extra_ping_targets.split(','):
            t = t.strip()
            if t:
                up = _do_ping(t)
                targets.append({'label': t, 'host': t, 'up': up, 'type': 'custom'})

    return targets


class NetworkStatusView(APIView):
    permission_classes = [IsAuthenticated]

    def get(self, request, client_id):
        config = get_object_or_404(ClientConfiguration, client_id=client_id)
        targets = []

        internet_up = _do_ping('8.8.8.8')
        targets.append({'label': 'Internet (Google DNS)', 'host': '8.8.8.8', 'up': internet_up, 'type': 'internet'})

        if config.pfsense_wan_ip:
            up = _do_ping(config.pfsense_wan_ip)
            targets.append({'label': 'pfSense WAN', 'host': config.pfsense_wan_ip, 'up': up, 'type': 'pfsense'})

        if config.pfsense_admin_url:
            import requests as req
            try:
                r = req.get(config.pfsense_admin_url, timeout=3, verify=False)
                up = r.status_code < 500
            except Exception:
                up = False
            targets.append({'label': 'pfSense Admin', 'host': config.pfsense_admin_url, 'up': up, 'type': 'pfsense'})

        if config.starlink_ip:
            up = _do_ping(config.starlink_ip)
            targets.append({'label': 'Starlink Router', 'host': config.starlink_ip, 'up': up, 'type': 'starlink'})

        if config.network_ping_targets:
            for t in config.network_ping_targets.split(','):
                t = t.strip()
                if t:
                    up = _do_ping(t)
                    targets.append({'label': t, 'host': t, 'up': up, 'type': 'custom'})

        return Response({
            'targets': targets,
            'all_up': all(t['up'] for t in targets if t['up'] is not None),
        })


class OutboundEmailViewSet(viewsets.ModelViewSet):
    """Agent-composed outbound mail: the sent log, and sending.

    Lives beside the email accounts so Clients -> Users / Agents can offer a
    composer without going through a ticket. Creating a row sends the message.
    """
    serializer_class = OutboundEmailSerializer
    permission_classes = [IsAuthenticated]
    search_fields = ['subject', 'to_emails', 'body']
    filterset_fields = ['status', 'account', 'sender', 'ai_assisted']

    def get_queryset(self):
        qs = scope_to_client(
            OutboundEmail.objects.select_related('account', 'sender').all(),
            self.request.user,
        )
        client_id = self.request.query_params.get('client')
        if client_id:
            qs = qs.filter(client_id=client_id)
        return qs

    def _sendable_accounts(self):
        """Mailboxes the caller is allowed to send from.

        Scoped to their client first. A client-portal agent is further limited to
        mailboxes assigned to them (or unassigned shared ones) so one agent
        cannot send as another agent's mailbox.
        """
        qs = scope_to_client(
            ClientEmailAccount.objects.filter(is_active=True), self.request.user,
        )
        user = self.request.user
        if not is_internal(user):
            qs = qs.filter(models.Q(assigned_agent=user) | models.Q(assigned_agent__isnull=True))
        return qs

    @action(detail=False, methods=['get'])
    def mailboxes(self, request):
        """The From: options for the composer."""
        accounts = self._sendable_accounts()
        missing_smtp = []
        out = []
        for a in accounts:
            ready = bool(a.smtp_host and a.smtp_pass)
            if not ready:
                missing_smtp.append(a.name)
            out.append({
                'id': a.id, 'name': a.name, 'email': a.email,
                'client': a.client_id, 'client_name': a.client.name,
                'assigned_agent': a.assigned_agent_id,
                'smtp_ready': ready,
            })
        return Response({
            'mailboxes': out,
            'can_send': not is_ticket_readonly(request.user),
            'needs_smtp_setup': missing_smtp,
        })

    def create(self, request, *args, **kwargs):
        # The System Administrator oversees the platform and must never be able
        # to email a customer — same rule as ticket replies.
        if is_ticket_readonly(request.user):
            return Response(
                {'error': 'Your role has read-only access and cannot send email.'},
                status=403,
            )

        serializer = self.get_serializer(data=request.data)
        serializer.is_valid(raise_exception=True)
        data = serializer.validated_data

        account = data.get('account')
        if not account:
            return Response({'error': 'Choose a mailbox to send from.'}, status=400)
        # Re-check ownership server-side: the client id and mailbox both come
        # from the request, so a caller could otherwise name another tenant's
        # mailbox and send as them.
        if not self._sendable_accounts().filter(pk=account.pk).exists():
            return Response(
                {'error': 'You are not allowed to send from that mailbox.'}, status=403,
            )
        if not (account.smtp_host and account.smtp_pass):
            return Response(
                {'error': f'"{account.name}" has no SMTP password configured yet. '
                          f'Set it under Configuration -> Email.'},
                status=400,
            )

        ticket = data.get('ticket')
        if ticket and ticket.client_id != account.client_id:
            return Response({'error': 'That ticket belongs to a different client.'}, status=400)

        record = OutboundEmail.objects.create(
            client=account.client,
            account=account,
            sender=request.user,
            to_emails=data['to_emails'],
            cc_emails=data.get('cc_emails', ''),
            subject=data['subject'],
            body=data['body'],
            ai_assisted=data.get('ai_assisted', False),
            ticket=ticket,
            status=OutboundEmail.QUEUED,
        )

        from django.utils import timezone as tz
        from core.outbound import send_outbound_email
        agent_name = request.user.get_full_name() or request.user.username
        try:
            message_id = send_outbound_email(
                account,
                to=record.to_emails,
                subject=record.subject,
                body=record.body,
                cc=record.cc_emails,
                from_name=f'{agent_name} ({account.name})' if agent_name else account.name,
            )
        except Exception as e:
            record.status = OutboundEmail.FAILED
            record.error = str(e)[:2000]
            record.save(update_fields=['status', 'error'])
            return Response(
                {'error': f'Send failed: {e}', 'id': record.id}, status=400,
            )

        record.status = OutboundEmail.SENT
        record.message_id = message_id
        record.sent_at = tz.now()
        record.save(update_fields=['status', 'message_id', 'sent_at'])

        # When the mail relates to a ticket, leave the usual public trail so the
        # conversation stays readable from the ticket.
        if ticket:
            try:
                from tickets.models import TicketComment
                TicketComment.objects.create(
                    ticket=ticket, author=request.user,
                    body=f'[Email sent to {record.to_emails}]\n\n{record.body}',
                    comment_type='public', source_message_id=message_id,
                )
            except Exception:
                pass

        return Response(self.get_serializer(record).data, status=201)
