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

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

# Create Blueprint
resource_bp = Blueprint('resources', __name__)


@resource_bp.route('/resources')
@login_required
def resources():
    """Zeigt die Ressourcenpool-Übersicht"""
    import logging
    logging.info("=== RESOURCES ROUTE CALLED ===")
    
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        # Filter aus Query-Parametern
        resource_type = request.args.get('type', 'all')
        status_filter = request.args.get('status', 'all')
        search_query = request.args.get('search', '')
        show_fake = request.args.get('show_fake', 'false') == 'true'
        
        logging.info(f"Filters: type={resource_type}, status={status_filter}, search={search_query}, show_fake={show_fake}")
        
        # Basis-Query
        query = """
            SELECT 
                rp.id,
                rp.resource_type,
                rp.resource_value,
                rp.status,
                rp.is_fake,
                rp.allocated_to_license,
                rp.created_at,
                rp.status_changed_at,
                rp.status_changed_by,
                c.name as customer_name,
                l.license_type
            FROM resource_pools rp
            LEFT JOIN licenses l ON rp.allocated_to_license = l.id
            LEFT JOIN customers c ON l.customer_id = c.id
            WHERE 1=1
        """
        
        params = []
        
        # Filter anwenden
        if resource_type != 'all':
            query += " AND rp.resource_type = %s"
            params.append(resource_type)
        
        if status_filter != 'all':
            query += " AND rp.status = %s"
            params.append(status_filter)
        
        if search_query:
            query += " AND (rp.resource_value ILIKE %s OR c.name ILIKE %s)"
            params.extend([f'%{search_query}%', f'%{search_query}%'])
        
        if not show_fake:
            query += " AND rp.is_fake = false"
        
        query += " ORDER BY rp.resource_type, rp.resource_value"
        
        cur.execute(query, params)
        
        resources_list = []
        rows = cur.fetchall()
        logging.info(f"Query returned {len(rows)} rows")
        
        for row in rows:
            resources_list.append({
                'id': row[0],
                'resource_type': row[1],
                'resource_value': row[2],
                'status': row[3],
                'is_fake': row[4],
                'allocated_to_license': row[5],
                'created_at': row[6],
                'status_changed_at': row[7],
                'status_changed_by': row[8],
                'customer_name': row[9],
                'license_type': row[10]
            })
        
        # Statistiken
        stats_query = """
            SELECT 
                resource_type,
                status,
                is_fake,
                COUNT(*) as count
            FROM resource_pools
        """
        
        # Apply test filter to statistics as well
        if not show_fake:
            stats_query += " WHERE is_fake = false"
            
        stats_query += " GROUP BY resource_type, status, is_fake"
        
        cur.execute(stats_query)
        
        stats = {}
        for row in cur.fetchall():
            res_type = row[0]
            status = row[1]
            is_fake = row[2]
            count = row[3]
            
            if res_type not in stats:
                stats[res_type] = {
                    'total': 0, 
                    'available': 0, 
                    'allocated': 0, 
                    'quarantined': 0, 
                    'test': 0, 
                    'prod': 0,
                    'available_percent': 0
                }
            
            stats[res_type]['total'] += count
            stats[res_type][status] = stats[res_type].get(status, 0) + count
            if is_fake:
                stats[res_type]['test'] += count
            else:
                stats[res_type]['prod'] += count
        
        # Calculate percentages
        for res_type in stats:
            if stats[res_type]['total'] > 0:
                stats[res_type]['available_percent'] = int((stats[res_type]['available'] / stats[res_type]['total']) * 100)
        
        # Pagination parameters (simple defaults for now)
        try:
            page = int(request.args.get('page', '1') or '1')
        except (ValueError, TypeError):
            page = 1
        per_page = 50
        total = len(resources_list)
        total_pages = (total + per_page - 1) // per_page if total > 0 else 1
        
        # Sort parameters
        sort_by = request.args.get('sort', 'id')
        sort_order = request.args.get('order', 'asc')
        
        return render_template('resources.html', 
                             resources=resources_list,
                             stats=stats,
                             resource_type=resource_type,
                             status_filter=status_filter,
                             search=search_query,  # Changed from search_query to search
                             show_fake=show_fake,
                             total=total,
                             page=page,
                             total_pages=total_pages,
                             sort_by=sort_by,
                             sort_order=sort_order,
                             recent_activities=[],  # Empty for now
                             datetime=datetime)  # For template datetime usage
        
    except Exception as e:
        import traceback
        logging.error(f"Fehler beim Laden der Ressourcen: {str(e)}")
        logging.error(f"Traceback: {traceback.format_exc()}")
        flash('Fehler beim Laden der Ressourcen!', 'error')
        return redirect(url_for('admin.dashboard'))
    finally:
        cur.close()
        conn.close()


