import logging
from datetime import datetime, timedelta
from zoneinfo import ZoneInfo
from flask import Blueprint, render_template, request, redirect, session, url_for, flash

import config
from auth.decorators import login_required
from utils.audit import log_audit
from utils.network import get_client_ip
from db import get_connection, get_db_connection, get_db_cursor
from db_license import get_license_db_cursor
from models import get_active_sessions

# Create Blueprint
session_bp = Blueprint('sessions', __name__)


@session_bp.route("/sessions")
@login_required
def sessions():
    # Use regular DB for customer/license info
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        # First get license mapping from admin DB
        cur.execute("SELECT id, license_key FROM licenses")
        license_map = {row[0]: row[1] for row in cur.fetchall()}
        
        # Get customer mapping
        cur.execute("SELECT l.id, c.name FROM licenses l JOIN customers c ON l.customer_id = c.id")
        customer_map = {row[0]: row[1] for row in cur.fetchall()}
        
        cur.close()
        conn.close()
        
        # Now get sessions from license server DB
        with get_license_db_cursor() as license_cur:
            # Get active sessions
            license_cur.execute("""
                SELECT id, license_id, session_token, ip_address, client_version,
                       started_at, last_heartbeat, hardware_id,
                       EXTRACT(EPOCH FROM (NOW() - last_heartbeat))/60 as minutes_inactive
                FROM license_sessions 
                WHERE ended_at IS NULL
                ORDER BY last_heartbeat DESC
            """)
            
            active_sessions = []
            for row in license_cur.fetchall():
                active_sessions.append((
                    row[0],  # id
                    row[2],  # session_token
                    license_map.get(row[1], 'Unknown'),  # license_key
                    customer_map.get(row[1], 'Unknown'),  # customer name
                    row[3],  # ip_address
                    row[4],  # client_version
                    row[5],  # started_at
                    row[6],  # last_heartbeat
                    row[8]   # minutes_inactive
                ))
            
            # Get recent ended sessions
            license_cur.execute("""
                SELECT id, license_id, session_token, ip_address,
                       started_at, ended_at,
                       EXTRACT(EPOCH FROM (ended_at - started_at))/60 as duration_minutes
                FROM license_sessions 
                WHERE ended_at IS NOT NULL 
                AND ended_at > NOW() - INTERVAL '24 hours'
                ORDER BY ended_at DESC
                LIMIT 50
            """)
            
            recent_sessions = []
            for row in license_cur.fetchall():
                recent_sessions.append((
                    row[0],  # id
                    row[2],  # session_token
                    license_map.get(row[1], 'Unknown'),  # license_key
                    customer_map.get(row[1], 'Unknown'),  # customer name
                    row[3],  # ip_address
                    row[4],  # started_at
                    row[5],  # ended_at
                    row[6]   # duration_minutes
                ))
        
        return render_template("sessions.html", 
                             active_sessions=active_sessions,
                             recent_sessions=recent_sessions)
    
    except Exception as e:
        logging.error(f"Error loading sessions: {str(e)}")
        flash('Fehler beim Laden der Sessions!', 'error')
        return redirect(url_for('admin.dashboard'))


