@app.route('/access_logs_data')
def access_logs_data():
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(
            host=MYSQL_HOST,
            user=MYSQL_USER,
            password=MYSQL_PASSWORD,
            database=MYSQL_DATABASE
        )
        cursor = conn.cursor(dictionary=True)
        
        # Create access_logs table if it doesn't exist
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS access_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                license_plate VARCHAR(255) NOT NULL,
                feed_type VARCHAR(50) NOT NULL,
                action VARCHAR(50) NOT NULL,
                timestamp DATETIME NOT NULL
            )
        ''')
        
        # Fetch all logs
        cursor.execute("SELECT * FROM access_logs ORDER BY timestamp DESC")
        logs = cursor.fetchall()
        
        # Process logs for all_time_stats
        entrances = [log for log in logs if log['feed_type'].lower() == 'entrance']
        exits = [log for log in logs if log['feed_type'].lower() == 'exit']
        granted = [log for log in logs if log['action'].lower() == 'auto']
        denied = [log for log in logs if log['action'].lower() != 'auto']
        
        # Get unique plates
        registered_plates = set(log['license_plate'] for log in granted)
        unregistered_plates = set(log['license_plate'] for log in denied)
        
        # Find peak hour
        hour_counts = Counter()
        for log in logs:
            timestamp = log['timestamp']
            if hasattr(timestamp, 'hour'):
                hour = timestamp.hour
            else:
                # Handle string timestamps if needed
                try:
                    hour = datetime.fromisoformat(str(timestamp)).hour
                except:
                    hour = 0
            hour_counts[hour] += 1
        
        peak_hour = max(hour_counts.items(), key=lambda x: x[1])[0] if hour_counts else 0
        
        # Calculate average daily traffic
        if logs:
            # Get unique dates from logs
            dates = set()
            for log in logs:
                timestamp = log['timestamp']
                if hasattr(timestamp, 'date'):
                    dates.add(timestamp.date())
                else:
                    try:
                        dates.add(datetime.fromisoformat(str(timestamp)).date())
                    except:
                        pass
            
            avg_traffic = round(len(logs) / max(1, len(dates)))
        else:
            avg_traffic = 0
        
        # Create all_time_stats dictionary
        all_time_stats = {
            'total_entrances': len(entrances),
            'total_exits': len(exits),
            'granted_access': len(granted),
            'denied_access': len(denied),
            'registered_vehicles': len(registered_plates),
            'unregistered_vehicles': len(unregistered_plates),
            'peak_hour': f"{peak_hour:02d}:00",
            'avg_traffic': avg_traffic
        }
        
        # Process data for charts (daily, weekly, monthly)
        now = datetime.now()
        
        # Create reportData structure
        report_data = {
            'day': process_period_data(logs, now, 'day'),
            'week': process_period_data(logs, now, 'week'),
            'month': process_period_data(logs, now, 'month')
        }
        
        return jsonify({
            'all_time_stats': all_time_stats,
            'report_data': report_data
        })
    
    except mysql.connector.Error as err:
        logging.error(f"MySQL Error fetching reports data: {err}")
        return jsonify({'error': 'Error fetching reports data'}), 500
    finally:
        if cursor:
            cursor.close()
        if conn and conn.is_connected():
            conn.close()