# Old add_resource function removed - using add_resources instead


@resource_bp.route('/resources/quarantine/<int:resource_id>', methods=['POST'])
@login_required
def quarantine(resource_id):
    """Ressource in Quarantäne versetzen"""
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        reason = request.form.get('reason', '')
        
        # Hole aktuelle Ressourcen-Info
        cur.execute("""
            SELECT resource_value, status, allocated_to_license 
            FROM resource_pools WHERE id = %s
        """, (resource_id,))
        resource = cur.fetchone()
        
        if not resource:
            flash('Ressource nicht gefunden!', 'error')
            return redirect(url_for('resources.resources'))
        
        # Setze Status auf quarantined
        cur.execute("""
            UPDATE resource_pools 
            SET status = 'quarantined', 
                allocated_to_license = NULL,
                status_changed_at = CURRENT_TIMESTAMP,
                status_changed_by = %s,
                quarantine_reason = %s
            WHERE id = %s
        """, (session['username'], reason, resource_id))
        
        # Wenn die Ressource zugewiesen war, entferne die Zuweisung
        if resource[2]:  # allocated_to_license
            cur.execute("""
                DELETE FROM license_resources 
                WHERE license_id = %s AND resource_id = %s
            """, (resource[2], resource_id))
            
            # History-Eintrag
            cur.execute("""
                INSERT INTO resource_history (resource_id, license_id, action, action_by, notes, ip_address)
                VALUES (%s, %s, 'quarantined', %s, %s, %s)
            """, (resource_id, resource[2], session['username'], reason, get_client_ip()))
        
        conn.commit()
        
        # Audit-Log
        log_audit('QUARANTINE', 'resource', resource_id,
                 old_values={'status': resource[1]},
                 new_values={'status': 'quarantined', 'reason': reason})
        
        flash(f'Ressource {resource[0]} in Quarantäne versetzt!', 'warning')
        
    except Exception as e:
        conn.rollback()
        logging.error(f"Fehler beim Quarantänisieren der Ressource: {str(e)}")
        flash('Fehler beim Quarantänisieren der Ressource!', 'error')
    finally:
        cur.close()
        conn.close()
    
    return redirect(url_for('resources.resources'))


@resource_bp.route('/resources/release', methods=['POST'])
@login_required
def release():
    """Ressourcen aus Quarantäne freigeben oder von Lizenz entfernen"""
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        resource_ids = request.form.getlist('resource_ids[]')
        action = request.form.get('action', 'release')
        
        if not resource_ids:
            flash('Keine Ressourcen ausgewählt!', 'error')
            return redirect(url_for('resources.resources'))
        
        for resource_id in resource_ids:
            # Hole aktuelle Ressourcen-Info
            cur.execute("""
                SELECT resource_value, status, allocated_to_license 
                FROM resource_pools WHERE id = %s
            """, (resource_id,))
            resource = cur.fetchone()
            
            if resource:
                # Setze Status auf available
                cur.execute("""
                    UPDATE resource_pools 
                    SET status = 'available', 
                        allocated_to_license = NULL,
                        status_changed_at = CURRENT_TIMESTAMP,
                        status_changed_by = %s,
                        quarantine_reason = NULL
                    WHERE id = %s
                """, (session['username'], resource_id))
                
                # Entferne Lizenz-Zuweisung wenn vorhanden
                if resource[2]:  # allocated_to_license
                    cur.execute("""
                        DELETE FROM license_resources 
                        WHERE license_id = %s AND resource_id = %s
                    """, (resource[2], resource_id))
                    
                    # History-Eintrag
                    cur.execute("""
                        INSERT INTO resource_history (resource_id, license_id, action, action_by, ip_address)
                        VALUES (%s, %s, 'released', %s, %s)
                    """, (resource_id, resource[2], session['username'], get_client_ip()))
                
                # Audit-Log
                log_audit('RELEASE', 'resource', resource_id,
                         old_values={'status': resource[1]},
                         new_values={'status': 'available'})
        
        conn.commit()
        flash(f'{len(resource_ids)} Ressource(n) freigegeben!', 'success')
        
    except Exception as e:
        conn.rollback()
        logging.error(f"Fehler beim Freigeben der Ressourcen: {str(e)}")
        flash('Fehler beim Freigeben der Ressourcen!', 'error')
    finally:
        cur.close()
        conn.close()
    
    return redirect(url_for('resources.resources'))


