#!/usr/bin/env python3
import requests
import urllib3
import subprocess
import os
import pandas as pd
import zipfile

# Disable SSL warnings
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

base_url = "https://localhost:443"
admin_user = {"username": "rac00n", "password": "1248163264"}

def login(session):
    """Login to admin panel"""
    login_data = {
        "username": admin_user["username"],
        "password": admin_user["password"]
    }
    response = session.post(f"{base_url}/login", data=login_data, verify=False, allow_redirects=False)
    return response.status_code == 302

def test_export_licenses():
    """Test license export functionality"""
    session = requests.Session()
    
    if not login(session):
        return ["✗ Failed to login"]
    
    results = []
    
    # Test Excel export
    print("1. Testing License Excel Export:")
    print("-" * 40)
    
    response = session.get(f"{base_url}/export/licenses?format=excel", verify=False)
    
    if response.status_code == 200:
        # Save file
        filename = "test_licenses.xlsx"
        with open(filename, 'wb') as f:
            f.write(response.content)
        
        # Check file size
        file_size = os.path.getsize(filename)
        results.append(f"✓ Excel export successful - Size: {file_size} bytes")
        
        # Verify it's a valid Excel file
        try:
            df = pd.read_excel(filename)
            results.append(f"✓ Valid Excel file with {len(df)} rows, {len(df.columns)} columns")
            
            # Check columns
            expected_cols = ['Lizenzschlüssel', 'Kunde', 'E-Mail', 'Typ', 'Status']
            found_cols = [col for col in expected_cols if col in df.columns]
            results.append(f"✓ Found columns: {', '.join(found_cols[:3])}...")
            
            # Check for UTF-8 content
            if df['Kunde'].str.contains('ü|ö|ä|ß').any():
                results.append("✓ UTF-8 characters preserved in Excel")
        except Exception as e:
            results.append(f"✗ Error reading Excel: {str(e)}")
        
        os.remove(filename)
    else:
        results.append(f"✗ Excel export failed: Status {response.status_code}")
    
    # Test CSV export
    print("\n2. Testing License CSV Export:")
    print("-" * 40)
    
    response = session.get(f"{base_url}/export/licenses?format=csv", verify=False)
    
    if response.status_code == 200:
        # Save file
        filename = "test_licenses.csv"
        with open(filename, 'wb') as f:
            f.write(response.content)
        
        # Check file
        file_size = os.path.getsize(filename)
        results.append(f"✓ CSV export successful - Size: {file_size} bytes")
        
        # Read and verify CSV
        try:
            df = pd.read_csv(filename, sep=';', encoding='utf-8-sig')
            results.append(f"✓ Valid CSV file with {len(df)} rows")
            
            # Check for German date format
            if df['Gültig bis'].str.match(r'\d{2}\.\d{2}\.\d{4}').any():
                results.append("✓ German date format (DD.MM.YYYY)")
        except Exception as e:
            results.append(f"✗ Error reading CSV: {str(e)}")
        
        os.remove(filename)
    else:
        results.append(f"✗ CSV export failed: Status {response.status_code}")
    
    return results

def test_export_customers():
    """Test customer export functionality"""
    session = requests.Session()
    
    if not login(session):
        return ["✗ Failed to login"]
    
    results = []
    
    # Test Excel export
    print("\n3. Testing Customer Excel Export:")
    print("-" * 40)
    
    response = session.get(f"{base_url}/export/customers?format=excel", verify=False)
    
    if response.status_code == 200:
        filename = "test_customers.xlsx"
        with open(filename, 'wb') as f:
            f.write(response.content)
        
        try:
            df = pd.read_excel(filename)
            results.append(f"✓ Customer Excel export: {len(df)} customers")
            
            # Check statistics columns
            if 'Lizenzen gesamt' in df.columns and 'Aktive Lizenzen' in df.columns:
                results.append("✓ License statistics included")
            
            # Check for UTF-8
            if 'Name' in df.columns and df['Name'].str.contains('ü|ö|ä|ß').any():
                results.append("✓ UTF-8 customer names preserved")
                
        except Exception as e:
            results.append(f"✗ Error: {str(e)}")
        
        os.remove(filename)
    else:
        results.append(f"✗ Customer export failed: Status {response.status_code}")
    
    # Test CSV export
    print("\n4. Testing Customer CSV Export:")
    print("-" * 40)
    
    response = session.get(f"{base_url}/export/customers?format=csv", verify=False)
    
    if response.status_code == 200:
        results.append("✓ Customer CSV export successful")
    else:
        results.append(f"✗ Customer CSV export failed: Status {response.status_code}")
    
    return results

def check_pandas_installation():
    """Check if pandas is installed in container"""
    print("5. Checking pandas installation:")
    print("-" * 40)
    
    result = subprocess.run([
        "docker", "exec", "admin-panel", "python", "-c", 
        "import pandas; import openpyxl; print('pandas version:', pandas.__version__)"
    ], capture_output=True, text=True)
    
    if result.returncode == 0:
        print("✓ pandas is installed")
        print(result.stdout)
    else:
        print("✗ pandas not installed - installing...")
        # Install pandas
        subprocess.run([
            "docker", "exec", "admin-panel", "pip", "install", 
            "pandas", "openpyxl"
        ], capture_output=True)
        print("✓ pandas and openpyxl installed")

# Main execution
print("Testing Export Functionality")
print("=" * 50)

# Check dependencies first
check_pandas_installation()

# Rebuild if needed
print("\nRebuilding admin panel...")
subprocess.run(["docker-compose", "build", "admin-panel"], capture_output=True)
subprocess.run(["docker-compose", "up", "-d", "admin-panel"], capture_output=True)
subprocess.run(["sleep", "5"], capture_output=True)

# Test exports
license_results = test_export_licenses()
for result in license_results:
    print(result)

customer_results = test_export_customers()
for result in customer_results:
    print(result)

# Check database content for comparison
print("\n6. Database Content Summary:")
print("-" * 40)

result = subprocess.run([
    "docker", "exec", "db", "psql", "-U", "adminuser", "-d", "meinedatenbank",
    "-c", "SELECT COUNT(*) as licenses FROM licenses;"
], capture_output=True, text=True)
print(result.stdout)

result = subprocess.run([
    "docker", "exec", "db", "psql", "-U", "adminuser", "-d", "meinedatenbank",
    "-c", "SELECT COUNT(*) as customers FROM customers;"
], capture_output=True, text=True)
print(result.stdout)