from flask import Blueprint, request, jsonify, send_file, make_response
from src.models.database import db
from src.models.recognition import Recognition
from src.models.camera import Camera
from src.models.list import Whitelist, Blacklist
import os
import tempfile
from datetime import datetime
import csv
from io import StringIO

export_bp = Blueprint('export', __name__)

@export_bp.route('/recognitions/csv', methods=['GET'])
def export_recognitions_to_csv():
    """Export recognitions to CSV"""
    # Get filter parameters
    license_plate = request.args.get('license_plate')
    camera_id = request.args.get('camera_id')
    date_from = request.args.get('date_from')
    date_to = request.args.get('date_to')
    is_in_whitelist = request.args.get('is_in_whitelist')
    is_in_blacklist = request.args.get('is_in_blacklist')
    
    # Build query
    query = db.session.query(Recognition, Camera).join(
        Camera, Recognition.camera_id == Camera.id
    )
    
    # Apply filters
    if license_plate:
        query = query.filter(Recognition.license_plate.like(f'%{license_plate}%'))
    
    if camera_id:
        query = query.filter(Recognition.camera_id == camera_id)
    
    if date_from:
        try:
            date_from = datetime.fromisoformat(date_from)
            query = query.filter(Recognition.capture_time >= date_from)
        except ValueError:
            return jsonify({'error': 'Invalid date_from format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400
    
    if date_to:
        try:
            date_to = datetime.fromisoformat(date_to)
            query = query.filter(Recognition.capture_time <= date_to)
        except ValueError:
            return jsonify({'error': 'Invalid date_to format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400
    
    if is_in_whitelist is not None:
        is_in_whitelist = is_in_whitelist.lower() == 'true'
        query = query.filter(Recognition.is_in_whitelist == is_in_whitelist)
    
    if is_in_blacklist is not None:
        is_in_blacklist = is_in_blacklist.lower() == 'true'
        query = query.filter(Recognition.is_in_blacklist == is_in_blacklist)
    
    # Order by capture time (newest first)
    query = query.order_by(Recognition.capture_time.desc())
    
    # Get results
    results = query.all()
    
    if not results:
        return jsonify({'error': 'No data to export'}), 404
    
    # Create CSV in memory
    output = StringIO()
    writer = csv.writer(output)
    
    # Write header
    writer.writerow(['ID', 'License Plate', 'Date & Time', 'Camera', 'Location', 'Confidence', 'Status', 'Processed'])
    
    # Write data rows
    for recognition, camera in results:
        status = ''
        if recognition.is_in_whitelist:
            status = 'Whitelist'
        elif recognition.is_in_blacklist:
            status = 'Blacklist'
        
        writer.writerow([
            recognition.id,
            recognition.license_plate,
            recognition.capture_time.strftime('%Y-%m-%d %H:%M:%S'),
            camera.name,
            camera.location or '',
            f"{recognition.confidence:.2f}%" if recognition.confidence else "N/A",
            status,
            'Yes' if recognition.processed else 'No'
        ])
    
    # Create temporary file
    temp_file = tempfile.NamedTemporaryFile(delete=False, suffix='.csv')
    temp_file_path = temp_file.name
    
    # Write CSV to file
    with open(temp_file_path, 'w', newline='') as f:
        f.write(output.getvalue())
    
    return send_file(
        temp_file_path,
        as_attachment=True,
        download_name=f'recognitions_{datetime.now().strftime("%Y%m%d_%H%M%S")}.csv',
        mimetype='text/csv'
    )

@export_bp.route('/whitelist/csv', methods=['GET'])
def export_whitelist_to_csv():
    """Export whitelist to CSV"""
    # Get filter parameters
    valid_only = request.args.get('valid') == 'true'
    
    # Build query
    query = Whitelist.query
    
    if valid_only:
        now = datetime.utcnow()
        query = query.filter(Whitelist.valid_from <= now)
        query = query.filter((Whitelist.valid_to == None) | (Whitelist.valid_to >= now))
    
    # Get results
    whitelist = query.all()
    
    if not whitelist:
        return jsonify({'error': 'No data to export'}), 404
    
    # Create CSV in memory
    output = StringIO()
    writer = csv.writer(output)
    
    # Write header
    writer.writerow(['ID', 'License Plate', 'Description', 'Valid From', 'Valid To', 'Created At'])
    
    # Write data rows
    for entry in whitelist:
        valid_to = entry.valid_to.strftime('%Y-%m-%d') if entry.valid_to else 'Indefinite'
        
        writer.writerow([
            entry.id,
            entry.license_plate,
            entry.description or '',
            entry.valid_from.strftime('%Y-%m-%d'),
            valid_to,
            entry.created_at.strftime('%Y-%m-%d %H:%M:%S')
        ])
    
    # Create temporary file
    temp_file = tempfile.NamedTemporaryFile(delete=False, suffix='.csv')
    temp_file_path = temp_file.name
    
    # Write CSV to file
    with open(temp_file_path, 'w', newline='') as f:
        f.write(output.getvalue())
    
    return send_file(
        temp_file_path,
        as_attachment=True,
        download_name=f'whitelist_{datetime.now().strftime("%Y%m%d_%H%M%S")}.csv',
        mimetype='text/csv'
    )

@export_bp.route('/blacklist/csv', methods=['GET'])
def export_blacklist_to_csv():
    """Export blacklist to CSV"""
    # Get filter parameters
    valid_only = request.args.get('valid') == 'true'
    
    # Build query
    query = Blacklist.query
    
    if valid_only:
        now = datetime.utcnow()
        query = query.filter(Blacklist.valid_from <= now)
        query = query.filter((Blacklist.valid_to == None) | (Blacklist.valid_to >= now))
    
    # Get results
    blacklist = query.all()
    
    if not blacklist:
        return jsonify({'error': 'No data to export'}), 404
    
    # Create CSV in memory
    output = StringIO()
    writer = csv.writer(output)
    
    # Write header
    writer.writerow(['ID', 'License Plate', 'Reason', 'Alert Level', 'Valid From', 'Valid To', 'Created At'])
    
    # Write data rows
    for entry in blacklist:
        valid_to = entry.valid_to.strftime('%Y-%m-%d') if entry.valid_to else 'Indefinite'
        
        writer.writerow([
            entry.id,
            entry.license_plate,
            entry.reason or '',
            entry.alert_level,
            entry.valid_from.strftime('%Y-%m-%d'),
            valid_to,
            entry.created_at.strftime('%Y-%m-%d %H:%M:%S')
        ])
    
    # Create temporary file
    temp_file = tempfile.NamedTemporaryFile(delete=False, suffix='.csv')
    temp_file_path = temp_file.name
    
    # Write CSV to file
    with open(temp_file_path, 'w', newline='') as f:
        f.write(output.getvalue())
    
    return send_file(
        temp_file_path,
        as_attachment=True,
        download_name=f'blacklist_{datetime.now().strftime("%Y%m%d_%H%M%S")}.csv',
        mimetype='text/csv'
    )

@export_bp.route('/stats/camera/csv', methods=['GET'])
def export_camera_stats_to_csv():
    """Export camera statistics to CSV"""
    # Get filter parameters
    date_from = request.args.get('date_from')
    date_to = request.args.get('date_to')
    
    # Build query
    from sqlalchemy import func, desc
    
    query = db.session.query(
        Camera.id,
        Camera.name,
        Camera.location,
        func.count(Recognition.id).label('recognition_count'),
        func.sum(Recognition.is_in_whitelist.cast(db.Integer)).label('whitelist_count'),
        func.sum(Recognition.is_in_blacklist.cast(db.Integer)).label('blacklist_count')
    ).join(
        Recognition, Camera.id == Recognition.camera_id
    ).group_by(
        Camera.id, Camera.name, Camera.location
    )
    
    # Apply date filters
    if date_from:
        try:
            date_from = datetime.fromisoformat(date_from)
            query = query.filter(Recognition.capture_time >= date_from)
        except ValueError:
            return jsonify({'error': 'Invalid date_from format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400
    
    if date_to:
        try:
            date_to = datetime.fromisoformat(date_to)
            query = query.filter(Recognition.capture_time <= date_to)
        except ValueError:
            return jsonify({'error': 'Invalid date_to format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400
    
    # Order by recognition count (highest first)
    query = query.order_by(desc('recognition_count'))
    
    # Get results
    results = query.all()
    
    if not results:
        return jsonify({'error': 'No data to export'}), 404
    
    # Create CSV in memory
    output = StringIO()
    writer = csv.writer(output)
    
    # Write header
    writer.writerow(['ID', 'Camera', 'Location', 'Recognitions', 'Whitelist', 'Blacklist'])
    
    # Write data rows
    for row in results:
        writer.writerow([
            row.id,
            row.name,
            row.location or '',
            row.recognition_count,
            row.whitelist_count or 0,
            row.blacklist_count or 0
        ])
    
    # Create temporary file
    temp_file = tempfile.NamedTemporaryFile(delete=False, suffix='.csv')
    temp_file_path = temp_file.name
    
    # Write CSV to file
    with open(temp_file_path, 'w', newline='') as f:
        f.write(output.getvalue())
    
    return send_file(
        temp_file_path,
        as_attachment=True,
        download_name=f'camera_stats_{datetime.now().strftime("%Y%m%d_%H%M%S")}.csv',
        mimetype='text/csv'
    )

# HTML export as alternative to PDF
@export_bp.route('/recognitions/html', methods=['GET'])
def export_recognitions_to_html():
    """Export recognitions to HTML"""
    # Get filter parameters
    license_plate = request.args.get('license_plate')
    camera_id = request.args.get('camera_id')
    date_from = request.args.get('date_from')
    date_to = request.args.get('date_to')
    is_in_whitelist = request.args.get('is_in_whitelist')
    is_in_blacklist = request.args.get('is_in_blacklist')
    
    # Build query
    query = db.session.query(Recognition, Camera).join(
        Camera, Recognition.camera_id == Camera.id
    )
    
    # Apply filters
    if license_plate:
        query = query.filter(Recognition.license_plate.like(f'%{license_plate}%'))
    
    if camera_id:
        query = query.filter(Recognition.camera_id == camera_id)
    
    if date_from:
        try:
            date_from = datetime.fromisoformat(date_from)
            query = query.filter(Recognition.capture_time >= date_from)
        except ValueError:
            return jsonify({'error': 'Invalid date_from format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400
    
    if date_to:
        try:
            date_to = datetime.fromisoformat(date_to)
            query = query.filter(Recognition.capture_time <= date_to)
        except ValueError:
            return jsonify({'error': 'Invalid date_to format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400
    
    if is_in_whitelist is not None:
        is_in_whitelist = is_in_whitelist.lower() == 'true'
        query = query.filter(Recognition.is_in_whitelist == is_in_whitelist)
    
    if is_in_blacklist is not None:
        is_in_blacklist = is_in_blacklist.lower() == 'true'
        query = query.filter(Recognition.is_in_blacklist == is_in_blacklist)
    
    # Order by capture time (newest first)
    query = query.order_by(Recognition.capture_time.desc())
    
    # Get results
    results = query.all()
    
    if not results:
        return jsonify({'error': 'No data to export'}), 404
    
    # Create HTML content
    html = """
    <!DOCTYPE html>
    <html>
    <head>
        <title>Recognition Report</title>
        <style>
            body { font-family: Arial, sans-serif; margin: 20px; }
            h1 { color: #333; text-align: center; }
            table { width: 100%; border-collapse: collapse; margin-top: 20px; }
            th, td { padding: 8px; text-align: left; border-bottom: 1px solid #ddd; }
            th { background-color: #f2f2f2; }
            tr:hover { background-color: #f5f5f5; }
            .filters { margin: 20px 0; padding: 10px; background-color: #f9f9f9; border-radius: 5px; }
            .summary { margin-top: 20px; font-weight: bold; }
        </style>
    </head>
    <body>
        <h1>Recognition Report</h1>
        
        <div class="filters">
            <h3>Filters:</h3>
    """
    
    if date_from:
        html += f"<p>From date: {date_from.strftime('%Y-%m-%d %H:%M:%S')}</p>"
    if date_to:
        html += f"<p>To date: {date_to.strftime('%Y-%m-%d %H:%M:%S')}</p>"
    if license_plate:
        html += f"<p>License plate: {license_plate}</p>"
    if camera_id:
        camera = Camera.query.get(camera_id)
        html += f"<p>Camera: {camera.name if camera else camera_id}</p>"
    
    html += """
        </div>
        
        <table>
            <tr>
                <th>License Plate</th>
                <th>Date & Time</th>
                <th>Camera</th>
                <th>Confidence</th>
                <th>Status</th>
            </tr>
    """
    
    for recognition, camera in results:
        status = ''
        if recognition.is_in_whitelist:
            status = 'Whitelist'
        elif recognition.is_in_blacklist:
            status = 'Blacklist'
        
        html += f"""
            <tr>
                <td>{recognition.license_plate}</td>
                <td>{recognition.capture_time.strftime('%Y-%m-%d %H:%M')}</td>
                <td>{camera.name}</td>
                <td>{"%.2f%%" % recognition.confidence if recognition.confidence else "N/A"}</td>
                <td>{status}</td>
            </tr>
        """
    
    html += f"""
        </table>
        
        <div class="summary">
            <p>Total records: {len(results)}</p>
            <p>Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}</p>
        </div>
    </body>
    </html>
    """
    
    # Create response
    response = make_response(html)
    response.headers["Content-Type"] = "text/html"
    response.headers["Content-Disposition"] = f"attachment; filename=recognitions_{datetime.now().strftime('%Y%m%d_%H%M%S')}.html"
    
    return response

@export_bp.route('/whitelist/html', methods=['GET'])
def export_whitelist_to_html():
    """Export whitelist to HTML"""
    # Get filter parameters
    valid_only = request.args.get('valid') == 'true'
    
    # Build query
    query = Whitelist.query
    
    if valid_only:
        now = datetime.utcnow()
        query = query.filter(Whitelist.valid_from <= now)
        query = query.filter((Whitelist.valid_to == None) | (Whitelist.valid_to >= now))
    
    # Get results
    whitelist = query.all()
    
    if not whitelist:
        return jsonify({'error': 'No data to export'}), 404
    
    # Create HTML content
    html = """
    <!DOCTYPE html>
    <html>
    <head>
        <title>Whitelist Report</title>
        <style>
            body { font-family: Arial, sans-serif; margin: 20px; }
            h1 { color: #333; text-align: center; }
            table { width: 100%; border-collapse: collapse; margin-top: 20px; }
            th, td { padding: 8px; text-align: left; border-bottom: 1px solid #ddd; }
            th { background-color: #f2f2f2; }
            tr:hover { background-color: #f5f5f5; }
            .filters { margin: 20px 0; padding: 10px; background-color: #f9f9f9; border-radius: 5px; }
            .summary { margin-top: 20px; font-weight: bold; }
        </style>
    </head>
    <body>
        <h1>Whitelist Report</h1>
        
        <div class="filters">
            <h3>Filters:</h3>
    """
    
    if valid_only:
        html += "<p>Only valid entries</p>"
    
    html += """
        </div>
        
        <table>
            <tr>
                <th>License Plate</th>
                <th>Description</th>
                <th>Valid From</th>
                <th>Valid To</th>
            </tr>
    """
    
    for entry in whitelist:
        valid_to = entry.valid_to.strftime('%Y-%m-%d') if entry.valid_to else 'Indefinite'
        
        html += f"""
            <tr>
                <td>{entry.license_plate}</td>
                <td>{entry.description or ''}</td>
                <td>{entry.valid_from.strftime('%Y-%m-%d')}</td>
                <td>{valid_to}</td>
            </tr>
        """
    
    html += f"""
        </table>
        
        <div class="summary">
            <p>Total records: {len(whitelist)}</p>
            <p>Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}</p>
        </div>
    </body>
    </html>
    """
    
    # Create response
    response = make_response(html)
    response.headers["Content-Type"] = "text/html"
    response.headers["Content-Disposition"] = f"attachment; filename=whitelist_{datetime.now().strftime('%Y%m%d_%H%M%S')}.html"
    
    return response

@export_bp.route('/blacklist/html', methods=['GET'])
def export_blacklist_to_html():
    """Export blacklist to HTML"""
    # Get filter parameters
    valid_only = request.args.get('valid') == 'true'
    
    # Build query
    query = Blacklist.query
    
    if valid_only:
        now = datetime.utcnow()
        query = query.filter(Blacklist.valid_from <= now)
        query = query.filter((Blacklist.valid_to == None) | (Blacklist.valid_to >= now))
    
    # Get results
    blacklist = query.all()
    
    if not blacklist:
        return jsonify({'error': 'No data to export'}), 404
    
    # Create HTML content
    html = """
    <!DOCTYPE html>
    <html>
    <head>
        <title>Blacklist Report</title>
        <style>
            body { font-family: Arial, sans-serif; margin: 20px; }
            h1 { color: #333; text-align: center; }
            table { width: 100%; border-collapse: collapse; margin-top: 20px; }
            th, td { padding: 8px; text-align: left; border-bottom: 1px solid #ddd; }
            th { background-color: #f2f2f2; }
            tr:hover { background-color: #f5f5f5; }
            .filters { margin: 20px 0; padding: 10px; background-color: #f9f9f9; border-radius: 5px; }
            .summary { margin-top: 20px; font-weight: bold; }
        </style>
    </head>
    <body>
        <h1>Blacklist Report</h1>
        
        <div class="filters">
            <h3>Filters:</h3>
    """
    
    if valid_only:
        html += "<p>Only valid entries</p>"
    
    html += """
        </div>
        
        <table>
            <tr>
                <th>License Plate</th>
                <th>Reason</th>
                <th>Alert Level</th>
                <th>Valid From</th>
                <th>Valid To</th>
            </tr>
    """
    
    for entry in blacklist:
        valid_to = entry.valid_to.strftime('%Y-%m-%d') if entry.valid_to else 'Indefinite'
        
        html += f"""
            <tr>
                <td>{entry.license_plate}</td>
                <td>{entry.reason or ''}</td>
                <td>{entry.alert_level}</td>
                <td>{entry.valid_from.strftime('%Y-%m-%d')}</td>
                <td>{valid_to}</td>
            </tr>
        """
    
    html += f"""
        </table>
        
        <div class="summary">
            <p>Total records: {len(blacklist)}</p>
            <p>Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}</p>
        </div>
    </body>
    </html>
    """
    
    # Create response
    response = make_response(html)
    response.headers["Content-Type"] = "text/html"
    response.headers["Content-Disposition"] = f"attachment; filename=blacklist_{datetime.now().strftime('%Y%m%d_%H%M%S')}.html"
    
    return response

@export_bp.route('/stats/camera/html', methods=['GET'])
def export_camera_stats_to_html():
    """Export camera statistics to HTML"""
    # Get filter parameters
    date_from = request.args.get('date_from')
    date_to = request.args.get('date_to')
    
    # Build query
    from sqlalchemy import func, desc
    
    query = db.session.query(
        Camera.id,
        Camera.name,
        Camera.location,
        func.count(Recognition.id).label('recognition_count'),
        func.sum(Recognition.is_in_whitelist.cast(db.Integer)).label('whitelist_count'),
        func.sum(Recognition.is_in_blacklist.cast(db.Integer)).label('blacklist_count')
    ).join(
        Recognition, Camera.id == Recognition.camera_id
    ).group_by(
        Camera.id, Camera.name, Camera.location
    )
    
    # Apply date filters
    if date_from:
        try:
            date_from = datetime.fromisoformat(date_from)
            query = query.filter(Recognition.capture_time >= date_from)
        except ValueError:
            return jsonify({'error': 'Invalid date_from format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400
    
    if date_to:
        try:
            date_to = datetime.fromisoformat(date_to)
            query = query.filter(Recognition.capture_time <= date_to)
        except ValueError:
            return jsonify({'error': 'Invalid date_to format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400
    
    # Order by recognition count (highest first)
    query = query.order_by(desc('recognition_count'))
    
    # Get results
    results = query.all()
    
    if not results:
        return jsonify({'error': 'No data to export'}), 404
    
    # Create HTML content
    html = """
    <!DOCTYPE html>
    <html>
    <head>
        <title>Camera Statistics Report</title>
        <style>
            body { font-family: Arial, sans-serif; margin: 20px; }
            h1 { color: #333; text-align: center; }
            table { width: 100%; border-collapse: collapse; margin-top: 20px; }
            th, td { padding: 8px; text-align: left; border-bottom: 1px solid #ddd; }
            th { background-color: #f2f2f2; }
            tr:hover { background-color: #f5f5f5; }
            .filters { margin: 20px 0; padding: 10px; background-color: #f9f9f9; border-radius: 5px; }
            .summary { margin-top: 20px; font-weight: bold; }
            .number { text-align: right; }
        </style>
    </head>
    <body>
        <h1>Camera Statistics Report</h1>
        
        <div class="filters">
            <h3>Filters:</h3>
    """
    
    if date_from:
        html += f"<p>From date: {date_from.strftime('%Y-%m-%d %H:%M:%S')}</p>"
    if date_to:
        html += f"<p>To date: {date_to.strftime('%Y-%m-%d %H:%M:%S')}</p>"
    
    html += """
        </div>
        
        <table>
            <tr>
                <th>Camera</th>
                <th>Location</th>
                <th>Recognitions</th>
                <th>Whitelist</th>
                <th>Blacklist</th>
            </tr>
    """
    
    for row in results:
        html += f"""
            <tr>
                <td>{row.name}</td>
                <td>{row.location or ''}</td>
                <td class="number">{row.recognition_count}</td>
                <td class="number">{row.whitelist_count or 0}</td>
                <td class="number">{row.blacklist_count or 0}</td>
            </tr>
        """
    
    html += f"""
        </table>
        
        <div class="summary">
            <p>Total cameras: {len(results)}</p>
            <p>Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}</p>
        </div>
    </body>
    </html>
    """
    
    # Create response
    response = make_response(html)
    response.headers["Content-Type"] = "text/html"
    response.headers["Content-Disposition"] = f"attachment; filename=camera_stats_{datetime.now().strftime('%Y%m%d_%H%M%S')}.html"
    
    return response