@resource_bp.route('/resources/history/<int:resource_id>')
@login_required
def resource_history(resource_id):
    """Zeigt die Historie einer Ressource"""
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        # Hole Ressourcen-Info
        cur.execute("""
            SELECT resource_type, resource_value, status, is_fake
            FROM resource_pools WHERE id = %s
        """, (resource_id,))
        resource = cur.fetchone()
        
        if not resource:
            flash('Ressource nicht gefunden!', 'error')
            return redirect(url_for('resources.resources'))
        
        # Hole Historie
        cur.execute("""
            SELECT 
                rh.action,
                rh.action_timestamp,
                rh.action_by,
                rh.notes,
                rh.ip_address,
                l.license_key,
                c.name as customer_name
            FROM resource_history rh
            LEFT JOIN licenses l ON rh.license_id = l.id
            LEFT JOIN customers c ON l.customer_id = c.id
            WHERE rh.resource_id = %s
            ORDER BY rh.action_timestamp DESC
        """, (resource_id,))
        
        history = []
        for row in cur.fetchall():
            history.append({
                'action': row[0],
                'timestamp': row[1],
                'by': row[2],
                'notes': row[3],
                'ip_address': row[4],
                'license_key': row[5],
                'customer_name': row[6]
            })
        
        return render_template('resource_history.html',
                             resource={
                                 'id': resource_id,
                                 'type': resource[0],
                                 'value': resource[1],
                                 'status': resource[2],
                                 'is_fake': resource[3]
                             },
                             history=history)
        
    except Exception as e:
        logging.error(f"Fehler beim Laden der Ressourcen-Historie: {str(e)}")
        flash('Fehler beim Laden der Historie!', 'error')
        return redirect(url_for('resources.resources'))
    finally:
        cur.close()
        conn.close()


@resource_bp.route('/resources/metrics')
@login_required
def resource_metrics():
    """Zeigt Metriken und Statistiken zu Ressourcen"""
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        # Allgemeine Statistiken
        cur.execute("""
            SELECT 
                resource_type,
                status,
                is_fake,
                COUNT(*) as count
            FROM resource_pools
            GROUP BY resource_type, status, is_fake
            ORDER BY resource_type, status
        """)
        
        general_stats = {}
        for row in cur.fetchall():
            res_type = row[0]
            if res_type not in general_stats:
                general_stats[res_type] = {
                    'total': 0,
                    'available': 0,
                    'allocated': 0,
                    'quarantined': 0,
                    'test': 0,
                    'production': 0
                }
            
            general_stats[res_type]['total'] += row[3]
            general_stats[res_type][row[1]] += row[3]
            if row[2]:
                general_stats[res_type]['test'] += row[3]
            else:
                general_stats[res_type]['production'] += row[3]
        
        # Zuweisungs-Statistiken
        cur.execute("""
            SELECT 
                rp.resource_type,
                COUNT(DISTINCT l.customer_id) as unique_customers,
                COUNT(DISTINCT rp.allocated_to_license) as unique_licenses
            FROM resource_pools rp
            JOIN licenses l ON rp.allocated_to_license = l.id
            WHERE rp.status = 'allocated'
            GROUP BY rp.resource_type
        """)
        
        allocation_stats = {}
        for row in cur.fetchall():
            allocation_stats[row[0]] = {
                'unique_customers': row[1],
                'unique_licenses': row[2]
            }
        
        # Historische Daten (letzte 30 Tage)
        cur.execute("""
            SELECT 
                DATE(action_timestamp) as date,
                action,
                COUNT(*) as count
            FROM resource_history
            WHERE action_timestamp >= CURRENT_DATE - INTERVAL '30 days'
            GROUP BY DATE(action_timestamp), action
            ORDER BY date, action
        """)
        
        historical_data = {}
        for row in cur.fetchall():
            date_str = row[0].strftime('%Y-%m-%d')
            if date_str not in historical_data:
                historical_data[date_str] = {}
            historical_data[date_str][row[1]] = row[2]
        
        # Top-Kunden nach Ressourcennutzung
        cur.execute("""
            SELECT 
                c.name,
                rp.resource_type,
                COUNT(*) as count
            FROM resource_pools rp
            JOIN licenses l ON rp.allocated_to_license = l.id
            JOIN customers c ON l.customer_id = c.id
            WHERE rp.status = 'allocated'
            GROUP BY c.name, rp.resource_type
            ORDER BY count DESC
            LIMIT 20
        """)
        
        top_customers = []
        for row in cur.fetchall():
            top_customers.append({
                'customer': row[0],
                'resource_type': row[1],
                'count': row[2]
            })
        
        return render_template('resource_metrics.html',
                             general_stats=general_stats,
                             allocation_stats=allocation_stats,
                             historical_data=historical_data,
                             top_customers=top_customers)
        
    except Exception as e:
        logging.error(f"Fehler beim Laden der Ressourcen-Metriken: {str(e)}")
        flash('Fehler beim Laden der Metriken!', 'error')
        return redirect(url_for('resources.resources'))
    finally:
        cur.close()
        conn.close()