@session_bp.route("/sessions/history")
@login_required  
def session_history():
    """Zeigt die Session-Historie"""
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        # Query parameters
        license_key = request.args.get('license_key', '')
        username = request.args.get('username', '')
        days = int(request.args.get('days', 7))
        
        # Base query
        query = """
            SELECT 
                ls.id,
                l.license_key,
                ls.machine_name as username,
                ls.hardware_id,
                ls.started_at,
                ls.ended_at,
                ls.last_heartbeat,
                CASE WHEN ls.ended_at IS NULL THEN true ELSE false END as is_active,
                c.name as customer_name,
                l.license_type,
                l.is_test
            FROM license_sessions ls
            JOIN licenses l ON ls.license_id = l.id
            LEFT JOIN customers c ON l.customer_id = c.id
            WHERE 1=1
        """
        
        params = []
        
        # Apply filters
        if license_key:
            query += " AND l.license_key = %s"
            params.append(license_key)
            
        if username:
            query += " AND ls.machine_name ILIKE %s"
            params.append(f'%{username}%')
            
        # Time filter
        query += " AND ls.started_at >= CURRENT_TIMESTAMP - INTERVAL '%s days'"
        params.append(days)
        
        query += " ORDER BY ls.started_at DESC LIMIT 1000"
        
        cur.execute(query, params)
        
        sessions_list = []
        for row in cur.fetchall():
            session_duration = None
            if row[4] and row[5]:  # started_at and ended_at
                duration = row[5] - row[4]
                hours = int(duration.total_seconds() // 3600)
                minutes = int((duration.total_seconds() % 3600) // 60)
                session_duration = f"{hours}h {minutes}m"
            elif row[4] and row[7]:  # started_at and is_active
                duration = datetime.now(ZoneInfo("UTC")) - row[4]
                hours = int(duration.total_seconds() // 3600)
                minutes = int((duration.total_seconds() % 3600) // 60)
                session_duration = f"{hours}h {minutes}m (aktiv)"
            
            sessions_list.append({
                'id': row[0],
                'license_key': row[1],
                'username': row[2],
                'hardware_id': row[3],
                'started_at': row[4],
                'ended_at': row[5],
                'last_heartbeat': row[6],
                'is_active': row[7],
                'customer_name': row[8],
                'license_type': row[9],
                'is_test': row[10],
                'duration': session_duration
            })
        
        # Get unique license keys for filter dropdown
        cur.execute("""
            SELECT DISTINCT l.license_key, c.name as customer_name
            FROM license_sessions ls
            JOIN licenses l ON ls.license_id = l.id
            LEFT JOIN customers c ON l.customer_id = c.id
            WHERE ls.started_at >= CURRENT_TIMESTAMP - INTERVAL '30 days'
            ORDER BY c.name, l.license_key
        """)
        
        available_licenses = []
        for row in cur.fetchall():
            available_licenses.append({
                'license_key': row[0],
                'customer_name': row[1] or 'Unbekannt'
            })
        
        return render_template("session_history.html", 
                             sessions=sessions_list,
                             available_licenses=available_licenses,
                             filters={
                                 'license_key': license_key,
                                 'username': username,
                                 'days': days
                             })
        
    except Exception as e:
        logging.error(f"Fehler beim Laden der Session-Historie: {str(e)}")
        flash('Fehler beim Laden der Session-Historie!', 'error')
        return redirect(url_for('sessions.sessions'))
    finally:
        cur.close()
        conn.close()


@session_bp.route("/session/end/<int:session_id>", methods=["POST"])
@login_required
def terminate_session(session_id):
    """Beendet eine aktive Session"""
    try:
        session_info = None
        
        # Get session info from license server DB
        with get_license_db_cursor() as license_cur:
            license_cur.execute("""
                SELECT license_id, hardware_id, machine_name 
                FROM license_sessions
                WHERE id = %s AND ended_at IS NULL
            """, (session_id,))
            
            result = license_cur.fetchone()
            if not result:
                flash('Session nicht gefunden oder bereits beendet!', 'error')
                return redirect(url_for('sessions.sessions'))
            
            license_id = result[0]
            
            # Terminate session in license server DB
            license_cur.execute("""
                UPDATE license_sessions 
                SET ended_at = CURRENT_TIMESTAMP, end_reason = 'admin_terminated' 
                WHERE id = %s
            """, (session_id,))
        
        # Get license key from admin DB for audit log
        conn = get_connection()
        cur = conn.cursor()
        cur.execute("SELECT license_key FROM licenses WHERE id = %s", (license_id,))
        license_key = cur.fetchone()[0] if cur.fetchone() else 'Unknown'
        cur.close()
        conn.close()
        
        # Audit log
        log_audit('SESSION_TERMINATE', 'session', session_id,
                 additional_info=f"Session beendet für Lizenz {license_key}")
        
        flash('Session erfolgreich beendet!', 'success')
        
    except Exception as e:
        logging.error(f"Fehler beim Beenden der Session: {str(e)}")
        flash('Fehler beim Beenden der Session!', 'error')
    
    return redirect(url_for('sessions.sessions'))


@session_bp.route("/sessions/terminate-all/<license_key>", methods=["POST"])
@login_required
def terminate_all_sessions(license_key):
    """Beendet alle aktiven Sessions einer Lizenz"""
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        # Count active sessions
        cur.execute("""
            SELECT COUNT(*) FROM license_sessions ls
            JOIN licenses l ON ls.license_id = l.id
            WHERE l.license_key = %s AND ls.ended_at IS NULL
        """, (license_key,))
        
        active_count = cur.fetchone()[0]
        
        if active_count == 0:
            flash('Keine aktiven Sessions gefunden!', 'info')
            return redirect(url_for('sessions.sessions'))
        
        # Terminate all sessions
        cur.execute("""
            UPDATE license_sessions 
            SET ended_at = CURRENT_TIMESTAMP, end_reason = 'admin_terminated_all' 
            WHERE license_id IN (
                SELECT id FROM licenses WHERE license_key = %s
            ) AND ended_at IS NULL
        """, (license_key,))
        
        conn.commit()
        
        # Audit log
        log_audit('SESSION_TERMINATE_ALL', 'license', None,
                 additional_info=f"{active_count} Sessions beendet für Lizenz {license_key}")
        
        flash(f'{active_count} Sessions erfolgreich beendet!', 'success')
        
    except Exception as e:
        conn.rollback()
        logging.error(f"Fehler beim Beenden der Sessions: {str(e)}")
        flash('Fehler beim Beenden der Sessions!', 'error')
    finally:
        cur.close()
        conn.close()
    
    return redirect(url_for('sessions.sessions'))


@session_bp.route("/sessions/cleanup", methods=["POST"])
@login_required
def cleanup_sessions():
    """Bereinigt alte inaktive Sessions"""
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        days = int(request.form.get('days', 30))
        
        # Delete old inactive sessions
        cur.execute("""
            DELETE FROM license_sessions 
            WHERE ended_at IS NOT NULL 
            AND ended_at < CURRENT_TIMESTAMP - INTERVAL '%s days'
            RETURNING id
        """, (days,))
        
        deleted_ids = [row[0] for row in cur.fetchall()]
        deleted_count = len(deleted_ids)
        
        conn.commit()
        
        # Audit log
        if deleted_count > 0:
            log_audit('SESSION_CLEANUP', 'system', None,
                     additional_info=f"{deleted_count} Sessions älter als {days} Tage gelöscht")
        
        flash(f'{deleted_count} alte Sessions bereinigt!', 'success')
        
    except Exception as e:
        conn.rollback()
        logging.error(f"Fehler beim Bereinigen der Sessions: {str(e)}")
        flash('Fehler beim Bereinigen der Sessions!', 'error')
    finally:
        cur.close()
        conn.close()
    
    return redirect(url_for('sessions.session_history'))


@session_bp.route("/sessions/statistics")
@login_required
def session_statistics():
    """Zeigt Session-Statistiken"""
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        # Aktuelle Statistiken
        cur.execute("""
            SELECT 
                COUNT(DISTINCT l.license_key) as active_licenses,
                COUNT(DISTINCT ls.machine_name) as unique_users,
                COUNT(DISTINCT ls.hardware_id) as unique_devices,
                COUNT(*) as total_active_sessions
            FROM license_sessions ls
            JOIN licenses l ON ls.license_id = l.id
            WHERE ls.ended_at IS NULL
        """)
        
        current_stats = cur.fetchone()
        
        # Sessions nach Lizenztyp
        cur.execute("""
            SELECT 
                l.license_type,
                COUNT(*) as session_count
            FROM license_sessions ls
            JOIN licenses l ON ls.license_id = l.id
            WHERE ls.ended_at IS NULL
            GROUP BY l.license_type
            ORDER BY session_count DESC
        """)
        
        sessions_by_type = []
        for row in cur.fetchall():
            sessions_by_type.append({
                'license_type': row[0],
                'count': row[1]
            })
        
        # Top 10 Lizenzen nach aktiven Sessions
        cur.execute("""
            SELECT 
                l.license_key,
                c.name as customer_name,
                COUNT(*) as session_count,
                l.device_limit
            FROM license_sessions ls
            JOIN licenses l ON ls.license_id = l.id
            JOIN customers c ON l.customer_id = c.id
            WHERE ls.ended_at IS NULL
            GROUP BY l.license_key, c.name, l.device_limit
            ORDER BY session_count DESC
            LIMIT 10
        """)
        
        top_licenses = []
        for row in cur.fetchall():
            top_licenses.append({
                'license_key': row[0],
                'customer_name': row[1],
                'session_count': row[2],
                'device_limit': row[3]
            })
        
        # Session-Verlauf (letzte 7 Tage)
        cur.execute("""
            SELECT 
                DATE(ls.started_at) as date,
                COUNT(*) as login_count,
                COUNT(DISTINCT l.license_key) as unique_licenses,
                COUNT(DISTINCT ls.machine_name) as unique_users
            FROM license_sessions ls
            JOIN licenses l ON ls.license_id = l.id
            WHERE ls.started_at >= CURRENT_DATE - INTERVAL '7 days'
            GROUP BY DATE(ls.started_at)
            ORDER BY date
        """)
        
        session_history = []
        for row in cur.fetchall():
            session_history.append({
                'date': row[0].strftime('%Y-%m-%d'),
                'login_count': row[1],
                'unique_licenses': row[2],
                'unique_users': row[3]
            })
        
        # Durchschnittliche Session-Dauer
        cur.execute("""
            SELECT 
                AVG(EXTRACT(EPOCH FROM (ended_at - started_at))/3600) as avg_duration_hours
            FROM license_sessions
            WHERE ended_at IS NOT NULL
            AND ended_at - started_at < INTERVAL '24 hours'
            AND started_at >= CURRENT_DATE - INTERVAL '30 days'
        """)
        
        avg_duration = cur.fetchone()[0] or 0
        
        return render_template("session_statistics.html",
                             current_stats={
                                 'active_licenses': current_stats[0],
                                 'unique_users': current_stats[1],
                                 'unique_devices': current_stats[2],
                                 'total_sessions': current_stats[3]
                             },
                             sessions_by_type=sessions_by_type,
                             top_licenses=top_licenses,
                             session_history=session_history,
                             avg_duration=round(avg_duration, 1))
        
    except Exception as e:
        logging.error(f"Fehler beim Laden der Session-Statistiken: {str(e)}")
        flash('Fehler beim Laden der Statistiken!', 'error')
        return redirect(url_for('sessions.sessions'))
    finally:
        cur.close()
        conn.close()