@resource_bp.route('/resources/report', methods=['GET'])
@login_required
def resources_report():
    """Generiert einen Ressourcen-Report"""
    from io import BytesIO
    import xlsxwriter
    
    conn = get_connection()
    cur = conn.cursor()
    
    try:
        # Erstelle Excel-Datei im Speicher
        output = BytesIO()
        workbook = xlsxwriter.Workbook(output)
        
        # Formatierungen
        header_format = workbook.add_format({
            'bold': True,
            'bg_color': '#4CAF50',
            'font_color': 'white',
            'border': 1
        })
        
        date_format = workbook.add_format({'num_format': 'dd.mm.yyyy hh:mm'})
        
        # Sheet 1: Übersicht
        overview_sheet = workbook.add_worksheet('Übersicht')
        
        # Header
        headers = ['Ressourcen-Typ', 'Gesamt', 'Verfügbar', 'Zugewiesen', 'Quarantäne', 'Test', 'Produktion']
        for col, header in enumerate(headers):
            overview_sheet.write(0, col, header, header_format)
        
        # Daten
        cur.execute("""
            SELECT 
                resource_type,
                COUNT(*) as total,
                COUNT(CASE WHEN status = 'available' THEN 1 END) as available,
                COUNT(CASE WHEN status = 'allocated' THEN 1 END) as allocated,
                COUNT(CASE WHEN status = 'quarantined' THEN 1 END) as quarantined,
                COUNT(CASE WHEN is_fake = true THEN 1 END) as test,
                COUNT(CASE WHEN is_fake = false THEN 1 END) as production
            FROM resource_pools
            GROUP BY resource_type
            ORDER BY resource_type
        """)
        
        row = 1
        for data in cur.fetchall():
            for col, value in enumerate(data):
                overview_sheet.write(row, col, value)
            row += 1
        
        # Sheet 2: Detailliste
        detail_sheet = workbook.add_worksheet('Detailliste')
        
        # Header
        headers = ['Typ', 'Wert', 'Status', 'Test', 'Kunde', 'Lizenz', 'Zugewiesen am', 'Zugewiesen von']
        for col, header in enumerate(headers):
            detail_sheet.write(0, col, header, header_format)
        
        # Daten
        cur.execute("""
            SELECT 
                rp.resource_type,
                rp.resource_value,
                rp.status,
                rp.is_fake,
                c.name as customer_name,
                l.license_key,
                rp.status_changed_at,
                rp.status_changed_by
            FROM resource_pools rp
            LEFT JOIN licenses l ON rp.allocated_to_license = l.id
            LEFT JOIN customers c ON l.customer_id = c.id
            ORDER BY rp.resource_type, rp.resource_value
        """)
        
        row = 1
        for data in cur.fetchall():
            for col, value in enumerate(data):
                if col == 6 and value:  # Datum
                    detail_sheet.write_datetime(row, col, value, date_format)
                else:
                    detail_sheet.write(row, col, value if value is not None else '')
            row += 1
        
        # Spaltenbreiten anpassen
        overview_sheet.set_column('A:A', 20)
        overview_sheet.set_column('B:G', 12)
        
        detail_sheet.set_column('A:A', 15)
        detail_sheet.set_column('B:B', 30)
        detail_sheet.set_column('C:D', 12)
        detail_sheet.set_column('E:F', 25)
        detail_sheet.set_column('G:H', 20)
        
        workbook.close()
        output.seek(0)
        
        # Sende Datei
        filename = f"ressourcen_report_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
        
    except Exception as e:
        logging.error(f"Fehler beim Generieren des Reports: {str(e)}")
        flash('Fehler beim Generieren des Reports!', 'error')
        return redirect(url_for('resources.resources'))
    finally:
        cur.close()
        conn.close()


@resource_bp.route('/resources/add', methods=['GET', 'POST'])
@login_required
def add_resources():
    """Fügt neue Ressourcen zum Pool hinzu"""
    if request.method == 'POST':
        conn = get_connection()
        cur = conn.cursor()
        
        try:
            resource_type = request.form.get('resource_type')
            resources_text = request.form.get('resources_text', '')
            is_fake = request.form.get('is_fake', 'false') == 'true'
            
            if not resource_type or not resources_text.strip():
                flash('Bitte Ressourcentyp und Ressourcen angeben!', 'error')
                return redirect(url_for('resources.add_resources'))
            
            # Parse resources (one per line)
            resources = [r.strip() for r in resources_text.strip().split('\n') if r.strip()]
            
            # Validate resources based on type
            valid_resources = []
            invalid_resources = []
            
            for resource in resources:
                if resource_type == 'domain':
                    # Basic domain validation
                    import re
                    if re.match(r'^[a-zA-Z0-9][a-zA-Z0-9-]{0,61}[a-zA-Z0-9]?\.[a-zA-Z]{2,}$', resource):
                        valid_resources.append(resource)
                    else:
                        invalid_resources.append(resource)
                elif resource_type == 'ipv4':
                    # IPv4 validation
                    parts = resource.split('.')
                    if len(parts) == 4 and all(p.isdigit() and 0 <= int(p) <= 255 for p in parts):
                        valid_resources.append(resource)
                    else:
                        invalid_resources.append(resource)
                elif resource_type == 'phone':
                    # Phone number validation (basic)
                    import re
                    if re.match(r'^\+?[0-9]{7,15}$', resource.replace(' ', '').replace('-', '')):
                        valid_resources.append(resource)
                    else:
                        invalid_resources.append(resource)
                else:
                    invalid_resources.append(resource)
            
            # Check for duplicates
            existing_resources = []
            if valid_resources:
                placeholders = ','.join(['%s'] * len(valid_resources))
                cur.execute(f"""
                    SELECT resource_value 
                    FROM resource_pools 
                    WHERE resource_type = %s 
                    AND resource_value IN ({placeholders})
                """, [resource_type] + valid_resources)
                existing_resources = [row[0] for row in cur.fetchall()]
            
            # Filter out existing resources
            new_resources = [r for r in valid_resources if r not in existing_resources]
            
            # Insert new resources
            added_count = 0
            for resource in new_resources:
                cur.execute("""
                    INSERT INTO resource_pools 
                    (resource_type, resource_value, status, is_fake, created_by)
                    VALUES (%s, %s, 'available', %s, %s)
                """, (resource_type, resource, is_fake, session['username']))
                added_count += 1
            
            conn.commit()
            
            # Log audit
            if added_count > 0:
                log_audit('BULK_CREATE', 'resource', 
                         additional_info=f"Added {added_count} {resource_type} resources")
            
            # Flash messages
            if added_count > 0:
                flash(f'✅ {added_count} neue Ressourcen erfolgreich hinzugefügt!', 'success')
            if existing_resources:
                flash(f'⚠️ {len(existing_resources)} Ressourcen existierten bereits und wurden übersprungen.', 'warning')
            if invalid_resources:
                flash(f'❌ {len(invalid_resources)} ungültige Ressourcen wurden ignoriert.', 'error')
            
            return redirect(url_for('resources.resources', show_fake=request.form.get('show_fake', 'false')))
            
        except Exception as e:
            conn.rollback()
            logging.error(f"Fehler beim Hinzufügen von Ressourcen: {str(e)}")
            flash('Fehler beim Hinzufügen der Ressourcen!', 'error')
        finally:
            cur.close()
            conn.close()
    
    # GET request - show form
    show_fake = request.args.get('show_fake', 'false') == 'true'
    return render_template('add_resources.html', show_fake=show_fake)